1, Mybatis annotation development single table operation

1.1 common notes of mybatis

In recent years, annotation development has become more and more popular. Mybatis can also use annotation development, so that we can reduce the writing of Mapper

Mapping file. We first learn about some basic CRUD, and then learn about complex mapping multi table operations.

@Insert: add

@Update: implement updates

@Delete: enables deletion

@Select: implement query

@Result: implement result set encapsulation

@Results: can be used with @ Result to encapsulate multiple Result sets

@One: implement one-to-one result set encapsulation

@Many: implement one to many result set encapsulation

1.2 addition, deletion, modification and query of mybatis

We complete the simple operations of adding, deleting, modifying and querying the student table

• Step 1: create mapper interface

  public interface StudentMapper {
      //Query all
      @Select("SELECT * FROM student")
      public abstract List<Student> selectAll();
  
      //Add operation
      @Insert("INSERT INTO student VALUES (#{id},#{name},#{age})")
      public abstract Integer insert(Student stu);
  
      //Modify operation
      @Update("UPDATE student SET name=#{name},age=#{age} WHERE id=#{id}")
      public abstract Integer update(Student stu);
  
      //Delete operation
      @Delete("DELETE FROM student WHERE id=#{id}")
      public abstract Integer delete(Integer id);
  }
  
  

• Step 2: test class

  public class Test01 {
      @Test
      public void selectAll() throws Exception{
          //1. Load the core configuration file
          InputStream is = Resources.getResourceAsStream("MyBatisConfig.xml");
  
          //2. Get SqlSession factory object
          SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
  
          //3. Obtain the SqlSession object through the factory object
          SqlSession sqlSession = sqlSessionFactory.openSession(true);
  
          //4. Get the implementation class object of StudentMapper interface
          StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
  
          //5. Call the method in the implementation class object to receive the result
          List<Student> list = mapper.selectAll();
  
          //6. Treatment results
          for (Student student : list) {
              System.out.println(student);
          }
  
          //7. Release resources
          sqlSession.close();
          is.close();
      }
  
      @Test
      public void insert() throws Exception{
          //1. Load the core configuration file
          InputStream is = Resources.getResourceAsStream("MyBatisConfig.xml");
  
          //2. Get SqlSession factory object
          SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
  
          //3. Obtain the SqlSession object through the factory object
          SqlSession sqlSession = sqlSessionFactory.openSession(true);
  
          //4. Get the implementation class object of StudentMapper interface
          StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
  
          //5. Call the method in the implementation class object to receive the result
          Student stu = new Student(4,"Zhao Liu",26);
          Integer result = mapper.insert(stu);
  
          //6. Treatment results
          System.out.println(result);
  
          //7. Release resources
          sqlSession.close();
          is.close();
      }
  
      @Test
      public void update() throws Exception{
          //1. Load the core configuration file
          InputStream is = Resources.getResourceAsStream("MyBatisConfig.xml");
  
          //2. Get SqlSession factory object
          SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
  
          //3. Obtain the SqlSession object through the factory object
          SqlSession sqlSession = sqlSessionFactory.openSession(true);
  
          //4. Get the implementation class object of StudentMapper interface
          StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
  
          //5. Call the method in the implementation class object to receive the result
          Student stu = new Student(4,"Zhao Liu",36);
          Integer result = mapper.update(stu);
  
          //6. Treatment results
          System.out.println(result);
  
          //7. Release resources
          sqlSession.close();
          is.close();
      }
  
      @Test
      public void delete() throws Exception{
          //1. Load the core configuration file
          InputStream is = Resources.getResourceAsStream("MyBatisConfig.xml");
  
          //2. Get SqlSession factory object
          SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
  
          //3. Obtain the SqlSession object through the factory object
          SqlSession sqlSession = sqlSessionFactory.openSession(true);
  
          //4. Get the implementation class object of StudentMapper interface
          StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
  
          //5. Call the method in the implementation class object to receive the result
          Integer result = mapper.delete(4);
  
          //6. Treatment results
          System.out.println(result);
  
          //7. Release resources
          sqlSession.close();
          is.close();
      }
  }
  
  

