Mybatis

1. Run the first mybatis program

1.1 import

<dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.7</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>

1.2 create a module

• Write mybatis core configuration file 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">
  <configuration>
      <environments default="development">
          <environment id="development">
              <transactionManager type="JDBC"/>
              <dataSource type="POOLED">
                  <property name="driver" value="com.mysql.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="root"/>
              </dataSource>
          </environment>
      </environments>
      <mappers>
          <mapper resource="UserMapper.xml"/>
      </mappers>
  </configuration>
  

• Write mybatis tool class

  public class MybatisUtils {
  
      private static SqlSessionFactory sqlSessionFactory ;
      static {//static is added to optimize performance, and the class is executed only once after it is created
          try {//The first step of using mybatis is to get the sqlsessionfactory object
              String resource = "mybatis-config.xml";
              InputStream inputStream = Resources.getResourceAsStream(resource);
              sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
          } catch (IOException e) {
              e.printStackTrace();
          }
      }
  
      public static SqlSession getSqlSession(){
          return sqlSessionFactory.openSession();
      }
  }
  

1.3 coding

• Entity class

  package com.ding.pojo;
  
  public class User {
      private int id;
      private String name;
      private String pwd;
  
      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;
      }
  
      public int getId() {
          return id;
      }
  
      public void setId(int id) {
          this.id = id;
      }
  
      public User() {
      }
  
      public User(int id, String name, String pwd) {
          this.id = id;
          this.name = name;
          this.pwd = pwd;
      }
  }
  
  

• Dao interface

  public interface UserDao {
  List<User> getUserList();
  }
  

• Interface implementation class (Mapper configuration file)

  Each Mapper needs to be registered in the core configuration file

<?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 Dao Interface-->
<mapper namespace="com.ding.dao.UserDao">
    <select id="getUserList" resultType="com.ding.pojo.User">
        select * from user;
    </select>
</mapper>

1.4 testing

Use Junit to test the source files

2. Addition, deletion, modification and query

2.1 namespace

The package name in the namespace must be consistent with the package name in the interface

2.2 select

Select and query statements

• id: is the method name in the corresponding namespace

• resultType: return value of Sql statement execution

• parameterType: parameter type

  1. Write interface

  User getUserById(int id);
  

  2. Write the sql statement in the corresponding mapper

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

  3. Testing

  SqlSession sqlSession = MybatisUtils.getSqlSession();
          UserMapper mapper = sqlSession.getMapper(UserMapper.class);
          System.out.println(mapper.getUserById(1).toString());
          sqlSession.close();
  

2.3 Insert

Add record statement

• id: is the method name in the corresponding namespace

• parameterType: parameter type

1. Write interface

