Mybatis advanced query

ResutlMap properties

Establish object-relational mapping

* resultType If the attribute name of the entity is the same as the field name in the table, the query result is automatically encapsulated in the entity class. * ResutlMap If the attribute name of the entity is not the same as the field name in the table, you can use the ResutlMap implementation to manually encapsulate the entity classCopy the code

1. Write the UserMapper interface

public interface UserMapper { 
    public List<User> findAllResultMap(a); 
}
Copy the code

1. Write UserMapper. XML

<! -- Implement manual mapping encapsulation resultMap ID ="userResultMap" This label uniquely identifies the entity type after type="user" encapsulation. < ID column=" UID "Property =" ID "></ ID > Encapsulates primary key fields in the table <result column="NAME" property="username"></result> Column ="NAME" Select * from user where username = 'username'; select * from user where username = 'username'; 
    <resultMap id="userResultMap" type="user"> 
        <id column="uid" property="id"></id>
        <result column="NAME" property="username"></result> 
        <result column="PASSWORD" property="username"></result> 
    </resultMap> 
    
    <select id="findAllResultMap" resultMap="userResultMap"> 
        SELECT id AS uid,username AS NAME,password AS PASSWORD FROM USER 
    </select>
Copy the code

3) Code testing

@Test 
public void testFindAllResultMap(a) throws Exception { 
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class); 
    List<User> list = userMapper.findAllResultMap(); 
    for(User user : list) { System.out.println(user); }}Copy the code

Multi-condition query (three types)

demand

Query user table by id and USERNAME

1) Method 1

Get parameters using #{arg0}-#{argn} or #{param1}-#{paramn}

UserMapper interface

public interface UserMapper { 

    public List<User> findByIdAndUsername1(Integer id, String username); 
    
}
Copy the code

UserMapper.xml

<mapper namespace="com.lagou.mapper.UserMapper"> 
    
    <select id="findByIdAndUsername1" resultType="user"> 
        <! -- select * from user where id = #{arg0} and username = #{arg1} --> 
        select * from user where id = #{param1} and username = #{param2} 
    </select> 
    
</mapper>
Copy the code

test

@Test 
public void testFindByIdAndUsername(a) throws Exception { 
    
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class); 
    List<User> list = userMapper.findByIdAndUsername1(1 , "Son desire");
    System.out.println(list); 
}
Copy the code

2) Method two

Using annotations, introduce the @param () annotation to get parameters

UserMapper interface

public interface UserMapper { 
    
    public List<User> findByIdAndUsername2(@Param("id") Integer id,@Param("username") String username); 
    
}
Copy the code

UserMapper.xml

<mapper namespace="com.lagou.mapper.UserMapper"> 
    
    <select id="findByIdAndUsername2" resultType="user"> 
        select * from user where id = #{id} and username = #{username} 
    </select> 
    
</mapper>
Copy the code

test

@Test 
public void testFindByIdAndUsername(a) throws Exception { 

    UserMapper userMapper = sqlSession.getMapper(UserMapper.class); 
    List<User> list = userMapper.findByIdAndUsername2(1."Son desire");
    System.out.println(list); 
}
Copy the code

3) Method 3 (recommended)

Pass parameters using POJO objects

UserMapper interface

public interface UserMapper { 

    public List<User> findByIdAndUsername3(User user); 
    
}
Copy the code

UserMapper.xml

<mapper namespace="com.lagou.mapper.UserMapper"> 

    <select id="findByIdAndUsername3" parameterType="com.lagou.domain.User" resultType="com.lagou.domain.User"> 
        select * from user where id = #{id} and username = #{username} 
    </select> 
    
</mapper>
Copy the code

test

@Test 
public void testFindByIdAndUsername(a) throws Exception { 

    UserMapper userMapper = sqlSession.getMapper(UserMapper.class); 
    User param = new User(); 
    param.setId(1); 
    param.setUsername("Son desire"); 
    List<User> list = userMapper.findByIdAndUsername3(param); 
    System.out.println(list); 
    
}
Copy the code

