Spring（16）：新增功能：在超市订单系统中实现订单表的查询（采用MapperFactoryBean）

本文主要是介绍Spring（16）：新增功能：在超市订单系统中实现订单表的查询（采用MapperFactoryBean），希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

2018/1/2

功能描述：

（1）实现按条件查询订单表Bill，查询条件如下：

1 商品名称 2 供应商 3 是否付款

（2）结果列显示如下：

订单编码、商品名称、供应商名称、账单金额、是否付款、创建时间

（3）使用resultMap做显示列表字段的自定义映射，

（4）采用MapperFactoryBean；

以下是解决思路和过程；

【0】文件框架和jar包：

图1

图2

【1】新功能肯定有新的实体类，在\com\smbms\entities包下，新建Bill.java：

（留意 providerName 属性，为什么会出现呢？在下面的传参步骤【6】讲到）

package com.smbms.entities;import java.util.Date;public class Bill {private Integer id;private String billCode;private String produceName;private String productDesc;private String productUnit;private Integer productCount;private Integer totalPrice ;private Integer isPayment ;private Integer providerId;private Integer createBy;//userIdprivate Date creationDate;private Integer modifyBy;//userIdprivate Date modifyDate;private String providerName;public String getProviderName() {return providerName;}public void setProviderName(String providerName) {this.providerName = providerName;}public Bill() {}public Bill(Integer id, String billCode, String produceName, String productDesc, String productUnit,Integer productCount, Integer totalPrice, Integer isPayment, Integer providerId, Integer createBy,Date creationDate, Integer modifyBy, Date modifyDate) {super();this.id = id;this.billCode = billCode;this.produceName = produceName;this.productDesc = productDesc;this.productUnit = productUnit;this.productCount = productCount;this.totalPrice = totalPrice;this.isPayment = isPayment;this.providerId = providerId;this.createBy = createBy;this.creationDate = creationDate;this.modifyBy = modifyBy;this.modifyDate = modifyDate;}public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getBillCode() {return billCode;}public void setBillCode(String billCode) {this.billCode = billCode;}public String getProductDesc() {return productDesc;}public void setProductDesc(String productDesc) {this.productDesc = productDesc;}public String getProductUnit() {return productUnit;}public void setProductUnit(String productUnit) {this.productUnit = productUnit;}public Integer getProductCount() {return productCount;}public void setProductCount(Integer productCount) {this.productCount = productCount;}public Integer getTotalPrice() {return totalPrice;}public void setTotalPrice(Integer totalPrice) {this.totalPrice = totalPrice;}public Integer getIsPayment() {return isPayment;}public void setIsPayment(Integer isPayment) {this.isPayment = isPayment;}public Integer getProviderId() {return providerId;}public void setProviderId(Integer providerId) {this.providerId = providerId;}public Integer getCreateBy() {return createBy;}public void setCreateBy(Integer createBy) {this.createBy = createBy;}public Date getCreationDate() {return creationDate;}public void setCreationDate(Date creationDate) {this.creationDate = creationDate;}public Integer getModifyBy() {return modifyBy;}public void setModifyBy(Integer modifyBy) {this.modifyBy = modifyBy;}public Date getModifyDate() {return modifyDate;}public void setModifyDate(Date modifyDate) {this.modifyDate = modifyDate;}@Overridepublic String toString() {return "Bill [id=" + id + ", billCode=" + billCode + ", produceName=" + produceName + ", productDesc="+ productDesc + ", productUnit=" + productUnit + ", productCount=" + productCount + ", totalPrice="+ totalPrice + ", isPayment=" + isPayment + ", providerId=" + providerId + ", createBy=" + createBy+ ", creationDate=" + creationDate + ", modifyBy=" + modifyBy + ", modifyDate=" + modifyDate + "]";}public String getProduceName() {return produceName;}public void setProduceName(String produceName) {this.produceName = produceName;}}

【2】在\com\smbms\dao 包，新建接口文件 BillMapper.java：

package com.smbms.dao;import java.util.List;
import java.util.Map;import com.smbms.entities.Bill;public interface BillMapper {public List<Bill> billShow();
//	public List<Bill> billShowByCondition(Map<String,String> billMap);//注释掉这行public List<Bill> billShowByCondition(Bill bill);
}

【3】在\com\smbms\dao 包，新建sql映射文件 BillMapper.xml：

（使用select 标签，以及resultMap标签）

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.smbms.dao.BillMapper"><select id="billShow" resultType="com.smbms.entities.Bill">select * from smbms_bill</select><select id="billShowByCondition" resultMap="resultSet"parameterType="com.smbms.entities.Bill">select b.billCode,b.produceName,p.proName,b.totalPrice,b.isPayment,b.creationDate from smbms_bill b,smbms_provider pwhere b.produceName=#{produceName} and b.providerId=#{providerId}and b.isPayment=#{isPayment}and b.providerId=p.id</select><resultMap type="com.smbms.entities.Bill" id="resultSet"><result property="billCode" column="billCode"/><result property="produceName" column="produceName"/>  <result property="totalPrice" column="totalPrice"/>  <result property="isPayment" column="isPayment"/>  <result property="creationDate" column="creationDate"/>  </resultMap>
</mapper>

【4】因为使用了MapperFactoryBean，故而不需要添加接口文件 BillMapper.java的实现类，只需要简单的在Spring配置文件添加几行配置，

（当然前提是将数据源、sqlSessionfactory也配置好了）：

配置文件 applicationContext-mybatis.xml，路径在src源文件下：

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xmlns:context="http://www.springframework.org/schema/context"xmlns:aop="http://www.springframework.org/schema/aop"xmlns:tx="http://www.springframework.org/schema/tx"xmlns:p="http://www.springframework.org/schema/p"xsi:schemaLocation="http://www.springframework.org/schema/beanshttp://www.springframework.org/schema/beans/spring-beans-4.2.xsdhttp://www.springframework.org/schema/aophttp://www.springframework.org/schema/aop/spring-aop-4.2.xsdhttp://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-2.5.xsd"><!-- 数据源配置 --><bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"><property name="driverClassName" value="com.mysql.jdbc.Driver"></property><property name="url"><value><![CDATA[jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf-8]]></value></property><property name="username" value="root"></property><property name="password" value=""></property>    </bean><!-- SqlSessionFactoryBean 配置 --><bean id="sqlSessionFactory"class="org.mybatis.spring.SqlSessionFactoryBean"><!-- 引用数据源组件 --><property name="dataSource" ref="dataSource"></property><!-- 引用Mybatis配置文件的配置 --><property name="configLocation" value="classpath:mybatis-config.xml"></property></bean><!-- 配置DAO-bill，使用 MapperFactoryBean 注册映射器实现 --><bean id="billMapper" class="org.mybatis.spring.mapper.MapperFactoryBean"><property name="mapperInterface" value="com.smbms.dao.BillMapper"></property><property name="sqlSessionFactory" ref="sqlSessionFactory"></property></bean>	</beans>

以上，DAO层的实现由配置文件的Bean标签实现了，从而可以被Service层组装使用。

【5】Service层接口文件、实现类以及配置信息：

在\com\smbms\service包，新建接口文件BillService.java：

package com.smbms.service;import java.util.List;
import java.util.Map;import com.smbms.entities.Bill;public interface BillService {public List<Bill> findBillsWithConditions();public List<Bill> findBillsWithConditionsByThressConditions(Bill bill);}

在\com\smbms\service包，新建接口实现类BillServiceImpl.java：

package com.smbms.service;import java.util.List;
import java.util.Map;import com.smbms.dao.BillMapper;
import com.smbms.entities.Bill;public class BillServiceImpl implements BillService {private BillMapper billMapper;@Overridepublic List<Bill> findBillsWithConditions() {return billMapper.billShow();}@Overridepublic List<Bill> findBillsWithConditionsByThressConditions(Bill bill) {return billMapper.billShowByCondition(bill);}public BillServiceImpl() {}public BillServiceImpl(BillMapper billMapper) {this.billMapper = billMapper;}}

配置文件 applicationContext-mybatis.xml添加以下信息，路径在src源文件下：

    <!-- 配置bill业务Bean ,构造器注入,一定要有带参构造函数--><bean id="billService" class="com.smbms.service.BillServiceImpl"><constructor-arg name="billMapper" ref="billMapper" ></constructor-arg></bean>

【6】编写单元测试BillServiceImplTest.java：

package com.smbms.service;import java.util.List;import org.junit.Test;
import org.springframework.context.support.ClassPathXmlApplicationContext;import com.smbms.entities.Bill;public class BillServiceImplTest {@Testpublic void test() {System.out.println("start..");System.out.println("----------------1----------------");@SuppressWarnings("resource")ClassPathXmlApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext-mybatis.xml");BillService billService = (BillService)ctx.getBean("billService");List<Bill> bills = billService.findBillsWithConditions();for(Bill b:bills){System.out.println(b.toString());}System.out.println("--------------2-----------");
//		HashMap<String, String> billMap = new HashMap<String,String>();
//		billMap.put("produceName", "apple");
//		billMap.put("providerId","1");
//		billMap.put("isPayment", "2");Bill bill = new Bill();bill.setProviderId(1);bill.setProduceName("apple");bill.setIsPayment(2);bills = billService.findBillsWithConditionsByThressConditions(bill);for(Bill b:bills){System.out.println(b.toString());}}}

这样，所有工作都完成了。

【7】输出结果：

DEBUG 01-02 20:54:44,099 JDBC Connection [jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf-8, UserName=root@localhost, MySQL Connector Java] will not be managed by Spring  (JakartaCommonsLoggingImpl.java:54) 
DEBUG 01-02 20:54:44,132 ==>  Preparing: select * from smbms_bill   (JakartaCommonsLoggingImpl.java:54) 
DEBUG 01-02 20:54:44,212 ==> Parameters:   (JakartaCommonsLoggingImpl.java:54) 
DEBUG 01-02 20:54:44,741 <==    Columns: id, billCode, produceName, produceDesc, produceUint, produceCount, totalPrice, isPayment, createBy, creationDate, modifyBy, modifyDate, providerId  (JakartaCommonsLoggingImpl.java:59) 
DEBUG 01-02 20:54:44,742 <==        Row: 1, 001, apple, fruit, individual, 100.00, 100.00, 2, 1, 2018-01-02 11:09:20.0, null, null, 1  (JakartaCommonsLoggingImpl.java:59) 
DEBUG 01-02 20:54:44,769 <==        Row: 2, 002, banana, fruit, individual, 200.00, 100.00, 2, 1, 2018-01-02 11:26:10.0, null, null, 1  (JakartaCommonsLoggingImpl.java:59) 
DEBUG 01-02 20:54:44,770 <==      Total: 2  (JakartaCommonsLoggingImpl.java:54) 
DEBUG 01-02 20:54:44,773 Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5c33f1a9]  (JakartaCommonsLoggingImpl.java:54) 
DEBUG 01-02 20:54:44,774 Returning JDBC Connection to DataSource  (DataSourceUtils.java:327) 
Bill [id=1, billCode=001, produceName=apple, productDesc=null, productUnit=null, productCount=null, totalPrice=100, isPayment=2, providerId=1, createBy=1, creationDate=Tue Jan 02 11:09:20 CST 2018, modifyBy=null, modifyDate=null]
Bill [id=2, billCode=002, produceName=banana, productDesc=null, productUnit=null, productCount=null, totalPrice=100, isPayment=2, providerId=1, createBy=1, creationDate=Tue Jan 02 11:26:10 CST 2018, modifyBy=null, modifyDate=null]
--------------2-----------
DEBUG 01-02 20:54:44,775 Creating a new SqlSession  (JakartaCommonsLoggingImpl.java:54) 
DEBUG 01-02 20:54:44,775 SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@37883b97] was not registered for synchronization because synchronization is not active  (JakartaCommonsLoggingImpl.java:54) 
DEBUG 01-02 20:54:44,776 Fetching JDBC Connection from DataSource  (DataSourceUtils.java:110) 
DEBUG 01-02 20:54:44,777 JDBC Connection [jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf-8, UserName=root@localhost, MySQL Connector Java] will not be managed by Spring  (JakartaCommonsLoggingImpl.java:54) 
DEBUG 01-02 20:54:44,777 ==>  Preparing: select b.billCode,b.produceName,p.proName,b.totalPrice,b.isPayment,b.creationDate from smbms_bill b,smbms_provider p where b.produceName=? and b.providerId=? and b.isPayment=? and b.providerId=p.id   (JakartaCommonsLoggingImpl.java:54) 
DEBUG 01-02 20:54:44,778 ==> Parameters: apple(String), 1(Integer), 2(Integer)  (JakartaCommonsLoggingImpl.java:54) 
DEBUG 01-02 20:54:44,870 <==    Columns: billCode, produceName, proName, totalPrice, isPayment, creationDate  (JakartaCommonsLoggingImpl.java:59) 
DEBUG 01-02 20:54:44,872 <==        Row: 001, apple, JingDong, 100.00, 2, 2018-01-02 11:09:20.0  (JakartaCommonsLoggingImpl.java:59) 
DEBUG 01-02 20:54:44,874 <==      Total: 1  (JakartaCommonsLoggingImpl.java:54) 
DEBUG 01-02 20:54:44,875 Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@37883b97]  (JakartaCommonsLoggingImpl.java:54) 
DEBUG 01-02 20:54:44,875 Returning JDBC Connection to DataSource  (DataSourceUtils.java:327) 
Bill [id=null, billCode=001, produceName=apple, productDesc=null, productUnit=null, productCount=null, totalPrice=100, isPayment=2, providerId=null, createBy=null, creationDate=Tue Jan 02 11:09:20 CST 2018, modifyBy=null, modifyDate=null]
Row: 1, 001, apple, fruit, individual, 100.00, 100.00, 2, 1, 2018-01-02 11:09:20.0, null, null, 1  (JakartaCommonsLoggingImpl.java:59) 
DEBUG 01-02 20:54:44,769 <==        Row: 2, 002, banana, fruit, individual, 200.00, 100.00, 2, 1, 2018-01-02 11:26:10.0, null, null, 1  (JakartaCommonsLoggingImpl.java:59) 
DEBUG 01-02 20:54:44,770 <==      Total: 2  (JakartaCommonsLoggingImpl.java:54) 
DEBUG 01-02 20:54:44,773 Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5c33f1a9]  (JakartaCommonsLoggingImpl.java:54) 
DEBUG 01-02 20:54:44,774 Returning JDBC Connection to DataSource  (DataSourceUtils.java:327) 
Bill [id=1, billCode=001, produceName=apple, productDesc=null, productUnit=null, productCount=null, totalPrice=100, isPayment=2, providerId=1, createBy=1, creationDate=Tue Jan 02 11:09:20 CST 2018, modifyBy=null, modifyDate=null]
Bill [id=2, billCode=002, produceName=banana, productDesc=null, productUnit=null, productCount=null, totalPrice=100, isPayment=2, providerId=1, createBy=1, creationDate=Tue Jan 02 11:26:10 CST 2018, modifyBy=null, modifyDate=null]
--------------2-----------
DEBUG 01-02 20:54:44,775 Creating a new SqlSession  (JakartaCommonsLoggingImpl.java:54) 
DEBUG 01-02 20:54:44,775 SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@37883b97] was not registered for synchronization because synchronization is not active  (JakartaCommonsLoggingImpl.java:54) 
DEBUG 01-02 20:54:44,776 Fetching JDBC Connection from DataSource  (DataSourceUtils.java:110) 
DEBUG 01-02 20:54:44,777 JDBC Connection [jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf-8, UserName=root@localhost, MySQL Connector Java] will not be managed by Spring  (JakartaCommonsLoggingImpl.java:54) 
DEBUG 01-02 20:54:44,777 ==>  Preparing: select b.billCode,b.produceName,p.proName,b.totalPrice,b.isPayment,b.creationDate from smbms_bill b,smbms_provider p where b.produceName=? and b.providerId=? and b.isPayment=? and b.providerId=p.id   (JakartaCommonsLoggingImpl.java:54) 
DEBUG 01-02 20:54:44,778 ==> Parameters: apple(String), 1(Integer), 2(Integer)  (JakartaCommonsLoggingImpl.java:54) 
DEBUG 01-02 20:54:44,870 <==    Columns: billCode, produceName, proName, totalPrice, isPayment, creationDate  (JakartaCommonsLoggingImpl.java:59) 
DEBUG 01-02 20:54:44,872 <==        Row: 001, apple, JingDong, 100.00, 2, 2018-01-02 11:09:20.0  (JakartaCommonsLoggingImpl.java:59) 
DEBUG 01-02 20:54:44,874 <==      Total: 1  (JakartaCommonsLoggingImpl.java:54) 
DEBUG 01-02 20:54:44,875 Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@37883b97]  (JakartaCommonsLoggingImpl.java:54) 
DEBUG 01-02 20:54:44,875 Returning JDBC Connection to DataSource  (DataSourceUtils.java:327) 
Bill [id=null, billCode=001, produceName=apple, productDesc=null, productUnit=null, productCount=null, totalPrice=100, isPayment=2, providerId=null, createBy=null, creationDate=Tue Jan 02 11:09:20 CST 2018, modifyBy=null, modifyDate=null]

