How to realize Foreign key query with MyBatis and MycatDao
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!
