//model层 package com.model; import java.io.Serializable; import java.util.Date; import java.util.List; public class User implements Serializable { private int id; private String username;// 用户姓名 private String sex;// 性别 private Date birthday;// 生日 private String address;// 地址 private List<Orders> ordersList; public List<Orders> getOrdersList() { return ordersList; } public void setOrdersList(List<Orders> ordersList) { this.ordersList = ordersList; } public User() { } public User(String username, String sex, Date birthday, String address) { this.username = username; this.sex = sex; this.birthday = birthday; this.address = address; } public User(String username, String sex, String address) { this.username = username; this.sex = sex; this.address = address; } 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 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 + "]"; } }
//
package com.model; import java.util.List; public class Orders { private String number; private Integer id; private Integer user_id; private String createtime; private List<OrderDetail> orderDetail;//订单详情 private User user;//用户 public User getUser() { return user; } public void setUser(User user) { this.user = user; } public List<OrderDetail> getOrderDetail() { return orderDetail; } public void setOrderDetail(List<OrderDetail> orderDetail) { this.orderDetail = orderDetail; } @Override public String toString() { return "Order{" + "number='" + number + '\'' + ", id=" + id + ", user_id=" + user_id + ", createtime='" + createtime + '\'' + '}'+this.getOrderDetail().toString()+this.getUser().toString(); } public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public Integer getUser_id() { return user_id; } public void setUser_id(Integer user_id) { this.user_id = user_id; } public String getCreatetime() { return createtime; } public void setCreatetime(String createtime) { this.createtime = createtime; } public String getNumber() { return number; } public void setNumber(String number) { this.number = number; } }
//
package com.model; public class OrderDetail { private Integer id; private Integer orders_id; private Integer items_id; private Integer items_num; private Items items; public Items getItems() { return items; } public void setItems(Items items) { this.items = items; } @Override public String toString() { return "OrderDetail{" + "id=" + id + ", orders_id=" + orders_id + ", items_id=" + items_id + ", items_num=" + items_num + '}'; } public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public Integer getOrders_id() { return orders_id; } public void setOrders_id(Integer orders_id) { this.orders_id = orders_id; } public Integer getItems_id() { return items_id; } public void setItems_id(Integer items_id) { this.items_id = items_id; } public Integer getItems_num() { return items_num; } public void setItems_num(Integer items_num) { this.items_num = items_num; } }
//
package com.model; public class Items { private Integer id; private String name; private float price; @Override public String toString() { return "Iteams{" + "id=" + id + ", name='" + name + '\'' + ", price=" + price + '}'; } 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; } }
//UserMapper
package com.mapper; import com.model.User; import java.util.List; public interface UserMapper { public List<User> findUserAndItems(); }
映射思路
- 将用户信息映射到user中。
- 在user类中添加订单列表属性List<Orders> orderslist,将用户创建的订单映射到orderslist
- 在Orders中添加订单明细列表属性List<Orderdetail> detailList,将订单的明细映射到detailList
- 在Orderdetail中添加Items属性,将订单明细所对应的商品映射到Items
//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="com.mapper.UserMapper"> <resultMap id="userAndItemsResMap" type="user"> <id property="id" column="id" /> <result property="username" column="username"/> <result property="address" column="address"/> <collection property="ordersList" ofType="com.gyf.model.Orders"> <id property="id" column="orders_id" /> <collection property="orderDetail" ofType="com.gyf.model.OrderDetail"> <id property="id" column="orderdetail_id" /> <result property="items_num" column="items_num"/> <association property="items" javaType="items"> <id property="id" column="items_id"/> <result property="name" column="name"/> <result property="price" column="price"/> </association> </collection> </collection> </resultMap> <select id="findUserAndItems" resultMap="userAndItemsResMap"> select u.id, u.username, u.address, o.id orders_id, od.id orderdetail_id, i.id items_id, i.name, od.items_num, i.price from user u, orders o, orderdetail od, items i where u.id = o.user_id and o.id = od.orders_id and i.id = od.items_id </select> </mapper>
//test
public class Demo001 { SqlSession session; @Before public void before() throws IOException { System.out.println("before.....获取session"); // a)读取配置文件; InputStream is = Resources.getResourceAsStream("SqlMapConfig.xml"); //b)通过SqlSessionFactoryBuilder创建SqlSessionFactory会话工厂。 SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(is); session = sessionFactory.openSession(); } @After public void after(){ session.close(); }
@Test public void test001() throws IOException { UserMapper userMapper = session.getMapper(UserMapper.class); List<User> list = userMapper.findUserAndItems(); for (User user : list){ System.out.println("用户名:" + user.getUsername() + "用户ID:"+user.getId() + "地址:" + user.getAddress()); for(Orders order : user.getOrdersList()){ System.out.println("订单号为:" + order.getId()); for (OrderDetail orderDetail : order.getOrderDetail()){ System.out.println("订单详情ID:"+ orderDetail.getId() + "数量:"+ orderDetail.getItems_num() + "商品名称:" + orderDetail.getItems().getName() + "价格" + orderDetail.getItems().getPrice()); } System.out.println("============================"); } } } } //配置文件 <?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <!--数据源--> <properties resource="db.properties"/> <!--配置别名--> <typeAliases> <!--<typeAlias type="com.model.User" alias="user"></typeAlias>--> <!--指定包名,别名就是类名,第一个小写 User 别名就是user--> <package name="com.model"></package> </typeAliases> <!-- 配置mybatis的环境信息 --> <environments default="development"> <environment id="development"> <!-- 配置JDBC事务控制,由mybatis进行管理 --> <transactionManager type="JDBC"></transactionManager> <!-- 配置数据源,采用dbcp连接池 --> <dataSource type="POOLED"> <property name="driver" value="${driverClass}"/> <property name="url" value="${url}"/> <property name="username" value="${name}"/> <property name="password" value="${password}"/> </dataSource> </environment> </environments> <!--告诉mybatis加载映射文件--> <mappers> <!--<mapper resource="com/sqlmap/User.xml"></mapper>--> <!--第一种:写映射文件的名字--> <!--<mapper resource="com/mapper/UserMapper.xml"></mapper>--> <!--第二种:写类名,一定要有个映射文件与之对应 如果没有,那么在UserMapper要声明注解--> <!--<mapper class="com.mapper.UserMapper"></mapper>--> <!--第三种:可以写包名--> <package name="com.mapper"></package> </mappers> </configuration>
控制台打印结果:
用户名:王五用户ID:1地址:江西宜春
订单号为:3
订单详情ID:1数量:1商品名称:台式机价格3000.0
订单详情ID:2数量:3商品名称:笔记本价格6000.0
============================
订单号为:4
订单详情ID:3数量:4商品名称:macbook笔记本价格22200.0
订单详情ID:4数量:3商品名称:笔记本价格6000.0
============================
总结
resultType:将查询结果按照sql列名pojo属性名一致性映射到pojo中。
resultMap:使用association和collection完成一对一和一对多高级映射(对结果有特殊的映射要求)。
association:将关联查询信息映射到一个pojo对象中。
collection:将关联查询信息映射到一个list集合中。