Previously we introduced the use of XML configuration method to complete the operations of adding, deleting, modifying and querying data. When using this method, you need to use [namespace.tag number] when actually calling. This method is very convenient when writing SQL statements, but when executing The SQL statement link is not very elegant; in addition, we also introduced the use of mappers to complete the addition, deletion, modification, and query operations of data. Using this method, you can directly call the method in the interface to execute when actually calling, but when writing SQL statements, It seems unfriendly, especially when writing dynamic SQL statements, it is very troublesome; if the two methods can be combined, it will be more elegant to use the mapper call after configuring the dynamic SQL in XML. In this article, we will introduce the mapper and XML configuration separation of duties.
If you don’t know much about the Mybatis mapper and XML configuration, it is recommended that you understand it first before reading this article. You can refer to:
Mybatis uses mappers to query (@Select), add (@Insert), modify (@Update), and delete (@Delete)
https://blog.csdn.net/m1729339749/article/details/132831561Mybatis query datahttps://blog.csdn. net/m1729339749/article/details/132469672Mybatis insert, modify, delete https://blog.csdn.net/m1729339749/article/ details/132601345
1. Data preparation
Here we directly use the script to initialize the data in the database
--Create the database if it does not exist CREATE DATABASE IF NOT EXISTS demo DEFAULT CHARSET utf8; --Switch database USE demo; -- Create user table CREATE TABLE IF NOT EXISTS T_USER( ID INT PRIMARY KEY, USERNAME VARCHAR(32) NOT NULL, AGE INT NOT NULL ); --Insert user data INSERT INTO T_USER(ID, USERNAME, AGE) VALUES(1, 'Zhang San', 20),(2, 'Li Si', 22),(3, 'Wang Wu', 24);
Created a database named demo; created a user table named T_USER in the library and inserted data into the table
2. Create entity classes
Create UserInfo and UserInfoQuery classes under cn.horse.demo
UserInfoClass:
package cn.horse.demo;
public class UserInfo {
private Integer id;
private String name;
private Integer age;
public void setId(Integer id) {
this.id = id;
}
public Integer getId() {
return id;
}
public void setName(String name) {
this.name = name;
}
public String getName() {
return name;
}
public void setAge(Integer age) {
this.age = age;
}
public Integer getAge() {
return age;
}
@Override
public String toString() {
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.append('{');
stringBuilder.append("id: " + this.id);
stringBuilder.append(", ");
stringBuilder.append("name: " + this.name);
stringBuilder.append(", ");
stringBuilder.append("age: " + this.age);
stringBuilder.append('}');
return stringBuilder.toString();
}
}
UserInfoQueryClass:
package cn.horse.demo;
public class UserInfoQuery {
private Integer startAge;
private Integer endAge;
public void setStartAge(Integer startAge) {
this.startAge = startAge;
}
public Integer getStartAge() {
return startAge;
}
public void setEndAge(Integer endAge) {
this.endAge = endAge;
}
public Integer getEndAge() {
return endAge;
}
}
3. Create mapper and Mapper configuration
Create the UserInfoMapper interface under cn.horse.demo
UserInfoMapperInterface:
package cn.horse.demo;
import org.apache.ibatis.annotations.*;
import java.util.List;
public interface UserInfoMapper {
List<UserInfo> find(@Param("query") UserInfoQuery query);
Integer insert(@Param("userInfoList") List<UserInfo> userInfoList);
Integer update(@Param("userInfoList") List<UserInfo> userInfoList);
Integer delete(@Param("idList") List<Integer> idList);
}
Create the cn/horse/demo directory under resources, and create the UserInfoMapper.xml configuration file in this directory
UserInfoMapper.xmlConfiguration:
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.horse.demo.UserInfoMapper">
<resultMap id="userInfoMap" type="cn.horse.demo.UserInfo">
<result column="ID" property="id" />
<result column="USERNAME" property="name"/>
<result column="AGE" property="age"/>
</resultMap>
<select id="find" parameterType="cn.horse.demo.UserInfoQuery" resultMap="userInfoMap">
SELECT
ID,
USERNAME,
AGE
FROM T_USER
<where>
<if test="null != query.startAge">
AND AGE >= #{query.startAge}
</if>
<if test="null != query.endAge">
AND AGE & amp;lt;= #{query.endAge}
</if>
</where>
</select>
<insert id="insert">
INSERT INTO T_USER(ID,
USERNAME,
AGE)
VALUES
<foreach collection="userInfoList" item="userInfo" separator=",">
(#{userInfo.id}, #{userInfo.name}, #{userInfo.age})
</foreach>
</insert>
<update id="update">
<foreach collection="userInfoList" item="userInfo" separator=";" close=";">
UPDATE T_USER
<set>
<if test="null != userInfo.name and '' != userInfo.name">
USERNAME = #{userInfo.name},
</if>
<if test="null != userInfo.age">
AGE = #{userInfo.age},
</if>
</set>
WHERE ID = #{userInfo.id}
</foreach>
</update>
<delete id="delete">
DELETE FROM T_USER
<where>
ID IN
<foreach collection="idList" item="id" open="(" separator="," close=")">
#{id}
</foreach>
</where>
</delete>
</mapper>
Note:
- The UserInfoMapper.xml configuration file needs to be consistent with the directory where the UserInfoMapper interface is located, because when the UserInfoMapper interface is loaded, UserInfoMapper.xml in the same directory will be loaded. Configuration file, if the directory is inconsistent, the UserInfoMapper.xml configuration file will not be loaded.
- The namespace in the UserInfoMapper.xml configuration file must be the fully qualified class name of the UserInfoMapper interface, and the tag in the UserInfoMapper.xml configuration file must be The number needs to be consistent with the method name in the UserInfoMapper interface.
4. Import configuration file
Create a new mybatis-config.xml configuration file under resources and introduce the UserInfoMapper mapper.
<?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>
<settings>
<setting name="logImpl" value="JDK_LOGGING"/>
</settings>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="org.gjt.mm.mysql.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/demo?useUnicode=true & amp;amp;useSSL=false & amp;amp;characterEncoding=utf8 & amp;amp;allowMultiQueries =true"/>
<property name="username" value="root"/>
<property name="password" value="horse"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper class="cn.horse.demo.UserInfoMapper" />
</mappers>
</configuration>
Here we use mapper to introduce the mapper. We only need to set the class attribute to the fully qualified class name of the UserInfoMapper interface.
5. Startup program configuration
1. Conversation tool class
Create a new SqlSessionUtils tool class under the cn.horse.demo package
package cn.horse.demo;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.InputStream;
import java.util.Objects;
public class SqlSessionUtils {
private static final SqlSessionFactory sqlSessionFactory;
static {
//Read mybatis configuration file
InputStream inputStream = ClassLoader.getSystemClassLoader().getResourceAsStream("mybatis-config.xml");
// Create SqlSession factory according to configuration
sqlSessionFactory = new SqlSessionFactoryBuilder()
.build(inputStream);
}
/**
* Start session
* @return
*/
public static SqlSession openSession() {
return sqlSessionFactory.openSession();
}
/**
* Close session
* @param sqlSession
*/
public static void closeSession(SqlSession sqlSession) {
if(Objects.nonNull(sqlSession)) {
sqlSession.close();
}
}
}
2. JDK log system configuration
Create a new logging.properties configuration file in the resources directory
handlers=java.util.logging.ConsoleHandler .level=INFO cn.horse.demo.UserInfoMapper.level=FINER java.util.logging.ConsoleHandler.level=ALL java.util.logging.ConsoleHandler.formatter=java.util.logging.SimpleFormatter java.util.logging.SimpleFormatter.format=%1$tY-%1$tm-%1$td %1$tT.%1$tL %4$s %3$s - %5$s%6$s %n
Create the JdkLogConfig class under cn.horse.demo
JdkLogConfigClass:
package cn.horse.demo;
import java.io.IOException;
import java.io.InputStream;
import java.util.logging.LogManager;
public class JdkLogConfig {
public JdkLogConfig() {
try {
InputStream inputStream = ClassLoader.getSystemClassLoader().getResourceAsStream("logging.properties");
LogManager.getLogManager().readConfiguration(inputStream);
} catch (IOException e) {
throw new RuntimeException(e);
}
}
}
3. Startup program configuration
package cn.horse.demo;
import org.apache.ibatis.session.SqlSession;
import java.util.List;
import java.util.function.Consumer;
public class Main {
public static void main(String[] args) {
//Introduce JDK log configuration
System.setProperty("java.util.logging.config.class", "cn.horse.demo.JdkLogConfig");
}
private static void execute(Consumer<UserInfoMapper> function) {
SqlSession sqlSession = null;
try {
sqlSession = SqlSessionUtils.openSession();
function.accept(sqlSession.getMapper(UserInfoMapper.class));
sqlSession.commit();
} finally {
SqlSessionUtils.closeSession(sqlSession);
}
}
}
The execute method is used to perform operations. In the method, the sqlSession.getMapper method is used to obtain the mapper object, and then the specific execution operation of the mapper object is delegated to the Consumer object.
6. Query data
//Introduce JDK log configuration
System.setProperty("java.util.logging.config.class", "cn.horse.demo.JdkLogConfig");
// Inquire
execute((UserInfoMapper userInfoMapper) -> {
UserInfoQuery query = new UserInfoQuery();
query.setStartAge(20);
List<UserInfo> userInfoList = userInfoMapper.find(query);
for (UserInfo userInfo: userInfoList) {
System.out.println(userInfo);
}
});
The result after execution is as follows:
7. Add data in batches
//Introduce JDK log configuration
System.setProperty("java.util.logging.config.class", "cn.horse.demo.JdkLogConfig");
// insert
execute((UserInfoMapper userInfoMapper) -> {
List<UserInfo> userInfoList = new ArrayList<>();
UserInfo userInfo1 = new UserInfo();
userInfo1.setId(5);
userInfo1.setName("王五1");
userInfo1.setAge(5);
userInfoList.add(userInfo1);
UserInfo userInfo2 = new UserInfo();
userInfo2.setId(6);
userInfo2.setName("王五2");
userInfo2.setAge(6);
userInfoList.add(userInfo2);
Integer total = userInfoMapper.insert(userInfoList);
System.out.println("Number of items inserted: " + total);
});
The result after execution is as follows:
8. Modify data in batches
//Introduce JDK log configuration
System.setProperty("java.util.logging.config.class", "cn.horse.demo.JdkLogConfig");
// renew
execute((UserInfoMapper userInfoMapper) -> {
List<UserInfo> userInfoList = new ArrayList<>();
UserInfo userInfo1 = new UserInfo();
userInfo1.setId(5);
userInfo1.setName("王五11");
userInfoList.add(userInfo1);
UserInfo userInfo2 = new UserInfo();
userInfo2.setId(6);
userInfo2.setAge(26);
userInfoList.add(userInfo2);
Integer total = userInfoMapper.update(userInfoList);
System.out.println("Number of updates: " + total);
});
The result after execution is as follows:
9. Deleting data in batches
//Introduce JDK log configuration
System.setProperty("java.util.logging.config.class", "cn.horse.demo.JdkLogConfig");
// delete
execute((UserInfoMapper userInfoMapper) -> {
Integer total = userInfoMapper.delete(Arrays.asList(5, 6));
System.out.println("Number of items deleted: " + total);
});
The result after execution is as follows:
