Mybatis20_mybatis映射文件深入(动态SQL)6
一、动态sql语句
1、动态sql语句概述
Mybatis的映射文件中,前面我们的SQL都是比较简单的,有些时候业务逻辑复杂时,我们的 SQL是动态变化的,此时在前面的学习中我们的 SQL 就不能满足要求了。
2、环境搭建
UserMapper.java
package com.itheima.mapper; import com.itheima.domain.User; import java.util.List; public interface UserMapper { public List<User> findByCondition(User user); }
UserMapper.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.itheima.mapper.UserMapper"> <!--查询操作--> <select id="findByCondition" parameterType="user" resultType="user"> select * from user where id=#{id} and username=#{username} and password=#{password} </select> </mapper>
log4j.properties
### direct log messages to stdout ### log4j.appender.stdout=org.apache.log4j.ConsoleAppender log4j.appender.stdout.Target=System.out log4j.appender.stdout.layout=org.apache.log4j.PatternLayout log4j.appender.stdout.layout.ConversionPattern=%d{ABSOLUTE} %5p %c{1}:%L - %m%n ### direct messages to file mylog.log ### log4j.appender.file=org.apache.log4j.FileAppender log4j.appender.file.File=c:/mylog.log log4j.appender.file.layout=org.apache.log4j.PatternLayout log4j.appender.file.layout.ConversionPattern=%d{ABSOLUTE} %5p %c{1}:%L - %m%n ### set log levels - for more verbose logging change 'info' to 'debug' ### log4j.rootLogger=debug, stdout
MapperTest.java
package com.itheima.test; import com.itheima.domain.User; import com.itheima.mapper.UserMapper; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import org.junit.Test; import java.io.IOException; import java.io.InputStream; import java.util.List; public class MapperTest { @Test public void test1() throws IOException { InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream); SqlSession sqlSession = sqlSessionFactory.openSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); //模拟条件user User condition = new User(); condition.setId(1); condition.setUsername("zhangsan"); condition.setPassword("123"); List<User> userList = mapper.findByCondition(condition); System.out.println(userList); } }
Run test1方法,检查结果:[User{id=1, username='zhangsan', password='123'}]
当查询条件id、username和password都存在时控制台打印的sql语句如下:
当模拟条件中没有password时,MapperTest.java:
package com.itheima.test; import com.itheima.domain.User; import com.itheima.mapper.UserMapper; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import org.junit.Test; import java.io.IOException; import java.io.InputStream; import java.util.List; public class MapperTest { @Test public void test1() throws IOException { InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream); SqlSession sqlSession = sqlSessionFactory.openSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); //模拟条件user User condition = new User(); condition.setId(1); condition.setUsername("zhangsan"); // condition.setPassword("123"); List<User> userList = mapper.findByCondition(condition); System.out.println(userList); } }
Run test1方法,检查结果:[]
当查询条件id、username存在,password不存在时控制台打印的sql语句如下:
3、动态SQL之<if>
我们根据实体类的不同取值,使用不同的SQL语句来进行查询。比如在id如果不为空时可以根据id查询,如果username不为空时还要加入用户名作为条件。这种情况在我们的多条件组合查询中经常会碰到。
UserMapper.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.itheima.mapper.UserMapper"> <!--查询操作--> <select id="findByCondition" parameterType="user" resultType="user"> select * from user where 1=1 <if test="id!=0"> and id=#{id} </if> <if test="username!=null"> and username=#{username} </if> <if test="password!=null"> and password=#{password} </if> </select> </mapper>
当查询条件id、username存在,password不存在时控制台输出结果:[User{id=1, username='zhangsan', password='123'}]
控制台打印的sql语句如下:
优化where条件,UserMapper.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.itheima.mapper.UserMapper"> <!--查询操作--> <select id="findByCondition" parameterType="user" resultType="user"> select * from user <where> <if test="id!=0"> and id=#{id} </if> <if test="username!=null"> and username=#{username} </if> <if test="password!=null"> and password=#{password} </if> </where> </select> </mapper>
MapperTest中只有id存在时执行查看控制台sql语句:
只有username存在时执行查看控制台sql语句:
id、username、password都不存在时执行查看控制台sql语句:
4、动态SQL之<foreach>
循环执行sql的拼接操作,例如:SELECT * FROM USER WHERE id IN (1,3,4)
foreach标签的属性含义如下:
<foreach>标签用于遍历集合,它的属性:
-
- collection:代表要遍历的集合元素,注意编写时不要写#{}
- open:代表语句的开始部分
- close:代表结束部分
- item:代表遍历集合的每个元素,生成的变量名
- sperator:代表分隔符
UserMapper.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.itheima.mapper.UserMapper"> <select id="findByIds" parameterType="list" resultType="user"> select * from user <where> <foreach collection="list" open="id in(" close=")" item="id" separator=","> #{id} </foreach> </where> </select> </mapper>
collecton="array"表示id传过来的是数组:int[] ids = new int[]{1,3}; mapper.findByIds(ids);
MapperTest.java:
package com.itheima.test; import com.itheima.domain.User; import com.itheima.mapper.UserMapper; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import org.junit.Test; import java.io.IOException; import java.io.InputStream; import java.util.ArrayList; import java.util.List; public class MapperTest { @Test public void test1() throws IOException { InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream); SqlSession sqlSession = sqlSessionFactory.openSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); List<Integer> ids = new ArrayList<>(); ids.add(1); List<User> userList = mapper.findByIds(ids); System.out.println(userList); } }
执行test1,查看控制台sql语句和输出结果:
ids值中再增加一个值:ids.add(3);
执行test1,查看控制台sql语句和输出结果:
二、SQL片段抽取
Sql中可将重复的sql提取出来,使用时用include引用即可,最终达到sql重用的目的
UserMapper.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.itheima.mapper.UserMapper"> <!--查询操作--> <select id="findByCondition" parameterType="user" resultType="user"> select * from user <where> <if test="id!=0"> and id=#{id} </if> <if test="username!=null"> and username=#{username} </if> <if test="password!=null"> and password=#{password} </if> </where> </select> <select id="findByIds" parameterType="list" resultType="user"> select * from user <where> <foreach collection="list" open="id in(" close=")" item="id" separator=","> #{id} </foreach> </where> </select> </mapper>
select * from user这条sql语句在多个地方用到,可以抽取出来
<?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.itheima.mapper.UserMapper"> <!--sql语句抽取--> <sql id="selectUser">select * from user</sql> <!--查询操作--> <select id="findByCondition" parameterType="user" resultType="user"> <include refid="selectUser"></include> <where> <if test="id!=0"> and id=#{id} </if> <if test="username!=null"> and username=#{username} </if> <if test="password!=null"> and password=#{password} </if> </where> </select> <select id="findByIds" parameterType="list" resultType="user"> <include refid="selectUser"></include> <where> <foreach collection="list" open="id in(" close=")" item="id" separator=","> #{id} </foreach> </where> </select> </mapper>
执行MapperTest类中的test1方法,检查正常输出:
三、知识小结
MyBatis映射文件配置:
<select>:查询
<insert>:插入
<update>:修改
<delete>:删除(请求的参数parameterType是个实体,则sql语句中的占位符#{}内部写的字符串就是实体的属性名,这个字符串写什么都行,但是为了方便程序的可读性一般会写一个有意义的字符串)
<where>:where条件(自动判断where后面是否有条件,有条件就加where,无条件就不加where)
<if>:if判断(如果满足某一个条件则下面的sql就拼接上,如果不满足条件则下面的sql不拼接)
<foreach>:循环(常用在in中,in中的值需要循环拼接,业务层传过来一个集合或数组,通过foreach拿到集合或数组内部的数据,然后进行拼接)
<sql>:sql片段抽取(重复的sql进行抽取并进行引用)