Mybatis动态SQL配置

 1 package sun.dao;
 2 
 3 import sun.domain.QueryObj;
 4 import sun.domain.User;
 5 
 6 import java.util.List;
 7 
 8 public interface UserDao {
 9   
10     /**
11      * 根据已有条件进行查询(if where)
12      */
13     List<User> findUserByCondition(User user);
14 
15     /**
16      * 根据集合中的id进行查询(if where foreach)
17      */
18     List<User> findUserByList(QueryObj qobj);
19 }

 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 <mapper namespace="sun.dao.UserDao">
 7     <resultMap id="userMap" type="sun.domain.User">
 8         <!--主键字段对应-->
 9         <id property="user_id" column="id"></id>
10         <!--非主键关系对应-->
11         <result property="user_name" column="username"></result>
12         <result property="user_birthday" column="birthday"></result>
13         <result property="user_address" column="address"></result>
14         <result property="user_sex" column="sex"></result>
15     </resultMap>
16 
17     <!--公共sql语句抽取-->
18     <sql id="defaultSelect">
19         select * from user
20     </sql>
21 
22     <!--根据条件进行查询-->
23     <select id="findUserByCondition" resultMap="userMap" parameterType="user">
24         <include refid="defaultSelect"></include>
25         <where>
26             <if test="user_name!=null">
27                 and username=#{user_name}
28             </if>
29             <if test="user_sex!=null">
30                 and sex=#{user_sex}
31             </if>
32         </where>
33     </select>
34     <!--根据id列表进行查询-->
35     <select id="findUserByList" parameterType="queryobj" resultMap="userMap">
36         <include refid="defaultSelect"></include>
37         <where>
38             <if test="ids!=null and ids.size()>0">
39                 <foreach collection="ids" open="id in (" close=")" item="id" separator=",">
40                     #{id}
41                 </foreach>
42             </if>
43         </where>
44     </select>
45 </mapper>

 1 package sun.test;
 2 
 3 
 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.After;
 9 import org.junit.Before;
10 import org.junit.Test;
11 import sun.dao.UserDao;
12 import sun.domain.QueryObj;
13 import sun.domain.User;
14 
15 import java.io.IOException;
16 import java.io.InputStream;
17 import java.util.ArrayList;
18 import java.util.Date;
19 import java.util.List;
20 
21 public class MybatisTest {
22 
23     private InputStream in;
24     private SqlSession sqlSession;
25     private UserDao userDao;
26 
27     @Before
28     public void init() throws IOException {
29         // 读取配置文件
30         in = Resources.getResourceAsStream("SqlMapConfig.xml");
31         // 创建SqlSessionFactory
32         SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
33         SqlSessionFactory factory = builder.build(in);
34         // 使用工厂生产sqlsession对象
35         sqlSession = factory.openSession();
36         // 使用sqlsession创建UserDao接口代理对象
37         userDao = sqlSession.getMapper(UserDao.class);
38     }
39 
40     @After
41     public void destory() throws IOException {
42         sqlSession.commit();
43         sqlSession.close();
44         in.close();
45     }
46 
47 
48     @Test
49     public void conditionTest(){
50         User user = new User();
51         user.setUser_name("kelvin");
52         user.setUser_sex("女");
53         List<User> users = userDao.findUserByCondition(user);
54         for (User user1 : users) {
55             System.out.println(user1);
56         }
57     }
58 
59     @Test
60     public void ids(){
61         QueryObj queryObj = new QueryObj();
62         ArrayList<Integer> ids = new ArrayList<Integer>();
63         ids.add(51);
64         ids.add(52);
65         ids.add(53);
66         queryObj.setIds(ids);
67         List<User> users = userDao.findUserByList(queryObj);
68         for (User user : users) {
69             System.out.println(user);
70         }
71 
72     }
73 }