That's enough for rodert to teach you MyBatis-Actual Warfare

Keywords: MySQL Mybatis SQL xml Java

[toc]

Article content picture upload failed, welcome to read, eBook in white.

Preface

Statement: Refer to the source Internet, any disputes can be left a message.Standing on the shoulders of the forefathers, we can see farther.

This tutorial is all hands-on, dedicated to the most practical tutorials, does not require any rewards, and only wishes to forward more support.
Welcome to my Public Number, I hope you can get acquainted with me or hurry up, WeChat Search: JavaPub

You can talk about anything, wait for you!

With some knowledge of mybatis, you can skip the previous basic introduction.

1. What is the JDBC Siege Lion of Mybatis

1,1, use idea to build maven projects

! [idea creates maven project screenshots] ()
! [idea creates maven project screenshots] ()

1,2, introducing mysql dependency packages

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.32</version>
</dependency>

1,3, preparing data

  • Create a database:

create database mydatabase;

  • Create tables:
DROP TABLE IF EXISTS tb_user;
CREATE TABLE tb_user (
id char(32) NOT NULL,
user_name varchar(32) DEFAULT NULL,
password varchar(32) DEFAULT NULL,
name varchar(32) DEFAULT NULL,
age int(10) DEFAULT NULL,
sex int(2) DEFAULT NULL,
birthday date DEFAULT NULL,
created datetime DEFAULT NULL,
updated datetime DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  • insert data
INSERT INTO mydatabase.tb_user ( userName, password, name, age, sex, birthday, created, updated) VALUES ( 'zpc', '123456', 'Xiao Ming', '22', '1', '1990-09-02', sysdate(), sysdate());
INSERT INTO mydatabase.tb_user ( userName, password, name, age, sex, birthday, created, updated) VALUES ( 'hj', '123456', 'Quiet', '22', '1', '1993-09-05', sysdate(), sysdate());

1,4, jdbc code review

  • JDBCTest.class

7 Steps to Operate Database

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

/**
 * @author Evan
 */
public class JDBCTest {
    public static void main(String[] args) throws Exception {
        Connection connection = null;
        PreparedStatement prepareStatement = null;
        ResultSet rs = null;

        try {
            // 1, Load Driver
            Class.forName("com.mysql.jdbc.Driver");
            // 2, Get Connections
            String url = "jdbc:mysql://127.0.0.1:3306/mydatabase";
            String user = "root";
            String password = "123456";
            connection = DriverManager.getConnection(url, user, password);
            // 3, get statement, preparedStatement
            String sql = "select * from tb_user where id=?";
            prepareStatement = connection.prepareStatement(sql);
            // 4, Set parameters
            prepareStatement.setLong(1, 1l);
            // 5, Execute the query
            rs = prepareStatement.executeQuery();
            // 6, Processing result sets
            while (rs.next()) {
                System.out.println(rs.getString("userName"));
                System.out.println(rs.getString("name"));
                System.out.println(rs.getInt("age"));
                System.out.println(rs.getDate("birthday"));
            }
        } finally {
            // 7, Close the connection, release resources
            if (rs != null) {
                rs.close();
            }
            if (prepareStatement != null) {
                prepareStatement.close();
            }
            if (connection != null) {
                connection.close();
            }
        }
    }
}

1,5, jdbc Disadvantage Analysis

4, Introduction to MyBatis

! [Introduction Screenshot] ()

Official Documents http://www.mybatis.org/mybati...

3. MyBatis Overall Architecture

4, quick start

4,1, introducing dependencies (pom.xml)

<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.2.8</version>
</dependency>

4, 2, global configuration file (mybatis-config.xml)

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
  PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-config.dtd">
<!-- Root Label -->
<configuration>
<!--<properties>-->
<!--    <property name="driver" value="com.mysql.jdbc.Driver"/>-->
<!--    <property name="url" value="jdbc:mysql://127.0.0.1:3306/mybatis-110?useUnicode=true&amp;characterEncoding=utf-8&amp;allowMultiQueries=true"/>-->
<!--    <property name="username" value="root"/>-->
<!--    <property name="password" value="123456"/>-->
<!--</properties>-->

   <!-- Environment, you can configure multiple, default: Specify which environment to use -->
   <environments default="test">
      <!-- id: Unique Identification -->
      <environment id="test">
         <!-- Transaction Manager, JDBC Type Transaction Manager -->
         <transactionManager type="JDBC" />
         <!-- Data Source, Pool Type Data Source -->
         <dataSource type="POOLED">
            <property name="driver" value="com.mysql.jdbc.Driver" />
            <property name="url" value="jdbc:mysql://127.0.0.1:3306/mybatis-110" />
            <property name="username" value="root" />
            <property name="password" value="123456" />
         </dataSource>
      </environment>
      <environment id="development">
         <!-- Transaction Manager, JDBC Type Transaction Manager -->
         <transactionManager type="JDBC" />
         <!-- Data Source, Pool Type Data Source -->
         <dataSource type="POOLED">
            <property name="driver" value="${driver}" /> <!-- Configured properties，So you can reference it directly -->
            <property name="url" value="${url}" />
            <property name="username" value="${username}" />
            <property name="password" value="${password}" />
         </dataSource>
      </environment>
   </environments>
</configuration>

4,3, ConfigurationMap.xml(MyMapper.xml)

<?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:Root tag, namespace: Namespaces, freely written, generally guaranteed to be unique -->
<mapper namespace="MyMapper">
   <!-- statement，Content: sql Sentence. id: Unique Identity, Write Anyway, Keep Unique in Same Namespace
      resultType: sql Encapsulation type of statement query result set,tb_user That is, the name of the table in the database
    -->
   <select id="selectUser" resultType="com.zpc.mybatis.User">
      select * from tb_user where id = #{id}
   </select>
</mapper>

4, 4, modify global configuration file (mybatis-config.xml)

join MyMapper.xml To configure

<?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">
<!-- Root Label -->
<configuration>
   <!-- Environment, you can configure multiple, default: Specify which environment to use -->
   <environments default="test">
      <!-- id: Unique Identification -->
      <environment id="test">
         <!-- Transaction Manager, JDBC Type Transaction Manager -->
         <transactionManager type="JDBC" />
         <!-- Data Source, Pool Type Data Source -->
         <dataSource type="POOLED">
            <property name="driver" value="com.mysql.jdbc.Driver" />
            <property name="url" value="jdbc:mysql://127.0.0.1:3306/mydatabase" />
            <property name="username" value="root" />
            <property name="password" value="123456" />
         </dataSource>
      </environment>
   </environments>
   <mappers>
     <mapper resource="mappers/MyMapper.xml" />
   </mappers>
</configuration>

4,5, build SqlSessionFactory(MybatisTest.java)

    // Specify Global Profile
    String resource = "mybatis-config.xml";
    // Read Configuration File
    InputStream inputStream = Resources.getResourceAsStream(resource);
    // Building sqlSessionFactory
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

4,6, open the sqlSession callback and execute sql(MyBatisTest.xml)

        // Get sqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession();
        // Operation CRUD, first parameter: specify statement, rule: namespace +'. '+ statementId
        // The second parameter: specify the parameters passed in sql: here is the user id
        User user = sqlSession.selectOne("MyMapper.selectUser", 1);
        System.out.println(user);
  • Complete Code

MyBatisTest.java

import com.zpc.test.pojo.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 java.io.InputStream;

public class MybatisTest {
   public static void main(String[] args) throws Exception {
      // Specify Global Profile
      String resource = "mybatis-config.xml";
      // Read Configuration File
      InputStream inputStream = Resources.getResourceAsStream(resource);
      // Building sqlSessionFactory
      SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
      // Get sqlSession
      SqlSession sqlSession = sqlSessionFactory.openSession();
      try {
         // Operation CRUD, first parameter: specify statement, rule: namespace +'. '+ statementId
         // The second parameter: specify the parameters passed in sql: here is the user id
         User user = sqlSession.selectOne("MyMapper.selectUser", 1);
         System.out.println(user);
      } finally {
         sqlSession.close();
      }
   }
}

User.java

import java.text.SimpleDateFormat;
import java.util.Date;

public class User {
    private String id;
    private String userName;
    private String password;
    private String name;
    private Integer age;
    private Integer sex;
    private Date birthday;
    private String created;
    private String updated;

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getName() {
        return name;
    }

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

    public Integer getAge() {
        return age;
    }

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

    public Integer getSex() {
        return sex;
    }

    public void setSex(Integer sex) {
        this.sex = sex;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public String getCreated() {
        return created;
    }

    public void setCreated(String created) {
        this.created = created;
    }

    public String getUpdated() {
        return updated;
    }

    public void setUpdated(String updated) {
        this.updated = updated;
    }
}

4,7, directory structure

5, Analysis

5,1, Log

5,2, Summary of MyBatis usage steps

1) Configure mybatis-Config.xmlGlobal configuration file (1, data source, 2, external mapper)

2) Create SqlSessionFactory

