Learn mybatis

Time:2020-5-27

1. About mybatis

  • Learning video: https://www.bilibili.com/vide…
  • Persistence: the process of transforming program data into persistent and transient state
  • Persistence layer: the code block that completes the persistence work, collectively referred to as Dao layer
  • Mybatis: a semi persistent framework to simplify database connection and operation. It is the current mainstream
  • Official website: https://mybatis.org/mybatis-3…

2. The first mybatis project

2.1. Configuration database

=Create database, table, table data

CREATE TABLE `user` (
		`id` int(20) NOT NULL AUTO_INCREMENT,
		#ID is not 0, self increasing
		`name` varchar(30) DEFAULT NULL,
		#Name default null
		`pwd` varchar(30) DEFAULT NULL,
		#PWD default null
		Primary key (` ID ') ා primary key index = ID
		)ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
		INSERT INTO `user` VALUES(1,'Tom','123456');
		INSERT INTO `user` VALUES(2,'Peter','123456');
INSERT INTO `user` VALUES(3,'Jhon','123456');

2.2. Construction environment

  • Parent Project Guide Package

<dependency>
		<! -- MySQL driver -- >
		<groupId>mysql</groupId>
		<artifactId>mysql-connector-java</artifactId>
		<version>5.1.48</version>
		</dependency>
		<dependency>
		[! -- mybatis official website -- >
		<groupId>org.mybatis</groupId>
		<artifactId>mybatis</artifactId>
		<version>3.5.2</version>
		</dependency>
		<dependency>
		<! -- JUnit test -- >
		<groupId>junit</groupId>
		<artifactId>junit</artifactId>
		<version>4.12</version>
</dependency>
  • Subproject load resource filter

Prevent configuration files with suffixes of properties and XML from failing to load

<! -- resource filter, to prevent the failure of importing resources, preferably in the parent-child pom.xml Add some code to it -- >
		<build>
		<resources>
		<resource>
		<directory>src/main/resources</directory>
		<includes>
		<include>**/*.properties</include>
		<include>**/*.xml</include>
		</includes>
		<filtering>true</filtering>
		</resource>
		<resource>
		<directory>src/main/java</directory>
		<includes>
		<include>**/*.properties</include>
		<include>**/*.xml</include>
		</includes>
		<filtering>true</filtering>
		</resource>
		</resources>
</build>
  • Configure resources / mybais- 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">

		<configuration>
		<! -- data source -- >
		<environments default="development">
		<environment id="development">
		<transactionManager type="JDBC"/>
		<! -- connect to the database, all will be down -- >
		<dataSource type="POOLED">
		<property name="driver" value="com.mysql.jdbc.Driver"/>
		<property name="url" value="jdbc:mysql://localhost:3306/mybatis?useSSL=false&amp;useUnicode=true&amp;characterEncoding=utf-8"/>
		<property name="username" value="root"/>
		<property name="password" value="admin"/>
		</dataSource>
		</environment>
		</environments>
		<! -- mapper injection mybatis -- >
		<mappers>
		<mapper resource="com/ssl/dao/UserMapper.xml"/>
		</mappers>
</configuration>
  • Write factory tool class

    • After spring integrates mybatis, this operation will be repeated in mybats- config.xml Medium configuration
public class MyBatisUtil {
		/**
		*Enhance the scope of sqlsessionfactory for global use
		*/
		private static SqlSessionFactory sqlSessionFactory;

		static {
		try {
		/*
		Using mybatis as the first step, get the sqlsessionfactory object
		*/
		String resource = "mybatis-config.xml";
		InputStream inputStream = Resources.getResourceAsStream(resource);
		sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
		} catch (IOException e) {
		e.printStackTrace();
		}
		}

		/**
		*Sqlsessionfactory object get sqlsession instance
		*/
		public static SqlSession getSqlSession() {
		return sqlSessionFactory.openSession();
		}
}

2.3 Dao layer

  • Pojo:User
  • Dao: interface; implementation class becomes XXXMapper.xml file
public interface UserDao {
		List<User> getUserList();
}
<?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 namespace = Dao interface to implement -- >
		<mapper namespace="com.ssl.dao.UserDao">
		<select id="getUserList" resultType="com.ssl.pojo.User">
		select * from mybatis.user
		</select>
</mapper>

2.4 testing

  • Resource filter exception, see configuration resource filter of subproject above
  • Mapper registration failed exception: in mybatis- config.xml to configure
<mappers>
		<mapper resource="com/ssl/dao/UserMapper.xml"/>
</mappers>
public class UserMapperTest {
		@Test
		public void getUserList() {
		//1 get is sqlsession object
		SqlSession sqlSession = MyBatisUtil.getSqlSession();
		//2 gets the. Class of the interface because of polymorphism
		UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
		List<User> userList = userMapper.getUserList();
		for (User user : userList) {
		System.out.println(user);
		}
		//3 suggestions: finally close sqlsession
		sqlSession.close();
		}
}

3. CRUD

3.1 namespace = all interface class names are the same

<mapper namespace="com.ssl.dao.UserMapper">
		<select id="getUserList" resultType="com.ssl.pojo.User">
		select * from mybatis.user
		</select>
</mapper>

3.2 select

Learn mybatis

  • ID: method name in the interface
  • Parametertype: return result
  • Resulttype: method parameter

3.3 insert/update/delete

  • To add, delete or modify, you need to add a transaction. The return value is only int. you do not need to add a resulttype
<mapper namespace="com.ssl.dao.UserMapper">
		<! -- adding, deleting, and changing need to commit transactions. There is no specified return value. They are all ints, so there is no need to add resulttype -- >
		<! -- add a user, and the attributes in the object can be retrieved directly -- >
		<insert id="addUser" parameterType="com.ssl.pojo.User">
		INSERT INTO mybatis.user(id,NAME,pwd) VALUES (#{id},#{name},#{pwd});
		</insert>
		<update id="updateUser" parameterType="com.ssl.pojo.User">
		update mybatis.user set name = #{name},pwd=#{pwd} where id=#{id};
		</update>
		<delete id="deleteUserById" parameterType="int">
		delete from mybatis.user where id=#{id}
		</delete>
</mapper>

3.4 common errors

  • Adding, deleting and modifying SQL statement
#Values needed for increase 
		insert into mybatis.user(id,NAME,pwd) VALUES (#{id},#{name},#{pwd});
		#Set required for modification
		update mybatis.user set name = #{name},pwd=#{pwd} where id=#{id};
		#Delete requires from
delete from mybatis.user where id=#{id}
  • The reason for the bug is to look from the back to the front

3.5 universal map

  • If there are too many database fields and too many beans are needed to add and modify, use map to encapsulate the parameters,
  • Benefit 1: avoid redundant code
  • Advantage 2: skip the specific bean attribute, name the key as you like, and ensure that value is the attribute of the field

3.6 fuzzy query

<! -- fuzzy query 1 not recommended -- >
		<select id="getLikeUser1" parameterType="string" resultType="com.ssl.pojo.User">
		select * from mybatis.user where name like #{value};
		</select>
		<! -- fuzzy query 2 recommended write dead -- >
		<select id="getLikeUser2" parameterType="string" resultType="com.ssl.pojo.User">
		select * from mybatis.user where name like "%"#{value}"%";
</select>

4 configuration analysis

4.1 mybatis_config.xml

  • Create mybatis in resource_ config.xml

4.2 configuration properties in mybatis

  • Properties
  • Settings
  • Typealiases (type aliases)
  • Typehandlers (type processors)
  • Objectfactory (object factory)
  • Plugins (plug-ins)
  • Environments (environment configuration)
  • Environment (environment variable)
  • Transaction manager
  • Datasource (data source)
  • Databaseidprovider (database vendor ID)
  • Mappers (mapper)

4.3 environment

  1. Transaction manager: transactionmanager, default “JDBC”
  2. Connect to database: default pooled
<environments default="development">
		<environment id="development">
		<! -- transaction manager: default JDBC -- >
		<transactionManager type="JDBC"/>
		<! -- connect data source: default pooled -- >
		<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>

4.4 properties

  • db.properies
driver=com.mysql.jdbc.Driver
		url=jdbc:mysql://localhost:3306/mybatis?useSSL=false&amp;useUnicode=true&amp;characterEncoding=utf-8
		username=root
password=123456

4.5 settings

Settings configuration interpretative statement Default state
Cacheenabled (important) Globally turn on or off any caching configured in all mapper profiles. On by default
lazyLoadingEnabled Global switch to delay loading. When on, all associated objects delay loading. The switch state of the item can be overridden by setting the fetchtype property in a specific association. Default off
Mapunderscoretocamelcase (important) Whether to enable automatic mapping of hump naming, that is, from the classic database column name a_ Column maps to the classic java property name acolumn. Default off
Logimpl (most important) Specifies the specific implementation of the logs used by mybatis. If not specified, it will be found automatically. SLF4J、LOG4J、STDOUT_ Logging, etc., closed by default

4.6 type aliases

  • effect: mapper.xml Simplify writing when configuring resulttype

Learn mybatis

<! -- alias various classes, simplify configuration -- >
		<typeAliases>
		<! -- Method 1: specify a class
		<typeAlias alias="User" type="com.ssl.pojo.User"/>
		-->
		<! -- mode 2: specify the package, and the lowercase in the package as the alias
		You can also change the lowercase name by using @ value ("alias") on the class
		-->
		<package name="com.ssl.pojo"/>
</typeAliases>

4.7 plugins

Two plug-ins that need to be learned in the later stage to make mybatis configuration easier

  • Mybatis-Plus
  • MyBatis Generator Core

4.8 mappers

  1. In mybatis- config.xml Mapper mapper in
<! -- use resource reference relative to classpath -- >
		<mappers>
		<mapper resource="org/mybatis/builder/AuthorMapper.xml"/>
		<mapper resource="org/mybatis/builder/BlogMapper.xml"/>
		<mapper resource="org/mybatis/builder/PostMapper.xml"/>
</mappers>
  1. Class and package binding: the interface and mapper configuration file must have the same name. Do you need to learn from the same package?
<! -- use the class mapper interface to implement the fully qualified class name of the class -- >
		<mappers>
		<mapper class="org.mybatis.builder.AuthorMapper"/>
		<mapper class="org.mybatis.builder.BlogMapper"/>
		<mapper class="org.mybatis.builder.PostMapper"/>
		</mappers>
		<! -- use package + name to register all mapper interface implementations in the package as mappers -- >
		<mappers>
		<package name="org.mybatis.builder"/>
</mappers>

4.9 mybatisutil (parameter scope)

name interpretative statement
SqlSessionFactoryBuilder Once created, it is no longer needed. The scope is local variable = static code block loads first
SqlSessionFactory It exists all the time during operation. The scope is the application scope, using the singleton mode or the static singleton mode.
SqlSession The thread of the request to connect to the database is unsafe and will be closed as soon as it is used up. The scope is method or request, which will be closed as soon as it is used up. The close operation is very important
public class MyBatisUtil {
		//0 promotes the scope of sqlsessionfactory in step 3
		private static SqlSessionFactory sqlSessionFactory;

		static {
		try {
		//1 get mybatis configuration file
		String resource = "mybatis-config.xml";
		//2 get the input stream of the profile
		InputStream inputStream = Resources.getResourceAsStream(resource);
		//3 use sqlsessionfactorybuilder(). Build() to create sqlsessionfactory
		sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
		} catch (IOException e) {
		e.printStackTrace();
		}
		}

		//4. Get the session through getsqlsession
		public static SqlSession getSqlSession() {
		return sqlSessionFactory.openSession(true);
		}
}

5 property name and field name are inconsistent

5.1 change PWD – > password in user

  • Simulate the implementation of this process. The database is PWD. Change the user attribute in POJO to PWD, resulting in inconsistency
//Query result: inconsistent field query result is null
User {id = 1, name = ecstasy, password = null '}

5.2 solutions

  • Change POJO member property name = database field name: too low to use, not recommended
  • Use result set map = resultmap: which field is inconsistent, the inconsistent member property will be mapped to the column of the database

Learn mybatis

<?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 namespace = Dao interface to be implemented = Original Dao implementation class -- >
		<mapper namespace="com.ssl.dao.UserMapper">
		<! -- change result set mapping, resolve inconsistency between attribute name and database column name -- >
		<resultMap id="UserMap" type="com.ssl.pojo.User">
		<result column="pwd" property="password"/>
		</resultMap>
		<! -- query a user by ID -- >
		<select id="getUserById" parameterType="int" resultMap="UserMap">
		select * from mybatis.user where id = #{id};
		</select>
</mapper>

6 open log

6.1 log factory

  • Configure in setting: name = logimpl case and space cannot be wrong
  • Log4j: must master, steps: setting configuration, package guide, pro configuration, loading during test

    • Setting configuration, + import dependency package

       <settings>
      						<setting name="logImpl" value="log4j"/>
      				</settings>
      <! -- log4j log -- >
      						<dependency>
      						<groupId>log4j</groupId>
      						<artifactId>log4j</artifactId>
      						<version>1.2.17</version>
      				</dependency>
    • Log4j.properties: go to the Internet to find one if necessary

      #Log4j log system: general configuration
      						# Define the root logger with appender file
      						# log=D:\logs
      						log4j.rootLogger = DEBUG, FILE, console
      						#Output to the log package under the current directory file
      						log4j.appender.FILE=org.apache.log4j.FileAppender
      						log4j.appender.FILE.File=./logs/log4j.log
      						# Set the immediate flush to true (default)
      						log4j.appender.FILE.ImmediateFlush=true
      						# Set the threshold to debug mode
      						log4j.appender.FILE.Threshold=debug
      						# Set the threshold to debug mode
      						#Set log information append
      						log4j.appender.FILE.Append=true
      						# Set the maximum file size before rollover
      						# 30MB
      						log4j.appender.FILE.MaxFileSize=5KB
      						# Set the backup index
      						log4j.appender.FILE.MaxBackupIndex=2
      						# Define the layout for file appender
      						log4j.appender.FILE.layout=org.apache.log4j.PatternLayout
      						log4j.appender.FILE.layout.conversionPattern=%m%n
      						#Output log to console
      						log4j.appender.console=org.apache.log4j.ConsoleAppender
      						log4j.appender.console.layout=org.apache.log4j.PatternLayout
      						log4j.appender.console.layout.ConversionPattern=[%d{yyyy-MM-dd HH:mm:ss}]-[%t]-[%F:%L]-[%p]-[%c]-%m%n
      						#log4j.appender.console.layout.ConversionPattern=[%d{yyyy-MM-dd}]-[%t]-[%x]-[%-5p]-[%-10c:%m%n]
      						log4j.appender.console.encoding=UTF-8
    • In test:static Logger logger = Logger.getLogger(UserMapperTest.class);

      public class UserMapperTest {
      						//Use log4j
      						static Logger logger = Logger.getLogger(UserMapperTest.class);
      
      						@Test
      						public void getUserById() {
      						//1 get is sqlsession object
      						SqlSession sqlSession = MyBatisUtil.getSqlSession();
      						//2. Get method 1: getmapper
      						UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
      						User user = userMapper.getUserById(1);
      						System.out.println(user);
      						//3 suggestions: finally close sqlsession
      						sqlSession.close();
      						}
      				}
  • STDOUT_ Logging: Master, no need to import package. Mybatis is configured by default. The disadvantage is that it is only displayed on the console
 <settings>
		<!-- <setting name="logImpl" value="log4j"/>-->
		<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
  • LOG4J2
  • JDK_LOGGING
  • COMMONS_LOGGING
  • SLF4J
  • NO_LOGGING

7 Pagination

7.1 limit

  • First parameter: StartIndex = subscript of start page = (page number – 1) * PageSize
  • Second parameter: PageSize = size of each page in the page
  • There is only one parameter: the default is to page from the first element to the index subscript
select * from user limit startIndex,pageSize;
		#StartIndex = (page number - 1) * PageSize
		select * from user limit index;
#From the first element to the index user by default

7.2 paging

  • Both map and rowbbounds can implement paging: Map paging is recommended because the default key = {key}
public interface UserMapper {
		/**
		*Get a user by ID
		*@ param ID specified
		* @return User
		*/
		User getUserById(int id);

		/**
		*Paging data through map, recommended
		*@ param map common
		* @return ListUser
		*/
		List<User> getLimitUser(Map<String,Object> map);

		/**
		*Understand, not recommended, paging data through rowbounds
		* @return ListUser
		*/
		List<User> getLimitUserByRowBounds();
}
<?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.ssl.dao.UserMapper">
		<! -- change result set mapping, resolve inconsistency between attribute name and database column name -- >
		<resultMap id="UserMap" type="com.ssl.pojo.User">
		<result column="pwd" property="password"/>
		</resultMap>
		<! -- map paging -- >
		<select id="getLimitUser" parameterType="map" resultMap="UserMap">
		select * from mybatis.user limit #{startIndex},#{pageSize};
		</select>
		[! -- rowbounds page -- >
		<select id="getLimitUserByRowBounds" resultMap="UserMap">
		select * from mybatis.user ;
		</select>
</mapper>
public class UserMapperTest {    
		@Test
		public void getLimitUser() {
		UserMapper userMapper = MyBatisUtil.getSqlSession().getMapper(UserMapper.class);
		Map<String, Object> map = new HashMap<>();
		// mapper.xml Will automatically find the key = {key} in the map
		map.put("startIndex",0);
		map.put("pageSize",2);
		List<User> limitUser = userMapper.getLimitUser(map);
		for (User user : limitUser) {
		System.out.println(user);
		}
		}
		@Test
		public void getLimitUserBrRowBounds() {
		SqlSession sqlSession = MyBatisUtil.getSqlSession();
		//Old query paging: using rowbounds
		RowBounds rowBounds = new RowBounds(0, 2);
		//Grammatical trouble
		List<User> usersList = sqlSession.selectList("com.ssl.dao.UserMapper.getLimitUserByRowBounds",0,rowBounds);
		for (User user : usersList) {
		System.out.println(user);
		}
		}
}

7.3 paging plug in

  • Self study the mybatis PageHelper plug-in. The company needs to go to the website for self-study

8 annotation configuration SQL statement

8.1 mybaits- config.xml Configuration mapper in

<! -- interface class for binding annotation Development -- >
		<mappers>
		<mapper class="com.ssl.dao.UserMapper"/>
</mappers>

8.2 disadvantages

  • If the column name and member property name in the table are inconsistent, it will be null

8.3 crud of notes

  • Learn @ param (very important)

    • Add basic types and string suggestions, not reference types
    • (uid) is the {uid} in SQL
public interface UserMapper {
		/**
		*The limitation of using annotation development is that the column must be consistent with the attribute name of Dao interface member. If you know that the output is null, you can't find it
		*Therefore, annotation statement development is easy to use in simple scenarios
		*/
		@Select("select * from user")
		List<User> getUsers();

		/**
		* @param id=uid
		* @return User
		*/
		@Select("select * from user where id = #{uid}")
		User getUserById(@Param("uid") int id);
}
  • Add, delete, change and auto commit transactions
public static SqlSession getSqlSession() {
		//It is not recommended to submit the commit manually
		return sqlSessionFactory.openSession(true);
}

9 Lombok

9.1 installing Lombok plug-in in idea

9.2 Maven installation dependency

<dependency>
		<groupId>org.projectlombok</groupId>
		<artifactId>lombok</artifactId>
		<version>1.18.12</version>
</dependency>

9.3 @ data and other notes

  • @Data: most commonly used. Automatically add setter getter equals to string,
  • @Allargsconstructor: parametric construction
  • @Noargsconstructor: nonparametric construction

9.4 disadvantages

  • Although it can be mixed, multiple constructorsCannot overload
  • The company uses it as soon as possible, and uses it less when it is not used, because it has changed the writing habit of Java source code, which is not conducive to promotion

10 to one

  • Import Lombok plug-ins and dependencies to reduce POJO code
  • Create new entity classes student, teacher, and database tables

    • There is a field TID in student that uses foreign key constraint to associate with teacher
CREATE TABLE `student` (
		`id` int(10) NOT NULL,
		`name` varchar(20) DEFAULT NULL,
		`tid` int(10) DEFAULT NULL,
		PRIMARY KEY (`id`),
		KEY `fktid` (`tid`),
		CONSTRAINT `fktid` FOREIGN KEY (`tid`) REFERENCES `teacher` (`id`)
		) ENGINE=InnoDB DEFAULT CHARSET=utf8

		CREATE TABLE `teacher` (
		`id` int(10) NOT NULL,
		`name` varchar(20) DEFAULT NULL,
		PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
  • Configuration environment: POJO and mapper interfaces
@Data
		@AllArgsConstructor
		@NoArgsConstructor
		public class Student {
		private Integer id;
		private String name;
		//Need to associate a teacher class
		private Teacher teacher;
		}

		@Data
		@AllArgsConstructor
		@NoArgsConstructor
		public class Teacher {
		private int id;
		private String name;
}
public interface StudentMapper {
		//Subquery
		List<Student> getStudents();
		//Joint table query
		List<Student> getStudents1();
}

10.1: subquery = query nesting

  • mapper.xml

    • The following are important for understanding
    • Javatype = “teacher” if alias is configured, case can be used. Go to the POJO path to find the teacher, and then use its member attribute TID

Learn mybatis

Method 1: nesting by query = subquery -- >
		<select id="getStudents" resultMap="resultStudents">
		select * from mybatis.student;
		</select>
		<resultMap id="resultStudents" type="student">
		<! -- association: used when the attribute is an object -- >
		<! -- Collection: used when the property is collection -- >
		<association property="teacher" column="tid" javaType="teacher" select="getTeachers"/>
		</resultMap>
		<select id="getTeachers" resultType="teacher">
		select * from mybatis.teacher where id = #{tid}
</select>
  • test
@Test
		public void getStudents() {
		StudentMapper studentMapper = MyBatisUtil.getSqlSession().getMapper(StudentMapper.class);
		List<Student> students = studentMapper.getStudents();
		for (Student student : students) {
		System.out.println(student);
		}
		/*
		Student (id = 1, name = Xiaohong, teacher = teacher (id = 1, name = teacher Qin))
		*/
}

10.2: Joint table query = result nesting

  • Use multi table query to avoid writing multiple SQL
  • mapper.xml

Learn mybatis

Method 2: nesting by result = joint table query -- >
		<select id="getStudents1" resultMap="resultStudents1">
		select  s.id sid,s.name sname,t.name tname
		from student s,teacher t
		where s.tid = t.id;
		</select>
		<resultMap id="resultStudents1" type="student">
		<result property="id" column="sid"/>
		<result property="name" column="sname"/>
		<association property="teacher" javaType="teacher">
		<result property="name" column="tname"/>
		</association>
</resultMap>
  • Test: unchanged from nested query results
@Test
		public void getStudents1() {
		StudentMapper studentMapper = MyBatisUtil.getSqlSession().getMapper(StudentMapper.class);
		List<Student> students = studentMapper.getStudents1();
		for (Student student : students) {
		System.out.println(student);
		}
		/*
		Student (id = 1, name = Xiaohong, teacher = teacher (id = 0, name = teacher Qin))
		*/
}

11 one to many

  • Configuration environment: mapper interface under POJO and Dao
@Data
		@AllArgsConstructor
		@NoArgsConstructor
		public class Student {
		private Integer id;
		private String name;
		private int tid;
		}

		@Data
		@AllArgsConstructor
		@NoArgsConstructor
		public class Teacher {
		private int id;
		private String name;
		//One to many set
		private List<Student> students;
}
public interface StudentMapper {
    
		}

		public interface TeacherMapper {
		/**
		*Subqueries: nested queries by query
		* @param tid
		* @return
		*/
		Teacher getTeacher(@Param("id") int tid);
		/**
		*Joint table query: nested query by result
		* @param tid
		* @return
		*/
		Teacher getTeacher1(@Param("id") int tid);
}

11.1: subquery

Learn mybatis

<! -- subquery: nested query by query -- >
		<select id="getTeacher" resultMap="teacherToStudent">
		select id, name from mybatis.teacher where id = #{id}
		</select>
		<resultMap id="teacherToStudent" type="teacher">
		<result property="id" column="id"/>
		<result property="name" column="name"/>
		<! -- column = "Id" is the ID in the teacher table -- >
		<collection property="students" javaType="List" ofType="student"
		column="id" select="getStudentsByTeacherId" />
		</resultMap>
		<select id="getStudentsByTeacherId" resultType="student">
		select * from mybatis.student where tid = #{id}
</select>

Test:

@Test
		public void getTeacher() {
		TeacherMapper teacherMapper = MyBatisUtil.getSqlSession().getMapper(TeacherMapper.class);
		Teacher teacher = teacherMapper.getTeacher(1);
		System.out.println(teacher);
		/*
		What if id = 0? That is, configure result property = "Id" column = "Id" in collection
		Teacher (id = 1, name = teacher Qin, students = [student (id = 1, name = Xiaohong, TID = 1), student (id = 2, name = Xiaoming, TID = 1)
		*/
}

11.2: Joint table query

<! -- associated table query: nested query by result -- >
		<select id="getTeacher1" resultMap="teacherAndStudent">
		select  s.id sid,s.name sname,t.name tname,t.id tid
		from mybatis.teacher t,mybatis.student s
		where s.tid=t.id and t.id =#{id}
		</select>
		<resultMap id="teacherAndStudent" type="teacher">
		<result property="id" column="tid"/>
		<result property="name" column="tname"/>
		<! -- collection objects are bound with collection. Javatype returns a single property, not a collection,
		The return property is set bound with oftype -- >
		<collection property="students" ofType="student">
		<result property="id" column="sid"/>
		<result property="name" column="sname"/>
		<result property="tid" column="tid"/>
		</collection>
</resultMap>

Test:

@Test
		public void getTeacher1() {
		TeacherMapper teacherMapper = MyBatisUtil.getSqlSession().getMapper(TeacherMapper.class);
		Teacher teacher = teacherMapper.getTeacher1(1);
		System.out.println(teacher);
		/*
		Teacher (id = 1, name = Mary, students = [student (id = 1, name = small red, TID = 1), student (id = 2, name = Tom, TID = 1)
		*/
}

11.3: summary of multi table query

  • One of many to one: Association
  • Many in one to many: Collection
  • javaType & ofType

    • Javatype: Specifies the Java return value type of the attribute in the entity class
    • Oftype: map the list or some specified POJO generic types, and associate the generic type student in the list with oftype binding
  • Note:

    • To ensure the readability of SQL, it is recommended to use join table query

11.4: self study supplement of interview questions

  • MySQL engine
  • InnoDB underlying principle
  • Index and index optimization

12 dynamic SQL

  • Concept: dynamic SQL is one of the powerful features of mybatis, which simplifies the writing of native complex SQL
  • Four judgment conditions:

    • if
    • choose (when, otherwise)
    • trim (where, set)
    • foreach
  • Build database
CREATE TABLE blog(
		ID varchar (50) not null comment 'blog ID',
		Title varchar (100) not null comment 'blog title',
		Author varbinary (30) not null comment 'blogger',
		#Database time datetime type = date type in POJO
		#The hump command of underline name transferred to POJO needs mybatis to enable the hump command
		create_ Time datetime not null comment 'creation time',
		Views int (30) not null comment 'views'
)ENGINE=INNODB DEFAULT CHARSET = utf8;
  • POJO and hump naming
@Data
		@AllArgsConstructor
		@NoArgsConstructor
		public class Blog {
		private String id;
		private String title;
		private String author;
		/**
		*The hump command of underline name transferred to POJO needs mybatis to enable the hump command
		*/
		private Date createTime;
		private int views;
}
<! -- solve the hump command and use setting configuration. Only XX in the database can be used_ Hump in XX programming bean -- >
		<settings>
		<setting name="logImpl" value="STDOUT_LOGGING"/>
		<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
  • Utils
public class MyBatisUtil {
		/**
		*Enhance the scope of sqlsessionfactory for global use
		*/
		private static SqlSessionFactory sqlSessionFactory;

		static {
		try {
		/*
		Using mybatis as the first step, get the sqlsessionfactory object
		*/
		String resource = "mybatis-config.xml";
		InputStream inputStream = Resources.getResourceAsStream(resource);
		sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
		} catch (IOException e) {
		e.printStackTrace();
		}
		}

		/**
		*Sqlsessionfactory object get sqlsession instance
		*/
		public static SqlSession getSqlSession() {

		return sqlSessionFactory.openSession(true);
		}
		}
		//Randomly generate views in database tables
		public class UUIDUtils {
		public static String getId() {
		return UUID.randomUUID().toString().replaceAll("-", "");
		}

		@Test
		public void getUUId() {
		System.out.println(UUIDUtils.getId());
		}
}
  • Mapper interface
public interface BlogMapper {
		int addBlog(Blog blog);
		List<Blog> queryBlogByIf(Map map);
		List<Blog> queryBlogByWhere(Map map);
		List<Blog> queryBlogByForeach(Map map);
}

if

  • Concept: the common scenario of SQL is to judge and use
  • Method 1: when using directly through < if > or jump from < include > to SQL, you need to ensure that where is set up, so you need to add something similar in the SQL statementwhere 1= 1perhapswhere state = 'active'Etc

    • SQL fragment reuse is used here, as explained later

Learn mybatis

<! --if: jump to use through include, but the disadvantage is that the judgment condition must be written where 1=1-->
		<select id="queryBlogByIf" parameterType="map" resultType="Blog">
		select * from mybatis.blog where 1=1
		<include  refid="if_title_author_like" />
		</select>
		<sql id="if_title_author_like">
		<if test="title !=null">
		and  title like #{title}
		</if>
		<if test="author !=null">
		and author like #{author}
		</if>
</sql>
  • Mode 2: use the combination of “where” and “if”, you don’t need to add it manuallywhere 1= 1
<! -- if: it is judged by where to use / use if directly, but it is not recommended. Principle: if test exists, add where -- >
		<select id="queryBlogByWhere" parameterType="map" resultType="Blog">
		select * from mybatis.blog
		<where>
		<if test="id !=null">
		id like #{id}
		</if>
		<if test="views !=null">
		and views like #{views}
		</if>
		</where>
</select>
  • Test: fuzzy queries need to encapsulate wildcards
@Test
		public void queryBlogByIf() {
		SqlSession sqlSession = MyBatisUtil.getSqlSession();
		BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
		Map<String, String> map = new HashMap<>();
		//Fuzzy query, benefits of using map
		map.put("title", "%my%");
		List<Blog> blogs = blogMapper.queryBlogByIf(map);
		System.out.println(blogs);
		sqlSession.close();
		}

		@Test
		public void queryBlogByWhere() {
		SqlSession sqlSession = MyBatisUtil.getSqlSession();
		BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
		Map<String, Object> map = new HashMap<>();
		//map.put("id","%4%");
		map.put("views", "%2%");
		List<Blog> blogs = blogMapper.queryBlogByWhere(map);
		System.out.println(blogs);
		sqlSession.close();
}

choose

  • Concept: sometimes, we don’t want to use all the conditions, but just want to choose one of multiple conditions to use. In this case, mybatis provides the choose element, which is a bit like the switch statement in Java.
  • Requirement: when “title” is passed in, search by “title”; when “author” is passed in, search by “author”. If neither is passed in, a blog marked featured is returned
  • Details: choose can only satisfy one of them when other wisw; useWHERE state = ‘ACTIVE’To ensure the establishment of where
<! -- official website case -- >
		<select id="findActiveBlogLike" resultType="Blog">
		SELECT * FROM BLOG WHERE state = ‘ACTIVE’
		<choose>
		<when test="title != null">
		AND title like #{title}
		</when>
		<when test="author != null and author.name != null">
		AND author_name like #{author.name}
		</when>
		<otherwise>
		AND featured = 1
		</otherwise>
		</choose>
</select>

where

  • The disadvantage of using < if > alone: if there is no query condition or the first condition is not met, the wrong SQL statement will appear:
<select id="findActiveBlogLike"
		resultType="Blog">
		SELECT * FROM BLOG
		WHERE
		<if test="state != null">
		state = #{state}
		</if>
		<if test="title != null">
		AND title like #{title}
		</if>
		<if test="author != null and author.name != null">
		AND author_name like #{author.name}
		</if>
</select>
  • Error SQL:
#No conditions
		SELECT * FROM BLOG
		WHERE
		#The first condition doesn't hold
		SELECT * FROM BLOG
		WHERE
AND title like ‘someTitle’
  • Use < where >

    • Advantage: the where element inserts the where clause only if the child element returns anything. Also, if the beginning of a clause is “and” or, “,whereElements will also remove them.
<select id="findActiveBlogLike" resultType="Blog">
		SELECT * FROM BLOG
		<where>
		<if test="state != null">
		state = #{state}
		</if>
		<if test="title != null">
		AND title like #{title}
		</if>
		<if test="author != null and author.name != null">
		AND author_name like #{author.name}
		</if>
		</where>
</select>
  • Use custom trim elements to customize the functionality of where elements

    • Equivalent to < where > < trim >

      • Prefix = “where” if the conditions are met, the automatically added fields are: prefixoverrides automatically ignored fields, details: and | or both contain a space, which is the key point of writing correct SQL
<trim prefix="WHERE" prefixOverrides="AND |OR ">
		...
</trim>

set

  • Concept: used for dynamic update statements is called set. The set element can be used to dynamically contain columns that need to be updated and ignore other columns that do not

    • Equivalent trim statement,setThe element will dynamically insert the set keyword at the beginning of the line, and will delete the extra commas (these commas are introduced when using conditional statements to assign values to columns) = that is to say, mybatis automatically adds commas to you in set in update, but you add them by hand, < set > will also ignore them
<trim prefix="SET" suffixOverrides=",">
		...
</trim>
<update id="updateAuthorIfNecessary">
		update Author
		<set>
		<if test="username != null">username=#{username},</if>
		<if test="password != null">password=#{password},</if>
		<if test="email != null">email=#{email},</if>
		<if test="bio != null">bio=#{bio}</if>
		</set>
		where id=#{id}
</update>

foreach

  • Concept: another common use case of dynamic SQL is to traverse a collection (especially when building in conditional statements)
  • Native: SELECT * FROM blog WHERE 1=1 AND (id=1 OR id=2);
  • Details: if < where > multiple conditions are true, native and writing will be ignored

Learn mybatis

<select id="queryBlogByForeach" parameterType="map" resultType="Blog">
		select * from mybatis.blog
		<where>
		<foreach collection="ids" item="id" open="(" separator="or" close=")">
		id=#{id}
		</foreach>
		</where>
		</select>
		Case of official website: when using in -- >
		<select id="selectPostIn" resultType="domain.blog.Post">
		SELECT *
		FROM POST P
		WHERE ID in
		<foreach item="item" index="index" collection="list"
		open="(" separator="," close=")">
		#{item}
		</foreach>
</select>

SQL fragment

  • Review: the previous < where > combined with < if >, we extract and reuse public SQL statements
  • Use: < SQL id = > tags and < include refid = > references
  • Details:

    • It is better to use SQL fragments based on a single table. Tables with multiple tables are not necessarily supported
    • Use SQL fragment reuse instead of the “where” tag, because it has built-in fields that will be ignored
<sql id="if_title_author_like">
		<if test="title !=null">
		and  title like #{title}
		</if>
		<if test="author !=null">
		and author like #{author}
		</if>
		</sql>

		<select id="queryBlogByIf" parameterType="map" resultType="Blog">
		select * from mybatis.blog where 1=1
		<include refid="if_title_author_like" />
</select>

Bind (understanding)

  • The bind element allows you to create a variable outside the ognl expression and bind it to the current context. For example:
<select id="selectBlogsLike" resultType="Blog">
		<bind name="pattern" value="'%' + _parameter.getTitle() + '%'" />
		SELECT * FROM BLOG
		WHERE title LIKE #{pattern}
</select>

13 cache

  • When we query the same data again, we directly go to cache, and then we don’t need to save any more, so we can solve the speed problem
  • What kind of data needs to be cached?

    • Data that is frequently queried and not frequently changed can be cached

Cache schematic (important)

Working principle of L2 cache:

  • A sqlsession is a level-1 cache. After the query operation, it is saved in the level-1 cache by default
  • If level-2 cache is enabled, level-1 cache must be closed first, and then the cache data will be saved to level-2 cache
  • In the second query, the user operation will ignore the search in the secondary cache

Learn mybatis

L1 cache

  • By default, only local session (first level) caching is enabled, which only caches data in one session.

    • Think of L1 cache as a map in a session for easy understanding

Learn mybatis

  • Cache failure

    • The addition, deletion and modification will invalidate all SQL caches. Next time, it will be rewritten and checked from the database
    • Query different things, query different mapper.xml
    • Clear cache manually: sqlSession.clearCache();

L2 cache

  • Mybatis config, XML enable global cache
<settings>
		<setting name="logImpl" value="STDOUT_LOGGING"/>
		<! -- enable global cache is enabled by default, display and write are easy to read -- >
		<setting name="cacheEnabled" value="true"/>
</settings>
  • mappper.xml Enable L2 cache:

    <! -- enable L2 cache -- >
    		<cache/>
    • The results of all select statements in the mapping statement file will be cached.
    • All insert, update, and delete statements in the mapping statement file refresh the cache.
    • Cache will useThe least recently used algorithm (LRU) is used to clear unnecessary cache.
    • The cache does not refresh periodically (that is, there is no refresh interval).
    • The cache saves 1024 references to a list or object, regardless of which query method returns.
    • Cache is treated as read / write cache, which means that the acquired object is not shared and can be modified safely by the caller without interfering with the potential modifications made by other callers or threads.

      • Read / write cache requires POJO to enable serialization
@Data
		@AllArgsConstructor
		@NoArgsConstructor
		public class User implements Serializable {
		private int id;
		private String name;
		private String pwd;

		public static void main(String[] args) {
		new ArrayList<>();
		new HashMap<>();
		new LinkedList<>();
		}
}
  • Parameters can be specified when enabling L2 cache

    <cache
    				eviction="FIFO"
    				flushInterval="60000"
    				size="512"
    		readOnly="true"/>
    • Eviction: clear algorithm, default LRU

      • LRU– last least used: remove objects that are not used for the longest time.
      • FIFO– first in, first out: remove objects in the order they enter the cache.
      • SOFT– soft reference: removes objects based on garbage collector state and soft reference rules.
      • WEAK– weak reference: more actively remove objects based on garbage collector state and weak reference rules.
    • Flushinterval: refresh cache interval, none by default
    • Size: maximum number of caches, default: 1024
    • Readonly: read-only cache; write operation will not go through the cache and query directly from the database. The default is read / write cache
  • Use L2 cache

    • Test statement
public class MyTest {
		@Test
		public void queryUserById() {
		SqlSession sqlSession = MyBatisUtil.getSqlSession();
		SqlSession sqlSession1 = MyBatisUtil.getSqlSession();
		UserMapper mapper = sqlSession.getMapper(UserMapper.class);
		User user = mapper.queryUserById(1);
		//Close the last sqlsession. If you enable the second level cache, the first level cache will be saved to the second level cache
		sqlSession.close();
		System.out.println("=================");

		UserMapper mapper1 = sqlSession1.getMapper(UserMapper.class);
		//If the second level cache is turned on, the next query operation of the same mapper will first repeat the search in the second level cache
		User user1 = mapper1.queryUserById(1);
		sqlSession1.close();
		}
}

Learn mybatis

Learn mybatis

Custom cache (learn)

  • Concept: ehcache is a distributed cache, mainly for general purpose cache
  • Handwriting or importing third-party ehcache cache dependency
  • So you can customize ehcache.xml configuration file
<!-- https://mvnrepository.com/artifact/org.mybatis.caches/mybatis-ehcache -->
		<dependency>
		<groupId>org.mybatis.caches</groupId>
		<artifactId>mybatis-ehcache</artifactId>
		<version>1.2.0</version>
</dependency>
  • stay mapper.xml Medium configuration
 <cache type="org.mybatis.caches.encache.EhcacheCache"/>
  • Create in resource ehcache.xml File:
<?xml version="1.0" encoding="UTF-8"?>
		<ehcache xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
		xsi:noNamespaceSchemaLocation="http://ehcache.org/ehcache.xsd"
		updateCheck="false">
      
		<defaultCache
		eternal="false"
		maxElementsInMemory="10000"
		overflowToDisk="false"
		diskPersistent="false"
		timeToIdleSeconds="1800"
		timeToLiveSeconds="259200"
		memoryStoreEvictionPolicy="LRU"/>
   
		<cache
		name="cloud_user"
		eternal="false"
		maxElementsInMemory="5000"
		overflowToDisk="false"
		diskPersistent="false"
		timeToIdleSeconds="1800"
		timeToLiveSeconds="1800"
		memoryStoreEvictionPolicy="LRU"/>
      
		<!--
		Diskstore: is the cache path. Ehcache is divided into two levels: memory and disk. This attribute defines the cache location of the disk. The parameters are explained as follows:
		user.home  – user home directory
		user.dir   – user's current working directory
		java.io.tmpdir  – default temporary file path
		-->
		<diskStore path="java.io.tmpdir/Tmp_EhCache"/>
		<!--
		Defaultcache: the default cache policy, which is used when ehcache cannot find the defined cache. Only one can be defined.
		-->
		<!--
		Name: cache name.
		Maxelementsinmemory: maximum number of caches
		Maxelementsondisk: the maximum number of caches on the hard disk.
		Etarnal: whether the object is permanently valid. Once set, timeout will not work.
		Overflow todisk: save to disk or not, when the system crashes
		Timetoidleseconds: sets the allowed idle time (in seconds) of the object before it expires. Optional property, the default value is 0, that is, infinite idle time.
		Timetoliveseconds: sets the allowed lifetime (in seconds) of an object before it expires. The maximum time is between creation time and expiration time. It is only used when the ternal = false object is not permanently valid. The default value is 0., that is, the object's lifetime is infinite.
		Diskpersistent: whether to cache the data in the restart period of the virtual machine. The default value is false
		Diskspoolbuffersizemb: this parameter sets the size of the diskstore's cache. The default is 30MB. Each cache should have its own buffer.
		Diskexpirythreadintervalseconds: disk expiration thread run time interval. The default is 120 seconds.
		Memorystoreevaluation policy: when the maxelementsinmemory limit is reached, ehcache will clean up the memory according to the specified policy. The default policy is LRU (least recently used). You can set it to FIFO (first in, first out) or LFU (less used).
		Clearonflush: whether to clear when the memory quantity is the largest.
		Memorystoreevictionpolicy: the optional policies are LRU (least recently used, default policy), FIFO (first in first out), LFU (least number of accesses).
		FIFO, first in first out, which is the most familiar, first in, first out.
		LFU, less frequently used, is the strategy used in the above example. To be frank, it is the least used strategy. As mentioned above, the cached element has a hit attribute, and the one with the lowest hit value will be cleared out of the cache.
		LRU, least recently used, the cached element has a timestamp. When the cache capacity is full, and it needs to make room to cache new elements, the element with the farthest timestamp from the current time in the existing cache element will be cleared out of the cache.
		-->
</ehcache>

Redis

  • Self study, another start