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

于 2018-01-02 21:12:38 发布
阅读量5.8k 收藏 13
点赞数 4
分类专栏： Spring+SpringMVC+Mybatis，项目实战 Spring/MVC/Mybatis
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_29166327/article/details/78950754
版权

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;//userId
	private Date creationDate;
	private Integer modifyBy;//userId
	private 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;
	}
	@Override
	public 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 mapper
 PUBLIC "-//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 p
		  where 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/beans
        http://www.springframework.org/schema/beans/spring-beans-4.2.xsd
        http://www.springframework.org/schema/aop
        http://www.springframework.org/schema/aop/spring-aop-4.2.xsd
        http://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;
	
	@Override
	public List<Bill> findBillsWithConditions() {
		return billMapper.billShow();
	}

	@Override
	public 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 {

	@Test
	public 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）》

 

欢迎扫二维码关注公众号，获取技术干货

 

后台技术汇
关注
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值