Mybatis

Official document: [official document of Mybatis]( Introduction to mybatis – MyBatis 3)

1. Introduction

1.1 what is Mybatis

• MyBatis is an excellent persistence layer framework.
• It supports custom SQL, stored procedures, and advanced mapping.
• MyBatis eliminates almost all JDBC code and the work of setting parameters and obtaining result sets.
• MyBatis can configure and map primitive types, interfaces and Java POJO s (Plain Old Java Objects) to records in the database through simple XML or annotations.

How to get Mybatis

• maven warehouse [Mybatis_Maben]( Maven Repository: mybatis (mvnrepository.com))

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

• GitHub: Mybatis_GitHub

1.2 persistence

Data persistence

• Persistence is the process of transforming program data in persistent state and transient state

  Memory: loss upon power failure

  Database (jdbc), io file persistence.

1.3 durable layer

• Code block that completes the persistence work
• The layer boundary is very obvious

1.4 why do you need Mybatis

• Convenient, the traditional JDBC code is very complex, in order to simplify the emergence of the framework, automation
• Help programmers store data into the database.
• advantage
  • Easy to learn and used by many people.
  • flexible
  • The separation of sql and code improves maintainability.
  • Provide mapping labels to support the mapping of orm fields between objects and databases
  • Provide object relationship mapping labels to support object relationship construction and maintenance
  • Provide xml tags to support writing dynamic sql.

2. The first Mybatis program

2.1 construction environment

Build database

CREATE DATABASE `mybatis`;

USE `mybatis`;