<insert id="insertUser" parameterType="com.ding.pojo.User" >
        insert into user (id,name,pwd) values (#{id},#{name},#{pwd});
    </insert>

2. Write the sql statement corresponding to Mapper

<insert id="insertUser" parameterType="com.ding.pojo.User" >
        insert into user (id,name,pwd) values (#{id},#{name},#{pwd});
    </insert>

3. Testing

@Test
    public void test2(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        System.out.println(mapper.insertUser(new User(2,"xiaogang","2333")));
        sqlSession.commit();//Add, delete and modify transactions that need to be committed
        sqlSession.close();
    }

2.4 update

A statement that updates a record

• id: is the method name in the corresponding namespace

• parameterType: parameter type

1. Write interface

int updateUser(User u);

2. Write the sql statement corresponding to Mapper

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

3. Testing

@Test
    public void test3(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        System.out.println(mapper.updateUser(new User(2,"xiaod","23332323")));
        sqlSession.commit();//Add, delete and modify operations need to commit transactions
        sqlSession.close();
    }

2.5 delete

Delete a specific record

• id: is the method name in the corresponding namespace

• parameterType: parameter type

1. Write interface

int deleteUser(int id);

2. Write the sql statement corresponding to Mapper

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

3. Testing

@Test
    public void test4(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        System.out.println(mapper.deleteUser(1));
        sqlSession.commit();
        sqlSession.close();
    }

all in all add, delete, and modify transactions that need to be committed

2.6 map

Using map to transfer values

Assuming that there are too many entity classes or tables, fields or parameters in the database, we should consider using map

2.7 fuzzy query

1. When executing java code, pass wildcard%%

2. Use wildcards in sql splicing!

like "%"#{value}"%"

3. Configuration analysis

3.1. Core configuration file

• mybatis-config.xml

  properties(Properties)
  settings(Settings)
  typeAliases(Type alias)
  typeHandlers(Type (processor)
  objectFactory(Object factory)
  plugins(Plug in)
  environments(Environment configuration)
  environment(Environment variables)
  transactionManager(Transaction manager)
  dataSource((data source)
  databaseIdProvider(Database (vendor ID)
  mappers(Mapper)
  

3.2. Environment configuration

Although multiple environments can be configured, only one environment can be selected for each sqlsessionfactory instance

Learn to use and configure multiple sets of operating environments

The default transaction manager of Mybatis is JDBC, and the connection pool is POOLED

3.3. properties

You can reference the configuration file through the properties property

These properties are externally configurable and can be dynamically replaced. They can be configured in a typical java property file or passed through the child elements of the properties element [db.properties]

Write a configuration file

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybatis?useSSL=false&useUnicode=true&characterEncoding=UTF-8
username=root
password=root

Import in the core configuration file (can only be placed in the front position in the configuration)

<properties resource="db.properties"/>

• You can import external files directly
• You can add some attribute configurations
• If two files have the same field, the external configuration file is preferred

3.4 type aliases

• A type alias is a short name for a java type

• The meaning of existence is only to reduce the redundancy of class fully qualified names

• <!--    You can alias an entity class-->
      <typeAliases>
          <typeAlias type="com.ding.pojo.User" alias="User"/>
      </typeAliases>
  

• You can also specify a package name. Mybatis will search for the required JavaBeans under the package name. For example, scan the package of the entity class, and its default alias is the class name of this class, with the initial lowercase

  <!--You can alias an entity class-->
  <typeAliases>
  	<package name="com.ding.pojo"/>
  </typeAliases>
  

  There are few entity classes. The first one is recommended (and can be diy)

  There are many entity classes. The second one is recommended

  If the second type needs to be renamed, you can add annotations to the entity class

  @Alias("User")
  public class User{}
  

3.5. settings

3.6 other configurations

• typeHanlers (type processor)

• objectFactory (object factory)

• plugins plug-in

  • mybatis-generator-core
  • Mybatis plus

3.7 mappers

MapperRegistry: register and bind our Mapper file

Mode 1:

<!--every last Mapper.xml All need to be in Mybatis Register in core profile-->
<mappers>
	<mapper resource="UserMapper.xml"/>
</mappers>

Method 2: register with class file binding

<mappers>
	<mapper class="com.ding.mapper.UserMapper"/>
</mappers>

Note:

• Interface and its Mapper configuration file must have the same name!
• Interface and its Mapper configuration file must be in the same package!

Mode 3:

<mappers>
	<package name="com.ding.mapper"/>
</mappers>

Note:

• Consistent with the precautions of mode 2

3.8 life cycle and scope

Lifecycle and scope are crucial because incorrect use can lead to very serious concurrency problems

SqlSessionFactoryBuilder:

• Once the SqlSessionFactory is created, it is no longer needed
• local variable

SqlSessionFactory

• It can be regarded as the connection pool of the database
• Once SqlSessionFactory is created, it should always exist during the operation of the application. There is no reason to discard it or recreate another instance
• Therefore, the best scope of SqlSessionFactory is the application scope
• The simplest is to use singleton mode or static singleton mode

SqlSession

• A request to connect to the connection pool
• The instance of SqlSession is not thread safe and cannot be shared, so its best scope is the request or method scope
• You need to close it immediately after it is used up, otherwise the resources will be occupied

4. Solve the problem of inconsistency between attribute name and field name

• Solution 1: alias
• Solution 2: resultmap

Result set mapping

<!--    Result set mapping-->
    <resultMap id="UserMap" type="User">
<!--        column Represents each column in the database-->
<!--        property Represents the corresponding attribute value in the entity class-->
        <result column="pwd" property="password"/>
<!--What's the difference? What's the mapping-->
    </resultMap>
<!--use resultMap-->
<select id="getUserList" resultMap="UserMap">
        select * from user;
    </select>

The resultmap element is the most important and powerful element in MyBatis

The design idea of ResultMap is that there is no need to configure explicit result mapping for simple statements, but only need to describe their relationship for more complex statements

The advantage of ResultMap is that although you already know it well, you don't need to use them explicitly

If only everything were always so simple (wisdom)

5. Log

5.1 log factory

If a database operation is abnormal and we need to troubleshoot, the log is our best assistant

[the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-tkkme0sz-1625968595100) (C: \ users \ 86133 \ appdata \ roaming \ typora \ typora user images \ image-20210709091914965. PNG)]

Which log implementation to use depends on the situation

STDOUT_LOGGING standard log output

<settings>
        <setting name="logImpl" value="STDOUT_LOGGING"/>
    </settings>

5.2 ,LOG4J

1. Pilot package (imported in maven)

2,log4j. Properties (create configuration file)

#Output the log information with the level of DEBUG to the two destinations of console and file. The definitions of console and file are in the following code
log4j.rootLogger=DEBUG,console,file

#Settings related to console output
log4j.appender.console = org.apache.log4j.ConsoleAppender
log4j.appender.console.Target = System.out
log4j.appender.console.Threshold=DEBUG
log4j.appender.console.layout = org.apache.log4j.PatternLayout
log4j.appender.console.layout.ConversionPattern=[%c]-%m%n

#Settings related to file output
log4j.appender.file = org.apache.log4j.RollingFileAppender
log4j.appender.file.File=./log/ding.log
log4j.appender.file.MaxFileSize=10mb
log4j.appender.file.Threshold=DEBUG
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern= [%p][%d{yy-MM-dd}][%c]%m%n

#Log output level
log4j.logger.org.mybatis=DEBUG
log4j.logger.java.sql=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.ResultSet=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG

3. Change the log in the mybatis core configuration file to "LOG4J"

<settings>
        <setting name="logImpl" value="LOG4J"/>
    </settings>

4. The use of Log4j can be directly tested and run

[the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-dj0v7icu-1625968595103) (C: \ users \ 86133 \ appdata \ roaming \ typora \ user images \ image-20210709102919811. PNG)]

Simple use

1. Import the package import. Org in the class where log4j will be used apache. log4j. Logger;

2. The log object parameter is the class of the current class

static Logger logger = Logger.getLogger(UserMapperTest.class);

3. Log level

info debug errror

6. Pagination

Why pagination?

• Reduce data processing

6.1 paging with Limit

grammar:select * from user limit startIndex,pageSize
select * from user limit 3; #[0,n)

Using Mybatis to realize paging and core sql

<select id="limitGetUser" parameterType="map" resultMap="UserMap">
        select * from user limit #{start} , #{size};
</select>

Usage example

@Test
public void test3(){
	SqlSession sqlSession = MybatisUtils.getSqlSession();
	UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    HashMap<String, Integer> map = new HashMap<>();
    map.put("start",0);
    map.put("size",0);
    List<User> userList = mapper.limitGetUser(map);
    for (User u : userList) {
        System.out.println(u.toString());
    }
    sqlSession.close();
}

6.2 paging plug-in

pagehelper

7. Using annotation development

1. Annotation is implemented on the interface

@Select("select * from user;")
List<User> getUserList();

2. The interface needs to be bound on the core configuration file

<!--    Binding interface-->
    <mappers>
        <mapper class="com.ding.mapper.UserMapper"/>
    </mappers>

3. Test use

Essence: reflection mechanism implementation

Bottom layer: dynamic proxy

etSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
HashMap<String, Integer> map = new HashMap<>();
map.put("start",0);
map.put("size",0);
List userList = mapper.limitGetUser(map);
for (User u : userList) {
System.out.println(u.toString());
}
sqlSession.close();
}

### 6.2 paging plug-in

**pagehelper**

## 7. Using annotation development

1.Annotations are implemented on the interface

```java
@Select("select * from user;")
List<User> getUserList();

2. The interface needs to be bound on the core configuration file

<!--    Binding interface-->
    <mappers>
        <mapper class="com.ding.mapper.UserMapper"/>
    </mappers>

3. Test use

Essence: reflection mechanism implementation

Bottom layer: dynamic proxy