mybatis之动态SQL
DongTaiMapper.xml
1 <?xml version="1.0" encoding="UTF-8" ?> 2 <!DOCTYPE mapper 3 PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 4 "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> 5 6 <!--动态sql举例--> 7 <mapper namespace="DongTai.UserDaoDT"> 8 9 <!-- if标签:只要test中的表达式为true，就会执行if标签中的条件。 --> 10 <!-- where标签：自动判断是否需要加上"where"关键字--> 11 <select id="selectByIf" parameterType="bean.User" resultMap="userList"> 12 select * from user 13 <where> 14 <if test="name != null"> 15 name LIKE '${name}%' 16 </if> 17 </where> 18 </select> 19 20 <!--choose标签:多个选项中选择一个。--> 21 <select id="selectByChoose" parameterType="bean.User" resultMap="userList"> 22 select * from user 23 <where> 24 <choose> 25 <when test="name != null"> 26 name LIKE '${name}%' 27 </when> 28 <when test="age != null"> 29 age LIKE '${age}%' 30 </when> 31 <otherwise> 32 1 = 1 33 </otherwise> 34 </choose> 35 </where> 36 </select> 37 38 <!--trim标签：替换关键字--> 39 <select id="selectByTrim" parameterType="bean.User" resultMap="userList"> 40 select * from user 41 <trim prefix="WHERE" prefixOverrides="AND | OR"> 42 <if test="name != null"> 43 AND name LIKE '${name}%' 44 </if> 45 <if test="age != null"> 46 AND age LIKE '${age}%' 47 </if> 48 </trim> 49 </select> 50 51 <!-- where标签：自动判断是否需要加上"where"关键字和去掉"and"关键字--> 52 <!-- select * from user name LIKE '${name}%' AND age LIKE '${age}%'--> 53 <select id="selectByWhere" parameterType="bean.User" resultMap="userList"> 54 select * from user 55 <where> 56 <if test="name != null"> 57 name LIKE '${name}%' 58 </if> 59 <if test="age != null"> 60 AND age LIKE '${age}%' 61 </if> 62 </where> 63 </select> 64 65 <!--foreach标签：处理传参为List或数组的情况--> 66 <!--SELECT * FROM user WHERE id IN (?,?,?...)--> 67 <select id="selectByList" resultMap="userList"> 68 SELECT * FROM user WHERE id IN 69 <foreach collection="list" open="(" separator="," close=")" item="item_id"> 70 #{item_id} 71 </foreach> 72 </select> 73 74 <!--传参为数组--> 75 <select id="selectByArray" resultMap="userList"> 76 SELECT * FROM user WHERE id IN 77 <foreach collection="array" open="(" separator="," close=")" item="item_id"> 78 #{item_id} 79 </foreach> 80 </select> 81 82 83 <!--批量返回数据类型定义--> 84 <resultMap type="bean.User" id="userList"> 85 <!--property:对象属性; column:表字段名--> 86 <result property="id" column="id"/> 87 <result property="name" column="name"/> 88 <result property="age" column="age"/> 89 </resultMap> 90 91 92 93 </mapper>
TestDongTai.java
1 package DongTai; 2 3 import bean.User; 4 import org.apache.ibatis.io.Resources; 5 import org.apache.ibatis.session.SqlSession; 6 import org.apache.ibatis.session.SqlSessionFactory; 7 import org.apache.ibatis.session.SqlSessionFactoryBuilder; 8 import org.junit.*; 9 10 import java.io.IOException; 11 import java.io.Reader; 12 import java.util.ArrayList; 13 import java.util.List; 14 15 /* 16 * 使用mybatis举例，使用动态SQL举例 17 * */ 18 public class TestDongTai { 19 String resource = "mybatis-config-dongtai.xml"; 20 SqlSessionFactory sqlSessionFactory = null; 21 SqlSession session = null; 22 23 // if标签:只要test中的表达式为true，就会执行if标签中的条件 24 @Test 25 public void testSelectByIf() { 26 // 接口自动实例化 27 UserDaoDT userDao = session.getMapper(UserDaoDT.class); 28 User user = new User(); 29 user.setName("Tom"); 30 user.setAge("25"); 31 System.out.println("查询对象：" + user); 32 // 执行sql 33 List listUser = userDao.selectByIf(user); 34 System.out.println(listUser); 35 } 36 37 // choose标签:多个选项中选择一个 38 @Test 39 public void testSelectByChoose() { 40 // 接口自动实例化 41 UserDaoDT userDao = session.getMapper(UserDaoDT.class); 42 User user = new User(); 43 // user.setName("Tom"); 44 user.setAge("2"); 45 System.out.println("查询对象：" + user); 46 // 执行sql 47 List listUser = userDao.selectByChoose(user); 48 System.out.println(listUser); 49 } 50 51 // trim标签：替换关键字 52 @Test 53 public void testsSelectByTrim() { 54 // 接口自动实例化 55 UserDaoDT userDao = session.getMapper(UserDaoDT.class); 56 User user = new User(); 57 // user.setName("Tom"); 58 user.setAge("2"); 59 System.out.println("查询对象：" + user); 60 // 执行sql 61 List listUser = userDao.selectByTrim(user); 62 System.out.println(listUser); 63 } 64 65 // where标签：自动判断是否需要加上"where"关键字和去掉"and"关键字 66 @Test 67 public void testSelectByWhere() { 68 // 接口自动实例化 69 UserDaoDT userDao = session.getMapper(UserDaoDT.class); 70 User user = new User(); 71 user.setName("Tom"); 72 user.setAge("25"); 73 System.out.println("查询对象：" + user); 74 // 执行sql 75 List listUser = userDao.selectByWhere(user); 76 System.out.println(listUser); 77 } 78 79 // foreach标签：处理传参为List的情况 80 @Test 81 public void testSelectByList() { 82 // 接口自动实例化 83 UserDaoDT userDao = session.getMapper(UserDaoDT.class); 84 List idList = new ArrayList(); 85 idList.add(1); 86 idList.add(2); 87 idList.add(3); 88 System.out.println("查询List：" + idList); 89 // 执行sql 90 List listUser = userDao.selectByList(idList); 91 System.out.println(listUser); 92 } 93 94 // foreach标签：处理传参为数组的情况 95 @Test 96 public void testSelectByArray() { 97 // 接口自动实例化 98 UserDaoDT userDao = session.getMapper(UserDaoDT.class); 99 Integer[] ids = {1, 2, 3}; 100 System.out.println("查询数组：" + ids); 101 // 执行sql 102 List listUser = userDao.selectByArray(ids); 103 System.out.println(listUser); 104 } 105 106 107 @Before 108 public void before() { 109 // System.out.println("Before"); 110 try { 111 Reader read = Resources.getResourceAsReader(resource); 112 // 创建工厂 113 sqlSessionFactory = new SqlSessionFactoryBuilder().build(read); 114 // 创建session对象 115 session = sqlSessionFactory.openSession(); 116 } catch (IOException e) { 117 e.printStackTrace(); 118 } 119 } 120 121 @After 122 public void close() { 123 session.close(); 124 // System.out.println("After"); 125 } 126 127 }
UserDaoDT.java
1 package DongTai; 2 3 import bean.User; 4 5 import java.util.List; 6 7 8 /* 9 * Mybatis使用动态SQL举例 10 * */ 11 public interface UserDaoDT { 12 13 public List<User> selectByIf(User user); 14 15 public List<User> selectByChoose(User user); 16 17 public List<User> selectByWhere(User user); 18 19 public List<User> selectByTrim(User user); 20 21 public List<User> selectByList(List list); 22 23 public List<User> selectByArray(Integer[] ids); 24 25 }
