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

This article analyzes "how MyBatis-Plus implements linked table queries". The content is detailed and easy to understand, and friends who are interested in "how to achieve linked table query in MyBatis-Plus" can follow the editor's idea to read it slowly and deeply. I hope it will be helpful to everyone after reading. Let's follow the editor to learn more about "how to realize linked table query by MyBatis-Plus".

Installation using method

Add dependencies to the project

Com.github.yulichang mybatis-plus-join 1.2.2

Or clone code to local, execute mvn install, and then introduce the above dependency

Note: mybatis plus version > = 3.4.0

Use

Mapper inherits MPJBaseMapper (required)

Service inherits MPJBaseService (optional)

ServiceImpl inherits MPJBaseServiceImpl (optional)

Usage of core classes MPJLambdaWrapper and MPJQueryWrapperMPJLambdaWrapper

MPJLambdaWrapper example

Simple 3-table query

Class test {@ Resource private UserMapper userMapper Void testJoin () {List list = userMapper.selectJoinList (UserDTO.class, new MPJLambdaWrapper () .selectAll (UserDO.class) .select (UserAddressDO::getTel) .selectas (UserAddressDO::getAddress, UserDTO::getUserAddress) .select (AreaDO::getProvince) AreaDO::getCity) .leftJoin (UserAddressDO.class, UserAddressDO::getUserId, UserDO::getId) .leftJoin (AreaDO.class, AreaDO::getId, UserAddressDO::getAreaId) .eq (UserDO::getId, 1) .like (UserAddressDO::getTel, "1") .gt (UserDO::getId 5)) }}

Corresponding sql

SELECT t.id, t.name, t.sex, t.head_img, t1.tel, t1.address AS userAddress, t2.province, t2.city FROM user t LEFT JOIN user_address T1 ON t1.user_id = t.id LEFT JOIN area T2 ON t2.id = t1.area_id WHERE (t.id =? AND t1.tel LIKE? AND t.id >?)

Description:

UserDTO.class query result return class (resultType)

SelectAll () queries all fields of the specified entity class

Select () query specifies fields that support variable parameters. The same select can only query fields of the same table.

So separate UserAddressDO and AreaDO into two select ()

SelectAs () field alias query, used when the database field is inconsistent with the business entity class attribute name

LeftJoin () parameter description

The first parameter: the entity class class that participates in the join table

The second parameter: the ON field of the join table. This property must be the attribute of the first parameter entity class.

The third parameter: another entity class attribute of the ON that participates in the join table

The default main table alias is t, and other table aliases are called in the order in which they are called.

Conditional query, you can query the fields of the main table and all tables participating in the connection, and all call the native methods of mp. There is no risk of sql injection in normal use.

Paging query

Class test {@ Resource private UserMapper userMapper Void testJoin () {IPage iPage = userMapper.selectJoinPage (new Page (2,10), UserDTO.class, new MPJLambdaWrapper () .selectAll (UserDO.class) .select (UserAddressDO::getTel) .selectas (UserAddressDO::getAddress, UserDTO::getUserAddress) .select (AreaDO::getProvince AreaDO::getCity) .leftJoin (UserAddressDO.class, UserAddressDO::getUserId, UserDO::getId) .leftJoin (AreaDO.class, AreaDO::getId, UserAddressDO::getAreaId)) }}

Corresponding sql

SELECT t.id, t.name, t.sex, t.head_img, t1.tel, t1.address AS userAddress, t2.province, t2.cityFROM user t LEFT JOIN user_address T1 ON t1.user_id = t.id LEFT JOIN area T2 ON t2.id = t1.area_idLIMIT?,? MPJQueryWrapper

Simple 3-table query

Class test {@ Resource private UserMapper userMapper Void testJoin () {List list = userMapper.selectJoinList (UserDTO.class, new MPJQueryWrapper () .selectAll (UserDO.class) .select ("addr.tel", "addr.address") "a.province") .leftJoin ("user_address addr on t.id = addr.user_id") .rightJoin ("area an on addr.area_id = a.id") .like ("addr.tel", "1") .le ("a.province", "1")) }}

Corresponding sql

SELECT t.id, t.name, t.sex, t.head_img, addr.tel, addr.address, a.provinceFROM user t LEFT JOIN user_address addr on t.id = addr.user_id RIGHT JOIN area an on addr.area_id = a.idWHERE (addr.tel LIKE? AND a.province

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.