基于Java Servlet 构建的用户信息管理系统
项目源码:https://github.com/Huidaka/Java-code/tree/master/usermanager
项目展示:huidaka.xyz 用户名:root 密码:123456
核心功能
- 登录、注册
- 添加用户信息
- 删除某一个用户信息
- 删除选中的用户信息
- 分页查询所有用户信息
- 模糊查询用户信息
- 更新用户信息
重要知识点
- 简单的Web服务器设计能力
- Java 操作 MySQL 数据库
- 数据库设计
- json 的使用
- 强化 HTTP 协议的理解
- Servlet 的使用
- Java集合的使用
- 前端知识的简单使用,比如:HTML+CSS+JS
整体架构
项目整体基于HTTP协议,前端使用HTML+CSS+JS构建页面整体布局,后端采用分层结构,分为Servlet层,
Service层,Dao层的设计,这样的设计可以让我们的项目代码实现高内聚低耦合。
数据库设计
只需要一张用户表,表示用户的信息
database if exists usermanger;
create DATABASE if not exists usermanger character set utf8;
use usermanger;
drop table if exists usermessage;
create table `usermessage` (
`id` INT PRIMARY KEY auto_increment,
`name` varchar (60),
`username` varchar (60) default 'bit',
`password` varchar (60) default '123456',
`gender` varchar (4),
`age` int,
`address` varchar (90),
`qq` varchar (20),
`email` varchar (30)
);
INSERT INTO usermessage VALUES(1,'张飞','zhangfei','123','男',18,'成都','1262913815','126@qq.com');
INSERT INTO usermessage VALUES(2,'关羽','guanyu','1234','男',18,'陕西','1262913816','1262@qq.com');
INSERT INTO usermessage VALUES(3,'张三','zhangsan','1235','女',19,'陕西','1262913817','1263@qq.com');
INSERT INTO usermessage VALUES(4,'李四','lisi','1236','男',20,'北京','1262913818','1264@qq.com');
INSERT INTO usermessage VALUES(5,'王五','wangwu','1237','女',21,'陕西','1262913819','1265@qq.com');
INSERT INTO usermessage VALUES(6,'孙权','sunquan','1238','男',22,'上海','1262913814','1266@qq.com');
INSERT INTO usermessage VALUES(7,'孙悟空','sunwukong','1239','男',23,'陕西','1262913813','1267@qq.com');
服务器 API 设计
登录
请求:
POST /loginServlet
响应:
{msg: true}
添加用户
请求:
POST /addServlet
响应:
{msg: true}
删除某一个用户信息
请求:
GET /deleteServlet?id=1
删除选中用户信息
请求:
POST /deleteSelectedServlet
响应:
{msg: true}
分页查询所有用户信息
请求:
POST /findByPageServlet
data:{currentPage,rows,name,address,email}
响应: 响应体内容为,每一页的用户信息
模糊查询用户信息
请求:
POST /findByPageServlet
data:{currentPage,rows,name,address,email}
响应:
响应体内容为,每一页的用户信息
更新用户信息
-------------------更新用户信息之前,先请求得到要修改的用户的信息--------------------------
请求:
POST /returnServlet
响应:
当前需要更新的用户的信息
--------------------------更新完成后,提交更新信息请求------------------------------------
请求:
POST /updateServlet
响应:
{msg: true}
创建一个 JavaWeb 项目
创建entity包
- 创建User类。
package entiy;
public class User {
private int id;
private String name;
private String username;
private String password;
private String gender;
private int age;
private String address;
private String qq;
private String email;
public String getEmail() {return email;}
public void setEmail(String eamil) {this.email = eamil;}
public int getId() {return id;}
public void setId(int id) {this.id = id;}
public String getName() {return name;}
public void setName(String name) {this.name = name;}
public String getUsername() {return username;}
public void setUsername(String username) {this.username = username;}
public String getPassword() {return password;}
public void setPassword(String password) {this.password = password;}
public String getGender() {return gender;}
public void setGender(String gender) {this.gender = gender;}
public int getAge() {return age;}
public void setAge(int age) {this.age = age;}
public String getAddress() {return address;}
public void setAddress(String address) {this.address = address;}
public String getQq() {return qq;}
public void setQq(String qq) {this.qq = qq;}
}
- 创建分页对象
package entiy;
import java.util.List;
public class PageBean<T> {
private int totalCount; //总记录数
private int totalPage; //总页码
private List<T> list; //每页中的数据
private int currentPage; //当前页码
private int rows; //每页的记录数
public int getTotalCount() {return totalCount;}
public void setTotalCount(int totalCount) {this.totalCount = totalCount;}
public int getTotalPage() {return totalPage;}
public void setTotalPage(int totalPage) {this.totalPage = totalPage;}
public List<T> getList() {return list;}
public void setList(List<T> list) {this.list = list;}
public int getCurrentPage() {return currentPage;}
public void setCurrentPage(int currentPage) {this.currentPage = currentPage;}
public int getRows() {return rows;}
public void setRows(int rows) {this.rows = rows;}
}
创建一个util包,创建DBUtil类。(连接数据库)
package util;
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class DBUtil {
private static DataSource dataSource = null;
private static final String URL = "jdbc:mysql://127.0.0.1:3306/usermanger?characterEncoding=utf-8&useSSL=true";
private static final String USERNAME = "root"; //数据库用户名
private static final String PASSWORD = "123456";//数据库登录密码
public static DataSource getDataSource() {
if(dataSource == null){
synchronized (DBUtil.class){
if(dataSource == null){
dataSource = new MysqlDataSource();
((MysqlDataSource)dataSource).setURL(URL);
((MysqlDataSource)dataSource).setUser(USERNAME);
((MysqlDataSource)dataSource).setPassword(PASSWORD);
}
}
}
return dataSource;
}
public static Connection getConnection(){
try {
return getDataSource().getConnection();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return null;
}
//按顺序关闭数据库连接
public static void close(Connection connection, PreparedStatement statement, ResultSet resultSet) throws SQLException {
if(resultSet != null){
resultSet.close();
}
if (statement != null) {
statement.close();
}
if (connection != null) {
connection.close();
}
}
}
创建dao包和UserDao类
public class UserDao {
//查询
public User login(User loginUser) {
return null;
}
//添加
public int add(User addUser) {
return 0;
}
//删除
public int delete(int id) {
return 0;
}
//查询
public User find(int id) {
return null;
}
//更新
public int update(User updateUser) {
return 0;
}
/**
* 分页进行模糊查询
* start:开始查询的起始位置
* rows:共查询的记录
* map:包含:currentPage、rows、name、address、email
*/
public List<User> findByPage(int start, int rows,Map<String, String[]> map) {
return null;
}
/**
* 查询共有多少条记录
* @param map 包含 name address email
* @return
*/
public int findAllRecord(Map<String, String[]> map) {
return 1;
}
}
实现UserDao.login
public User login(String loginname, String loginpassword){
Connection connection = DBUtil.getConnection();
PreparedStatement statement = null;
ResultSet resultSet = null;
User user = null;
String sql ="select * from user where user=? and password=?";//查询名字和密码相匹配的用户
try {
statement = connection.prepareStatement(sql);
statement.setString(1,loginname);
statement.setString(2,loginpassword);
resultSet = statement.executeQuery();
if(resultSet.next()){
return resultCreateUser(resultSet); //如果存在用户就返回该用户的相关信息
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
try {
DBUtil.close(connection,statement,resultSet); //关闭数据库连接
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
return user;
}
实现UserDao.add
//添加用户
public int add(User user){
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
connection = DBUtil.getConnection();
String sql = "insert into usermessage(name,username,password,gender,age,address,qq,email) value (" +
"?,?,?,?,?,?,?,?)";
statement = connection.prepareStatement(sql);
statement.setString(1,user.getName());
statement.setString(2,user.getUsername());
statement.setString(3,user.getPassword());
statement.setString(4,user.getGender());
statement.setInt(5,user.getAge());
statement.setString(6,user.getAddress());
statement.setString(7,user.getQq());
statement.setString(8,user.getEmail());
int ret = statement.executeUpdate();//根据前端界面提供的用户信息来新增用户
return ret;
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
try {
DBUtil.close(connection,statement,resultSet);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
return 0;
}
实现UserDao.delete
//根据id删除用户
public int delete(int id){
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
connection = DBUtil.getConnection();
String sql = "delete from usermessage where id=?";;
statement = connection.prepareStatement(sql);
statement.setInt(1,id);
int ret = statement.executeUpdate();
return ret;
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
try {
DBUtil.close(connection,statement,resultSet);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
return 0;
}
实现UserDao.find
//根据id查找用户
public User find(int id){
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
connection = DBUtil.getConnection();
String sql = "select * from usermessage where id=?";;
statement = connection.prepareStatement(sql);
statement.setInt(1,id);
resultSet= statement.executeQuery();
if(resultSet.next()){
return resultCreateUser(resultSet);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
try {
DBUtil.close(connection,statement,resultSet);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
return null;
}
//根据name查找用户
public User findName(String name){
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
connection = DBUtil.getConnection();
String sql = "select * from usermessage where name = ?";;
statement = connection.prepareStatement(sql);
statement.setString(1,name);
resultSet= statement.executeQuery();
if(resultSet.next()){
return resultCreateUser(resultSet);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
try {
DBUtil.close(connection,statement,resultSet);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
return null;
}
实现UserDao.update
//更新用户信息
public int updateUser(User upUser){
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
connection = DBUtil.getConnection();
String sql = "update usermessage set gender=? ,age=? ,address=?, qq=?, email=? where id=?";
statement = connection.prepareStatement(sql);
statement.setString(1,upUser.getGender());
statement.setInt(2,upUser.getAge());
statement.setString(3,upUser.getAddress());
statement.setString(4, upUser.getQq());
statement.setString(5, upUser.getEmail());
statement.setInt(6,upUser.getId());
int ret = statement.executeUpdate();
return ret;
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
try {
DBUtil.close(connection,statement,resultSet);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
return 0;
}
实现UserDao.findByPage
/**
* 查询当前条件下的所有的用户信息。
* 当然这个条件随机:组合,或者不给条件
* @param start 开始查询的起始位置
* @param rows 每次查询多少条记录
* @param map name,address,email
* Map<String, String[]> map
* map{name:"gaobo",address:"陕西",email:"123...."}
* String[]实际上,只放了一个元素。
* 之所以用String[],是因为从req.getParameterMap()获取到的就是String,String[]的形式
* 用String[]的形式是为了应对多选,一个属性有多个值的情况
* @return
*/
public List<User> findByPage(int start, int rows, Map<String,String[]> map){
List<User> userList = new LinkedList<>();
String sql = "select * from usermessage where 1=1"; //写1=1是为了保证条件都没有的时候进行全部用户查询
List<String> list = new LinkedList<>();
StringBuffer stringBuffer = new StringBuffer(sql);
Set<String> set = map.keySet();
for(String str : set){ //通过循环将约束条件拼接到sql语句上
String value = map.get(str)[0];
//只有在前端输入条件时才会把条件拼接到sql语句中
if(value != null && value != ""){
stringBuffer.append(" and ").append(str).append(" like ? ");
//把所有约束条件的值都先存放到list中,方便后边直接进行赋值
list.add("%" + value + "%");
}
}
stringBuffer.append("limit ?,?"); // 进行分页查询的条件
list.add(String.valueOf(start));
list.add(String.valueOf(rows));
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
connection = DBUtil.getConnection();
statement = connection.prepareStatement(stringBuffer.toString());
//给sql语句的参数赋值
for(int i = 0; i<list.size(); i++){
statement.setObject(i+1,list.get(i));
}
resultSet = statement.executeQuery();
//根据返回结果集创建用户
while (resultSet.next()){
userList.add(resultCreateUser(resultSet));
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
try {
DBUtil.close(connection,statement,resultSet);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
return userList;
}
实现UserDao.findAllRecord
/**查询符合条件的用户共有多少个
* 逻辑和模糊查询函数一样,区别就是这个函数进行查询的时候不用加上limit分页查询
* @param map
* @return
*/
public int findAllRecord(Map<String, String[]> map) {
int count = 0;
String sql = "select count(*) from usermessage where 1=1";
Set<String> set = map.keySet();
StringBuffer sb = new StringBuffer(sql);
List<Object> list = new ArrayList<>();
for(String str : set){
String value = map.get(str)[0];
if(value!=null && !value.equals("")){
sb.append(" and ").append(str).append(" like ?");
list.add("%"+value+"%");
}
}
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
connection = DBUtil.getConnection();
try {
statement = connection.prepareStatement(sb.toString());
//给sql语句赋值
for(int i=0; i<list.size(); i++){
statement.setObject(i+1,list.get(i));
}
resultSet = statement.executeQuery();
if(resultSet.next()){
count = resultSet.getInt(1);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
finally{
try {
DBUtil.close(connection,statement,resultSet);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
return count;
}
}
Service层设计实现
package service;
import dao.UserDao;
import entiy.PageBean;
import entiy.User;
import java.util.List;
import java.util.Map;
public class UserService {
public User login(String loginname, String loginpassword){
UserDao userDao = new UserDao();
return userDao.login(loginname,loginpassword);
}
public int add(User user){
UserDao userDao = new UserDao();
int ret = userDao.add(user);
return ret;
}
public int delete(int id) {
UserDao userDao = new UserDao();
int ret = userDao.delete(id);
return ret;
}
public User find(int id) {
UserDao userDao = new UserDao();
User user = userDao.find(id);
return user;
}
public User findName(String name){
UserDao userDao = new UserDao();
User user = userDao.findName(name);
return user;
}
public int updateUser(User upUser) {
UserDao userDao = new UserDao();
int ret = userDao.updateUser(upUser);
return ret;
}
/**
* @param currentPage 当前页
* @param rows 每页的行数
* @param map 包含 name address email 根据map当中的条件 进行查询
* @return
* private int totalCount; //总记录数 findAllRecord 完成
* private int totalPage; //总页码 完成
* private List<T> list; //每页中的数据 ---》 findByPage 完成
* private int currentPage; //当前页码 已知的 完成
* private int rows; //每页的记录数 完成
*
*/
public PageBean<User> findAllByPage(int currentPage, int rows, Map<String, String[]> map) {
UserDao userDao = new UserDao();
PageBean<User> pageBean = new PageBean<>();
//设置总记录数
int totalCount = userDao.findAllRecord(map);
pageBean.setTotalCount(totalCount);
//设置总页数
int totalPage = 0;
if(totalCount % rows == 0){
totalPage = totalCount / rows;
}
else {
totalPage = (totalCount / rows )+ 1;
}
pageBean.setTotalPage(totalPage);
//设置每页中的数据
int start = (currentPage-1)*rows;
List<User> listUser = userDao.findByPage(start,rows,map);
pageBean.setList(listUser);
//设置当前页数
pageBean.setCurrentPage(currentPage);
//设置每页记录数
pageBean.setRows(rows);
return pageBean;
}
}
Servlet实现与实现
LoginServlet实现
@WebServlet("/loginServlet")
public class LoginServlet extends HttpServlet {
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
req.setCharacterEncoding("utf-8");
resp.setContentType("application/json;charset=utf-8");
String username = req.getParameter("username");
String password = req.getParameter("password");
UserService userService = new UserService(); //调用service层的登录方法,判断是否登录成功
User user = userService.login(username,password);
Map<String,Boolean> returnMap = new HashMap<>(); //创建一个map集合,存放返回到客户端的数据
if(user == null){
System.out.println("登录失败");
returnMap.put("msg",false);
}
else{
System.out.println("登录成功");
//登录成功返回一个session,用来保存用户的登录信息,验证用户是否进行了登录
req.getSession().setAttribute("root","123456");
returnMap.put("msg",true);
}
//利用Jackson将map转化为json对象
ObjectMapper objectMapper = new ObjectMapper();
objectMapper.writeValue(resp.getWriter(),returnMap);
}
}
FindByPageServlet实现
用户进行登录后,需要先进行查询,将查询结果显示到页面上。
@WebServlet("/findByPageServlet")
public class FindBeanPageServlet extends HttpServlet {
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
req.setCharacterEncoding("utf-8");
resp.setContentType("application/json;charset=utf-8");
Map<String,String[]> map = req.getParameterMap();
Map<String,String[]> stringMap = new HashMap<>(map);
String currentPage = req.getParameter("current");
String rows = req.getParameter("rows");
UserService userService = new UserService();
PageBean<User> pageBean = userService.findAllByPage(Integer.parseInt(currentPage),Integer.parseInt(rows),map);
ObjectMapper objectMapper = new ObjectMapper();
objectMapper.writeValue(resp.getWriter(),pageBean);
}
}
修改/更新用户信息实现
- 先根据id,查询当前要修改的用户是否存在。把当前查询到的用户写入session。然后跳转的更新页面
( FindUserServlet )。 - 获取到当前用户的session信息,并且返回(ReturnServlet)。
- 更新完成,提交信息。
@WebServlet("/findUserServlet")
public class FindUserServlet extends HttpServlet {
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
req.setCharacterEncoding("utf-8");
resp.setContentType("application/json;charset=utf-8"); //设置编码格式
int id = Integer.parseInt(req.getParameter("id")); //获取参数,将String类型的id转化为INT类型
UserService userService = new UserService();
User user = userService.find(id); //调用Service层的方法,查询id为i的数据
if(user == null){
System.out.println("未找到该用户");
}
else {
req.getSession().setAttribute("User",user); //找到需要更新的用户,记录到session
resp.sendRedirect("update.html"); //跳转到update.html
}
}
}
//把刚刚记录到session里面的数据,取出来转化为json.方便在更新页面上预显示信息
@WebServlet("/returnServlet")
public class ReturnServlet extends HttpServlet {
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
req.setCharacterEncoding("utf-8");
resp.setContentType("application/json;charset=utf-8");
User user = (User)req.getSession().getAttribute("User");
ObjectMapper objectMapper = new ObjectMapper();
objectMapper.writeValue(resp.getWriter(),user);
}
}
//当修改页面完成修改后,点击提交将新的user信息进行更新
@WebServlet("/updateServlet")
public class UpdateServlet extends HttpServlet {
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
req.setCharacterEncoding("utf-8");
resp.setContentType("application/json;charset=utf-8");
User user = (User)req.getSession().getAttribute("User");
//将id赋给updateUser对象
user.setName(req.getParameter("name"));
user.setGender(req.getParameter("gender"));
user.setAge(Integer.parseInt(req.getParameter("age")));
user.setAddress(req.getParameter("address"));
user.setQq(req.getParameter("qq"));
user.setEmail(req.getParameter("email"));
//调用Service层的方法,更新
UserService userServices = new UserService();
int result = userServices.updateUser(user);
Map<String,Boolean> map = new HashMap<>();
if(result == 1){
map.put("msg",true);
}
else{
map.put("msg",false);
}
ObjectMapper objectMapper = new ObjectMapper();
objectMapper.writeValue(resp.getWriter(),map);
}
}
删除用户信息实现
删除单个用户
获取前端参数id.
@WebServlet("/deleteServlet")
public class DeleteServlet extends HttpServlet {
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
req.setCharacterEncoding("utf-8");
resp.setContentType("application/json;charset=utf-8");
int id = Integer.parseInt(req.getParameter("id"));
UserService userService = new UserService();
int ret = userService.delete(id);
if(ret == 1){
System.out.println("删除成功");
resp.sendRedirect("list.html");
}
else {
System.out.println("删除失败");
resp.getWriter().write("<h2 删除失败!>" + "</h2>");
}
}
}
删除选中用户
获取前端选中的id数组
@WebServlet("/deleteSelectedServlet")
public class DeleteSelectedServlet extends HttpServlet {
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
req.setCharacterEncoding("utf-8");
resp.setContentType("application/json;charset=utf-8");
String strID = req.getParameter("id[]");
String[] strIDs = strID.split(" "); //获取传过来的id数组
UserService userService = new UserService();
int sum = 0;
for(String str : strIDs ){ //将id数组中所有id用户都删除
int id = Integer.parseInt(str);
sum += userService.delete(id);
}
Map<String,Boolean> returnMap = new HashMap<>();
if(sum == strIDs.length){
returnMap.put("msg",true); //如果全都删除成功后,那么sum就等于strIDs.length
}
else {
returnMap.put("msg",false);
}
ObjectMapper objectMapper = new ObjectMapper();
objectMapper.writeValue(resp.getWriter(),returnMap);
}
}
前端页面的设计
前端采用HTML+CSS+JS设计。
因为对前端的知识了解不是很多,所以这个项目的前端部分时直接从网上用的别人写好的模板,就不做详细的解释了。
前后端服务器数据交互-以登录为例:
<script>
//登录请求
$(function () {
$("#submit").click(function () {
var username=$("#user").val();
var password=$("#password").val();
$.ajax({
url:"/loginServlet",//发送请求的地址
data:{"username":username,"password":password},//发送给服务器的数据
type:"POST",//请求方式 ("POST" 或 "GET"), 默认为 "GET"
dataType:"json",//预期服务器返回的数据类型
success:function (data) {//请求成功后的回调函数
console.log(data);
if(data.msg===true){
window.location.href="list.html";
}else{
/*window.location.reload(); 数据清空*/
$("#message").text("账号或密码错误,请重试!");
$("#user").val("");
$("#password").val("");
$("#verifycode").val("");
}
}
});
});
});
</script>
项目难点
如何进行模糊查询?
因为我们平时写SQL语句都是已经事先知道好约束条件以及查询内容的,所以我们可以直接进行查询。但是我们要进行模糊查询的时候,所有条件都是未知的,前端给我们传几个数据,具体传哪几个数据我们都是未知的,这都是运行之后,用户根据需求输入的。
所以我们的SQL语句就需要根据用户输入的信息动态拼接生成完整的SQL语句。
-
首先我们前端传过来的数据都保存在一个Map里面,我们在service层复制一下这个Map,然后把里面的currentPage,rows数据去掉。这两个实在service层计算分页查询的相关信息的。我们让Map里面只剩下三个模糊查询的条件。
-
然后我们在dao层里面先写一个基础的SQL语句,假如三个条件都没有约束,那我们直接执行基础的SQL语句,进行全部用户信息的查询
String sql = "select * from usermessage where 1=1";
- 然后我们遍历map中的数据,如果某个条件不为空,就把该条件的约束加到SQL语句中
Set<String> set = map.keySet();
for(String str : set){ //通过循环将约束条件拼接到sql语句上
String value = map.get(str)[0];
//只有在前端输入条件时才会把条件拼接到sql语句中
if(value != null && !value.equals("")){
stringBuffer.append(" and ").append(str).append(" like ?");
//把所有约束条件的值都先存放到list中,方便后边直接进行赋值
list.add("%" + value + "%");
}
}
- 我们再把liimit条件加上去
stringBuffer.append(" limit ?,?"); // 进行分页查询的条件
list.add(start);
list.add(rows);
- 给sql语句的参数赋值
for(int i = 0; i<list.size(); i++){
statement.setObject(i+1,list.get(i));
}
举例:
假如我们在输入框名字栏输入了”郭“这个字,在地址栏输了个”河南“,邮箱栏什么都没有输入。默认当前第一页,每页五条数据。那我们dao层findByPage函数传入的参数就是
start:0; rows:5; map:name - 郭,address - 河南,email - “” ;
第一次循环:
判断name不为空,也不为空字符串,所以name是有限制条件的,所以对SQL进行拼接name限制条件,此时的SQL就是:
select * from usermessage where 1=1 and name like ?
第二次循环:
判断address不为空,也不为空字符串,所以address也是有限制条件的,所以对SQL进行拼接address限制条件,此时的SQL就是:
select * from usermessage where 1=1 and name like ? and address like ?
第三次循环:
判断email为空字符串,说明用户没有对emai信息进行限制,所以不用在SQL语句中对email信息进行拼接。
循环结束:把limit分页查询条件拼接上去
select * from usermessage where 1=1 and name like ? and address like ? limit 0,5;
对SQL中的参数进行赋值,最终查询语句的SQL就拼接完成
select * from usermessage where 1=1 and name like %郭% and address %河南% ? limit 0,5;
项目拓展
- 信息预填
管理员更新用户信息的时候,服务器自动将用户旧信息填入对应的信息框中作参考。首先,管理员点击修改之后,先访问FindUserServlet,服务器通过查询id获取用户信息,然后将该用户原来的信息保存到服务器(用过session机制),返回给浏览器一个sessionID,然后再重定向到update.html,然后浏览器打开update.html的时候会自动带上刚刚返回的sessionID访问ReturnServlet来获取刚刚服务器保存的用户信息,获取到之后就会填入到对应的信息栏中,让管理员更改用户信息的时候可以参照原来的旧信息。
效果:
代码:
@WebServlet("/findUserServlet")
public class FindUserServlet extends HttpServlet {
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
req.setCharacterEncoding("utf-8");
resp.setContentType("application/json;charset=utf-8");
int id = Integer.parseInt(req.getParameter("id"));
UserService userService = new UserService();
User user = userService.find(id); //通过id查找用户
if(user == null){
System.out.println("未找到该用户");
}
else {
req.getSession().setAttribute("User",user); //保存user,返回sessionID
resp.sendRedirect("update.html");
}
}
}
@WebServlet("/returnServlet")
public class ReturnServlet extends HttpServlet {
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
req.setCharacterEncoding("utf-8");
resp.setContentType("application/json;charset=utf-8");
UserService userService = new UserService();
User user = (User)req.getSession().getAttribute("User");//通过sessionID,访问保存的用户信息
ObjectMapper objectMapper = new ObjectMapper();
objectMapper.writeValue(resp.getWriter(),user); //通过json将user信息序列化并将结果返回给update.html
}
}
- 请求过滤
为了防止用户直接通过url访问后面需要登录才可以实现的功能,可以将所有的请求过滤一遍。登录成功的用户,我们可以给浏览器返回一个sessionID,用来判断用户是否登录过。首先判断url是否访问的是登录界面,如果是就不进行拦截,直接假如filter链向下执行,如果不是,我们就进行判断,判断这个请求中是否包含了sessionID,然后判断这个sessionID是否是登录成功返回的sessionID,如果是,就证明用户已经登录过了,可以放行,如果不是,就将用户重定向到登录界面,提示用户进行登录
代码:
public class loginFilter implements Filter {
@Override
public void init(FilterConfig filterConfig) throws ServletException {
}
@Override
public void doFilter(ServletRequest servletRequest, ServletResponse servletResponse, FilterChain filterChain) throws IOException, ServletException {
/** 1,doFilter方法的第一个参数为ServletRequest对象。此对象给过滤器提供了对进入的信息(包括* 表单数据、cookie和HTTP请求头)的完全访问。第二个参数为ServletResponse,通常在简单的过* 滤器中忽略此参数。最后一个参数为FilterChain,此参数用来调用servlet或JSP页。*/
HttpServletRequest request = (HttpServletRequest) servletRequest;
/** 如果处理HTTP请求,并且需要访问诸如getHeader或getCookies等在ServletRequest中* 无法得到的方法,就要把此request对象构造成HttpServletRequest*/
HttpServletResponse response = (HttpServletResponse) servletResponse;
String currentURL = request.getRequestURI();
System.out.println(currentURL);
//取得根目录所对应的绝对路径:
String targetURL = currentURL.substring(currentURL.indexOf("/", 1), currentURL.length());
System.out.println(targetURL);
//截取到当前文件名用于比较
HttpSession session = request.getSession(false);
if (!"/login.html".equals(targetURL)) {//判断当前页是否是重定向以后的登录页面页面,如果是就不做session的判断,防止出现死循环
if (session == null || session.getAttribute("root") == null) {
//*用户登录以后需手动添加session
System.out.println("request.getContextPath()=" + request.getContextPath());
response.sendRedirect(request.getContextPath() + "/login.html");
//如果session为空表示用户没有登录就重定向到login.html页面
return;
}
}
//加入filter链继续向下执行
filterChain.doFilter(request, response);
}
@Override
public void destroy() {
}
}
后续拓展
- 对用户的字段进行添加如:用户等级,VIP,关注人数,粉丝数,头像,手机号等。
- 功能上添加:根据等级进行查询,根据粉丝人数进行查询等。