Fuzzy query

demand

SQL > alter table user

1) Method 1

UserMapper interface

public interface UserMapper { 

    public List<User> findByUsername1(String username); 
    
}
Copy the code

UserMapper.xml

<mapper namespace="com.lagou.mapper.UserMapper"> 

    <select id="findByUsername1" parameterType="string" resultType="user"> 
        select * from user where username like #{username} 
    </select> 
    
</mapper>
Copy the code

test

@Test 
public void testFindByUsername(a) throws Exception { 

    UserMapper userMapper = sqlSession.getMapper(UserMapper.class); 
    List<User> list = userMapper.findByUsername1(King "% %"); 
    for(User user : list) { System.out.println(user); }}Copy the code

2) Method two

UserMapper interface

public interface UserMapper { 
    
    public List<User> findByUsername2(String username); 
}
Copy the code

UserMapper.xml

<mapper namespace="com.lagou.mapper.UserMapper"> 

    <! -- Not recommended because of SQL injection problems --> 
    <select id="findByUsername2" parameterType="string" resultType="user"> 
        select * from user where username like '${value}' 
    </select> 
    
</mapper>
Copy the code

test

@Test 
public void testFindByUsername(a) throws Exception { 

    UserMapper userMapper = sqlSession.getMapper(UserMapper.class); 
    List<User> list = userMapper.findByUsername2(King "% %"); 
    for(User user : list) { System.out.println(user); }}Copy the code

3) ${} differs from #{}

#{} : indicates a placeholder
- #{} allows you to set values in a preparedStatement placeholder, automatically convert Java and JDBC types, and effectively prevent SQL injection.
- #{} can accept simple type values or POJO attribute values.
- If parameterType transfers a single simple type value, the name in the #{} parentheses is arbitrary.
${} : concatenates SQL strings
- ${} allows you to concatenate the incoming content of parameterType into SQL without JDBC type conversion, resulting in SQL injection problems.
- ${} can accept simple type values or POJO attribute values.
- If parameterType transmits a single simple type value, the ${} bracket must be value.
  - Add: TextsqlNode. Java source code can be proved

Mybatis mapping file in depth

Returns the primary key

Application Scenario After inserting a record into the database, we want to get the primary key of the record in the database immediately.

useGeneratedKeys

public interface UserMapper { 

    // Return primary key
    public void save(User user); 
    
}
Copy the code

<! -- useGeneratedKeys="true" -- useGeneratedKeys="true" -- useGeneratedKeys="true" -- useGeneratedKeys="true" -- useGeneratedKeys="true" 
<insert id="save" parameterType="user" useGeneratedKeys="true" keyProperty="id"> 
    INSERT INTO `user`(username,birthday,sex,address) values(#{username},#{birthday},#{sex},#{address}) 
</insert>
Copy the code

Note: this only applies to the primary key increment database, mysql and sqlserver support, oracle does not.

selectKey

public interface UserMapper { 

    // Return primary key
    public void save(User user); 
    
}
Copy the code

<! -- selectKey applies to a wide range of applications, Support all types database keyColumn="id" specify the primary keyColumn name keyProperty="id" specify the primary key to encapsulate to the id attribute of the entity resultType="int" specify the primary key type order="AFTER" set before (AFTER) the SQL statement execution, Execute this statement --> 
<insert id="save" parameterType="user"> 
    <selectKey keyColumn="id" keyProperty="id" resultType="int" order="AFTER"> 
        SELECT LAST_INSERT_ID(); 
    </selectKey> 
    INSERT INTO `user`(username,birthday,sex,address) 
        values(#{username},#{birthday},#{sex},#{address}) 
</insert>
Copy the code

The test code

@Test 
public void testSave(a) throws Exception { 
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class); 
    
    User user = new User(); 
    user.setUsername("Son desire"); 
    user.setAddress("Beijing"); 
    user.setBirthday(new Date()); 
    user.setSex("Male"); 
    
    userMapper.save(user); 
    System.out.println("Return primary key :" + user.getId()); 
}
Copy the code

Dynamic SQL

Application Scenario Dynamic SQL is used when we need to execute different SQL statements based on different conditions.

Dynamic SQL <if>

Query by id and username, but not sure if both have values.

A) UserMapper interface

