5.1 新建数据库准备
CREATE TABLE `finacial_products` (
`product_id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(32) NOT NULL COMMENT '理财产品名称',
`price` float(10,1) NOT NULL COMMENT '理财产品定价',
`detail` text COMMENT '理财产品描述',
`pic` varchar(64) DEFAULT NULL COMMENT '理财产品图片',
`invasttime` datetime NOT NULL COMMENT '理财产品收益日期',
PRIMARY KEY (`product_id`)
) ；
CREATE TABLE `customer` (
`cus_id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(32) NOT NULL COMMENT '用户名称',
`acno` varchar(32) DEFAULT NULL COMMENT '卡号',
`gender` varchar(4) DEFAULT NULL COMMENT '性别',
`phone` varchar(256) DEFAULT NULL COMMENT '电话',
PRIMARY KEY (`cus_id`)
)；
CREATE TABLE `batch` (
`batch_id` int(11) NOT NULL AUTO_INCREMENT,
`cus_id` int(11) NOT NULL COMMENT '创建批次用户id',
`number` varchar(32) NOT NULL COMMENT '批次编码',
`createtime` datetime NOT NULL COMMENT '创建批次时间',
`note` varchar(100) DEFAULT NULL COMMENT '备注',
PRIMARY KEY (`batch_id`),
KEY `FK_batch_1` (`cus_id`),
CONSTRAINT `FK_batch_id` FOREIGN KEY (`cus_id`) REFERENCES `customer` (`cus_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
)；
CREATE TABLE `batchdetail` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`batch_id` int(11) NOT NULL COMMENT '批次id',
`product_id` int(11) NOT NULL COMMENT '理财产品id',
`product_num` int(11) DEFAULT NULL COMMENT '理财产品购买数量',
PRIMARY KEY (`id`),
KEY `FK_batchdetail_1` (`batch_id`),
KEY `FK_batchdetail_2` (`product_id`),
CONSTRAINT `FK_batchdetai_1` FOREIGN KEY (`batch_id`) REFERENCES `batch` (`batch_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `FK_batchdetai_2` FOREIGN KEY (`product_id`) REFERENCES `finacial_products` (`product_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
)；
5.2 一对一查询
新建类在包 cn.com.mybatis.po
Batch.java
getter/setter方法
public class Batch {
private int batch_id;
private int cus_id;
private String number;
private Date createtime;
private String note;
.....
}
BatchCustomer.java
public class BatchCustomer extends Batch{
private String username;
private String acno;
...
}
一对一查询
使用resultType实现
UserMapper.xml
<select id="findBatchCustomer" resultType="cn.com.mybatis.po.BatchCustomer">
SELECT
BATCH.*,
CUSTOMER.username,
CUSTOMER.acno
FROM
BATCH,
CUSTOMER
WHERE BATCH.cus_id = CUSTOMER.cus_id
</select>
MyBatisTest.java
@Test
public void testBatchCustomer() throws Exception{ SqlSession sqlSession = dataConn.getSqlSession(); //调用userMapper的方法
List<BatchCustomer> bcList=sqlSession.selectList("test.findBatchCustomer");
if(bcList!=null){
BatchCustomer batchCustomer = null;
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
for (int i = 0; i < bcList.size(); i++) {
batchCustomer = bcList.get(i);
System.out.println("卡号为"+batchCustomer.getAcno()+"的名为"
+batchCustomer.getUsername()+"的客户:\n于"
+sdf.format(batchCustomer.getCreatetime())+"采购了批次号为"
+batchCustomer.getNumber()+"的一批理财产品");
}
}
sqlSession.close();
}
运行的结果：
DEBUG [main] - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@480bdb19]
DEBUG [main] - ==> Preparing: SELECT BATCH.*, CUSTOMER.username, CUSTOMER.acno FROM BATCH, CUSTOMER WHERE BATCH.cus_id = CUSTOMER.cus_id
DEBUG [main] - ==> Parameters:
DEBUG [main] - <== Total: 1
卡号为622848的名为Mr的客户:
于2018-10-04 16:47:41采购了批次号为牛奶的一批理财产品
DEBUG [main] - Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@480bdb19]
DEBUG [main] - Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@480bdb19]
数据库的内容
customer表：
batch表：
使用resultMap实现
新建两个类
Customer.java
public class Customer implements Serializable{
private int cus_id;
private String username;
private String acno;
private String gender;
private String phone;
private List<Batch> batchList;
...
}
BatchItem.java
import java.util.Date;
import java.util.List;
public class BatchItem {
private int batch_id;
private int cus_id;
private String number;
private Date createtime;
private String note;
private Customer customer;
...
}
UserMapper.xml
<resultMap type="cn.com.mybatis.po.BatchItem" id="BatchInfoMap">
<id column="batch_id" property="batch_id"/>
<result column="cus_id" property="cus_id"/>
<result column="number" property="number"/>
<result column="createtime" property="createtime" javaType="java.util.Date"/>
<result column="note" property="note"/>
<association property="customer" javaType="cn.com.mybatis.po.Customer">
<id column="cus_id" property="cus_id"/>
<result column="username" property="username"/>
<result column="acno" property="acno"/>
<result column="gender" property="gender"/>
<result column="phone" property="phone"/>
</association>
</resultMap> <select id="findBatchCustomerToMap" resultMap="BatchInfoMap">
SELECT
BATCH.*,
CUSTOMER.username,
CUSTOMER.acno
FROM
BATCH,
CUSTOMER
WHERE BATCH.cus_id = CUSTOMER.cus_id
</select>
MyBatisTest.java
@Test
public void testBatchCustomerToMap() throws Exception{ SqlSession sqlSession=dataConn.getSqlSession(); //调用userMapper的方法
List<BatchItem> bcList=sqlSession.selectList("findBatchCustomerToMap");
if(bcList!=null){
BatchItem batchItem = null;
Customer customer = null;
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
for (int i = 0; i < bcList.size(); i++) {
batchItem = bcList.get(i);//取出批次对象
customer = batchItem.getCustomer();//取出该批次的用户信息
System.out.println("卡号为"+customer.getAcno()+"的名为"
+customer.getUsername()+"的客户:\n于"
+sdf.format(batchItem.getCreatetime())+"采购了批次号为"
+batchItem.getNumber()+"的一批理财产品");
}
}
sqlSession.close();
}
控制台结果：
DEBUG [main] - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@7e2d773b]
DEBUG [main] - ==> Preparing: SELECT BATCH.*, CUSTOMER.username, CUSTOMER.acno FROM BATCH, CUSTOMER WHERE BATCH.cus_id = CUSTOMER.cus_id
DEBUG [main] - ==> Parameters:
DEBUG [main] - <== Total: 1
卡号为622848的名为Mr的客户:
于2018-10-04 16:47:41采购了批次号为牛奶的一批理财产品
DEBUG [main] - Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@7e2d773b]
一对多查询
首先对数据路进行补充：
batch表：
batchdetail表：
customer表：
finacial_products表：
在数据库中测试指令：
SELECT
BATCH.*,
CUSTOMER.username,
CUSTOMER.acno,
BATCHDETAIL.product_id,
BATCHDETAIL.product_num
FROM
BATCH,
CUSTOMER,
BATCHDETAIL
WHERE BATCH.cus_id = CUSTOMER.cus_id AND BATCHDETAIL.batch_id=BATCH.batch_id;
得到：
Batch.java
添加一句代码：
public class Batch {
private int batch_id;
private int cus_id;
private String number;
private Date createtime;
private String note;
private List<BatchDetail> batchDetials;
...
}
BatchDetail.java
public class BatchDetail {
private int id;
private int batch_id;
private int product_id;
private int product_num;
...
}
BatchItem.java
添加
//批次包含的理财产品订购信息
private List<BatchDetail> batchDetails;
BatchDatas.java
public class BatchDatas {
private int batch_id;
private int cus_id;
private String number;
private Date createtime;
private String note;
private Customer customer;
private List<BatchDetail> batchDetails;
...
}
UserMapper.xml
<resultMap type="cn.com.mybatis.po.BatchItem" id="BatchAndBatchDetailResultMap"
extends="BatchInfoMap">
<collection property="batchDetails" ofType="cn.com.mybatis.po.BatchDetail">
<!-- id:订单明细的唯一标识 -->
<id column="id" property="id"/>
<result column="batch_id" property="batch_id"/>
<result column="product_id" property="product_id"/>
<result column="product_num" property="product_num"/>
</collection>
</resultMap> <select id="findBatchAndBatchDetail" resultMap="BatchAndBatchDetailResultMap">
SELECT
BATCH.*,
CUSTOMER.username,
CUSTOMER.acno,
BATCHDETAIL.product_id,
BATCHDETAIL.product_num
FROM
BATCH,
CUSTOMER,
BATCHDETAIL
WHERE BATCH.cus_id = CUSTOMER.cus_id AND BATCHDETAIL.batch_id=BATCH.batch_id
</select>
MyBatisTest.java
@Test
public void testfindBatchAndBatchDetail() throws Exception{ SqlSession sqlSession=dataConn.getSqlSession(); //调用userMapper的方法
BatchItem batchItem=sqlSession.selectOne("findBatchAndBatchDetail");
if(batchItem!=null){
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Customer customer = batchItem.getCustomer();//取出该批次的用户信息
//取出该批次订购的理财产品信息
List<BatchDetail> batchDetails = batchItem.getBatchDetails();
System.out.println("卡号为"+customer.getAcno()+"的名为"
+customer.getUsername()+"的客户:\n于"
+sdf.format(batchItem.getCreatetime())+"采购了批次号为"
+batchItem.getNumber()+"的一批理财产品，详情如下：");
BatchDetail batchDetail = null;
if(batchDetails!=null){
for (int i = 0; i < batchDetails.size(); i++) {
batchDetail = batchDetails.get(i);
System.out.println("id为"+batchDetail.getProduct_id()
+"的理财产品"+batchDetail.getProduct_num()+"份");
} } }
sqlSession.close();
}
测试结果：
DEBUG [main] - ==> Preparing: SELECT BATCH.*, CUSTOMER.username, CUSTOMER.acno, BATCHDETAIL.product_id,
BATCHDETAIL.product_num FROM BATCH, CUSTOMER, BATCHDETAIL WHERE BATCH.cus_id = CUSTOMER.cus_id AND
BATCHDETAIL.batch_id=BATCH.batch_id
DEBUG [main] - ==> Parameters:
DEBUG [main] - <== Total: 2
卡号为622848的名为Mr的客户:
于2018-10-04 16:47:41采购了批次号为牛奶的一批理财产品，详情如下：
id为1的理财产品100份
id为2的理财产品50份
DEBUG [main] - Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@2173f6d9]
多对多的查询
Customer.java
public class Customer implements Serializable{
private int cus_id;
private String username;
private String acno;
private String gender;
private String phone;
private List<Batch> batchList;
...
}
Batch.java
public class Batch {
private int batch_id;
private int cus_id;
private String number;
private Date createtime;
private String note;
private List<BatchDetail> batchDetials;
...
}
BatchDetail.java
public class BatchDetail {
private int id;
private int batch_id;
private int product_id;
private int product_num;
private FinacialProduct finacialProduct;
...
}
FinacialProduct.java
public class FinacialProduct {
private int id;
private String name;
private double price;
private String detail;
private String imgpath;
private Date invattime;
...
}
UserMapper.xml
<resultMap type="cn.com.mybatis.po.Customer" id="UserAndProductsResultMap">
<!-- 客户信息 -->
<result column="username" property="username"/>
<result column="acno" property="acno"/> <!--批次订单信息，一个客户对应多个订单-->
<collection property="batchList" ofType="cn.com.mybatis.po.Batch">
<id column="batch_id" property="batch_id"/>
<result column="cus_id" property="cus_id"/>
<result column="number" property="number"/>
<result column="createtime" property="createtime" javaType="java.util.Date"/>
<result column="note" property="note"/> <collection property="batchDetials" ofType="cn.com.mybatis.po.BatchDetail">
<!-- id:订单明细的唯一标识 -->
<id column="id" property="id"/>
<result column="batch_id" property="batch_id"/>
<result column="product_id" property="product_id"/>
<result column="product_num" property="product_num"/> <association property="finacialProduct" javaType="cn.com.mybatis.po.FinacialProduct">
<id column="product_id" property="id"/>
<result column="name" property="name"/>
<result column="price" property="price"/>
<result column="detail" property="detail"/>
</association>
</collection>
</collection>
</resultMap> <select id="findUserAndProducts" resultMap="UserAndProductsResultMap">
SELECT
BATCH.*,
CUSTOMER.username,
CUSTOMER.acno,
BATCHDETAIL.product_id,
BATCHDETAIL.product_num,
FINACIAL_PRODUCTS.name,
FINACIAL_PRODUCTS.detail,
FINACIAL_PRODUCTS.price
FROM
BATCH,
CUSTOMER,
BATCHDETAIL,
FINACIAL_PRODUCTS
WHERE BATCH.cus_id = CUSTOMER.cus_id
AND BATCHDETAIL.batch_id=BATCH.batch_id
AND FINACIAL_PRODUCTS.product_id=BATCHDETAIL.product_id;
</select>
MybatisTest.java
@Test
public void testfindCustomerAndProducts() throws Exception{ SqlSession sqlSession=dataConn.getSqlSession(); //调用userMapper的方法，获取所有用户信息(以及从属批次信息)
List<Customer> customerList=sqlSession.selectList("findUserAndProducts");
if(customerList!=null){
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Customer customer = null;
for (int i = 0; i < customerList.size(); i++) {
customer = customerList.get(i);
//1.获取用户基本信息
System.out.println("卡号为"+customer.getAcno()+"的名为"
+customer.getUsername()+"的客户:");
//2.获取用户下的所有批次订单信息
List<Batch> batchList=customer.getBatchList();
Batch batch = null;
for (int j = 0; j < batchList.size(); j++) {
batch = batchList.get(j);
System.out.println("于"
+sdf.format(batch.getCreatetime())+"采购了批次号为"
+batch.getNumber()+"的一批理财产品，详情如下：");
//3.获取一个批次的明细
List<BatchDetail> batchDetails = batch.getBatchDetials();
BatchDetail batchDetail = null;
FinacialProduct finacialProduct = null;
for (int k = 0; k < batchDetails.size(); k++) {
batchDetail = batchDetails.get(k);
System.out.println("id为"+batchDetail.getProduct_id()
+"的理财产品"+batchDetail.getProduct_num()+"份。");
//4.获取每个批次明细中的理财产品详细信息
finacialProduct = batchDetail.getFinacialProduct();
System.out.println("该理财产品的详细信息为：\n"
+"产品名称:"+finacialProduct.getName()
+"|产品价格:"+finacialProduct.getPrice()
+"|产品简介:"+finacialProduct.getDetail());
}
}
System.out.println("**************************************");
} }
sqlSession.close();
}
实测结果：
DEBUG [main] - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@7a187f14]
DEBUG [main] - ==> Preparing: SELECT BATCH.*, CUSTOMER.username, CUSTOMER.acno, BATCHDETAIL.product_id,
BATCHDETAIL.product_num, FINACIAL_PRODUCTS.name, FINACIAL_PRODUCTS.detail, FINACIAL_PRODUCTS.price FROM BATCH,
CUSTOMER, BATCHDETAIL, FINACIAL_PRODUCTS WHERE BATCH.cus_id = CUSTOMER.cus_id AND BATCHDETAIL.batch_id=
BATCH.batch_id AND FINACIAL_PRODUCTS.product_id=BATCHDETAIL.product_id;
DEBUG [main] - ==> Parameters:
DEBUG [main] - <== Total: 2
卡号为622848的名为Mr的客户:
于2018-10-04 16:47:41采购了批次号为牛奶的一批理财产品，详情如下：
id为1的理财产品100份。
该理财产品的详细信息为：
产品名称:花生|产品价格:12.0|产品简介:酒鬼花生
id为2的理财产品50份。
该理财产品的详细信息为：
产品名称:方便面|产品价格:2.0|产品简介:康师傅
**************************************
延迟加载
sqlMapConfig.xml
添加代码：
<settings>
<setting name="lazyLoadingEnabled" value="true"/>
<setting name="aggressiveLazyLoading" value="false"/>
</settings>
UserMapper.xml
<!-- 延迟加载的resultMap -->
<resultMap id="BatchUserLazyLoadingResultMap" type="cn.com.mybatis.po.BatchItem">
<!-- 对订单信息进行映射配置 -->
<id column="batch_id" property="batch_id"/>
<result column="cus_id" property="cus_id"/>
<result column="number" property="number"/>
<result column="createtime" property="createtime" javaType="java.util.Date"/>
<result column="note" property="note"/>
<!-- 实现延迟加载用户信息 -->
<association property="customer" javaType="cn.com.mybatis.po.Customer" select="findCustomerById" column="cus_id">
</association>
</resultMap> <select id="findBatchUserLazyLoading" resultMap="BatchUserLazyLoadingResultMap">
SELECT * FROM BATCH
</select> <select id="findCustomerById" parameterType="int" resultType="cn.com.mybatis.po.Customer">
SELECT * FROM CUSTOMER WHERE cus_id=#{id}
</select>
MyBatisTest.java
@Test
public void testFindBatchCustomerLazyLoading() throws Exception{ SqlSession sqlSession=dataConn.getSqlSession();
//调用userMapper的方法，获取所有订单信息(未加载关联的用户信息)
List<BatchItem> batchItemList=sqlSession.selectList("findBatchUserLazyLoading");
BatchItem batchItem = null;
Customer customer = null;
for (int i = 0; i < batchItemList.size(); i++) {
batchItem = batchItemList.get(i);
System.out.println("订单编号："+batchItem.getNumber());
//执行getCustomer时才会去查询用户信息，这里实现了延迟加载
customer=batchItem.getCustomer();
System.out.println("订购用户姓名:"+customer.getUsername());
}
sqlSession.close();
}
结果：
DEBUG [main] - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@7a187f14]
DEBUG [main] - ==> Preparing: SELECT * FROM BATCH
DEBUG [main] - ==> Parameters:
DEBUG [main] - <== Total: 2
订单编号：牛奶
DEBUG [main] - ==> Preparing: SELECT * FROM CUSTOMER WHERE cus_id=?
DEBUG [main] - ==> Parameters: 1(Integer)
DEBUG [main] - <== Total: 1
订购用户姓名:Mr
订单编号：方便面
订购用户姓名:Mr
DEBUG [main] - Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@7a187f14]
DEBUG [main] - Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@7a187f14]
DEBUG [main] - Returned connection 2048425748 to pool.
Mappwe动态代理
创建新的mapper文件
CustomerMapper.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="cn.com.mybatis.mapper.CustomerMapper"> <!-- 查询用户 -->
<select id="findCustomerById" parameterType="int" resultType="cn.com.mybatis.po.Customer">
SELECT * FROM CUSTOMER WHERE cus_id=#{cus_id}
</select>
<!-- 新增用户 -->
<insert id="insertCustomer" parameterType="cn.com.mybatis.po.Customer">
INSERT INTO CUSTOMER(username,acno,gender,phone)
value(#{username},#{acno},#{gender},#{phone})
</insert>
<!-- 删除用户 -->
<delete id="deleteCustomer" parameterType="java.lang.Integer">
DELETE FROM CUSTOMER WHERE cus_id=#{cus_id}
</delete>
<!-- 修改用户 -->
<update id="updateCustomerAcNo" parameterType="cn.com.mybatis.po.Customer" >
UPDATE CUSTOMER SET acno = #{acno} WHERE cus_id=#{cus_id}
</update> </mapper>
注意：引入文件除非使用的是package方法
SqlMapConfig.xml
<mappers>
<mapper resource="sqlmap/UserMapper.xml"/>
<mapper resource="sqlmap/CustomerMapper.xml"/>
</mappers>
新建包以及接口
CustomerMapper.java
public interface CustomerMapper {
//根据Id查询用户信息
public Customer findCustomerById(int id) throws Exception;
//添加用户信息
public void insertCustomer(Customer customer) throws Exception;
//删除用户信息
public void deleteCustomer(int id) throws Exception;
//修改用户信息
public void updateCustomerAcNo(Customer customer) throws Exception;
}
MyBatisTest.java
//动态代理
@Test
public void testFindCustomerOnMapper() throws Exception{
SqlSession sqlSession=dataConn.getSqlSession();
//获取Mapper代理
CustomerMapper customerMapper=sqlSession.getMapper(CustomerMapper.class);
//执行Mapper代理对象的查询方法
Customer customer=customerMapper.findCustomerById(1);
System.out.println("用户姓名："+customer.getUsername()+"|"
+"卡号："+customer.getAcno());
sqlSession.close();
}
结果：
DEBUG [main] - Created connection 331510866.
DEBUG [main] - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@13c27452]
DEBUG [main] - ==> Preparing: SELECT * FROM CUSTOMER WHERE cus_id=?
DEBUG [main] - ==> Parameters: 1(Integer)
DEBUG [main] - <== Total: 1
用户姓名：Mr|卡号：622848
DEBUG [main] - Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@13c27452]
DEBUG [main] - Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@13c27452]
DEBUG [main] - Returned connection 331510866 to pool.