After studying MyBatis for a week, I have a general understanding of MyBatis. I know that MyBatis is an excellent persistence layer framework. When writing JDBC in the past, we will find a lot of code duplication, bloated and tedious. It's painstaking. Until the emergence of MyBatis, MyBatis avoided almost all JDBC code and manual setting of parameters and getting result sets. MyBatis can use simple XML or annotations to configure and map native information. In a word, there are fewer operations, more tasks, more concise code, and unnecessary operations. I will focus on what I need to pay attention to and what I have stepped on in the article. The article is concise, fast-paced, and the knowledge points proposed need to be digested slowly

1.1. Build mybatis environment

1.2.mybatis overall architecture

1.3. Create project

Because it's easy to learn my eclipse tools and common web projects, you can build maven projects, all of which are the same

Remember to change WebRoot\WEB-INF with eclipse. The main purpose is to be compatible with myeclipse, even if myeclipse is used.

1.4. Import dependency

1.5. Create structure directory

Create the config directory and test directory under java Resources. The config directory is mainly used to put the configuration files sqlMapConfig and log4 log files and database load files , test directory as a test directory, and then create three packages mapper (similar to dao), pojo (similar to bean), utils under src

1.6. Create profile

Create db.properties and put it in the config directory, mysql used here

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/springtest
username=root
password=guohang

Create the mybatis kernel file sqlMapConfig.xml and put it in the config directory

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
	<!-- Import database connection configuration -->
	<properties resource="db.properties"></properties>
	<!-- Define aliases -->
	<!-- <typeAliases> <typeAlias type="com.gh.mybatis.pojo.Blog" alias="Blog" 
		/> <package name="com.gh.mybatis.pojo.Blog" /> </typeAliases> -->
	<!-- Define data source -->
	<environments default="test">
		<environment id="test">
			<transactionManager type="JDBC"></transactionManager>
			<dataSource type="POOLED">
				<property name="driver" value="${driver}" />
				<property name="url" value="${url}" />
				<property name="username" value="${username}" />
				<property name="password" value="${password}" />
			</dataSource>
		</environment>
	</environments>
	<!-- Define mapping file -->
	<mappers>
		<!-- <mapper resource="com/gh/mybatis/dao/User.xml" /> -->
		<package name="com.gh.mybatis.mapper" />
	</mappers>
</configuration>

Note: the mappers label in this can't be forgotten. It can be matched with mapper or package. The main difference between the two is that the former is accurate to a single file, and one more mapping file will be added here. The latter is directly assigned to the whole package. Later mapping files will be created in this package without adding a mapping file

Import log4.properties log file

# Global logging configuration
log4j.rootLogger=DEBUG, stdout
# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

The whole mybatis environment is set up here

2.1.CRUD operation (using dynamic proxy)

2.2. Creation of database test table

