🔍
📢

MyBatis动态SQL拼接和关联查询

本文重点讲述if、where、set、choose、trim和一对一,一对多,多对多相关内容!

 

为什么我们要使用mybatis,是因为JDBC存在以下问题:

1、 数据库连接创建、释放频繁造成系统资源浪费,从而影响系统性能。如果使用数据库连接池可解决此问题。

2、 Sql语句在代码中硬编码,造成代码不易维护,实际应用中sql变化的可能较大,sql变动需要改变java代码。

3、 使用preparedStatement向占有位符号传参数存在硬编码,因为sql语句的where条件不一定,可能多也可能少,修改sql还要修改代码,系统不易维护。

4、 对结果集解析存在硬编码(查询列名),sql变化导致解析代码变化,系统不易维护,如果能将数据库记录封装成pojo对象解析比较方便。

mybatis介绍
MyBatis 本是apache的一个开源项目iBatis, 2010年这个项目由apache software foundation 迁移到了google code,并且改名为MyBatis 。2013年11月迁移到Github。

MyBatis是一个优秀的持久层框架,它对jdbc的操作数据库的过程进行封装,使开发者只需要关注 SQL 本身,而不需要花费精力去处理例如注册驱动、创建connection、创建statement、手动设置参数、结果集检索等jdbc繁杂的过程代码。

Mybatis通过xml或注解的方式将要执行的各种statement(statement、preparedStatemnt、CallableStatement)配置起来,并通过java对象和statement中的sql进行映射生成最终执行的sql语句,最后由mybatis框架执行sql并将结果映射成java对象并返回。​

 

源码地址: https://github.com/yuanqinnan/java-learn

 

一、引言
二、if标签
三、where 标签
四、set 标签
五、choose(when,otherwise) 标签
六、trim 标签
七、SQL片段
八、foreach标签

九、关联查询
十、一对一
10.1、使用resultType
10.2、使用resultMap
十一、一对多
十二、多对多


一、引言
在之前的CRUD例子中,都是一些很简单的SQL,然而实际的业务开发中会有一些复杂的SQL,我们经常需要拼接SQL,拼接的时候要确保不能忘了必要的空格,还要注意省掉列名列表最后的逗号。Mybatis个一个强大特性--动态SQL,这一特性可以彻底摆脱这种痛苦。


二、if标签
现在有如下查询:


<!-- 根据条件查询用户 -->
<select id="queryUserByWhere" parameterType="user" resultType="user">
SELECT id, username, birthday, sex, address FROM `user`
WHERE sex = #{sex} AND username LIKE
'%${username}%'
</select>

当我们带入两个参数时,返回结果不会有问题,可是当我们只带入姓名,不带入性别时,结果就不合理,因为sex带入的null,作为查询条件就过滤了结果,这个时候我们需要if标签。

改造sql:


<!-- 根据条件查询用户 -->
<select id="queryUserByWhere" parameterType="user" resultType="user">
SELECT id, username, birthday, sex, address FROM `user`
WHERE 1=1
<if test="sex!=null and sex !=''">
AND sex = #{sex}
</if>
<if test="username!=null and username!=''">
AND username like
'%${username}%'
</if>
</select>

将接口和方法都加入其中


@Test
public void testQueryUserByWhere() {
// mybatis和spring整合,整合之后,交给spring管理
SqlSession sqlSession = this.sqlSessionFactory.openSession();
// 创建Mapper接口的动态代理对象,整合之后,交给spring管理
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
// 使用userMapper执行根据条件查询用户
User user = new User();
//user.setSex("1");
user.setUsername("张");
List<User>list = userMapper.queryUserByWhere(user);
for (User u : list) {
System.out.println(u);
}
// mybatis和spring整合,整合之后,交给spring管理
sqlSession.close();
}


三、where 标签
where标签会把第一个and忽略,当然如果是or开头的,MyBatis也会把它忽略,此外,在where元素中你不需要考虑空格的问题,MyBatis会智能的帮你加上。


