Mybatis

Framework introduction

Framework is a semi-finished software. We can continue to develop based on this semi-finished software to complete our personalized needs.

ORM introduction

ORM (object relational mapping): Object Relational Mapping

It refers to the mapping pattern of persistent data and entity objects. It is a technology to solve the mismatch between object-oriented and relational databases.

That is, the data in the database can be read and saved in the java class, or the data contained in the java class can be saved in the database

Introduction to Mabatis

The previous JDBC can meet our needs for database operation, but there are some problems:

1. Frequent creation and destruction of database connections will waste system resources and affect system performance
2. sql statements are hard coded (write dead) in the code. If you want to modify sql statements, you need to modify the java code, which makes the code difficult to maintain.
3. During query operation, you need to manually encapsulate the data in the result set into entity objects.
4. When adding, deleting, modifying and querying require parameters, you need to manually set the data of the entity object to the placeholder of the sql statement.

Solution:

1. Initialize resources using database connection pool
2. Extract sql statements into the configuration file. In the future, you only need to modify the configuration file
3. Use underlying techniques such as reflection and introspection. Automatic mapping of attributes and fields between entities and tables

In other words, all our problems can be solved, but it is difficult for us to solve them ourselves, so we need some excellent frameworks written by others.

• Mybatis is an excellent persistence layer framework based on java. It encapsulates JDBC internally, so that developers only need to pay attention to the sql statement itself, and do not need to spend energy on complex operations such as loading drivers, creating connections, and creating executors.
• Mybatis configures various statements to be executed by means of xml or annotation, and generates the final sql statement to be executed by mapping java objects and dynamic parameters in the statement.
• Mybatis executes the sql and maps the result to a java object and returns it. The ORM idea is adopted to solve the problem of entity and database mapping, encapsulate JDBC and shield the underlying access details of JDBC API, so that we can complete the persistence operation of database without dealing with JDBC API.
• Mybatis official website: http://www.mybatis.org/mybatis-3/

Getting started with Mybatis

Get database data through simple Mybatis

1. Data preparation

   Create a new java project on COM luxoin. Create a User class under object and click com luxoin. Create UserTest01 class under Dao.

   /**
    * @author: luoxin
    * @create: 2021-07-17 19:08
    **/
   
   package com.luxoin.object;
   
   public class User {
   
       private int id,age;
       private String name;
   
       public User(int id, int age, String name) {
           this.id = id;
           this.age = age;
           this.name = name;
       }
   
       public User() {
       }
   
       @Override
       public String toString() {
           return "User{" +
                   "id=" + id +
                   ", age=" + age +
                   ", name='" + name + '\'' +
                   '}';
       }
   
       public int getId() {
           return id;
       }
   
       public void setId(int id) {
           this.id = id;
       }
   
       public int getAge() {
           return age;
       }
   
       public void setAge(int age) {
           this.age = age;
       }
   
       public String getName() {
           return name;
       }
   
       public void setName(String name) {
           this.name = name;
       }
   
       public static void main(String[] args) {
   
       }
   }
   
   

   /**
    * @author: luoxin
    * @create: 2021-07-17 19:10
    **/
   
   package com.luxoin.dao;
   
   import com.luxoin.object.User;
   import org.apache.ibatis.io.Resources;
   import org.apache.ibatis.session.SqlSession;
   import org.apache.ibatis.session.SqlSessionFactory;
   import org.apache.ibatis.session.SqlSessionFactoryBuilder;
   import org.junit.Test;
   
   import java.io.IOException;
   import java.io.InputStream;
   import java.util.List;
   
   public class UserTest01 {
   
       public static void main(String[] args) {
   
       }
       @Test
       public void selectAll() throws IOException {
   //        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 sqlSession factory
           SqlSession sqlSession = sqlSessionFactory.openSession();
   //        4. Execute the sql statement in the mapping configuration file and receive the result
           List<User> list = sqlSession.selectList("UserMapper.selectAll");
   //        5. Treatment results
           for(User u : list) {
               System.out.println(u);
           }
   //        6. Release resources
           sqlSession.close();
           is.close();
       }
   }
   
   

