1: Link to idea database
2: The first mybatis process
1: The package and xml interceptor required by Maven are intercepted and scanned
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>org.example</groupId>
<artifactId>mybatis-study</artifactId>
<packaging>pom</packaging>
<version>1.0-SNAPSHOT</version>
//Subproject
<modules>
<module>mybatis-01-hello</module>
<module>mybatis-02-baseKnowledge</module>
</modules>
<dependencies>
<!-- mysql-->
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
<!-- mybatis-->
<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.2</version>
</dependency>
<!-- junit-->
<!-- https://mvnrepository.com/artifact/junit/junit -->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
<!-- https://mvnrepository.com/artifact/log4j/log4j -->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
<!-- Automatic generation of entity classes get set And construction method and toString -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.20</version>
</dependency>
</dependencies>
<!-- Interception identification xml Useful -->
<build>
<resources>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.xml</include>
<include>**/*.properties</include>
</includes>
<filtering>false</filtering>
</resource>
<resource>
<directory>src/main/resources</directory>
<includes>
<include>**/*.xml</include>
<include>**/*.properties</include>
</includes>
<filtering>false</filtering>
</resource>
</resources>
</build>
</project>
2: Tool class: get sqlSession
package com.atshikai.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 {
// The first step to using mybatis: get the sqlSessionFactory factory
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
} catch (IOException e) {
e.printStackTrace();
}
}
// Equivalent to getConnection annotation
public static SqlSession getSqlSession(){
// Change to true to automatically commit transactions
return sqlSessionFactory.openSession(true);
}
}
About sqlsession
1 once SqlSessionFactory is created, SqlSessionFactoryBuilder will be destroyed - local variables
2 SqlSessionFactory: it can be imagined that once the database connection pool is created, the singleton mode always exists during operation
3 SqlSession is a request to connect to the connection pool. It cannot be shared. Close it immediately after it is used up
4 a map is equivalent to a specific business!!!
3:mybatis-config.xml configuration file
<?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>
<!--1 Import profile-->
<properties resource="db.properties"/>
<!--2 Set log file-->
<settings>
<!-- Solve the inconsistency between database and entity class names(Strict hump naming) database g_shi==Entity class gShi-->
<setting name="mapUnderscoreToCamelCase" value="true"/>
<setting name="logImpl" value="STDOUT_LOGGING"/>
<!--The package needs to be imported for operation-->
<!-- <setting name="logImpl" value="LOG4J"/>-->
</settings>
<!-- 3 The first letter of the class name is lowercase to start the alias, and then used for the return value-->
<typeAliases>
<package name="com.atshikai.pojo"/>
</typeAliases>
<!--4 Environment configuration-->
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<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>
<!-- 5 Register mapper Mapping-->
<mappers>
//Direct absolute path lookup
<!--<mapper resource="com/atshikai/dao/userMapper.xml"/>-->
//The following two need to be in the same path
<mapper class="com.atshikai.dao.UserMapper"/>
<!-- <package name="com.atshikai.dao"/>-->
</mappers>
</configuration>
4: Entity class
package com.atshikai.pojo;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data //get，set
@NoArgsConstructor //Nonparametric structure
@AllArgsConstructor //Parametric structure
public class User {
private int id;
private String name;
private String pwd;
}
Logj print log generation
5: Entity class interface
package com.atshikai.dao;
import com.atshikai.pojo.User;
import java.util.List;
import java.util.Map;
public interface UserMapper {
List<User> getUserList();
//Query by id
User selectById(int id);
// Fuzzy query
List<User> selectIgnore(String name);
// increase
int insertUser(User user);
// Adding User objects using map
int addUser(Map<String,Object> map);
// Delete
int deleteUser(int id);
// change
int updateUser(User user);
}
6: Entity class interface (write sql statement)
<?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.atshikai.dao.UserMapper">
<select id="getUserList" resultType="com.atshikai.pojo.User">
select * from mybatis.user
</select>
<select id="selectById" resultType="com.atshikai.pojo.User" parameterType="int">
select * from mybatis.user where id=#{id};
</select>
<!-- Fuzzy query-->
<select id="selectIgnore" parameterType="String" resultType="com.atshikai.pojo.User">
select * from mybatis.user where name like #{value}
</select>
<insert id="insertUser" parameterType="com.atshikai.pojo.User">
insert into mybatis.user(id,name,pwd) values(#{id},#{name},#{pwd});
</insert>
<!-- use map When adding object parameters, you should use #{} is not ${} -- >
<insert id="addUser" parameterType="java.util.Map">
insert into mybatis.user values(#{id},#{name},#{pwd});
</insert>
<delete id="deleteUser" parameterType="int">
delete from mybatis.user where id=#{id};
</delete>
<update id="updateUser" parameterType="com.atshikai.pojo.User">
update mybatis.user set name=#{name},pwd=#{pwd} where id=#{id};
</update>
</mapper>
7: Test
import com.atshikai.dao.UserMapper;
import com.atshikai.pojo.User;
import com.atshikai.utils.MyBatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class MyTest {
@Test
public void testSelect(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> userList = mapper.getUserList();
for (User user:userList){
System.out.println(user);
}
sqlSession.commit();
sqlSession.close();
}
@Test
public void testInsert(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
int i = mapper.insertUser(new User(4, "Gaskay 5", "666"));
System.out.println(i);
sqlSession.commit();
sqlSession.close();
}
// Add users with map
@Test
public void testMapAdd(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
Map<String,Object> map=new HashMap<String, Object>();
map.put("id",7);
map.put("name","Jian Zhang");
map.put("pwd","888");
mapper.addUser(map);
sqlSession.commit();
sqlSession.close();
}
// Fuzzy query
@Test
public void selectIgnore(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> users = mapper.selectIgnore("%cover%");
for(User user:users){
System.out.println(user);
}
sqlSession.commit();
sqlSession.close();
}
@Test
public void selectById(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = mapper.selectById(2);
System.out.println(user);
sqlSession.commit();
sqlSession.close();
}
@Test
public void deleteUser(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
int i = mapper.deleteUser(2);
System.out.println(i);
sqlSession.commit();
sqlSession.close();
}
@Test
public void updateUser(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
int i = mapper.updateUser(new User(1, "Kaikai", "888"));
System.out.println(i);
sqlSession.commit();
sqlSession.close();
}
}
3: The difference between writing sql # () and ${}
Can be used, but #{} it can prevent sql injection and is safer
4resultType and resultMap
resultType
1: resultType can encapsulate the query results into pojo type, but the property name of pojo class must be consistent with the field name of the queried database table.
2: If the field queried by sql is inconsistent with the property name of pojo, you need to use resultMap to correspond the field name and property name, conduct manual configuration and encapsulation, and map the results to pojo
resultMap
resultMap can map query results into POJOs of complex types. For example, POJOs and list s are included in the query result mapping object to realize one-to-one query and one to many query.
5: Multi table query (one to many, many to one)
Summary: points for attention
Many to one (multiple students correspond to one teacher)
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.atshikai.dao.StudentMapper">
<!-- Nested processing according to results resultMap Associated external sql-->
<select id="getStudents1" resultMap="studentMap1">
select s.id sid,s.name sname,t.id,t.name tname
from student s,teacher t
where s.tid=t.id;
</select>
<resultMap id="studentMap1" type="student">
<result property="id" column="sid"/>
<result property="name" column="sname"/>
<!-- For complex queries, we need to deal with them separately. Objects: association Set: collection-->
<association property="teacher" javaType="Teacher">
<result property="id" column="id"/>
<result property="name" column="tname"/>
</association>
</resultMap>
<!-- ================================================================ -->
<!--
Idea: 1 query the information of all students, 2 according to the students found id，Find the corresponding teacher
-->
<resultMap id="studentMap" type="student">
<result property="id" column="id"/>
<result property="name" column="name"/>
<!-- For complex queries, we need to deal with them separately. Objects: association Set: collection-->
<association property="teacher" column="tid"
javaType="Teacher" select="getTeacher"/>
</resultMap>
<select id="getStudents" resultMap="studentMap">
select * from student
</select>
<select id="getTeacher" resultType="teacher">
select * from teacher where id=#{Tid};
</select>
</mapper>
One to many (one teacher corresponds to multiple students)
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.atshikai.dao.TeacherMapper">
<!-- Subquery-->
<select id="getTeachers1" resultMap="teacherMap1">
select * from teacher t where t.id=#{Tid}
</select>
<resultMap id="teacherMap1" type="Teacher">
<result property="id" column="id"/>
<result property="name" column="name"/>
<collection property="students" ofType="Student"
javaType="ArrayList" select="queryStudentByTid" column="id"/>
</resultMap>
<select id="queryStudentByTid" resultType="Student">
select * from student where tid=#{Tid}
</select>
<!-- =====================================================-->
<!-- Joint query-->
<select id="getTeachers" resultMap="teacherMap">
select s.id sid,s.name sname, t.id tid,t.name tname
from student s,teacher t
where s.tid=tid and tid=#{Tid}
</select>
<resultMap id="teacherMap" type="Teacher">
<result property="id" column="tid"/>
<result property="name" column="tname"/>
<collection property="students" ofType="Student">
<result property="id" column="sid"/>
<result property="name" column="sname"/>
<result property="tid" column="tid"/>
</collection>
</resultMap>
</mapper>
5: Dynamic sql statement
For automatically generated id classes
package com.atshikai.utils;
import java.util.UUID;
public class IdUtils {
public static String getId(){
return UUID.randomUUID().toString().replace("-","");
}
}
The so-called dynamic sql is still an sql statement in essence, only adding logic code to it
- sql fragment: query include (try not to use where in include)
<sql id="if-title-author">
<if test="title!=null">
and title=#{title}
</if>
<if test="author!=null">
and author=#{author}
</if>
</sql>
<select id="queryBlogIf" parameterType="map" resultType="blog">
select * from blog where 1=1
<include refid="if-title-author"></include>
</select>
- Query where,choose,when(where nesting can intelligently remove useless and. If there is nothing nested in where, it will not affect code execution)
<select id="queryBlogChoose" parameterType="map" resultType="blog">
select * from blog
<where>
<choose>
<when test="title!=null">
and title=#{title}
</when>
<when test="author!=null">
and author=#{author}
</when>
<otherwise>
views=#{views}
</otherwise>
</choose>
</where>
</select>
- Update set (you can intelligently remove unnecessary ones. If there is nothing in the set, an error will occur)
<update id="updateBlog" parameterType="map">
update blog
<set>
<if test="title!=null">
title=#{title},
</if>
<if test="author!=null">
author=#{author}
</if>
</set>
where id=#{id}
</update>
- Query for each
<select id="queryByIDs" parameterType="map" resultType="blog">
select * from blog
<where>
<foreach collection="ids" item="id"
open="(" separator="or" close=")">
id=#{id}
</foreach>
</where>
</select>
6:mybatis L1 L2 cache
See episode 27, 28 and 29 of crazy God in detail
Summary:
L1 cache: equivalent to a map
Cache invalidation:
1. Inquire about different things;
2. Adding, deleting and modifying may change the previous operation, so it must be refreshed
3. Query different mapper xml
4. Manually clean up the cache (the L1 cache is enabled by default and cannot be closed) sqlsession clearCache();
L2 cache
It's me (L1 cache) that dies, and then pass the cache on to you (L2 cache)