第五章.MyBatis高级映射
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.javapublic 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.