2. Import jar package

   Link: https://pan.baidu.com/s/1iBL6ghrzJp9-aoVPtwEFSA
   Extraction code: 4r4r

   The package guide step is to create a directory named libs under the project root directory, copy the relevant jar packages to the directory, select them, right-click them, and click Add As Library

3. Create the mapping configuration file usertest01 under src XML (if it is a Java Web project, the file location is in the resource directory)

   <?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="UserMapper">
       <select id="selectAll" resultType="com.luxoin.object.User">
           select * from user;
       </select>
   </mapper>
   

4. Create the core configuration file mybatisconfig. Under src XML (if it is a Java Web project, the file location is in the resource directory)

   <?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>
       <environments default="mysql">
           <environment id="mysql">
               <transactionManager type="JDBC"></transactionManager>
               <dataSource type="POOLED">
                   <property name="driver" value="com.mysql.jdbc.Driver"/>
                   <property name="url" value="jdbc:mysql://127.0.0.1:3306/test01"/>
                   <property name="username" value="root"/>
                   <property name="password" value="123456"/>
               </dataSource>
           </environment>
       </environments>
       <mappers>
           <mapper resource="UserTest01.xml"/>
       </mappers>
   </configuration>
   

5. Write test classes to complete the use of relevant API s

You can call the function written in the main function, or you can double click on the function name of the function name (you must have @Test annotation, and the relevant jar package has been given above).

Quick start summary

1. frame

   Framework is a semi-finished software. We can continue to develop based on this semi-finished software to complete our personalized needs.

2. ORM

   Object relational mapping, mapping of data and entity objects

3. Mybatis

   It is an excellent persistence layer framework based on java, which encapsulates JDBC internally

4. Getting started steps

   1. Import jar package
   2. Writing a mapping profile
   3. Write core configuration file
   4. Use the relevant API s to complete programming

Mybatis related API s

Resources

org.apache.ibatis.io.Resources: tool class for loading resources (ibatis is the predecessor of mybatis)

Core approach:

Return value	Method name	explain
InputStream	getResourceAsStream(String fileName)	Returns the byte input stream of the specified resource through the class loader

SqlSessionFactoryBuilder

org.apache.ibatis.session.SqlSessionFactoryBuilder: get the function class of SqlSessionFactory factory object.

Return value	Method name	explain
SqlSessionFactory	build(InputStream is)	Get the SqlSession factory object by specifying the byte resource input stream

SqlSessionFactory

org.apache.ibatis.session.SqlSessionFactory: gets the factory interface of the SqlSession builder object

Return value	Method name	explain
SqlSession	openSession()	Get the SqlSession builder object and start the manual commit transaction
SqlSession	openSession(boolean autoCommit)	Get the SqlSession builder object. If the parameter is true, automatic transaction submission will be enabled

SqlSession

org.apache.ibatis.session.SqlSession: Builder object interface. Used to execute SQL, manage transactions, and interface agents.

Return value	Method name	explain
List	selectList(String statement, Object parameter)	Execute the query statement and return the List collection
T	selectOne(String statement, Object parameter)	Execute the query statement and return a result object
int	insert(String statement, Object parameter)	Execute the insert statement to return the number of affected rows
int	updata(String statement, Object parameter)	Execute the updata statement to return the number of affected rows
int	delete(String statement, Object parameter)	Execute the delete statement to return the number of affected rows
void	commit()	Price increase transaction
void	rollback()	Rollback transaction
T	getMapper(Class cls)	Gets the proxy implementation class object of the specified interface
void	close()	Release resources

Mybatis mapping profile

Introduction to mapping profile

The mapping configuration file contains the mapping relationship between data and objects and the sql statements to be executed.

The mapping configuration file can be roughly divided into three parts

<?xml version="1.0" encoding="utf-8" ?>
<!--
    It must be written on the top
    Explained xml Version and code of
-->

<!--
    Mybatis of DTD The function is to suggest whether the writing format is correct, and to give prompt and automatic completion
-->
<!DOCTYPE mapper
        PUBLIC  "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<!--
    mapper   Core root label
    namespace Attribute: namespace, through which the corresponding mapping can be found