CREATE TABLE `user` (
  `uid` int(11) NOT NULL auto_increment,
  `username` varchar(50) default NULL,
  `password` varchar(50) default NULL,
  `status` varchar(10) default NULL,
  PRIMARY KEY  (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8


INSERT INTO USER(uid,username,PASSWORD,STATUS) VALUES(1,'Guo Hang','123456','Y')
INSERT INTO USER(uid,username,PASSWORD,STATUS) VALUES(2,'Guo Degang','123456','Y')
INSERT INTO USER(uid,username,PASSWORD,STATUS) VALUES(3,'Guo Kirin','123456','N')
INSERT INTO USER(uid,username,PASSWORD,STATUS) VALUES(3,'Guo Bao K','123456','N')

2.3. Creation of tool class and entity class

Create pojo class

package com.gh.mybatis.pojo;

public class User {
	private int uid;
	private String username;
	private String password;
	private String status;

	public int getUid() {
		return uid;
	}

	public void setUid(int uid) {
		this.uid = uid;
	}

	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 getStatus() {
		return status;
	}

	public void setStatus(String status) {
		this.status = status;
	}

	public User() {
		super();
		// TODO Auto-generated constructor stub
	}

	public User(int uid, String username, String password, String status) {
		super();
		this.uid = uid;
		this.username = username;
		this.password = password;
		this.status = status;
	}

	@Override
	public String toString() {
		return "User [uid=" + uid + ", username=" + username + ", password=" + password + ", status=" + status + "]";
	}

}

Create a mapping file of interface and this class under mapper
Note: the name of the created interface and xml Mapping file must be the same, and it is better to put them under the same package

package com.gh.mybatis.mapper;

import java.util.List;

import com.gh.mybatis.pojo.Orders;
import com.gh.mybatis.pojo.QueryOrdersAndUser;

public interface User {
	
	
}

<?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">

Import the mybatis tool class to the utils layer. At the beginning, write it several times and then use the tool class to understand and deepen the impression

package com.gh.mybatis.utils;

import java.io.IOException;
import java.io.InputStream;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class MybatisUtils {
	private static SqlSessionFactory sqlSessionFactory = null;

	static {
		try {
			InputStream in = Resources.getResourceAsStream("sqlMapConfig.xml");//Import core profile
			sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);//Create a factory
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	private MybatisUtils() {
	}

	public static SqlSession getSqlSession() {
		return sqlSessionFactory.openSession();//Get an sqlSession
	}
}

Create a test package under the test directory and a junit test class under the package

package com.gh.mybatis.test;

import static org.junit.Assert.*;

import java.util.Arrays;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.websocket.Session;

import org.apache.ibatis.session.SqlSession;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import com.gh.mybatis.mapper.User;
import com.gh.mybatis.pojo.Orders;
import com.gh.mybatis.pojo.QueryOrdersAndUser;
import com.gh.mybatis.utils.MybatisUtils;

public class MybatisTest {
	private SqlSession sqlsession;

	// Get objects in advance through tool classes
	@Before
	public void getSqlSession() {
		sqlsession = MybatisUtils.getSqlSession();
	}

	@After
	public void getCommint() {
		sqlsession.commit();
		sqlsession.close();

	}

	//Define an intermediate as the output class, in which all fields of all sql query result sets are defined. This method is relatively simple, and there are many in the company

	
	@Test
	public void testfindOrdersList() throws Exception {
		User mapper = sqlsession.getMapper(User.class);
		List<QueryOrdersAndUser> findOrdersList = mapper.findOrdersList();
		Iterator<QueryOrdersAndUser> iterator = findOrdersList.iterator();
		while(iterator.hasNext()) {
			QueryOrdersAndUser next = iterator.next();
			System.out.println(next.getOrder()+"   "+next.getUsername());
		}
	}
}

After all are created, the directory structure is as follows

Crud a series of operations

2.4.1. First query

Do the following in the User.xml mapping file
Note: as like as two peas in the interface, the id name in the select tag will not be found.

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.gh.mybatis.dao.User"> //namespace: interface location
	<!--*************** CRUD **************** -->
	<select id="findUserByid" parameterType="int"//id: used to map methods in the interface. parameterType: the type of data passed in. Here, the id passed in is int type
		resultType="com.gh.mybatis.bean.UserBean">//The returned data type. Here you can return the normal type or the object
		select * from user where uid=#{id}//Write sql statement: get value using {}
	</select>

Configuring methods in an interface

Write test methods

Result

2.4.2. Method without interface

Without using the interface, you can directly use selectOne to get the specific xml method path and pass in parameters instead of defining methods in the user interface

Result
Note: the column name and property name should be the same, otherwise they cannot be mapped. If they are not the same, use resultMap, which will be described later

2.4.3. Fuzzy query

xml file class add select

Add method in interface
test
Pay attention to the spliced sql statements

2.4.4. Difference between ා and $

Note: if $, the attribute must be value, and only value. In general, we still use#
#{}: placeholder, which can prevent sql injection (single quotation mark '' will be added to the replacement result)
${}: sql splicing symbol (single quotation mark 'will not be added to the replacement result, and like and order by will be used later. There is sql injection problem, which needs to be filtered in the code manually)

2.4.5. Sorting

Sorting is different from other sql queries. First, look at the sql statement select * from user order by {uid} DESC, it seems that there is no problem when you write like this, and it can also run and sort normally in the database. However, in mybatis, it will not report errors, nor sort, and the data will be output as it is. To avoid this problem, you need to use the $symbol to solve this problem , code directly
Add a select tag to the xml file

Add a method to the interface

test
Result

2.4.6. Multi parameter transfer

The classic example of multiparameter transfer is paging
Be careful:
1. There are three ways to transfer multiple parameters. The first way is to directly assign the value after the sql statement ා, when creating the select tag
eg:select * from user limit #{2},#{2}
2. Use annotation to add Param to the interface. The value of annotation should be consistent with the placeholder parameter of mapper
eg: public List selectUserByPage(@Param("offset") int offset,@Param("pagesize")int pagesize);
3. How to use map
Add the select tag in the xml file, the first way

Interface

test

Second kinds
user.xml
select * from user limit #{offset},#{pagesize}
Interface

Test ibid
Third kinds
Interface

test

2.4.7. Add data

Add data: add an insert tag in the user.xml file, and add it in the normal way
Note: in normal development, we usually want to get the id of the added data after adding a piece of data, so that we can use the selectKey tag
xml

<insert id="insertUser"
		parameterType="com.gh.mybatis.bean.UserBean">
		<!-- Add data -->
		<!--Get after adding id -->
		<selectKey resultType="java.lang.Integer" order="AFTER"
			keyProperty="uid">
			select LAST_INSERT_ID()
		</selectKey>
		insert into user(username,password) values(#{username},#{password})
	</insert>

Interface

public int insertUser(UserBean user);

test

// Add data
	@Test
	public void testInsertUser() {
		User usermapper = sqlsession.getMapper(User.class);
		UserBean userBean = new UserBean();
		userBean.setUsername("Zhang San");
		userBean.setPassword("123");
		int insertUser = usermapper.insertUser(userBean);
		sqlsession.commit();
		sqlsession.close();
		System.out.println(insertUser);
		System.out.println(userBean.getUid());
	}

2.4.8. Modify data

Note: when modifying the data, we need to pay attention to the following points: when we modify the data in the normal way, we will find that unless we attach values to all properties, if we modify a single value, other values will become null in the database. For this situation, we have two solutions
1. Query once before adding, but it will access the database twice, which will affect the efficiency. It is not recommended to use
2. Use the dynamic sql tag set, later on!!!
xml

Interface

test

2.4.9. Delete

There is no need to pay attention to the deletion. The deletion is finished according to the normal way. As for batch deletion and batch modification, they are implemented in dynamic sql with foreache, which will be described later
xml

test

3.1. Dynamic sql

In the following dynamic sql, you can learn more about if, where, foreach and sql segments

    3.2.if

The condition in the if tag is equivalent to that the value is not equal to null and the sql in the tag is executed if it is not an empty string. It will automatically add the sql in the if after the sql statement. But note: if the user does not input, then if will not execute. If not, it will execute select statement separately. If not write where 1=1 will lead to incomplete sql statements and errors in incomplete programs. Do we have to write a "where 1=1" every time? The solution is to use the "where" tag, which is used to replace the "where" condition. So the role of "where" is here. The following "where" is just a little more detailed

<select id="selectActiveByAdminName" parameterType="string"
		resultType="com.gh.mybatis.bean.UserBean">
		SELECT * FROM user WHERE 1=1
		<if test="value !=null and value !=''">
			AND username LIKE '%${value}%'
		</if>
	</select>

    3.3.choose

Note: you can understand it as if else. Look at the code

<select id="selectActiveByUserNameOrStyle"
		parameterType="com.gh.mybatis.bean.UserBean"
		resultType="com.gh.mybatis.bean.UserBean">
		select * from user where status='Y'
		<choose>
			<when test="username !=null and username!=''">
				and username like #{username}
			</when>
			<when test="password !=null and password!=''">
				and password like #{password}
			</when>
			<otherwise>
				and uid='1'
			</otherwise>
		</choose>
	</select>

    3.4.where

    3.5.set

Note: use set to solve the problem of null modification. With set, you can selectively modify values and other data will not be null

<!-- set Will automatically remove the comma after the statement -->
	<update id="setTest"
		parameterType="com.gh.mybatis.bean.UserBean">
		update user 
		<set>
		<if test="username !=null and username !=''">username=#{username},</if>
		<if test="password !=null and username !=''">password=#{password},</if>
		<if test="status !=null and username !=''">status=#{status}</if>
		</set>
		where uid=#{uid}
	</update>

test

    3.6.trim

It's no use. I know

    3.7.foreach

Note: it's the end of traversing the batch operation. For example, if you want to delete in batch, you should pay attention to the properties in the collection operation. The collection operation is a collection object, and the item is equivalent to a variable. The following are all splicing characters

<delete id="foreachTest" >
		delete from user where uid in
		<foreach collection="list" item="item" open="(" close=")" separator=",">
			#{item}
		</foreach>
	</delete>

testThe most important is sql statement

3.8.sql segment

Its function is to package the repeated code, and use the include reference to the place that needs to be used
For example, when modifying, a lot of parameters will be written every time, so we can use sql fragments to encapsulate it, and use include reference when necessary

4.1. Reverse engineering

What is reverse engineering? It means that all structural directories, configuration files, and the most basic addition, deletion, modification, and query can be generated. That is to say, all the operations we have done before can be generated with one click. It will automatically map the tables in the database to the classes, without writing bean s manually. It's just a bull's-eye

First of all, import dependency is included in the download link given above

Import the reverse engineering line configuration file generator.xml, and remember to put it in java Resources, otherwise it will not be found

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE generatorConfiguration PUBLIC
        "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
        "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd" >
<generatorConfiguration>
	 <context id="context" targetRuntime="MyBatis3">
		<!-- Local database driver jar The full path of a package is renamed its local path when it is used -->
		<!-- <classPathEntry location="D:/software/Mavenrepo/mysql/mysql-connector-java/5.1.44/mysql-connector-java-5.1.44.jar" 
			/> <context id="context" targetRuntime="MyBatis3"> -->
		<!--Define generated java Encoding format of class -->
		<property name="javaFileEncoding" value="UTF-8" />

		<!--suppressAllComments Set totrue No more comments will be generated -->
		<commentGenerator>
			<property name="suppressAllComments" value="true" />
		</commentGenerator>

		<!-- Database configuration -->
		<jdbcConnection driverClass="com.mysql.jdbc.Driver"
			connectionURL="jdbc:mysql://localhost:3306/springtest" userId="root"
			password="guohang" />

		<javaTypeResolver>
			<property name="forceBigDecimals" value="false" />
		</javaTypeResolver>

		<!-- Location of entity class generation -->
		<javaModelGenerator
			targetPackage="com.gh.mybatis.pojo" targetProject=".\src">
			<property name="trimStrings" value="true" />
		</javaModelGenerator>

		<!-- Mapper.xml Location of files -->
		<sqlMapGenerator targetPackage="com.gh.mybatis.mapper"
			targetProject=".\src">
		</sqlMapGenerator>

		<!-- Mapper Location of interface files -->
		<javaClientGenerator
			targetPackage="com.gh.mybatis.mapper" targetProject=".\src"
			type="XMLMAPPER">
		</javaClientGenerator>
		<!-- Specify database tables -->
		<table schema="" tableName="user"></table>
	</context>
</generatorConfiguration>

Then create a package that contains the reverse engineering execution file

package com.gh.mybatis.generator;

import java.io.File;
import java.io.IOException;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.mybatis.generator.api.MyBatisGenerator;
import org.mybatis.generator.config.Configuration;
import org.mybatis.generator.config.xml.ConfigurationParser;
import org.mybatis.generator.exception.InvalidConfigurationException;
import org.mybatis.generator.exception.XMLParserException;
import org.mybatis.generator.internal.DefaultShellCallback;

public class Generator {
	public static void main(String[] args)
			throws InvalidConfigurationException, IOException, XMLParserException, SQLException, InterruptedException {
		// Paste code from mybatis document
		List<String> warnings = new ArrayList<String>();
		boolean overwrite = true;
		File configFile = new File("generator.xml");
		ConfigurationParser cp = new ConfigurationParser(warnings);
		Configuration config = cp.parseConfiguration(configFile);
		DefaultShellCallback callback = new DefaultShellCallback(overwrite);
		MyBatisGenerator myBatisGenerator = new MyBatisGenerator(config, callback, warnings);
		myBatisGenerator.generate(null);
	}
}

Note: then you can run it. Just click it once when you run it. It didn't come out because you didn't refresh the directory. If you click it once and add code, it will double and overlap with the number of times you click. Then you have to delete it and execute it again. After you generate it, you will find that the methods in the interface and the sql in the xml are all ready. How to use it to name yourself is over

5.1. L1 cache

Test code

@Test
	public void testFindUserByidCacheLevelOne1() {
		User usermapper = sqlsession.getMapper(User.class);
		UserBean findUserByid = usermapper.findUserByid(1);
		System.out.println("Results queried");
		UserBean findUserByid2 = usermapper.findUserByid(1);
		System.out.println("Results queried");
		sqlsession.close();
		System.out.println("session Close");
		System.out.println(findUserByid);
	}

Pay attention to the results. We query twice, but it only executes once. Why? This is the first level cache

First level cache flushed
Test code

@Test
	public void testFindUserByidCacheLevelOne2() {
		User usermapper = sqlsession.getMapper(User.class);
		UserBean findUserByid = usermapper.findUserByid(1);
		System.out.println("Results queried");
		UserBean userBean = new UserBean();
		userBean.setPassword("456789");
		userBean.setUid(1);
		usermapper.updateUser(userBean);
		System.out.println("Modification executed");
		UserBean findUserByid2 = usermapper.findUserByid(1);
		System.out.println("Results queried");
		sqlsession.close();
		System.out.println("session Close");
		System.out.println(findUserByid);
	}

As a result, two queries were made

Conclusion:
The first level cache is session level cache, which exists by default. When a query is executed within the same session range, if the query is the same, the second query will get the data from the cache. If there are addition, deletion and modification operations between two queries, the SqlSession cache will be automatically cleared, indicating that the next query will execute the sql statement again

5.2. L2 cache

Summary: it is not enabled by default. If the same data query is executed in different session ranges, each query will perform an independent database retrieval process
Enable secondary cache: first add to the configuration file
Then implement the serialization interface. If there are addition, deletion and modification operations between two queries, the SqlSession cache will be automatically cleared, indicating that the next query will execute the sql statement again

6.1. Multi table query