动态SQL
动态SQLMyBatis最强大的特性之一就是它的动态SQL功能。
如果您有任何JDBC或类似框架的经验,那么您就会理解有条件地将SQL字符串连接在一起是多么痛苦,确保不要忘记空格或在列列表末尾省略al逗号。
动态SQL处理起来可能非常痛苦,而使用动态SOL永远都不会是一件很麻烦的事情,MyBatis使用一种强大的动态SQL语言(可以在任何映射的SQL语句中使用)无疑改善了这种情况。
使用JSTL或任何类似的基于XML的文本处理器的人都应该熟悉动态SOL元素。
在以前的MyBatis版本中,有很多元素需要了解和理解。MyBatis 3在此基础上有了很大的改进,现在只有不到一半的元素。
一起工作。MyBatis使用了强大的基于OGNL的表达式来消除大多数其他元素
--->if
--->choose(when,otherwise)
--->trim(where,set)
--->foreach
此文章及以后不带结果的截图,影响整体文章的布局美感!!!
其他的一些可以简单看一下之前的博文!
首先来看看本次工程的目录吧:
mybatis-config.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" ></properties> <environments default="development"> <environment id="development"> <transactionManager type="JDBC" /> <dataSource type="POOLED"> <property name="driver" value="${jdbc.driver}" /> <property name="url" value="${jdbc.url}" /> <property name="username" value="${jdbc.username}" /> <property name="password" value="${jdbc.password}" /> </dataSource> </environment> </environments> <mappers> <mapper resource="DynamicSQL.xml"/> </mappers> </configuration>
Employee.java(getter&setter&toString)
public class Employee { private int id; private String name; private String gender; private String email; private Department dept; }
现在基本的布局已经完成!!
1)if
A.在DynamicMapper.java接口中
//携带了哪个字段的查询条件就携带这个字段的值 public List<Employee> getEmpByIf(Employee emp);
在Dynamic'SQl.xml文件
<!-- if --> <!-- 查询员工,要求,携带了那个字段查询条件就带上那个字段的字段值 --> <!-- public List<Employee> getEmpByIf(Employee emp); --> <select id="getEmpByIf" resultType="com.MrChengs.bean.Employee"> select * from test where <!-- test:判断表达式(OGNL) --> <!-- OGNL:apache官方文档有明确的解释说明 --> <!-- 从参数中取值进行判断不是数据库中取值 --> <!-- 特殊字符应该写转义字符 --> <if test="id!=null"> id=#{id} </if> <if test="name!=null and name!=''"> and name like #{name} </if> <if test="email!=null and email.trim()!=''"> and email like #{email} </if> </select>
在这个文件的内容简要的进行说明一下:
and name like #{name}
这里的红色的name是我们查询的name值,不是数据库中的name
#{name}是把我们手动输入的红色name传递过去,进行数据库的查询
测试类:
public SqlSessionFactory getSqlSessionFactory() throws IOException{ String resource = "mybatis-config.xml"; InputStream inputStream = Resources.getResourceAsStream(resource); return new SqlSessionFactoryBuilder().build(inputStream); } @Test public void test() throws IOException { SqlSessionFactory sessionFactory = getSqlSessionFactory(); SqlSession session = sessionFactory.openSession(); try{ DynamicSQLMapper mapper = session.getMapper(DynamicSQLMapper.class); //传入的红色name值进行数据库的查询 Employee emp = new Employee(5, "%Mr%", "boy", "%1287%"); List<Employee> emps = mapper.getEmpByIf(emp); System.out.println(emps); }finally{ session.close(); } }
查询之后的显示代码
DEBUG 10-02 12:13:49,806 ==> Preparing: select * from test where id=? and name like ? and email like ? (BaseJdbcLogger.java:159) DEBUG 10-02 12:13:49,843 ==> Parameters: 5(Integer), %Mr%(String), %1287%(String) (BaseJdbcLogger.java:159) DEBUG 10-02 12:13:49,873 <== Total: 1 (BaseJdbcLogger.java:159) [Employee [id=5, name=MrChengs, gender=boy, email=1287xxxxxx@xx.com, dept=null]]
B.在查询的时候,如果某些时候某些条件没带可能导致sql拼装有问题
实例:
<select id="getEmpByIf" resultType="com.MrChengs.bean.Employee"> select * from test where <!-- test:判断表达式(OGNL) --> <!-- OGNL:apache官方文档有明确的解释说明 --> <!-- 从参数中取值进行判断不是数据库中取值 --> <!-- 特殊字符应该写转义字符 --> <!--此时我们假设忘记把id传进来 --> <if test="name!=null and name!=''"> and name like #{name} </if> <if test="email!=null and email.trim()!=''"> and email like #{email} </if> </select>
look:
show message:DEBUG 10-02 12:18:30,831 ==> Preparing: select * from test where and name like ? and email like ?
(BaseJdbcLogger.java:159)
solution ①: where 1=1
<select id="getEmpByIf" resultType="com.MrChengs.bean.Employee"> select * from test <!-- 加入固定的条件,怎么拼装都行 --> where 1=1 <!-- test:判断表达式(OGNL) --> <!-- OGNL:apache官方文档有明确的解释说明 --> <!-- 从参数中取值进行判断不是数据库中取值 --> <!-- 特殊字符应该写转义字符 --> <if test="name!=null and name!=''"> and name like #{name} </if> <if test="email!=null and email.trim()!=''"> and email like #{email} </if> </select>
solution ②:使用<where></where> 只会去掉一个and 或者or
<select id="getEmpByIf" resultType="com.MrChengs.bean.Employee"> select * from test <where> <!-- test:判断表达式(OGNL) --> <!-- OGNL:apache官方文档有明确的解释说明 --> <!-- 从参数中取值进行判断不是数据库中取值 --> <!-- 特殊字符应该写转义字符 --> <if test="name!=null and name!=''"> and name like #{name} </if> <if test="email!=null and email.trim()!=''"> and email like #{email} </if> </where> </select>
注意使用and
2.使用trim标签进行,字符串截取
先看一个案例的错误代码展示:
DynamicSQLMapper.java
//测试Trim public List<Employee> getEmpByIfTrim(Employee emp);
在DynamicSQL.xml
<!-- 测试Trim() --> <!-- public List<Employee> getEmpByIfTrim(Employee emp); --> <select id="getEmpByIfTrim" resultType="com.MrChengs.bean.Employee"> select * from test where <if test="id!=null"> id=#{id} and </if> <if test="name!=null and name!=''"> name like #{name} and </if> <if test="email!=null and email.trim()!=''"> email like #{email} </if> </select>
假设我们此时传参为name属性一个
@Test public void testgetEmpByIfTrim() throws IOException { SqlSessionFactory sessionFactory = getSqlSessionFactory(); SqlSession session = sessionFactory.openSession(); try{ DynamicSQLMapper mapper = session.getMapper(DynamicSQLMapper.class); Employee emp = new Employee("%Mr%", null, null); List<Employee> emps = mapper.getEmpByIfTrim(emp); System.out.println(emps); }finally{ session.close(); } }
拼串结果
DEBUG 10-02 13:31:59,995 ==> Preparing: select * from test where id=? and name like ? and
开始使用trim标签:(一些用法都在注释中,请注意看注释)
<!-- 测试Trim() --> <!-- public List<Employee> getEmpByIfTrim(Employee emp); --> <select id="getEmpByIfTrim" resultType="com.MrChengs.bean.Employee"> select * from test
<!-- prefix:前缀, trim标签体中是整个字符串拼串后的结果 给拼串后的整体字符串加一个前缀--> <!-- prefixOverrides:前缀覆盖, 去点整个前缀前面多余的字符串 --> <!-- suffix:后缀, 给拼串后的整个字符串加一个后缀 --> <!-- suffixOverrides:后缀覆盖,去掉整个字符串后面多余的字符串 -->
<trim prefix="where" suffixOverrides="and"> <if test="name!=null and name!=''"> name like #{name} and </if> <if test="email!=null and email.trim()!=''"> email like #{email} and </if> <if test="gender!=null"> gender=#{gender} </if> </trim> </select>
测试:
public void testgetEmpByIfTrim() throws IOException { SqlSessionFactory sessionFactory = getSqlSessionFactory(); SqlSession session = sessionFactory.openSession(); try{ DynamicSQLMapper mapper = session.getMapper(DynamicSQLMapper.class); Employee emp = new Employee("%Mr%", null, null); List<Employee> emps = mapper.getEmpByIfTrim(emp); System.out.println(emps); }finally{ session.close(); } }
结果拼串:
DEBUG 10-02 13:43:25,216 ==> Preparing: select * from test where name like ? (BaseJdbcLogger.java:159) DEBUG 10-02 13:43:25,266 ==> Parameters: %Mr%(String) (BaseJdbcLogger.java:159)
注意:在测试id的时候,不写则默认为零,博主自己测试的时候遇到的,所以把id的查询条件拿掉了!
3.choose分支选择
如果带了id使用id进行查询,带了name就是用name进行查询
只能使用一个进行查询
接口类的代码:
//测试choose public List<Employee> getEmpBychoose(Employee emp);
DynamicSQL.xml:
<!-- choose --> <!-- 如果带了id使用id进行查询,带了name就是用name进行查询,只能使用一个进行查询 --> <!-- public List<Employee> getEmpBychoose(Employee emp); --> <select id="getEmpBychoose" resultType="com.MrChengs.bean.Employee"> select * from test <where> <choose> <when test="name!=null"> name like #{name} </when> <when test="email!=null"> email = #{email} </when> <when test="id!=null"> id=#{id} </when> <otherwise> d_id=1 </otherwise> </choose> </where> </select>
测试代码:
//测试choose @Test public void testgetEmpBychoose() throws IOException { SqlSessionFactory sessionFactory = getSqlSessionFactory(); SqlSession session = sessionFactory.openSession(); try{ DynamicSQLMapper mapper = session.getMapper(DynamicSQLMapper.class); Employee emp = new Employee("%Mr%", null, null); emp.setId(5); List<Employee> emps = mapper.getEmpBychoose(emp); System.out.println(emps); }finally{ session.close(); } }
结果:
DEBUG 10-02 14:07:35,311 ==> Preparing: select * from test WHERE name like ? (BaseJdbcLogger.java:159)
DEBUG 10-02 14:07:35,363 ==> Parameters: %Mr%(String) (BaseJdbcLogger.java:159)
此时我们不仅传入了name同时还传入了id,但是拼串之后是使用name进行查询的
3.更新
A.<set></set>版本
在接口中:
//更新方法 public void updataEmp(Employee emp);
在DynamicSQl.xml文件:
<!-- update更新 --> <!-- 更新 --> <!-- public void updataEmp(Employee emp); --> <update id="updataEmp"> update test <set> <if test="name!=null">name=#{name},</if> <if test="email!=null"> email=#{email},</if> <if test="gender!=null">gender=#{gender},</if> </set> where id=#{id} </update>
使用<set>标签,可以自动为我们解决存在的 ”,“ 问题
测试:
//更新upddate @Test public void testgetEmpupdate() throws IOException { SqlSessionFactory sessionFactory = getSqlSessionFactory(); SqlSession session = sessionFactory.openSession(); try{ DynamicSQLMapper mapper = session.getMapper(DynamicSQLMapper.class); Employee emp = new Employee("MrChengsR", "gril", null); emp.setId(7); mapper.updataEmp(emp); System.out.println(emp); session.commit(); }finally{ session.close(); } }
此时修改数据成功
B.<trim><trim> version
仅仅是修改xml文件,其余的都不变
<update id="updataEmp"> update test <trim prefix="set" suffixOverrides=","> <if test="name!=null">name=#{name},</if> <if test="email!=null"> email=#{email},</if> <if test="gender!=null">gender=#{gender},</if> </trim> where id=#{id} </update>
4.foreach
A)foreach:
DynamicSQLMapper.java
//foreach public List<Employee> getEmpsByCollection(List<Integer> list);
DynamicSQL.xml
<!-- foreach: --> <!-- public List<Employee> getEmpsByCollection(Employee emp); --> <select id="getEmpsByCollection" resultType="com.MrChengs.bean.Employee" > select * from test where id in( <!-- collection:指定遍历的集合 --> <!-- list类型的参数会做特殊的处理封装在map中,map的key叫list --> <!-- item:将当前遍历出的元素赋值给指定的变量 --> <!-- #{变量名} 就能取出当前遍历的元素 --> <!-- separator:每个元素之间的分隔符 此时是in(a,b,c,d)这里面的 , --> <!-- open:遍历出所有结果拼接一个开始的字符 --> <!-- close:便利的所有结果拼出结尾 --> <!-- index:遍历list是索引,遍历map就是map的key --> <foreach collection="list" item="item_id" separator=","> #{item_id} </foreach> ) </select>
测试类:
@Test public void testgetEmpForeach() throws IOException { SqlSessionFactory sessionFactory = getSqlSessionFactory(); SqlSession session = sessionFactory.openSession(); try{ DynamicSQLMapper mapper = session.getMapper(DynamicSQLMapper.class); List<Employee> emps = mapper.getEmpsByCollection(Arrays.asList(5,7,8)); for(Employee emp : emps){ System.out.println(emp); } }finally{ session.close(); } }
得到结果:
DEBUG 10-02 19:16:01,838 ==> Preparing: select * from test where id in( ? , ? , ? ) (BaseJdbcLogger.java:159) DEBUG 10-02 19:16:01,887 ==> Parameters: 5(Integer), 7(Integer), 8(Integer) (BaseJdbcLogger.java:159) DEBUG 10-02 19:16:01,909 <== Total: 3 (BaseJdbcLogger.java:159) Employee [id=5, name=MrChengs, gender=boy, email=xxxxxxxx@qq.com, dept=null] Employee [id=7, name=MrChengs, gender=gril, email=zhangsan@qq.com, dept=null] Employee [id=8, name=MrChen, gender=gril, email=xxxxxx@xx.xxx, dept=null]
B.批量保存
方法1:
接口类中:
//批量存取 public void addEmps(@Param("emps")List<Employee> employee);
xml文件:
<!-- //批量存取--> <!-- public void addEmps(@Param("emps")Employee employee); --> <insert id="addEmps"> insert into test(name,gender,email,d_id) values <foreach collection="emps" separator="," item="emp"> <!-- 传参数之前是我们new的一个对象,传参数之后是插入数据库的数据 --> (#{emp.name},#{emp.gender},#{emp.email},#{emp.dept.id}) </foreach> </insert>
实现类:
//批量存取 @Test public void testgetEmpaddEmps() throws IOException { SqlSessionFactory sessionFactory = getSqlSessionFactory(); SqlSession session = sessionFactory.openSession(); try{ DynamicSQLMapper mapper = session.getMapper(DynamicSQLMapper.class); List<Employee> employee = new ArrayList<Employee>(); employee.add(new Employee("Ma", "gril", "Ma@Ma", new Department(1))); employee.add(new Employee("Mb", "boy", "Mb@Mb", new Department(2))); mapper.addEmps(employee); session.commit(); }finally{ session.close(); } }
此时是成功插入数据
方法二:
<!-- 方法二 --> <!-- 需要加上 --> <!-- jdbc.url=jdbc:mysql://localhost:3306/mybatis?allowMultiQueries=true --> <insert id="addEmps"> <foreach collection="emps" separator=";" item="emp"> insert into test(name,gender,email,d_id) values (#{emp.name},#{emp.gender},#{emp.email},#{emp.dept.id}) </foreach> </insert>
其余不变可以进行测试
c.两个重要的参数
<!-- 两个重要的参数 -->
<!-- _parameter:代表整个参数,单个参数就是这个参数,多个参数就是封装成的map -->
<!-- _databaseId:配置了databaseIdProvider标签,就是代表当前数据库的别名 -->
_databaseId:
mybatis-config.xml
<databaseIdProvider type="DB_VENDOR"> <property name="MySQL" value="mysql"/> <property name="Oracle" value="oracle"/> </databaseIdProvider>
接口类中
//测试两个属性 public List<Employee> getEmpselect();
DynamicMapper.xml
<!-- 两个重要的参数 --> <!-- _parameter:代表整个参数,单个参数就是这个参数,多个参数就是封装成的map --> <!-- _databaseId:配置了databaseIdProvider标签,就是代表当前数据库的别名 --> <!-- public Employee getEmpselect(int id); --> <!-- 修改if中的test条件即可实现不同数据库之间的查询 --> <select id="getEmpselect" resultType="com.MrChengs.bean.Employee" databaseId="mysql"> <if test="_databaseId=='mysql'"> select * from test </if> <if test="_databaseId=='oracle'"> select * from test </if> </select>
测试类:
//两个重要的参数 @Test public void testgetEmpselect() throws IOException { SqlSessionFactory sessionFactory = getSqlSessionFactory(); SqlSession session = sessionFactory.openSession(); try{ DynamicSQLMapper mapper = session.getMapper(DynamicSQLMapper.class); List<Employee> emps= mapper.getEmpselect(); for(Employee emp : emps){ System.out.println(); } }finally{ session.close(); } }
此时可以成功查询数据!!
_parameter
在接口类中:把刚刚测试代码加上id
//测试两个属性 public List<Employee> getEmpselect(int id);
在xnl文件中:
<!-- public Employee getEmpselect(int id); --> <select id="getEmpselect" resultType="com.MrChengs.bean.Employee" databaseId="mysql"> <if test="_databaseId=='mysql'"> select * from test <if test="_parameter!=null"> where id=#{id} </if> </if> <if test="_databaseId=='oracle'"> select * from test </if> </select>
测试类:
@Test public void testgetEmpselect() throws IOException { SqlSessionFactory sessionFactory = getSqlSessionFactory(); SqlSession session = sessionFactory.openSession(); try{ DynamicSQLMapper mapper = session.getMapper(DynamicSQLMapper.class); List<Employee> emps= mapper.getEmpselect(5); System.out.println(emps); }finally{ session.close(); } }
此时的查询成功!!!
D.bind标签的使用
接口类中:
//测试两个属性 //public List<Employee> getEmpselect(); //public List<Employee> getEmpselect(int id); public List<Employee> getEmpselect(Employee em);
xml文件:
<select id="getEmpselect" resultType="com.MrChengs.bean.Employee" databaseId="mysql">
<!-- bind:可以将OGNL表达式的值绑定到一个变量中,方便引用这个变量的值 -->
<!-- name :是我们指定的绑定参数-->
<!-- value :指定参数的值 -->
<bind name="_name" value="'%'+name+'%'"/>
<if test="_databaseId=='mysql'">
select * from test
<if test="_parameter!=null">
where name like #{_name}
</if>
</if>
<if test="_databaseId=='oracle'">
select * from test
</if>
</select>
测试类:
@Test public void testgetEmpselect() throws IOException { SqlSessionFactory sessionFactory = getSqlSessionFactory(); SqlSession session = sessionFactory.openSession(); try{ DynamicSQLMapper mapper = session.getMapper(DynamicSQLMapper.class); //List<Employee> emps= mapper.getEmpselect(); //List<Employee> emps= mapper.getEmpselect(5); Employee emp = new Employee(); emp.setName("M"); List<Employee> emps= mapper.getEmpselect(emp); System.out.println(emps); }finally{ session.close(); } }
E.SQL标签
<!-- <include refid=""></include> -->
<!-- SQL:抽取可重用的sql字段,方便后面引用 -->
<!-- include:就是引用外部标签 -->
<!--
1.sql抽取:经常要查询的列名,或者插入用的列名抽取出来方便引用
2.include来引用已经抽取的sql
3.include还可以自定义一些property,sql标签内部只能使用自定义的属性
include-property:取值正确方式 ${prop}
#{不可以使用这种方式}
-->
<sql id="">
<!-- 同时这里面还可以使用 if进行判断 -->
<if test=""></if>
</sql>