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

最新推荐文章于 2022-04-19 11:22:17 发布

后台技术汇

最新推荐文章于 2022-04-19 11:22:17 发布

阅读量875

点赞数

分类专栏： Spring+SpringMVC+Mybatis，项目实战 Spring/MVC/Mybatis

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

版权

Spring+SpringMVC+Mybatis，项目实战同时被 2 个专栏收录

126 篇文章 3 订阅

订阅专栏

Spring/MVC/Mybatis

83 篇文章 3 订阅

订阅专栏

18/1/16

在《SpringMVC（13）：使用springmvc优化订单管理系统的示例（登陆和注销的简单实现）》基础上，给《超市订单管理系统》增加新功能：用户管理功能（跳转所有用户查询和有条件用户查询）；

学习阶段，所以使用的框架：springmvc + spring + jdbc，增加了用户查询功能，但分页功能没完善，相关的类有UserDao、UserService；sql为有条件的连表查询；

【0】mysql 数据表的内容，按照需要将查询以下的内容：

图1

【1】文件框架和jar包（添加了role实体类/Dao/Service，同时UserService/UserDao也增加了方法）：

图2

图3

图4-jar包

【2】在登陆进入系统页面：sys.jsp后，会有用户查询和用户管理两个选项。因此，修改sys.jsp（下面列出了主要代码）：

	<h4>请在下面的表格填写内容：</h4>
	<form action="${pageContext.request.contextPath}/user/userlist.html"
			name="actionForm" id="actionForm" method="post">
		用户名 ：<input type="text" name="queryUserName" value=""/>
		用户角色(100/101/110/111) ：<input type="text" name="queryUserRole" value=""/>
		页码 ：<input type="text" name="pageIndex" value=""/>
		<br>
		<input type="submit" name="查询"/>
		<input type="reset" value="重置"/>
	</form>
	
	<br>
		<a href="${pageContext.request.contextPath}/user/userlist.html">跳转到用户管理，无查询条件.</a>

输出效果：

图5

解释：1、在输入框键入数据，点击“提交”会发送到Controller；2、直接点击超链接“跳转到用户管理，无查询条件.”则也会发送请求到Controller。

【3】对于两种请求，使用了一个处理器 @RequestMapping(value="/userlist")+private String getUserList(){} 进行处理，所以在com\User\Controller\UserController.java 添加以下内容：

	@RequestMapping(value="/userlist")
	private String getUserList(Model model,
					@RequestParam(value="queryUserName",required=false) String queryUserName , 
					@RequestParam(value="queryUserRole",required=false) String queryUserRole,
					@RequestParam(value="pageIndex",required=false) String pageIndex) throws SQLException{
		log.info("getUserList ====> queryUserName: "+queryUserName);
		log.info("getUserList ====> queryUserRole: "+queryUserRole);
		log.info("getUserList ====> pageIndex: "+pageIndex);
		int _queryUserRole = 0;
		List<User> userList = null;
		//设置页面容量
//		int pageSize = Constants.pageSize;
		int pageSize = 5;
		//设置当前页码
		int currentPageNo = 1;
		if(queryUserName == null){
			queryUserName = "";
		}
		if(queryUserRole != null && !queryUserRole.equals("")){
			_queryUserRole = Integer.parseInt(queryUserRole);
		}
		if(pageIndex != null){
			try{
				currentPageNo = Integer.valueOf(pageIndex);
			}catch(NumberFormatException e){
				return "redirect:/user/syserror.html";
			}
		}
		
		//总数量
		int totalCount = userService.getUserCount(queryUserName,_queryUserRole);
		
		//总页数***
		PageSupport pages = new PageSupport();
		pages.setCurrentPageNo(currentPageNo);
		pages.setPageSize(pageSize);
		pages.setTotalCount(totalCount);
		int totalPageCount = pages.getTotalPageCount();
		
		//控制首页和尾页
		if(currentPageNo < 1){
			currentPageNo = 1;
		}else if(currentPageNo > totalPageCount){
			currentPageNo = totalPageCount;
		}
		userList = userService.getUserList(queryUserName,_queryUserRole,currentPageNo,pageSize);
		model.addAttribute("userList", userList);
		List<Role> roleList = null;
		roleList = roleService.getRoleList();
		//rolelist
		model.addAttribute("roleList", roleList);
		//input data
		model.addAttribute("queryUserName",queryUserName);
		model.addAttribute("queryUserRole",queryUserRole);
		//underline data
		model.addAttribute("totalPageCount", totalPageCount);
		model.addAttribute("totalCount", totalCount);
		model.addAttribute("currentPageNo", currentPageNo);
		return "userlist";
	}

解释：1、由于没有完善分页功能，分页相关的变量和函数都是伪处理（即类的空函数）；

2、View 可能传递进来的三个参数都设置为了：@RequestParam(value="XXXX",required=false)；

3、使用了模型model进行传递参数（本质也是map）；

【4】在UserServiceImpl.java添加以下内容：

	@Override
	public List<User> getUserList(String queryUserName, int _queryUserRole, int currentPageNo, int pageSize) throws SQLException {
		return userDao.getUserList(queryUserName, _queryUserRole, currentPageNo, pageSize);	
	}


	@Override
	public int getUserCount(String queryUserName, int _queryUserRole) throws SQLException {
		return userDao.getUserCount(queryUserName,_queryUserRole);
	}

在接口 UserService.java添加以下内容：

	public List<User>  getUserList(String queryUserName,int _queryUserRole,
						int currentPageNo,int pageSize) throws SQLException;
	public int getUserCount(String queryUserName,int _queryUserRole) throws SQLException;