3) Create SqlSession objects from SqlSessionFactory

4) Operating database CRUD through SqlSession

5) CallSession.commit() Submit a transaction

6) CallSession.close() Close the session

6, Complete Add-Delete Check-Change (CURD)

6,1, create USerDao.java Interface

import com.zpc.mybatis.pojo.User;
import java.util.List;

public interface UserDao {

    /**
     * Query user information based on id
     *
     * @param id
     * @return
     */
    public User queryUserById(String id);

    /**
     * Query all user information
     *
     * @return
     */
    public List<User> queryUserAll();

    /**
     * New Users
     *
     * @param user
     */
    public void insertUser(User user);

    /**
     * Update user information
     *
     * @param user
     */
    public void updateUser(User user);

    /**
     * Delete user information based on id
     *
     * @param id
     */
    public void deleteUser(String id);
}

6,2, CreateUserDaoImpl.java

import com.zpc.mybatis.dao.UserDao;
import com.zpc.mybatis.pojo.User;
import org.apache.ibatis.session.SqlSession;
import java.util.List;

public class UserDaoImpl implements UserDao {
    public SqlSession sqlSession;

    public UserDaoImpl(SqlSession sqlSession) {
        this.sqlSession = sqlSession;
    }

    @Override
    public User queryUserById(String id) {
        return this.sqlSession.selectOne("UserDao.queryUserById", id);
    }

    @Override
    public List<User> queryUserAll() {
        return this.sqlSession.selectList("UserDao.queryUserAll");
    }

    @Override
    public void insertUser(User user) {
        this.sqlSession.insert("UserDao.insertUser", user);
    }

    @Override
    public void updateUser(User user) {
        this.sqlSession.update("UserDao.updateUser", user);
    }

    @Override
    public void deleteUser(String id) {
        this.sqlSession.delete("UserDao.deleteUser", id);
    }

}

6,3, write UserDao corresponding Mapper(UserDaoMapper.xml)

