MyBatis系列：（5）动态SQL

1、动态SQL操作之查询

查询条件不确定，需要根据情况产生SQL语法，这种情况叫动态SQL

            select emp_id,emp_name,emp_sal from emp                                    and emp_id = #{pid}                                        and emp_name like #{pname}                                        and emp_sal < #{psal}                        

2、动态SQL操作之更新

更新条件不确定，需要根据情况产生SQL语法，这种情况叫动态SQL

            update emp                                    emp_name = #{pname},                                        emp_sal = #{psal},                            where emp_id = #{pid}    

3、动态SQL操作之删除

根据多个id进行删除

            delete from emp where emp_id in                                    #{pid}                            delete from emp where emp_id in                    #{pid}            

4、动态SQL操作之添加

根据条件，添加数据

                                                    emp_id,                                        emp_name,                                        emp_sal,                                                                                #{id},                                        #{name},                                        #{sal},                                        insert into emp() values()    

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

                                                    select emp_id,emp_name,emp_sal from emp                                    and emp_id = #{pid}                                        and emp_name like #{pname}                                        and emp_sal < #{psal}                                                update emp                                    emp_name = #{pname},                                        emp_sal = #{psal},                            where emp_id = #{pid}                        delete from emp where emp_id in                                    #{pid}                            delete from emp where emp_id in                    #{pid}                                                                        emp_id,                                        emp_name,                                        emp_sal,                                                                                #{id},                                        #{name},                                        #{sal},                                        insert into emp() values()    

mybatis.xml

db.properties

mysql.driver=com.mysql.jdbc.Drivermysql.url=jdbc:mysql://127.0.0.1:3306/testdbmysql.username=rootmysql.password=rootoracle.driver=oracle.jdbc.driver.OracleDriveroracle.url=jdbc:oracle:thin:@127.0.0.1:1521:orcloracle.username=scottoracle.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 threadLocal = new ThreadLocal();    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 dynamicFind(Integer id,String name,Double sal){        SqlSession sqlSession = null;        try {            Map map = new HashMap();            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 list = dao.dynamicFind(1,null, null);        //List list = dao.dynamicFind(null,"tom", null);        //List list = dao.dynamicFind(null,null, 350D);        List list = dao.dynamicFind(null,"tom", 350D);//        List 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 map = new HashMap();            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 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 ids = new ArrayList();        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+"行数据");    }}