public List<User> findByIdAndUsernameIf(User user);
Copy the code

B) Usermapper.xml mapping

<! -- where tag is equivalent to where 1=1, but does not concatenate where keyword without condition --> 
<select id="findByIdAndUsernameIf" parameterType="user" resultType="user"> 
    SELECT * FROM `user` 
    <where> 
        <if test="id ! = null"> 
            AND id = #{id} 
        </if> 
        <if test="username ! = null"> 
            AND username = #{username} 
        </if> 
    </where> 
</select>
Copy the code

C) Test code

// If tag Where tag
@Test public void testFindByIdAndUsernameIf(a) throws Exception { 

    UserMapper userMapper = sqlSession.getMapper(UserMapper.class); 
    
    User param = new User(); 
    // param.setId(42); 
    // param.setUsername(" wang 小二");
    
    List<User> list = userMapper.findByIdAndUsernameIf(param);
    System.out.println(list); 
}
Copy the code

Dynamic SQL <set>

The user table data needs to be updated dynamically, if the attribute has a value, it is updated, and no value is processed.

A) UserMapper interface

public void updateIf(User user);
Copy the code

B) Usermapper.xml mapping

<! When the set tag is updated, it automatically adds the set keyword, and then removes the last condition comma. 
<update id="updateIf" parameterType="user"> 
    UPDATE `user` 
    <set>
        <if test="username ! = null"> 
            username = #{username}, 
        </if> 
        <if test="birthday ! = null"> 
            birthday = #{birthday}, 
        </if> 
        <if test="sex ! =null"> 
            sex = #{sex}, 
        </if> 
        <if test="address ! =null"> 
            address = #{address}, 
        </if> 
    </set> 
    WHERE id = #{id} 
</update>
Copy the code

C) Test code

/ / set the label
@Test public void testUpdateIf(a)throws Exception{ 
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class); 
    User user = new User(); 
    user.setId(1); 
    user.setUsername("Little Two Kings"); 
    user.setSex("Female"); 
    
    userMapper.updateIf(user); 
    
}
Copy the code

Dynamic SQL <foreach>

Foreach is used to loop over data. For example, select * from user where id in (1,2,3).

The * <foreach> tag is used to iterate over the collection and has the following attributes: • Collection: represents the collection element to iterate over • Open: represents the beginning of the statement • Close: represents the end of the statement • Item: represents each element of the collection to iterate over, and the variable name generated • Sperator: Represent separatorCopy the code

A) set

UserMapper interface

public List<User> findByList(List<Integer> ids);
Copy the code

UserMaper. XML mapping

<! Select * from List; select * from List; select * from List; 
<select id="findByList" parameterType="list" resultType="user" > 
    SELECT * FROM `user` 
    <where> 
        <foreach collection="collection" open="id in(" close=")" item="id" separator=",">
            #{id} 
        </foreach> 
    </where> 
</select>
Copy the code

The test code

// foreach tag list
@Test public void testFindByList(a) throws Exception { 
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class); 
    
    List<Integer> ids = new ArrayList<>(); 
    ids.add(46); 
    ids.add(48); 
    ids.add(51); 
    
    List<User> list = userMapper.findByList(ids); 
    System.out.println(list); 
}
Copy the code

B) array

UserMapper interface

public List<User> findByArray(Integer[] ids);
Copy the code

UserMaper. XML mapping

<! -- If the query condition is Array Array, the collection property value is Array -->
<select id="findByArray" parameterType="int" resultType="user"> 
    SELECT * FROM `user` 
    <where> 
        <foreach collection="array" open="id in(" close=")" item="id" separator=",">
            #{id} 
        </foreach> 
    </where> 
</select>
Copy the code

The test code

