- what is MyBatis?
MyBatis is one of the most commonly used persistence layer frameworks in Java development. What is persistence?
Persistence is to store program data in lasting Status and instantaneous The mechanism of transition between states. In popular terms, transient data (such as data in memory, which cannot be permanently saved) is persisted into persistent data (such as persistent to the database, which can be saved for a long time)
The persistence layer is this part of the code block to realize persistence. There are many solutions for persistence. The mainstream ones are JPA, mybatis, hibernate, etc. Although there are differences in implementation and use, in the final analysis, they are the encapsulation of JDBC.
How to play MyBatis?
Introduction to MyBatis
1.mybatis-config. Data source configuration of MySQL 8 in XML core configuration file
<environments default="mysql">
<!-- to configure mysql environment-->
<environment id="mysql">
<!-- Configure transactions-->
<transactionManager type="JDBC"></transactionManager>
<!-- Configure connection pool-->
<dataSource type="POOLED">
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/mybatis?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT&nullCatalogMeansCurrent = true"/>
<property name="username" value="root"/>
<property name="password" value="*****"/>
</dataSource>
</environment>
<mappers>
</mappers>
</environments>
2. Create entity class and dao layer interface
3. The configuration file of Dao layer interface (for example, write a configuration file corresponding to UserDao). Now this configuration file is equivalent to the implementation class of the interface
<?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">
<!-- The namespace should correspond to the full package name of the interface id Write the full package name to the method return type of the corresponding interface-->
<mapper namespace="com.xwl.dao.IUserDao">
<select id="getList" resultType="com.xwl.pojo.User">
select * from User
</select>
</mapper>
<!-- Maven Resource filtering, resulting in src Configuration file in, not generated to target Directory, in pom.xml It can be solved under medium configuration -->
<build>
<resources>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.properties</include>
<include>**/*.xml</include>
</includes>
<filtering>false</filtering>
</resource>
<resource>
<directory>src/main/resources</directory>
<includes>
<include>**/*.properties</include>
<include>**/*.xml</include>
</includes>
<filtering>false</filtering>
</resource>
</resources>
</build>
4. Write a tool class to facilitate obtaining sqlSession
/**
* Mybatis Tool class
*/
public class MybatisUtils {
//sqlSession factory
private static SqlSessionFactory sqlSessionFactory;
//Static code block, create an instance of sqlSessionFactory
static{
try {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
}catch (Exception e){
e.printStackTrace();
}
}
/**
* Obtain an sqlSession instance through factory mode
* sqlSession Equivalent to the previous PreparedStatement
* Methods such as CRUD are encapsulated
* @return sqlSession example
*/
public static SqlSession getSqlSession(){
return sqlSessionFactory.openSession();
}
}
5. Test type test
@Test
public void testGetList(){
SqlSession sqlSessioin = MybatisUtils.getSqlSession();
IUserDao userDao = sqlSession.getMapper(IUserDao.class);
List<User> userList = userDao.getList();
}
MyBatis CRUD
1.Dao layer defines corresponding interfaces and CRUD methods
/**
* User Class
*/
public interface IUserDao {
/**
* Get user list
* @return User list
*/
List<User> getList();
/**
* Get user by Id
* @param id User id
* @return user
*/
User getById(int id);
/**
* Add a user
* @param user user
* @return Number of records added
*/
int addOne(User user);
/**
* Delete a User by id
* @param id User id
* @return Number of records deleted
*/
int deleteById(int id);
/**
* Update a user
* @param user user
* @return Number of records updated
*/
int updateOne(User user);
}
2.mapper. Writing sql in XML 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">
<mapper namespace="com.kuang.dao.IUserDao">
<select id="getList" resultType="com.kuang.pojo.User">
select * from User
</select>
<select id="getById" parameterType="int" resultType="com.kuang.pojo.User">
select * from User where id = #{id}
</select>
<insert id="addOne" parameterType="com.kuang.pojo.User">
insert into User (id,name,pwd) values(#{id},#{name},#{pwd})
</insert>
<delete id="deleteById" parameterType="int">
delete from User where id = #{id}
</delete>
<update id="updateOne" parameterType="com.kuang.pojo.User">
update User set name = #{name},pwd = #{pwd} where id = #{id}
</update>
</mapper>
3. Use these interfaces through sqlSession and perform JUnit test
package com.kuang.dao;
import com.kuang.pojo.User;
import com.kuang.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.List;
public class UserDaoTest {
@Test
public void testGetList(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
IUserDao userDao = sqlSession.getMapper(IUserDao.class);
List<User> userList = userDao.getList();
userList.forEach(x->{
System.out.println(x.getName());
});
}
@Test
public void testGetListById(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
IUserDao userDao = sqlSession.getMapper(IUserDao.class);
User user = userDao.getById(1);
System.out.println(user.getName());
}
@Test
public void testAddOne(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
IUserDao userDao = sqlSession.getMapper(IUserDao.class);
userDao.addOne(new User(4,"ljg","123"));
sqlSession.commit();
sqlSession.close();
}
@Test
public void testDeleteById(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
IUserDao userDao = sqlSession.getMapper(IUserDao.class);
userDao.deleteById(4);
sqlSession.commit();
sqlSession.close();
}
@Test
public void testUpdateOne(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
IUserDao userDao = sqlSession.getMapper(IUserDao.class);
userDao.updateOne(new User(1,"zhuzhu","345"));
sqlSession.commit();//All additions, deletions and modifications must be committed
sqlSession.close();
}
}
Map and fuzzy query
1.Map is a panacea for transferring parameters. You only need to fill in the #{} Key in sql to transfer parameters to this place. It is suitable for transferring multiple parameters
/**
* Get users by Id and name
* @param map It is more flexible to transfer parameters through map
* @return user
*/
User getByIdAndName(Map<String,Object> map);
<select id="getByIdAndName" parameterType="map" resultType="com.kuang.pojo.User">
select * from User where id = #{id} and name = #{username}
</select>
@Test
public void testGetListByIdAndName(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
IUserDao userDao = sqlSession.getMapper(IUserDao.class);
Map<String,Object> map = new HashMap<>();
map.put("id",1);
map.put("username","zhuzhu");
User user = userDao.getByIdAndName(map);
System.out.println(user.getName());
}
2. How to write fuzzy query
/**
* Fuzzy query by name
* @param name user name
* @return user
*/
List<User> getByName(String name);
//Either of the following two ways can be used, or directly like #{name}, adding "%%" when passing parameters
<select id="getByName" resultType="com.kuang.pojo.User">
select * from User where name like "%"#{name}"%"
</select>
<!-- #{} is a placeholder ${} is a string splice -- >
<select id="getByName" resultType="com.kuang.pojo.User">
select * from User where name like '%${name}%'
</select>
sql injection exists in the above description
Mybatis master profile
1. Environment
<?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 can cooperate with multiple environments, which can be selected through development
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT&nullCatalogMeansCurrent = true"/>
<property name="username" value="root"/>
<property name="password" value="a2192500"/>
</dataSource>
</environment>
<environment id="test">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT&nullCatalogMeansCurrent = true"/>
<property name="username" value="root"/>
<property name="password" value="a2192500"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="com/kuang/dao/UserMapper.xml" />
</mappers>
</configuration>
<transactionManager type="JDBC"/> //In addition to JDBC, you can also learn about Management
<dataSource type="POOLED"> //type=["POOLED"|"UNPOOLED"|JNDI]
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT&nullCatalogMeansCurrent = true"/>
<property name="username" value="root"/>
<property name="password" value="a2192500"/>
</dataSource>
2.properties property
You can import the configuration file through the properties attribute, which should be written in front of environments
Write a configuration file db.properties driver=com.mysql.cj.jdbc.Driver url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT&nullCatalogMeansCurrent=true username=root password=a2192500
<properties resource="db.properties"/> //Read external configuration file
<dataSource type="POOLED">
<property name="driver" value="${driver}"/>//The configuration in properties is introduced through ${}, and the external configuration file is read first
<property name="url" value="${url}"/>
<property name="username" value="${username}"/>
<property name="password" value="${password}"/>
</dataSource>
3. Alias typeAliases
<!-- Set the alias directly through the full class name-->
<typeAliases>
<typeAlias type="com.kuang.pojo.User" alias="user"/>
</typeAliases>
<!-- Direct scanning pojo For all classes in the class, the initial lowercase of the class name is used as the default alias -->
<typeAliases>
<package name="com.kuang.pojo"/>
</typeAliases>
<!-- In addition to the above two, you can also add an annotation directly to the class@Alias(),Set alias
4. Set settings
<!--setting It is written in detail in the official documents. The following is a log setting -->
<settings>
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
5. Map mapper
<!--Profile path import -->
<mappers>
<mapper resource="com/kuang/dao/UserMapper.xml" />
</mappers>
<!-- Full class name -->
<mappers>
<mapper resource="com.kuang.dao.UserMapper.xml" />
</mappers>
<!-- Package import method -->
<mappers>
<package name="com.kuang.dao"/>
</mappers>
Life cycle of several important objects
[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-2qoqtxro-1623571607205) (/ users / xwl / library / Application Support / typora user images / image-20210612132230366. PNG)]
In the process of using Mybatis, we mainly use sqlSessionFactoryBuilder, sqlSessionFactory and sqlSession. sqlSessionFactoryBuilder is only used to produce a seqSessionFactory, which is not needed after it is used up, while seqSessionFactory produces our seqSession, which must have a value, that is, its life cycle is the beginning to end of the whole application, Each seqSession is equivalent to a connection to the database. Close it after using it. Do not connect it with a value and occupy resources.
resultMap attribute mapping
1. When the column names in our database are inconsistent with our attribute names, we can use resultMap to modify the mapping relationship between them
<resultMap id="userMap" type="com.kuang.pojo.User">
<result column="pwd" property="password"/>
</resultMap>
Dynamic sql
1. In order to simplify the tedious sql splicing, MyBatis provides some special tags
<!-- -->
<select id="getById" parameterType="int" resultType="com.kuang.pojo.User">
select * from User where
<if test="id != null">
id = #{id}
</if>
<if test="name != null">
and name = #{name}
</if>
</select>
2. There are some problems with the above sql. When the first condition is not satisfied and the second condition is satisfied, the sql statement becomes, where and name = #{name}, which is obviously wrong. Therefore, mybatis provides a label to solve this problem
<!-- When id When empty,Return all data-->
<select id="getById" parameterType="int" resultType="com.kuang.pojo.User">
select * from User
<where>
<if test="id != null">
id = #{id}
</if>
<if test="name != null">
and name = #{name}
</if>
</where>
</select>
3. Equivalent to switch case default
<select id="getById" parameterType="int" resultType="com.kuang.pojo.User">
select * from User
<where>
<choose>
<when test="id != null">
id = #{id}
</when>
<otherwise>
name = #{name}
</otherwise>
</choose>
</where>
</select>
4. Similar to where, it can automatically remove redundant commas and use them in update statements
5. Functions that trim can replace
6.
<foreach item="id" collection="ids">
open="(" separator="," close=")"
#{id}
</foreach>