-->
<mapper namespace="UserMapper">
<!--
    select label:Label of table query function
    id Attribute: unique identification, fit namespace Find the to execute sql sentence
    resultType Property: Specifies the object to map the query results
    parameterType Property: Specifies the type of parameter mapping object    for example java.lang.Long
-->
    <select id="selectAll" resultType="com.luxoin.object.User">
        select * from user;
    </select>
</mapper>

After realizing the functions of addition, deletion, query and modification, the files are as follows:

<?xml version="1.0" encoding="utf-8" ?>
<!--
    It must be written on the top
    Explained xml Version and code of
-->

<!--
    Mybatis of DTD The function is to suggest whether the writing format is correct, and to give prompt and automatic completion
-->
<!DOCTYPE mapper
        PUBLIC  "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<!--
    mapper   Core root label
    namespace Attribute: namespace, through which the corresponding mapping can be found
-->
<mapper namespace="UserMapper">
<!--
    select label:Label of table query function
    id Attribute: unique identification, fit namespace Find the to execute sql sentence
    resultType Property: Specifies the object to map the query results
    parameterType Property: Specifies the type of parameter mapping object    for example java.lang.Long
-->
    <select id="selectAll" resultType="com.luxoin.object.User">
        select * from user;
    </select>
    <select id="selectById" resultType="com.luxoin.object.User" parameterType="java.lang.Integer">
        select * from user where id = #{id};
    </select>
    <insert id="insert" parameterType="com.luxoin.object.User">
        insert into user values(#{id},#{name},#{age});
    </insert>
    <update id="update" parameterType="com.luxoin.object.User">
        update user set name = #{name} where id = #{id};
    </update>
    <delete id="delete" parameterType="java.lang.Integer">
        delete from user where id = #{id};
    </delete>
</mapper>

/**
 * @author: luoxin
 * @create: 2021-07-17 19:10
 **/

package com.luxoin.dao;

import com.luxoin.object.User;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class UserTest01 {

    public static void main(String[] args) throws IOException {

    }
    @Test
    public void delete() throws IOException {
        InputStream is = Resources.getResourceAsStream("MybatisConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        int result = sqlSession.delete("UserMapper.delete", 4);
        sqlSession.commit();
        System.out.println("Affected"+result+"that 's ok");
        sqlSession.close();
        is.close();

    }
    @Test
    public void update() throws IOException {
        InputStream is = Resources.getResourceAsStream("MybatisConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        User user = new User(1,45,"Luo Xin");
        int result = sqlSession.update("UserMapper.update", user);
        sqlSession.commit();
        System.out.println("Affected"+result+"that 's ok");
        sqlSession.close();
        is.close();
    }
    @Test
    public void insert() throws IOException {
//        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 sqlSession factory
        SqlSession sqlSession = sqlSessionFactory.openSession();
//        4. Execute the sql statement in the mapping configuration file and receive the result
        User user = new User(5,64,"Zhao Liu");
        int result = sqlSession.insert("UserMapper.insert", user);
        sqlSession.commit();
//        5. Treatment results
        System.out.println("Affected"+result+"that 's ok");
//        6. Release resources
        sqlSession.close();
        is.close();

    }
    @Test
    public void selectById() throws IOException {
//        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 sqlSession factory
        SqlSession sqlSession = sqlSessionFactory.openSession();
//        4. Execute the sql statement in the mapping configuration file and receive the result
        User s = sqlSession.selectOne("UserMapper.selectById",3);
//        5. Treatment results
        System.out.println(s);
//        6. Release resources
        sqlSession.close();
        is.close();
    }
    @Test
    public void selectAll() throws IOException {
//        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 sqlSession factory
        SqlSession sqlSession = sqlSessionFactory.openSession();
//        4. Execute the sql statement in the mapping configuration file and receive the result
        List<User> list = sqlSession.selectList("UserMapper.selectAll");
//        5. Treatment results
        for(User u : list) {
            System.out.println(u);
        }
//        6. Release resources
        sqlSession.close();
        is.close();
    }
}

Mybatis core profile

Introduction to core configuration file

The core configuration file contains the core settings and attribute information of Mybatis. Such as database connection, transaction, connection pool information, etc.

<?xml version="1.0" encoding="UTF-8" ?>
<!--Document declaration, must be on the first line-->
<!--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>
<!--
    environments Configure the database environment. There can be multiple environments
    defualt Property specifies which environment we are using
-->
    <environments default="mysql">
<!--
    environment Label configuration database environment
    id Property uniquely identifies different environments
-->
        <environment id="mysql">
<!--
    transactionManager Tag: transaction management
    type Attributes: adopt JDBC Default transactions
-->
            <transactionManager type="JDBC"></transactionManager>
<!--
    dataSource Labels: data source information
    type Properties: connection pool
-->
            <dataSource type="POOLED">
<!--
    property Labels: configuring labels
-->
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://127.0.0.1:3306/test01"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>


<!--
    mappers Labels: importing mapping profiles
-->
    <mappers>
<!--
    mapper Label: brings in the specified mapping profile
    resource Property specifies the path and name of the mapping profile
-->
        <mapper resource="UserTest01.xml"/>
    </mappers>
</configuration>

Introduction of database connection configuration file

In the above core configuration file, our database connection information is written dead. In formal development, we are required to extract the database connection information into a file separately, so we need to introduce the file into the core configuration file.

If we extract the database connection information into JDBC Properties file

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/test01
username=root
password=123456

We need a properties tag to introduce the database connection configuration file. We need the resource attribute to specify the file path and obtain the file data through ${key name}.

The modified core configuration file is:

<?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>
<!--Import profile-->
    <properties resource="jdbc.properties"></properties>
    <environments default="mysql">
        <environment id="mysql">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
            <!--Get the information in the configuration file-->
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>


    <mappers>
        <mapper resource="UserTest01.xml"/>
    </mappers>
</configuration>

Alias

In the code written earlier, we always write a long string of the values of resultType and parameterType, such as com luxoin. object. User, very troublesome. We want to use user only to replace this long string of characters. At this time, we can use alias

• : alias parent label
• : alias child label
• attribute
  • type: Specifies the full class name
  • Alias: specify alias
• : sublabel the alias of all classes under the specified package. The alias is the class name

Aliasing needs to be performed under the core configuration file

<?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>
    <properties resource="jdbc.properties"></properties>
    
    <typeAliases>
<!--        Alias the specified class-->
        <typeAlias type="com.luxoin.object.User" alias="User"></typeAlias>
<!--        Alias all classes under the package-->
        <package name="com.luxoin.object"/>
    </typeAliases>
    <environments default="mysql">
        <environment id="mysql">
            <transactionManager type="JDBC"></transactionManager>
            <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>

    <mappers>
        <mapper resource="UserTest01.xml"/>
    </mappers>
</configuration>

The dao layer is implemented in the traditional way of Mybatis

dao:Data Access Object

Dao layer is implemented in traditional way

Layered idea: controller, service and dao.

Call process: the control layer calls the business layer, and the persistence layer is called monthly, and the persistence layer deals with the database

LOG4J (log for java 4 read English)

When troubleshooting problems in the daily development process, it is inevitable to output the sql statements, parameters, results and other information actually executed by Mybatis. We can use the function of log4j to output the execution information.

Mybatis advanced

Implementing dao layer with Mybatis interface proxy

Interface proxy mode - Implementation Rules

To implement the dao layer in the traditional way, we should not only implement the interface, but also write the implementation class. The Mybatis framework can help us omit the steps of writing dao implementation classes. Programmers only need to write interfaces, and the Mybatis framework creates the dynamic proxy object of the interface according to the definition of the interface.

Implementation rules:

1. The namespace in the mapping configuration file must be the same as the full class name of the dao layer interface
2. The id attribute of the add / delete / modify query tag in the mapping configuration file must be the same as the method name of the dao layer interface
3. The parameterType attribute of the add / delete / modify query tag in the mapping configuration file must be the same as the parameter of the dao layer interface method
4. The resultType attribute of the add / delete / modify query tag in the mapping configuration file must be the same as the return value of the dao layer interface method

That is to say, the specific implementation of the function was implemented at the dao layer before, but now the dao implementation class can be omitted, so the specific implementation of the function has changed to the service layer implementation class, and the implementation method has been changed.

@Override
    public List<User> selectAll() {
        InputStream is = null;
        List<User> list = null;
        SqlSession sqlSession = null;
        try {
//        1. Load the core configuration file
            is = Resources.getResourceAsStream("MybatisConfig.xml");
//        2. Get sqlSession factory object
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
//        3. Obtain the sqlSession object through the sqlSession factory
            sqlSession = sqlSessionFactory.openSession();
//        4. Get the implementation class object of UserMapper interface
            UserMapper mapper = sqlSession.getMapper(UserMapper.class);
//        5. Call the method through the implementation class object to receive the result
            list = mapper.selectAll();
//        5. Treatment results
            for (User u : list) {
                System.out.println(u);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //        6. Release resources
            sqlSession.close();
            try {
                is.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return list;
    }
   

Mybatis mapping profile - dynamic SQL

Introduction to dynamic SQL

In the Mybatis mapping configuration file, our SQL is relatively simple. Sometimes when the business logic is complex, our SQL changes dynamically. At this time, the SQL learned above can not meet the requirements.

Multi criteria query:

When the query condition is 3, the SQL statement is: select * from user where id=? andname=? and age=?;

When the query condition is 2, the SQL statement is: select * from user where id=? andname=?;

In the above case, if you use the previous knowledge, you can only write two different select tags and two functions, but only one SQL statement is enough in dynamic SQL.

Dynamic SQL tag

: condition label. If there are dynamic conditions, use this tag instead of the where keyword

: condition judgment tag

The following configuration file implements multi condition query. It should be easy to read, but it can only be queried with id

<select id="selectByIds" resultType="com.luxoin.object.User" parameterType="java.lang.Integer">
        select * from user
        <where>
            <if test="id != null"> id = #{id}</if>
            <if test="name != null">and name = #{name}</if>
            <if test="age != age">and age = #{age}</if>
        </where>
    </select>

: loop through labels

For example, the following two statements:

select * from user where id in (1,2);
select * from user where id in (1,2,3);

If we want to use a dynamic SQL statement, we need to use the foreach tag. We regard the contents in the following parentheses as the elements in the set, that is, we pass in a set, and then use the foreach loop to put all the elements in the set in the parentheses.

<!--
collection Attribute: the type of container that holds the parameters
open Properties: starting sql sentence
close Properties: end sql sentence
item:Name of the parameter variable
separator:Separator between parameters
     -->
<foreach collection="" close="" item="" open="" separator="" >

</foreach>

The mapping configuration file of the above dynamic sql statement is:

<select id="selectByIdss" resultType="com.luxoin.object.User" parameterType="java.util.List">
        select * from user
        <where>
        <foreach collection="java.util.List" close=");" item="id" open="id in (" separator="," >
            #{id}
        </foreach>
        </where>
    </select>

SQL fragment extraction

• We can extract some repetitive SQL statements to achieve the effect of reuse.

• : extract sql statement label

  <sql id="Fragment unique identifier">Extracted sql sentence</sql>
  

• : introducing sql fragment Tags

  <include refid="identification" />
  

Mybatis core profile – paging plug-in

Introduction to paging plug-in

• Pagination can display many results in pages
• If you are currently on the first page, there is no previous page, and there is no next page on the last page
• It is necessary to specify the current page and how many results are displayed on this page

Paging query in sql requires the use of the limit keyword

​ limit n,m; Represents the query of consecutive m records starting from the n index

Paging is also a common technology in enterprise development, but the currently used Mybatis does not have paging function. If we want to realize paging function, we need to manually write limit statements, but the sql statements for paging operation in different databases are different, so the cost of handwritten paging is high. At this time, you can use the paging plug-in to realize the paging function.

PageHelper: a third-party paging assistant that encapsulates complex paging operations, making paging simple

Implementation steps of paging plug-in:

1. Import jar package

   Link: https://pan.baidu.com/s/1TnjK6_aADf0B-HKR-W_zpw
   Extraction code: 4r4r

2. Integrating the paging assistant plug-in in the core configuration file

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

3. In the test class, use the classification assistant related API to realize the paging function

In fact, it is executed before the query is executed

PageHelper.startPage(1,2);

The two parameters are PageIndex (starting from 1) and pagesize

It should be noted that when writing the mapping configuration file, do not add;, at the end of the statement, Otherwise, an error will be reported

Mybatis multi table operation

Multi table model

• What we learned before is single table operation, but in the actual development, with the deepening of business difficulty, we must need multi table operation.
• Multi table model classification
  • One to one: establish a foreign key on either party and associate it with the other party's primary key
  • One to many: establish a foreign key in one of the many parties and associate the primary key of one party
  • Many to many: with the help of an intermediate table, at least two fields in the intermediate table are associated with the primary keys of the two tables respectively

one-on-one

• One to one model the person and ID card the first mock exam is the one to one model.

The biggest difference between multi table query and single table query is the mapping configuration file. The results of multi table query are not only retained in an 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.luoxin.table01.OneToOneMapper">
<!--    id Is a unique identifier   type Is a mapping type,Here is the alias-->
    <resultMap id="OneToOne" type="card">
<!--        id Labels are used for primary key mapping   column Property to specify the column name   property Property specifies the member name in the class-->
        <id column="cid" property="id"></id>
<!--        result Label for non primary key mapping,column Property to specify the column name   property Property specifies the member name in the class-->
        <result column="number" property="number"></result>
<!--
            The third attribute is another entity class,Another label is required association
            association:Used to configure the mapping relationship of included objects
            property:The variable name of the included object
            javaType:The data type of the contained object. Here is the alias
-->
        <association property="p" javaType="person">
            <id column="pid" property="id"></id>
            <result column="name" property="name"></result>
            <result column="age" property="age"></result>
        </association>
    </resultMap>
<!--    previous resulitType It is required for single table and multiple tables resultMap，resultMap Need to declare first-->
    <select id="selectAll" resultMap="OneToOne">
        SELECT c.id cid,number,pid,NAME,age FROM card c,person p WHERE c.pid = p.id
    </select>
</mapper>

One to many

• One to many model: students and classes

The above one-to-one is to include another object in an object, while one to many is to include a collection in an object, which is full of other objects. In fact, the mapping configuration file is different

<?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.luoxin.table02.OneToManyMapper">
    <resultMap id="otm" type="classes">
        <id column="cid" property="id"></id>
        <result column="cname" property="name"></result>

<!--
        collection:Configure the contained collection object mapping relationship
-->
        <collection property="student" ofType="student">
            <id column="sid" property="id"></id>
            <result column="sname" property="name"></result>
            <result column="age" property="age"></result>
        </collection>
    </resultMap>
    <select id="selectAll" resultMap="otm">
        SELECT c.id cid,c.name cname,s.id sid,s.name sname,s.age age FROM class c,student s WHERE s.cid=c.id
    </select>
</mapper>

Many to many

Many to many model: students and courses. A student can choose multiple courses, and a course can also be selected by multiple students.

Project source code of the three models and database sql statements:

Link: https://pan.baidu.com/s/16ErMbSDgxWbQ7lfcSGE4lA
Extraction code: 4r4r

Mybatis annotation development

Introduction to common notes

In addition to using the mapping configuration file, we can also use the annotation form to operate. The configuration file is too complex and the annotation is simple. Although the annotation is more concise, we should also master how to use the configuration file.

Common notes:

@Select("sql statement for query"): execute query operation annotation

@Insert("new sql statement"): execute the new operation annotation

@Updata("modified sql statement"): execute modification annotation

@Delete("deleted sql statement"): execute the delete operation

Use of annotations

• Create interfaces and query methods

  The bottom layer of the mapping is the agent, and the agent needs an interface, so the creation of the interface is essential. Create the methods we want to use in the interface. If it is before, we have to write the mapping configuration file to configure the interface location, return value type and return value correspondence in the mapping configuration file. Using annotations doesn't need to be so complicated at all. We just need to write a line @ Select("query sql statement") on the method declaration of the interface. At first, I was a little surprised. Don't you even need the mapping of the returned results?

  So I tried

  select name,id,age from user
  select * from user
  select id,name,age from user
  

  The query results of these statements in different order are the same, and the output results of the last three are the same, which can match normally, that is, there is an automatic encapsulation mechanism in the annotation.

  After learning, you will find that the above is only a simple encapsulation function, that is, the fields we query can be encapsulated correctly only if they are the same as the attribute names in the entity class, otherwise they can't. Annotation encapsulation will be learned later.

• Configure the mapping relationship in the core configuration file

  The configuration method is

  <mappers>
  <package name="The path of the package where the interface is located">
  </mappers>
  

  Note that it is the path of the package and does not need to be specific to a class.

• Writing test classes

  After completing the above two steps, you can test

Annotation multi table operation

We learned the annotation earlier, but it is only the annotation of single table query. We don't even need to encapsulate the results. Next, the annotation of multi table operation is more troublesome. We need to manually encapsulate the queried fields. The following picture is the packaging process. We are already familiar with the colume and property, that is, encapsulating the queried colume field into the property property property of the class

• One to one implementation

  

  Note: in fact, there is automatic encapsulation in the third result, so it is required that the attribute name of our custom class and the database field name should be the same

  @Select("select * from card")
      //Result s contains multiple Results, enclosed in curly braces
      @Results({
              //Each Result represents an item and encapsulates the queried colume field into the property attribute of the class
              @Result(column = "id",property = "id"),
              @Result(column = "number",property = "number"),
              //When one item is a custom object, the following format is used
              @Result(
                      //one = @One(select = "com.luoxin.zhujie.zhujie2.selectById") is the format of one-to-one annotation, and the following string is the path of another function we want to call
                      //My understanding is to pass the queried pid as a parameter into the one function, and then encapsulate the result into the javaType specified class and name it p
                      column = "pid",
                      one = @One(select = "com.luoxin.zhujie.zhujie2.selectById"),
                      javaType = Person.class,
                      property = "p"
              )
      })
      public abstract List<Card> selectAll();
  

  public interface zhujie2 {
      @Select("select * from person where id = #{id}")
      Person selectById(Integer id);
  }
  

  Test class:

  /**
   * @author: luoxin
   * @create: 2021-07-19 15:17
   **/
  
  package com.luoxin.zhujie;
  
  import com.luoxin.bean.Card;
  import org.apache.ibatis.io.Resources;
  import org.apache.ibatis.session.SqlSession;
  import org.apache.ibatis.session.SqlSessionFactory;
  import org.apache.ibatis.session.SqlSessionFactoryBuilder;
  
  import java.io.IOException;
  import java.io.InputStream;
  import java.util.List;
  
  public class Test {
  
      @org.junit.Test
      public void selectAll() throws IOException {
          InputStream is = Resources.getResourceAsStream("MybatisConfig.xml");
          SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
          SqlSession sqlSession = sqlSessionFactory.openSession(true);
          zhujie mapper = sqlSession.getMapper(zhujie.class);
          List<Card> list = mapper.selectAll();
          for(Card o:list) {
              System.out.println(o);
          }
          sqlSession.close();
          is.close();
      }
  }
  
  

• One to many implementation

  

• Many to many implementation

  

Building SQL statements with Mybatis

Introduction to SQL build object

• When we developed through annotations, the relevant SQL statements were spelled directly by ourselves, which is troublesome and error prone.
• Mybatis provided us with org apache. ibatis. idbc. The sql function class is specifically used to build sql statements

This object replaces keywords with methods
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class Test {

  @org.junit.Test
  public void selectAll() throws IOException {
      InputStream is = Resources.getResourceAsStream("MybatisConfig.xml");
      SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
      SqlSession sqlSession = sqlSessionFactory.openSession(true);
      zhujie mapper = sqlSession.getMapper(zhujie.class);
      List<Card> list = mapper.selectAll();
      for(Card o:list) {
          System.out.println(o);
      }
      sqlSession.close();
      is.close();
  }

}

- One to many implementation

[External chain picture transfer...(img-GJtY9x3B-1626682488574)]

- Many to many implementation

[External chain picture transfer...(img-gN3vAjYX-1626682488576)]

### Building SQL statements with Mybatis

**SQL Introduction to building objects**

- When we used to develop through annotations, the related SQL Sentences are spelled directly by themselves, which is troublesome and easy to make mistakes.
- Mybatis Provided us with org.apache.ibatis.idbc.SQL Functional classes are designed to build sql sentence

This object replaces keywords with methods