• be careful:

  Modify the core configuration file of MyBatis. We use the mapping file instead of annotation, so we only need to load the Mapper interface with annotation

<mappers>
    <!--Scan classes that use annotations-->
    <mapper class="com.XXX.mapper.UserMapper"></mapper>
</mappers>

Alternatively, you can specify the package where the interface containing the mapping relationship is scanned

<mappers>
    <!--Scan the package of the class using the annotation-->
    <package name="com.XXX.mapper"></package>
</mappers>

1.3 annotation development summary

Annotations can simplify development operations and omit the preparation of mapping configuration files.

• Common notes

  @Select("SQL statement of query"): execute query operation annotation

  @Insert("SQL statement of query"): execute new operation annotation

  @Update("SQL statement of query"): perform modification operations

  @Delete("SQL statement of query"): execute the delete operation

• Configure mapping relationship

  <mappers> <package name="Interface package"/> </mappers>    
  

2, Multi table operation developed by MyBatis annotation

2.1 MyBatis annotation for complex mapping development

Before implementing complex relationship mapping, we can implement it through configuration in the mapping file. After annotation development, we can use the combination of @ Results annotation, @ Result annotation, @ One annotation and @ Many annotation to complete the configuration of complex relationship

2.2 one to one query

2.2.1 one to one query model

One to one query requirement: query a user's information and find the corresponding ID card information of the user at the same time

2.2.2 one to one query statement

Corresponding sql statement:

SELECT * FROM card；

SELECT * FROM person WHERE id=#{id};

2.2.3 creating a PersonMapper interface

public interface PersonMapper {
    //Query by id
    @Select("SELECT * FROM person WHERE id=#{id}")
    public abstract Person selectById(Integer id);
}

2.2.4 configuring Mapper with annotations

public interface CardMapper {
    //Query all
    @Select("SELECT * FROM card")
    @Results({
            @Result(column = "id",property = "id"),
            @Result(column = "number",property = "number"),
            @Result(
                    property = "p",             // The variable name of the included object
                    javaType = Person.class,    // The actual data type of the contained object
                    column = "pid",             // Query the person table according to the pid field in the queried card table
                    /*
                        one,@One One to one fixed writing
                        select Property: specifies which method in which interface is called
                     */
                    one = @One(select = "com.XXX.one_to_one.PersonMapper.selectById")
            )
    })
    public abstract List<Card> selectAll();
}

2.2.5 testing

public class Test01 {
    @Test
    public void selectAll() throws Exception{
        //1. Load the core configuration file
        InputStream is = Resources.getResourceAsStream("MyBatisConfig.xml");

        //2. Get SqlSession factory object
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);

        //3. Obtain the SqlSession object through the factory object
        SqlSession sqlSession = sqlSessionFactory.openSession(true);

        //4. Get the implementation class object of CardMapper interface
        CardMapper mapper = sqlSession.getMapper(CardMapper.class);

        //5. Call the method in the implementation class object to receive the result
        List<Card> list = mapper.selectAll();

        //6. Treatment results
        for (Card card : list) {
            System.out.println(card);
        }

        //7. Release resources
        sqlSession.close();
        is.close();
    }

}

2.2.6 one to one configuration summary

@Results: Encapsulates the parent annotation of the mapping relationship.
	Result[] value(): Defined Result array
@Result: Child annotations that encapsulate mapping relationships.
	column Attribute: the name of the field in the queried table
	property Attribute: attribute name in entity object
	javaType Property: the data type of the contained object
	one Attribute: fixed attribute for one-to-one query
 @One: Annotations for one-to-one queries.
	select Property: specifies to call a method in an interface

2.3 one to many query

2.3.1 one to many query model

One to many query: query a course and find the student information corresponding to the course at the same time

2.3.2 one to many query statements

Corresponding sql statement:

SELECT * FROM classes

SELECT * FROM student WHERE cid=#{cid}