<?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:Root tag, namespace: Namespaces, freely written, generally guaranteed to be unique -->
<mapper namespace="UserDao">
    <!-- statement，Content: sql Sentence. id: Unique Identity, Write Anyway, Keep Unique in Same Namespace
       resultType: sql Encapsulation type of statement query result set,tb_user This is the table in the database
     -->
    <!--<select id="queryUserById" resultType="com.zpc.mybatis.pojo.User">-->
    <!--select * from tb_user where id = #{id}-->
    <!--</select>-->

    <!--aliases-->
    <select id="queryUserById" resultType="com.zpc.mybatis.pojo.User">
      select
       tuser.id as id,
       tuser.user_name as userName,
       tuser.password as password,
       tuser.name as name,
       tuser.age as age,
       tuser.birthday as birthday,
       tuser.sex as sex,
       tuser.created as created,
       tuser.updated as updated
       from
       tb_user tuser
       where tuser.id = #{id};
   </select>

    <select id="queryUserAll" resultType="com.zpc.mybatis.pojo.User">
        select * from tb_user;
    </select>

    <!--insert data-->
    <insert id="insertUser" parameterType="com.zpc.mybatis.pojo.User">
        INSERT INTO tb_user (
        user_name,
        password,
        name,
        age,
        sex,
        birthday,
        created,
        updated
        )
        VALUES
        (
        #{userName},
        #{password},
        #{name},
        #{age},
        #{sex},
        #{birthday},
        now(),
        now()
        );
    </insert>

    <update id="updateUser" parameterType="com.zpc.mybatis.pojo.User">
        UPDATE tb_user
        <trim prefix="set" suffixOverrides=",">
            <if test="userName!=null">user_name = #{userName},</if>
            <if test="password!=null">password = #{password},</if>
            <if test="name!=null">name = #{name},</if>
            <if test="age!=null">age = #{age},</if>
            <if test="sex!=null">sex = #{sex},</if>
            <if test="birthday!=null">birthday = #{birthday},</if>
            updated = now(),
        </trim>
        WHERE
        (id = #{id});
    </update>

    <delete id="deleteUser">
        delete from tb_user where id=#{id}
    </delete>
</mapper>
  • Add Configuration (mybatis-config.xml)
<mappers>
    <mapper resource="mappers/MyMapper.xml"/>
    <mapper resource="mappers/UserDaoMapper.xml"/>
</mappers>

6,4, add UserDao test cases

Pom.xmlFile Add junit Dependency

<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.12</version>
</dependency>

UserDao.java, hold Alt+Enter and select create test

6.5. Write test cases for UserDao

import com.zpc.mybatis.dao.UserDao;
import com.zpc.mybatis.dao.impl.UserDaoImpl;
import com.zpc.mybatis.pojo.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.Before;
import org.junit.Test;
import java.io.InputStream;
import java.util.Date;
import java.util.List;

public class UserDaoTest {

    public UserDao userDao;
    public SqlSession sqlSession;

    @Before
    public void setUp() throws Exception {
        // mybatis-config.xml
        String resource = "mybatis-config.xml";
        // Read Configuration File
        InputStream is = Resources.getResourceAsStream(resource);
        // Build SqlSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
        // Get sqlSession
        sqlSession = sqlSessionFactory.openSession();
        this.userDao = new UserDaoImpl(sqlSession);
    }

    @Test
    public void queryUserById() throws Exception {
        System.out.println(this.userDao.queryUserById("1"));
    }

    @Test
    public void queryUserAll() throws Exception {
        List<User> userList = this.userDao.queryUserAll();
        for (User user : userList) {
            System.out.println(user);
        }
    }

    @Test
    public void insertUser() throws Exception {
        User user = new User();
        user.setAge(16);
        user.setBirthday(new Date("1990/09/02"));
        user.setName("Dapeng");
        user.setPassword("123456");
        user.setSex(1);
        user.setUserName("evan");
        this.userDao.insertUser(user);
        this.sqlSession.commit();
    }

    @Test
    public void updateUser() throws Exception {
        User user = new User();
        user.setBirthday(new Date());
        user.setName("Jingpeng");
        user.setPassword("654321");
        user.setSex(1);
        user.setUserName("evanjin");
        user.setId("1");
        this.userDao.updateUser(user);
        this.sqlSession.commit();
    }

    @Test
    public void deleteUser() throws Exception {
        this.userDao.deleteUser("4");
        this.sqlSession.commit();
    }

}

6.6. Directory structure

6.7. Resolve inconsistencies between database field names and entity class attribute names

When I queried the data, I found no information about userName.
User{id='2', userName='null', password='123456', name='Quiet', age=22, sex=0, birthday='1993-09-05', created='2018-06-30 18:22:28.0', updated='2018-06-30 18:22:28.0'}
Reason: The field name of the database is user_name, the property name in POJO is userName
The inconsistency between the two ends prevents mybatis from populating the corresponding field information.Modify method: Use aliases in sql statements.

  • Solution 1: Use aliases in sql statements:
<select id="queryUserById" resultType="com.zpc.mybatis.pojo.User">
   select
    tuser.id as id,
    tuser.user_name as userName,
    tuser.password as password,
    tuser.name as name,
    tuser.age as age,
    tuser.birthday as birthday,
    tuser.sex as sex,
    tuser.created as created,
    tuser.updated as updated
    from
    tb_user tuser
    where tuser.id = #{id};
</select>
  • Solution 2: When referring to the specific configuration of resultMap-mapper below
  • Solution 3: Reference Hump Matching - mybatis-Config.xmlWhen

7. Dynamic Proxy Mapper Implementation Class

7.1. Consider the above CRUD issues

  1. Interface->Implementation Class->Map.xml
  2. In the implementation class, the way mybatis is used is very similar
  3. xml's sql statement is hard-coded into Java code
  • Think about whether you can write interfaces directly without writing implementation classes.Write only interfaces and Mapping.xml Is that OK?
  • Because sqlsession is used in the implementation class of re-dao(mapper) similarly.So mybatis provides a dynamic proxy for the interface.

7,2, Modify CRUD with Dynamic Proxy

  • Modify the setUp method for test cases

  • Execute the queryUserAll() method

org.apache.ibatis.binding.BindingException: Type interface com.zpc.mybatis.dao.UserDao is not known to the MapperRegistry.
    at org.apache.ibatis.binding.MapperRegistry.getMapper(MapperRegistry.java:47)
    at org.apache.ibatis.session.Configuration.getMapper(Configuration.java:655)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.getMapper(DefaultSqlSession.java:222)
at com.zpc.mybatis.test.UserDaoTest.setUp(UserDaoTest.java:32)
  • Analyze the reason in UserMapper.xml Full path of interface configured in

mapper.xml namespace
If you want to use the dynamic proxy interface passed by mybatis, you need a value in the namespace that corresponds to the full path of the corresponding Mapper(dao) interface.There is no limit to the definition of Namespace in Mapper itself, as long as it is not repeated, but if you use Mybatis's DAO interface dynamic proxy, namespace must be the full path to the DAO interface, for example:com.zpc.mybatis.dao.UserDao

<mapper namespace="com.zpc.mybatis.dao.UserDao">

7.3. Complete examples

  1. Create UserMapper interface (corresponding to the original UserDao)
public interface UserMapper {
   
   /**
    * Login (specify the name of the incoming parameter directly with the comment)
    * @param userName
    * @param password
    * @return
    */
   public User login(@Param("userName") String userName, @Param("password") String password);
   
   /**
    * Query user information based on table name (specify incoming parameter name directly using annotations)
    * @param tableName
    * @return
    */
   public List<User> queryUserByTableName(@Param("tableName") String tableName);
   
   /**
    * Query user information based on Id
    * @param id
    * @return
    */
   public User queryUserById(Long id);
   
   /**
    * Query all user information
    * @return
    */
   public List<User> queryUserAll();
   
   /**
    * Add User Information
    * @param user
    */
   public void insertUser(User user);
   
   /**
    * Update user information based on id
    * @param user
    */
   public void updateUser(User user);
   
   /**
    * Delete user information based on id
    * @param id
    */
   public void deleteUserById(Long id);
}
  1. EstablishUsermapper.xml
<?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:Root tag, namespace: Namespace, Write freely, generally guarantees that the namespace is unique. In order to use interface dynamic proxy, the full path name of the interface must be here-->
<mapper namespace="com.zpc.mybatis.dao.UserMapper">
    <!--
       1.#{}, a precompiled way of preparedstatement, using placeholder substitution to prevent sql injection, any parameter name can be accepted when a parameter
       2.${},Ordinary Statement，String splicing directly, not preventable sql Injection, a parameter, must be used ${value}Receive parameters
     -->
    <select id="queryUserByTableName" resultType="com.zpc.mybatis.pojo.User">
        select * from ${tableName}
    </select>

    <select id="login" resultType="com.zpc.mybatis.pojo.User">
        select * from tb_user where user_name = #{userName} and password = #{password}
    </select>

    <!-- statement，Content: sql Sentence.
       id: Unique identity, freely written, unique under the same namespace, consistent with method name after using dynamic proxy
       resultType: sql Statement queries the encapsulation type of the result set, which is the same as the return type of the method after using the dynamic proxy. resultMap: Either-or
       parameterType: Parameter type, which is the same as the method's parameter type after using dynamic proxy
     -->
    <select id="queryUserById" resultType="com.zpc.mybatis.pojo.User">
        select * from tb_user where id = #{id}
    </select>
    <select id="queryUserAll" resultType="com.zpc.mybatis.pojo.User">
        select * from tb_user
    </select>
    <!-- Added Statement
       id: Unique identity, freely written, unique under the same namespace, consistent with method name after using dynamic proxy
       parameterType: Parameter type, which is the same as the method's parameter type after using dynamic proxy
       useGeneratedKeys:Turn on Primary Key Writeback
       keyColumn: Specify the primary key for the database
       keyProperty: Corresponding Primary Key pojo Property Name
     -->
    <insert id="insertUser" useGeneratedKeys="true" keyColumn="id" keyProperty="id"
            parameterType="com.zpc.mybatis.pojo.User">
        INSERT INTO tb_user (
        id,
        user_name,
        password,
        name,
        age,
        sex,
        birthday,
        created,
        updated
        )
        VALUES
        (
        null,
        #{userName},
        #{password},
        #{name},
        #{age},
        #{sex},
        #{birthday},
        NOW(),
        NOW()
        );
    </insert>
    <!-- 
       //Updated statement
       id: Unique identity, freely written, unique under the same namespace, consistent with method name after using dynamic proxy
       parameterType: Parameter type, which is the same as the method's parameter type after using dynamic proxy
     -->
    <update id="updateUser" parameterType="com.zpc.mybatis.pojo.User">
        UPDATE tb_user
        <trim prefix="set" suffixOverrides=",">
            <if test="userName!=null">user_name = #{userName},</if>
            <if test="password!=null">password = #{password},</if>
            <if test="name!=null">name = #{name},</if>
            <if test="age!=null">age = #{age},</if>
            <if test="sex!=null">sex = #{sex},</if>
            <if test="birthday!=null">birthday = #{birthday},</if>
            updated = now(),
        </trim>
        WHERE
        (id = #{id});
    </update>
    <!-- 
       //Deleted statement
       id: Unique identity, freely written, unique under the same namespace, consistent with method name after using dynamic proxy
       parameterType: Parameter type, which is the same as the method's parameter type after using dynamic proxy
     -->
    <delete id="deleteUserById" parameterType="java.lang.String">
        delete from tb_user where id=#{id}
    </delete>
</mapper>
  1. Global configuration file mybatis-Config.xmlIntroduceUserMapper.xml
<mappers>
    <mapper resource="mappers/MyMapper.xml"/>
    <mapper resource="mappers/UserDaoMapper.xml"/>
    <mapper resource="mappers/UserMapper.xml"/>
</mappers>
  1. Create UserMapper Test Case
import com.zpc.mybatis.dao.UserMapper;
import com.zpc.mybatis.pojo.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.Before;
import org.junit.Test;

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

public class UserMapperTest {

    public UserMapper userMapper;

    @Before
    public void setUp() throws Exception {
        // Specify Profile
        String resource = "mybatis-config.xml";
        // Read Configuration File
        InputStream inputStream = Resources.getResourceAsStream(resource);
        // Building sqlSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        // Get sqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession(true);

        // 1. The namespace of the mapping file must be the full path of the mapper interface
        // 2. The id of the statement of the mapping file must match the method name of the mapper interface
        // 3. Statement's resultType must match the return type of the mapper interface method
        // 4. The parameterType of the statement must match (not necessarily) the parameter type of the mapper interface method
        this.userMapper = sqlSession.getMapper(UserMapper.class);
    }

    @Test
    public void testQueryUserByTableName() {
        List<User> userList = this.userMapper.queryUserByTableName("tb_user");
        for (User user : userList) {
            System.out.println(user);
        }
    }

    @Test
    public void testLogin() {
        System.out.println(this.userMapper.login("hj", "123456"));
    }

    @Test
    public void testQueryUserById() {
        System.out.println(this.userMapper.queryUserById("1"));
    }

    @Test
    public void testQueryUserAll() {
        List<User> userList = this.userMapper.queryUserAll();
        for (User user : userList) {
            System.out.println(user);
        }
    }

    @Test
    public void testInsertUser() {
        User user = new User();
        user.setAge(20);
        user.setBirthday(new Date());
        user.setName("Okami");
        user.setPassword("123456");
        user.setSex(2);
        user.setUserName("bigGod222");
        this.userMapper.insertUser(user);
        System.out.println(user.getId());
    }

    @Test
    public void testUpdateUser() {
        User user = new User();
        user.setBirthday(new Date());
        user.setName("Quiet");
        user.setPassword("123456");
        user.setSex(0);
        user.setUserName("Jinjin");
        user.setId("1");
        this.userMapper.updateUser(user);
    }

    @Test
    public void testDeleteUserById() {
        this.userMapper.deleteUserById("1");
    }
}
  • Directory structure:

7,4, Summary of Dynamic Agents

Using the mapper interface to implement classes without writing an interface is very convenient and officially recommended.

  1. Mapper's namespace must match the full path of the mapper interface.
  2. The method name of the Mapper interface must match the id defined by sql.
  3. The input parameter type of the method in the Mapper interface must match the parameterType defined by sql.(This sentence needs rethinking)
  4. The output parameter type of the method in the Mapper interface must match the resultType defined by sql.

8, mybatis-Config.xmlDetailed

Mybatis-Config.xmlStrict order, follow the order of documents.

8,1, properties property reads external resources

The properties of a property configuration are externally configurable and dynamically replaceable, either in a typical Java property file or through the child elements of a property element.For example:

<properties resource="org/mybatis/example/config.properties">
  <property name="username" value="dev_user"/>
  <property name="password" value="F2Fa3!33TYyg"/>
</properties>

The attributes can then be used throughout the configuration file to replace the attribute values that need to be dynamically configured.For example:

<dataSource type="POOLED">
  <property name="driver" value="${driver}"/>
  <property name="url" value="${url}"/>
  <property name="username" value="${username}"/>
  <property name="password" value="${password}"/>
</dataSource>

The username and password in this example will be replaced by the corresponding values set in the properties element.The driver and url properties will beConfig.propertiesReplace with the corresponding value in the file.This provides many flexible options for configuration.

  • Attributes can also be passed to SqlSessionFactoryBuilder.build() method.For example:
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader, props);
// ... or ...
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader, environment, props);

