一、环境准备
1.准备数据库,并插入数据
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.编写实体类
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.准备数据库连接配置文件
jdbc.url=jdbc:mysql://localhost:3306/mybatis-example?allowMultiQueries=true
jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.username=root
jdbc.password=1234
4.准备mybatis-config.xml配置文件
<?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>
<!--引入properties文件-->
<properties resource="jdbc.properties"/>
<settings>
<!--开启mybatis的日志输出-->
<setting name="logImpl" value="STDOUT_LOGGING"/>
<!--开启驼峰命名自动映射-->
<setting name="mapUnderscoreToCamelCase" value="true"/>
<!--开启resultMap深层次自动映射-->
<setting name="autoMappingBehavior" value="FULL"/>
</settings>
<typeAliases>
<!--批量将包下的类起别名,别名为类的首字母小写-->
<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>
二、编写java代码和配置文件
5.编写EmployeeMapper接口
package com.suchuanlin.mapper;
import com.suchuanlin.pojo.Employee;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface EmployeeMapper {
//根据员工姓名和工资查询员工信息
List<Employee> queryEmpList(@Param("name") String name, @Param("salary") Double salary);
//根据员工id,更新员工数据(要求:传入的name和salary不为null的才更新)
int updateEmp(Employee employee);
//根据员工姓名和工资查询员工信息
List<Employee> queryEmpListTrim(@Param("name") String name, @Param("salary") Double salary);
//根据员工id,更新员工数据(要求:传入的name和salary不为null的才更新)
int updateEmpTrim(Employee employee);
/*根据两个条件查询:
1.如果姓名不为空、用姓名查;
2.如果姓名为空,薪水不为空,用薪水查
3.都为空,查询全部*/
List<Employee> queryEmpListByChoose(@Param("name") String name, @Param("salary") Double salary);
//根据id批量查询
List<Employee> queryBatch(@Param("ids") List<Integer> ids);
//根据id批量删除
int deleteBatch(@Param("ids") List<Integer> ids);
//批量增加
int insertBatch(@Param("employeeList") List<Employee> employeeList);
//批量修改
int updateBatch(@Param("employeeList") List<Employee> employeeList);
}
6.编写EmployeeMapper.xml配置文件
<?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">
<!--根据员工姓名和工资查询员工信息-->
<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>
<!--根据员工id,更新员工数据(要求:传入的name和salary不为null的才更新)-->
<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>
<!--根据员工姓名和工资查询员工信息-->
<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>
<!--根据员工id,更新员工数据(要求:传入的name和salary不为null的才更新)-->
<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>
<!--根据两个条件查询:
1.如果姓名不为空、用姓名查;
2.如果姓名为空,薪水不为空,用薪水查
3.都为空,查询全部-->
<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>
<!--根据id批量查询-->
<!--
collection="ids" 要遍历的集合
item="id" 获取每个遍历项
separator="," 遍历元素的分隔符
open="(" 遍历之前要添加的字符串
close=")" 遍历之后要添加的字符串
-->
<select id="queryBatch" resultType="employee">
select *
from t_emp
where emp_id in
<foreach collection="ids" item="id" separator="," open="(" close=")">
#{id}
</foreach>
</select>
<!--根据id批量删除-->
<delete id="deleteBatch">
delete from t_emp
where emp_id in
<foreach collection="ids" item="id" separator="," open="(" close=")">
#{id}
</foreach>
</delete>
<!--批量增加-->
<insert id="insertBatch">
insert into t_emp (emp_name, emp_salary)
values
<foreach collection="employeeList" item="employee" separator=",">
(#{employee.empName},#{employee.empSalary})
</foreach>
</insert>
<!--批量修改-->
<!--
如果一个标签涉及多个语句,需要设置允许指定多语句
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>
三、测试程序
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("田七");
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("赵六");
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);
}
}
//测试根据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);
}
}
//测试根据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
public void testInsertBatch(){
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
List<Employee> employeeList = new ArrayList<>();
Employee employee1 = new Employee(null, "周润发", 10000.88);
Employee employee2 = new Employee(null, "刘德华", 20000.88);
Employee employee3 = new Employee(null, "黎明", 30000.88);
Employee employee4 = new Employee(null, "郭富城", 40000.88);
Collections.addAll(employeeList, employee1, employee2, employee3, employee4);
int rows = mapper.insertBatch(employeeList);
System.out.println(rows);
}
//测试批量修改
@Test
public void testUpdateBatch(){
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
List<Employee> employeeList = new ArrayList<>();
Employee employee1 = new Employee(11, "迪丽热巴", 50000.88);
Employee employee2 = new Employee(12, "古力娜扎", 60000.88);
Employee employee3 = new Employee(13, "杨紫", 70000.88);
Employee employee4 = new Employee(14, "白鹿", 80000.88);
Collections.addAll(employeeList, employee1, employee2, employee3, employee4);
int rows = mapper.updateBatch(employeeList);
System.out.println("更新行数" + rows);
}
}
四、SQL片段
7.抽取SQL片段
<!-- 使用sql标签抽取重复出现的SQL片段 -->
<sql id="mySelectSql">
select emp_id,emp_name,emp_age,emp_salary,emp_gender from t_emp
</sql>
8.引用SQL片段
<!-- 使用include标签引用声明的SQL片段 -->
<include refid="mySelectSql"/>