MyBatis

1, Framework introduction

1.1 concept of framework

Framework is a semi-finished product of software, which completes the general operation in the process of software development. Programmers can realize specific functions with little or no processing, so as to simplify the development process and improve efficiency.

1.2 common frames

• MVC framework: simplifies the development steps of Servlet

  • Struts2
  • SpringMVC

• Persistence layer framework: a framework for completing database operations

  • apache DBUtils
  • Hibernate
  • Spring JPA
  • MyBatis

• Platform framework: a framework that integrates different functions

  • Spring

​ SSM - Spring SpringMVC MyBatis

​ SSH - Spring Struts2 Hibernate

1.3 introduction to mybatis

MyBatis is a semi-automatic ORM framework:

Semi automatic: because Hibernate is a fully automatic framework

ORM: Object Relational Mapping, which corresponds an object in Java to a row of records in the data table one by one.

ORM framework provides the mapping relationship between entity classes and data tables, and realizes the persistence of objects through the configuration of mapping files.

• MyBatis, formerly known as iBtis, is an open source project maintained by apache foundation
• In 2010, iBatis migrated to (code hosting) Google Code and officially changed its name to MyBatis
• In 2013, MyBatis migrated to github
• MyBatis features:
  • Support custom SQL and stored procedures
  • The original JDBC is encapsulated, almost all JDBC code is eliminated (open connection, load and execute SQL...), and developers only need to pay attention to SQL
  • Support XML and annotation configuration to automatically complete ORM operation and realize result mapping

2, MyBatis framework deployment

Framework deployment is to introduce the framework into the project

2.1 create Maven project

• Java Engineering
• Web Engineering

2.2 add MyBatis dependency

• In POM Add dependencies to XML. Both of the following dependencies are required
  • Mysql Driver
  • MyBatis

<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.47</version>
</dependency>

<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.4.6</version>
</dependency>

2.3 create MyBatis configuration file

There is a resources folder under the main folder of maven project. Under this folder, create a new mybatis configuration file. You can add the mybatis profile template first.

<configuration>
    <!-- stay environments Configuration database connection information in -->
    <!-- stay environments Multiple can be defined in a label environment Label, each environment Tags can define a set of connection configurations -->
    <!-- default Property to specify which set of configurations to use -->
    <environments default="development">
        <environment id="development">

            <!-- transactionManager Label is used to configure how the database is managed -->
            <transactionManager type="JDBC"></transactionManager>

            <!-- dataSource Tags are used to configure database connection information -->
            <dataSource type="POOLED">
                <property name="driver" value="${database.driver}"/>
                <property name="url" value="${database.url}"/>
                <property name="username" value="${database.username}"/>
                <property name="password" value="${database.password}"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource=""></mapper>
    </mappers>
</configuration>

3, MyBatis framework uses

Case: student information database operation

3.1 create data table

create table tb_students(
	sid int primary key auto_increment,
	stu_num char(5) not null UNIQUE,
	stu_name VARCHAR(20) not null,
	stu_gender char(2) not null,
	stu_age int not null
)

3.2 creating entity classes

@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class Student {
    private int stuId;
    private String stuNum;
    private String stuName;
    private String stuGender;
    private String stuAge;
}

3.3 create DAO interface and define method

public interface StudentDAO {
    public int insertStudent(Student s);
    public int deleteStudent(String stuNum);
}

3.4 create interface mapping file

• Create a new folder named mappers in the resources directory
• In mappers, create a new one named studentmapper XML configuration file, according to the template
• Implement the methods defined in DAO in the mapping file

<!--Mapping file to correspond to mapper Interface pass namespace Property-->
<mapper namespace="com.gsjt.dao.StudentDAO">

    <!--id by mapper The corresponding method name in the class, resultType Is the defined receiving type, which is generally the corresponding entity class-->
    <select id="" resultType="">

    </select>

    <!--id by mapper Method name in class-->
    <insert id="insertStudent">
        insert into tb_students(stu_num, stu_name, stu_gender, stu_age)
        values (#{stuNum}, #{stuName}, #stuGender, #{stuAge})
    </insert>

    <delete id="deleteStudent">
        delete from tb_students where stu_name = #{stuNum}
    </delete>

</mapper>

3.5 add mapping file to main configuration file

<mappers>
    <mapper resource="mappers/StudentMapper.xml"></mapper>
</mappers>

4, Unit test

public class StudentDAOTest {
    @Test
    public void insertStudent() {

        try {
            // Load mybatis configuration file
            InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
            SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();

            // Session (connection) factory, the parameter is connection information
            SqlSessionFactory factory = builder.build(is);
            SqlSession sqlSession = factory.openSession();
            StudentDAO studentDAO = sqlSession.getMapper(StudentDAO.class);
            System.out.println(studentDAO);

            // Insertion method
            int i = studentDAO.insertStudent(new Student(1, "10002", "Li Si", "male", 22));

            // Manually committed transactions
            sqlSession.commit();
            System.out.println("The result returned by the insert operation, " + i);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    @Test
    public void deleteStudent() {
    }
}

5, Addition, deletion and modification of MyBatis

Case: addition, deletion, modification and query of student information

5.1 add

It has been demonstrated in the previous section

5.2 deletion

Delete according to student number

• Define the deletion method in StudentDAO

• In studentmapper The interface method is "implemented" in XML, and the deldete tag is used

• Write test code in test class

  	@Test
      public void deleteStudent() {
          try {
              InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
              SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
              SqlSessionFactory factory = builder.build(in);
              SqlSession session = factory.openSession();
  
              StudentDAO studentDAO = session.getMapper(StudentDAO.class);
  
              int i = studentDAO.deleteStudent("10002");
              session.commit();
  
              System.out.println("Result of delete operation, " + i);
  
          }   catch (Exception e) {
              e.printStackTrace();
          }
      }
  

5.3 modification

Modify other field information according to student number (primary key)

• Add interface method

  public int updateStudent(Student s);
  

• Add mapper mapping

  	<update id="updateStudent">
          update tb_students set
              stu_name = #{stuName},
              stu_gender = #{stuGender},
              stu_age = #{stuAge}
          where
              stu_num = #{stuNum}
      </update>
  

• test method

  	@Test
      public void testUpdateStudent() {
          try {
              InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
              SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
              SqlSessionFactory factory = builder.build(in);
              SqlSession session = factory.openSession();
              StudentDAO studentDAO = session.getMapper(StudentDAO.class);
              int i = studentDAO.updateStudent(new Student(0, "10001", "Zhao Liu", "female", 26));
              session.commit();
              assertEquals(1, i);
          }   catch (Exception e) {
              e.printStackTrace();
          }
      }
  

5.4 query all

Note that there are two ways to write the mapping relationship between the query results and ordinary Java classes.

Note: no transaction is required for query. It doesn't matter whether it is written or not.

• Alias the found fields and use the specified class name

  	<!-- resultType Specifies the entity class that returns the result encapsulation -->
      <!-- resultSets Specifies the collection type of the current operation. The method return value in the interface can be omitted in some cases -->
      <select id="searchAllStudents" resultType="com.gsjt.pojo.Student">
          select sid stuId, stu_num stuNum, stu_name stuName, stu_gender stuGender, stu_age stuAge
          from tb_students;
      </select>
  

• It is recommended to use resultMap, which can be reused.

  	<!-- Used to define ORM -->
      <resultMap id="studentMap" type="com.gsjt.pojo.Student">
          <id column="sid" property="stuId"/>
          <id column="stu_num" property="stuNum"/>
          <id column="stu_name" property="stuName"/>
          <id column="stu_gender" property="stuGender"/>
          <id column="stu_age" property="stuAge"/>
      </resultMap>
  
      <!-- resultMap Used to reference an entity mapping relationship -->
      <select id="searchAllStudents" resultMap="studentMap">
          select sid, stu_num, stu_name, stu_gender, stu_age
          from tb_students;
      </select>
  

5.5 query a single record

	<select id="queryStudent" resultMap="studentMap">
        select sid, stu_num, stu_name, stu_gender, stu_age
        from tb_students
        where stu_num = #{param}
    </select>

	@Test
    public void testQueryStudent() {
        try {
            InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
            SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
            SqlSessionFactory factory = builder.build(in);
            SqlSession session = factory.openSession();
            StudentDAO studentDAO = session.getMapper(StudentDAO.class);
            Student s = studentDAO.queryStudent("10001");
            assertNotNull(s);
            System.out.println(s.toString());
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

5.6 multi parameter query

Note: when MyBatis performs conditional query,

1. The operation method has only one parameter of simple type or substring type, which can be obtained directly through #{} any parameter during mapper configuration;

2. If the operation method has an object type parameter, you can directly use #{attrname} to obtain the value of the attribute during mapper configuration (attrname must be the attribute of the parameter object);

3. If the operation method has a map type parameter, you can directly obtain the value of the corresponding key through #{key} in Mapper configuration;

4. The operation method has multiple parameters. Use the @ Param annotation to specify the alias

Note: if the DAO operation method does not specify the parameter alias through @ param, you can use arg0,arg1 Or param1, param2 To get parameters.

	public List<Student> listStudentsByPage(@Param("start") int start,
                                            @Param("pageSize") int pageSize);

	<select id="listStudentsByPage" resultMap="studentMap">
        select sid, stu_num, stu_name, stu_gender, stu_age
        from tb_students
        limit #{start}, #{pageSize}
    </select>

	@Test
    public void testListStudentByPage() {
        try {
            InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
            SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
            SqlSessionFactory factory = builder.build(in);
            SqlSession session = factory.openSession();
            StudentDAO studentDAO = session.getMapper(StudentDAO.class);
            List<Student> s = studentDAO.listStudentsByPage(0, 10);
            assertNotNull(s);
            s.forEach(item -> System.out.println(item.toString()));
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

5.7 total number of query records

	public int countStudents();

Specify that the current return value type is int through resultMap, otherwise an error will be reported

	<select id="countStudents" resultType="int">
        select count(1) from tb_students
    </select>

5.8 add and backfill automatically generated primary keys

During the addition operation, a new student object is created. When inserting, the stuId should be the self incremented primary key in the database and should be backfilled to this student object.

<!-- useGeneratedKeys Whether backfilling is required, keyProperty Set backfill properties -->
<insert id="insertStudent" useGeneratedKeys="true" keyProperty="stuId">
    insert into tb_students(stu_num, stu_name, stu_gender, stu_age)
    values (#{stuNum}, #{stuName}, #{stuGender}, #{stuAge})
</insert>

• Whether useGeneratedKeys needs backfilling
• keyProperty sets the backfill property

6, mybatis tool class encapsulation

public class MyBatisUtil {

    private static SqlSessionFactory factory;
    private static final ThreadLocal<SqlSession> local = new ThreadLocal<SqlSession>();

    static {
        try {
            InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
            factory = new SqlSessionFactoryBuilder().build(in);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static SqlSession getSqlSession() {
        SqlSession sqlSession = local.get();
        if (sqlSession == null) {
            sqlSession = factory.openSession();
            local.set(sqlSession);
        }
        return factory.openSession();
    }

    public static <T extends Object>T getMapper(Class<T> c) {
        SqlSession sqlSession = getSqlSession();
        return sqlSession.getMapper(c);
    }
}

7, Transaction management

SqlSession object

• getMapper(DAO.class): get Mapper (instance of DAO interface)
• Transaction management: provides transaction management methods

7.1 manually commit transactions

 	@Test
    public void deleteStudent() {
        SqlSession session = MyBatisUtil.getSqlSession();
        try {
            StudentDAO studentDAO = session.getMapper(StudentDAO.class);
            int i = studentDAO.deleteStudent("10002");
            session.commit();      // Submit
            System.out.println("Result of delete operation, " + i);
        }   catch (Exception e) {
            session.rollback();     // RollBACK 
            e.printStackTrace();
        }
    }

7.2 auto commit transactions

​ factory. The opensession () method can pass a Boolean parameter to indicate whether to automatically commit the transaction. The default is false.

However, if there are multiple database operations in the program at this time, if automatic submission is used, it will be submitted automatically after the first operation is completed. Manual management is recommended at this time.

8, MyBatis master profile

​ mybatis-config.xml is the main MyBatis configuration file

Note: the tags in the configuration file must be in order, in the following order: properties, settings, typealiases, typehandlers, objectfactory, objectwrapperfactory, plugins, environments, databaseidprovider and mappers

8.1 properties tab

Used to set key value pairs or load property files< property name ="" value=""/>

• Create JDBC. Net under the resources folder In the properties file, the key value pairs are configured as follows:

  mysql_driver = com.mysql.jdbc.Driver
  mysql_url = jdbc:mysql://localhost:3306/testdb?characterEncoding=utf-8
  mysql_username = root
  mysql_password = 123456
  

• The main configuration file is as follows. After the properties tag is introduced, the data can be retrieved according to the key value pair

  <configuration>
      <properties resource="jdbc.properties"></properties>
      <environments default="development">
          <environment id="development">
              <transactionManager type="JDBC"></transactionManager>
              <dataSource type="POOLED">
                  <property name="driver" value="${mysql_driver}"/>
                  <property name="url" value="${mysql_url}"/>
                  <property name="username" value="${mysql_username}"/>
                  <property name="password" value="${mysql_password}"/>
              </dataSource>
          </environment>
      </environments>
      <mappers>
          <mapper resource=""></mapper>
      </mappers>
  </configuration>
  

8.2 settings tab

Set some properties when mybatis works

<settings>
    <setting name="cacheEnable" value="true"/>    <!-- L2 cache -->
    <setting name="lazyLoadingEnable" value="true"/>    <!-- Delayed loading -->
</settings>

8.3 typeAlias label

<!-- It is used to alias the entity class. In the mapping file, you can directly use the alias to replace the fully qualified name of the entity class -->
<typeAliases>
    <typeAlias type="com.gsjt.pojo.Student" alias="Student"></typeAlias>
</typeAliases>

8.4 plugins tab

Used to configure mybatis plug-ins, such as paging plug-ins.

8.5 environment label

Configure database connection information

	<environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="${mysql_driver}"/>
                <property name="url" value="${mysql_url}"/>
                <property name="username" value="${mysql_username}"/>
                <property name="password" value="${mysql_password}"/>
            </dataSource>
        </environment>
    </environments>

• The environment tab is used to configure a separate database connection information
  • The type attribute of the transactionManager has two optional values:
    • JDBC: use JDBC to manage transactions in your code
    • MANAGED: the transaction is MANAGED by the container, and the connection is not handled manually
  • dataSource tag, configuration database connection information, several values of type attribute:
    • POOLED
    • UNPOOLED
    • JNDI

8.6 mappers label

Used to load the mapping configuration in the project (mapping file, DAO annotation)

	<mappers>
        <mapper resource="mappers/StudentMapper.xml"></mapper>
    </mappers>	

9, mapper profile

9.1 initialization process of mybatis mapper file

9.2 Mapper file specification

• The root label of the mapper file must be mapper. Mapper has an attribute namespace to specify which interface to implement

• insert declaration add operation

  Common attributes:

  • id, binding interface method name
  • parameterType, used to specify the parameter type of the corresponding method in the interface (can be omitted)
  • useGeneratedKeys, whether the primary key backfill is required for the add operation
  • keyProperty, which specifies the property of the object to which the backfilled primary key is bound
  • Timeout, set the timeout of this operation. If it is not set, it will wait all the time

Another way to backfill the primary key: use the selectKey tag.

• Delete statement delete

• update modification

• select query operation, the operation with the most attributes

  Common attributes:

  • resultType: Specifies the return type of the current sql execution
  • resultMap, which specifies the correspondence between the data table and the entity class
  • useCache, whether to enable L2 cache
  • id
  • timeout
  • parameterType

• resultMap specifies the field mapping relationship

  <!-- Used to define ORM -->
  <resultMap id="studentMap" type="com.gsjt.pojo.Student">
      <id column="sid" property="stuId"/>
      <id column="stu_num" property="stuNum"/>
      <id column="stu_name" property="stuName"/>
      <id column="stu_gender" property="stuGender"/>
      <id column="stu_age" property="stuAge"/>
  </resultMap>
  

• Cache specifies the cache attribute configuration

  <cache type="" size="" readOnly="false"/>
  

  See cache section for cache configuration

• sql and include tags are equivalent to taking. The sql tag defines the sql statement fragment, and include introduces this fragment.

  <sql id="test">sid, stu_name, stu_gender</sql>
  
  <select id="" resultMap ="">
      select <include refid="test"> from tb_students
  </select>
  

10, Paging plug-in

The paging plug-in is a third-party plug-in PageHelper independent of the MyBatis framework

10.1 add paging plug-in dependency

<!-- https://mvnrepository.com/artifact/com.github.pagehelper/pagehelper -->
<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper</artifactId>
    <version>5.2.0</version>
</dependency>

10.2 configuration asking price

Configure in the main configuration file of mybatis through the plugins tag

<plugins>
    <plugin interceptor="com.github.pagehelper.PageInterceptor"></plugin>
</plugins>

10.3 use

	@Test
    public void testPageHelper() {
        StudentDAO studentDAO = MyBatisUtil.getMapper(StudentDAO.class);

        PageHelper.startPage(2, 4);
        
        List<Student> students = studentDAO.listByPageHelper();
        
        PageInfo<Student> pageInfo = new PageInfo<Student>(students);
        
        students.forEach(item -> System.out.println(item.toString()));
        System.out.println("--------------------------------------------------");
        pageInfo.getList().forEach(item -> System.out.println(item.toString()));
    }

11, Association mapping

11.1 entity relationship

Entity relationship - data entity, entity relationship knows the relationship between data and data

For example: users and roles, houses and vulnerabilities, orders and goods

There are four types of entity relationships:

• One to one: person and ID card, student and student number, user basic information and user details

  Data table relationship: primary key Association (user table primary key and detail primary key are the same, indicating matching data)

• One to many: classes and students

• Many to one: students and classes

  Data table relationship: add a foreign key at the multiple end and a primary key Association at the one end

• Many to many: users and roles, students and communities

  Data table relationship: establish the relationship table in Chapter 3 to be associated with the primary keys of the original two tables

11.2 create a web project and introduce mybatis

11.3 one to one relationship

Example: user - details

11.3. 1 data sheet

-- User information table
create table users(
	user_id int primary key auto_increment,
    user_name varchar(20) not null unique,
    user_pwd varchar(20) not null,
    user_realname varchar(20) not null,
    user_img varchar(100) not null
);
-- User details table
create table details(
	detail_id int primary key auto_increment,
    user_addr varchar(50) not null,
    user_tel char(11) not null,
    user_desc varchar(200),
    uid int not null unique,
    -- constraint users foreign key(uid) reference users(user_id)
);

11.3. 2 create entity class

/* User.java */
@Data
@ToString
@NoArgsConstructor
@AllArgsConstructor
public class User {
    private int userId;
    private String userName;
    private String userPwd;
    private String userRealName;
    private String userImg;

    // Internal object
    private Detail detail;
}

/* Detail.java */
@Data
@ToString
@NoArgsConstructor
@AllArgsConstructor
public class Detail {
    private int detailId;
    private String userAddr;
    private String userTel;
    private String userDesc;
    private int userId;
}

11.3. 3 insert

You need to ensure atomicity when inserting. When inserting the users table, you also need to insert the details table. And use foreign keys logically. The primary key backfill is used when the users table needs to be inserted.

<sql id="allAttrs">
    user_name, user_pwd, user_realname, user_img
</sql>

<insert id="insertUser" useGeneratedKeys="true" keyProperty="userId">
    insert into users(<include refid="allAttrs"/>)
    values (#{userName}, #{userPwd}, #{userRealName}, #{userImg})
</insert>

<!-- detail of mapper -->
<sql id="allAttrs">
    user_addr, user_tel, user_desc, user_id
</sql>

<insert id="insertDetail">
    insert into details(<include refid="allAttrs"/>)
    values (#{userAddr}, #{userTel}, #{userDesc}, #{userId})
</insert>

@Test
public void testUserInsert() {
    User u = new User(0, "nickname", "wadwa", "Zhao Liu", "01.jpg", null);
    Detail d = new Detail(0, "Wuhan", "18600011125", "This is a signature", 0);
    SqlSession sqlSession = MyBatisUtil.getSqlSession();
    try {
        UserDAO userDAO = sqlSession.getMapper(UserDAO.class);
        int i = userDAO.insertUser(u);
        System.out.println("New user insert, " + i);

        d.setUserId(u.getUserId());		// Set logical foreign key
        DetailDAO detailDAO = sqlSession.getMapper(DetailDAO.class);
        int j = detailDAO.insertDetail(d);
        System.out.println("Insert details, " + j);

        sqlSession.commit();	// Commit transaction
    }   catch (Exception e) {
        e.printStackTrace();
        sqlSession.rollback();	// Rollback transaction
    }
}

11.3. 4 one to one association query

The difficulty is that there are two mapping methods for association mapping

• Table join query, mapping internal class attributes directly

  <sql id="allAttrs">
      user_name, user_pwd, user_realname, user_img
  </sql>
  
  <resultMap id="userMap" type="com.gsjt.pojo.User">
      <id column="user_id" property="userId"/>
      <result column="user_name" property="userName"/>
      <result column="user_pwd" property="userPwd"/>
      <result column="user_realname" property="userRealName"/>
      <result column="user_img" property="userImg"/>
      <result column="detail_id" property="detail.detailId"/>
      <result column="user_addr" property="detail.userAddr"/>
      <result column="user_tel" property="detail.userTel"/>
      <result column="user_desc" property="detail.userDesc"/>
      <result column="user_id" property="detail.userId"/>
  </resultMap>
  
  <select id="queryUser" resultMap="userMap">
      select u.user_id, <include refid="allAttrs"/>, detail_id, user_addr, user_tel, user_desc
      from users u
      inner join details d
      on u.user_id = d.user_id
      where u.user_name = #{username};
  </select>
  

• Use subquery

  • Use query in another table

    <sql id="allAttrs">
        user_addr, user_tel, user_desc, user_id
    </sql>
    
    <resultMap id="detailMap" type="com.gsjt.pojo.Detail">
        <id column="detail_id" property="detailId"/>
        <result column="user_addr" property="userAddr"/>
        <result column="user_tel" property="userTel"/>
        <result column="user_desc" property="userDesc"/>
        <result column="user_id" property="userId"/>
    </resultMap>
    
    <select id="queryDetailByUid" resultMap="detailMap">
        select detail_id, <include refid="allAttrs"/>
        from details
        where user_id = #{userId}
    </select>
    

  • Sub queries are used in the main table with the help of the assignment tag

    <resultMap id="userMapUseSubQuery" type="com.gsjt.pojo.User">
        <id column="user_id" property="userId"/>
        <result column="user_name" property="userName"/>
        <result column="user_pwd" property="userPwd"/>
        <result column="user_realname" property="userRealName"/>
        <result column="user_img" property="userImg"/>
        <association property="detail" select="com.gsjt.dao.DetailDAO.queryDetailByUid" column="user_id"/>
    </resultMap>
    
    <select id="queryUserUseSubQuery" resultMap="userMapUseSubQuery">
        select user_id, <include refid="allAttrs"/>
               from users
        where user_name = #{username}
    </select>
    

11.4 one to many Association

Case: class information (1) - student information (n) 11.4.1

11.4. 1 create table

create table classes(
	cid int primary key auto_increment,
	cname varchar(20) not null unique,
	cdesc varchar(100)
);

create table students(
	sid char(5) primary key,
	sname varchar(20) not null,
	sage int not null,
	scid int not null
);

11.4. 2 entity class

@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Clazz {
    private int classId;
    private String className;
    private String classDesc;

    private List<Student> stus;
}

@Data
@ToString
@NoArgsConstructor
@AllArgsConstructor
public class Student {
    private String studentId;
    private String stuName;
    private int stuAge;
    private int stuCid;
}

11.4. 3 association query

When querying a class, all the students in the class should be queried by association

There are also two implementations

• join query

  <resultMap id="classMap" type="Clazz">
      <id column="cid" property="classId"/>
      <result column="cname" property="className"/>
      <result column="cdesc" property="classDesc"/>
  
      <!-- Clazz Object stus Property is a List Collection, need to use collection label -->
      <!-- collection Tagged property Property indicates which property is bound to the object, ofType Property represents the element type within the collection -->
      <collection property="stus" ofType="Student">
          <result column="sid" property="stuId"/>
          <result column="sname" property="stuName"/>
          <result column="sage" property="stuAge"/>
      </collection>
  </resultMap>
  
  <select id="queryClass" resultMap="classMap">
      select cid, cname, cdesc, sid, sname, sage
      from classes c inner join students s
      on c.cid = s.scid
      where c.cid = #{classId};
  </select>
  

• Subquery

  <resultMap id="classMap" type="Clazz">
      <id column="cid" property="classId"/>
      <result column="cname" property="className"/>
      <result column="cdesc" property="classDesc"/>
  
      <collection property="stus" select="com.gsjt.dao.StudentDAO.listStudentsByCid" column="cid"/></resultMap>
  
  <select id="queryClass" resultMap="classMap">
      select cid, cname, cdesc
      from classes
      where cid = #{classId}
  </select>
  

  At the same time, you should also configure the query interface and mapper in the sub query.

11.5 many to one Association

Example: student (n) - class (1)

When a student is queried, the corresponding class object is queried at the same time.

11.5. 1 entity class

@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Clazz {
    private int classId;
    private String className;
    private String classDesc;
    // private List<Student> stus;
}

@Data
@ToString
@NoArgsConstructor
@AllArgsConstructor
public class Student {
    private String stuId;
    private String stuName;
    private int stuAge;
    // private int stuCid;

    private Clazz clazz;
}

11.5. 2 association query

• Connection query: similar to one-to-one relationship query
• Subquery: similar to one-to-one relationship query

11.6 many to many Association

Case: students and courses

11.6. 1 create data table

You need to create a relationship table in Chapter 3

-- Student list above
-- Class Schedule Card
create table courses(
	cources_id int primary key auto_increment,
    cource_name varchar(20) not null
);

-- Course selection information table/Transcript
create table grades(
	sid char(5) not null,
    cid int not null,
    score int not null
);

11.6. 2 association query

When querying students, you can also query the courses selected by students

When you query the course based on the course number, you can query the students who choose this course at the same time

12, Dynamic SQL

Dating websites and e-commerce platforms all have screening functions

Mybatis provides dynamic SQL configuration to implement multi condition query

12.1 what is dynamic SQL

Dynamically complete SQL splicing according to query conditions

12.2 labels

• If: splice if the conditions are met, otherwise, splice is not allowed

  <select id="findActiveBlogWithTitleLike"
       resultType="Blog">
    	SELECT * FROM BLOG
    	WHERE 1 = 1
    	<if test="title != null">
      	AND title like #{title}
    	</if>
      <if test="author != null and author.name != null">
      	AND author_name like #{author.name}
    	</if>
  </select>
  

• choose, when, otherwise: similar to switch in java, select one of multiple conditions to use

  <select id="findActiveBlogLike"
       resultType="Blog">
    	SELECT * FROM BLOG WHERE 1 = 1
    	<choose>
      	<when test="title != null">
        		AND title like #{title}
      	</when>
      	<when test="author != null and author.name != null">
        		AND author_name like #{author.name}
      	</when>
      	<otherwise>
        		AND featured = 1
      	</otherwise>
    	</choose>
  </select>
  

• Where tag: used to define where statements

  <select id="findActiveBlogLike"
       resultType="Blog">
    	SELECT * FROM BLOG
    	<where>
      	<if test="state != null">
           	state = #{state}
      	</if>
      	<if test="title != null">
          	AND title like #{title}
      	</if>
      	<if test="author != null and author.name != null">
          	AND author_name like #{author.name}
      	</if>
    	</where>
  </select>
  

  The where tag element inserts the "where" clause only if the child element returns anything. Moreover, if clause starts with the "AND" OR ", where element removes them.

• Trim: if the words defined in the where tag do not meet the requirements, you can also customize the trim element to customize the functions of the where element. For example, the custom trim elements equivalent to the where clause are:

  <trim prefix="WHERE" prefixOverrides="AND |OR ">
    ...
  </trim>
  

  The prefixOverrides property ignores text sequences separated by pipe characters (note that spaces are necessary in this example). The above example removes all the contents specified in the prefixOverrides property and inserts the contents specified in the prefix property.

• Set tag: used to dynamically update statements. The set element can dynamically contain the required columns and ignore other updated columns:

  <update id="updateAuthorIfNecessary">
    	update Author
      <set>
      	<if test="username != null">username=#{username},</if>
       	<if test="password != null">password=#{password},</if>
        	<if test="email != null">email=#{email},</if>
        	<if test="bio != null">bio=#{bio}</if>
      </set>
    	where id=#{id}
  </update>
  

  Equivalent to:

  <trim prefix="SET" suffixOverrides=",">
    ...
  </trim>
  

• forEach: Collection traversal (commonly used when building IN clauses)

  <select id="selectPostIn" resultType="domain.blog.Post">
    	SELECT *
    	FROM POST P
    	WHERE ID in
    	<foreach item="item" index="index" collection="list"
        	open="(" separator="," close=")">
          	#{item}
    	</foreach>
  </select>
  

• Bind: allows you to create a variable and bind it dynamically in an expression

  <select id="selectBlogsLike" resultType="Blog">
    <bind name="pattern" value="'%' + _parameter.getTitle() + '%'" />
    SELECT * FROM BLOG
    WHERE title LIKE #{pattern}
  </select>
  

• Script: to use dynamic SQL in the annotated mapper interface class, you can use the script element

  @Update({"<script>",
           "update Author",
           "  <set>",
           "    <if test='username != null'>username=#{username},</if>",
           "    <if test='password != null'>password=#{password},</if>",
           "    <if test='email != null'>email=#{email},</if>",
           "    <if test='bio != null'>bio=#{bio}</if>",
           "  </set>",
           "where id=#{id}",
           "</script>"})
  void updateAuthorValues(Author author);
  

12.3 #{} and ${}

• ${key} means to obtain parameters. First obtain the values of the parameters and splice them into the SQL statement, and then execute the SQL. Problems that may cause SQL injection;
• #{key} means to obtain parameters. Compile (precompile) the SQL statement first, and then set the obtained parameters to SQL. That is, compile them into placeholders first. This can avoid the problem of SQL injection.

Note: when ${key} is used, the default parameter will be taken as an object. Parameter object or HashMap can be used when passing parameters. If it is an ordinary value, the parameter type must be declared, the parameterType attribute must be added in the select tag, and @ Param must be added when passing parameters, even if there is only one parameter.

<select id="searchMember" parameterType="java.lang.String" resultMap="...">
    select * from ...
    where name like '%${keyword}%'		
</select>

pubilc List<...> ambigousSearch(@Param("keyword") String keyword);

13, MyBatis log configuration

As an encapsulated ORM framework, MyBatis has no way to track its running process. In order for developers to understand its execution process and complete all work at each execution step, MyBatis framework itself integrates log4j log framework to record and track the running process. We only need to configure the relevant logs of MyBatis to see the log information during the operation of MyBatis.

13.1 add log framework dependency

<!-- log4j -->
<dependency>
    <groupId>log4j</groupId>
    <artifactId>log4j</artifactId>
    <version>1.2.17</version>
</dependency>

13.2 add log profile

• Create log4j. In the resources directory Properties, a file with that name must be created in this folder because MyBatis is automatically loaded.

• In log4j How to configure log output in the properties file.

  This is the official configuration of MyBatis

  # Declare the output level and output mode of the log
  log4j.rootLogger=DEBUG,stdout
  log4j.logger.org,mybatis.example.BlogMapper=TRACE
  # Console output...
  log4j.appender.stdout=org.apache.log4j.ConsoleAppender
  log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
  # Define print format% t is thread name% 5p is log level
  log4j.appender.stdout.layout.ConversionPattern=[%t] %5p - %n%m
  

13.3 log level

When using the log framework to output log information, it will be divided into five levels according to the importance of the output log information. From loose to strict.

log level	explain
DEBUG	Output debugging information
INFO	Output suggestive information
WARN	Output warning information
ERROR	General error message
FATAL	Fatal error message

14, Database connection pool configuration - consolidated Druid

Connection pool: a connection pool is a container for storing connection objects. A container is a collection and must be thread safe, that is, two threads cannot get the same connection object. At the same time, it also has the characteristics of queue: first in first out principle.

Advantages of using connection pool: avoid the overhead caused by frequent creation and closing of database connections and save system resources.

As an ORM framework, MyBatis needs to connect to the database during database operation. MyBatis supports the creation of database-based connection pool.

When we configure the MyBatis data source, only when the value of the type attribute of the dataSource tag is POOLED, we can use the built-in connection pool of MyBatis to manage connections.

If we want to use a third-party database connection pool, we need to configure it ourselves.

14.1 common connection pools

• JDBC

• DBCP

• C3P0

• Druid: it has good performance and provides a relatively convenient monitoring system. It is widely used in enterprises. Alibaba is open source

• HiKari: best performance

14.2 adding dependencies

<!-- druid -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.2.8</version>
</dependency>

14.3 create Druid connection pool factory

Create a class that inherits PooledDataSourceFactory and set the dataSource to DruidDataSource in the parameterless constructor

public class DruidDataSourceFactory extends PooledDataSourceFactory {
    public DruidDataSourceFactory() {
        this.dataSource = new DruidDataSource();
    }
}

14.4 configure Druid connection pool factory to MyBatis data source

In the MyBatis main configuration file, use DruidDataSourceFactory.

<environment id="DruidTest">
    <transactionManager type="JDBC"></transactionManager>
    <!-- POOLED Using MyBatis Internal connection pool -->
    <!-- MyBatis You need a connection pool factory that can generate database connection pools PooledDataSourceFactory -->
    <!-- DruidDataSourceFactory Inherited from PooledDataSourceFactory -->
    <dataSource type="com.gsjt.utils.DruidDataSourceFactory">
        <property name="driverClass" value="${mysql_driver}"/>
        <property name="jdbcUrl" value="${mysql_url}"/>
        <property name="username" value="${mysql_username}"/>
        <property name="password" value="${mysql_password}"/>
    </dataSource>
</environment>

At the same time, the parameters required by Druid are driverClass and JDBC URL, which is different from the POOLED mode.

15, MyBatis cache

MyBatis is a JDBC based package, which makes database operation more convenient. Besides encapsulating the operation steps of JDBC, MyBatis also optimizes its performance:

• A caching mechanism is introduced into MyBatis to improve the retrieval efficiency of MyBatis
• The delayed loading mechanism is introduced into MyBatis to reduce unnecessary access to the database

15.1 working principle of cache

• Check whether there is data to be queried in the cache;
• If it exists, the data is directly obtained from the cache and returned, which reduces the number of accesses to the database and greatly improves the efficiency;
• If it does not exist, it will be queried from the database. After the query, the data will be returned and stored in the cache for the next query.

15.2 caching in mybatis

The cache in MyBatis is divided into L1 cache and L2 cache

15.2. L1 cache

The first level cache is also called SqlSession level cache. Cache memory is allocated separately for each SqlSession and can be used directly without manually opening it; The cache of multiple sqlsessions is not shared.

Characteristics of L1 cache:

• If the same SqlSession object is used for multiple queries, the data will be stored in the cache after the first query, and subsequent queries will directly access the cache;
• If the queried object is modified after the first query, the modification will affect the cache. The second query will directly access the cache, resulting in inconsistent query results with the database.
• When you want to skip the cache and query the database directly, you can use SqlSession Clearcache() to clear the cache of the current SqlSession object.
• If the current sqlSession is used to update after the first query and before the second query, the cache will become invalid and the second query will directly access the database.

Existing problems

• After the first query, the database has been modified, but the data before modification is still displayed in the second query
  • Analysis: modification and query are not the same thread, so different dao objects are used (different sqlsessions are used), so modification will not cause cache invalidation of query operation;
  • There are two solutions
    • Let the query and modification use the same SqlSession object (unreasonable);
    • Let the cache empty after each query operation.

15.2. L2 cache

The L2 cache is also called the SqlSessionFactory level cache. The SqlSession obtained through the same factory object can share the L2 cache; In the application server, SqlSessionFactory is a singleton, so the secondary cache data can be shared globally.

L2 cache features:

• The L2 cache is not enabled by default and needs to be in mybatis config Open in the setting tag in XML,

  <settings>
  	<setting name="cacheEnabled" value="true"></setting>
  </settings>
  

  In addition, as long as the chche tag is used in the mapper file, the L2 Cache is enabled. This tag has some parameters. eviction sets the Cache recovery policy, and the values are LRU (default), FIFO, SOFT and leak. flushInterval sets the refresh interval. By default, it is not empty. Whether readOnly is read-only. Size specifies the size. type specifies the full class name of the custom Cache and implements the Cache interface.

  <cache></cache>
  

• The L2 cache can only cache objects that implement the serialization interface;

15.2. 3 cache query data

• First judge whether the L2 cache is enabled. If not, then judge whether the L1 cache is enabled. If not, directly query the database

• If the L1 cache is turned off, there is no data even if the L2 cache is turned on, because the L2 cache data is obtained from the L1 cache

• The L1 cache is not normally closed

• L2 cache is not enabled by default

• If the L2 cache is closed, directly judge whether the L1 cache has data. If not, check the database

• If the L2 cache is enabled, first judge whether there is data in the L2 cache, and if so, return it directly; If not, query the L1 cache. If yes, return. If not, query the database;

To sum up: first check the L2 cache, then the L1 cache, and then the database; Even in an sqlSession, the L2 cache will be checked first; This is especially true for queries in a namespace;

16, Delayed loading

Delayed loading means that if a sub query is executed in MyBatis (at least twice), only the first query is executed by default. The execution of the sub query will be triggered when the query results of the sub query are used. If the results of the sub query are not used, the sub query will not be executed.

The main configuration file of MyBatis needs to be configured

<settings>  
   <setting name="lazyLoadingEnabled" value="true"/><!--Delayed loading/Lazy loading-->  
   <setting name="aggressiveLazyLoading" value="false"/><!--Active loading/Preload-->  
</settings>