2.3.3 create StudentMapper interface

public interface StudentMapper {
    //Query student table according to cid
    @Select("SELECT * FROM student WHERE cid=#{cid}")
    public abstract List<Student> selectByCid(Integer cid);
}

2.3.4 configuring Mapper with annotations

public interface ClassesMapper {
    //Query all
    @Select("SELECT * FROM classes")
    @Results({
            @Result(column = "id",property = "id"),
            @Result(column = "name",property = "name"),
            @Result(
                    property = "students",  // The variable name of the included object
                    javaType = List.class,  // The actual data type of the contained object
                    column = "id",          // Query the student table according to the id field of the queried classes table
                    /*
                        many,@Many Fixed writing method of one to many query
                        select Property: specifies which query method in which interface is called
                     */
                    many = @Many(select = "com.XXX.one_to_many.StudentMapper.selectByCid")
            )
    })
    public abstract List<Classes> selectAll();
}

2.3.5 testing

public class Test01 {
    @Test
    public void selectAll() throws Exception{
        //1. Load the core configuration file
        InputStream is = Resources.getResourceAsStream("MyBatisConfig.xml");

        //2. Get SqlSession factory object
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);

        //3. Obtain the SqlSession object through the factory object
        SqlSession sqlSession = sqlSessionFactory.openSession(true);

        //4. Get the implementation class object of the ClassesMapper interface
        ClassesMapper mapper = sqlSession.getMapper(ClassesMapper.class);

        //5. Call the method in the implementation class object to receive the result
        List<Classes> list = mapper.selectAll();

        //6. Treatment results
        for (Classes cls : list) {
            System.out.println(cls.getId() + "," + cls.getName());
            List<Student> students = cls.getStudents();
            for (Student student : students) {
                System.out.println("\t" + student);
            }
        }

        //7. Release resources
        sqlSession.close();
        is.close();
    }

}

2.3.6 one to many configuration summary

@Results: Encapsulates the parent annotation of the mapping relationship.
	Result[] value(): Defined Result array
@Result: Child annotations that encapsulate mapping relationships.
	column Attribute: the name of the field in the queried table
	property Attribute: attribute name in entity object
	javaType Property: the data type of the contained object
	many Attribute: fixed attribute of one to many query
@Many: Annotations for one to many queries.
	select Property: specifies to call a method in an interface

2.4 many to many query

2.4.1 many to many query model

Many to many query: query students and corresponding course information

2.4.2 many to many query statements

Corresponding sql statement:

SELECT DISTINCT s.id,s.name,s.age FROM student s,stu_cr sc WHERE sc.sid=s.id
SELECT c.id,c.name FROM stu_cr sc,course c WHERE sc.cid=c.id AND sc.sid=#{id}

2.4.3 adding CourseMapper interface method

public interface CourseMapper {
    //Query the selected course by Student id
    @Select("SELECT c.id,c.name FROM stu_cr sc,course c WHERE sc.cid=c.id AND sc.sid=#{id}")
    public abstract List<Course> selectBySid(Integer id);
}

2.4.4 configuring Mapper with annotations

public interface StudentMapper {
    //Query all
    @Select("SELECT DISTINCT s.id,s.name,s.age FROM student s,stu_cr sc WHERE sc.sid=s.id")
    @Results({
            @Result(column = "id",property = "id"),
            @Result(column = "name",property = "name"),
            @Result(column = "age",property = "age"),
            @Result(
                    property = "courses",   // The variable name of the included object
                    javaType = List.class,  // The actual data type of the contained object
                    column = "id",          // Use the id of the student table as the association condition to query the intermediate table and curriculum table
                    /*
                        many,@Many Fixed writing method of one to many query
                        select Property: specifies which query method in which interface is called
                     */
                    many = @Many(select = "com.XXX.many_to_many.CourseMapper.selectBySid")
            )
    })
    public abstract List<Student> selectAll();
}

2.4.5 testing

