目录
一.数据库表设计
1、管理员表
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '管理员id',
`image` varchar(255) DEFAULT NULL COMMENT '管理员头像',
`name` varchar(255) DEFAULT NULL COMMENT '管理员姓名',
`gender` varchar(10) DEFAULT NULL COMMENT '管理员性别',
`phone` varchar(11) DEFAULT NULL COMMENT '管理员联系电话',
`username` varchar(50) DEFAULT NULL COMMENT '登录账号',
`password` varchar(50) DEFAULT NULL COMMENT '登录密码',
`identity` tinyint(4) DEFAULT NULL COMMENT '管理员身份（0代表投资人，1代表店长，2代表网管，3代表系统管理员）',
`status` tinyint(4) DEFAULT NULL COMMENT '状态（1：正常 0：停用）',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
2、使用记录表
CREATE TABLE `usage_records` (
`record_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '记录ID',
`customer_id` int(11) DEFAULT NULL COMMENT '顾客ID',
`computer_id` int(11) DEFAULT NULL COMMENT '电脑ID',
`start_time` datetime DEFAULT NULL COMMENT '使用开始时间',
`end_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '使用结束时间',
PRIMARY KEY (`record_id`),
KEY `customer_id` (`customer_id`),
KEY `computer_id` (`computer_id`),
CONSTRAINT `usage_records_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`),
CONSTRAINT `usage_records_ibfk_2` FOREIGN KEY (`computer_id`) REFERENCES `computers` (`computer_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
3、交易记录表
CREATE TABLE `transaction_records` (
`record_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '充值和消费记录',
`customer_id` int(11) DEFAULT NULL COMMENT '顾客ID',
`card_number` int(11) DEFAULT NULL COMMENT '会员卡号',
`transaction_type` varchar(50) DEFAULT NULL COMMENT '交易类型',
`amount` decimal(10,2) DEFAULT NULL COMMENT '交易金额',
`transaction_date` date DEFAULT NULL COMMENT '交易日期',
PRIMARY KEY (`record_id`),
KEY `customer_id` (`customer_id`),
KEY `card_number` (`card_number`),
CONSTRAINT `transaction_records_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`),
CONSTRAINT `transaction_records_ibfk_2` FOREIGN KEY (`card_number`) REFERENCES `membership_cards` (`card_number`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8;
4、会员卡表
CREATE TABLE `membership_cards` (
`card_number` int(11) NOT NULL COMMENT '会员卡号',
`password` varchar(50) DEFAULT NULL COMMENT '会员密码',
`name` varchar(255) DEFAULT NULL COMMENT '会员姓名',
`gender` varchar(10) DEFAULT NULL COMMENT '会员性别',
`registration_date` varchar(10) DEFAULT NULL COMMENT '注册日期',
`membership_level` varchar(50) DEFAULT NULL COMMENT '会员等级',
`balance` decimal(20,2) DEFAULT NULL COMMENT '余额',
`status` tinyint(4) DEFAULT NULL COMMENT '状态（0代表冻结，1代表正常）',
`customer_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '顾客ID',
PRIMARY KEY (`card_number`),
KEY `customer_id` (`customer_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
5、客户表
CREATE TABLE `customers` (
`customer_id` int(11) NOT NULL COMMENT '顾客ID',
`name` varchar(255) DEFAULT NULL COMMENT '顾客姓名',
`gender` varchar(10) DEFAULT NULL COMMENT '顾客性别',
`date_of_birth` varchar(10) DEFAULT NULL COMMENT '顾客的出生日期',
`phone` varchar(20) DEFAULT NULL COMMENT '顾客的联系电话',
`id_address` varchar(255) DEFAULT NULL COMMENT '顾客的身份证地址',
`id_number` varchar(20) DEFAULT NULL COMMENT '顾客的身份证号码',
`is_member` tinyint(4) DEFAULT NULL COMMENT '顾客是否为会员（1：是会员 0：不是会员）',
`status` tinyint(4) DEFAULT NULL COMMENT '顾客的上机状态（1：上机 0：下机）',
PRIMARY KEY (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
6、电脑表
CREATE TABLE `computers` (
`computer_id` int(11) NOT NULL COMMENT '电脑ID',
`model` varchar(255) DEFAULT NULL COMMENT '电脑型号',
`cpu` varchar(255) DEFAULT NULL COMMENT 'CPU',
`graphics_card` varchar(255) DEFAULT NULL COMMENT '显卡',
`monitor` varchar(255) DEFAULT NULL COMMENT '显示器',
`status` tinyint(4) DEFAULT NULL COMMENT '电脑状态',
PRIMARY KEY (`computer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
二.页面展示
1、首页
2、管理员管理页面
3、顾客信息页面
4、会员卡信息页面
5、电脑信息页面
6、电脑使用记录页面
7、消费和充值记录页面
三.部分代码
1、index.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" isELIgnored="false" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<html>
<head>
<title>主页</title>
<link rel="stylesheet" type="text/css" href="/static/layui/css/layui.css"/>
<script src="/static/layui/layui.js" type="text/javascript" charset="utf-8"></script>
<script src="/static/kindeditor/kindeditor.js" type="text/javascript" charset="utf-8"></script>
<script src="/static/jquery-2.1.4.js" type="text/javascript" charset="utf-8"></script>
<%--<script src="/static/layer/layer.js" type="text/javascript" charset="utf-8"></script>--%>
<script src="/static/mylayer.js" type="text/javascript" charset="utf-8"></script>
</head>
<body class="layui-layout-body">
<div class="layui-layout layui-layout-admin">
<div class="layui-header">
<div class="layui-logo">网吧会员管理系统</div>
<ul class="layui-nav layui-layout-left"> </ul>
<ul class="layui-nav layui-layout-right">
<li class="layui-nav-item">
<c:if test="${user.name != null}">
<a href="javascript:;">
管理员${user.name}
</a>
</c:if>
<c:if test="${user.name == null}">
<a href="javascript:;">
用户${user.name}
</a>
</c:if>
<dl class="layui-nav-child">
<dd>
<a href="javascript:openUser()">基本资料</a>
</dd>
<dd>
<a href="javascript:openPass()">修改密码</a>
</dd>
<dd>
<a href="javascript:openPic()">修改头像</a>
</dd>
</dl>
</li>
<li class="layui-nav-item">
<a href="javascript:login()">管理员登录</a>
</li>
<li class="layui-nav-item">
<a href="javascript:logout()">注销</a>
</li>
</ul>
</div>
<div class="layui-side layui-bg-black">
<div class="layui-side-scroll">
<!-- 左侧导航区域（可配合layui已有的垂直导航） -->
<ul class="layui-nav layui-nav-tree" >
<li class="layui-nav-item layui-nav-itemed">
<a href="javascript:;">电脑上机信息</a>
<dl class="layui-nav-child">
<%--<dd>
<a href="javascript:;"
data-url="/user_list.jsp"
class="site-demo-active">管理员管理</a>
</dd>--%>
<c:if test="${user.name != null}">
<c:if test="${user.identity ==0 ||user.identity == 3}">
<c:if test="${user.status == 0}">
<dd>
<a href="javascript:;"
data-url="computers_list.jsp"
class="site-demo-active">电脑信息</a>
</dd>
<dd>
<a href="javascript:;"
data-url="usageRecords_list.jsp"
class="site-demo-active">电脑使用记录</a>
</dd>
</c:if>
<c:if test="${user.status == 1}">
<dd>
<a href="javascript:;"
data-url="/user_list.jsp"
class="site-demo-active">管理员管理</a>
</dd>
<dd>
<a href="javascript:;"
data-url="/customers_list.jsp"
class="site-demo-active">顾客信息</a>
</dd>
<dd>
<a href="javascript:;"
data-url="membershipCards_list.jsp"
class="site-demo-active">会员卡信息</a>
</dd>
<dd>
<a href="javascript:;"
data-url="computers_list.jsp"
class="site-demo-active">电脑信息</a>
</dd>
<dd>
<a href="javascript:;"
data-url="usageRecords_list.jsp"
class="site-demo-active">电脑使用记录</a>
</dd>
<dd>
<a href="javascript:;"
data-url="transactionRecords_list.jsp"
class="site-demo-active">消费和充值记录</a>
</dd>
</c:if>
</c:if>
<c:if test="${user.identity == 1 ||user.identity == 2}">
<c:if test="${user.status == 0}">
<dd>
<a href="javascript:;"
data-url="computers_list.jsp"
class="site-demo-active">电脑信息</a>
</dd>
<dd>
<a href="javascript:;"
data-url="usageRecords_list.jsp"
class="site-demo-active">电脑使用记录</a>
</dd>
</c:if>
<c:if test="${user.status == 1}">
<dd>
<a href="javascript:;"
data-url="/customers_list.jsp"
class="site-demo-active">顾客信息</a>
</dd>
<dd>
<a href="javascript:;"
data-url="membershipCards_list.jsp"
class="site-demo-active">会员卡信息</a>
</dd>
<dd>
<a href="javascript:;"
data-url="computers_list.jsp"
class="site-demo-active">电脑信息</a>
</dd>
<dd>
<a href="javascript:;"
data-url="usageRecords_list.jsp"
class="site-demo-active">电脑使用记录</a>
</dd>
<dd>
<a href="javascript:;"
data-url="transactionRecords_list.jsp"
class="site-demo-active">消费和充值记录</a>
</dd>
</c:if>
</c:if>
</c:if>
<c:if test="${user.name == null}">
<dd>
<a href="javascript:;"
data-url="computers_list.jsp"
class="site-demo-active">电脑信息</a>
</dd>
<dd>
<a href="javascript:;"
data-url="usageRecords_list.jsp"
class="site-demo-active">电脑使用记录</a>
</dd>
</c:if>
</dl>
</li>
</ul>
</div>
</div>
<div class="layui-body"><!-- 内容主体区域 -->
<iframe name="rightframe" width="99%" height="97%" src="computers_list.jsp"></iframe>
</div>
<div class="layui-footer" style="color: deeppink;background-color: #e9ecef" >祝您上网愉快</div>
</div>
<script type="text/javascript">
var $ = layui.jquery;
var layer = layui.layer;
var element = layui.element;
$('.site-demo-active').click(function() {
window.open($(this).data('url'), "rightframe");
});
element.render();// element.init();
function openURL(url){
window.open(url, "rightframe");
}
function logout() {
layer.confirm(
'您确认要退出么',
{icon:3},
function() {
location.href = '/user?method=logout'
}
);
}
function login() {
layer.confirm(
'只有管理员才能登录哟',
{icon:3},
function() {
location.href = '/user?method=logout'
}
);
}
</script>
</body>
</html>2、UserServlet
package com.project.netbar_membership.controller;
import com.project.netbar_membership.pojo.User;
import com.project.netbar_membership.pojo.query.UserQuery;
import com.project.netbar_membership.service.IUserService;
import com.project.netbar_membership.service.impl.UserServiceImpl;
import com.project.netbar_membership.util.JSONUtil;
import com.project.netbar_membership.util.LayUITableResult;
import com.project.netbar_membership.util.Result;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.io.IOException;
import java.util.HashMap;
import java.util.Map;
@WebServlet("/user")
public class UserServlet extends HttpServlet {
private IUserService userService = (IUserService) new UserServiceImpl();
@Override
protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
req.setCharacterEncoding("UTF-8");
String method = req.getParameter("method");
if (method == null || method.equals("")) {
method = "selectByPage";
}
switch (method) {
case "selectByPage":
selectByPage(req, resp);
break;
case "deleteById":
deleteById(req, resp);
break;
case "deleteAll":
deleteAll(req, resp);
break;
case "add":
add(req, resp);
break;
case "getUserUpdatePage":
getUserUpdatePage(req, resp);
break;
case "update":
update(req, resp);
break;
case "login":
login(req, resp);
break;
case "logout":
logout(req, resp);
break;
case "updateStatus":
updateStatus(req, resp);
break;
}
}
private void updateStatus(HttpServletRequest req, HttpServletResponse resp) {
System.out.println("UserServlet.updateStatus");
String id = req.getParameter("id");
String status = req.getParameter("status");
boolean isSuccess = userService.updateStatus(Integer.parseInt(id), Integer.parseInt(status));
Result result = isSuccess ? Result.ok("更新状态成功") : Result.error("更新状态失败");
JSONUtil.toJSON(resp, result);
}
private void logout(HttpServletRequest req, HttpServletResponse resp) throws IOException {
System.out.println("UserServlet.logout");
//确认注销，清空session数据
HttpSession session = req.getSession();
session.invalidate();
//重定向到登录页面（login.jsp）
resp.sendRedirect(req.getContextPath() + "/login.jsp");
}
private void login(HttpServletRequest req, HttpServletResponse resp) throws IOException {
System.out.println("UserServlet.login");
String username = req.getParameter("username");
String password = req.getParameter("password");
String code = req.getParameter("code");
HttpSession session = req.getSession();
String codeInSession = (String) session.getAttribute("codeInSession");
if (!codeInSession.equalsIgnoreCase(code)) {
// {'code':1, 'msg':'验证码错误'}
Map<String, Object> map = new HashMap<>();
map.put("code", 1);
map.put("msg", "验证码错误");
JSONUtil.toJSON(resp, map);
return;
}
//User user = userService.login(name, MD5Util.MD5Encode(password + MD5Util.SALT));
User user = userService.login(username, password);
if (user != null) {
//该用户是否被禁用
if (user.getStatus() == 0) {
JSONUtil.toJSON(resp, Result.error("该用户被禁用"));
return;
}
//把user作为登录凭证放到Session，
//后面只要判断Session里面有没有user，就知道当前这个用户有没有登录
session.setAttribute("user", user);
JSONUtil.toJSON(resp, Result.ok("登录成功"));
} else {
JSONUtil.toJSON(resp, Result.error("用户名或密码错误"));
}
}
private void update(HttpServletRequest req, HttpServletResponse resp) throws IOException {
System.out.println("UserServlet.update");
//id,image,name,gender,phone,username,password,identity,status
String id = req.getParameter("id");
String image = req.getParameter("image");
String name = req.getParameter("name");
String gender = req.getParameter("gender");
String phone = req.getParameter("phone");
String username = req.getParameter("username");
String password = req.getParameter("password");
String identity = req.getParameter("identity");
String status = req.getParameter("status");
//User user = new User(Integer.parseInt(id), name, MD5Util.MD5Encode(password + MD5Util.SALT), Integer.parseInt(role), nickName, email, phone, image,Integer.parseInt(status));
User user = new User(Integer.parseInt(id), image, name, gender, phone, username, password, Integer.parseInt(identity), Integer.parseInt(status));
boolean isSuccess = userService.update(user);
Result result = isSuccess ? Result.ok("修改成功") : Result.error("修改失败");
JSONUtil.toJSON(resp, result);
}
private void getUserUpdatePage(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
System.out.println("UserServlet.getUserUpdatePage");
String id = req.getParameter("id");
User user = userService.getUserUpdatePage(Integer.parseInt(id));
//把user数据放到req里面
req.setAttribute("user", user);
//转发到user_update.jsp页面进行展示
req.getRequestDispatcher("user_update.jsp").forward(req, resp);
}
private void add(HttpServletRequest req, HttpServletResponse resp) throws IOException {
System.out.println("UserServlet.add");
//id,image,name,gender,phone,username,password,identity,status
String image = req.getParameter("image");
String name = req.getParameter("name");
String gender = req.getParameter("gender");
String phone = req.getParameter("phone");
String username = req.getParameter("username");
String password = req.getParameter("password");
String identity = req.getParameter("identity");
User user = new User(image,name,gender,phone,username,password,Integer.parseInt(identity));
boolean isSuccess = userService.add(user);
Result result = isSuccess ? Result.ok("添加成功") : Result.error("添加失败");
JSONUtil.toJSON(resp, result);
}
private void deleteById(HttpServletRequest req, HttpServletResponse resp) throws IOException {
System.out.println("UserServlet.deleteById");
String id = req.getParameter("id");
boolean isSuccess = userService.deleteById(Integer.parseInt(id));
//resp.sendRedirect(req.getContextPath() + "/user");
Result result = isSuccess ? Result.ok("删除成功") : Result.error("删除失败");
JSONUtil.toJSON(resp, result);
}
private void deleteAll(HttpServletRequest req, HttpServletResponse resp) throws IOException {
System.out.println("UserServlet.deleteAll");
//String id = req.getParameter("id");
String[] ids = req.getParameterValues("ids[]");
boolean isSuccess = userService.deleteAll(ids);
Result result = isSuccess ? Result.ok("删除成功") : Result.error("删除失败");
JSONUtil.toJSON(resp, result);
}
//http://localhost:8080/JavaWeb/user?method=selectByPage&page=1&limit=10
private void selectByPage(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
System.out.println("UserServlet.selectByPage");
String pageStr = req.getParameter("page");
if (pageStr == null || pageStr.equals("")) {
pageStr = "1";
}
String limitStr = req.getParameter("limit");
if (limitStr == null || limitStr.equals("")) {
limitStr = "10";
}
int page = Integer.parseInt(pageStr);
int limit = Integer.parseInt(limitStr);
String name = req.getParameter("name");
String gender = req.getParameter("gender");
UserQuery userQuery = new UserQuery(page, limit, name, gender);
LayUITableResult layUITableResult = userService.selectByPage(userQuery);
System.out.println("LayUITableResult" + layUITableResult);
JSONUtil.toJSON(resp, layUITableResult);
}
}
3、IUserService
package com.project.netbar_membership.service;
import com.project.netbar_membership.pojo.User;
import com.project.netbar_membership.pojo.query.UserQuery;
import com.project.netbar_membership.util.LayUITableResult;
public interface IUserService {
//PageInfo<User> selectByPage(Integer pageNo, Integer pageSize);
LayUITableResult selectByPage(UserQuery userQuery);
boolean deleteById(int id);
boolean add(User user);
User getUserUpdatePage(int id);
boolean update(User user);
User login(String username, String password);
boolean deleteAll(String[] ids);
boolean updateStatus(int id, int status);
}
4、UserServiceImpl
package com.project.netbar_membership.service.impl;
import com.project.netbar_membership.dao.IUserDao;
import com.project.netbar_membership.dao.impl.UserDaoImpl;
import com.project.netbar_membership.pojo.User;
import com.project.netbar_membership.pojo.query.UserQuery;
import com.project.netbar_membership.service.IUserService;
import com.project.netbar_membership.util.LayUITableResult;
import java.util.List;
public class UserServiceImpl implements IUserService {
private IUserDao userDao = new UserDaoImpl();
//业务：为了封装PageInfo，查询两次数据库
@Override
public LayUITableResult selectByPage(UserQuery userQuery) {
List<User> list = userDao.selectByPage(userQuery);
Integer totalCount = userDao.selectTotalCount(userQuery);
return LayUITableResult.ok(totalCount, list);
}
@Override
public boolean deleteById(int id) {
int deleteCount = userDao.deleteById(id);
return deleteCount == 1 ? true : false;
}
@Override
public boolean add(User user) {
int addCount = userDao.add(user);
return addCount == 1 ? true : false;
}
@Override
public User getUserUpdatePage(int id) {
User user = userDao.getUserUpdatePage(id);
return user;
}
@Override
public boolean update(User user) {
int updateCount = userDao.update(user);
return updateCount == 1 ? true : false;
}
@Override
public User login(String username, String password) {
return userDao.login(username, password);
}
@Override
public boolean deleteAll(String[] ids) {
// delete from user where id in();
int count = 0;
for (String id : ids) {
count += userDao.deleteById(Integer.parseInt(id));
}
return count == ids.length ? true : false;
}
@Override
public boolean updateStatus(int id, int status) {
int count = userDao.updateStatus(id, status);
return count == 1 ? true : false;
}
}
5、IUserDao
package com.project.netbar_membership.dao;
import com.project.netbar_membership.pojo.User;
import com.project.netbar_membership.pojo.query.UserQuery;
import java.util.List;
public interface IUserDao {
List<User> selectByPage(UserQuery userQuery);
Integer selectTotalCount(UserQuery userQuery);
int deleteById(int id);
int add(User user);
User getUserUpdatePage(int id);
int update(User user);
User login(String username, String password);
int updateStatus(int id, int status);
}
6、UserDaoImpl
package com.project.netbar_membership.dao.impl;
import com.project.netbar_membership.dao.IUserDao;
import com.project.netbar_membership.dao.IUserDao;
import com.project.netbar_membership.pojo.User;
import com.project.netbar_membership.pojo.query.UserQuery;
import com.project.netbar_membership.util.JDBCUtil;
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 UserDaoImpl implements IUserDao {
@Override
public List<User> selectByPage(UserQuery userQuery) {
int offset = (userQuery.getPage() - 1) * userQuery.getLimit();
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
List<User> list = new ArrayList<>();
try {
connection = JDBCUtil.getConnection();
//id,image,name,gender,phone,username,password,identity,status
String sql = "SELECT id,image,name,gender,phone,username,password,identity,status FROM user where 1=1 ";
//这里面放所有搜索条件
List<Object> queryList = new ArrayList<>();
String queryName = userQuery.getName();
if (queryName != null && !"".equals(queryName)) {
sql += " and name like ? ";
//queryList.add(queryName);
queryList.add("%" + queryName + "%");
}
String queryGender = userQuery.getGender();
if (queryGender != null && !"".equals(queryGender)) {
sql += " and gender=? ";
queryList.add(queryGender);
}
sql += " limit ?,? ";
preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < queryList.size(); i++) {
preparedStatement.setObject(i + 1, queryList.get(i));
}
preparedStatement.setInt(queryList.size() + 1, offset);
preparedStatement.setInt(queryList.size() + 2, userQuery.getLimit());
System.out.println(preparedStatement);
resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
//id,image,name,gender,phone,username,password,identity,status
int id = resultSet.getInt("id");
String image = resultSet.getString("image");
String name = resultSet.getString("name");
String gender = resultSet.getString("gender");
String phone = resultSet.getString("phone");
String username = resultSet.getString("username");
String password = resultSet.getString("password");
int identity = resultSet.getInt("identity");
int status = resultSet.getInt("status");
User user = new User(id, image, name, gender, phone, username, password, identity, status);
list.add(user);
}
for (User user : list) {
System.out.println(user);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JDBCUtil.close(connection, preparedStatement, resultSet);
}
return list;
}
@Override
public Integer selectTotalCount(UserQuery userQuery) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
int totalCount = 0;
try {
connection = JDBCUtil.getConnection();
//String sql = "select count(*) from user";
String sql = "select count(*) from user where 1=1 ";
List<Object> queryList = new ArrayList<>();
String queryName = userQuery.getName();
if (queryName != null && !"".equals(queryName)) {
sql += " and name like ? ";
queryList.add(queryName);
}
String queryGender = userQuery.getGender();
if (queryGender != null && !"".equals(queryGender)) {
sql += " and gender=? ";
queryList.add(queryGender);
}
preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < queryList.size(); i++) {
preparedStatement.setObject(i + 1, queryList.get(i));
}
System.out.println(preparedStatement);
resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
totalCount = resultSet.getInt(1);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally{
JDBCUtil.close(connection, preparedStatement, resultSet);
}
return totalCount;
}
@Override
public int deleteById(int id) {
Connection connection = null;
PreparedStatement preparedStatement = null;
int count = 0;
try {
connection = JDBCUtil.getConnection();
String sql = "delete from user where id=?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1, id);
System.out.println(preparedStatement);
count = preparedStatement.executeUpdate();
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JDBCUtil.close(connection, preparedStatement, null);
}
return count;
}
@Override
public int add(User user) {
Connection connection = null;
PreparedStatement preparedStatement = null;
int count = 0;
try {
connection = JDBCUtil.getConnection();
//id,image,name,gender,phone,username,password,identity,status
String sql = "insert into user(image,name,gender,phone,username,password,identity) values(?,?,?,?,?,?,?)";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, user.getImage());
preparedStatement.setString(2, user.getName());
preparedStatement.setString(3, user.getGender());
preparedStatement.setString(4, user.getPhone());
preparedStatement.setString(5, user.getUsername());
preparedStatement.setString(6, user.getPassword());
preparedStatement.setInt(7, user.getIdentity());
System.out.println(preparedStatement);
count = preparedStatement.executeUpdate();
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JDBCUtil.close(connection, preparedStatement, null);
}
return count;
}
@Override
public User getUserUpdatePage(int id) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
User user = null;
try {
connection = JDBCUtil.getConnection();
//id,image,name,gender,phone,username,password,identity,status
String sql = "select id,image,name,gender,phone,username,password,identity,status from user where id=?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1, id);
System.out.println(preparedStatement);
resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
String image = resultSet.getString("image");
String name = resultSet.getString("name");
String gender = resultSet.getString("gender");
String phone = resultSet.getString("phone");
String username = resultSet.getString("username");
String password = resultSet.getString("password");
int identity = resultSet.getInt("identity");
int status = resultSet.getInt("status");
user = new User(id, image, name, gender, phone, username, password, identity, status);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return user;
}
@Override
public int update(User user) {
Connection connection = null;
PreparedStatement preparedStatement = null;
int updateCount = 0;
try {
connection = JDBCUtil.getConnection();
//id,image,name,gender,phone,username,password,identity,status
String sql = "update user set image=?,name=?,gender=?,phone=?,username=?,password=?,identity=?,status=? where id=?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, user.getImage());
preparedStatement.setString(2, user.getName());
preparedStatement.setString(3, user.getGender());
preparedStatement.setString(4, user.getPhone());
preparedStatement.setString(5, user.getUsername());
preparedStatement.setString(6, user.getPassword());
preparedStatement.setInt(7, user.getIdentity());
preparedStatement.setInt(8, user.getStatus());
preparedStatement.setInt(9, user.getId());
System.out.println(preparedStatement);
updateCount = preparedStatement.executeUpdate();
//System.out.println(updateCount);
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JDBCUtil.close(connection, preparedStatement, null);
}
return updateCount;
}
@Override
public User login(String username, String password) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
User user = null;
try {
connection = JDBCUtil.getConnection();
//id,image,name,gender,phone,username,password,identity,status
String sql = "SELECT id,image,name,gender,phone,username,password,identity,status FROM user where username=? and password=?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, username);
preparedStatement.setString(2, password);
System.out.println(preparedStatement);
resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {//判断下一个有没有，如果返回true而且指向下一个，没有返回false
//id,image,name,gender,phone,username,password,identity,status
int id = resultSet.getInt("id");
String image = resultSet.getString("image");
String name = resultSet.getString("name");
String gender = resultSet.getString("gender");
String phone = resultSet.getString("phone");
int identity = resultSet.getInt("identity");
int status = resultSet.getInt("status");
user = new User(id, image, name, gender, phone, username, password, identity, status);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JDBCUtil.close(connection, preparedStatement, resultSet);
}
return user;
}
@Override
public int updateStatus(int id, int status) {
Connection connection = null;
PreparedStatement preparedStatement = null;
int count = 0;
try {
connection = JDBCUtil.getConnection();
String sql = "update user set status=? where id=?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1, status);
preparedStatement.setInt(2, id);
System.out.println(preparedStatement);
count = preparedStatement.executeUpdate();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return count;
}
}
四.配置文件
1、db.properties
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/netbar_membership?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2b8
username=root
password=1234562、pom.xml
<?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>org.example</groupId>
<artifactId>netbar_membership</artifactId>
<version>1.0-SNAPSHOT</version>
<packaging>war</packaging>
<name>netbar_membership Maven Webapp</name>
<!-- FIXME change it to the project's website -->
<url>http://www.example.com</url>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<maven.compiler.source>1.7</maven.compiler.source>
<maven.compiler.target>1.7</maven.compiler.target>
</properties>
<dependencies>
<dependency>
<groupId>jstl</groupId>
<artifactId>jstl</artifactId>
<version>1.2</version>
</dependency>
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>javax.servlet-api</artifactId>
<version>3.1.0</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>jsp-api</artifactId>
<version>2.0</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>com.mysql</groupId>
<artifactId>mysql-connector-j</artifactId>
<version>8.0.31</version>
</dependency>
<!--Jackson包-->
<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-core</artifactId>
<version>2.9.0</version>
</dependency>
<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-databind</artifactId>
<version>2.9.0</version>
</dependency>
<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-annotations</artifactId>
<version>2.9.0</version>
</dependency>
<!--lombok-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.14</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
<version>1.3.1</version>
</dependency>
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.4</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.11</version>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<finalName>netbar_membership</finalName>
<pluginManagement><!-- lock down plugins versions to avoid using Maven defaults (may be moved to parent pom) -->
<plugins>
<plugin>
<groupId>org.apache.tomcat.maven</groupId>
<artifactId>tomcat7-maven-plugin</artifactId>
<version>2.2</version>
<configuration>
<port>8070</port>
<path>/</path>
<uriEncoding>UTF-8</uriEncoding>
<!-- tomcat虚拟映射路径 -->
<staticContextPath>/pic</staticContextPath>
<!-- 设置本地存放图片的路径-->
<staticContextDocbase>D:/mypic/</staticContextDocbase>
<contextReloadable>true</contextReloadable>
<useTestClasspath>true</useTestClasspath>
</configuration>
</plugin>
<plugin>
<artifactId>maven-clean-plugin</artifactId>
<version>3.1.0</version>
</plugin>
<!-- see http://maven.apache.org/ref/current/maven-core/default-bindings.html#Plugin_bindings_for_war_packaging -->
<plugin>
<artifactId>maven-resources-plugin</artifactId>
<version>3.0.2</version>
</plugin>
<plugin>
<artifactId>maven-compiler-plugin</artifactId>
<version>3.8.0</version>
</plugin>
<plugin>
<artifactId>maven-surefire-plugin</artifactId>
<version>2.22.1</version>
</plugin>
<plugin>
<artifactId>maven-war-plugin</artifactId>
<version>3.2.2</version>
</plugin>
<plugin>
<artifactId>maven-install-plugin</artifactId>
<version>2.5.2</version>
</plugin>
<plugin>
<artifactId>maven-deploy-plugin</artifactId>
<version>2.8.2</version>
</plugin>
</plugins>
</pluginManagement>
</build>
</project>
扫一扫
1692
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
