1. Optimizing code

Let's start with a little optimization of yesterday's code. Test methods written in test classes. If we write one test method at a time, we need to build factories or something, and there will be a lot of repetitive code.
Modify the test method Mybatis Test

public class MybatisTest {

    private InputStream in;
    private SqlSession session;
    private UserDao userDao;
    //Execution before test method execution
    @Before
    public void before() throws IOException {
        //1. Get factory drawings (configuration information)
        in = Resources.getResourceAsStream("SqlMapConfig.xml");
        //2. Recruitment of workers (founder)
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        //3. Build factories for workers'drawings (builders build factory objects)
        SqlSessionFactory factory = builder.build(in);
        //4. Build a product pipeline (open automatic commit transaction)
        session = factory.openSession(true);
        //5. Produce products according to product design drawings (dynamic agent)
        userDao = session.getMapper(UserDao.class);
    }
    //Execution after test method execution
    @After
    public void after() throws IOException {
        //7. Recycling (Releasing Resources)
        session.close();
        in.close();
    }

    @Test
    public void findAll() throws IOException {
        //6. Use products
        List<User> users = userDao.findAll();
        for (User user :
                users) {
            System.out.println(user);
        }
    }
}

Modify SqlMapConfig.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <!--Loading external resources-->
    <properties resource="druid.properties"/>
    <!--Alias-->
    <typeAliases>
        <!--Individual settings-->
        <!--<typeAlias  alias="user" type="com.lois.domain.User"/>-->
        <!--Batch settings, scan the entire package of classes, alias class names (capitals or lowercases can be used)-->
        <package name="com.lois.domain"/>
    </typeAliases>
    <environments default="mysql">
        <!--To configure mysql Environmental Science-->
        <environment id="mysql">
            <!--Configure transaction types-->
            <transactionManager type="JDBC"></transactionManager>
            <!--Configure the data source (connection pool)-->
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>

    <!--Specify mapping file-->
    <mappers>
        <package name="com.lois.dao"/>
    </mappers>

</configuration>

Run the method again to see if it succeeded.

2. Addition, deletion, examination and modification

1. Modify UserDao interface

public interface UserDao {
    /**
     * Query all users
     * @return
     */
    public List<User> findAll();

    /**
     * Query users according to id
     * @param id
     * @return
     */
    public User findById(int id);

    /**
     * Save users
     * @param user
     */
    public void save(User user);

    /**
     * Modify Users
     * @param user
     */
    public void update(User user);

    /**
     * Delete users according to id
     * @param id
     */
    public void deleteById(int id);

}

1. Modify the Dao mapping file UserDao.xml

<mapper namespace="com.lois.dao.UserDao">
    <select id="findAll" resultType="user" >
        select * from user;
    </select>

    <select id="findById" parameterType="int" resultType="user">
        select * from user where id = #{uid}
    </select>

    <insert id="save" parameterType="user">
        insert into user(name,age) values(#{name},#{age})
    </insert>

    <update id="update" parameterType="user">
        update user set name = #{name},age = #{age} where id = #{id}
    </update>

    <delete id="deleteById" parameterType="int" >
        delete from user where id = #{uid}
    </delete>
</mapper>

1. Add a method to the test class MybatisTest for testing

@Test
    public void findById(){
        //6. Use products
        User user = userDao.findById(1);
        System.out.println(user);
    }

    @Test
    public void save(){
        User user = new User();
        user.setName("Small chapter");
        user.setAge(33);
        userDao.save(user);
        //View database table data changes
    }

    @Test
    public void update(){
        //User Age Modified to id 3
        User user = userDao.findById(3);
        System.out.println(user.getAge());
        user.setAge(100);
        userDao.update(user);
        //View the user's age with id 3 in the database table and the number printed in the console
    }

    @Test
    public void deleteById(){
        //Delete users with id 3
        userDao.deleteById(3);
    }

1. Test each test method and observe data changes in database tables

Note: In fact, except select does not require transaction submission, all other operations require transaction submission. If you do not set true in factory.openSession(true), that is to say, if you do not open the automatic commit transaction, you can also see that the above test method has been successfully implemented, but it has no practical effect. If you do not open the automatic commit transaction, you need to submit the transaction session.commit() manually after each method.

3. Fuzzy Query

Let's talk about fuzzy queries, and by the way, look at the differences between {} and ${} in the Dao mapping file.

1. Add two methods to the UserDao interface, one for #{} and one for ${}

/**
     * Fuzzy Query #{}
     * @param name
     * @return
     */
    public List<User> findLikeName1(String name);

    /**
     * Fuzzy Query ${}
     * @param name
     * @return
     */
    public List<User> findLikeName2(String name);

1. Add the following mapping to the UserDao.xml Mapping File

	<select id="findLikeName1" parameterType="string" resultType="user">
        select * from user where name like #{name}
    </select>

    <select id="findLikeName2" parameterType="string" resultType="user">
        select * from user where name like '%${value}%'
    </select>

1. Add two test methods to the test class

	@Test
    public void findLikeName1(){
        System.out.println(userDao.findLikeName1("%Small%"));
    }
    @Test
    public void findLikeName2(){
        System.out.println(userDao.findLikeName2("Small"));
    }

I don't know if you found out that using {} requires splicing in the test method, while using ${} does not, because {} cannot be used in'.

1. Comparison of test results
   findLikeName1
   
   findLikeName2
   

CONCLUSION: We can know from the output sql statements that using {} is a preloading method, while ${} is just string splicing, so using {} is better to prevent sql injection.

4. Dynamic SQL statements

On the above we write some very simple SQL statements, but in practice, we will also sentence very complex SQL statements, such as dynamic changes in the SQL statement, so let's explain how mybatis implements dynamic SQL statements.

4.1 < if > label

• test attributes (mandatory): Judgment criteria, where & &, | |, and, or can not be used instead

1. Adding methods to the UserDao interface

    /**
     * Dynamic sql-if
     * @param user
     * @return
     */
    public List<User> findByUser1(User user);

1. Want to add mappings to the UserDao.xml Mapping File

	<select id="findByUser1" parameterType="user" resultType="user">
        select * from user where 1=1
        <if test="name != null and name != '' ">
            and name = #{name}
        </if>
        <if test="age != null and age != 0 ">
            and age = #{age}
        </if>
    </select>

Note: where 1=1 is to be able to concatenate strings, so that the following if is not valid, the sql statement is still correct

1. Add a data to the database table, modify the save test method, and run it

@Test
    public void save(){
        User user = new User();
        user.setName("Xiao Ming");
        user.setAge(32);
        userDao.save(user);
        //View database table data changes
    }

1. Add test methods to test classes, run test methods

@Test
    public void findByUser(){
        User user = new User();
        user.setName("Xiao Ming");
        //user.setAge(22);
        System.out.println(userDao.findByUser1(user));
    }

1. Release comments and run again
2. Contrast results
   
   
   Discovering that the spliced strings are different

4.2 < where > label

The < where > tag can give mybatis a judgment as to whether or not to add where, so that you don't have to write where 1=1.

1. Modify the mapping just in the mapping file

<select id="findByUser1" parameterType="user" resultType="user">
        select * from user
        <where>
            <if test="name != null and name != '' ">
                and name = #{name}
            </if>
            <if test="age != null and age != 0 ">
                and age = #{age}
            </if>
        </where>
    </select>

1. Modify test methods in test classes and run test methods

@Test
    public void findByUser(){
        User user = new User();
        //user.setName("Xiaoming");
        //user.setAge(22);
        System.out.println(userDao.findByUser1(user));
    }

1. Release the comments in turn and run the test method again
   
   
   

4.3 < foreach > tag

When we do range queries, we add values in a set dynamically as parameters. So how do we pass parameters? The < foreach > tag is used at this time.
Label Attributes Introduction:

• Collection: collection to traverse
• open: The beginning of a mosaic string
• close: the end of a concatenated string
• item: A single data alias extracted from a collection
• separator: What conformance separation is used between each item
• The content of the < foreach > tag is traversal data between open and close

1. Create a QueryVo class to store List collection parameters

public class QueryVo implements Serializable {
    private List<Integer> ages;

    public List<Integer> getAges() {
        return ages;
    }

    public void setAges(List<Integer> ages) {
        this.ages = ages;
    }
}

1. Adding methods to the UserDao interface

public List<User> findByUserInAge(QueryVo queryVo);

1. Add mappings to UserDao.xml mappings

	<select id="findByUserInAge" resultType="user" parameterType="QueryVo">
        select * from user
        <where>
            <if test="ages != null and ages.size() > 0">
                <foreach collection="ages" open="age in ( " close=")" item="age" separator=",">
                    #{age}
                </foreach>
            </if>
        </where>
    </select>

1. Add test methods to test classes, run test methods

@Test
    public void findByUserInAge(){
        List<Integer> list = new ArrayList<Integer>();
        list.add(22);
        list.add(32);
        QueryVo queryVo = new QueryVo();
        queryVo.setAges(list);
        System.out.println(userDao.findByUserInAge(queryVo));
    }

1. Analysis of operation results
   
   You can see that two data are traversed and added after the sql statement

5 multi-table query

5.1 Preparations

1. Create subproject day2
2. Copy all the resources in day1
3. Delete UserDao.xml, UserDao interface class and QueryVo entity class
4. Delete the third record in the database table user (so that there are only two data in the user table with IDs of 1 and 2)
5. Execute sql script to create table account

use mybatistest;
drop table if exists `account`;
create table `account`(
	`id` int(10) primary key auto_increment,
	`uid` int(10),
	`money` double
);
insert into account(uid,money) values (1,2000),(2,3000),(1,3200);

drop table if exists `role`;
create table `role`(
	`id` int(10) primary key auto_increment,
	`name` varchar(30),
	`desc` varchar(60)
);
insert into `role`(`name`,`desc`) values ('Chairman','The company's top leader'),('General manager','Company Manager'),('Finance Minister','Managing Company Finance');

drop table if exists `user_role`;
create table `user_role`(
	`uid` int(10),
	`rid` int(10)
);
insert into `user_role` values (1,1),(1,2),(2,2),(2,3);

5.2 One-to-one (many-to-one)

5.2.1 Mode 1

1. Create the com.lois.domain.Account entity class and generate get, set, tostring methods

public class Account implements Serializable {
    private Integer id;
    private Integer uid;
    private Double money;
}

1. Writing sql statements

select account.*,user.name,user.age from user,account where user.id = account.uid;

1. According to the figure above, we need to write the entity class com.lois.domain.AccountUser. Note: This class inherits the Account class.

public class AccountUser extends Account implements Serializable {
    private String name;
    private Integer age;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    @Override
    public String toString() {
        return super.toString()+"AccountUser{" +
                "name='" + name + '\'' +
                ", age=" + age +
                '}';
    }
}

1. Write the com.lois.dao.AccountDao interface class

public interface AccountDao {
    /**
     * Search all accounts and get the owner and age of the account at the same time
     * @return
     */
    public List<AccountUser> findAll();
}

1. Write com/lois/dao/AccountDao.xml mapping file

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.lois.dao.AccountDao">
    <select id="findAll" resultType="accountuser">
        select account.*,user.name,user.age from user,account where user.id = account.uid;
    </select>
</mapper>

1. Modify the test class com.lois.test.MybatisTest

public class MybatisTest {

    private InputStream in;
    private SqlSession session;
    private AccountDao accountDao;
    //Execution before test method execution
    @Before
    public void before() throws IOException {
        //1. Get factory drawings (configuration information)
        in = Resources.getResourceAsStream("SqlMapConfig.xml");
        //2. Recruitment of workers (founder)
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        //3. Build factories for workers'drawings (builders build factory objects)
        SqlSessionFactory factory = builder.build(in);
        //4. Build a product pipeline (open automatic commit transaction)
        session = factory.openSession(true);
        //5. Produce products according to product design drawings (dynamic agent)
        accountDao = session.getMapper(AccountDao.class);
    }
    //Execution after test method execution
    @After
    public void after() throws IOException {
        //7. Recycling (Releasing Resources)
        session.close();
        in.close();
    }

    @Test
    public void findAllAccount(){
        System.out.println(accountDao.findAll());
    }
}

1. Running results (3 records)

[Account{id=1, uid=1, money=2000.0}AccountUser{name='Xiao Ming', age=22},
 Account{id=2, uid=2, money=3000.0}AccountUser{name='Xiaohong', age=25},
  Account{id=3, uid=1, money=3200.0}AccountUser{name='Xiao Ming', age=22}]

5.2.2 Mode 2

1. Modify the com.lois.domain.Account entity class to generate get, set, tostring methods

public class Account implements Serializable {
    private Integer id;
    private Integer uid;
    private Double money;

    private User user;
}

1. Modify the return value List < Account > of the AccountDao interface findAll method

public List<Account> findAll();

1. Modify AccountDao.xml Mapping File

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.lois.dao.AccountDao">
    <resultMap id="accountMap" type="account">
        <id column="id" property="id"/>
        <result column="uid" property="uid"/>
        <result column="money" property="money"/>
        <!--Mapping a single object-->
        <association property="user" javaType="user">
            <result column="name" property="name"/>
            <result column="age" property="age"/>
        </association>
    </resultMap>

    <select id="findAll" resultMap="accountMap">
        select account.*,user.name,user.age from user,account where user.id = account.uid;
    </select>
</mapper>

1. Running test

[Account{id=1, uid=1, money=2000.0, user=User{id=null, name='Xiao Ming', age=22, accounts=null}}, 
Account{id=2, uid=2, money=3000.0, user=User{id=null, name='Xiaohong', age=25, accounts=null}}, 
Account{id=3, uid=1, money=3200.0, user=User{id=null, name='Xiao Ming', age=22, accounts=null}}]

5.3 One-to-many

1. Writing sql statements

select user.*,account.* from user left join account on user.id = account.uid;

You can see that there are two IDS in the table, because both user and account have id s, so we need to modify the sql statement (alias)

select user.*,account.id as aid,account.uid,account.money from user left join account on user.id = account.uid;

1. Modify User entity class, add List < Account > accounts to entity class, and regenerate set and get, tostring methods

public class User implements Serializable {
    private Integer id;
    private String name;
    private Integer age;

    private List<Account> accounts;
}

1. Write user persistence layer com.lois.dao.UserDao interface

public interface UserDao {
    /**
     * Query all users and query all account information under users
     * @return
     */
    public List<User> findAll();
}

1. Writing a com/lois/dao/UserDao.xml mapping class requires resultMap because the fields we find do not match the entity class

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.lois.dao.UserDao">
    <resultMap id="userMap" type="user">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <result column="age" property="age"/>
        <!--collection It is used to establish the corresponding relationship of set attributes in one-to-many
            ofType Data types used to specify collection elements-->
        <collection property="accounts" ofType="account">
            <id column="aid" property="id"/>
            <result column="uid" property="uid"/>
            <result column="money" property="money"/>
        </collection>
    </resultMap>

    <select id="findAll" resultMap="userMap">
        select user.*,account.id as aid,account.uid,account.money from user left join account on user.id = account.uid;
    </select>
</mapper>

1. Add a method to the test class com.lois.test.MybatisTest, and userDao gets it on its own in the test class

@Test
    public void findAllUser(){
        System.out.println(userDao.findAll());
    }

1. Running test

[User{id=1, name='Xiao Ming', age=22, accounts=[Account{id=1, uid=1, money=2000.0}, Account{id=3, uid=1, money=3200.0}]}, 
User{id=2, name='Xiaohong', age=25, accounts=[Account{id=2, uid=2, money=3000.0}]}]

You can see that two users were identified, with two accounts under one user.

5.4-to-many pairs

We have studied one-to-many in the past. Many-to-many can actually be regarded as a two-way one-to-many relationship. A user can have multiple roles, and a role can have multiple users

1. Writing sql statements

select role.*,user.name as uname,user.age from role left join user_role on (role.id = user_role.rid) left join user on (user_role.uid = user.id);

1. Write the entity class com.lois.domain.Role to generate set, get, tostring methods

public class Role implements Serializable {
    private Integer id;
    private String name;
    private String desc;

    private List<User> users;
}

1. Write the com.lois.dao.RoleDao interface class

public interface RoleDao {
    /**
     * Query all roles and users with roles
     * @return
     */
    public List<Role> findAll();
}

1. Write a com/lois/dao/RoleDao.xml mapping file, because we use aliases in sql statements, so we need to use resultMap

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.lois.dao.RoleDao">
    <resultMap id="roleMap" type="role">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <result column="desc" property="desc"/>
        <collection property="users" ofType="user">
            <result column="uname" property="name"/>
            <result column="age" property="age"/>
        </collection>
    </resultMap>

    <select id="findAll" resultMap="roleMap">
        select role.*,user.name as uname,user.age from role left join user_role on (role.id = user_role.rid) left join user on (user_role.uid = user.id);
    </select>
</mapper>

1. Add a method to the test class com.lois.test.MybatisTest, which roleDao obtains on its own in the test class

@Test
    public void findAllRole(){
        System.out.println(roleDao.findAll());
    }

1. Running test

[Role{id=1, name='Chairman', desc='The company's top leader', users=[User{id=null, name='Xiao Ming', age=22, accounts=null}]}, 
Role{id=2, name='General manager', desc='Company Manager', users=[User{id=null, name='Xiao Ming', age=22, accounts=null}, User{id=null, name='Xiaohong', age=25, accounts=null}]},
 Role{id=3, name='Finance Minister', desc='Managing Company Finance', users=[User{id=null, name='Xiaohong', age=25, accounts=null}]}]

You can see that the query reveals three roles and two users on the role general manager (which may not be reasonable, but we are also testing).

6. Homework

Just now we said that many-to-many is two-to-many, we have completed one-to-many above, then another "one user-to-many roles" needs to be supplemented by yourself!