Spring（13）：新增功能（SqlSessionTemplate方案）：超市订单的供应商表查询 and 根据供应商名称查询供应商信息--功能实现（上）-CSDN博客

本文链接：https://blog.csdn.net/qq_29166327/article/details/78943953

2017/12/31

功能：查询provider相关信息。

【1】文件结构和jar包依赖

图1

图2

【2】新建ProviderMapper.java 接口文件：

package com.smbms.dao;

import java.util.List;

import com.smbms.entities.Provider;

public interface ProviderMapper {
	public List<Provider> providershow();
	public List<Provider> providershowbyAddress(String address);
}

解释：定义了两个方法：分别是有条件的和无条件查询；

【3】新建ProviderMapper.xml SQL映射文件：

<?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.ProviderMapper">
	<select id="providershow" resultType="com.smbms.entities.Provider">
		select * from smbms_provider
	</select>
	
	<select id="providershowbyAddress" resultType="com.smbms.entities.Provider"
	     parameterType="string">
	     select * from smbms_provider where proAddress like CONCAT('%',#{address},'%')
	</select>
	
</mapper>

解释：sql语句的定义；

【4】新建Provider.java 实体类：

package com.smbms.entities;

import java.util.Date;

public class Provider {
	private Integer id;
	private String proCode;
	private String proName;
	private String proDesc;
	private String proContact;
	private String proPhone;
	private String proFax;
	private Integer createBy;
	private Date creationDate;
	private Date modifyDate;
	private Integer modifyBy;
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getProCode() {
		return proCode;
	}
	public void setProCode(String proCode) {
		this.proCode = proCode;
	}
	public String getProName() {
		return proName;
	}
	public void setProName(String proName) {
		this.proName = proName;
	}
	public String getProDesc() {
		return proDesc;
	}
	public void setProDesc(String proDesc) {
		this.proDesc = proDesc;
	}
	public String getProContact() {
		return proContact;
	}
	public void setProContact(String proContact) {
		this.proContact = proContact;
	}
	public String getProPhone() {
		return proPhone;
	}
	public void setProPhone(String proPhone) {
		this.proPhone = proPhone;
	}
	public String getProFax() {
		return proFax;
	}
	public void setProFax(String proFax) {
		this.proFax = proFax;
	}
	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 Date getModifyDate() {
		return modifyDate;
	}
	public void setModifyDate(Date modifyDate) {
		this.modifyDate = modifyDate;
	}
	public Integer getModifyBy() {
		return modifyBy;
	}
	public void setModifyBy(Integer modifyBy) {
		this.modifyBy = modifyBy;
	}
	@Override
	public String toString() {
		return "Provider [id=" + id + ", proCode=" + proCode + ", proName=" + proName + ", proDesc=" + proDesc
				+ ", proContact=" + proContact + ", proPhone=" + proPhone + ", proFax=" + proFax + ", createBy="
				+ createBy + ", creationDate=" + creationDate + ", modifyDate=" + modifyDate + ", modifyBy=" + modifyBy
				+ "]";
	}
	public Provider(Integer id, String proCode, String proName, String proDesc, String proContact, String proPhone,
			String proFax, Integer createBy, Date creationDate, Date modifyDate, Integer modifyBy) {
		super();
		this.id = id;
		this.proCode = proCode;
		this.proName = proName;
		this.proDesc = proDesc;
		this.proContact = proContact;
		this.proPhone = proPhone;
		this.proFax = proFax;
		this.createBy = createBy;
		this.creationDate = creationDate;
		this.modifyDate = modifyDate;
		this.modifyBy = modifyBy;
	}
	public Provider() {
		super();
		// TODO 自动生成的构造函数存根
	}
	
}

解释：这里的属性定义要跟MySQL的表字段相对应；

【5】对于接口ProviderMapper.java 新建实现类 ProviderMapperImpl.java：

package com.smbms.service;

import java.util.List;

import org.mybatis.spring.SqlSessionTemplate;

import com.smbms.dao.ProviderMapper;
import com.smbms.entities.Provider;

public class ProviderMapperImpl implements ProviderMapper {
	private SqlSessionTemplate sqlSession;
	@Override
	public List<Provider> providershow() {
		return sqlSession.selectList("com.smbms.dao.ProviderMapper.providershow");
	}
	
	@Override
	public List<Provider> providershowbyAddress(String address) {
		return sqlSession.selectList("com.smbms.dao.ProviderMapper.providershowbyAddress",address);
	}


	public ProviderMapperImpl(SqlSessionTemplate sqlSession) {
		super();
		this.sqlSession = sqlSession;
	}
	public ProviderMapperImpl() {}
	
	public SqlSessionTemplate getSqlSession() {
		return sqlSession;
	}
	public void setSqlSession(SqlSessionTemplate sqlSession) {
		this.sqlSession = sqlSession;
	}
	
}

解释：（1）里面通过 SqlSessionTemplate 进行sql语句调用，这里使用的是采用字符串来指定映射项，比较容易产生错误，如果发生变化，只能等到运行时候才能发现；

（2）这个文件属于DAO层，所以是为了被业务类调用存在的；

（3）这个是通过bean的<constructor-arg/>配置实现的，注意构造函数要写上去！！

【6】新建业务类接口 ProviderService.java 和实现类 ProviderServiceImpl.java：

ProviderService.java ：

package com.smbms.service;

import java.util.List;

import com.smbms.entities.Provider;

public interface ProviderService {
	public List<Provider> findUsersWithConditions();
	public List<Provider> findUsersWithConditionsByAddress(String address);
}

ProviderServiceImpl.java：

package com.smbms.service;

import java.util.List;

import com.smbms.entities.Provider;

public class ProviderServiceImpl implements ProviderService{
	ProviderMapperImpl providerMapper = new ProviderMapperImpl();
	@Override
	public List<Provider> findUsersWithConditions() {
		List<Provider> provider = providerMapper.providershow();
		return provider;
	}
	
	@Override
	public List<Provider> findUsersWithConditionsByAddress(String address) {
		List<Provider> provider = providerMapper.providershowbyAddress(address);
		return provider;
	}
	
	public ProviderServiceImpl() {}
	public ProviderServiceImpl(ProviderMapperImpl providerMapper) {
		this.providerMapper = providerMapper;
	}

	public ProviderMapperImpl getProviderMapper() {
		return providerMapper;
	}

	public void setProviderMapper(ProviderMapperImpl providerMapper) {
		this.providerMapper = providerMapper;
	}
	
}

解释：（1）观察一下就能发现，在这业务类文件，其实是对 providerMapper.providershow() 方法进行一层封装了，返回值也是返回方法的执行结果；

（2）然后注意传参；

（3）这个是通过bean的<constructor-arg/>配置实现的，注意构造函数要写上去！！

（4）养成编写接口文件的习惯，能够使得整体结构更加完整和便于后期理解。

【7】编写测试类 ProviderServiceImplTest.java：

package com.smbms.service;

import java.util.List;

import org.junit.Test;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.smbms.entities.Provider;

public class ProviderServiceImplTest {
	ProviderServiceImpl providerService = new ProviderServiceImpl();

	@Test
	public void test() {
		System.out.println("start..");
		System.out.println("----------------1----------------");
		ClassPathXmlApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext-mybatis.xml");
		ProviderService providerService = (ProviderService) ctx.getBean("providerServiceImpl");
		List<Provider> providers = providerService.findUsersWithConditions();
		for(Provider p: providers){
			p.toString();
		}
		System.out.println("---------------2----------------");
		String address = "Maoming";
		System.out.println("------------显示供应商地点为茂名的结果------------");
		providers = providerService.findUsersWithConditionsByAddress(address);
		for(Provider p: providers){
			p.toString();
		}
	}

}

解释：单元测试，显示两条查询的结果；

【8】配置文件

（1）配置文件 applicationContext-mybatis.xml 内容：

<?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: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">
	
	<!-- 数据源配置 -->
	<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>
	<!-- 配置SQL映射文件信息 ，逐个列出SQL映射文件麻烦，所以使用 mapperLocations 属性扫描加载SQL映射文件-->
	    <property name="mapperLocations">
	    	<list>
	    		<value>classpath:com/smbms/dao/**/*.xml</value>
	    	</list>
	    </property>
    </bean>
    
    <!-- 配置SQLSessionTemplate -->
   	<bean id="sqlSessionTemplate" class="org.mybatis.spring.SqlSessionTemplate">
   		<constructor-arg name="sqlSessionFactory" ref="sqlSessionFactory"></constructor-arg>
   	</bean>
   	
   	<bean id="providerMapperImpl" class="com.smbms.service.ProviderMapperImpl">
   		<constructor-arg name="sqlSession" ref="sqlSessionTemplate"></constructor-arg>
   	</bean>
   	
   	<bean id="providerServiceImpl" class="com.smbms.service.ProviderServiceImpl">
   		<constructor-arg name="providerMapper" ref="providerMapperImpl" ></constructor-arg>
   	</bean>

</beans>

（2）mybatis-config.xml 配置文件内容：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
 PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
 "http://mybatis.org/dtd/mybatis-3-config.dtd" >
 <configuration>
 	<!-- 可以指定一个包名，MyBatis 会在包名下面搜索需要的 Java Bean，-->
 	<typeAliases>
 		<package name="com.smbms.service"/>
 	</typeAliases> 
 </configuration>

【9】输出结果：

DEBUG 01-01 11:38:21,049 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-01 11:38:21,057 ==>  Preparing: select * from smbms_provider   (JakartaCommonsLoggingImpl.java:54) 
DEBUG 01-01 11:38:21,112 ==> Parameters:   (JakartaCommonsLoggingImpl.java:54) 
DEBUG 01-01 11:38:21,637 <==    Columns: id, proAddress, proFax, createdBy, creatiomDate, modifyBy, modifyDate  (JakartaCommonsLoggingImpl.java:59) 
DEBUG 01-01 11:38:21,638 <==        Row: 1, Maoming, 0213-11, 110, 2017-12-31 00:00:00.0, null, null  (JakartaCommonsLoggingImpl.java:59) 
DEBUG 01-01 11:38:21,662 <==        Row: 2, Gaozhou, 0213-12, 110, 2017-12-31 18:32:43.0, null, null  (JakartaCommonsLoggingImpl.java:59) 
DEBUG 01-01 11:38:21,662 <==      Total: 2  (JakartaCommonsLoggingImpl.java:54) 
DEBUG 01-01 11:38:21,677 Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@3c19aaa5]  (JakartaCommonsLoggingImpl.java:54) 
DEBUG 01-01 11:38:21,677 Returning JDBC Connection to DataSource  (DataSourceUtils.java:327) 
---------------2----------------
------------显示供应商地点为茂名的结果------------
DEBUG 01-01 11:38:21,678 Creating a new SqlSession  (JakartaCommonsLoggingImpl.java:54) 
DEBUG 01-01 11:38:21,678 SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@6ff29830] was not registered for synchronization because synchronization is not active  (JakartaCommonsLoggingImpl.java:54) 
DEBUG 01-01 11:38:21,678 Fetching JDBC Connection from DataSource  (DataSourceUtils.java:110) 
DEBUG 01-01 11:38:21,679 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-01 11:38:21,679 ==>  Preparing: select * from smbms_provider where proAddress like CONCAT('%',?,'%')   (JakartaCommonsLoggingImpl.java:54) 
DEBUG 01-01 11:38:21,680 ==> Parameters: Maoming(String)  (JakartaCommonsLoggingImpl.java:54) 
DEBUG 01-01 11:38:21,759 <==    Columns: id, proAddress, proFax, createdBy, creatiomDate, modifyBy, modifyDate  (JakartaCommonsLoggingImpl.java:59) 
DEBUG 01-01 11:38:21,760 <==        Row: 1, Maoming, 0213-11, 110, 2017-12-31 00:00:00.0, null, null  (JakartaCommonsLoggingImpl.java:59) 
DEBUG 01-01 11:38:21,764 <==      Total: 1  (JakartaCommonsLoggingImpl.java:54) 
DEBUG 01-01 11:38:21,764 Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@6ff29830]  (JakartaCommonsLoggingImpl.java:54) 
DEBUG 01-01 11:38:21,765 Returning JDBC Connection to DataSource  (DataSourceUtils.java:327)

可以对比数据库内容：

图3

【10】工程说明.txt：

（1）这是Mybatis和Spring整合练习的工程；
（2）实际使用有3个包：dao、entities、service；
（3）Mybatis配置文件内容很简洁，Spring完成大部分配置管理；
（4）通过SQLSessionTemplate的实现类对数据库进行操作；
（5）配置DAO组件并进入SqlSessionTemplate实例；
（6）配置业务Bean并注入DAO实例 ；
（7）完成的功能是：查询provider的列表，模糊查询供应商信息；

下篇文章会介绍，使用注入映射器的方法，让我们的配置和使用更加简洁和方便。