目录
动态sql
通过mybatis提供的各种标签方法实现动态拼接sql
需求：根据性别和名字查询用户
if
1.UserMapper.xml映射文件中添加以下内容
<!-- 根据条件查询用户 -->
<select id="findUserListByIf" parameterType="user"
resultType="user">
select * from user
where 1=1
<if test="id!=null">
and id=#{id}
</if>
<if test="username!=null and username!=''">
and username like '%${username}%'
</if>
</select>2.在UserMapper接口添加如下方法：
List<User> findUserListByIf(User user);3.在UserMapperTest单元测试类中添加如下测试方法：
@Test
public void testFindUserListByIF() {
SqlSession sqlSession = sqlSessionFactory.openSession();
// 获得mapper代理对象
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
// 设置查询条件
User user = new User();
user.setId(10);
user.setUsername("张");
// 执行查询
List<User> userList = userMapper.findUserListByIf(user);
for (User user2 : userList) {
System.out.println(user2);
}
sqlSession.close();
}4.测试结果
- 同时查id和username
- 注释掉查id，只查username
- 注释掉username，只查id
- username要做不等于空字符串的校验。
- User类中id属性的类型要改为Integer包装类型，不然会报错，因为int类型的id是不可能为null。
where
1.UserMapper.xml映射文件中添加以下内容
<where><where />可以自动处理第一个and。
<!-- 根据条件查询用户 where -->
<select id="findUserListByWhere" parameterType="user" resultType="user">
select * from user
<where>
<if test="id!=null">
and id=#{id}
</if>
<if test="username != null and username != ''">
and username like '%${username}%'
</if>
</where>2.在UserMapper接口添加如下方法：
List<User> findUserListByWhere(User user);3.在UserMapperTest单元测试类中添加如下测试方法：
@Test
public void testFindUserListByWhere() {
SqlSession sqlSession = sqlSessionFactory.openSession();
// 获得mapper代理对象
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
// 设置查询条件
User user = new User();
user.setId(10);
//user.setUsername("张");
// 执行查询
List<User> userList = userMapper.findUserListByWhere(user);
for (User user2 : userList) {
System.out.println(user2);
}
sqlSession.close();
}4.测试结果同上
sql片段
sql中可将重复的sql提取出来，使用时用include引用即可，最终达到sql重用的目的
例如
<!-- 根据条件查询用户 where -->
<select id="findUserListByWhere" parameterType="user" resultType="user">
select * from user
<where>
<if test="id!=null">
and id=#{id}
</if>
<if test="username != null and username != ''">
and username like '%${username}%'
</if>
</where>将其中的*即要查询的字段抽取出来
<sql id="userfields">
id,username,birthday,sex,address
</sql>用include引用
<select id="findUserListByWhere" parameterType="user"
resultType="user">
select <include refid="userfields"></include> from user
<where>
<if test="id!=null">
and id=#{id}
</if>
<if test="username != null and username != ''">
and username like '%${username}%'
</if>
</where>
</select>也可将where条件抽取出来，同上用法
如果引用其它mapper.xml映射文件的sql片段，则在引用时需要加上namespace，如下：
<include refid="namespace.sql片段"/>
foreach
向sql传递数组或List，mybatis使用foreach解析。
需要：根据多个id查询用户信息
SELECT * FROM USER WHERE id IN (10,26,24)
1.改造QueryVo，在QueryVo类中定义List属性ids存储多个用户id，并添加getter/setter方法：
public class QueryVo {
private User user;
private List<Integer> ids;
public List<Integer> getIds() {
return ids;
}
public void setIds(List<Integer> ids) {
this.ids = ids;
}
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
}
2.在UserMapper.xml映射文件添加：
<!-- 根据ids查询用户 foreach -->
<select id="queryUserByIds" parameterType="queryvo"
resultType="user">
SELECT * FROM user
<where>
<!-- foreach标签，进行遍历 -->
<!-- collection：遍历集合，这里是QueryVo的ids属性 -->
<!-- item：遍历的项目，名称要和后面的#（）里面一致 -->
<!-- open：在前面添加的sql片段 -->
<!-- close：在结尾处添加的sql片段 -->
<!-- separator：指定遍历的元素之间使用的分隔符 -->
<!-- and id IN(10,24,27) -->
<foreach collection="ids" item="id" open="and id in("
close=")" separator=",">
#{id}
</foreach>
</where>
</select>3.在UserMapper接口添加如下方法：
List<User> queryUserByIds(QueryVo queryVo);4.在UserMapperTest单元测试类中添加如下测试方法：
@Test
public void testqueryUserByIds() {
SqlSession sqlSession = sqlSessionFactory.openSession();
// 获得mapper代理对象
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
// 设置查询条件
QueryVo queryVo = new QueryVo();
List<Integer> ids = new ArrayList<Integer>();
ids.add(10);
ids.add(16);
ids.add(22);
queryVo.setIds(ids);
// 执行查询
List<User> userList = userMapper.queryUserByIds(queryVo);
for (User user2 : userList) {
System.out.println(user2);
}
sqlSession.close();
}5.测试结果
关联查询
一对一查询
需求：查询所有订单信息，关联查询下单用户信息。
注意：因为一个订单只会是一个人下的订单，所以从查询订单信息出发关联查询用户信息为一对一查询。如果从用户信息下的订单信息为一对多查询，因为一个用户可以下多个订单。
- 方法一：使用resultType
1.改造订单pojo类，此pojo类中包括了订单信息和用户信息，这样返回对象的时候，mybatis自动把用户信息也注入进来了。
在com.feng.pojo包下新建一个OrderUser类，继承Order类后只需要定义用户的信息字段即可。
public class OrderUser extends Orders {
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 + ",id=" + getId() + ", userId=" + getUserId()
+ ", number=" + getNumber() + ", createtime=" + getCreatetime() + ", note=" + getNote() + "]";
}
}
2.OrderMapper.xml映射文件中添加以下内容：
<!-- 一对一查询，查询订单，同时包含用户数据 -->
<select id="getOrderUserList" resultType="orderuser">
SELECT
o.id,
o.user_id userId,
o.number,
o.createtime,
o.note,
u.username,
u.address
FROM
orders o
LEFT JOIN user u ON o.user_id = u.id
</select>3.在OrderMapper接口添加如下方法：
List<OrderUser> getOrderUserList();4.在单元测试类中编写如下测试方法：
@Test
public void testGetOrderUserList() {
SqlSession sqlSession = sqlSessionFactory.openSession();
OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);
List<OrderUser> orderList = orderMapper.getOrderUserList();
for (OrderUser orderUser : orderList) {
System.out.println(orderUser);
}
sqlSession.close();
}5.测试结果
- 方法二:使用resultMap（推荐）
定义专门的resultMap用于映射一对一查询结果。
1.改造Order类。首先在Orders类中加入user属性，user属性中用于存储关联查询的用户信息，因为订单关联查询用户是一对一关系，所以这里使用单个User对象存储关联查询的用户信息。
public class Orders {
private Integer id;
// userId跟数据库的user_id对应，但是名字不一样
private Integer userId;
private String number;
private Date createtime;
private String note;
private User user;
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public Integer getUserId() {
return userId;
}
public void setUserId(Integer userId) {
this.userId = userId;
}
public String getNumber() {
return number;
}
public void setNumber(String number) {
this.number = number == null ? null : number.trim();
}
public Date getCreatetime() {
return createtime;
}
public void setCreatetime(Date createtime) {
this.createtime = createtime;
}
public String getNote() {
return note;
}
public void setNote(String note) {
this.note = note == null ? null : note.trim();
}
@Override
public String toString() {
return "Orders [id=" + id + ", userId=" + userId + ", number=" + number + ", createtime=" + createtime
+ ", note=" + note + ", user=" + user + "]";
}
}2.OrderMapper.xml映射文件中添加以下内容：
<resultMap type="orders" id="order_user_resultmap">
<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：Orders里面的user属性名 -->
<!-- javaType：属性类型 -->
<association property="user"
javaType="com.feng.pojo.User">
<!-- id：声明主键，表示user id是关联查询对象的唯一标识，与orders表的user_id关联 -->
<id property="id" column="user_id" />
<result property="username" column="username" />
<result property="address" column="address" />
</association>
</resultMap>
<select id="getOrderUserResultMap"
resultMap="order_user_resultmap">
SELECT
o.id,
o.user_id,
o.number,
o.createtime,
o.note,
u.username,
u.address
FROM
orders o
LEFT JOIN `user` u ON o.user_id = u.id
</select>3.在OrderMapper接口中编写如下方法：
List<Orders> getOrderUserResultMap();4.在单元测试类中编写如下测试方法：
@Test
public void testGetOrderUserResultMap() {
SqlSession sqlSession = sqlSessionFactory.openSession();
OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);
List<Orders> orderList = orderMapper.getOrderUserResultMap();
for (Orders orders : orderList) {
System.out.println(orders);
}
sqlSession.close();
}5.测试结果
Orders [id=1, userId=10, number=32, createtime=Thu Aug 08 00:00:00 CST 2019, note=null, user=User [id=10, username=张三, sex=null, birthday=null, address=北京市]]
Orders [id=2, userId=24, number=54, createtime=Wed Aug 07 00:00:00 CST 2019, note=null, user=User [id=24, username=张三丰, sex=null, birthday=null, address=河南郑州]]
Orders [id=3, userId=27, number=12, createtime=Sat Aug 03 00:00:00 CST 2019, note=null, user=User [id=27, username=张艺兴, sex=null, birthday=null, address=长沙]]
Orders [id=4, userId=10, number=76, createtime=Thu Aug 08 00:00:00 CST 2019, note=null, user=User [id=10, username=张三, sex=null, birthday=null, address=北京市]]
一对多查询
需求：查询所有用户信息及用户关联的订单信息。用户信息和订单信息为一对多关系。使用resultMap来实现。
1.在User类中加入List<Orders> orders属性
public class User {
private Integer id;
private String username;
private String sex;
private Date birthday;
private String address;
// 一对多关系里，在一方的属性增加List来封装多方。
private List<Orders> orders;
public List<Orders> getOrders() {
return orders;
}
public void setOrders(List<Orders> orders) {
this.orders = orders;
}
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 + ", orders=" + orders + "]";
}
}
2.在UserMapper.xml映射文件中添加以下内容：
<!-- 一对多查询，查询所有用户的相关订单 -->
<resultMap type="user" id="user_order_resultmap">
<id property="id" column="id" />
<result property="username" column="username" />
<result property="birthday" column="birthday" />
<result property="sex" column="sex" />
<result property="address" column="address" />
<!-- 配置一对多关联映射 -->
<collection property="orders" ofType="orders">
<!-- id对应Orders类的主键属性 -->
<!-- oid别名是用来区别的 -->
<id property="id" column="oid" />
<result property="number" column="number" />
<result property="createtime" column="createtime" />
<result property="note" column="note" />
</collection>
</resultMap>
<select id="getUserWithOrders" resultMap="user_order_resultmap">
SELECT
u.id,
u.username,
u.birthday,
u.sex,
u.address,
o.id oid,
o.number,
o.createtime,
o.note
FROM
`user` u
LEFT JOIN orders o ON u.id = o.user_id
</select>
3.在UserMapper接口中编写如下方法：
List<User> getUserWithOrders();4.在单元测试类中编写如下测试方法：
@Test
public void testGetUserWithOrders() {
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<User> userList = userMapper.getUserWithOrders();
for (User user : userList) {
System.out.println(user);
}
sqlSession.close();
}5.测试结果：
User [id=10, username=张三, sex=1, birthday=Thu Jul 10 00:00:00 CST 2014, address=北京市, orders=[Orders [id=1, userId=null, number=32, createtime=Thu Aug 08 00:00:00 CST 2019, note=null, user=null], Orders [id=4, userId=null, number=76, createtime=Thu Aug 08 00:00:00 CST 2019, note=null, user=null]]]
User [id=24, username=张三丰, sex=1, birthday=null, address=河南郑州, orders=[Orders [id=2, userId=null, number=54, createtime=Wed Aug 07 00:00:00 CST 2019, note=null, user=null]]]
User [id=27, username=张艺兴, sex=1, birthday=Wed Aug 07 00:00:00 CST 2019, address=长沙, orders=[Orders [id=3, userId=null, number=12, createtime=Sat Aug 03 00:00:00 CST 2019, note=null, user=null]]]
User [id=16, username=张小明, sex=1, birthday=null, address=河南郑州, orders=[]]
User [id=22, username=陈小明, sex=1, birthday=null, address=河南郑州, orders=[]]
User [id=25, username=陈小明, sex=1, birthday=null, address=河南郑州, orders=[]]
User [id=26, username=王五, sex=null, birthday=null, address=null, orders=[]]
User [id=28, username=张艺兴, sex=1, birthday=Wed Aug 07 00:00:00 CST 2019, address=长沙, orders=[]]
User [id=29, username=刘备, sex=1, birthday=Wed Aug 07 00:00:00 CST 2019, address=蜀国, orders=[]]
扫一扫
683
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