<select id="queryUserByWhere1" parameterType="user" resultType="user">
SELECT id, username, birthday, sex, address FROM `user`
<!-- where标签可以自动添加where,同时处理sql语句中第一个and或者or关键字 -->
<where>
<if test="sex!=null">
AND sex = #{sex}
</if>
<if test="username!=null and username!=''">
AND username like
'%${username}%'
</if>
</where>
</select>


四、set 标签
在更新的时候我们也需要像where一样能够进行动态判断,这个时候就使用set标签,set会使最后的逗号忽略,我们就可以动态的更新那些修改了的字段。

如下:


<update id="dynamicSetTest" parameterType="user">
update `user`
<set>
<if test="sex != null">
sex = #{sex},
</if>
<if test="username!=null and username!=''">
username = #{username},
</if>
</set>
where id = #{id}
</update>

测试:


@Test
public void dynamicSetTest() {
SqlSession sqlSession = this.sqlSessionFactory.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User user = new User();
//user.setSex("1");
user.setUsername("袁大大");
user.setId(26);
userMapper.dynamicSetTest(user);
sqlSession.commit();
sqlSession.close();
}


五、choose(when,otherwise) 标签
choose的作用类似Java语言中的switch,可以解决我们只想选择一个查询条件的情况。

如下:


<select id="selectUserByChoose" resultType="user" parameterType="user">
select id, username, birthday, sex, address FROM `user`
<where>
<choose>
<when test="id !='' and id != null">
id=#{id}
</when>
<when test="username !='' and username != null">
and username like #{username}
</when>
<otherwise>
and sex=#{sex}
</otherwise>
</choose>
</where>
</select>

这个写法很容易理解,与switch相同,匹配成功后就会跳出。


六、trim 标签
trim标记是一个格式化的标记,可以完成set或者是where标记的功能,怎么用呢:

增加prefix前缀,去掉第一个prefixoverride中内容。

增加suffix后缀,去掉最后一个suffixoverride中内容。

通过trim可以解决where 与set 问题


<select id="selectUserByUsernameAndSex" resultType="user" parameterType="user">
select * from user
<!-- <where>
<if test="username != null">
username=#{username}
</if>

<if test="username != null">
and sex=#{sex}
</if>
</where> -->
<trim prefix="where" prefixOverrides="and | or">
<if test="username != null">
and username=#{username}
</if>
<if test="sex != null">
and sex=#{sex}
</if>
</trim>
</select>

先增加where,并去掉第一个and 或者or ,替换了where if 写法。


<!-- 根据 id 更新 user 表的数据 -->
<update id="updateUserById" parameterType="com.ys.po.User">
update user u
<!-- <set>
<if test="username != null and username != ''">
u.username = #{username},
</if>
<if test="sex != null and sex != ''">
u.sex = #{sex}
</if>
</set> -->
<trim prefix="set" suffixOverrides=",">
<if test="username != null and username != ''">
u.username = #{username},
</if>
<if test="sex != null and sex != ''">
u.sex = #{sex},
</if>
</trim>

where id=#{id}
</update>

增加set,并去掉最后一个逗号,替换了set if写法。


七、SQL片段
写sql时经常会出现一些重复片段,我们可以进行提取,这样可以做到重用。

先使用sql进行声明:

<!-- 声明sql片段 -->
<sql id="userFields">
id, username, birthday, sex, address
</sql>
使用include refid:


<select id="queryUserBySqlWhere" parameterType="user" resultType="user">
<!-- SELECT id, username, birthday, sex, address FROM `user` -->
<!-- 使用include标签加载sql片段;refid是sql片段id -->
SELECT <include refid ="userFields"/> FROM `user`
<!-- where标签可以自动添加where关键字,同时处理sql语句中第一个and关键字 -->
<where>
<if test="sex != null">
AND sex = #{sex}
</if>
<if test="username != null and username != ''">
AND username LIKE
'%${username}%'
</if>
</where>
</select>


