2023-01-12
一、Mybatis动态SQL
即将SQL动态化
同时Mybatis的动态SQL支持OFNL表达式，OGNL（Object Graph Navigation Language）对象图导航语言。
1、先搭建环境
（1）创建一个“maven”模块，命名为“day04_mybatis”
（2）在“day04_mybatis”中的“pom.xml”中的<project>标签内部添加依赖，即添加jar包
<dependencies>
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.26</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.6</version>
</dependency>
<!-- 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>
</dependencies>
（3）在"day04_mybatis.src.main.resources"中添加"db.properties（连接数据库，里面的形式是：key=value）,需要设置4个值（driver、url、username、password）"和“log4j.xml”
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">
<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">
<appender name="STDOUT" class="org.apache.log4j.ConsoleAppender">
<param name="Encoding" value="UTF-8" />
<layout class="org.apache.log4j.PatternLayout">
<param name="ConversionPattern" value="%5p %d{MM-dd HH:mm:ss,SSS} %m (%F:%L) \n" />
</layout>
</appender>
<logger name="java.sql">
<level value="debug"/>
</logger>
<logger name="org.apache.ibatis">
<level value="info"/>
</logger>
<root>
<level value="debug" />
<appender-ref ref="STDOUT" />
</root>
</log4j:configuration>
（4）在“day04_mybatis.src.resources”中添加“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 resource="db.properties"></properties>
<settings>
<setting name="mapUnderscoreToCamelCase" value="true"/>
<!-- 延迟加载-->
<setting name="lazyLoadingEnabled" value="true"/>
<!-- 延迟加载的属性-->
<setting name="aggressiveLazyLoading" value="false"/>
</settings>
<typeAliases>
<package name="com.hh.mybatis.pojo"/>
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${db.driver}"/>
<property name="url" value="${db.url}"/>
<property name="username" value="${db.username}"/>
<property name="password" value="${db.password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="mapper/EmployeeMapper.xml"></mapper>
</mappers>
</configuration>
（5）在“day04_mybatis.src.main.java”中创建“com.hh.mybatis.pojo”和“com.hh.mybatis.mapper”。之后在“day04_mybatis.src.main.resources”中创建“mapper”文件夹，在“mapper”文件夹下创建“EmployeeMapper.xml”
（6）在“pojo”文件夹下创建“Dept”、“Employee”。在“mapper”文件夹下创建“EmployeeMapper”接口并添加代码
public interface EmployeeMapper {
/**
* 按条件查询员工信息（条件不确定）
* @return
*/
public List<Employee> selectEmpByOpr(Employee employee);
}
（7）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.hh.mybatis.mapper.EmployeeMapper" >
<!-- 按条件查询员工信息（条件不确定）-->
<select id="selectEmpByOpr" resultType="employee">
select
id,
last_name,
email,
salary,
dept_id,
from
tbl_employee
</select>
</mapper>
（8）在“day04_mybatis.src.test.java"中创建测试类“TestDynamicSql”
public class TestDynamicSql {
@Test
public void testDynamicSql() throws Exception{
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
EmployeeMapper employeeMapper = sqlSession.getMapper(EmployeeMapper.class);
Employee employee = new Employee();
List<Employee> employees = employeeMapper.selectEmpByOpr(employee);
for (Employee employee1 : employees) {
System.out.println("employee1 = " + employee1);
}
}
}
（9）注意：
如果出现“java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax； check the manual that corres”这种问题报错时，可以将sql语句先在“SQLyog”中先写一遍，运行，如果运行无误，之后将代码再放入“映射文件”中，即可
2、进行动态SQL设置
（1）注意：在动态SQL中“test”中放置的是“属性”
（2）动态SQL<if>标签
用于完成简单的判断
<select id="selectEmpByOpr" resultType="employee">
SELECT
id,
last_name,
email,
salary
FROM
tbl_employee
WHERE
<if test="id != null">
id = #{id}
</if>
<if test="lastName != null">
and last_name = #{lastName}
</if>
<if test="email != null">
and email = #{email}
</if>
<if test="salary != null">
and salary = #{salary}
</if>
</select>
测试类
public class TestDynamicSql {
@Test
public void testDynamicSql() throws Exception{
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
EmployeeMapper employeeMapper = sqlSession.getMapper(EmployeeMapper.class);
Employee employee = new Employee();
//动态标签
employee.setId(1);
// employee.setSalary(50.0);
List<Employee> employees = employeeMapper.selectEmpByOpr(employee);
for (Employee employee1 : employees) {
System.out.println("employee1 = " + employee1);
}
}
}
注意：动态参数中<if>，无参数时报错，没有第一个参数也报错。
(3)动态SQL：<where>标签
where用于解决SQL语句中where关键字以及条件前面的and或者or的问题
<select id="selectEmpByOpr" resultType="employee">
SELECT
id,
last_name,
email,
salary
FROM
tbl_employee
<where>
<if test="id != null">
and id = #{id}
</if>
<if test="lastName != null">
and last_name = #{lastName}
</if>
<if test="email != null">
and email = #{email}
</if>
<if test="salary != null">
and salary = #{salary}
</if>
</where>
</select>
（3）动态SQL：<trim>
可以在条件判断完的SQL语句前后添加或者去掉指定的字符
标签中的属性：
①prefix:添加前缀
②prefixOverrides:去掉前缀
③suffix:添加后缀
④suffixOverrides:去掉后缀
例如：
<select id="selectEmpByOprTrim" resultType="employee">
SELECT
id,
last_name,
email,
salary
FROM
tbl_employee
<trim prefix="where" suffixOverrides="and">
<if test="id != null">
id = #{id} and
</if>
<if test="lastName != null">
last_name = #{lastName} and
</if>
<if test="email != null">
email = #{email} and
</if>
<if test="salary != null">
salary = #{salary}
</if>
</trim>
</select>
（4）动态SQL：<set>
用于解决修改操作中SQL语句中可能多出逗号的问题
在“EmployeeMapper”接口中添加方法
/**
* 按条件修改员工信息（条件不确定）
* @param employee
*/
public void updateEmpByOpr(Employee employee);
映射文件
<update id="updateEmpByOpr">
update
tbl_employee
<set>
<if test="lastName != null">
last_name = #{lastName},
</if>
<if test="email != null">
email = #{email},
</if>
<if test="salary != null">
salary = #{salary}
</if>
</set>
where
id = #{id}
</update>
（5）动态SQL标签：<choose>
类似于java中if-else（switch-case）结构
<update id="updateEmpByOneOpr">
SELECT
id,
last_name,
email,
salary
FROM
tbl_employee
<where>
<choose>
<when test="id != null">
id = #{id}
</when>
<when test="email != null">
email = #{email}
</when>
<when test="salary != null">
salary = #{salary}
</when>
<otherwise>
1=1
</otherwise>
</choose>
</where>
</update>
（6）动态SQL标签：<foreach>
类似于java中的for循环
collection:要迭代的集合
item:当前从集合中迭代出的元素
separator:元素与元素之间的分隔符
①“EmployeeMapper”接口中的函数
/**
* 通过多个id获取员工信息
* @param ids
* @return
*/
public List<Employee> selectEmpByIds(@Param("ids") List<Integer> ids);
②映射文件
<select id="selectEmpByIds" resultType="employee">
SELECT
id,
last_name,
email,
salary
FROM
tbl_employee
<where>
id in
(
<foreach collection="ids" item="id" separator=",">
#{id}
</foreach>
)
</where>
</select>
③测试主要代码
List<Integer> ids = new ArrayList<>();
ids.add(1);
ids.add(3);
ids.add(4);
List<Employee> employees = employeeMapper.selectEmpByIds(ids);
for (Employee employee1 : employees) {
System.out.println("employee1 = " + employee1);
}
（7）sql标签
提取可重用SQL片段
<sql id="select_employee">
SELECT
id,
last_name,
email,
salary
FROM
tbl_employee
</sql>
<sql id="emp_col">
id,
last_name,
email,
salary
</sql>
<!-- 按条件查询员工信息（条件不确定）-->
<select id="selectEmpByOpr" resultType="employee">
select
<include refid="emp_col"></include>
from
tbl_employee
<where>
<if test="id != null">
and id = #{id}
</if>
<if test="lastName != null">
and last_name = #{lastName}
</if>
<if test="email != null">
and email = #{email}
</if>
<if test="salary != null">
and salary = #{salary}
</if>
</where>
</select>
标签：salary,mybatis,笔记,id,employee,SQL,Mybatis,email From： https://www.cnblogs.com/isDaHua/p/17045760.html