2018/1/2

功能描述：

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

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

（2）结果列显示如下：

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

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

（4）采用MapperFactoryBean；

 

以下是解决思路和过程；

【0】文件框架和jar包：

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

图1

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

图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) 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)


 

【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）》

 


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

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