Mybatis annotation development single table operation

1. Common notes of mybatis

In recent years, annotation development has become more and more popular. Mybatis can also use annotation development, which can be used to reduce the writing of Mapper

Mapping file.

@Insert: add

@Update: implement update

@Delete: delete

@Select: implement query

@Result: implement result set encapsulation

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

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

@Many: implement one to many result set encapsulation

2. Addition, deletion and modification 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 core configuration file
          InputStream is = Resources.getResourceAsStream("MyBatisConfig.xml");
  
          //2. Get SqlSession factory object
          SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
  
          //3. Obtain SqlSession object through 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 and 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 core configuration file
          InputStream is = Resources.getResourceAsStream("MyBatisConfig.xml");
  
          //2. Get SqlSession factory object
          SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
  
          //3. Obtain SqlSession object through 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 and 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 core configuration file
          InputStream is = Resources.getResourceAsStream("MyBatisConfig.xml");
  
          //2. Get SqlSession factory object
          SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
  
          //3. Obtain SqlSession object through 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 and 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 core configuration file
          InputStream is = Resources.getResourceAsStream("MyBatisConfig.xml");
  
          //2. Get SqlSession factory object
          SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
  
          //3. Obtain SqlSession object through 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 and 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="mybatis.demo.mapper.UserMapper"></mapper>
</mappers>

You can also specify the mapping of the interface or the package it contains

<mappers>
    <!--Scan the package of the class that uses the annotation-->
    <package name="mybatis.demo.mapper"></package>
</mappers>

3. 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"): execute modification annotation

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

• Configure mapping relationship

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

Multi table operation developed by MyBatis annotation

1. Mybatis annotation realizes 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 one to one query

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 one to one query statement

Corresponding sql statement:

SELECT * FROM card；

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

2.3 create PersonMapper interface

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

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",             // 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 = "mybtis.demo.one_to_one.PersonMapper.selectById")
            )
    })
    public abstract List<Card> selectAll();
}

2.5 testing

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

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

        //3. Obtain SqlSession object through 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 and 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.6 one to one configuration summary

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

3 one to many query

1 one to many query model

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

2 one to many query statements

Corresponding sql statement:

SELECT * FROM classes

SELECT * FROM student WHERE cid=#{cid}

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);
}

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",  // 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 = "mybatis.demo.mapper.StudentMapper.selectByCid")
            )
    })
    public abstract List<Classes> selectAll();
}

5 test class

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

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

        //3. Obtain SqlSession object through 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 and 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();
    }

}

6 one to many configuration summary

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

4 many to many query

1 many to many query model

Demand for many to many query: query students and corresponding course information

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}

3. Add 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);
}

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",   // 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 = "mybatis.demo.many_to_many.CourseMapper.selectBySid")
            )
    })
    public abstract List<Student> selectAll();
}

5 test class

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

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

        //3. Obtain SqlSession object through 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 and 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();
    }

}

6 Summary of many to many configuration

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

Build sql

1 Introduction to SQL build object

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

2. Realization of query function

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

• @SelectProvider: generates SQL statement annotations for queries

  type attribute: generate SQL statement function class object

  Method attribute: Specifies the method to call

3. Implementation of new functions

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

• @InsertProvider: generate SQL statement annotations for adding.

  type attribute: generate SQL statement function class object

  Method attribute: Specifies the method to call

4. Implementation of modification function

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

• @UpdateProvider: generate SQL statement annotations for modification.

  type attribute: generate SQL statement function class object

  Method attribute: Specifies the method to call

5. Implementation of deletion function

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

• @DeleteProvider: generates SQL statement annotations for deletion.

  type attribute: generate SQL statement function class object

  Method attribute: Specifies the method to call