CREATE TABLE `user`(
`id` INT(20) NOT NULL,
`name` VARCHAR(30) DEFAULT NULL,
`pwd` VARCHAR(30) DEFAULT NULL,
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO `user`(`id`,`name`,`pwd`) VALUES
(1,'aaaa','1a1a1a'),
(2,'bbbb','2b2b2b'),
(3,'cccc','3c3c3c')

New project

1. Create a normal maven project

2. Delete src directory

3. Import maven dependencies

   <!--  Parent project  -->
       <groupId>org.xingye</groupId>
       <artifactId>test04_mybatis</artifactId>
       <version>1.0-SNAPSHOT</version>
       <!--Import dependency-->
       <dependencies>
           <!--    mysql drive-->
           <dependency>
               <groupId>mysql</groupId>
               <artifactId>mysql-connector-java</artifactId>
               <version>8.0.25</version>
           </dependency>
           <!--    mybatis-->
           <dependency>
               <groupId>org.mybatis</groupId>
               <artifactId>mybatis</artifactId>
               <version>3.5.7</version>
           </dependency>
           <!--    junit-->
           <dependency>
               <groupId>junit</groupId>
               <artifactId>junit</artifactId>
               <version>4.13.2</version>
           </dependency>
       </dependencies>
   

2.2 create a module

• Write the core configuration file of mybatis

  Create a new file mybatis-config.xml in the resources file, and write the configuration content in it

  <?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">
  <!--Core profile-->
  <configuration>
      <environments default="development">
          <environment id="development">
              <transactionManager type="JDBC"/>
              <dataSource type="POOLED">
                  <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                  <property name="url" value="jdbc:mysql://localhost:3306/mybatis?useSSL=true&amp;useUnicode=true&amp;characterEncoding=UTF-8"/>
                  <property name="username" value="root"/>
                  <property name="password" value="123456"/>
              </dataSource>
          </environment>
      </environments>
      <mappers>
          <mapper resource="org/mybatis/example/BlogMapper.xml"/>
      </mappers>
  </configuration>
  

• Write a tool class for mybatis

  package com.xingye.utils;
  
  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;
  
  //sqlSessionFactory --> sqlSession
  public class MybatisUtils {
  
      private static SqlSessionFactory sqlSessionFactory;
  
      static{
          try {
              //Get sqlSessionFactory object using Mybatis
              String resource = "mybatis-config.xml";
              InputStream inputStream = Resources.getResourceAsStream(resource);
              sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
          } catch (IOException e) {
              e.printStackTrace();
          }
      }
  
      //Get an instance of SqlSession
      public static SqlSession getSqlSession(){
          return sqlSessionFactory.openSession();//SqlSession
      }
  
  }
  

2.3 coding

• Entity class

  package com.xingye.pojo;
  
  //Entity class
  public class User {
      private int id;
      private String name;
      private String pwd;
  
      public User(){
      }
  
      public User(int id, String name, String pwd) {
          this.id = id;
          this.name = name;
          this.pwd = pwd;
      }
  
      public int getId() {
          return id;
      }
  
      public void setId(int id) {
          this.id = id;
      }
  
      public String getName() {
          return name;
      }
  
      public void setName(String name) {
          this.name = name;
      }
  
      public String getPwd() {
          return pwd;
      }
  
      public void setPwd(String pwd) {
          this.pwd = pwd;
      }
  
      @Override
      public String toString() {
          return "User{" +
                  "id=" + id +
                  ", name='" + name + '\'' +
                  ", pwd='" + pwd + '\'' +
                  '}';
      }
  }
  

• Dao interface

  package com.xingye.dao;
  
  import com.xingye.pojo.User;
  
  import java.util.List;
  
  public interface UserDao{
      List<User> getUserList();
  }
  

• Interface implementation 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">
  <!--namespace Bind a corresponding Dao/Mapper Interface-->
  <mapper namespace="com.xingye.dao.UserDao">
  <!--    Query statement-->
      <select id="getUserList" resultType="com.xingye.pojo.User">
          select *
          from mybatis.user;
      </select>
  </mapper>
  

2.4 testing

Register mappers in the core configuration file, and add the following code in pom.xml file

<!--    stay build Medium configuration resources，To prevent our resource export from failing-->
<build>
    <resources>
        <resource>
            <directory>src/main/java</directory>
            <includes>
                <include>**/*.properties</include>
                <include>**/*.xml</include>
            </includes>
            <filtering>true</filtering>
        </resource>
        <resource>
            <directory>src/main/resources</directory>
            <includes>
                <include>**/*.properties</include>
                <include>**/*.xml</include>
            </includes>
            <filtering>true</filtering>
        </resource>
    </resources>
</build>

• Junit test

package com.xing.dao;

import com.xingye.dao.UserDao;
import com.xingye.pojo.User;
import com.xingye.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;

public class UserDaoTest {
    @Test
    public void test(){
        //1. Get sqlSession object
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        //SQL execution method 1: getMapper
        UserDao userDao = sqlSession.getMapper(UserDao.class);//Get the class object of Dao interface
        List<User> userList = userDao.getUserList();

        for(User user:userList){
            System.out.println(user);
        }

        //Close SqlSession
        sqlSession.close();
    }
}

Note:

1. The package name in the namespace should be consistent with the package name of Dao/Mapper interface

3,CRUD

Create, retrieve, update, and delete

3.1 select

Select, query statement:

• id: is the method in the corresponding namespace

• resultType: return value of SQL statement execution!

• parameterType: parameter type

1. Write interface

//Query user by ID
User getUserById(int id);

2. Write the sql statement in the corresponding mapper

<select id="getUserById" parameterType="int" resultType="com.xingye.pojo.User">
    select *
    from mybatis.user
    where id = #{id};
</select>

3. Testing

//Query user test by Id
@Test
public void getUserById(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();

    try {
        //SQL execution method 1: getMapper
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        User user = userDao.getUserById(1);
        System.out.println(user);

    } finally {
        //Close SqlSession
        sqlSession.close();
    }
}

3.2 insert

1. Write interface

//Insert a user
int addUser(User user);

2. Write the sql statement in the corresponding mapper

<!--  The properties in the object can be taken out directly  -->
<insert id="addUser" parameterType="com.xingye.pojo.User">
    insert into mybatis.user(id, name, pwd) value (#{id}, #{name}, #{pwd});
</insert>

3. Testing

@Test
public void addUser(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    try {
        //SQL execution method 1: getMapper
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        userDao.addUser(new User(4,"dddd","4444"));
        sqlSession.commit();//Commit transaction
    } finally {
        //Close SqlSession
        sqlSession.close();
    }
}

3.3 delete

1. Write interface

//Delete a user
int deleteUser(int id);

2. Write the sql statement in the corresponding mapper

<delete id="deleteUser" parameterType="int">
    delete from mybatis.user where id = #{id};
</delete>

3. Testing

@Test
public void deleteUser(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    try {
        //SQL execution method 1: getMapper
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        userDao.deleteUser(4);
        sqlSession.commit();//Commit transaction
    } finally {
        //Close SqlSession
        sqlSession.close();
    }
}

3.4 update

1. Write interface

//Modify user
int updateUser(User user);

2. Write the sql statement in the corresponding mapper

<update id="updateUser" parameterType="com.xingye.pojo.User">
    update mybatis.user set name = #{name},pwd=#{pwd} where id = #{id};
</update>

3. Testing

@Test
public void updataUser(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    try {
        //SQL execution method 1: getMapper
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        userDao.updateUser(new User(4,"eeee","5555"));
        sqlSession.commit();//Commit transaction
    } finally {
        //Close SqlSession
        sqlSession.close();
    }
}

Small Tips:

Ctrl+Shift + minus sign, collapse all methods

Ctrl+Shift + plus sign, expand all methods

Note:

Add, delete, or modify transactions that need to be committed.

3.5 Map

If we have too many entity classes or tables, fields or parameters in the database, we should consider using Map!

1. Write interface

//Using Map
int addUser2(Map<String,Object> map);

2. Write the sql statement in the corresponding mapper

<insert id="addUser2" parameterType="map">
    insert into mybatis.user(id, name, pwd) value (#{userId}, #{userName}, #{userPwd});
</insert>

3. Testing

//Map test
@Test
public void addUser2(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    try {
        //SQL execution method 1: getMapper
        UserDao userDao = sqlSession.getMapper(UserDao.class);

        Map<String,Object> map=new HashMap<String,Object>();
        map.put("userId",5);
        map.put("userName","qwer");
        map.put("userPwd","1234567");
        userDao.addUser2(map);
        sqlSession.commit();//Commit transaction
    } finally {
        //Close SqlSession
        sqlSession.close();
    }
}

Pass parameters from Map and directly get the key from sql!

Object transfer parameters, you can directly get the attribute of the object in sql!

If there is only one basic type parameter, you can get it directly from sql and omit it!

Use Map or annotation for multiple parameters!

3.6 fuzzy query

Use wildcards in sql splicing!

1. Write interface

//Fuzzy query
    List<User> getUserLike(String value);

2. Write the sql statement in the corresponding mapper

    <select id="getUserLike" resultType="com.xingye.pojo.User">
        select * from mybatis.user where name like concat("%",#{value},"%")
    </select>

3. Testing

//Fuzzy query test
    @Test
    public void getUserLike(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();

        try {
            //SQL execution method 1: getMapper
            UserDao userDao = sqlSession.getMapper(UserDao.class);//Get the class object of Dao interface
            List<User> userList = userDao.getUserLike("a");

            for (User user : userList) {
                System.out.println(user);
            }
        } finally {
            //Close SqlSession
            sqlSession.close();
        }
    }

Unfinished to be continued