public class Test01 {
    @Test
    public void selectAll() throws Exception{
        //1. Load the core configuration file
        InputStream is = Resources.getResourceAsStream("MyBatisConfig.xml");

        //2. Get SqlSession factory object
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);

        //3. Obtain the SqlSession object through the factory object
        SqlSession sqlSession = sqlSessionFactory.openSession(true);

        //4. Get the implementation class object of StudentMapper interface
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);

        //5. Call the method in the implementation class object to receive the result
        List<Student> list = mapper.selectAll();

        //6. Treatment results
        for (Student student : list) {
            System.out.println(student.getId() + "," + student.getName() + "," + student.getAge());
            List<Course> courses = student.getCourses();
            for (Course cours : courses) {
                System.out.println("\t" + cours);
            }
        }

        //7. Release resources
        sqlSession.close();
        is.close();
    }

}

2.4.6 summary of many to many configuration

@Results: Encapsulates the parent annotation of the mapping relationship.
	Result[] value(): Defined Result array
@Result: Child annotations that encapsulate mapping relationships.
	column Attribute: the name of the field in the queried table
	property Attribute: attribute name in entity object
	javaType Property: the data type of the contained object
	many Attribute: fixed attribute of one to many query
@Many: Annotations for one to many queries.
	select Property: specifies to call a method in an interface

3, Build sql

3.1 introduction to SQL build object

• When we developed through annotations, the relevant SQL statements were spelled directly by ourselves. Some keywords are troublesome and error prone.
• MyBatis provides us with the org.apache.ibatis.jdbc.SQL function class, which is specially used to build SQL statements
  

3.2 realization of query function

• Define function classes and provide methods to get SQL statements of queries. 

• @SelectProvider: generates SQL statement annotations for queries.

  type attribute: generate SQL statement function class object

  Method property: Specifies the method to call

3.3 implementation of new functions

• Define function classes and provide methods to get new SQL statements.

• @InsertProvider: generate new SQL statement annotations.

  type attribute: generate SQL statement function class object

  Method property: Specifies the method to call

3.4 realization of modification function

• Define a function class and provide a method to get the modified SQL statement.

• @UpdateProvider: generate SQL statement annotations for modification.

  type attribute: generate SQL statement function class object

  Method property: Specifies the method to call

3.5 implementation of deletion function

• Define a function class and provide a method to get the deleted SQL statement.

• @DeleteProvider: generates SQL statement annotations for deletion.

  type attribute: generate SQL statement function class object

  Method property: Specifies the method to call

4, Comprehensive case

4.1 system introduction

We used the original JDBC operation database to operate the student management system, which is very troublesome. Now we use MyBatis operation database to simplify Dao's development.

4.2 environment construction (omitted)

4.3 code modification

• Step 1: add MyBatis configuration file

  <?xml version="1.0" encoding="UTF-8" ?>
  <!--MyBatis of DTD constraint-->
  <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
  
  <!--configuration Core root label-->
  <configuration>
  
      <!--Bring in configuration files for database connections-->
      <properties resource="config.properties"/>
  
      <!--to configure LOG4J-->
      <settings>
          <setting name="logImpl" value="log4j"/>
      </settings>
  
      <!--environments Configure the database environment. There can be multiple environments. default Property specifies which is used-->
      <environments default="mysql">
          <!--environment Configure database environment  id Property unique identifier-->
          <environment id="mysql">
              <!-- transactionManager Transaction management.  type Properties, using JDBC Default transaction-->
              <transactionManager type="JDBC"></transactionManager>
              <!-- dataSource Data source information   type Attribute connection pool-->
              <dataSource type="POOLED">
                  <!-- property Get configuration information of database connection -->
                  <property name="driver" value="${driver}" />
                  <property name="url" value="${url}" />
                  <property name="username" value="${username}" />
                  <property name="password" value="${password}" />
              </dataSource>
          </environment>
      </environments>
  
      <!--Configure mapping relationship-->
      <mappers>
          <package name="com.XXX"/>
      </mappers>
  </configuration>
  
  
  

Bit by bit