// Foreach tag array
@Test 
public void testFindByArray(a) throws Exception { 
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class); 
    
    Integer[] ids = {46.48.51}; 
    List<User> list = userMapper.findByArray(ids); 
    
    System.out.println(list); 
}
Copy the code

SQL fragment

Application scenario You can extract repeated SQL files from mapping files and use include to reference them

<! -- extract SQL fragment --> 
<sql id="selectUser"> 
    SELECT * FROM `user` 
</sql> 

<select id="findByList" parameterType="list" resultType="user" > 
    <! SQL > alter table SQL > alter table SQL 
    <include refid="selectUser"></include> 
    <where> 
        <foreach collection="collection" open="id in(" close=")" item="id" separator=",">
            #{id} 
        </foreach> 
    </where> 
</select> 

<select id="findByArray" parameterType="integer[]" resultType="user"> 
    <! SQL > alter table SQL > alter table SQL 
    <include refid="selectUser"></include> 
    <where> 
        <foreach collection="array" open="id in(" close=")" item="id" separator=",">
            #{id} 
        </foreach> 
    </where> 
</select>
Copy the code

Knowledge summary

MyBatis mapping file configuration

<select> : query <insert> : insert <update> : modify <delete> : delete <selectKey> : return primary key < WHERE > : where condition <if> : if judgment <foreach> : for loop <set> : Set < SQL > : SQL fragment extractionCopy the code

Mybatis core profile in depth

The plugins TAB

MyBatis can use third-party plug-ins to expand the function, PageHelper PageHelper is to encapsulate complex operations of paging, using a simple way to get the relevant data of paging development steps: ① Import universal PageHelper coordinates ② configure PageHelper plugin in mybatis core configuration file ③ test page data retrieval

① Import common PageHelper coordinates

<! -- Paging Assistant --> 
<dependency> 
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper</artifactId> 
    <version>4.1.2</version> 
</dependency> 
<dependency> 
    <groupId>com.github.jsqlparser</groupId> 
    <artifactId>jsqlparser</artifactId> 
    <version>0.9.4</version> 
</dependency>
Copy the code

Configure PageHelper plugin in mybatis core configuration file

<! -- Plugins for paging Assistant --> 
<plugin interceptor="com.github.pagehelper.PageHelper"> 
    <! --> 
    <property name="dialect" value="mysql"/> 
</plugin>
Copy the code

③ Test the implementation of paging code

@Test 
public void testPageHelper(a){ 
    // Set paging parameters
    PageHelper.startPage(1.2); 
    
    List<User> select = userMapper2.select(null); 
    for(User user : select){ System.out.println(user); }}Copy the code

Get additional parameters related to paging

// Data for other pages
PageInfo<User> pageInfo = new PageInfo<User>(select); 
System.out.println("Total number of items:"+pageInfo.getTotal()); 
System.out.println("Total pages:"+pageInfo.getPages()); 
System.out.println("Current page:"+pageInfo.getPageNum()); 
System.out.println("Page display length:"+pageInfo.getPageSize()); 
System.out.println("Is the first page:"+pageInfo.isIsFirstPage()); 
System.out.println("Is the last page:"+pageInfo.isIsLastPage());
Copy the code

Knowledge summary

MyBatis core configuration file common tags: 1, this tag can load external properties file 2, typeAliases tag: set type alias 3, Environments tag: data source environment configuration tag 4, plugins tag: Configure the MyBatis plugin

Mybatis multi-table query

This section describes database table relationships

Relational database table relationships are divided into

* One to one * one to many * many-to-manyCopy the code

For example,

* A person can only have one ID number and one ID number can only belong to one person * Users and orders are one-to-many, Orders and users are many-to-one, a user can place multiple orders. Multiple orders belong to the same user * Students and courses are many-to-one, a student can take multiple courses. A course can be taken by multiple studentsCopy the code

Case Environment Preparation

