MyBatis 动态SQL

1.输入映射和输出映射

Mapper.xml映射文件中定义了操作数据库的sql，每个sql是一个statement，映射文件是MyBatis的核心。

a）输入参数映射

parameterType(输入类型)

1.传递简单类型

使用#{}占位符，或者${}进行sql拼接

2.传递pojo对象

MyBatis使用ognl表达式解析对象字段的值，#{}或者${}括号中的值为pojo属性名称。

3.传递pojo包装对象

开发中通过使用pojo传递查询条件

查询条件可能是综合的查询条件，不仅包括用户查询条件还包括其他查询条件，这是就需要使用包装对象传递输入参数。

包装对象：pojo类中的一个属性是另外一个pojo。

QueryVo.java

package com.nikehu.mybatis.pojo; /** * 包装的pojo * @author 猪猪 * */ public class QueryVo { private User user; public User getUser() { return user; } public void setUser(User user) { this.user = user; } }

UserMapper.xml

<!-- 包装的pojo --> <insert id="insertQueryVo" parameterType="QueryVo"> INSERT INTO `mybatis01`.`user` ( `username`, `birthday`, `sex`, `address` ) VALUES ( #{ user.username }, #{ user.birthday }, #{ user.sex }, #{ user.address } ) ; </insert>

b）返回值映射

resultType(输出类型)

1.输出简单类型

<!-- 查询用户总记录数 --> <select id="getUserCount" resultType="int"> SELECT COUNT(1) FROM `user`; </select>

2.输出pojo类型

3.输出pojo列表

resultMap

resultType可以指定成功查询结果映射为pojo，但需要pojo属性名和sql查询的列名一致方可映射成功。

如果sql查询字段名和pojo的属性名不一致，可以通过resultMap将字段名和属性名做一个对应关系，resultMap实质上还需要将查询结果映射到pojo对象中。

resultMap可以实现将查询结果映射为复杂类型的pojo，比如在查询结果映射对象中包括pojo和list实现一对一查询和一对多查询

如果sql查询字段名和pojo的属性名不一致：

解决方案1：取别名

<select id="getOrderList" resultType="order"> SELECT `id`, `user_id` `userId`, `number`, `createtime`, `note` FROM `order` </select>

解决方案2：resultMap解决字段名与属性不一致的问题

<!-- 定义resultMap --> <resultMap type="order" id="order_list_map"> <!-- <id/>用于映射主键 --> <id property="id" column="id"/> <!-- <result/> 用于映射其他字段 --> <result property="userId" column="user_id"/> <result property="number" column="number"/> <result property="createtime" column="createtime"/> <result property="note" column="note"/> </resultMap> <!-- 使用resultMap --> <select id="getOrderListMap" resultMap="order_list_map"> SELECT `id`, `user_id`, `number`, `createtime`, `note` FROM `order` </select>

当pojo中部分属性名和数据库表中字段名一致时，可以不用配置，但是type属性必须写对，大小写不区分

2.动态SQL

a）If标签

<!-- if标签 --> <select id="getUserByPojo" parameterType="user" resultType="user"> SELECT `id`, `username`, `birthday`, `sex`, `address` FROM `mybatis01`.`user` <!-- WHERE username LIKE #{name} --> WHERE 1=1 <if test="username != null and username != ''"> and username LIKE '%${username}%' </if> <if test="sex != null and sex != ''"> and sex = #{sex} </if> </select> @Test public void testGetUserByPojo(){ SqlSession sqlSession = SqlSessionFactoryUtils.getSqlSessionFactory().openSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); User u = new User(); u.setUsername("小"); u.setSex("1"); List<User> users = mapper.getUserByPojo(u); for (User user : users) { System.out.println(user); } sqlSession.close(); }

b）Where标签

<!-- where标签 --> <select id="getUserByPojo" parameterType="user" resultType="user"> SELECT `id`, `username`, `birthday`, `sex`, `address` FROM `mybatis01`.`user` <!-- WHERE username LIKE #{name} --> <!-- where标签 自动补上where关键字，同时处理多余的and关键字 --> <where> <if test="username != null and username != ''"> and username LIKE '%${username}%' </if> <if test="sex != null and sex != ''"> and sex = #{sex} </if> </where> </select>

使用where标签，就不能再手动的加上where关键字。

c）Sql片段

sql中可将重复的sql提取出来，使用时用include引用即可，最终达到sql复用的目的

<!-- 使用sql标签将重复的sql片段抽取出来 --> <sql id="user_sql"> `id`, `username`, `birthday`, `sex`, `address` </sql> <select id="getUserById" parameterType="int" resultType="user"> SELECT <include refid="user_sql"></include> FROM `mybatis01`.`user` WHERE id = #{id} </select> <select id="getUserByPojo" parameterType="user" resultType="user"> SELECT <!-- 使用include标签引入sql片段 --> <include refid="user_sql"></include> FROM `mybatis01`.`user` <!-- WHERE username LIKE #{name} --> <where> <if test="username != null and username != ''"> and username LIKE '%${username}%' </if> <if test="sex != null and sex != ''"> and sex = #{sex} </if> </where> </select>

d）Foreach标签

场景1：Sql 中in关键字的使用

<select id="getUsersByIds" parameterType="QueryVo" resultType="user"> SELECT <include refid="user_sql"/> `uuid2` FROM `user` <where> <!-- foreach循环标签的使用 id IN(1,25,29,30,40) collection:要遍历的集合； open:循环开始时加上的片段； close：循环结束时加上的片段； seseparator：每一次循环的分隔符； item：每一次循环的元素变量。 --> <foreach collection="ids" open="id IN(" item="uid" separator="," close=")"> #{uid} </foreach> </where> </select> package com.nikehu.mybatis.pojo; import java.util.List; /** * 包装的pojo * @author 猪猪 * */ 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 testGetUsersByIds(){ SqlSession sqlSession = SqlSessionFactoryUtils.getSqlSessionFactory().openSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); QueryVo vo = new QueryVo(); ArrayList<Integer> ids = new ArrayList<Integer>(); ids.add(1); ids.add(2); ids.add(29); ids.add(30); ids.add(40); vo.setIds(ids); List<User> users = mapper.getUsersByIds(vo); for (User user : users) { System.out.println(user); } }

简写：构建ids列表

vo.setIds(Arrays.asList(1,25,29,30,40));

3.关联查询

a）一对一关联

一个订单只能有一个用户

一个用户可以有多个用户

使用resultType

关联查询使用resultType必须要有与数据库关系一致的pojo

OrderMapper.xml

<!-- 一对一关联查询使用resultType 查询订单关联用户 --> <select id="getOrderUser" resultType="OrderUser"> SELECT o.`id`, o.`user_id` userId, o.`number`, o.`createtime`, o.`note`, u.`username`, u.`address` FROM `order` o LEFT JOIN `user` u ON u.`id` = o.`user_id` </select>

OrderUser.java

package com.nikehu.mybatis.pojo; /** * 订单关联用户信息的pojo * @author 猪猪 * */ public class OrderUser extends Order { private String username; private String address; public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public String getAddress() { return address; } public void setAddress(String address) { this.address = address; } @Override public String toString() { return "OrderUser [username=" + username + ", address=" + address + ", getId()=" + getId() + ", getUserId()=" + getUserId() + ", getNumber()=" + getNumber() + ", getCreatetime()=" + getCreatetime() + ", getNote()=" + getNote() + "]"; } }

使用resultMap

在Order类里面封装一个User类对象，在使用resultMap查询(更符合面向对象的思想)

Order.java

package com.nikehu.mybatis.pojo; /** * 订单关联用户信息的pojo * @author 猪猪 * */ public class OrderUser extends Order { private String username; private String address; public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public String getAddress() { return address; } public void setAddress(String address) { this.address = address; } @Override public String toString() { return "OrderUser [username=" + username + ", address=" + address + ", getId()=" + getId() + ", getUserId()=" + getUserId() + ", getNumber()=" + getNumber() + ", getCreatetime()=" + getCreatetime() + ", getNote()=" + getNote() + "]"; } }

OrderMapper.xml

<!-- 一对一关联查询使用resultMap 查询订单关联用户 --> <resultMap type="order" id="order_user_map"> <!-- <id/>用于映射主键 --> <id property="id" column="id"/> <!-- <result/> 用于映射其他字段 --> <result property="userId" column="user_id"/> <result property="number" column="number"/> <result property="createtime" column="createtime"/> <result property="note" column="note"/> <!-- user属性的映射 --> <!-- association用于配置一对一关系; property：order里面的user属性 javaType:指定property的Java类型，可以用别名 --> <association property="user" javaType="User"> <id column="user_id" property="id"/> <result column="sex" property="sex"/> <result column="birthday" property="birthday"/> <result column="username" property="username"/> <result column="address" property="address"/> </association> </resultMap> <select id="getOrderUserList" resultMap="order_user_map"> SELECT o.`id`, o.`user_id`, o.`number`, o.`createtime`, o.`note`, u.`sex`, u.`birthday`, u.`username`, u.`address` FROM `order` o LEFT JOIN `user` u ON u.`id` = o.`user_id` </select>

b）一对多关联

User.java

package com.nikehu.mybatis.pojo; import java.util.Date; import java.util.List; public class User { private Integer id; private String username;// 用户姓名 private String sex;// 性别 private Date birthday;// 生日 private String address;// 地址 private String uuid2; private List<Order> orders; public List<Order> getOrders() { return orders; } public void setOrders(List<Order> orders) { this.orders = orders; } public String getUuid2() { return uuid2; } public void setUuid2(String uuid2) { this.uuid2 = uuid2; } public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public String getSex() { return sex; } public void setSex(String sex) { this.sex = sex; } public Date getBirthday() { return birthday; } public void setBirthday(Date birthday) { this.birthday = birthday; } public String getAddress() { return address; } public void setAddress(String address) { this.address = address; } @Override public String toString() { return "User [id=" + id + ", username=" + username + ", sex=" + sex + ", birthday=" + birthday + ", address=" + address + ", uuid2=" + uuid2 + "]"; } } <resultMap type="user" id="user_order_map"> <id column="id" property="id"/> <result column="sex" property="sex"/> <result column="birthday" property="birthday"/> <result column="username" property="username"/> <result column="address" property="address"/> <result column="uuid2" property="uuid2"/> <!-- collection用于配置一对多关联 property：User中的订单列表orders属性； ofType：property值的Java类型，支持别名 --> <collection property="orders" ofType="Order"> <id property="id" column="oid"/> <!-- <result/> 用于映射其他字段 --> <result property="userId" column="id"/> <result property="number" column="number"/> <result property="createtime" column="createtime"/> <result property="note" column="note"/> </collection> </resultMap> <select id="getUserOrderMap" resultMap="user_order_map"> SELECT u.`id`, u.`username`, u.`birthday`, u.`sex`, u.`address`, u.`uuid2`, o.`id` oid, o.`number`, o.`createtime`, o.`note` FROM `user` u LEFT JOIN `order` o ON o.`user_id` = u.`id` </select> @Test public void testGetUserOrderMap() { SqlSession sqlSession = SqlSessionFactoryUtils.getSqlSessionFactory().openSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); List<User> users = mapper.getUserOrderMap(); for (User user : users) { System.out.println(users); System.out.println("该用户的订单有："); for (Order order : user.getOrders()) { System.out.println(order); } } sqlSession.close(); }

多对多可拆分成两个一对多查询