八、foreach标签
当我们向sql传递数组或List,mybatis使用foreach解析。

foreach标签,进行遍历

collection:遍历的集合,这里是QueryVo的ids属性

item:遍历的项目,可以随便写,,但是和后面的#{}里面要一致

open:在前面添加的sql片段

close:在结尾处添加的sql片段

separator:指定遍历的元素之间使用的分隔符


<select id="queryUserByIds" parameterType="com.yuanqinnan.pojo.QueryVo" resultType="user">
SELECT * FROM `user`
<where>
<foreach collection="ids" item="item" open="id IN (" close=")"
separator=",">
#{item}
</foreach>

改造QueryVo:

@Data
public class QueryVo {
private User user;
private List<Integer> ids;
}
测试方法:


@Test
public void queryUserByIds(){
SqlSession sqlSession = this.sqlSessionFactory.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
QueryVo user = new QueryVo();
List<Integer>ids = new ArrayList<>();
ids.add(1);
ids.add(10);
ids.add(24);
user.setIds(ids);
List<User> list = userMapper.queryUserByIds(user);
for (User u : list) {
System.out.println(u);
}
sqlSession.close();
}

动态sql其实是一个拼接过程,我们掌握上面这些标签,就能完成mybatis的动态sql。

 

 

九、关联查询
通过动态SQL我们可以进行复杂SQL的编写,但之前的例子都是单表查询,在实际开发中,当然不可能都是单表,很多时候我们需要进行关联多表查询(有些公司为了性能还是尽量的使用单表查询),表与表之间的关系分为一对一,一对多,多对多,我们讲讲这三种是如何编写的。

先进行表和实体的创建:


CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(32) NOT NULL COMMENT '用户名称',
`birthday` date DEFAULT NULL COMMENT '生日',
`sex` char(1) DEFAULT NULL COMMENT '性别',
`address` varchar(256) DEFAULT NULL COMMENT '地址',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', '王五', null, '2', null);
INSERT INTO `user` VALUES ('10', '张三', '2014-07-10', '1', '北京市');
INSERT INTO `user` VALUES ('16', '张小明', null, '1', '河南郑州');
INSERT INTO `user` VALUES ('22', '陈小明', null, '1', '河南郑州');
INSERT INTO `user` VALUES ('24', '张三丰', null, '1', '河南郑州');
INSERT INTO `user` VALUES ('25', '陈小明', null, '1', '河南郑州');
INSERT INTO `user` VALUES ('26', '王五', null, null, null);

