Shulou(Shulou.com)06/01 Report--

This article is about how MyBatis and MycatDao implement foreign key queries. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

1. MyBatis query

First, let's take a look at the following table structure:

The id of the user (user Information Table) table is the foreign key of user_id in the user_role (user permissions intermediate table).

Using MyBatis join query we need the following configuration and code: entity object:

/ / User

@ Getter

@ Setter

@ TableName ("user")

Public class User extends BaseEntity {

Private String name

Private String password

Private List roles

}

/ / user rights relationship intermediate table

@ Getter

@ Setter

@ TableName ("user_role")

Public class RoleRe {

Private String userId

Private String roleId

}

Web layer:

@ GetMapping ("/ testFind")

Public User testFind () {

Return userService.testFind ()

}

Servre layer:

Public User testFind () {

Return userMapper.leftJionRole ()

}

Mapper layer:

User leftJionRole ()

Xml configuration:

SELECT id,u.name,u.password, ur.role_id FROM user u, user_role ur WHERE u.id = ur.user_id

Xml is a major advantage of MyBatis. Xml allows more free operation of dynamic sql, but it also adds corresponding configuration operations while bringing convenience. If we want to use associated queries, if one of the returned objects is a collection attribute, we must configure collection and specify the type of ofType query results:

{

"id": 3

"name": "test"

"password": "111111"

"roles": [

{

"userId": null

"roleId": "3"

}

{

"userId": null

"roleId": "2"

}

]

}

It is obvious that the userId is empty here, because the mapping field of userId is not configured in result, so the returned result is null. Such minor errors often occur in development.

II. MycatDao query

Solid object

@ Getter

@ Setter

/ / mycatDao will map the case of the entity to underscore

Public class UserRole {

/ / need to inform mycatDao that this field is a foreign key

@ ForeginKey (value = User.class)

Private String userId

Private String roleId

}

@ GetMapping (value = "/ test/user", produces = "application/json")

Public JsonValue getUserInfoList () {

/ / paging object and status code

PageResultSet result = new PageResultSet ()

JsonValue jsonRest = null

Try {

PagedQuery qry = new PowerDomainQuery ()

/ / remove duplicate fields

.withAutoRemoveDupFields (true)

/ / add the first property User and ignore the return field id

.addDomainFieldsExclude (User.class, new String [] {"id"})

/ / add the second attribute user rights association table

.addDomainFieldsExclude (UserRole.class, null)

/ / execute query

JsonRest = leaderDao.exePagedQuery (qry)

} catch (Exception e) {

Result.retCode =-1

JsonRest = Json.createValue ("error:" + e.toString ())

}

Return jsonRest

}

Response result

[

{

"name": {

"chars": "test"

"string": "test"

"valueType": "STRING"

}

"password": {

"chars": "111111"

"string": "111111"

"valueType": "STRING"

}

"roles": {

"chars": "user,admin1"

"string": "user,admin1"

"valueType": "STRING"

}

"userId": {

"integral": true

"valueType": "NUMBER"

}

"roleId": {

"integral": true

"valueType": "NUMBER"

}

}

{

"name": {

"chars": "test"

"string": "test"

"valueType": "STRING"

}

"password": {

"chars": "111111"

"string": "111111"

"valueType": "STRING"

}

"roles": {

"chars": "user,admin1"

"string": "user,admin1"

"valueType": "STRING"

}

"userId": {

"integral": true

"valueType": "NUMBER"

}

"roleId": {

"integral": true

"valueType": "NUMBER"

}

}

]

In operation, MycatDao simplifies a lot of operations to facilitate developers, which will be obviously reflected in development efficiency, but the experience of MycatDao is inferior to that of MyBatis in response results, which will be improved in the future development.

Thank you for reading! On "MyBatis and MycatDao how to achieve foreign key query" this article is shared here, I hope the above content can be of some help to you, so that you can learn more knowledge, if you think the article is good, you can share it out for more people to see it!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.