【8】要点解释：

1、在上面的代码可以看到，我还试了一下Map作为传参，这个也是可以的。

2、但我最后选择使用Bill对象传参，这样的好处是在返回值时，直接对应了设定的对象，例如数据库的smbms_bill表并没有peoviderName这一字段，但是返回值利用resultMap，将另一个表的column 字段'providerName' 对应了Bill对象的peoviderName参数。方便最后的显示打印。

【9】工程说明.txt：

（1）这是Mybatis和Spring整合练习的工程；
（2）实际使用有3个包：dao、entities、service；
（3）Mybatis配置文件内容很简洁，Spring完成大部分配置管理；
（4）通过SQLSessionTemplate的实现类对数据库进行操作；
（5）配置DAO组件并进入SqlSessionTemplate实例；
（6）配置业务Bean并注入DAO实例 ；（7）完成的功能是：查询provider的列表，模糊查询供应商信息；
（8）与smbms05MybatisSpring的区别：去掉了实现类ProviderMapperImpl；仅仅保留ProviderMapper
接口和相关SQL映射文件，通过MapperFactoryBean注入给业务组件。(9)此外，也新增了一个User查询，照壶画瓢；
(10)smbms06是使用MapperFactoryBean注入映射器，而smbms07是更加方便的使用MapperScannerConfigurer注入映射器。（11）smbms08增加新功能，实现按条件查询订单表Bill；使用resultMap做显示列表字段的自定义映射，使用Map传参，采用MapperFactoryBean注册映射器实现。[bill 和 provider 表]

此外，可以使用采用MapperScannerConfigurer映射器改进，可以参考下一篇博文：《Spring（17）：新增功能：在超市订单系统中实现订单表的查询（采用MapperScannerConfigurer）》