本博客源码下载:戳我一下
Mybatis学习笔记汇总:戳我一下
一、什么是动态sql
mybatis
核心对sql
语句进行灵活操作,通过表达式进行判断,对sql
进行灵活拼接、组装。
二、if
语句
1、配置UserMapper.xml
<!-- 用户信息的综合查询总数 -->
<select id="findUserCount" parameterType="UserQueryVo" resultType="int">
select count(*) from user
<!--
where可以自动的去掉条件中的第一个and
-->
<where>
<if test="userCustom!=null">
<if
test="userCustom.sex!=null and userCustom.sex!=''">
and user.sex=#{userCustom.sex}
</if>
<if test="userCustom.username!=null and userCustom.username!=''">
and user.username like '%${userCustom.username}%'
</if>
</if>
</where>
</select>
2、测试:
void testFindUserCount() throws Exception {
SqlSession sqlSession = sessionFactory.openSession();
//得到UserMapper对象,Mybatis自动生成mapper代理对象
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
//调用userMapper方法
//创建包装对象,设置查询条件
UserQueryVo userQueryVo = new UserQueryVo();
UserCustom userCustom = new UserCustom();
//userCustom.setSex("1");
userCustom.setUsername("小明");
userQueryVo.setUserCustom(userCustom);
int count = userMapper.findUserCount(userQueryVo);
System.out.println(count);
sqlSession.close();
}
3、运行结果
DEBUG [main] - Created connection 2072313080.
DEBUG [main] - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@7b84fcf8]
DEBUG [main] - ==> Preparing: select count(*) from user WHERE user.username like '%小明%'
DEBUG [main] - ==> Parameters:
DEBUG [main] - <== Total: 1
3
DEBUG [main] - Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@7b84fcf8]
DEBUG [main] - Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@7b84fcf8]
DEBUG [main] - Returned connection 2072313080 to pool.
三、sql片段
1、定义sql片段
<!-- 定义一个sql片段
id:sql片段唯一标识
经验:定义sql片段是基于单表来定义,这样sql片段的可重用性高
在sql片段中不要包括where标签
-->
<sql id="query_user_where">
<if test="userCustom!=null">
<if
test="userCustom.sex!=null and userCustom.sex!=''">
and user.sex=#{userCustom.sex}
</if>
<if test="userCustom.username!=null and userCustom.username!=''">
and user.username='%${userCustom.username}$'
</if>
</if>
</sql>
2、使用sql片段
<!-- 引用sql片段,如果该片段不再该文件中,要加namespace -->
<where>
<include refid="query_user_where"></include>
</where>
四、foreach
语句
1、在输入参数类型中添加List<Integer> ids
传入多个id
package com.jiayifan.po;
import java.util.List;
/**
* 包装类型
* @author 贾一帆
*
*/
public class UserQueryVo {
//传入多个id
private List<Integer> ids;
//在这里包装所需要的查询条件
//用户查询条件
private UserCustom userCustom;
//可以包装其他查询条件,订单,商品
public UserCustom getUserCustom() {
return userCustom;
}
public void setUserCustom(UserCustom userCustom) {
this.userCustom = userCustom;
}
public List<Integer> getIds() {
return ids;
}
public void setIds(List<Integer> ids) {
this.ids = ids;
}
}
2、修改配置
<!-- 定义一个sql片段
id:sql片段唯一标识
经验:定义sql片段是基于单表来定义,这样sql片段的可重用性高
在sql片段中不要包括where标签
-->
<sql id="query_user_where">
<if test="userCustom!=null">
<if
test="userCustom.sex!=null and userCustom.sex!=''">
and user.sex=#{userCustom.sex}
</if>
<if test="userCustom.username!=null and userCustom.username!=''">
and user.username='%${userCustom.username}$'
</if>
<if test="ids!=null">
<!-- 使用foreach遍历传入的ids
collection:指定输入对象中集合属性
item:每次遍历时生成的对象名
open:开始遍历时拼接的sql
close:结束时遍历时拼接的sql
separator:遍历的两个对象的中间需要拼接的sql
-->
<!--
实现 and (id=1 or id=10 or id=16) 的拼接
-->
<foreach collection="ids" item="user_id" open="and (" close=")" separator="or">
<!-- 每次遍历需要拼接的串 -->
id=#{user_id}
</foreach>
</if>
</if>
</sql>
3、测试
void testFindUserList() throws Exception {
SqlSession sqlSession = sessionFactory.openSession();
//得到UserMapper对象,Mybatis自动生成mapper代理对象
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
//调用userMapper方法
//创建包装对象,设置查询条件
UserQueryVo userQueryVo = new UserQueryVo();
UserCustom userCustom = new UserCustom();
//userCustom.setSex("1");
//userCustom.setUsername("小明");
//传入多个id
List<Integer> ids = new ArrayList<Integer>();
ids.add(1);
ids.add(10);
ids.add(26);
userQueryVo.setIds(ids);
userQueryVo.setUserCustom(userCustom);
List<UserCustom> list = userMapper.findUserList(userQueryVo);
System.out.println(list);
sqlSession.close();
}
4、运行结果
DEBUG [main] - Created connection 2072313080.
DEBUG [main] - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@7b84fcf8]
DEBUG [main] - ==> Preparing: select * from user WHERE ( id=? or id=? or id=? )
DEBUG [main] - ==> Parameters: 1(Integer), 10(Integer), 26(Integer)
DEBUG [main] - <== Total: 3
[User [id=1, username=hahaha, sex=null, birthday=Sun Mar 18 00:00:00 CST 2018, address=null], User [id=10, username=张三, sex=1, birthday=Thu Jul 10 00:00:00 CST 2014, address=北京市], User [id=26, username=王五, sex=null, birthday=null, address=null]]
DEBUG [main] - Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@7b84fcf8]
DEBUG [main] - Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@7b84fcf8]
DEBUG [main] - Returned connection 2072313080 to pool.