目录
mybatis中的动态SQL语句
mybatis的映射文件中,有的时候SQL是动态变化的,简单的SQL语句就不满足要求了。
企业的查询语句中,查询条件,有的时候可能有,有的时候可能没有,使用动态SQL语句,可以根据不同的情况拼接查询条件。
SqlMapConfig.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">
<!--mybatis的主配置文件-->
<configuration>
<typeAliases>
<!--用于指定要配置别名的包,当指定之后,该包下的实体类都会注册别名,并且类名就是别名,不再区分大小写-->
<package name="com.itheima.domain"/>
</typeAliases>
<!--配置环境-->
<environments default="mysql">
<!-- 配置mysql的环境-->
<environment id="mysql">
<!-- 配置事务的类型 -->
<transactionManager type="JDBC"></transactionManager>
<!--配置数据源(连接池)-->
<dataSource type="UNPOOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://192.168.171.131:3306/mybatis?characterEncoding=UTF-8"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
<mappers>
<package name="com.itheima.dao"/>
</mappers>
</configuration>
1、<if> 标签
第1个case:有1个<if>条件
com/itheima/dao/IUserDao.xml
注意:
- sql语句相关的内容是不关心大小写的,如and username中的username;
- 实体类属性是要注意大小写的,如#{username}中的 username;test="username != null"中的 username;
<!--根据传入参数 条件查询用户-->
<select id="findUserByCondition" parameterType="user" resultType="user">
select * from user where 1=1
<if test="username != null">
and username = #{username}
</if>
</select>
com.itheima.test.MybatisTest
@Test
// 测试根据传入参数条件查询用户
public void testFindByCondition(){
User user = new User();
user.setUsername("小王");
// 5、执行根据传入参数条件查询用户
List<User> users = userDao.findUserByCondition(user);
for(User u:users ){
System.out.println(u);
}
}
运行结果:
2020-04-03 09:53:32,491 567 [ main] DEBUG ansaction.jdbc.JdbcTransaction - Opening JDBC Connection
2020-04-03 09:53:32,797 873 [ main] DEBUG ansaction.jdbc.JdbcTransaction - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@52f759d7]
2020-04-03 09:53:32,809 885 [ main] DEBUG o.IUserDao.findUserByCondition - ==> Preparing: select * from user where 1=1 and username = ?
2020-04-03 09:53:32,887 963 [ main] DEBUG o.IUserDao.findUserByCondition - ==> Parameters: 小王(String)
2020-04-03 09:53:32,924 1000 [ main] DEBUG o.IUserDao.findUserByCondition - <== Total: 2
User{id=43, username='小王', birthday='Mon Mar 02 19:58:51 CST 2020', sex='男', address='我家在上海市03区'}
User{id=65, username='小王', birthday='Mon Mar 23 20:15:45 CST 2020', sex='女', address='上海市静安区5'}
2020-04-03 09:53:32,925 1001 [ main] DEBUG ansaction.jdbc.JdbcTransaction - Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@52f759d7]
2020-04-03 09:53:32,925 1001 [ main] DEBUG ansaction.jdbc.JdbcTransaction - Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@52f759d7]
第2个case:有多个<if>条件
com/itheima/dao/IUserDao.xml
有2个查询条件<if>标签生效,所以只能查询出来一条记录了;
<!--根据传入参数 条件查询用户-->
<select id="findUserByCondition" parameterType="user" resultType="user">
select * from user where 1=1
<if test="username != null">
and username = #{username}
</if>
<if test="sex != null">
and sex = #{sex}
</if>
</select>
com.itheima.test.MybatisTest
@Test
// 测试根据传入参数条件查询用户
public void testFindByCondition(){
User user = new User();
user.setUsername("小王");
user.setSex("女");
// 5、执行根据传入参数条件查询用户
List<User> users = userDao.findUserByCondition(user);
for(User u:users ){
System.out.println(u);
}
}
运行结果:
2020-04-03 10:08:14,436 898 [ main] DEBUG ansaction.jdbc.JdbcTransaction - Opening JDBC Connection
2020-04-03 10:08:14,880 1342 [ main] DEBUG ansaction.jdbc.JdbcTransaction - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@52f759d7]
2020-04-03 10:08:14,890 1352 [ main] DEBUG o.IUserDao.findUserByCondition - ==> Preparing: select * from user where 1=1 and username = ? and sex = ?
2020-04-03 10:08:14,948 1410 [ main] DEBUG o.IUserDao.findUserByCondition - ==> Parameters: 小王(String), 女(String)
2020-04-03 10:08:14,978 1440 [ main] DEBUG o.IUserDao.findUserByCondition - <== Total: 1
User{id=65, username='小王', birthday='Mon Mar 23 20:15:45 CST 2020', sex='女', address='上海市静安区5'}
2020-04-03 10:08:14,981 1443 [ main] DEBUG ansaction.jdbc.JdbcTransaction - Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@52f759d7]
2020-04-03 10:08:14,984 1446 [ main] DEBUG ansaction.jdbc.JdbcTransaction - Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@52f759d7]
2、<where> 标签
添加<where>标签后,就不用再写where 1=1
com/itheima/dao/IUserDao.xml
<!--根据传入参数 条件查询用户-->
<select id="findUserByCondition" parameterType="user" resultType="user">
select * from user
<where>
<if test="username != null">
and username = #{username}
</if>
<if test="sex != null">
and sex = #{sex}
</if>
</where>
</select>
com.itheima.test.MybatisTest
@Test
// 测试根据传入参数条件查询用户
public void testFindByCondition(){
User user = new User();
user.setUsername("小王");
user.setSex("女");
// 5、执行根据传入参数条件查询用户
List<User> users = userDao.findUserByCondition(user);
for(User u:users ){
System.out.println(u);
}
}
运行结果:
2020-04-03 10:52:11,796 1022 [ main] DEBUG ansaction.jdbc.JdbcTransaction - Opening JDBC Connection
2020-04-03 10:52:12,308 1534 [ main] DEBUG ansaction.jdbc.JdbcTransaction - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@33723e30]
2020-04-03 10:52:12,316 1542 [ main] DEBUG o.IUserDao.findUserByCondition - ==> Preparing: select * from user WHERE username = ? and sex = ?
2020-04-03 10:52:12,370 1596 [ main] DEBUG o.IUserDao.findUserByCondition - ==> Parameters: 小王(String), 女(String)
2020-04-03 10:52:12,400 1626 [ main] DEBUG o.IUserDao.findUserByCondition - <== Total: 1
User{id=65, username='小王', birthday='Mon Mar 23 20:15:45 CST 2020', sex='女', address='上海市静安区5'}
2020-04-03 10:52:12,402 1628 [ main] DEBUG ansaction.jdbc.JdbcTransaction - Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@33723e30]
2020-04-03 10:52:12,403 1629 [ main] DEBUG ansaction.jdbc.JdbcTransaction - Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@33723e30]
3、<foreach> 标签
查询时,有子查询,如:select * from user where id in (41,42,43)
<foreach>标签,用于遍历集合,它的属性:
- collection:代表要遍历的集合元素,注意编写时不要写#{}
- open:代表语句的开始部分
- close:代表结束部分
- item:代表遍历集合的每个元素,生成的变量名
- sperator:代表分隔符
com.itheima.domain.QueryVo
增加集合属性 private List<Integer> ids;
package com.itheima.domain;
import java.util.List;
/**
* 把实体类再包装一层,由多个对象组成一个查询条件,实现数据的查询
*/
public class QueryVo {
private User user;
private List<Integer> ids;
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
public List<Integer> getIds() {
return ids;
}
public void setIds(List<Integer> ids) {
this.ids = ids;
}
}
com.itheima.dao.IUserDao
package com.itheima.dao;
import com.itheima.domain.QueryVo;
import com.itheima.domain.User;
import java.util.List;
/**
* 用户的持久层接口
*/
public interface IUserDao {
// 根据queryVo中提供的id集合,查询用户信息
List<User> findUserInIds(QueryVo queryVo);
}
com/itheima/dao/IUserDao.xml
<!--根据queryVo中提供的id集合,查询用户信息-->
<select id="findUserInIds" parameterType="queryvo" resultType="user">
select * from user
<where>
<if test="ids != null and ids.size()>0">
<foreach collection="ids" open="and id in (" close=")" item="uid" separator=",">
#{uid}
</foreach>
</if>
</where>
</select>
com.itheima.test.MybatisTest
@Test
// 根据queryVo中提供的id集合,查询用户信息
public void testFindInIds(){
QueryVo vo = new QueryVo();
List<Integer> list = new ArrayList<Integer>();
list.add(41);
list.add(42);
list.add(43);
vo.setIds(list);
// 5、执行根据queryVo中提供的id集合,查询用户信息
List<User> users = userDao.findUserInIds(vo);
for(User u:users ){
System.out.println(u);
}
}
运行结果:
2020-04-03 11:13:53,738 551 [ main] DEBUG ansaction.jdbc.JdbcTransaction - Opening JDBC Connection
2020-04-03 11:13:54,129 942 [ main] DEBUG ansaction.jdbc.JdbcTransaction - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@396e2f39]
2020-04-03 11:13:54,139 952 [ main] DEBUG ima.dao.IUserDao.findUserInIds - ==> Preparing: select * from user WHERE id in ( ? , ? , ? )
2020-04-03 11:13:54,193 1006 [ main] DEBUG ima.dao.IUserDao.findUserInIds - ==> Parameters: 41(Integer), 42(Integer), 43(Integer)
2020-04-03 11:13:54,231 1044 [ main] DEBUG ima.dao.IUserDao.findUserInIds - <== Total: 3
User{id=41, username='大王', birthday='Sun Mar 01 19:58:51 CST 2020', sex='女', address='我家在上海市01区'}
User{id=42, username='中王', birthday='Mon Mar 02 19:58:51 CST 2020', sex='男', address='我家在上海市02区'}
User{id=43, username='小王', birthday='Mon Mar 02 19:58:51 CST 2020', sex='男', address='我家在上海市03区'}
2020-04-03 11:13:54,233 1046 [ main] DEBUG ansaction.jdbc.JdbcTransaction - Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@396e2f39]
2020-04-03 11:13:54,241 1054 [ main] DEBUG ansaction.jdbc.JdbcTransaction - Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@396e2f39]
4、<sql> 标签
<sql> 标签,抽取重复的sql语句,如 select * from user,注意后面要进行sql拼接,结尾不要加;号
<sql id="defaultUser">
select * from user
</sql>
引用的写法:
<include refid="defaultUser"/>
com/itheima/dao/IUserDao.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">
<!-- 命名空间,xml文件和dao接口对接起来 -->
<!--dao的全限定类名-->
<mapper namespace="com.itheima.dao.IUserDao">
<sql id="defaultUser">
select * from user
</sql>
<!--查询所有用户操作-->
<select id="findAll" resultType="user">
<include refid="defaultUser"/>
</select>
<!--依据用户id查询用户信息-->
<select id="findById" parameterType="integer" resultType="user" >
<include refid="defaultUser"/> where id = #{id}
</select>
<!--根据queryVo中提供的id集合,查询用户信息-->
<select id="findUserInIds" parameterType="queryvo" resultType="user">
<include refid="defaultUser"/>
<where>
<if test="ids != null and ids.size()>0">
<foreach collection="ids" open="and id in (" close=")" item="uid" separator=",">
#{uid}
</foreach>
</if>
</where>
</select>
</mapper>
源码:day03_eesy_02dynamicSQL