首页 > 代码库 > MyBatis系列：（5）动态SQL
MyBatis系列：（5）动态SQL
1、动态SQL操作之查询
查询条件不确定，需要根据情况产生SQL语法，这种情况叫动态SQL
<select id="dynamicFind" parameterType="map" resultMap="empMap">
select emp_id,emp_name,emp_sal from emp
<where>
<if test="pid != null">
and emp_id = #{pid}
</if>
<if test="pname != null">
and emp_name like #{pname}
</if>
<if test="psal != null">
and emp_sal < #{psal}
</if>
</where>
</select>
2、动态SQL操作之更新
更新条件不确定，需要根据情况产生SQL语法，这种情况叫动态SQL
<update id="dynamicUpdate" parameterType="map">
update emp
<set>
<if test="pname != null">
emp_name = #{pname},
</if>
<if test="psal != null">
emp_sal = #{psal},
</if>
</set>
where emp_id = #{pid}
</update>
3、动态SQL操作之删除
根据多个id进行删除
<delete id="dynamicDeleteArray">
delete from emp where emp_id in
<!-- foreach用于迭代数组元素
open表示开始符号
close表示结束符合
separator表示元素间的分隔符
item表示迭代的数组，属性值可以任意，但提倡与方法的数组名相同
#{pid}表示数组中的每个元素值
-->
<foreach collection="array" open="(" close=")" separator="," item="pid" >
#{pid}
</foreach>
</delete>
<delete id="dynamicDeleteList">
delete from emp where emp_id in
<foreach collection="list" open="(" close=")" separator="," item="pid">
#{pid}
</foreach>
</delete>
4、动态SQL操作之添加
根据条件，添加数据
<!-- sql片段对应字段名,id属性值任意 -->
<sql id="key">
<!-- 去掉最后一个,号 -->
<trim suffixOverrides=",">
<if test="id != null">
emp_id,
</if>
<if test="name != null">
emp_name,
</if>
<if test="sal != null">
emp_sal,
</if>
</trim>
</sql>
<!-- sql片段对应?,id属性值任意 -->
<sql id="value">
<!-- 去掉最后一个,号 -->
<trim suffixOverrides=",">
<if test="id != null">
#{id},
</if>
<if test="name != null">
#{name},
</if>
<if test="sal != null">
#{sal},
</if>
</trim>
</sql>
<!-- <include refid="key"/>和<include refid="value"/>表示引用上面定义的sql片段 -->
<insert id="dynamicInsert" parameterType="emp">
insert into emp(<include refid="key"></include>) values(<include refid="value"></include>)
</insert>
5、参考代码
准备SQL
CREATE TABLE Emp( emp_id INT(5) PRIMARY KEY, emp_name VARCHAR(20), emp_sal DOUBLE(8,2) );
Emp.java
package com.rk.entity;
public class Emp {
private Integer id;
private String name;
private Double sal;
public Emp(){}
public Emp(Integer id, String name, Double sal) {
this.id = id;
this.name = name;
this.sal = sal;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Double getSal() {
return sal;
}
public void setSal(Double sal) {
this.sal = sal;
}
}EmpMapper.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.rk.entity.Emp">
<!-- 当实体属性与表字段名不相同的时候，必须书写以下代码
当实体属性与表字段名相同的时候，以下代码可选 （可写可不写）
-->
<resultMap type="emp" id="empMap">
<id property="id" column="emp_id"/>
<result property="name" column="emp_name"/>
<result property="sal" column="emp_sal"/>
</resultMap>
<!-- 1、动态SQL之SELECT -->
<select id="dynamicFind" parameterType="map" resultMap="empMap">
select emp_id,emp_name,emp_sal from emp
<where>
<if test="pid != null">
and emp_id = #{pid}
</if>
<if test="pname != null">
and emp_name like #{pname}
</if>
<if test="psal != null">
and emp_sal < #{psal}
</if>
</where>
</select>
<!-- 2、动态SQL之UPDATE -->
<!-- set标签自动判断哪个是最后一个字段，会自动去掉最后一个,号 -->
<update id="dynamicUpdate" parameterType="map">
update emp
<set>
<if test="pname != null">
emp_name = #{pname},
</if>
<if test="psal != null">
emp_sal = #{psal},
</if>
</set>
where emp_id = #{pid}
</update>
<!-- 3、动态SQL之DELETE -->
<delete id="dynamicDeleteArray">
delete from emp where emp_id in
<!-- foreach用于迭代数组元素
open表示开始符号
close表示结束符合
separator表示元素间的分隔符
item表示迭代的数组，属性值可以任意，但提倡与方法的数组名相同
#{pid}表示数组中的每个元素值
-->
<foreach collection="array" open="(" close=")" separator="," item="pid" >
#{pid}
</foreach>
</delete>
<delete id="dynamicDeleteList">
delete from emp where emp_id in
<foreach collection="list" open="(" close=")" separator="," item="pid">
#{pid}
</foreach>
</delete>
<!-- 4、动态SQL之INSERT -->
<!-- sql片段对应字段名,id属性值任意 -->
<sql id="key">
<!-- 去掉最后一个,号 -->
<trim suffixOverrides=",">
<if test="id != null">
emp_id,
</if>
<if test="name != null">
emp_name,
</if>
<if test="sal != null">
emp_sal,
</if>
</trim>
</sql>
<!-- sql片段对应?,id属性值任意 -->
<sql id="value">
<!-- 去掉最后一个,号 -->
<trim suffixOverrides=",">
<if test="id != null">
#{id},
</if>
<if test="name != null">
#{name},
</if>
<if test="sal != null">
#{sal},
</if>
</trim>
</sql>
<!-- <include refid="key"/>和<include refid="value"/>表示引用上面定义的sql片段 -->
<insert id="dynamicInsert" parameterType="emp">
insert into emp(<include refid="key"></include>) values(<include refid="value"></include>)
</insert>
</mapper>mybatis.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!-- 加载类路径下的属性文件 -->
<properties resource="db.properties"/>
<!-- 设置类型别名 -->
<typeAliases>
<typeAlias type="com.rk.entity.Emp" alias="emp"/>
</typeAliases>
<!-- 设置一个默认的连接环境信息 -->
<environments default="mysql_development">
<environment id="mysql_development">
<!-- mybatis使用jdbc事务管理方式 -->
<transactionManager type="JDBC"/>
<!-- mybatis使用连接池方式来获取连接 -->
<dataSource type="POOLED">
<!-- 配置与数据库交互的4个必要属性 -->
<property name="driver" value="http://www.mamicode.com/${mysql.driver}"/>
<property name="url" value="http://www.mamicode.com/${mysql.url}"/>
<property name="username" value="http://www.mamicode.com/${mysql.username}"/>
<property name="password" value="http://www.mamicode.com/${mysql.password}"/>
</dataSource>
</environment>
<!-- 连接环境信息，取一个任意唯一的名字 -->
<environment id="oracle_development">
<!-- mybatis使用jdbc事务管理方式 -->
<transactionManager type="jdbc"/>
<!-- mybatis使用连接池方式来获取连接 -->
<dataSource type="pooled">
<!-- 配置与数据库交互的4个必要属性 -->
<property name="driver" value="http://www.mamicode.com/${oracle.driver}"/>
<property name="url" value="http://www.mamicode.com/${oracle.url}"/>
<property name="username" value="http://www.mamicode.com/${oracle.username}"/>
<property name="password" value="http://www.mamicode.com/${oracle.password}"/>
</dataSource>
</environment>
</environments>
<!-- 加载映射文件-->
<mappers>
<mapper resource="com/rk/entity/EmpMapper.xml"/>
</mappers>
</configuration>db.properties
mysql.driver=com.mysql.jdbc.Driver mysql.url=jdbc:mysql://127.0.0.1:3306/testdb mysql.username=root mysql.password=root oracle.driver=oracle.jdbc.driver.OracleDriver oracle.url=jdbc:oracle:thin:@127.0.0.1:1521:orcl oracle.username=scott oracle.password=tiger
MyBatisUtils.java
package com.rk.utils;
import java.io.IOException;
import java.io.Reader;
import java.sql.Connection;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
public class MyBatisUtils {
private static ThreadLocal<SqlSession> threadLocal = new ThreadLocal<SqlSession>();
private static SqlSessionFactory sqlSessionFactory;
static{
try {
Reader reader = Resources.getResourceAsReader("mybatis.xml");
sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
} catch (IOException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
private MyBatisUtils() {}
public static SqlSession getSqlSession(){
SqlSession sqlSession = threadLocal.get();
if(sqlSession == null){
sqlSession = sqlSessionFactory.openSession();
threadLocal.set(sqlSession);
}
return sqlSession;
}
public static void closeSqlSession(){
SqlSession sqlSession = threadLocal.get();
if(sqlSession != null){
sqlSession.close();
threadLocal.remove();
}
}
public static void main(String[] args) {
Connection conn = MyBatisUtils.getSqlSession().getConnection();
System.out.println(conn!=null ? "连接成功" : "连接失败");
}
}EmpDao.java
package com.rk.dao;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import com.rk.entity.Emp;
import com.rk.utils.MyBatisUtils;
public class EmpDao {
/**
* 动态SQL之SELECT
*/
public List<Emp> dynamicFind(Integer id,String name,Double sal){
SqlSession sqlSession = null;
try {
Map<String, Object> map = new HashMap<String, Object>();
map.put("pid", id);
map.put("pname", name != null ? "%"+name+"%" : null);
map.put("psal", sal);
sqlSession = MyBatisUtils.getSqlSession();
return sqlSession.selectList(Emp.class.getName() + ".dynamicFind", map);
} catch (Exception e) {
e.printStackTrace();
sqlSession.rollback();
}
finally{
MyBatisUtils.closeSqlSession();
}
return null;
}
@Test
public void testDynamicFind(){
EmpDao dao = new EmpDao();
//List<Emp> list = dao.dynamicFind(1,null, null);
//List<Emp> list = dao.dynamicFind(null,"tom", null);
//List<Emp> list = dao.dynamicFind(null,null, 350D);
List<Emp> list = dao.dynamicFind(null,"tom", 350D);
// List<Emp> list = dao.dynamicFind(0,"tom", 25D);
if(list != null && list.size()>0){
for(Emp emp : list){
System.out.println( emp.getName() + ":"+emp.getSal());
}
}
}
/**
* 动态SQL之UPDATE
*/
public int dynamicUpdate(Integer id,String name,Double sal){
SqlSession sqlSession = null;
try {
Map<String, Object> map = new HashMap<String, Object>();
map.put("pid", id);
map.put("pname", name);
map.put("psal", sal);
sqlSession = MyBatisUtils.getSqlSession();
int i=sqlSession.update(Emp.class.getName() + ".dynamicUpdate", map);
sqlSession.commit();
return i;
} catch (Exception e) {
e.printStackTrace();
sqlSession.rollback();
}
finally{
MyBatisUtils.closeSqlSession();
}
return 0;
}
@Test
public void testDynamicUpdate(){
int i = dynamicUpdate(3,"Lucy",null);
System.out.println("本次操作影响"+i+"行数据");
}
/**
* 动态SQL之DELETE（数组版本）
*/
public int dynamicDeleteArray(int... ids){
SqlSession sqlSession = null;
try {
sqlSession = MyBatisUtils.getSqlSession();
int i=sqlSession.update(Emp.class.getName() + ".dynamicDeleteArray", ids);
sqlSession.commit();
return i;
} catch (Exception e) {
e.printStackTrace();
sqlSession.rollback();
}
finally{
MyBatisUtils.closeSqlSession();
}
return 0;
}
@Test
public void testDynamicDeleteArray(){
int i = dynamicDeleteArray(3,2);
System.out.println("本次操作影响"+i+"行数据");
}
/**
* 动态SQL之DELETE（集合版本）
*/
public int dynamicDeleteList(List<Integer> ids){
SqlSession sqlSession = null;
try {
sqlSession = MyBatisUtils.getSqlSession();
int i=sqlSession.update(Emp.class.getName() + ".dynamicDeleteList", ids);
sqlSession.commit();
return i;
} catch (Exception e) {
e.printStackTrace();
sqlSession.rollback();
}
finally{
MyBatisUtils.closeSqlSession();
}
return 0;
}
@Test
public void testDynamicDeleteList(){
List<Integer> ids = new ArrayList<Integer>();
ids.add(1);
ids.add(4);
int i = dynamicDeleteList(ids);
System.out.println("本次操作影响"+i+"行数据");
}
/**
* 动态SQL之INSERT
*/
public int dynamicInsert(Emp emp){
SqlSession sqlSession = null;
try {
sqlSession = MyBatisUtils.getSqlSession();
int i=sqlSession.insert(Emp.class.getName() + ".dynamicInsert", emp);
sqlSession.commit();
return i;
} catch (Exception e) {
e.printStackTrace();
sqlSession.rollback();
}
finally{
MyBatisUtils.closeSqlSession();
}
return 0;
}
@Test
public void testDynamicInsert(){
Emp emp = new Emp(1, null, 30D);
int i = dynamicInsert(emp);
System.out.println("本次操作影响"+i+"行数据");
}
}MyBatis系列：（5）动态SQL
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。