【5】在UserDaoImpl.java 添加以下内容：

	@Override
	public List<User> getUserList(String queryUserName, int _queryUserRole, int currentPageNo, int pageSize) throws SQLException {
		Logger log = Logger.getLogger(UserController.class.getName());
		List<User> userList = new ArrayList<User>() ;

		sql = "select u.userCode,u.userName,u.gender,u.birthday,u.phone,r.roleName "
				+ " from smbms_user u,smbms_role r"
				+ " where u.userName  like CONCAT('%','"+queryUserName+"','%')" 
						+ " and u.userRole="+_queryUserRole
						+ " and u.userRole=r.id;";
		
		//针对直接跳转的请求，则赋值另外一条squall语句
		if(queryUserName == ""){
		sql = "select u.userCode,u.userName,u.gender,u.birthday,u.phone,r.roleName "
					+ " from smbms_user u,smbms_role r "
					+ " where u.userRole=r.id;";
		}
		
		System.out.println("userdao执行的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()){
			User user = new User();//很重要，开辟一个新的空间存储新的值！！
			user.setUserCode(rs.getString(1));
			user.setUserName(rs.getString(2));
			user.setGender(rs.getInt(3));
			user.setBirthday(rs.getDate(4));
			user.setPhone(rs.getString(5));
			user.setRoleName(rs.getString(6));
			log.info("loop:"+user);
			userList.add(user);
			log.info("inside--result:"+userList);
		}
		log.info("outside--result:"+userList);
		if(stmt!=null){
			stmt.close();
		}
		if(rs!=null){
			rs.close();
		}
		return userList;
	}

	@Override
	public int getUserCount(String queryUserName, int _queryUserRole) throws SQLException {
		Logger log = Logger.getLogger(UserController.class.getName());
		ArrayList<User> userList = new ArrayList<User>() ;
		User user = new User();
		int count = 0;
		
		//第一种方法
//		sql = "select * from smbms_user"
//				+ " where u.userName like CONCAT('%',#{"+queryUserName+"},'%')" 
//				+ " and u.userRole="+_queryUserRole+";";
		
		//第二种方法
		sql = "select COUNT('id') from smbms_user";
		
		System.out.println("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);
		rs.next();
		count = rs.getInt(1);
		if(stmt!=null){
			stmt.close();
		}
		if(rs!=null){
			rs.close();
		}
		return count;
	}

在接口 UserDao.java 添加以下内容：

	public List<User> getUserList(String queryUserName, int _queryUserRole, int currentPageNo, int pageSize) throws SQLException;

	public int getUserCount(String queryUserName,int _queryUserRole) throws SQLException;

【6】新建包：\com\Role\entities，添加Role.java实体类:

package com.Role.entities;

import java.math.BigInteger;
import java.util.Date;

public class Role {
	private Integer id;
	private String roleCode;
	private String roleName;
	private BigInteger createdBy;
	private Date creationDate;
	private BigInteger modifyBy;
	private Date modifyDate;
	......省略setter/getter 方法，无参/有参构造方法；
}

【7】新建包\com\Role\Service，接口RoleService.java：

package com.Role.Service;

import java.sql.SQLException;
import java.util.List;
import com.Role.entities.Role;

public interface RoleService {
	public List<Role> getRoleList() throws SQLException;
}

添加实现类RoleServiceImpl.java：

package com.Role.Service;

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

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

import com.Role.Dao.RoleDao;
import com.Role.entities.Role;

@Service("roleService")
public class RoleServiceImpl implements RoleService {
	@Autowired
	private RoleDao roleDao;
	
	@Override
	public List<Role> getRoleList() throws SQLException {
		return roleDao.getRoleList();
	}
	
}

【8】新建包\com\Role\Dao，接口RoleDao.java：

package com.Role.Dao;

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

import com.Role.entities.Role;

public interface RoleDao {
	public List<Role> getRoleList() throws SQLException;
}

添加实现类RoleDaoImpl.java：

package com.Role.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.Role.entities.Role;
import com.User.Controller.UserController;
import com.User.entities.User;

@Component("roleDao")
public class RoleDaoImpl implements RoleDao {
	private Connection conn = null;
	private Statement stmt = null;
	private ResultSet rs = null;
	private String sql;

	public List<Role> getRoleList() throws SQLException {
		Logger log = Logger.getLogger(UserController.class.getName());
		ArrayList<Role> roleList = new ArrayList<Role>() ;
		Role role = new Role();

		sql = "select id,roleCode,roleName from smbms_role; ";
		System.out.println("roledao执行的sql: "+sql);
		System.out.println("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()){
			role.setId(rs.getInt(1));
			role.setRoleCode(rs.getString(2));
			role.setRoleName(rs.getString(3));
			roleList.add(role);
		}
		if(stmt!=null){
			stmt.close();
		}
		if(rs!=null){
			rs.close();
		}
		return roleList;
	}


}

至此，后台逻辑完成；

【9】配置文件：

1、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>

2、springmvc-servlet.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: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>  
    <mvc:annotation-driven></mvc:annotation-driven>  
  
   	<!-- 配置静态资源访问 
     <mvc:resources mapping="/resources/**" location="/resources/" />
     <mvc:resources mapping="/images/**" location="/images/" />
     <mvc:resources mapping="/js/**" location="/js/" />	-->

    <!-- 配置视图解析器: 如何把 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>

3、spring配置文件 -- applicationContext-jdbc.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: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>
</beans>

【10】输出结果：

1、无条件查询: