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>
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/541826.html
標籤：其他