接着上一篇继续,首先介绍输入映射:通过parameterType指定输入参数的类型,类型可以是简单类型、hashmap、pojo的包装类型。
需求分析:如果是需要完成用户信息的综合查询,查询的条件可能很复杂,那么我们可以定义一个包装类型的pojo,把所有可能用到的查询条件封装到 此pojo的包装类型里。这里我们定义为QueryVo类:
public class QueryVo {
private User user;
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
}
Xml定义:
<!-- 通过queryVo来查询对象 -->
<select id="getUserByQueryVo" parameterType="queryVo" resultType="user">
select * from user where address like '%${user.address}%'
</select>
测试用例:
@Test
public void getUserByQueryVo() throws Exception {
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
QueryVo vo = new QueryVo();
User user = new User();
user.setAddress("河南");
vo.setUser(user);
List<User> userList = mapper.getUserByQueryVo(vo);
for (User user2 : userList) {
System.out.println(user2.getAddress());
}
sqlSession.close();
}
输出映射:定义resultMap通过resultMap来封装对象
<!-- 定义resultMap来告诉mybatis帮我们如何封装字段
type:表示这个resultMap最后封装的结果类型
-->
<resultMap type="orders" id="baseOrderMap">
<id column="id" property="id"/>
<result column="user_id" property="userId"/>
<result column="createtime" property="createtime"/>
<result column="number" property="number"/>
<result column="note" property="note"/>
</resultMap>
<select id="getOrderList" resultMap="baseOrderMap">
select * from orders ;
</select>
测试动用例:
/**
* 查询订单列表
* @throws Exception
*/
@Test
public void getOrderList() throws Exception {
OrdersMapper mapper = sqlSession.getMapper(OrdersMapper.class);
List<Orders> ordersList = mapper.getOrderList();
for (Orders orders : ordersList) {
System.out.println(orders.getUserId());
System.out.println(orders.getNote());
System.out.println(orders.getCreatetime());
}
sqlSession.close();
}
Mybatis的动态sql:传统的使用JDBC的方法,相信大家在组合复杂的的SQL语句的时候,需要去拼接,稍不注意哪怕少了个空格,都会导致错误。Mybatis的动态SQL功能正是为了解决这种问题, 其通过 if, choose, when, otherwise, trim, where, set, foreach标签,可组合成非常灵活的SQL语句,从而提高开发人员的效率。下面就去感受Mybatis动态SQL的魅力吧:
Mybatis的动态sql之if判断
<!-- 动态sql -->
<select id="getUserByCond" parameterType="user" resultType="user">
select * from user where 1=1
<if test="id !=null and id != '' ">
and id = #{id}
</if>
<if test="address !=null and address != '' ">
and address like '%${address}%'
</if>
</select>
测试用例:
/**
* 动态sql
* @throws Exception
*/
@Test
public void getUserByCond() throws Exception {
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = new User();
//user.setId(22);
user.setAddress("河南");
List<User> userList = mapper.getUserByCond(user);
for (User user2 : userList) {
System.out.println(user2.getAddress());
}
sqlSession.close();
}
第二个动态sql之where
<!-- 动态sql -->
<select id="getUserByCond" parameterType="user" resultType="user">
select * from user
<where>
<if test="id !=null and id != '' ">
and id = #{id}
</if>
<if test="address !=null and address != '' ">
and address like '%${address}%'
</if>
</where>
</select>
动态sql之foreach循环 :
<!-- 动态sql之foreach -->
<select id="getUserByIn" parameterType="queryVo" resultType="user">
select * from user
<where>
<!--
collection: 表示我们传入参数里面的集合属性
open:表示我们的循环sql以什么开头
close:表示我们的循环sql以什么结尾
separator:表示我们的循环sql用什么来做为分隔符
item:变量,表示我们每次循环,集合中的一个元素
-->
<foreach collection="ids" open="id in(" close=")" item="id" separator=",">
#{id}
</foreach>
</where>
</select>
测试用例:
/**
* 动态sql 之foreach
* @throws Exception
*/
@Test
public void getUserIn() throws Exception {
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
QueryVo vo = new QueryVo();
List<Integer> list = new ArrayList<Integer>();
list.add(1);
list.add(10);
list.add(16);
vo.setIds(list);
List<User> userList = mapper.getUserByIn(vo);
for (User user : userList) {
System.out.println(user.getId());
}
sqlSession.close();
}
动态sql之sql片段:
<sql id="baseSql">
select id,username,birthday,sex,address from user
</sql>
<!-- 动态sql之foreach -->
<select id="getUserByIn" parameterType="queryVo" resultType="user">
<include refid="baseSql"/>
<where>
<!--
collection: 表示我们传入参数里面的集合属性
open:表示我们的循环sql以什么开头
close:表示我们的循环sql以什么结尾
separator:表示我们的循环sql用什么来做为分隔符
item:变量,表示我们每次循环,集合中的一个元素
-->
<foreach collection="ids" open="id in(" close=")" item="id" separator=",">
#{id}
</foreach>
</where>
</select>
Mybatis的关联查询
一对一查询:
自定义resultMap
<resultMap type="orders" id="baseOrderWithUser">
<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="user" >
<result column="id" property="id"/>
<result column="username" property="username"/>
<result column="birthday" property="birthday"/>
<result column="sex" property="sex"/>
<result column="address" property="address"/>
</association>
</resultMap>
<select id="getOrdersWithUserBean" resultMap="baseOrderWithUser">
SELECT * FROM orders o LEFT JOIN USER u ON o.user_id = u.id
</select>
测试用例:
/**
* mybatis的关联查询之一对一,通过自定义resultMap来实现
* @throws Exception
*/
@Test
public void getOrdersWithUserBean() throws Exception {
OrdersMapper mapper = sqlSession.getMapper(OrdersMapper.class);
List<Orders> ordersList = mapper.getOrdersWithUserBean();
System.out.println(ordersList.size());
for (Orders orders : ordersList) {
System.out.println(orders.getUser().getAddress());
}
sqlSession.close();
}
一对多的关联查询,首选定义关联关系:
package com.yida.mybatis.pojo;
import java.util.Date;
import java.util.List;
public class User {
private int id;
private String username;
private Date birthday;
private String sex;
private String address;
private List<Orders> ordersList;
public List<Orders> getOrdersList() {
return ordersList;
}
public void setOrdersList(List<Orders> ordersList) {
this.ordersList = ordersList;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public User() {
super();
// TODO Auto-generated constructor stub
}
public User(int id, String username, Date birthday, String sex, String address) {
super();
this.id = id;
this.username = username;
this.birthday = birthday;
this.sex = sex;
this.address = address;
}
}
定义resultMap
<resultMap type="user" id="baseUserWithOrders">
<id column="id" property="id"/>
<result column="username" property="username"/>
<result column="birthday" property="birthday"/>
<result column="sex" property="sex"/>
<result column="address" property="address"/>
<!-- 在mybatis一对多的关联关系中,为了表示集合中每一个元素的类型,通常使用ofType来表示 -->
<collection property="orderList" ofType="orders">
<result column="user_id" property="userId"/>
<result column="number" property="number"/>
<result column="createtime" property="createtime"/>
<result column="note" property="note"/>
</collection>
</resultMap>
<!-- 查询mybatis一对多的关联关系 -->
<select id="getUserWithOrders" resultMap="baseUserWithOrders">
SELECT * FROM USER u LEFT JOIN orders o ON u.id = o.user_id WHERE u.id = 1
</select>
测试用例:
/**
* mybatis的一对多关联查询
*/
@Test
public void getUserWithOrders() throws Exception {
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> userList = mapper.getUserWithOrders();
System.out.println(userList.size());
for (User user : userList) {
System.out.println(user.getOrderList().size());
}
sqlSession.close();
}