If the property is configured in more than one place, MyBatis will load in the following order:

  1. The attributes specified inside the properties element body are first read.
  2. Then read the attribute file under the class path based on the resource attribute in the properties element or the path specified by the url attribute, overwriting the read attribute with the same name.
  3. Finally, the property passed as a method parameter is read, overwriting the property with the same name that has been read.

Therefore, the properties passed through the method parameters have the highest priority, followed by the configuration file specified in the resource/url property, and the lowest priority is the property specified in the properties property property.

8,2,settings settings

  • Turn on hump matching:
<settings>
    <setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>

8,3, typeAliases

Type aliases are short names that are named for Java types.It is only related to XML configuration and only exists to reduce redundancy in fully qualified class names.

<typeAliases>
    <typeAlias type="com.zpc.mybatis.pojo.User" alias="User"/>
</typeAliases>

Disadvantages: Each pojo class needs to be configured.
Solution: Use a scan package to scan all classes under the specified package. The alias after the scan is the class name (case insensitive), and the recommended time and class name are the same

<typeAliases>
    <!--type:Full path of entity class. alias:Alias, usually capitalized-->
    <!--<typeAlias type="com.zpc.mybatis.pojo.User" alias="User"/>-->
    <package name="com.zpc.mybatis.pojo"/>
</typeAliases>

Mybatis has built in many type aliases for common Java types.They are both case insensitive.

8,4, typeHandlers

Whether MyBatis sets a parameter in a PreparedStatement or fetches a value from a result set, the type processor converts the obtained value into a Java type in the appropriate way.You can override a type processor or create your own to handle unsupported or nonstandard types.

8.5.plugins interceptor

MyBatis allows you to intercept calls at a point in the execution of mapped statements.By default, MyBatis allows plug-ins to intercept method calls including:
Executor (update, query, flushStatements, commit, rollback, getTransaction, close, isClosed)
ParameterHandler (getParameterObject, setParameters)
ResultSetHandler (handleResultSets, handleOutputParameters)
StatementHandler (prepare, parameterize, batch, update, query)

Now some MyBatis plug-ins, such as PageHelper, are based on this principle, and sometimes the plug-in mechanism can be used to monitor sql execution efficiency.
Principle:

// ExamplePlugin.java
@Intercepts({@Signature(
  type= Executor.class,
  method = "update",
  args = {MappedStatement.class,Object.class})})
public class ExamplePlugin implements Interceptor {
  public Object intercept(Invocation invocation) throws Throwable {
    return invocation.proceed();
  }
  public Object plugin(Object target) {
    return Plugin.wrap(target, this);
  }
  public void setProperties(Properties properties) {
  }
}

To configure:

<!-- mybatis-config.xml -->
<plugins>
  <plugin interceptor="org.mybatis.example.ExamplePlugin">
    <property name="someProperty" value="100"/>
  </plugin>
</plugins>

The plug-in above will block all "update" method calls in the Executor instance, where Executor is the internal object responsible for executing low-level mapping statements.

8.6.environments (environment)

MyBatis can be configured to accommodate a variety of environments, such as development, testing, and production environments that require different configurations;
Although multiple environments can be configured, each SqlSessionFactory instance can only choose one.
Although this is also a convenient way to separate multiple environments, in real-world scenarios, we prefer to use spring to manage data sources to separate environments.

8.7.mappers