DROP TABLE IF EXISTS `order`;
CREATE TABLE `order` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL COMMENT '下单用户id',
`number` varchar(32) NOT NULL COMMENT '订单号',
`createtime` datetime NOT NULL COMMENT '创建订单时间',
`note` varchar(100) DEFAULT NULL COMMENT '备注',
PRIMARY KEY (`id`),
KEY `FK_order_1` (`user_id`),
CONSTRAINT `FK_order_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of order
-- ----------------------------
INSERT INTO `order` VALUES ('3', '1', '1000010', '2015-02-04 13:22:35', null);
INSERT INTO `order` VALUES ('4', '1', '1000011', '2015-02-03 13:22:41', null);
INSERT INTO `order` VALUES ('5', '10', '1000012', '2015-02-12 16:13:23', null);

User:


@Data
public class User implements Serializable {
private Integer id;
// 用户姓名
private String username;
// 性别
private String sex;
// 生日
private Date birthday;
// 地址
private String address;
}

Order:


@Data
public class Order {

// 订单id
private int id;
// 用户id
private Integer userId;
// 订单号
private String number;
// 订单创建时间
private Date createtime;
// 备注
private String note;
}


十、一对一
订单表与用户表,一个订单是由一个客户创建的,当我们通过订单去反查用户时,就是一对一关系。

有两种方式可以实现

10.1、使用resultType
可以改造订单pojo类,此pojo类中包括了订单信息和用户信息,这样返回对象的时候,mybatis自动把用户信息也注入进来了,创建OrderUserVO:


@Data
public class OrderUserVO extends Order {
/**
* 客户名称
*/
private String username;
/**
* 客户地址
*/
private String address;
}

xml文件:


<select id="queryOrderUser" resultType="com.yuanqinnan.pojo.OrderUserVO">
SELECTo.id,o.user_id,userId,o.number,o.createtime,o.note,u.username,u.address
FROM
`order` o
LEFT JOIN `user` u ON o.user_id = u.id
</select>

接口:

public interface OrderMapper {
List<Order> queryOrderAll();
List<Order> queryOrderAll2();
List<OrderUserVO> queryOrderUser();
}
测试:


@Test
public void queryOrderUser(){
SqlSession sqlSession = this.sqlSessionFactory.openSession();
OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);
List<OrderUserVO> list = orderMapper.queryOrderUser();
for (OrderUserVO u : list) {
System.out.println(u);
}
sqlSession.close();
}

定义专门的pojo类作为输出类型,其中定义了sql查询结果集所有的字段。此方法较为简单,企业中使用普遍。

10.2、使用resultMap

使用resultMap,定义专门的resultMap用于映射一对一查询结果,改造Order类,在Order类中加入User属性,user属性中用于存储关联查询的用户信息,因为订单关联查询用户是一对一关系,所以这里使用单个User对象存储关联查询的用户信息。


@Data
public class Order {

// 订单id
private int id;
// 用户id
private Integer userId;
// 订单号
private String number;
// 订单创建时间
private Date createtime;
// 备注
private String note;
/**
* 客户
*/
private User user;
}

先定义resultMap:


<resultMap type="com.yuanqinnan.model.Order" id="orderUserResultMap">
<id property="id" column="id"/>
<result property="userId" column="user_id"/>
<result property="number" column="number"/>
<result property="createtime" column="createtime"/>
<result property="note" column="note"/>
<!-- association :配置一对一属性 -->
<!-- property:order里面的User属性名 -->
<!-- javaType:属性类型 -->
<association property="user" javaType="user">
<!-- id:声明主键,表示user_id是关联查询对象的唯一标识-->
<id property="id" column="user_id"/>
<result property="username" column="username"/>
<result property="address" column="address"/>
</association>

</resultMap>

再添加查询


<!-- 一对一关联,查询订单,订单内部包含用户属性 -->
<select id="queryOrderUserResultMap" resultMap="orderUserResultMap">
SELECT o.id,o.user_id,o.number,o.createtime,o.note,u.username,u.address
FROM
`order` o
LEFT JOIN `user` u ON o.user_id = u.id
</select>

接口:

List<Order> queryOrderUserResultMap();
测试:


@Test
public void queryOrderUserResultMap(){
SqlSession sqlSession = this.sqlSessionFactory.openSession();
OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);
List<Order> list = orderMapper.queryOrderUserResultMap();
for (Order u : list) {
System.out.println(u);
}
sqlSession.close();
}


十一、一对多
一个客户会创建多个订单,当我们查询客户订单时,就会产生一对多的情况,我们以此为例。

首先,我们在User类中增加订单列表


@Data
public class User implements Serializable {

//id
private Integer id;
//用户姓名
private String username;
//性别
private String sex;
//生日
private Date birthday;
//地址
private String address;
//订单列表
private List<Order> orderList;
}

然后增加resultMap:


<resultMap type="com.yuanqinnan.model.Order" id="orderUserResultMap">
<id property="id" column="id"/>
<result property="userId" column="user_id"/>
<result property="number" column="number"/>
<result property="createtime" column="createtime"/>
<result property="note" column="note"/>

<!-- association :配置一对一属性 -->
<!-- property:order里面的User属性名 -->
<!-- javaType:属性类型 -->
<association property="user" javaType="user">
<!-- id:声明主键,表示user_id是关联查询对象的唯一标识-->
<id property="id" column="user_id"/>
<result property="username" column="username"/>
<result property="address" column="address"/>
</association>
</resultMap>

增加查询:


<!-- 一对一关联,查询订单,订单内部包含用户属性 -->
<select id="queryOrderUserResultMap" resultMap="orderUserResultMap">
SELECT o.id,o.user_id,o.number,o.createtime,o.note,u.username,u.address
FROM
`order` o
LEFT JOIN `user` u ON o.user_id = u.id
</select>

测试:


@Test
public void queryUserOrder(){
SqlSession sqlSession = this.sqlSessionFactory.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<User> list = userMapper.queryUserOrder();
for (User u : list) {
System.out.println(u);
}
sqlSession.close();
}


十二、多对多
多对多关系都需要中间表来维护,例如,一个订单包含多个产品,一种产品又可以出现在多个订单中,那这个时候我们就需要一个订单产品表作为中间表。这样我们需要新增两个表,产品表和订单产品表。


CREATE TABLE `product` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT NULL COMMENT '产品名称',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf;

-- ----------------------------
-- Records of product
-- ---------------------------
INSERT INTO product VALUES('1','笔记本电脑');
INSERT INTO product VALUES('2','小米手机');

CREATE TABLE `order_product` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`oid` int(11) DEFAULT NULL COMMENT '订单id',
`pid` int(11) DEFAULT NULL COMMENT '产品id',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
-- ----------------------------
-- Records of order_product
-- ---------------------------
INSERT INTO order_product VALUES('1','3','1');
INSERT INTO order_product VALUES('2','3','2');
INSERT INTO order_product VALUES('3','4','1');

我们可能遇到的多对多查询是:查询这个订单的所有产品或者查询这个产品的所有订单

那这个时候我们的订单和产品表里都应该对应多个中间表,实体就应该这么设计:

order实体增加中间表:


@Data
public class Order {

// 订单id
private int id;
// 用户id
private Integer userId;
// 订单号
private String number;
// 订单创建时间
private Date createtime;
// 备注
private String note;
//客户
private User user;
//中间表
private List<OrderProduct> products;
}

OrderProduct:


@Data
public class OrderProduct {

private Integer id;
//订单主键
private Integer oid;
//产品主键
private Integer pid;
//订单
private Order order;
//产品
private Product product;
}

product:


@Data
public class Product {
//产品id
private Integer id;
//产品名称
private String name;
//中间表
List<OrderProduct> orders;
}

resultmap:


<resultMap type="com.yuanqinnan.model.Order" id="orderBean">
<id column="id" property="id"/>
<result property="userId" column="user_id"/>
<result property="number" column="number"/>
<result property="createtime" column="createtime"/>
<result property="note" column="note"/>

<collection property="products" ofType="com.yuanqinnan.model.OrderProduct">
<id column="oiid" property="id"/>
<result column="oid" property="oid"/>
<result column="pid" property="pid"/>
<association property="product" javaType="com.yuanqinnan.model.Product">
<id column="pid" property="id"/>
<result column="pname" property="name"/>
</association>
</collection>
</resultMap>

新增查询订单的产品信息方法:


<select id="listOrder" resultMap="orderBean">
SELECT
o.*,
o.id oid,
p.id pid,
oi.id oiid,
p.NAME pname
FROM
`order` o
LEFT JOIN order_product oi ON o.id = oi.oid
LEFT JOIN product p ON p.id = oi.pid
</select>

测试:


@Test
public void listOrder(){
SqlSession sqlSession = this.sqlSessionFactory.openSession();
OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);
List<Order> list = orderMapper.listOrder();
for (Order u : list) {
System.out.println(u);
}
sqlSession.close();
}