DROP TABLE IF EXISTS `orders`; 
CREATE TABLE `orders` ( 
    `id` INT(11) NOT NULL AUTO_INCREMENT, 
    `ordertime` VARCHAR(255) DEFAULT NULL.`total` DOUBLE DEFAULT NULL.`uid` INT(11) DEFAULT NULL, 
    PRIMARY KEY (`id`), 
    KEY `uid` (`uid`), 
    CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `user` (`id`))ENGINE=INNODB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8; 

-- ---------------------------- 
-- Records of orders 
-- ---------------------------- 
INSERT INTO `orders` VALUES ('1'.'2020-12-12'.'3000'.'1'); 
INSERT INTO `orders` VALUES ('2'.'2020-12-12'.'4000'.'1'); 
INSERT INTO `orders` VALUES ('3'.'2020-12-12'.'5000'.'2'); 

-- ---------------------------- 
-- Table structure for sys_role 
-- ---------------------------- 
DROP TABLE IF EXISTS `sys_role`; 
CREATE TABLE `sys_role` ( 
    `id` INT(11) NOT NULL AUTO_INCREMENT, 
    `rolename` VARCHAR(255) DEFAULT NULL.`roleDesc` VARCHAR(255) DEFAULT NULL, 
    PRIMARY KEY (`id`))ENGINE=INNODB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8; 

-- ---------------------------- 
-- Records of sys_role 
-- ---------------------------- 
INSERT INTO `sys_role` VALUES ('1'.'CTO'.'CTO'); 
INSERT INTO `sys_role` VALUES ('2'.'CEO'.'CEO'); 

-- ----------------------------
-- Table structure for sys_user_role 
-- ---------------------------- 
DROP TABLE IF EXISTS `sys_user_role`; 
CREATE TABLE `sys_user_role` ( 
    `userid` INT(11) NOT NULL.`roleid` INT(11) NOT NULL, 
    PRIMARY KEY (`userid`.`roleid`), 
    KEY `roleid` (`roleid`),
    CONSTRAINT `sys_user_role_ibfk_1` FOREIGN KEY (`userid`) REFERENCES `sys_role` (`id`), 
    CONSTRAINT `sys_user_role_ibfk_2` FOREIGN KEY (`roleid`) REFERENCES `user` (`id`))ENGINE=INNODB DEFAULT CHARSET=utf8; 

-- ---------------------------- 
-- Records of sys_user_role 
-- ---------------------------- 
INSERT INTO `sys_user_role` VALUES ('1'.'1'); 
INSERT INTO `sys_user_role` VALUES ('2'.'1'); 
INSERT INTO `sys_user_role` VALUES ('1'.'2'); 
INSERT INTO `sys_user_role` VALUES ('2'.'2');
Copy the code

One to one (many to one)

introduce

One-to-one query model the relationship between the user table and the order table is that a user has multiple orders, and an order only belongs to the demand of a user for one-to-one query: query all orders, and at the same time, query the user to which each order belongs

One-to-one query statement

SELECT * FROM orders o LEFT JOIN USER u ON o.`uid`=u.`id`;
Copy the code

Code implementation

1) Order entity

public class Order { 

    private Integer id; 
    private Date ordertime; 
    private double money; 
    
    // Indicates the user to whom the current order belongs
    private User user; 
}
Copy the code

2) OrderMapper interface

public interface OrderMapper { 

    public List<Order> findAllWithUser(a); 
    
}
Copy the code

3) OrderMapper.xml mapping

<resultMap id="orderMap" type="com.lagou.domain.Order"> 
    <id column="id" property="id"></id> 
    <result column="ordertime" property="ordertime"></result> 
    <result column="money" property="money"></result> 
    
        <! JavaType ="user" encapsulates the entity's attribute type --> 
    <association property="user" javaType="com.lagou.domain.User"> 
        <id column="uid" property="id"></id> 
        <result column="username" property="username"></result> 
        <result column="birthday" property="birthday"></result> 
        <result column="sex" property="sex"></result> 
        <result column="address" property="address"></result> 
    </association> 
</resultMap>
Copy the code

4) Test code

@Test 
public void testOrderWithUser(a) throws Exception { 
    OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class); 
    
    List<Order> list = orderMapper.findAllWithUser(); 
    
    for(Order order : list) { System.out.println(order); }}Copy the code

