学习笔记——Mybatis动态SQL
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) " /> </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>
相关文章
- 从本体论开始说起——运营商关系图谱的构建及应用
- 如何成为一名数据科学家?
- 从未见过的堂兄杀了人,你的DNA是关键证据
- 20个安全可靠的免费数据源,各领域数据任你挑
- 20个安全可靠的免费数据源,各领域数据任你挑
- 阿里云李飞飞:All in Cloud时代,云原生数据库优势明显
- 基于Hadoop生态系统的一高性能数据存储格式CarbonData(性能篇)
- 大数据告诉你:10年漫威,到底有多少角色
- TigerGraph:实时图数据库助力金融风控升级
- Splunk利用Splunk Connected Experiences和Splunk Business Flow 扩大数据访问
- 大数据开发常见的9种数据分析手段
- 以免在景区看人,我爬了5W条全国景点门票数据...
- 【实战解析】基于HBase的大数据存储在京东的应用场景
- 数据科学家告诉你哪些计算机科学书籍是你应该看的
- Kafka作为大数据的核心技术,你了解多少?
- Spring Boot 整合 Redis 实现缓存操作
- 大数据学习必须掌握的五大核心技术有哪些?
- 基于Antlr在Apache Flink中实现监控规则DSL化的探索实践
- 甲骨文再次被Gartner评为分析型数据管理解决方案魔力象限领导者
- 爬取吴亦凡微博102118条转发数据,扒一扒流量的真假