动态slq
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.itheima.mybatis.mapper.UserMapper">
<!-- 根据用户的名字模糊查询 -->
<select id="findUserByQueryVo" parameterType="QueryVo" resultType="User">
select * from user where username like '%${user.username}%'
</select>
<!-- 查询数据条数 -->
<select id="countUser" resultType="Integer">
select count(*) from user
</select>
<!-- 根据性别和名字查询用户
where 可以去掉第一个前AND
-->
<select id="selectUserBySexAndUsername" parameterType="User" resultType="User">
select * from user
<where>
<if test="sex != null and sex != ''">
and sex= #{sex}
</if>
<if test="username != null and username != ''">
and username like '%${username}%'
</if>
</where>
</select>
<!-- 根据多个id查询用户信息 (1,2,3) -->
<select id="selectUserByIds" parameterType="QueryVo" resultType="User">
select * from user
<where>
id in
<foreach collection="idsList" item="id" separator="," open="(" close=")">
#{id}
</foreach>
</where>
</select>
</mapper>
定义一对一关系映射
需要在javaBean表示一个订单只有一个用户关系:
在Oders里面定义:private User user;
一对多
一个订单具有多个订单明细;
需求:查询订单(Orders),关联查询订单明细(orderDetail)(一对多查询)
多对多
用户和商品是多对多关系:
查询用户 关联查询 商品(Items)。
User:
package domain;
import java.util.Date;
import java.util.List;
public class User {
private Integer id;
private String username;
private Date birthday;
private String sex;
private String address;
private List<Orders> orderList;
//每个用户有多条订单
public List<Orders> getOrderList() {
return orderList;
}
public void setOrderList(List<Orders> orderList) {
this.orderList = orderList;
}
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 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;
}
@Override
public String toString() {
return "User [id=" + id + ", username=" + username + ", birthday="
+ birthday + ", sex=" + sex + ", address=" + address + "]";
}
}
Orders:
package domain;
import java.util.Date;
import java.util.List;
public class Orders {
private Integer id;
private Integer userId;
private String number;
private Date createTime;
private String note;
//一个订单只有一个用户
private User user;
//一个订单具有多个订单明细
private List<OrderDetail> orderDetailList;
public List<OrderDetail> getOrderDetail() {
return orderDetailList;
}
public void setOrderDetail(List<OrderDetail> orderDetailList) {
this.orderDetailList = orderDetailList;
}
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;
}
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;
}
public String toString() {
return "Orders [id=" + id + ", userId=" + userId + ", number=" + number + ", createTime=" + createTime
+ ", note=" + note + ", user=" + user + ", orderDetailList=" + orderDetailList + "]";
}
}
OrderDetail:
package domain;
public class OrderDetail {
private Integer id;
private Integer orderId;
private Integer itemsId;
private Integer itemsNum;
//一个订单明细只属于一个商品
private Items items;
public Items getItems() {
return items;
}
public void setItems(Items items) {
this.items = items;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public Integer getOrderId() {
return orderId;
}
public void setOrderId(Integer orderId) {
this.orderId = orderId;
}
public Integer getItemsId() {
return itemsId;
}
public void setItemsId(Integer itemsId) {
this.itemsId = itemsId;
}
public Integer getItemsNum() {
return itemsNum;
}
public void setItemsNum(Integer itemsNum) {
this.itemsNum = itemsNum;
}
}
Items:
package domain;
import java.util.Date;
public class Items {
private Integer id;
private String name;
private Float price;
private String detail;
private String pic;
private Date createTime;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Float getPrice() {
return price;
}
public void setPrice(Float price) {
this.price = price;
}
public String getDetail() {
return detail;
}
public void setDetail(String detail) {
this.detail = detail;
}
public String getPic() {
return pic;
}
public void setPic(String pic) {
this.pic = pic;
}
public Date getCreateTime() {
return createTime;
}
public void setCreateTime(Date createTime) {
this.createTime = createTime;
}
}
OrderMapper:
package dao;
import java.util.List;
import domain.OrderDetail;
import domain.Orders;
import domain.OrdersCustom;
public interface OrdersMapper {
//使用resultType实现一对一映射
public List<OrdersCustom> findOrdersAndUserResultType();
//使用resultMap实现一对一映射
public List<Orders> findOrdersAndUserResultMap();
//使用resultMap实现 一对多映射
public List<Orders> findOrdersAndOrderDetailMap();
}
OrderMapper.xml:
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="dao.OrdersMapper">
<!--
resultMap:实现查询列名和javaBean属性一一对应映射
type:指定需要映射的类型
id:resultMap的唯一标识,可以定义多个resultMap
-->
<resultMap type="orders" id="BaseResultMap">
<!-- 主键映射 :使用Id
column:查询列名
id:javaBean属性
-->
<id column="id" property="id"/>
<!-- 普通属性映射:使用result
column:查询列名
property:javaBean属性
-->
<result column="user_id" property="userId"/>
<result column="number" property="number"/>
<result column="createtime" property="createTime"/>
<result column="note" property="note"/>
<!-- association:配置一对一关系映射标签
property:表示映射到Orders中的那个属性
javaType:指定映射属性的类型
-->
<association property="user" javaType="user">
<!-- 配置主键映射 -->
<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"/>
</association>
<!--
查询订单,关联查询订单明细,一对多关系
collection:一对多关系映射
property:映射到Orders中的orderDetailList
ofType:指定集合封装对象类型
-->
<collection property="orderDetailList" ofType="orderDetail">
<!-- 主键映射 -->
<id column="orderdetail_id" property="id"/>
<!-- 普通属性映射 -->
<result column="orders_id" property="orderId"/>
<result column="items_id" property="itemsId"/>
<result column="items_num" property="itemsNum"/>
</collection>
</resultMap>
<!-- 1.使用resultType实现一对一映射 -->
<select id="findOrdersAndUserResultType" resultType="OrdersCustom">
select orders.*,user.username,user.sex,user.address from orders,user
where orders.user_id =user.id
</select>
<!-- 2.使用resultMap实现一对一映射 -->
<select id="findOrdersAndUserResultMap" resultMap="BaseResultMap">
select orders.*,user.username,user.sex,user.address from orders,user
where orders.user_id =user.id
</select>
<!-- 3.查询订单,关联查询订单明细(一对多) -->
<select id="findOrdersAndOrderDetailMap" resultMap="BaseResultMap">
select orders.*,user.username,user.sex,user.address,
orderdetail.id orderdetail_id,orderdetail.orders_id,orderdetail.items_id,orderdetail.items_num
from
orders,user,orderdetail
where orders.user_id=user.id and orders.id=orderdetail.orders_id
</select>
</mapper>
UserMapper:
package dao;
import java.util.List;
import domain.User;
public interface UserMapper {
//查询用户,关联查询商品
public List<User> findUserAndItemsManyToMany();
}
UserMapper.xml:
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="dao.UserMapper">
<!--
resultMap:实现查询列名和javaBean属性一一对应映射
type:指定需要映射的类型
id:resultMap的唯一标识,可以定义多个resultMap
-->
<resultMap type="user" id="BaseResultMap" >
<!-- 配置主键映射 -->
<id column="user_id" property="id"/>
<!-- 普通属性映射 -->
<result column="username" property="username"/>
<result column="birthday" property="birthday"/>
<result column="sex" property="sex"/>
<result column="address" property="address"/>
</resultMap>
<resultMap type="user" id="BaseResultMapRM" extends="BaseResultMap">
<!-- 一个用户有多个订单 -->
<collection property="orderList" ofType="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 property="orderDetailList" ofType="orderDetail">
<id column="orderdetail_id" property="id"/>
<result column="orders_id" property="orderId"/>
<result column="items_id" property="itemsId"/>
<result column="items_num" property="itemsNum"/>
<!-- 一个订单有一个商品:一对一关系 -->
<association property="items" javaType="items">
<id column="items_id" property="id"/>
<result column="name" property="name"/>
<result column="price" property="price"/>
<result column="detail" property="detail"/>
<result column="pic" property="pic"/>
<result column="createtime" property="createTime"/>
</association>
</collection>
</collection>
</resultMap>
<!-- 查询用户,关联查询商品:多对多-->
<select id="findUserAndItemsManyToMany" resultMap="BaseResultMapRM">
select
orders.*,user.id user_id,user.birthday,user.username,user.sex,user.address,
orderdetail.id orderdetail_id,orderdetail.orders_id,orderdetail.items_id,orderdetail.
items_num,items.id items_id,items.name,items.price,items.detail,items.pic,items.createtime
from
orders,user,orderdetail,items
where orders.user_id=user.id and orderdetail.orders_id=orders.id and items.id=orderdetail.items_id
</select>
</mapper>
Test:
package test;
import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;
import dao.OrdersMapper;
import dao.UserMapper;
import domain.Orders;
import domain.OrdersCustom;
import domain.User;
public class Main1 {
SqlSessionFactory sqlSessionFactory=null;
@Before
public void beforeConf() throws IOException{
String resources="sqlMapConfig.xml";
InputStream inputStream=Resources.getResourceAsStream(resources);
sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
}
//使用resultType实现一对一映射查询
@Test
public void findOrdersAndUserResultType(){
//获取sqlSession
SqlSession sqlSession=sqlSessionFactory.openSession();
//获取OrdersMapper代理对象
OrdersMapper ordersMapper=sqlSession.getMapper(OrdersMapper.class);
//调用接口方法
List<OrdersCustom> list = ordersMapper.findOrdersAndUserResultType();
System.out.println(list);
}
//使用resultMap实现一对一映射查询
@Test
public void findOrdersAndUserResultMap(){
//获取sqlSession
SqlSession sqlSession=sqlSessionFactory.openSession();
//获取OrdersMapper代理对象
OrdersMapper ordersMapper=sqlSession.getMapper(OrdersMapper.class);
//调用接口方法
List<Orders> list = ordersMapper.findOrdersAndUserResultMap();
System.out.println(list);
System.out.println(list.size());
}
//使用resultMap实现一对多映射
@Test
public void findOrdersAndOrderDetailMap(){
SqlSession sqlSession=sqlSessionFactory.openSession();
OrdersMapper ordersMapper=sqlSession.getMapper(OrdersMapper.class);
List<Orders> list = ordersMapper.findOrdersAndOrderDetailMap();
System.out.println(list);
}
//使用resultMap实现多对多映射查询
@Test
public void findUserAndItemsManyToMany(){
SqlSession sqlSession=sqlSessionFactory.openSession();
UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
List<User> list = userMapper.findUserAndItemsManyToMany();
System.out.println(list);
}
}