1. Environment preparation
1. Prepare the database and insert data
CREATE TABLE `t_emp`
(
emp_id INT AUTO_INCREMENT,
emp_name CHAR(100),
emp_salary DOUBLE(10, 5),
PRIMARY KEY (emp_id)
);
INSERT INTO `t_emp`(emp_name,emp_salary) VALUES("tom",200.33);
INSERT INTO `t_emp`(emp_name,emp_salary) VALUES("jerry",666.66);
INSERT INTO `t_emp`(emp_name,emp_salary) VALUES("andy",777.77);
2. Write entity classes
package com.suchuanlin.pojo;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Employee {
private Integer empId;
private String empName;
private Double empSalary;
}
3. Prepare database connection configuration file
jdbc.url=jdbc:mysql://localhost:3306/mybatis-example?allowMultiQueries=true jdbc.driver=com.mysql.cj.jdbc.Driver jdbc.username=root jdbc.password=1234
4. Prepare the mybatis-config.xml configuration file
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!--Introduce properties file-->
<properties resource="jdbc.properties"/>
<settings>
<!--Enable the log output of mybatis-->
<setting name="logImpl" value="STDOUT_LOGGING"/>
<!--Enable automatic camel case naming-->
<setting name="mapUnderscoreToCamelCase" value="true"/>
<!--Enable deep automatic mapping of resultMap-->
<setting name="autoMappingBehavior" value="FULL"/>
</settings>
<typeAliases>
<!--Give aliases to the classes under the package in batches, and the alias is the first letter of the class in lowercase -->
<package name="com.suchuanlin.pojo"/>
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<package name="com/suchuanlin/mapper"/>
</mappers>
</configuration>
2. Write java code and configuration files
5. Write EmployeeMapper interface
package com.suchuanlin.mapper;
import com.suchuanlin.pojo.Employee;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface EmployeeMapper {
//Query employee information based on employee name and salary
List<Employee> queryEmpList(@Param("name") String name, @Param("salary") Double salary);
//Update employee data based on employee ID (requirement: the name and salary passed in are not null before updating)
int updateEmp(Employee employee);
//Query employee information based on employee name and salary
List<Employee> queryEmpListTrim(@Param("name") String name, @Param("salary") Double salary);
//Update employee data based on employee ID (requirement: the name and salary passed in are not null before updating)
int updateEmpTrim(Employee employee);
/*Query based on two conditions:
1. If the name is not empty, use the name to search;
2. If the name is empty and the salary is not empty, use salary to check
3. All are empty, query all */
List<Employee> queryEmpListByChoose(@Param("name") String name, @Param("salary") Double salary);
//Batch query based on id
List<Employee> queryBatch(@Param("ids") List<Integer> ids);
//Delete in batches based on ID
int deleteBatch(@Param("ids") List<Integer> ids);
//Batch increase
int insertBatch(@Param("employeeList") List<Employee> employeeList);
//Batch Edit
int updateBatch(@Param("employeeList") List<Employee> employeeList);
}
6. Write the EmployeeMapper.xml configuration file
<?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="com.suchuanlin.mapper.EmployeeMapper">
<!--Query employee information based on employee name and salary-->
<select id="queryEmpList" resultType="employee">
select *
from t_emp
<where>
<if test="name != null and name != ''">emp_name = #{name}</if>
<if test="salary != null and salary != ''">and emp_salary = #{salary}</if>
</where>
</select>
<!--Update employee data based on employee ID (requirement: the name and salary passed in are not null before updating)-->
<update id="updateEmp">
update t_emp
<set>
<if test="empName != null and empName != ''">emp_name = #{empName},</if>
<if test="empSalary != null and empSalary != ''">emp_salary = #{empSalary}</if>
</set>
where emp_id = #{empId}
</update>
<!--Query employee information based on employee name and salary-->
<select id="queryEmpListTrim" resultType="employee">
select *
from t_emp
<trim prefix="where" prefixOverrides="and | or">
<if test="name != null and name != ''">emp_name = #{name}</if>
<if test="salary != null and salary != ''">and emp_salary = #{salary}</if>
</trim>
</select>
<!--Update employee data based on employee ID (requirement: the name and salary passed in are not null before updating)-->
<update id="updateEmpTrim">
update t_emp
<trim prefix="set" suffixOverrides=",">
<if test="empName != null and empName != ''">emp_name = #{empName},</if>
<if test="empSalary != null and empSalary != ''">emp_salary = #{empSalary}</if>
</trim>
</update>
<!--Query based on two conditions:
1. If the name is not empty, use the name to search;
2. If the name is empty and the salary is not empty, use salary to check
3. All are empty, query all -->
<select id="queryEmpListByChoose" resultType="employee">
select *
from t_emp
where
<choose>
<when test="name != null">emp_name = #{name}</when>
<when test="salary != null">emp_salary = #{salary}</when>
<otherwise>1 = 1</otherwise>
</choose>
</select>
<!--Batch query based on id-->
<!--
collection="ids" The collection to be traversed
item="id" gets each traversed item
separator="," separator for traversing elements
open="(" traverses the string to be added before
close=")" The string to be added after traversing
-->
<select id="queryBatch" resultType="employee">
select *
from t_emp
where emp_id in
<foreach collection="ids" item="id" separator="," open="(" close=")">
#{id}
</foreach>
</select>
<!--Batch deletion based on ID-->
<delete id="deleteBatch">
delete from t_emp
where emp_id in
<foreach collection="ids" item="id" separator="," open="(" close=")">
#{id}
</foreach>
</delete>
<!--Batch increase-->
<insert id="insertBatch">
insert into t_emp (emp_name, emp_salary)
values
<foreach collection="employeeList" item="employee" separator=",">
(#{employee.empName},#{employee.empSalary})
</foreach>
</insert>
<!--Batch modification-->
<!--
If a label involves multiple statements, you need to set it to allow specifying multiple statements.
jdbc.url=jdbc:mysql://localhost:3306/mybatis-example?allowMultiQueries=true
-->
<update id="updateBatch">
<foreach collection="employeeList" item="employee" separator=";">
update t_emp
<set>
emp_name = #{employee.empName},
emp_salary = #{employee.empSalary}
</set>
where emp_id = #{employee.empId}
</foreach>
</update>
</mapper>
3. Test program
package com.suchuanlin.test;
import com.suchuanlin.mapper.EmployeeMapper;
import com.suchuanlin.pojo.Employee;
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 org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
public class MyBatisDynamicSQL {
private SqlSession sqlSession;
@BeforeEach
public void before() throws IOException {
InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
sqlSession = sqlSessionFactory.openSession(true);
}
@AfterEach
public void clean(){
sqlSession.close();
}
@Test
public void testQueryEmpList(){
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
List<Employee> employeeList = mapper.queryEmpList(null, null);
for (Employee employee : employeeList) {
System.out.println(employee);
}
}
@Test
public void testUpdateEmp(){
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
Employee employee = new Employee();
employee.setEmpId(9);
employee.setEmpName("Tian Qi");
employee.setEmpSalary(3000.00);
int rows = mapper.updateEmp(employee);
System.out.println(employee);
}
@Test
public void testQueryEmpListTrim(){
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
List<Employee> employeeList = mapper.queryEmpListTrim("天七", 3000.0);
for (Employee employee : employeeList) {
System.out.println(employee);
}
}
@Test
public void testUpdateEmpTrim(){
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
Employee employee = new Employee();
employee.setEmpId(9);
employee.setEmpName("Zhao Liu");
employee.setEmpSalary(1888.88);
int rows = mapper.updateEmp(employee);
System.out.println(employee);
}
@Test
public void testQueryEmpListByChoose(){
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
List<Employee> employeeList = mapper.queryEmpListByChoose(null, null);
for (Employee employee : employeeList) {
System.out.println(employee);
}
}
//Test batch query based on id
@Test
public void testQueryBatch(){
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
List<Integer> ids = new ArrayList<>();
Collections.addAll(ids,1,2,3,4,5,6,7,8,9,10);
List<Employee> employeeList = mapper.queryBatch(ids);
for (Employee employee : employeeList) {
System.out.println(employee);
}
}
//Test batch deletion based on ID
@Test
public void testDeleteBatch(){
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
List<Integer> ids = new ArrayList<>();
Collections.addAll(ids,1,2,3);
int rows = mapper.deleteBatch(ids);
}
//Test batch increase
@Test
public void testInsertBatch(){
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
List<Employee> employeeList = new ArrayList<>();
Employee employee1 = new Employee(null, "Chow Yun-fat", 10000.88);
Employee employee2 = new Employee(null, "Andy Lau", 20000.88);
Employee employee3 = new Employee(null, "Dawn", 30000.88);
Employee employee4 = new Employee(null, "Aaron Kwok", 40000.88);
Collections.addAll(employeeList, employee1, employee2, employee3, employee4);
int rows = mapper.insertBatch(employeeList);
System.out.println(rows);
}
//Test batch modification
@Test
public void testUpdateBatch(){
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
List<Employee> employeeList = new ArrayList<>();
Employee employee1 = new Employee(11, "Dilraba", 50000.88);
Employee employee2 = new Employee(12, "Gulinaza", 60000.88);
Employee employee3 = new Employee(13, "Yang Zi", 70000.88);
Employee employee4 = new Employee(14, "白鹿", 80000.88);
Collections.addAll(employeeList, employee1, employee2, employee3, employee4);
int rows = mapper.updateBatch(employeeList);
System.out.println("Update number of rows" + rows);
}
}
4. SQL fragment
7. Extract SQL fragments
<!-- Use sql tags to extract recurring SQL fragments -->
<sql id="mySelectSql">
select emp_id,emp_name,emp_age,emp_salary,emp_gender from t_emp
</sql>
8. Quoting SQL fragments
<!-- Use the include tag to reference the declared SQL fragment --> <include refid="mySelectSql"/>
The knowledge points of the article match the official knowledge files, and you can further learn related knowledge. Java skill treeLoop structure statementfor loop statement 137841 people are learning the system