You need to tell MyBatis where to find the SQL mapping statement.That is, tell MyBatis where to find the mapping file.You can use resource references relative to the class path, or fully qualify resource locators (including the URL of file://), class names, package names, and so on.For example:

<!-- Use resource references relative to class paths -->
<mappers>
  <mapper resource="org/mybatis/builder/AuthorMapper.xml"/>
  <mapper resource="org/mybatis/builder/BlogMapper.xml"/>
  <mapper resource="org/mybatis/builder/PostMapper.xml"/>
</mappers>

<!-- Implement fully qualified class names for classes using mapper interfaces -->
<mappers>
  <mapper class="org.mybatis.builder.AuthorMapper"/>
  <mapper class="org.mybatis.builder.BlogMapper"/>
  <mapper class="org.mybatis.builder.PostMapper"/>
</mappers>

The so-called mapper interface path here.This is actually the dao's interface path.In mybatis, Dao packages are often called mappers.Class name, also known as mapper

  1. Define an interface.
  2. Define in the package where the interface is locatedMapper.xmlAnd requires that the xml file and the interface have the same name.
  3. In mybatis-Config.xmlMapper is introduced through the class path in.RequirementMapper.xmlThe namespace in is the full path to the class's interface.

Notes:

<mappers>
    <mapper resource="mappers/MyMapper.xml"/>
    <mapper resource="mappers/UserDaoMapper.xml"/>
    <!--Notes can be configured in the following ways-->
    <mapper class="com.zpc.mybatis.dao.UserMapper"/>
</mappers>

Question:

  1. Mapper.xmlAnd java files are not separated.Later tutorials are described and integrated with spring.
  2. One mapper needs to be unloaded.

Of course, you can also use package scanning (you must use annotations, that is, annotations on interface methods, such as @Select("select * from tb_user')):
Disadvantages:

  1. If there are many paths to the package?
  2. Mapper.xmlandMapper.javaNo separation.
  • Resolve when spring integrates.

9.Mapper XML file details

9.1.CRUD Tags

9.1.1.select

select - Write a query sql statement
Several properties in select describe:
id attribute: The unique identity of the statement under the current namespace.Must.Requires the method name in the id and mapper interfaces to be the same.
resultType: Maps the result set to a java object type.Must (alternative to resultMap)
parameterType: The type of parameter passed in.Can be omitted

9.1.2.insert

Several properties of insert describe:
id: unique identity, freely written, unique under the same namespace, consistent with method name after using dynamic proxy
parameterType: The type of parameter that is identical to the method's parameter type after using a dynamic proxy
useGeneratedKeys: Turn on primary key writeback
keyColumn: Specifies the primary key for the database
keyProperty: The name of the pojo property corresponding to the primary key
Inside tag: specific sql statement.

9.1.3.update

id attribute: the unique identity (required attribute) of the statement under the current namespace;
parameterType: The type of parameter passed in, which can be omitted.
Inside tag: specific sql statement.

9.1.4.delete

Several properties of delete explain:
id attribute: the unique identity (required attribute) of the statement under the current namespace;
parameterType: The type of parameter passed in, which can be omitted.
Inside tag: specific sql statement.

9.2. #{} and ${}

Scenario: A database has two identical tables.History Table, Current Table
Query information in tables, sometimes querying data from history tables, and sometimes querying data from new tables.
You want to use a method to complete the operation.

<select id="queryUserByTableName" resultType="com.zpc.mybatis.pojo.User">
    select * from #{tableName}
</select>

/**
 * Query user information based on table name (specify incoming parameter name directly using annotations)
 *
 * @param tableName
 * @return
 */
public List<User> queryUserByTableName(String tableName);

Test output:

Problem reporting syntax error: equivalent to executing such a sql:
select * from "tb_user";
Apparently there are more quotes for table names.

  • Correct:
<select id="queryUserByTableName" resultType="com.zpc.mybatis.pojo.User">
    select * from ${tableName}
</select>

Be careful:

{} Just a replacement?, which is equivalent to PreparedStatement replacing parameters with placeholders to prevent sql injection.

${} is a string stitching equivalent to a Statement in an SQL statement that uses a string to stitch sql; $can be part of SQL passed into the Statement and SQL injection cannot be prevented.

Use ${} to fetch parameter value information, use ${value}

{} is just a placeholder, independent of the name of the parameter. If there is only one parameter, it will automatically correspond.

Recommend:

/**
 * Query user information based on table name (specify incoming parameter name directly using annotations)
 *
 * @param tableName
 * @return
 */
public List<User> queryUserByTableName(@Param("tableName") String tableName);

<select id="queryUserByTableName" resultType="com.zpc.mybatis.pojo.User">
    select * from ${tableName}
</select>

When multiple parameters are {}:

/**
 * Login (specify the name of the incoming parameter directly with the comment)
 *
 * @param userName
 * @param password
 * @return
 */
public User login( String userName, String password);

<select id="login" resultType="com.zpc.mybatis.pojo.User">
    select * from tb_user where user_name = #{userName} and password = #{password}
</select>

Report errors:

org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: org.apache.ibatis.binding.BindingException: Parameter 'userName' not found. Available parameters are [0, 1, param1, param2]
### Cause: org.apache.ibatis.binding.BindingException: Parameter 'userName' not found. Available parameters are [0, 1, param1, param2]

Solution 1:

<select id="login" resultType="com.zpc.mybatis.pojo.User">
    select * from tb_user where user_name = #{0} and password = #{1}
</select>

Solution 2:

<select id="login" resultType="com.zpc.mybatis.pojo.User">
    select * from tb_user where user_name = #{param1} and password = #{param2}
</select>

Final solution:

/**
 * Login (specify the name of the incoming parameter directly with the comment)
 *
 * @param userName
 * @param password
 * @return
 */
public User login(@Param("userName") String userName, @Param("password") String password);

<select id="login" resultType="com.zpc.mybatis.pojo.User">
    select * from tb_user where user_name = #{userName} and password = #{password}
</select>

Typically, a comment @Param("xxxx") is added to the method's parameter list to explicitly specify the name of the parameter, then ${"xxxx"} or #{"xxxx"}
Use ${} when sql statements are generated dynamically;
#{} while a parameter is taking place in an sql statement

9.3. Interview Questions (#, $Difference)

/**
 * #Number
 * @param username1
 * @return
 */
User queryUserListByName1(@Param("username1") String username1);

/**
 * $Number
 * @param username2
 * @return
 */
User queryUserListByName2(@Param("username2") String username2);

<select id="queryUserListByName1" resultType="com.zpc.mybatis.pojo.User">
    select * from tb_user WHERE user_name=#{username1}
</select>

<select id="queryUserListByName2" resultType="com.zpc.mybatis.pojo.User">
    select * from tb_user WHERE user_name='${username2}'//Manually quoted
</select>

9.4.resultMap

Use:

9.5.sql fragment

<sql id=""></sql>
<include refId="" />

For example UserMapper.xml The following fragments are defined in:

<sql id="commonSql">
                id,
            user_name,
            password,
            name,
            age,
            sex,
            birthday,
            created,
            updated    
</sql>

Then you can UserMapper.xml Use it:

<select id="queryUserById" resultMap="userResultMap">
    select <include refid="commonSql"></include> from tb_user where id = #{id}
</select>

<select id="queryUsersLikeUserName" resultType="User">
    select <include refid="commonSql"></include> from tb_user where user_name like "%"#{userName}"%"
</select>

Sql fragments can also be defined in separate.xml files such as:
Definition CommonSQL.xml:

<?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="CommonSQL">
    <sql id="commonSql">
        id,
            user_name,
            password,
            name,
            age,
            sex,
            birthday,
            created,
            updated    
    </sql>
</mapper>

Use:

    <select id="queryUserById" resultMap="userResultMap">
        select <include refid="CommonSQL.commonSql"></include> from tb_user where id = #{id}
    </select>
    
    <select id="queryUsersLikeUserName" resultType="User">
        select <include refid="CommonSQL.commonSql"></include> from tb_user where user_name like "%"#{userName}"%"
    </select>

Of course, the global configuration file mybatis-Config.xmlThis external profile was introduced in:

<mappers>
        <mapper resource="CommonSQL.xml"/>
        <!--Turn on package scanning for mapper interface, class-based configuration-->
        <package name="com.zpc.mybatis.mapper"/>
</mappers>

10. Dynamic sql

Scenario: Query male users, if a name is entered, by name fuzzy query

10.1.if

Scenario: Query male users, if a name is entered, by name

Define interfaces:

/**
 * Query male users, if a name is entered, by name
 * @param name
 * @return
 */
List<User> queryUserList(@Param("name") String name);

Write mapper

<select id="queryUserList" resultType="com.zpc.mybatis.pojo.User">
    select * from tb_user WHERE sex=1
    <if test="name!=null and name.trim()!=''">
      and name like '%${name}%'
    </if>
</select>

Test:

@Test
public void testqueryUserList() {
    List<User> users = this.userMapper.queryUserList(null);
    for (User user : users) {
        System.out.println(user);
    }
}

10.2.choose when otherwise

Scenario: Query male users, if a name is entered, it will be searched by name ambiguously, otherwise if an age is entered, it will be searched by age, otherwise, it will be searched by user with name "Peng Cheng".

Define interfaces:

/**
 * Query male users, if a name is entered, it will be searched vaguely by name. Otherwise, if an age is entered, it will be searched by age. Otherwise, it will be searched for a user named Peng Cheng.
 * @param name
 * @param age
 * @return
 */
List<User> queryUserListByNameOrAge(@Param("name") String name,@Param("age") Integer age);

Write the mapper configuration:

<select id="queryUserListByNameOrAge" resultType="com.zpc.mybatis.pojo.User">
    select * from tb_user WHERE sex=1
    <!--
    1.Once conditional when，Subsequent when Will not execute
    2.When all when When none is executed,Will execute otherwise
    -->
    <choose>
        <when test="name!=null and name.trim()!=''">
            and name like '%${name}%'
        </when>
        <when test="age!=null">
            and age = #{age}
        </when>
        <otherwise>
            and name='Peng Cheng'
        </otherwise>
    </choose>
</select>

Test:

@Test
public void queryUserListByNameOrAge() throws Exception {
    List<User> users = this.userMapper.queryUserListByNameOrAge(null, 16);
    for (User user : users) {
        System.out.println(user);
    }
}

10.3.where and set

Scenario 1: Query all users, if name is entered for fuzzy query, if age is entered for query, if both are entered, both conditions are valid.

Interface:

/**
 * Query all users, if name is entered for fuzzy query by name, if age is entered for query by age, if both are entered, both conditions are valid
 * @param name
 * @param age
 * @return
 */
List<User> queryUserListByNameAndAge(@Param("name") String name,@Param("age") Integer age);

To configure:

<select id="queryUserListByNameAndAge" resultType="com.zpc.mybatis.pojo.User">
    select * from tb_user
    <!--If there is one more and，Will be automatically removed if missing and Or more than one and Error will occur-->
    <where>
        <if test="name!=null and name.trim()!=''">
            and name like '%${name}%'
        </if>
        <if test="age!=null">
            and age = #{age}
        </if>
    </where>
</select>

Test:

@Test
public void queryUserListByNameAndAge() throws Exception {
    List<User> users = this.userMapper.queryUserListByNameAndAge("Peng Cheng", 20);
    for (User user : users) {
        System.out.println(user);
    }
}

Scenario 2: Modify the user information, but do not if an attribute in the parameter user is null.
Interface:

/**
 * Update user information based on id
 *
 * @param user
 */
public void updateUser(User user);

To configure:

<update id="updateUser" parameterType="com.zpc.mybatis.pojo.User">
    UPDATE tb_user
    <trim prefix="set" suffixOverrides=",">
        <if test="userName!=null">user_name = #{userName},</if>
        <if test="password!=null">password = #{password},</if>
        <if test="name!=null">name = #{name},</if>
        <if test="age!=null">age = #{age},</if>
        <if test="sex!=null">sex = #{sex},</if>
        <if test="birthday!=null">birthday = #{birthday},</if>
        updated = now(),
    </trim>
    WHERE
    (id = #{id});
</update>

Test:

@Test
public void testUpdateUser() {
    User user = new User();
    user.setBirthday(new Date());
    user.setName("Quiet");
    user.setPassword("123456");
    user.setSex(0);
    user.setUserName("Jinjin");
    user.setId("1");
    this.userMapper.updateUser(user);
}

10.4.foreach

Scenario: Query user information by multiple IDS

Interface:

/**
 * Query by multiple Id s
 * @param ids
 * @return
 */
List<User> queryUserListByIds(@Param("ids") String[] ids);

To configure:

<select id="queryUserListByIds" resultType="com.zpc.mybatis.pojo.User">
    select * from tb_user where id in
    <foreach collection="ids" item="id" open="(" close=")" separator=",">
        #{id}
    </foreach>
</select>

Test:

@Test
public void queryUserListByIds() throws Exception {
    List<User> users = this.userMapper.queryUserListByIds(new String[]{"1","2"});
    for (User user : users) {
        System.out.println(user);
    }
}

If:testognl expression or simple java code
Choose when otherwise - > equivalent to if else if else
When test reference if
Where set has some error correction capabilities
Trim: prefix suffix prefixOverrides suffixOverrides
Foreach: collection item saparator open close

11. Caching

11.1. Level 1 Cache

In mybatis, first-level caches are turned on by default and cannot be turned off

Level 1 cache meets the criteria:
1. In the same session
2. Same SQL and parameters

Test:

@Test
public void testQueryUserById() {
    System.out.println(this.userMapper.queryUserById("1"));
    System.out.println(this.userMapper.queryUserById("1"));
}
2018-07-01 17:08:50,156 [main] [org.apache.ibatis.transaction.jdbc.JdbcTransaction]-[DEBUG] Opening JDBC Connection
2018-07-01 17:08:50,421 [main] [org.apache.ibatis.datasource.pooled.PooledDataSource]-[DEBUG] Created connection 242355057.
2018-07-01 17:08:50,423 [main] [com.zpc.mybatis.dao.UserMapper.queryUserById]-[DEBUG] ==>  Preparing: select * from tb_user where id = ? 
2018-07-01 17:08:50,476 [main] [com.zpc.mybatis.dao.UserMapper.queryUserById]-[DEBUG] ==> Parameters: 1(String)
2018-07-01 17:08:50,509 [main] [com.zpc.mybatis.dao.UserMapper.queryUserById]-[DEBUG] <==      Total: 1
User{id='1', userName='bigGod222', password='123456', name='Peng Cheng', age=20, sex=1, birthday='2018-07-01', created='2018-07-01 13:35:40.0', updated='2018-07-01 13:35:40.0'}
User{id='1', userName='bigGod222', password='123456', name='Peng Cheng', age=20, sex=1, birthday='2018-07-01', created='2018-07-01 13:35:40.0', updated='2018-07-01 13:35:40.0'}

Use:sqlSession.clearCache(); Cache can be forcibly cleared

Test:

@Test
public void testQueryUserById() {
    System.out.println(this.userMapper.queryUserById("1"));
    sqlSession.clearCache();
    System.out.println(this.userMapper.queryUserById("1"));
}

Journal:

2018-07-01 17:10:51,065 [main] [org.apache.ibatis.transaction.jdbc.JdbcTransaction]-[DEBUG] Opening JDBC Connection
2018-07-01 17:10:51,359 [main] [org.apache.ibatis.datasource.pooled.PooledDataSource]-[DEBUG] Created connection 242355057.
2018-07-01 17:10:51,360 [main] [com.zpc.mybatis.dao.UserMapper.queryUserById]-[DEBUG] ==>  Preparing: select * from tb_user where id = ? 
2018-07-01 17:10:51,408 [main] [com.zpc.mybatis.dao.UserMapper.queryUserById]-[DEBUG] ==> Parameters: 1(String)
2018-07-01 17:10:51,437 [main] [com.zpc.mybatis.dao.UserMapper.queryUserById]-[DEBUG] <==      Total: 1
User{id='1', userName='bigGod222', password='123456', name='Peng Cheng', age=20, sex=1, birthday='2018-07-01', created='2018-07-01 13:35:40.0', updated='2018-07-01 13:35:40.0'}
2018-07-01 17:10:51,438 [main] [com.zpc.mybatis.dao.UserMapper.queryUserById]-[DEBUG] ==>  Preparing: select * from tb_user where id = ? 
2018-07-01 17:10:51,438 [main] [com.zpc.mybatis.dao.UserMapper.queryUserById]-[DEBUG] ==> Parameters: 1(String)
2018-07-01 17:10:51,440 [main] [com.zpc.mybatis.dao.UserMapper.queryUserById]-[DEBUG] <==      Total: 1
User{id='1', userName='bigGod222', password='123456', name='Peng Cheng', age=20, sex=1, birthday='2018-07-01', created='2018-07-01 13:35:40.0', updated='2018-07-01 13:35:40.0'}

When update, insert, delete is executed, the cache is emptied
Test:

@Test
public void testQueryUserById() {
    System.out.println(this.userMapper.queryUserById("1"));
    //sqlSession.clearCache();

    User user=new User();
    user.setName("Beauty");
    user.setId("1");
    userMapper.updateUser(user);

    System.out.println(this.userMapper.queryUserById("1"));
}

Journal:

2018-07-01 17:18:15,128 [main] [org.apache.ibatis.transaction.jdbc.JdbcTransaction]-[DEBUG] Opening JDBC Connection
2018-07-01 17:18:15,399 [main] [org.apache.ibatis.datasource.pooled.PooledDataSource]-[DEBUG] Created connection 242355057.
2018-07-01 17:18:15,401 [main] [com.zpc.mybatis.dao.UserMapper.queryUserById]-[DEBUG] ==>  Preparing: select * from tb_user where id = ? 
2018-07-01 17:18:15,466 [main] [com.zpc.mybatis.dao.UserMapper.queryUserById]-[DEBUG] ==> Parameters: 1(String)
2018-07-01 17:18:15,492 [main] [com.zpc.mybatis.dao.UserMapper.queryUserById]-[DEBUG] <==      Total: 1
User{id='1', userName='bigGod222', password='123456', name='Peng Cheng', age=20, sex=1, birthday='2018-07-01', created='2018-07-01 13:35:40.0', updated='2018-07-01 13:35:40.0'}
2018-07-01 17:18:15,527 [main] [com.zpc.mybatis.dao.UserMapper.updateUser]-[DEBUG] ==>  Preparing: UPDATE tb_user set name = ?, updated = now() WHERE (id = ?); 
2018-07-01 17:18:15,529 [main] [com.zpc.mybatis.dao.UserMapper.updateUser]-[DEBUG] ==> Parameters: Beauty(String), 1(String)
2018-07-01 17:18:15,532 [main] [com.zpc.mybatis.dao.UserMapper.updateUser]-[DEBUG] <==    Updates: 1
2018-07-01 17:18:15,532 [main] [com.zpc.mybatis.dao.UserMapper.queryUserById]-[DEBUG] ==>  Preparing: select * from tb_user where id = ? 
2018-07-01 17:18:15,533 [main] [com.zpc.mybatis.dao.UserMapper.queryUserById]-[DEBUG] ==> Parameters: 1(String)
2018-07-01 17:18:15,538 [main] [com.zpc.mybatis.dao.UserMapper.queryUserById]-[DEBUG] <==      Total: 1
User{id='1', userName='bigGod222', password='123456', name='Beauty', age=20, sex=1, birthday='2018-07-01', created='2018-07-01 13:35:40.0', updated='2018-07-01 17:18:15.0'}

11.2. Secondary Cache

The scope of mybatis's secondary cache is the namespace of a mapper, and querying sql in the same namespace can be hit from the cache.

Turn on the secondary cache:

<mapper namespace="com.zpc.mybatis.dao.UserMapper">
    <cache/>
</mapper>

Test:

@Test
public void testCache() {
    System.out.println(this.userMapper.queryUserById("1"));

    sqlSession.close();
    SqlSession sqlSession = sqlSessionFactory.openSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);

    System.out.println(mapper.queryUserById("1"));
}

To turn on a secondary cache, you must serialize:

public class User implements Serializable{
    private static final long serialVersionUID = -3330851033429007657L;

Journal:

2018-07-01 17:23:39,335 [main] [org.apache.ibatis.transaction.jdbc.JdbcTransaction]-[DEBUG] Opening JDBC Connection
2018-07-01 17:23:39,664 [main] [org.apache.ibatis.datasource.pooled.PooledDataSource]-[DEBUG] Created connection 2092769598.
2018-07-01 17:23:39,665 [main] [com.zpc.mybatis.dao.UserMapper.queryUserById]-[DEBUG] ==>  Preparing: select * from tb_user where id = ? 
2018-07-01 17:23:39,712 [main] [com.zpc.mybatis.dao.UserMapper.queryUserById]-[DEBUG] ==> Parameters: 1(String)
2018-07-01 17:23:39,734 [main] [com.zpc.mybatis.dao.UserMapper.queryUserById]-[DEBUG] <==      Total: 1
User{id='1', userName='bigGod222', password='123456', name='Beauty', age=20, sex=1, birthday='2018-07-01', created='2018-07-01 13:35:40.0', updated='2018-07-01 17:18:15.0'}
2018-07-01 17:23:39,743 [main] [org.apache.ibatis.transaction.jdbc.JdbcTransaction]-[DEBUG] Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@7cbd213e]
2018-07-01 17:23:39,744 [main] [org.apache.ibatis.datasource.pooled.PooledDataSource]-[DEBUG] Returned connection 2092769598 to pool.
2018-07-01 17:23:39,746 [main] [com.zpc.mybatis.dao.UserMapper]-[DEBUG] Cache Hit Ratio [com.zpc.mybatis.dao.UserMapper]: 0.5
User{id='1', userName='bigGod222', password='123456', name='Beauty', age=20, sex=1, birthday='2018-07-01', created='2018-07-01 13:35:40.0', updated='2018-07-01 17:18:15.0'}

Turn off secondary caching:
Do not open, or mybatis-globallyConfig.xmlTo close the secondary cache

<settings>
    <!--Turn on hump matching-->
    <setting name="mapUnderscoreToCamelCase" value="true"/>
    <!--Turn on the secondary cache, global global switch, turn off here, turn on in mapper is useless-->
    <setting name="cacheEnabled" value="false"/>
</settings>

12. Advanced Query

12.1. Description of table relationships

Create the order table:
CREATE TABLE tb_order (
id int(11) NOT NULL AUTO_INCREMENT,
user_id int(11) DEFAULT NULL,
order_number varchar(255) DEFAULT NULL,
create datetime DEFAULT NULL,
updated datetime DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

public class Order {
    private Integer id;
    private Long userId;
    private String orderNumber;
    private Date created;
    private Date updated;
}

Requirement Description:

12.2. One-to-one queries

Method One: Core Ideas Extend Order Objects to Complete Mapping
New OrderUser entity class inherits Order:

public class OrderUser extends Order {
    private String userName;
    private String password;
    private String name;
    private Integer age;
    private Integer sex;
    private Date birthday;
    private Date created;
    private Date updated;
}

OrderMapper interface:

public interface OrderMapper {
     OrderUser queryOrderUserByOrderNumber(@Param("number") String number);
}

Configure OrderMapper:

 <mapper namespace="com.zpc.mybatis.dao.OrderMapper">
    <select id="queryOrderUserByOrderNumber" resultType="com.zpc.mybatis.pojo.OrderUser">
      select * from tb_order o left join tb_user u on o.user_id=u.id where o.order_number = #{number}
   </select>
</mapper>

Test:

@Test
public void queryOrderUserByOrderNumber() throws Exception {
    OrderUser orderUser = orderMapper.queryOrderUserByOrderNumber("201807010001");
    System.out.println(orderUser);
}

Method 2: Object-oriented idea, adding User object to Order object.

Add the User property to the Order object:

public class Order {
    private Integer id;
    private Long userId;
    private String orderNumber;
    private Date created;
    private Date updated;
    private User user;
}

Interface:

/**
 * Query order user information based on order number
 * @param number
 * @return
 */
Order queryOrderWithUserByOrderNumber(@Param("number") String number);

Auto-mapping cannot be completed using resultType. Result set mapping resultMap needs to be completed manually:

 <resultMap id="OrderUserResultMap" type="com.zpc.mybatis.pojo.Order" autoMapping="true">
     <id column="id" property="id"/>
     <!--association:Complete mapping of child objects-->
     <!--property:Property name of child object in parent object-->
     <!--javaType:Subobject java type-->
     <!--autoMapping:Completes the automatic mapping of child objects, matches by hump if hump is turned on-->
     <association property="user" javaType="com.zpc.mybatis.pojo.User" autoMapping="true">
         <id column="user_id" property="id"/>
     </association>
 </resultMap>

 <select id="queryOrderWithUserByOrderNumber" resultMap="OrderUserResultMap">
   select * from tb_order o left join tb_user u on o.user_id=u.id where o.order_number = #{number}
</select>

Test:

@Test
public void queryOrderWithUserByOrderNumber() throws Exception {
    Order order = orderMapper.queryOrderWithUserByOrderNumber("201807010001");
    System.out.println(order.getUser());
}

12.3. One-to-many queries

One-to-many query: query orders, query the information of the person who placed the order, and query the details of the order.

Order class:

public class Order {
    private Integer id;
    private Long userId;
    private String orderNumber;
    private Date created;
    private Date updated;
    private User user;
    private List<OrderDetail> detailList;
}
public class OrderDetail {
    private Integer id;
    private Integer orderId;
    private Double totalPrice;
    private Integer status;
}

Interface:

/**
 * Query order user information and order details based on order number
 * @param number
 * @return
 */
Order queryOrderWithUserAndDetailByOrderNumber(@Param("number") String number);

Mapper Mapping:

<resultMap id="OrderUserDetailResultMap" type="com.zpc.mybatis.pojo.Order" autoMapping="true">
    <id column="id" property="id"/>
    <!--collection:Define Subobject Collection Mapping-->
    <!--association:Complete mapping of child objects-->
    <!--property:Property name of child object in parent object-->
    <!--javaType:Subobject java type-->
    <!--autoMapping:Completes the automatic mapping of child objects, matches by hump if hump is turned on-->
    <association property="user" javaType="com.zpc.mybatis.pojo.User" autoMapping="true">
        <id column="user_id" property="id"/>
    </association>
    <collection property="detailList" javaType="List" ofType="com.zpc.mybatis.pojo.OrderDetail" autoMapping="true">
        <id column="id" property="id"/>
    </collection>
</resultMap>

 <select id="queryOrderWithUserAndDetailByOrderNumber" resultMap="OrderUserDetailResultMap">
   select * from tb_order o
   left join tb_user u on o.user_id=u.id
   left join tb_orderdetail od on o.id=od.order_id
   where o.order_number = #{number}
</select>

Test:

@Test
public void queryOrderWithUserAndDetailByOrderNumber() throws Exception {
    Order order = orderMapper.queryOrderWithUserAndDetailByOrderNumber("201807010001");
    System.out.println(order.getUser());
    System.out.println(order.getDetailList());
}

12.4. Many-to-many queries

Many-to-many query: query orders, query the information of the person who placed the order, and query the commodity data in the order details.

OrderDetail class

public class OrderDetail {
    private Integer id;
    private Integer orderId;
    private Double totalPrice;
    private Integer status;
    private Item item;
}

public class Item {
    private Integer id;
    private String itemName;
    private Float itemPrice;
    private String itemDetail;
}

Interface:

/**
 * Query order user's information, order details and corresponding commodity information according to order number
 * @param number
 * @return
 */
Order queryOrderWithUserAndDetailItemByOrderNumber(@Param("number") String number);

Mapper configuration:

<resultMap id="OrderUserDetailItemResultMap" type="com.zpc.mybatis.pojo.Order" autoMapping="true">
    <id column="id" property="id"/>
    <association property="user" javaType="com.zpc.mybatis.pojo.User" autoMapping="true">
        <id column="user_id" property="id"/>
    </association>
    <collection property="detailList" javaType="List" ofType="com.zpc.mybatis.pojo.OrderDetail" autoMapping="true">
        <id column="detail_id" property="id"/>
        <association property="item" javaType="com.zpc.mybatis.pojo.Item" autoMapping="true">
            <id column="item_id" property="id"/>
        </association>
    </collection>
</resultMap>

 <select id="queryOrderWithUserAndDetailItemByOrderNumber" resultMap="OrderUserDetailItemResultMap">
   select * ,od.id as detail_id from tb_order o
   left join tb_user u on o.user_id=u.id
   left join tb_orderdetail od on o.id=od.order_id
   left join tb_item i on od.item_id=i.id
   where o.order_number = #{number}
</select>

Test:

@Test
public void queryOrderWithUserAndDetailItemByOrderNumber() throws Exception {
    Order order = orderMapper.queryOrderWithUserAndDetailItemByOrderNumber("201807010001");
    System.out.println(order);
    System.out.println(order.getUser());
    System.out.println(order.getDetailList());
}

At this point, the directory structure is as follows:

Database script:
CREATE TABLE tb_order (
id int(11) NOT NULL AUTO_INCREMENT,
user_id int(11) DEFAULT NULL,
order_number varchar(255) DEFAULT NULL,
create datetime DEFAULT NULL,
updated datetime DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
INSERT INTO tb_order VALUES ('1', '2', '201807010001', '2018-07-01 19:38:35', '2018-07-01 19:38:40');

CREATE TABLE tb_item (
id int(11) NOT NULL,
itemName varchar(255) DEFAULT NULL,
itemPrice decimal(10,2) DEFAULT NULL,
itemDetail varchar(255) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO tb_item VALUES ('1', 'Socks', '29.90', 'Fragrant socks');
INSERT INTO tb_item VALUES ('2', 'Cover', '99.99', 'Okoben 001');

CREATE TABLE tb_orderdetail (
id int(11) NOT NULL AUTO_INCREMENT,
order_id int(11) DEFAULT NULL,
total_price decimal(10,0) DEFAULT NULL,
item_id int(11) DEFAULT NULL,
status int(10) unsigned zerofill DEFAULT NULL COMMENT '0 Success is not zero failure',
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
INSERT INTO tb_orderdetail VALUES ('1', '1', '10000', '1', '0000000001');
INSERT INTO tb_orderdetail VALUES ('2', '1', '2000', '2', '0000000000');

12.5. Inheritance of resultMap

12.6. Organizing advanced queries

The resutlType does not help us automatically complete the mapping, so we only use resultMap to map manually.
The unique identification of the data type id corresponding to the type result set, specified when referenced.

<resultMap type="Order" id="orderUserLazyResultMap">
<! - Defines the property of a single object in pojo Defines the property name of the object, the type of the javaType property,
        <association property="user" javaType="User" autoMapping="true">
            <id />
        </association>
<! - If an attribute is a collection using collection, the type of javaType collection, ofType represents the type of element stored in the collection
        <collection property="details" javaType="List" ofType="OrderDetail" autoMapping="true">
            <id />
</resultMap>

13. Delayed Loading

Follow-up Supplement

14. If a'<'solution appears in the sql statement

14,1, using character entities in xml

Because business requires greater than and less than signs in mybatis, they are used directly in SQL.

  • SELECT * FROM test WHERE 1 = 1 AND start_date <= CURRENT_DATE AND end_date >= CURRENT_DATE

However, when executed, errors are always reported:
`Error creation document instance. Cause:Org.xml.sax.SAXParseException; lineNumber: 74; columnNumber: 17; Element content must consist of well-formed character data or tags.
AND start_Date >= CURRENT_DATE AND end_Date <= CURRENT_When DATE is removed, there is no problem, so it is determined that the problem is caused by the greater than sign and the less than sign.

Then special symbols come to mind, and the escape characters are used to replace > and < and there is no problem.

  • SELECT * FROM test WHERE 1 = 1 AND start_date <= CURRENT_DATE AND end_date >= CURRENT_DATE

Case:

1.<if test="startDateTime!=null"> and mm.ttime &gt; to_date(#{startDateTime},'yyyy-mm-dd hh24:mi:ss')</if>  
2.<if test="endDateTime!=null"> and mm.ttime &lt;= to_date(#{endDateTime},'yyyy-mm-dd hh24:mi:ss')</if>

14,2, use <! [CDATA[<]]>

Case 1:

<![CDATA[ 
       and mm.ttime > to_date(#{startDateTime},'yyyy-mm-dd hh24:mi:ss') 
      and mm.ttime <= to_date(#{endDateTime},'yyyy-mm-dd hh24:mi:ss') 
]]>

Case 2:

Sample code for mapper file:

and (t1.status <![CDATA[ >= ]]> 1  and  t1.status <![CDATA[ <= ]]> 2)
//The above code actually corresponds to sql:
and (t1.status > =1 andt1.status <= 2)

Be careful:

Labels such as <where> <if> in sql statements marked with <![CDATA[]]> will not be resolved.

All content in the CDATA section is ignored by the parser.
The CDATA section starts with'<![CDATA['and ends with']]>'

Posted by bambambanana on Sat, 13 Jun 2020 10:41:23 -0700

Hot Keywords

©2024 Programmer Group