一、输入映射和输出映射
mapper.xml映射文件中定义了操作数据库的sql,每条sql就是一个statement,映射文件是MyBatis的核心。
1、parameterType(输入类型)
- 简单类型(常用于SQL查询的基本类型字段)
- pojo对象(常用于insert、update操作)
- vo对象(ViewObject - 表现层对象,pojo的属性一般为基本类型,vo的属性可以是引用类型)
第一步:定义mapper映射文件(定义了statement id、SQL语句、parameterType、resultType)
<!--当查询条件中有多个固定数量的查询条件, 可以通过传入一个vo来进行操作,sql的传参来自vo属性的属性(user.username、user.sex)--> <select id="findUserByVo" parameterType="cn.it.pojo.QueryVo" resultType="cn.it.pojo.User"> SELECT * FROM user WHERE username LIKE '%${user.username}%' AND sex =#{user.sex} </select>
第二步:定义mapper接口
List<User> findUserByVo(QueryVo queryVo);
第三步:定义ViewObject(vo封装了User对象,也可以根据需要封装其他内容,用以保存其他查询选项)
package cn.it.pojo; import java.util.List; /** * Created by Eric on 3/12/17. */ 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; } }
第四步:测试,
@Test public void findUserByQueryVo() throws Exception { SqlSession openSession = sqlSessionFactory.openSession(); UserMapper mapper = openSession.getMapper(UserMapper.class); User user = new User(); user.setUsername("王"); user.setSex("2"); QueryVo queryVo = new QueryVo(); queryVo.setUser(user); List<User> userList = mapper.findUserByVo(queryVo); for (User userInList : userList) { System.out.println(userInList); } }
2、resultType(输出类型)
- 输出简单类型(仅在SQL语句返回结果是一行一列是,输出类型才会是基本类型)
- 输出pojo对象(selectOne)
- 输出pojo对象列表(selectList)
第一步:定义mapper映射文件
<!-- 仅在SQL语句返回一行一列的情况下, 才会使用基本类型作为resultType --> <select id="findUserCount" resultType="int"> SELECT count(*) FROM user </select>
第二步:定义mapper接口
int findUserCount();
第三步:测试
@Test public void findUserCount() throws Exception { SqlSession openSession = sqlSessionFactory.openSession(); UserMapper mapper = openSession.getMapper(UserMapper.class); int count = mapper.findUserCount(); System.out.println("查询结果集的行数为: " + count); }
二、动态SQL拼接
1、if、where、sql、foreach标签的使用
第一步:定义mapper映射文件
<!-- 动态SQL举例: SELECT * FROM user WHERE 1=1 AND username LIKE '%${username}% AND sex=#{sex} --> <select id="findUserByUsernameAndSex" resultType="user" parameterType="user"> SELECT * FROM user <include refid="user_where"/> </select> <sql id="user_where"> <!--where标签可以让你省去书写难看的 1=1 ; 并自动帮你把第一个条件sql的AND关键字去掉--> <where> <if test="username!=null and username!=''">username LIKE '%${username}%'</if> <if test="sex!=null and sex!=''">AND sex=#{sex}</if> </where> </sql> <!--SQL语句: SELECT * FROM user WHERE id IN (1,2,3,4),foreach标签前的AND可以写,如果是第一个where条件也可以不写 --> <select id="findUserByIds" parameterType="queryVo" resultType="user"> SELECT * FROM user <where> <if test="ids.isEmpty() != true"> AND <foreach collection="ids" item="element" open="id IN (" close=")" separator=",">#{element}</foreach> </if> </where> </select>
第二步:定义mapper接口
List<User> findUserByUsernameAndSex(User user);
List<User> findUserByIds(QueryVo queryVo);
第三步:定义输入类型和输出类型(User、QueryVo)
第四步:测试
@Test public void findUserByUsernameAndSex() throws Exception { SqlSession openSession = sqlSessionFactory.openSession(); UserMapper mapper = openSession.getMapper(UserMapper.class); User user1 = new User(); User user2 = new User(); User user3 = new User(); User user4 = new User(); user1.setUsername("王"); user2.setSex("2"); user3.setUsername("王"); user3.setSex("2"); System.out.println("查询'username=王': " + mapper.findUserByUsernameAndSex(user1)); System.out.println("查询'sex=2': " + mapper.findUserByUsernameAndSex(user2)); System.out.println("查询 '王 + 性别' 组合: " + mapper.findUserByUsernameAndSex(user3)); System.out.println("查询'null': " + mapper.findUserByUsernameAndSex(user4)); } @Test public void findUserByIdSet() throws Exception { SqlSession openSession = sqlSessionFactory.openSession(); UserMapper mapper = openSession.getMapper(UserMapper.class); List<Integer> list = new ArrayList<Integer>(); list.add(1); list.add(16); list.add(30); QueryVo queryVo = new QueryVo(); queryVo.setIds(list); List<User> userList = mapper.findUserByIds(queryVo); for (User user : userList) { System.out.println(user); } }
三、关联查询
- orders表和user表的关联查询
- 遍历用户表,每个用户有往往有一个或多个订单,称为“一对多查询”;
- 遍历订单表,每个订单必定关联一个用户,称为“一对一查询”;
- resultType的改变
- user表的pojo对象为User.java;orders表的pojo对象为Orders.java。它们只能作为单表查询的parameterType和resultType
- 联表查询时,字段数增加,需要构建一个新的pojo来作为输入输出类型
-
-
- 自动映射:
- A、B联表查询,extend A表的pojo,在子类中扩展(B表字段对应的)属性,两表的同名字段问题可以通过SQL alias解决
- 优点:只要保证resultType的pojo属性名(property)和查询结果的字段名(column)一致,MyBatis可以自动把输出结果注入到resultType对象中
- 缺点:多表查询,No;一对多查询,No
- 手动映射:
- A、B联表查询,将其中一个pojo对象作为属性添加到另一个pojo中
- 优点:多表查询,OK;一对多查询,OK
- 缺点:配置繁琐
- 自动映射:
-
- 一对一查询
- 自动映射(可行)
- 手动映射(可行)
- 一对多查询
- 自动映射(不可行)
- 手动映射(可行)
1、一对一查询(手动映射)
第一步:定义mapper映射文件
<!-- 一对一: 手动映射 (列出每一个订单, 每个订单必然有一个下单用户) --> <select id="findOrdersAndUser" resultMap="ResultMap1"> SELECT a.*, b.id uid, username, birthday, sex, address FROM orders a, user b WHERE a.user_id = b.id </select> <resultMap id="ResultMap1" type="cn.manual.pojo.Orders"> <id column="id" property="id"/> <result column="user_id" property="userId"/> <result column="number" property="number"/> <result column="createtime" property="createtime"/> <result column="note" property="note"/> <association property="user" javaType="cn.manual.pojo.User"> <id column="uid" property="id"/> <result column="username" property="username"/> <result column="birthday" property="birthday"/> <result column="sex" property="sex"/> <result column="address" property="address"/> </association> </resultMap>
第二步:定义mapper接口
List<Orders> findOrdersAndUser();
第三步:定义pojo
第四步:测试
@Test public void findOrdersAndUserManual() throws Exception { SqlSession openSession = sqlSessionFactory.openSession(); UserMapper mapper = openSession.getMapper(Mapper.class); List<Orders> ordersList = mapper.findOrdersAndUser(); for (Orders orders : ordersList) { System.out.println(orders + " " + orders.getUser()); } }
2、一对多查询(手动映射)
第一步:定义mapper映射文件
<!--一多关系: 手动映射 (列出每个用户, 每个用户可能有一笔或者多笔订单) --> <select id="findUserAndOrders" resultMap="ResultMap2"> SELECT b.id uid, username, birthday, sex, address, a.* FROM orders a, user b WHERE a.user_id=b.id </select> <resultMap id="ResultMap2" type="cn.manual.pojo.User"> <id column="uid" property="id"/> <result column="username" property="username"/> <result column="birthday" property="birthday"/> <result column="sex" property="sex"/> <result column="address" property="address"/> <collection property="ordersList" ofType="cn.manual.pojo.Orders"> <id column="id" property="id"/> <result column="user_id" property="userId"/> <result column="number" property="number"/> <result column="createtime" property="createtime"/> <result column="note" property="note"/> </collection> </resultMap>
第二步:定义mapper接口
List<User> findUserAndOrders();
第三步:定义pojo
第四步:测试
@Test public void findUserAndOrders() throws Exception { SqlSession openSession = sqlSessionFactory.openSession(); UserMapper mapper = openSession.getMapper(Mapper.class); List<User> userList = mapper.findUserAndOrders(); for (User user : userList) { for (Orders orders : user.getOrders()) { System.out.println(user + " " + orders); } } }
以上全部代码来自于: https://github.com/echo1937/mybatis-demo的mybatis-exercise模块