动态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); } }