注:多对多可拆分为一对多,多对一,本文为一个用户对应多个订单,多个订单中对应多种商品
思路:1.通过用户id查出订单列表,2.通过订单id查出订单的商品信息,3.通过用户id查出用户的多个订单和每个订单对应的商品信息
注:test类的三个方法分别对应于上面的三步
1.User的javabean
package com.bean;
import java.util.List;
public class User {
private int id;
private String username;
private String loginname;
private String password;
private String phone;
private String address;
private List<Order> orders;
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 getLoginname() {
return loginname;
}
public void setLoginname(String loginname) {
this.loginname = loginname;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public List<Order> getOrders() {
return orders;
}
public void setOrders(List<Order> orders) {
this.orders = orders;
}
@Override
public String toString() {
return "User [id=" + id + ", username=" + username + ", loginname=" + loginname + ", password=" + password
+ ", phone=" + phone + ", address=" + address + "]";
}
}
2.Order的javabean
package com.bean;
import java.util.List;
public class Order {
private int id;
private String code;
private double total;
private User user;
private List<Article> articles;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getCode() {
return code;
}
public void setCode(String code) {
this.code = code;
}
public double getTotal() {
return total;
}
public void setTotal(double total) {
this.total = total;
}
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
public List<Article> getArticles() {
return articles;
}
public void setArticles(List<Article> articles) {
this.articles = articles;
}
@Override
public String toString() {
return "Order [id=" + id + ", code=" + code + ", total=" + total + "]";
}
}
3.Article的javabean
package com.bean;
public class Article {
private int id;
private String name;
private double price;
private String remark;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public double getPrice() {
return price;
}
public void setPrice(double price) {
this.price = price;
}
public String getRemark() {
return remark;
}
public void setRemark(String remark) {
this.remark = remark;
}
@Override
public String toString() {
return "Article [id=" + id + ", name=" + name + ", price=" + price + ", remark=" + remark + "]";
}
}
4.log4j.properties
# Global logging configuration
log4j.rootLogger=ERROR, stdout
# MyBatis logging configuration...
log4j.logger.com.mapper=DEBUG
# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n
5.jdbc.properties(对一些变量的设置)
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/mybatis
username=root
password=123456
6.mybatis配置文件
<?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">
<!--1.配置环境 ,默认的环境id为mysql-->
<configuration>
<!-- 导入 jdbc.properties 引用里面的值-->
<properties resource="jdbc.properties"/>
<!-- 1.指定mybatis所用日志的具体实现 -->
<settings>
<setting name="logImpl" value="LOG4j"/>
</settings>
<!--2.配置环境 ,默认的环境id为mysql-->
<environments default="mysql">
<!--2.2.配置id为mysql的数据库环境 -->
<environment id="mysql">
<!-- 2.2.1使用JDBC的事务管理 使用了JDBC的提交和回滚设置-->
<transactionManager type="JDBC"></transactionManager>
<!--2.2.2数据库连接池 POOLED是JDBC连接对象的数据源连接池的实现 -->
<dataSource type="POOLED">
<property name="driver" value="${driver}"/>
<property name="url" value="${url}"/>
<property name="username" value="${username}"/>
<property name="password" value="${password}"/>
</dataSource>
</environment>
</environments>
<!--3.配置Mapper的位置 -->
<mappers>
<mapper resource="com/mapper/userMapper.xml"/>
<mapper resource="com/mapper/orderMapper.xml"/>
<mapper resource="com/mapper/articleMapper.xml"/>
</mappers>
</configuration>
7.数据库的操作类(userMapper)
<?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">
<!-- namespace表示命名空间
程序代码访问文件是通过namespace完成的,包名+文件名命名
-->
<mapper namespace="com.mapper.userMapper">
<resultMap type="com.bean.User" id="userResultMap">
<id property="id" column="id"/>
<result property="username" column="username"/>
<result property="loginname" column="loginname"/>
<result property="password" column="password"/>
<result property="phone" column="phone"/>
<result property="address" column="address"/>
<collection property="orders" column="id" ofType="com.bean.Order" select="com.mapper.orderMapper.selectOrderMapper" fetchType="lazy">
<id property="id" column="id"/>
<result property="code" column="code"/>
<result property="total" column="total"/>
</collection>
</resultMap>
<select id="selectUserById" parameterType="Integer" resultMap="userResultMap">
select * from tb_user where id=#{id}
</select>
</mapper>
8.数据库的操作类(orderMapper)
<?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">
<!-- namespace表示命名空间
程序代码访问文件是通过namespace完成的,包名+文件名命名
-->
<mapper namespace="com.mapper.orderMapper">
<select id="selectOrderMapper" parameterType="int" resultType="com.bean.Order">
select * from tb_order where user_id=#{id}
</select>
<resultMap type="com.bean.Order" id="orderResultMap">
<id property="id" column="oid"/>
<result property="code" column="code"/>
<result property="total" column="total"/>
<association property="user" javaType="com.bean.User">
<id property="id" column="id"/>
<result property="username" column="username"/>
<result property="password" column="password"/>
<result property="loginname" column="loginname"/>
<result property="phone" column="phone"/>
<result property="address" column="address"/>
</association>
<collection property="articles" javaType="ArrayList" column="oid" ofType="com.bean.Article" select="com.mapper.articleMapper.ArticleMapper" fetchType="lazy">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="price" column="price"/>
<result property="remark" column="remark"/>
</collection>
</resultMap>
<select id="selectOrderById" parameterType="Integer" resultMap="orderResultMap">
select u.*,o.id AS oid,code,total,user_id
from tb_user u,tb_order o
where u.id=o.user_id and o.id=#{id}
</select>
<resultMap type="com.bean.Order" id="orderResultMap2">
<id property="id" column="id"/>
<result property="code" column="code"/>
<result property="total" column="total"/>
<collection property="articles" javaType="ArrayList" column="id" ofType="com.bean.Article" select="com.mapper.articleMapper.ArticleMapper" fetchType="lazy">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="price" column="price"/>
<result property="remark" column="remark"/>
</collection>
</resultMap>
<select id="selectOrderById2" parameterType="Integer" resultMap="orderResultMap2">
select *
from tb_order
where id=#{id}
</select>
</mapper>
9.数据库的操作类(articleMapper)
<?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">
<!-- namespace表示命名空间
程序代码访问文件是通过namespace完成的,包名+文件名命名
-->
<mapper namespace="com.mapper.articleMapper">
<select id="ArticleMapper" parameterType="int" resultType="com.bean.Article">
select * from tb_article where id in
(select article_id from tb_item where order_id=#{id})
</select>
</mapper>
10.查询
package com.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
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.apache.tools.ant.types.CommandlineJava.SysProperties;
import org.junit.Test;
import com.bean.Article;
import com.bean.Order;
import com.bean.User;
public class test {
@Test
public void selectById() throws IOException
{
InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession session = sessionFactory.openSession();
User user=session.selectOne("com.mapper.userMapper.selectUserById",1);
List<Order> orders=user.getOrders();
System.out.println("用户信息如下:");
System.out.println(user);
System.out.println("用户的订单如下:");
orders.forEach(order->System.out.println(order));
}
@Test
public void selectOrderById() throws IOException
{
InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession session = sessionFactory.openSession();
Order order=session.selectOne("com.mapper.orderMapper.selectOrderById",2);
System.out.println("order如下");
System.out.println(order);
System.out.println("用户如下");
User user=order.getUser();
System.out.println(user);
System.out.println("商品如下");
List<Article> articles=order.getArticles();
articles.forEach(article->System.out.println(article));
}
@Test
public void selectOrderById2() throws IOException
{
InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession session = sessionFactory.openSession();
User user=session.selectOne("com.mapper.userMapper.selectUserById",1);
System.out.println(user);
List<Order> orders=user.getOrders();
for(Order order:orders)
{
System.out.println("订单:"+order);
Order order2=session.selectOne("com.mapper.orderMapper.selectOrderById2",order.getId());
System.out.println("商品如下");
List<Article> articles=order2.getArticles();
articles.forEach(article->System.out.println(article));
}
}
}