4.Mybatis1动态sql if where foreach 对1,1对多，多对多查询
动态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);
}
}
浙公网安备 33010602011771号