SpringMVC（16）：使用springmvc+spring+jdbc 优化订单管理系统的示例（多条件查询供应商列表功能实现）

2018/1/20

功能：多条件查询供应商列表功能，通过供应商的名称或者供应商编码过滤；

【0】文件架构和jar包

图1

图2

【1】数据库记录

【2】前台信息

在sys.sjp添加代码：

	<h4>-----------------请在下面的表格填写“供应商查询条件”内容：-------------</h4>
	<form action="${pageContext.request.contextPath}/usera/providerlist.html"
			name="actionForm" id="actionForm" method="post">
		供应商编码 ：<input type="text" name="queryProviderCode" value=""/>
		供应商名称：<input type="text" name="queryProviderName" value=""/>
		<br>
		<input type="submit" name="查询"/>
		<input type="reset" value="重置"/>
	</form>

页面效果:

【3】代码分层：

处理器方法providerController.java添加代码：

package com.Provider.Controller;

import java.sql.SQLException;
import java.util.List;

import javax.validation.Valid;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.validation.BindingResult;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;

import com.Provider.Service.ProviderService;
import com.Provider.entities.Provider;
import com.User.Controller.UserController;

@Controller
@RequestMapping("/usera")
public class ProviderController {
	private Logger log = Logger.getLogger(UserController.class.getName());

	@Autowired
	private ProviderService providerService;
	
	@RequestMapping("/providerlist.html")
	public String getProviderList(Model model,
						@RequestParam(name="queryProviderCode",required=false)String queryProviderCode,
						@RequestParam(name="queryProviderName",required=false)String queryProviderName) throws SQLException{
		log.info("getUserList ====> queryUserName: "+queryProviderCode);
		log.info("getUserList ====> queryUserRole: "+queryProviderName);
		
		List<Provider> providerlist = null;
		
		if(queryProviderCode == null){
			queryProviderCode = "";
		}
		if(queryProviderName == null){
			queryProviderName = "";
		}
		
		providerlist = providerService.getProviderList(queryProviderCode,queryProviderName);
		model.addAttribute("prolist",providerlist);
		return "providerlist";
	}
}

解释：1.暂未加入分页处理，后续跟进；

Service层：ProviderService.java接口与实现类ProviderServiceImpl.java添加代码：

package com.Provider.Service;

import java.sql.SQLException;
import java.util.List;

import com.Provider.entities.Provider;

public interface ProviderService {
	public List<Provider> getProviderList(String queryProviderCode,String queryProviderName) throws SQLException;

	public boolean save(Provider provider) throws SQLException;
}

package com.Provider.Service;

import java.sql.SQLException;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.Provider.Dao.ProviderDao;
import com.Provider.entities.Provider;

@Service("providerService")
public class ProviderServiceImpl implements ProviderService {
	@Autowired
	ProviderDao providerDao;
	
	@Override
	public List<Provider> getProviderList(String queryProviderCode, String queryProviderName) throws SQLException {
		return providerDao.getProviderList(queryProviderCode,queryProviderName);
	}
}
	

Dao层：ProviderDao.java接口与实现类ProviderDaoImpl.java添加代码：

package com.Provider.Dao;

import java.sql.SQLException;
import java.util.List;

import com.Provider.entities.Provider;

public interface ProviderDao {

	List<Provider> getProviderList(String queryProviderCode, String queryProviderName) throws SQLException;

	public int save(Provider provider) throws SQLException;

}

package com.Provider.Dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;
import org.springframework.stereotype.Component;

import com.Provider.entities.Provider;
import com.User.Controller.UserController;

@Component("providerDao")
public class ProviderDaoImpl implements ProviderDao {
	private Connection conn = null;
	private Statement stmt = null;
	private ResultSet rs = null;
	private String sql;
	
	@Override
	public List<Provider> getProviderList(String queryProviderCode, String queryProviderName) throws SQLException {
		List<Provider> providerlist = new ArrayList<Provider>();
		
		sql = "select proCode,proName,proContact ,proPhone,proFax ,creatiomDate  "
				+ " from smbms_provider "
				+ " where proCode  like CONCAT('%','"+queryProviderCode+"','%')" 
				+ " and proName  like CONCAT('%','"+queryProviderName+"','%');" ;
		
		System.out.println("providerdao执行的sql: "+sql);
		String driver = "com.mysql.jdbc.Driver";
		String url = "jdbc:mysql://localhost:3306/test";
		String username = "root";
		String password = "";
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url,username,password);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		stmt = conn.createStatement();
		rs = stmt.executeQuery(sql);
		while(rs.next()){
			Provider pro= new Provider();
			pro.setProCode(rs.getString("proCode"));
			pro.setProName(rs.getString("proName"));
			pro.setProContact(rs.getString("proContact"));
			pro.setProPhone(rs.getString("proPhone"));
			pro.setProFax(rs.getString("proFax"));
			pro.setCreationDate(rs.getDate("creatiomDate"));
			providerlist.add(pro);
		}
		if(stmt!=null){
			stmt.close();
		}
		if(rs!=null){
			rs.close();
		}
		return providerlist;
	}
}

【4】配置文件

spring配置文件

<?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.xsd
			http://www.springframework.org/schema/context 
        	http://www.springframework.org/schema/context/spring-context-4.0.xsd
        	http://www.springframework.org/schema/aop
        	http://www.springframework.org/schema/aop/spring-aop-4.2.xsd
        ">
	<context:component-scan base-package="com.User.Dao"></context:component-scan>
	<context:component-scan base-package="com.User.Service"></context:component-scan>
	<context:component-scan base-package="com.Role.Dao"></context:component-scan>
	<context:component-scan base-package="com.Role.Service"></context:component-scan>
	<context:component-scan base-package="com.Provider.Dao"></context:component-scan>
	<context:component-scan base-package="com.Provider.Service"></context:component-scan>
</beans>

springmvc配置文件

<?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:mvc="http://www.springframework.org/schema/mvc"  
    xsi:schemaLocation="http://www.springframework.org/schema/beans 
    http://www.springframework.org/schema/beans/spring-beans.xsd  
        http://www.springframework.org/schema/context 
        http://www.springframework.org/schema/context/spring-context-4.0.xsd  
        http://www.springframework.org/schema/mvc 
        http://www.springframework.org/schema/mvc/spring-mvc-4.0.xsd">  
     <!--mvc静态资源访问   -->
	 <mvc:resources mapping="/statics/**" location="/statics/"/>
	
    <!-- 一键式配置 -->
    <context:component-scan base-package="com.User.Controller"></context:component-scan>
    <context:component-scan base-package="com.Provider.Controller"></context:component-scan>  
    <context:component-scan base-package="com.User"></context:component-scan>
    <mvc:annotation-driven></mvc:annotation-driven>  
  
    <!-- 配置视图解析器: 如何把 handler 方法返回值解析为实际的物理视图 -->  
    <!--prefix 前缀+suffix 后缀  -->  
    <bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">  
        <property name="prefix" value="/WEB-INF/jsp/"></property>  
        <property name="suffix" value=".jsp"></property>  
    </bean>         
  	
  	<!-- 全局异常处理 -->
  	<!--  -->
  	<bean class="org.springframework.web.servlet.handler.SimpleMappingExceptionResolver">
  		<property name="exceptionMappings">
  			<props>
  				<prop key="java.lang.RuntimeException">login</prop>
  			</props>
  		</property>
  	</bean>  	 
</beans> 

web.xml配置文件

<?xml version="1.0" encoding="UTF-8"?>  
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  
    xmlns="http://java.sun.com/xml/ns/javaee"  
    xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"  
    id="WebApp_ID" version="2.5">  
  
      <!-- 这个指定了log4j.xml放置的目录 -->
    <context-param>
		<param-name>log4jConfigLocation</param-name>
		<param-value>classpath:log4j.properties</param-value> 
	</context-param>
	 <!-- 一定要加上这个listener -->
	<listener>  
	    <listener-class>  
	        org.springframework.web.util.Log4jConfigListener 
	    </listener-class>  
	</listener> 
  
    <!-- 配置 DispatcherServlet -->
    <servlet>  
        <servlet-name>springmvc</servlet-name>  
        <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>  
        <init-param>
            <param-name>contextConfigLocation</param-name>  
            <param-value>classpath:springmvc-servlet.xml</param-value>  
        </init-param>  
        <!--容器启动时就被加载了    -->  
        <load-on-startup>1</load-on-startup>  
    </servlet>  
    <servlet-mapping>  
        <servlet-name>springmvc</servlet-name>  
        <url-pattern>/</url-pattern>  
    </servlet-mapping>  
       	
   	<filter>
		<filter-name>springUtf8Encoding</filter-name>
		<filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class>
		<init-param>
			<param-name>encoding</param-name>
			<param-value>UTF-8</param-value>
		</init-param>
		<init-param>
			<param-name>forceEncoding</param-name>
			<param-value>true</param-value>
		</init-param>
	</filter>
	<filter-mapping>
		<filter-name>springUtf8Encoding</filter-name>
		<url-pattern>/*</url-pattern>
	</filter-mapping>

    <welcome-file-list>
    	<welcome-file>/WEB-INF/jsp/login.jsp</welcome-file>
    </welcome-file-list>
    
    <context-param>
    	<param-name>contextConfigLocation</param-name>
    	<param-value>classpath:applicationContext-*.xml</param-value>
    </context-param>
    <!-- 配置Spring的ContextLoaderListener监听器，初始化spring容器 -->
    <listener>
    	<listener-class>
    	 	org.springframework.web.context.ContextLoaderListener
    	</listener-class>
    </listener>

</web-app>  

【5】结果显示页面：

新建providerlist.jsp，添加：

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>    
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>
	<h4>这是providerlist，负责展示供应商信息！</h4>
    <c:forEach items="${prolist}" var="arr">
        <c:out value="${arr.proCode}"></c:out>
        <c:out value="${arr.proName}"></c:out>
        <c:out value="${arr.proFax}"></c:out>
        <c:out value="${arr.proPhone}"></c:out>
        <c:out value="${arr.proContact}"></c:out>
        <c:out value="${arr.creationDate}"></c:out>
        <br>
    </c:forEach> 
</body>
</html>

【6】结果显示：

1、有条件：

2、无条件：