SMBMS
项目搭建准备工作
1.项目架构
2.数据库设计
3.项目搭建
1、项目如何搭建？
-
是否使用maven？
- 使用maven要去网上找依赖
- 不使用maven要自己手动导jar包
为了方便，这个项目使用maven搭建
2、创建项目
- 补全maven项目结构
- 更新WEB.XML的配置版本
<web-app xmlns="http://xmlns.jcp.org/xml/ns/javaee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee
http://xmlns.jcp.org/xml/ns/javaee/web-app_4_0.xsd"
version="4.0"
metadata-complete="true">
</web-app>
-
清理pom.xml
只需要保留GAV+项目的打包方式即可
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.thhh</groupId>
<artifactId>smbms</artifactId>
<version>1.0-SNAPSHOT</version>
<packaging>war</packaging>
</project>
3、配置Tomcat
4、测试项目是否搭建完成
5、导入依赖
用什么导入什么，不一定要一次性全部导入
<dependencies>
<!--servlet依赖-->
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>servlet-api</artifactId>
<version>2.5</version>
</dependency>
<!--JSP依赖-->
<dependency>
<groupId>javax.servlet.jsp</groupId>
<artifactId>jsp-api</artifactId>
<version>2.2.1-b03</version>
</dependency>
<!--数据库连接依赖-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
<!--JSTL标签-->
<dependency>
<groupId>javax.servlet.jsp.jstl</groupId>
<artifactId>jstl-api</artifactId>
<version>1.2</version>
</dependency>
<!--JSTL标签的依赖-->
<dependency>
<groupId>taglibs</groupId>
<artifactId>standard</artifactId>
<version>1.1.2</version>
</dependency>
</dependencies>
6、创建项目包结构
-
首先创建一个总的包，命名"com.公司名"
-
在创建这个项目会使用的包
7、创建实体类/JavaBean/ORM映射
数据库中有5张表，但是地址表没有什么用处，所以我们暂时不创建地址表对用的实体类
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-OnV82GEu-1626251512221)(SMBMS.assets/1747479-20200908233229281-1248921664.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-nDgAEqyf-1626251512222)(SMBMS.assets/1747479-20200908233243766-127564630.png)]
8、编写数据库操作的基础公共类BaseDao
- 数据库配置文件，这是一个资源文件，应该创建在maven项目的resources文件中
DRIVER=com.mysql.jdbc.Driver
URL=jdbc:mysql://localhost:3306/smbms?useUnicode=true&characterEncoding=utf-8
USERNAME=root
PASSWORD=123
- 使用静态代码块实现初始化参数
private static String DRIVER;
private static String URL;
private static String USERNAME;
private static String PASSWORD;
static {//静态代码块，在调用这个类的地方优先自动执行
//读取配置文件
//1、创建properties对象
Properties properties = new Properties();
//2、通过类加载器加载资源文件为字节输入流
InputStream in = BaseDao.class.getClassLoader().getResourceAsStream("db.properties");
try {
properties.load(in);
} catch (IOException e) {
e.printStackTrace();
}
DRIVER = properties.getProperty("DRIVER");
URL = properties.getProperty("URL");
USERNAME = properties.getProperty("USERNAME");
PASSWORD = properties.getProperty("PASSWORD");
}
- 编写数据库操作的公共方法
package com.thhh.dao;
/**
* 注意理解这个类中的方法之所以要传入这些数据库操纵对象是因为为了统一的关闭资源
* 而传入的对象中可以都是null，具体的对象获取在方法里面进行；也可以只有conn实例化，其他对象的实例化同样放在具体的方法里进行
*/
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
//操作数据库的公共类
public class BaseDao {
private static String DRIVER;
private static String URL;
private static String USERNAME;
private static String PASSWORD;
//静态代码块用于初始化JDBC4大参数，且静态代码块只会在第一次调用这个类的时候执行一次
static {//静态代码块，在调用这个类的地方优先自动执行
//读取配置文件
//1、创建properties对象
Properties properties = new Properties();
//2、通过类加载器加载资源文件为字节输入流
InputStream in = BaseDao.class.getClassLoader().getResourceAsStream("db.properties");
try {
properties.load(in);
} catch (IOException e) {
e.printStackTrace();
}
DRIVER = properties.getProperty("DRIVER");
URL = properties.getProperty("URL");
USERNAME = properties.getProperty("USERNAME");
PASSWORD = properties.getProperty("PASSWORD");
}
//1、编写获取数据库的连接对象的公共方法
public static Connection getConnection(){
Connection conn= null;
try {
//1、加载驱动类
Class.forName(DRIVER);
//2、获取连接对象
conn = DriverManager.getConnection(URL,USERNAME,PASSWORD);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return conn;
}
//2、编写查询公共方法 —— 注意查询的结果返回为ResultSet结果集
/**
* 用于查询数据的公共方法，注意：使用发送SQL语句的对象为PreparedStatement
* @param sql:查询的sql语句，由前端传递
* @param params:sql语句中占位符的值
*
*===============这下面的3个参数之所以在调用的时候传递原因就在于这3个都是资源，我们需要关闭，如果我们直接在这个方法里获取资源对象的话，那么我们就应该在这个方法中关闭资源===============
*===============但是调用处还在等待这个方法返回结果集，所以我们不应该在这个地方获取这3个对象，而应该由调用出传递，这样可以统一管理和关闭资源===============
*
* @param conn：调用出使用BaseDao.getConnection()获取到数据库连接对象传入
* @param pstmt：调用出只是传入null的引用。这个对象真正的实例化放在这个方法里面
* @param rs：返回的结果集，和pstmt只是传入null的引用。这个对象真正的实例化放在这个方法里面
*
* @return：返回查询到的结果集
*/
public static ResultSet executeQuery(String sql,Object[] params,Connection conn,PreparedStatement pstmt,ResultSet rs){
try {
pstmt = conn.prepareStatement(sql);
for (int i=1;i<= params.length;i++){//循环遍历参数数组，并将参数设入SQL中
pstmt.setObject(i,params[i-1]);//注意：数组的index从0开始，而PreparedStatement中设置占位符的值的index从1开始
}
rs = pstmt.executeQuery();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return rs;
}
//3、编写修改公共方法
/**
* 用于修改数据的公共方法，注意：使用发送SQL语句的对象为PreparedStatement
* @param sql：修改数据的sql语句模板
* @param params：模板中占位符对应的值
*
* =====下面两个对象需要调用出传入也是为了统一管理和关闭资源=====
* @param conn：调用出使用BaseDao.getConnection()获取到数据库连接对象传入
* @param pstmt：调用出只是传入null的引用。这个对象真正的实例化放在这个方法里面
*
* @return 返回受影响的行数
*/
public static int executeUpdate(String sql,Object[] params,Connection conn,PreparedStatement pstmt){
int result = 0;
try {
pstmt = conn.prepareStatement(sql);
for (int i=1;i< params.length;i++){//循环遍历参数数组，并将参数设入SQL中
pstmt.setObject(i,params[i-1]);//注意：数组的index从0开始，而PreparedStatement中设置占位符的值的index从1开始
}
result = pstmt.executeUpdate();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return result;
}
//4、编写关闭资源公共方法
/**
* 关闭资源
* @param conn：调用出使用BaseDao.getConnection()获取到数据库连接对象传入
* @param pstmt：调用出只是传入null的引用。这个对象真正的实例化放在这个方法里面
* @param rs：返回的结果集，和pstmt只是传入null的引用。这个对象真正的实例化放在这个方法里面
* @return：返回关闭资源的结果
*
* 注意:关闭资源的时候要倒着关
*/
public static boolean close(Connection conn,PreparedStatement pstmt,ResultSet rs){
boolean flag = true;
if (rs!=null){
try {
rs.close();
rs = null;//让这个变量为null，gc就会自动对其进行回收
} catch (SQLException throwables) {
throwables.printStackTrace();
flag = false;//关闭失败就将flag设置false
}
}
if (pstmt!=null){
try {
pstmt.close();
pstmt = null;
} catch (SQLException throwables) {
throwables.printStackTrace();
flag = false;
}
}
if (conn!=null){
try {
conn.close();
conn = null;
} catch (SQLException throwables) {
throwables.printStackTrace();
flag = false;
}
}
return flag;//返回关闭结果
}
}
9、编写字符编码过滤器
//编写过滤器
package com.thhh.filter;
import javax.servlet.*;
import java.io.IOException;
public class CharacterEncoding implements Filter {
public void init(FilterConfig filterConfig) throws ServletException {
}
public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain) throws IOException, ServletException {
request.setCharacterEncoding("utf-8");
response.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset=utf-8");
chain.doFilter(request,response);
}
public void destroy() {
}
}
<web-app xmlns="http://xmlns.jcp.org/xml/ns/javaee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee
http://xmlns.jcp.org/xml/ns/javaee/web-app_4_0.xsd"
version="4.0"
metadata-complete="true">
<!--注册字符编码过滤器-->
<filter>
<filter-name>CharacterEncoding</filter-name>
<filter-class>com.thhh.filter.CharacterEncoding</filter-class>
</filter>
<filter-mapping>
<filter-name>CharacterEncoding</filter-name>
<url-pattern>/*</url-pattern>
</filter-mapping>
</web-app>
10、导入静态资源
包括HTML、CSS、JS等，注意：这些资源都是网站的，所以我们应该将这些资源放在webapp下面
通过以上的步骤，一个WEB项目的搭建工作就算基本完成了
BaseDao总结
package com.wang.dao;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class BaseDao {
public static String driver;
public static String url;
public static String username;
public static String password;
//静态代码块 类加载的时候 完成初始化
static {
Properties properties = new Properties();
//通过类加载器 读取流中的数据
InputStream is = BaseDao.class.getClassLoader().getResourceAsStream("db.properties");
try {
//将这个流加载到 properties中
properties.load(is);
} catch (IOException e) {
e.printStackTrace();
}
//通过properties来获得数据
driver = properties.getProperty("driver");
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
}
//获得数据库的连接
public static Connection getConnection(){
Connection connection = null;
try {
//加载驱动
Class.forName(driver);
//连接数据库
connection = DriverManager.getConnection(url,username,password);
} catch (Exception e) {
e.printStackTrace();
}
return connection;
}
//jdbc查询公共方法
public static ResultSet execute(Connection connection,ResultSet resultSet,PreparedStatement preparedStatement,String sql,Object[] params) {
//预编译
try {
preparedStatement = connection.prepareStatement(sql);
//Object[] params 为传递进来的参数
for (int i = 0; i < params.length; i++) {//循环遍历参数数组，并将参数设入SQL中
preparedStatement.setObject(i+1,params[i]);//注意：数组的index从0开始，而PreparedStatement中设置占位符的值的index从1开始
}
resultSet = preparedStatement.executeQuery();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return resultSet;
}
//jdbc删改公共方法
public static int execute(Connection connection,PreparedStatement preparedStatement,String sql,Object[] params) {
//预编译
int updateRows = 0;
try {
preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < params.length; i++) {//循环遍历参数数组，并将参数设入SQL中
preparedStatement.setObject(i+1,params[i]);//注意：数组的index从0开始，而PreparedStatement中设置占位符的值的index从1开始
}
updateRows = preparedStatement.executeUpdate();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return updateRows;
}
public static Boolean closeResource(Connection connection,ResultSet resultSet,PreparedStatement preparedStatement){
Boolean flag = true;
if(connection!=null){
try {
connection.close();
//GC回收
connection = null;
} catch (SQLException throwables) {
throwables.printStackTrace();
flag = false;
}
}
if(resultSet!=null){
try {
resultSet.close();
//GC回收
resultSet = null;
} catch (SQLException throwables) {
throwables.printStackTrace();
flag = false;
}
}
if(preparedStatement!=null){
try {
preparedStatement.close();
//GC回收
preparedStatement = null;
} catch (SQLException throwables) {
throwables.printStackTrace();
flag = false;
}
}
return flag;
}
}
db.properties
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/smbms?useSSL=true&useUnicode=true&characterEncoding=utf-8
username=root
password=123456
登录功能实现
1.编写前端页面
2.设置首页
<!-- 设置欢迎首页-->
<welcome-file-list>
<welcome-file>login.jsp</welcome-file>
</welcome-file-list>
3.编写dao层得到登录用户登录的接口
package com.wang.dao.user;
import com.wang.pojo.User;
import java.sql.Connection;
import java.sql.SQLException;
public interface UserDao {
//从数据库中获取登录用户的信息
public User getLoginUser(Connection connection, String userCode) throws SQLException;
}
4.编写dao接口的实现类
package com.wang.dao.user;
import com.wang.dao.BaseDao;
import com.wang.pojo.User;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class UserDaoImpl implements UserDao{
@Override
public User getLoginUser(Connection connection, String userCode) throws SQLException {
ResultSet rs = null;
PreparedStatement pstm = null;
User user = null;
if (connection!=null){
String sql = "select * from smbms_user where userCode=?";
Object[] params = {userCode};
//我们要使用之前 重写的方法，就需要 按照重写的方法来给参数
rs = BaseDao.execute(connection, rs, pstm, sql, params);
if (rs.next()){
user = new User();
user.setId(rs.getInt("id"));
user.setUserCode(rs.getString("userCode"));
user.setUserName(rs.getString("userName"));
user.setUserPassword(rs.getString("userPassword"));
user.setGender(rs.getInt("gender"));
user.setBirthday(rs.getDate("birthday"));
user.setPhone(rs.getString("phone"));
user.setAddress(rs.getString("address"));
user.setUserRole(rs.getInt("userRole"));
user.setCreatedBy(rs.getInt("createdBy"));
user.setCreationDate(rs.getTimestamp("creationDate"));
user.setModifyBy(rs.getInt("modifyBy"));
user.setModifyDate(rs.getTimestamp("modifyDate"));
}
BaseDao.closeResource(null,rs,pstm);
}
return user;
}
}
5.业务层接口
package com.wang.service.user;
import com.wang.pojo.User;
public interface UserService {
public User login(String userCode,String password);
}
6.业务层实现类
package com.wang.service.user;
import com.wang.dao.BaseDao;
import com.wang.dao.user.UserDao;
import com.wang.dao.user.UserDaoImpl;
import com.wang.pojo.User;
import org.junit.Test;
import java.sql.Connection;
import java.sql.SQLException;
public class UserServiceImpl implements UserService{
//业务层需要调用dao 层的方法，所以需要引入dao
private UserDao userDao;
public UserServiceImpl(){
userDao = new UserDaoImpl();
}
@Override
public User login(String userCode, String password) {
Connection connection = null;
User user = null;
try {
connection = BaseDao.getConnection();
//调用的具体方法
user = userDao.getLoginUser(connection,userCode);
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
BaseDao.closeResource(connection,null,null);
}
return user;
}
@Test
public void test(){
UserServiceImpl userService = new UserServiceImpl();
User admin = userService.login("admin", "1234567");
System.out.println(admin.getUserPassword());
}
}
7.编写servlet
package com.wang.servlet.user;
import com.wang.pojo.User;
import com.wang.service.user.UserServiceImpl;
import com.wang.util.Constants;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
public class LoginServlet extends HttpServlet {
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
System.out.println("LoginServlet---start");
String userCode = req.getParameter("userCode");
String userPassword = req.getParameter("userPassword");
//需要调用service的实现类
UserServiceImpl userService = new UserServiceImpl();
User user = userService.login(userCode, userPassword);
if (user!=null){
req.getSession().setAttribute(Constants.USER_SESSION,user);
resp.sendRedirect("/smbms/jsp/frame.jsp");
}else{
req.setAttribute("error","用户名或密码不正确");
req.getRequestDispatcher("login.jsp").forward(req,resp);
}
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
doGet(req, resp);
}
}
8.注册servlet
<servlet>
<servlet-name>LoginServlet</servlet-name>
<servlet-class>com.wang.servlet.user.LoginServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>LoginServlet</servlet-name>
<url-pattern>/login.do</url-pattern>
</servlet-mapping>
9.测试访问，确保以上功能成功！
登录功能优化
注销功能:
思路:移除Session，返回登录页面
package com.wang.servlet.user;
import com.wang.util.Constants;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
public class LoginOutServlet extends HttpServlet {
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
//用户名 与密码注销，即将session给移除即可
req.getSession().removeAttribute(Constants.USER_SESSION);
resp.sendRedirect(req.getContextPath()+"/login.jsp");
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
doGet(req, resp);
}
}
注册xml
<servlet>
<servlet-name>LoginOutServlet</servlet-name>
<servlet-class>com.wang.servlet.user.LoginOutServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>LoginOutServlet</servlet-name>
<url-pattern>/jsp/logout.do</url-pattern>
</servlet-mapping>
登录拦截优化
编写一个过滤器，并注册
package com.wang.filter;
import com.wang.pojo.User;
import com.wang.util.Constants;
import javax.servlet.*;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
public class SysFilter implements Filter {
@Override
public void init(FilterConfig filterConfig) throws ServletException {
}
@Override
public void doFilter(ServletRequest servletRequest, ServletResponse servletResponse, FilterChain filterChain) throws IOException, ServletException {
HttpServletRequest request = (HttpServletRequest)servletRequest;
HttpServletResponse response = (HttpServletResponse)servletResponse;
//过滤器，从session中获取
User user = (User) request.getSession().getAttribute(Constants.USER_SESSION);
if (user==null){
response.sendRedirect("/smbms/error.jsp");
}else {
filterChain.doFilter(request,response);
}
}
@Override
public void destroy() {
}
}
<filter>
<filter-name>SysFilter</filter-name>
<filter-class>com.wang.filter.SysFilter</filter-class>
</filter>
<filter-mapping>
<filter-name>SysFilter</filter-name>
<url-pattern>/jsp/*</url-pattern>
</filter-mapping>
密码修改
1.导入前端素材
<li><a href="${pageContext.request.contextPath }/jsp/pwdmodify.jsp">密码修改</a></li>
2.写项目建议从底层向上写
3.Dao基础设置（在项目准备的时候已经封装好了，直接调用即可）（获得properties资源 连接数据库 操作数据库增删改查）的基础代码封装
4.UserDao接口（ 面向对象编程 针对user的数据库操作）
//修改当前用户的密码
public int updatePwd(Connection connection,int id,int password) throws SQLException;
5.UserDao接口实现类（具体实现）
//修改当前用户的密码
@Override
public int updatePwd(Connection connection, int id, int password) throws SQLException {
PreparedStatement patm = null;
int execute = 0;
if (connection!=null){
String sql = "update smbms_user set userPassword=? where id = ?";
Object parma[] = {id,password};
execute = BaseDao.execute(connection, patm, sql, parma);
BaseDao.closeResource(null,null,patm);
}
return execute;
}
6.UserService层
//密码修改
public Boolean updatePwd(String userCode,String userPassword);
7.UserService实现类
@Override
public Boolean updatePwd(String userCode,String userPassword) {
Boolean flag = false;
Connection connection = null;
try {
connection = BaseDao.getConnection();
if (userDao.updatePwd(connection,userCode,userPassword)>0){
flag = true;
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
BaseDao.closeResource(connection,null,null);
}
return flag;
}
8.servlet层 记得实现复用，需要提取出方法
package com.wang.servlet.user;
import com.wang.pojo.User;
import com.wang.service.user.UserServiceImpl;
import com.wang.util.Constants;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
public class UserServlet extends HttpServlet {
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
String method = req.getParameter("method");
if("savepwd".equals(method)){
updatePwd(req,resp);
}
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
doGet(req, resp);
}
public void updatePwd(HttpServletRequest req, HttpServletResponse resp){
//从session中拿到 值
Object o = req.getSession().getAttribute(Constants.USER_SESSION);
//从前端拿到新的密码
String newpassword = req.getParameter("newpassword");
Boolean flag = false;
if (o!=null && newpassword!= null ){
System.out.println(flag);
UserServiceImpl userService = new UserServiceImpl();
flag = userService.updatePwd(newpassword,((User)o).getUserCode());
if (flag){
System.out.println(flag);
req.setAttribute("message","密码修改成功！ 请退出重新登录！");
req.getSession().removeAttribute(Constants.USER_SESSION);
}else {
req.setAttribute("message","密码修改失败！");
}
}else {
req.setAttribute("message","新密码设置有错误！");
}
try {
req.getRequestDispatcher("pwdmodify.jsp").forward(req,resp);
} catch (ServletException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
}
9.测试；
优化密码修改使用Ajax;
1.阿里巴巴的fastjson
<!-- https://mvnrepository.com/artifact/com.alibaba/fastjson -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.76</version>
</dependency>
用户管理实现
思路：
1.导入分页的工具类
2.用户列表页面导入
userlist.jsp导入
1、获取用户数量
1.UserDao
//根据用户名或角色来查询用户总数
public int getUserCount(Connection connection,String username,int userRole) throws SQLException;
2.UserDaolmpl
//根据用户名或角色来查询用户总数{该项目中最难理解的SQL}
@Override
public int getUserCount(Connection connection, String username, int userRole) throws SQLException {
ResultSet rs = null;
PreparedStatement pstm = null;
int count = 0;
if (connection!=null){//如果没有username 与 userRole 则默认查询全部的count人数，并查出具体表单来
StringBuffer sql = new StringBuffer("select count(1) as count from smbms_user u,smbms_role r where u.userRole = r.id");
ArrayList<Object> list = new ArrayList<Object>();//用来存放传递来的参数
if (!StringUtils.isNullOrEmpty(username)){//按姓名查询的那个接口
sql.append(" and u.userName like ?");
list.add("%"+username+"%");//index: 0
}
if (userRole>0){//按职责查询的那个接口
sql.append(" and u.userRole = ?");
//index: 1
list.add(userRole);//因为userRole是int 类型 故可以不添加% % 来进行模糊定义
}
//将list转化为数组
Object[] params = list.toArray();
//在这里输出完整的sql语句，便于分析 检查
System.out.println("UserDaoImpl-->getUserCount:sql"+sql.toString());
//调用BaseDao的方法查询
rs = BaseDao.execute(connection, rs, pstm, sql.toString(), params);
if (rs.next()){
count = rs.getInt("count");//从结果集中获得总数的数量
}
BaseDao.closeResource(null,rs,pstm);
}
return count;
}
3.UserService
//查询记录数
public int getUserCount(String username,int userRole);
4.UserServicelmpl
@Override
public int getUserCount(String username, int userRole) {
Connection connection =null;
int count = 0;
try {
connection = BaseDao.getConnection();
count = userDao.getUserCount(connection, username, userRole);
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
BaseDao.closeResource(connection,null,null);
}
return count;
}
@Test
public void test(){
UserServiceImpl userService = new UserServiceImpl();
int userCount = userService.getUserCount(null, 0);
System.out.println(userCount);
}
2、获取用户列表
1.UserDao
//通过条件查询-userList
public List<User> getUserList(Connection connection,String userName,int userRole,int currenPageNo,int pageSize) throws SQLException;
2.UserDaolmpl
//通过条件查询-userList
@Override
public List<User> getUserList(Connection connection, String userName, int userRole, int currenPageNo, int pageSize) throws SQLException {
PreparedStatement pstm = null;
ResultSet rs = null;
ArrayList<User> userList = new ArrayList<User>();
if (connection!=null){
StringBuffer sql = new StringBuffer();
sql.append("select u.* ,r.roleName as userRoleName from smbms_user u,smbms_role r where u.userRole = r.id");
ArrayList<Object> list = new ArrayList<Object>();
if (!StringUtils.isNullOrEmpty(userName)){
sql.append(" and u.userName like ?");
list.add("%"+userName+"%");
}
if (userRole>0){
sql.append(" and u.userRole = ?");
list.add(userRole);
}
sql.append(" order by creationDate DESC limit ?,?");
currenPageNo = (currenPageNo-1)*pageSize;
list.add(currenPageNo);
list.add(pageSize);
Object[] params = list.toArray();
System.out.println("sql----->"+sql.toString());
rs = BaseDao.execute(connection,rs,pstm,sql.toString(),params);
while (rs.next()){
User _user = new User();
_user.setId(rs.getInt("id"));
_user.setUserCode(rs.getString("userCode"));
_user.setUserName(rs.getString("userName"));
_user.setGender(rs.getInt("gender"));
_user.setBirthday(rs.getDate("birthday"));
_user.setPhone(rs.getString("phone"));
_user.setUserRole(rs.getInt("userRole"));
_user.setUserRoleName(rs.getString("userRoleName"));
userList.add(_user);
}
BaseDao.closeResource(null,rs,pstm);
}
return userList;
}
3.UserService
//根据条件查询用户列表
public List<User> getUserList(String queryUserName,int queryUserRole,int currentPageNo,int pageSize);
4.UserServicelmpl
//根据条件查询用户列表
@Override
public List<User> getUserList(String queryUserName, int queryUserRole, int currentPageNo, int pageSize) {
Connection connection = null;
List<User> userList = null;
System.out.println("queryUserName--"+queryUserName);
System.out.println("queryUserRole--"+queryUserRole);
System.out.println("currentPageNo--"+currentPageNo);
System.out.println("pageSize--"+pageSize);
connection = BaseDao.getConnection();
try {
userList = userDao.getUserList(connection, queryUserName, queryUserRole, currentPageNo, pageSize);
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
BaseDao.closeResource(connection,null,null);
}
return userList;
}
3、获取角色操作
为了我们职责统一，可以吧角色的操作单独放在一个包中，和POJO类对应
RoleDao
//获取角色列表
public List<Role> getRoleList(Connection connection) throws SQLException;
RoleDaoImpl
package com.wang.dao.role;
import com.wang.dao.BaseDao;
import com.wang.pojo.Role;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class RoleDaoImpl implements RoleDao{
//获取角色列表
@Override
public List<Role> getRoleList(Connection connection) throws SQLException {
PreparedStatement pstm = null;
ResultSet resultSet = null;
ArrayList<Role> roleList = new ArrayList<Role>();
if (connection!=null){
String sql = "select * from smbms_role";
Object[] params = {};
resultSet = BaseDao.execute(connection, resultSet, pstm, sql, params);
while (resultSet.next()){
Role role = new Role();
role.setId(resultSet.getInt("id"));
role.setRoleCode(resultSet.getString("roleCode"));
role.setRoleName(resultSet.getString("roleName"));
roleList.add(role);
}
BaseDao.closeResource(null,resultSet,pstm);
}
return roleList;
}
}
RoleService
//获取角色列表
public List<Role> getRoleList();
RoleServiceImpl
public class RoleServiceImpl implements RoleService{
//引入dao
private RoleDao roleDao;
public RoleServiceImpl() {
roleDao = new RoleDaoImpl();
}
//获取角色列表
@Override
public List<Role> getRoleList() {
Connection connection = null;
List<Role> roleList = null;
connection = BaseDao.getConnection();
try {
roleList = roleDao.getRoleList(connection);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return roleList;
}
}
4、用户显示的Servlet
1.获取用户前端的数据（查询)
2判断请求是否需要执行，看参数的值判断
3.为了实现分页，需要计算出当前页面和总页面，页面大小…
4.用户列表展示
5.返回前端
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
String method = req.getParameter("method");
if ("savepwd".equals(method) && method != null) {//新密码修改
updatePwd(req, resp);
} else if ("pwdmodify".equals(method) && method != null){//对旧密码的验证
this.pwdModify(req, resp);
} else if (("query").equals(method) && method!=null){
this.query(req,resp);
}
}
//联表查询 重点 难点 必须自从头到尾的整理清楚
public void query(HttpServletRequest req, HttpServletResponse resp){
//查询用户列表
//从前端获取数据
String queryUserName = req.getParameter("queryname");
String temp = req.getParameter("queryUserRole");
String pageIndex = req.getParameter("pageIndex");
int queryUserRole = 0;
//获取用户列表
UserServiceImpl userService = new UserServiceImpl();
List<User> userList = null;
//第一次走这个请求时，一定是第一页，页面大小是固定的；
int pageSize = 5;//可以把这些到配置文件中，方便后期修改；
int currentPageNo = 1;
if (queryUserName == null){
queryUserName = "";
}
if (temp!=null && !temp.equals("")){
queryUserRole = Integer.parseInt(temp);//给查询赋值！0，1，2，3
}
if (pageIndex!=null){
currentPageNo = Integer.parseInt(pageIndex);
}
//获取用户的总页数（分页：上一页，下一页的情况）
int totalCount = userService.getUserCount(queryUserName, queryUserRole);
//总页数支持
PageSupport pageSupport = new PageSupport();
pageSupport.setCurrentPageNo(currentPageNo);
pageSupport.setPageSize(pageSize);
pageSupport.setTotalCount(totalCount);
int totalPageCount = ((int)(totalCount/pageSize))+1;
//控制首页和尾页
//如果页面要小于1了，就显示第一页的东西
if (currentPageNo<1){
currentPageNo = 1;
}else if (currentPageNo>totalPageCount){//当前页面大于了最后一页；
currentPageNo = totalPageCount;
}
//获取用户列表展示
System.out.println("-------userList前");
userList = userService.getUserList(queryUserName, queryUserRole, currentPageNo, pageSize);
req.setAttribute("userList",userList);
System.out.println("-------userList后");
RoleServiceImpl roleService = new RoleServiceImpl();
List<Role> roleList = roleService.getRoleList();
req.setAttribute("roleList",roleList);
req.setAttribute("totalCount",totalCount);
req.setAttribute("currentPageNo",currentPageNo);
req.setAttribute("totalPageCount",totalPageCount);
req.setAttribute("queryUserName",queryUserName);
req.setAttribute("queryUserRole",queryUserRole);
System.out.println("req.setAttribute后");
//返回前端
try {
req.getRequestDispatcher("userlist.jsp").forward(req,resp);
} catch (ServletException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
System.out.println("这里是UserServlet的请求转发；");
}
upport.setCurrentPageNo(currentPageNo);
pageSupport.setPageSize(pageSize);
pageSupport.setTotalCount(totalCount);
int totalPageCount = ((int)(totalCount/pageSize))+1;
//控制首页和尾页
//如果页面要小于1了，就显示第一页的东西
if (currentPageNo<1){
currentPageNo = 1;
}else if (currentPageNo>totalPageCount){//当前页面大于了最后一页；
currentPageNo = totalPageCount;
}
//获取用户列表展示
System.out.println("-------userList前");
userList = userService.getUserList(queryUserName, queryUserRole, currentPageNo, pageSize);
req.setAttribute("userList",userList);
System.out.println("-------userList后");
RoleServiceImpl roleService = new RoleServiceImpl();
List<Role> roleList = roleService.getRoleList();
req.setAttribute("roleList",roleList);
req.setAttribute("totalCount",totalCount);
req.setAttribute("currentPageNo",currentPageNo);
req.setAttribute("totalPageCount",totalPageCount);
req.setAttribute("queryUserName",queryUserName);
req.setAttribute("queryUserRole",queryUserRole);
System.out.println("req.setAttribute后");
//返回前端
try {
req.getRequestDispatcher("userlist.jsp").forward(req,resp);
} catch (ServletException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
System.out.println("这里是UserServlet的请求转发；");
}
2588
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