More than a pair of

introduce

One-to-many query model the relationship between the user table and the order table is that a user has multiple orders, and an order only belongs to the requirements of a user for one-to-many query: Query all users, and query the orders that the user has at the same time

One-to-many query statement

SELECT *,o.id oid FROM USER u LEFT JOIN orders o ON u.`id` = o.`uid`;
Copy the code

Code implementation

1) User entity

public class User { 
    private Integer id;
    private String username;
    private Date birthday; 
    private String sex; 
    private String address; 
    
    // Represents the list of orders that the current user has
    private List<Order> orderList; 
}
Copy the code

2) UserMapper interface

public interface UserMapper { 

    public List<User> findAllWithOrder(a);
}
Copy the code

3) Usermapper.xml mapping

<resultMap id="userMap" type="com.lagou.domain.User">
    <id column="id" property="id"></id> 
    <result column="username" property="username"></result> 
    <result column="birthday" property="birthday"></result>
    <result column="sex" property="sex"></result> 
    <result column="address" property="address"></result> 
        <! OfType =" Order "Encapsulates the generic type of the collection --> 
    <collection property="orderList" ofType="com.lagou.domain.Order"> 
        <id column="oid" property="id"></id> 
        <result column="ordertime" property="ordertime"></result> 
        <result column="money" property="money"></result> 
    </collection> 
</resultMap> 

<select id="findAllWithOrder" resultMap="userMap"> 
    SELECT *,o.id oid FROM USER u LEFT JOIN orders o ON u.`id`=o.`uid`; 
</select>
Copy the code

4) Test code

@Test 
public void testUserWithOrder(a) throws Exception { 
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class); 
    
    List<User> list = userMapper.findAllWithOrder(); 
    
    for(User user : list) { System.out.println(user); }}Copy the code

Many to many

introduce

In the many-to-many query model, the relationship between the user table and the role table is as follows: one user has multiple roles, and one role is used by multiple users. The requirement of many-to-many query is that all users are queried simultaneously

Many-to-many query statement

SELECT 
    * 
FROM
    USER u - the user table
    LEFT JOIN user_role ur Join the middle table with the left outer
        ON u.`id` = ur.`uid` 
    LEFT JOIN role r Join the middle table with the left outer
        ON ur.`rid` = r.`id` ;
Copy the code

Code implementation

1) User and Role entities

public class User { 

    private Integer id; 
    private String username; 
    private Date birthday; 
    private String sex; 
    private String address; 
    // Represents the list of roles associated with the current user
    private List<Role> roleList; 
}

public class Role { 

    private Integer id; 
    private String roleName; 
    private String roleDesc; 
}
Copy the code

2) UserMapper interface

public interface UserMapper { 

    public List<User> findAllWithRole(a); 
}
Copy the code

3) Usermapper.xml mapping

<resultMap id="userAndRoleMap" type="com.lagou.domain.User"> 
    <id column="id" property="id"></id> 
    <result column="username" property="username"></result> 
    <result column="birthday" property="birthday"></result> 
    <result column="sex" property="sex"></result> 
    <result column="address" property="address"></result> 
    <collection property="orderList" ofType="com.lagou.domain.Role"> 
        <id column="rid" property="id"></id>
        <result column="role_name" property="roleName"></result> 
        <result column="role_desc" property="roleDesc"></result> 
    </collection> 
</resultMap> 

<select id="findAllWithRole" resultMap="userAndRoleMap"> 
    SELECT * FROM USER u LEFT JOIN user_role ur ON u.`id`=ur.`uid` INNER JOIN role r ON ur.`rid` = r.`id`; 
</select>
Copy the code

4) Test code

@Test 
public void testUserWithRole(a) throws Exception { 
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class); 
    
    List<User> list = userMapper.findAllWithRole(); 
    
    for(User user : list) { System.out.println(user); }}Copy the code

summary

MyBatis multi-table configuration mode

* Many-to-one (one-to-one) configuration: Use <resultMap>+< Association > to configure * One-to-many configuration: Use <resultMap>+< Collection > to configure * Many-to-many: <resultMap>+< Collection > Configure * Many-to-many configuration is similar to one-to-many configuration, but the difficulty lies in the writing of SQL statements.Copy the code

MyBatis nested query

What is a nested query

Nested query is to split the joint query statement in the original multi-table query into a single table query, and then nested together using the syntax of Mybatis.

Take a chestnut

* Requirement: Query an order, and at the same time query the user that the order belongs to 1. SELECT * FROM orders o LEFT JOIN USER u ON o. 'uid' = U. 'id'; SELECT * FROM orders where id = no; SELECT * FROM orders where id = no; SELECT * FROM 'user' WHERE id = #{SELECT * FROM 'user' WHERE id = #Copy the code

One-to-one nested queries

introduce

Requirement: Query an order, and at the same time query the user that the order belongs to

One-to-one query statement

-- Check the order first
SELECT * FROM orders; 

-- Then query the user according to the order uid foreign key
SELECT * FROM `user` WHERE id = #{order uid};
Copy the code

Code implementation

1) OrderMapper interface

public interface OrderMapper { 

    public List<Order> findAllWithUser(a); 
}
Copy the code

2) OrderMapper.xml mapping

<! -- one-to-one nested query --> 
<resultMap id="orderMap" type="order">
    <id column="id" property="id"></id> 
    <result column="ordertime" property="ordertime"></result> 
    <result column="money" property="money"></result> 
    <! Select * from user where id = 1; 
    <association property="user" javaType="user" column="uid" select="com.lagou.mapper.UserMapper.findById"></association> 
</resultMap> 

<select id="findAllWithUser" resultMap="orderMap" > 
    SELECT * FROM orders 
</select>
Copy the code

3) UserMapper interface

public interface UserMapper { 

    public User findById(Integer id); 
}
Copy the code

4) Usermapper.xml mapping

<select id="findById" parameterType="int" resultType="user"> 
    SELECT * FROM `user` where id = #{uid} 
</select>
Copy the code

5) Test code

@Test 
public void testOrderWithUser(a) throws Exception { 
    OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class); 
    
    List<Order> list = orderMapper.findAllWithUser(); 
    
    for(Order order : list) { System.out.println(order); }}Copy the code

One-to-many nested queries

introduce

Requirement: Query all users, and query the orders that the user has at the same time

One-to-many query statement

-- Query the user first
SELECT * FROM `user`; 

-- Query the order list based on the user id primary key
SELECT * FROM orders where uid = # {user id};
Copy the code

Code implementation

A) UserMapper interface

public interface UserMapper { 

    public List<User> findAllWithOrder(a); 
}
Copy the code

B) Usermapper.xml mapping

<! -- One-to-many nested query --> 
<resultMap id="userMap" type="user"> 
    <id column="id" property="id"></id> 
    <result column="username" property="username"></result> 
    <result column="birthday" property="birthday"></result> 
    <result column="sex" property="sex"></result> 
    <result column="address" property="address"></result> 
    <! Select * from user where user id = 0; 
    <collection property="orderList" column="id" ofType="order" select="com.lagou.mapper.OrderMapper.findByUid"></collection> 
</resultMap> 

<select id="findAllWithOrder" resultMap="userMap"> 
    SELECT * FROM `user` 
</select>
Copy the code

C) OrderMapper interface

public interface OrderMapper { 

    public List<Order> findByUid(Integer uid); 
}
Copy the code

OrderMapper. XML mapping

<select id="findByUid" parameterType="int" resultType="order"> 
    SELECT * FROM orders where uid = #{uid} 
</select>
Copy the code

E) Test code

@Test 
public void testUserWithOrder(a) throws Exception { 
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class); 
    
    List<User> list = userMapper.findAllWithOrder();
    
    for(User user : list) { System.out.println(user); }}Copy the code

Many-to-many nested queries

introduce

Requirement: Query all roles of the user

Many-to-many query statement

-- Query the user first
SELECT * FROM `user`; 

-- Query the role list based on the primary key of the user ID
SELECT * FROM role r INNER JOIN user_role ur ON r.`id` = ur.`rid` 
    WHERE ur.`uid` = # {user id};
Copy the code

Code implementation

A) UserMapper interface

public interface UserMapper { 

    public List<User> findAllWithRole(a); 
}
Copy the code

B) Usermapper.xml mapping

<! -- Many-to-many nested query --> 
<resultMap id="userAndRoleMap" type="user"> 
    <id column="id" property="id"></id> 
    <result column="username" property="username"></result> 
    <result column="birthday" property="birthday"></result> 
    <result column="sex" property="sex"></result> 
    <result column="adress" property="address"></result>
    <! Select * from role list by user ID 
    <collection property="roleList" column="id" ofType="role" select="com.lagou.mapper.RoleMapper.findByUid"></collection> 
</resultMap> 

<select id="findAllWithRole" resultMap="userAndRoleMap">
    SELECT * FROM `user`
</select>
Copy the code

C) RoleMapper interface

public interface RoleMapper { 

    public List<Role> findByUid(Integer uid); 
}
Copy the code

D) Rolemapper.xml mapping

<select id="findByUid" parameterType="int" resultType="role">SELECT r.id,r.`role_name` roleName,r.`role_desc` roleDesc FROM role r INNER JOIN user_role ur ON r.`id` = ur.`rid` WHERE  ur.`uid` = #{uid}</select>
Copy the code

E) Test code

@Test 
public void testUserWithRole(a) throws Exception { 
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class); 
    
    List<User> list = userMapper.findAllWithRole(); 
    
    for(User user : list) { System.out.println(user); }}Copy the code

summary

One-to-one configuration: Use <resultMap>+<association> for configuration, and run the select command to query the one-to-many configuration based on the column condition. Use the <resultMap>+<collection> command to query many-to-many configurations based on the column condition. Use the <resultMap>+<collection> command to query many-to-many configurations based on the column condition. Disadvantages of simplified multi-table query operations: Execute multiple SQL statements, wasting database performanceCopy the code

Mybatis- Complex mapping & Config file depth

Mybatis advanced query

ResutlMap properties

Multi-condition query (three types)

1) Method 1

2) Method two

3) Method 3 (recommended)

Fuzzy query

1) Method 1

2) Method two

3) ${} differs from #{}

Mybatis mapping file in depth

Returns the primary key

useGeneratedKeys

selectKey

The test code

Dynamic SQL

Dynamic SQL <if>

A) UserMapper interface

B) Usermapper.xml mapping

C) Test code

Dynamic SQL <set>

A) UserMapper interface

B) Usermapper.xml mapping

C) Test code

Dynamic SQL <foreach>

A) set

B) array

SQL fragment

Knowledge summary

Mybatis core profile in depth

The plugins TAB

① Import common PageHelper coordinates

Configure PageHelper plugin in mybatis core configuration file

③ Test the implementation of paging code

Get additional parameters related to paging

Knowledge summary

Mybatis multi-table query

This section describes database table relationships

Relational database table relationships are divided into

For example,

Case Environment Preparation

One to one (many to one)

introduce

Code implementation

1) Order entity

2) OrderMapper interface

3) OrderMapper.xml mapping

4) Test code

More than a pair of

introduce

Code implementation

1) User entity

2) UserMapper interface

3) Usermapper.xml mapping

4) Test code

Many to many

introduce

Code implementation

1) User and Role entities

2) UserMapper interface

3) Usermapper.xml mapping

4) Test code

summary

MyBatis multi-table configuration mode

MyBatis nested query

What is a nested query

Take a chestnut

One-to-one nested queries

introduce

One-to-one query statement

Code implementation

1) OrderMapper interface

2) OrderMapper.xml mapping

3) UserMapper interface

4) Usermapper.xml mapping

5) Test code

One-to-many nested queries

introduce

One-to-many query statement