mybatis之动态SQL

<?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 }

 

mybatis之动态SQL

标签：oct   over   数组   gpo   otherwise   coding   创建   body   out