DBConnectionUtil
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class DBConnectionUtil {
private static String jdbcDriver = "";
private static String jdbcURL = "";
private static String jdbcUser = "";
private static String jdbcPassword = "";
static{
//静态初始化只会在类加载的时候执行一次
//读取properties文件
InputStream is = DBConnectionUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties p = new Properties();
try {
p.load(is);
jdbcDriver = p.getProperty("jdbcDriver");
jdbcURL = p.getProperty("jdbcURL");
jdbcUser = p.getProperty("jdbcUser");
jdbcPassword = p.getProperty("jdbcPassword");
} catch (Exception e1) {
// TODO: handle exception
e1.printStackTrace();
}
}
public static Connection getConnection(){
Connection conn = null;
try {
Class.forName(jdbcDriver);
conn = DriverManager.getConnection(jdbcURL, jdbcUser, jdbcPassword);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
public static void main(String[] args) {
System.out.println(getConnection());
}
public static void closeAll(ResultSet rs,Statement st,Connection conn){
try {
if(rs != null)
rs.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if(st != null)
st.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if(conn != null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
jsp页面
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@page import="com.beiruan.util.PageResult"%>
<!-- 这句话很关键不要写漏了--!>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<base href="<%=basePath%>">
<title>My JSP 'userlist.jsp' starting page</title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<!--
<link rel="stylesheet" type="text/css" href="styles.css">
-->
<script type="text/javascript" src="js/jquery-3.3.1.min.js"></script>
<script type="text/javascript">
function deleteUserByUserId(userId){
//alert(userId);
//提示框
if(confirm("你确定要删除吗？")){
$.post("userServlet?flag=1",{"userId":userId},function(data){
if(data=="1"){
$(obj).parent().parent().remove();
alert("删除成功！");
}else{
alert("删除失败！");
}
});
}
}
</script>
</head>
<body>
<div align="center">
<h1 style="color: red;">用户信息</h1>
<hr>
<table border="1px">
<tr>
<th>序号</th>
<th>用户名</th>
<th>性别</th>
<th>密码</th>
<th>状态</th>
<th>操作</th>
</tr>
<c:forEach var="user" items="${pageResult.results}" varStatus="index">
<tr>
<td>${(pageResult.pageNO-1)*pageResult.pageSize +index.count }</td>
<td>${user.username }</td>
<td>${user.sex }</td>
<td>${user.password }</td>
<td>${user.flag }</td>
<!-- 把超链接变成死链接 -->
<td><a href="javascript:void(0)" onclick="deleteUserByUserId(${user.userId},this)">删除</a></td>
</tr>
</c:forEach>
</table>
<%
PageResult pageResult = (PageResult)request.getAttribute("pageResult");
int pageCount = pageResult.getPageCount();
int pageNO = pageResult.getPageNO();
int start = pageNO-5 <= 0? 1:pageNO-5;
int end = start+10<pageCount ? start+10 : pageCount;
if(pageNO > 1){
%>
<a href="userServlet?pageNO=1&pageSize=10">首页</a>
<a href="userServlet?pageNO=<%=pageNO-1 %>&pageSize=10">上一页</a>
<%
}
for(int i=start;i<=end;i++){
%>
<a style="color: <%=(i== pageNO?"red":"") %>;" href="userServlet?pageNO=<%=i %>&pageSize=10"><%=i %></a>
<%
}
if(pageNO < pageCount){
%>
<a href="userServlet?pageNO=<%=pageCount %>&pageSize=10">末页</a>
<a href="userServlet?pageNO=<%=pageNO+1 %>&pageSize=10">下一页</a>
<%
}
%>
<form action="userServlet">
跳转到<input type="text" name="pageNO" value="${pageResult.pageNO }">页
<input type="submit" value="确定"/>
</form>
查询出${pageResult.count }条记录
<hr>
<h3 align="center" style="color: pink">ICP备案号:蜀ICP备06020089号-1</h3>
<!--
<c:forEach begin="1" end="10" varStatus="index">
<a>${index.count }</a>
</c:forEach>
-->
</div>
</body>
</html>
page实体类
//满足条件的记录条数
private int count;
//当前页面
private int pageNo;
//每页显示的条数
private int pageSize;
//总的页数
private int pageCount;
//当前页显示的内容
private List results;
user实体类
private int userId;
private String username;
private String password;
private int sex;
private int flag;
userdao接口
public List<User> getAllUserList();
public int getAllUserCount();
public List<User> getUserListPage(int pageNO,int pageSize);
public int deleteUserByUserId(int userId);
userdaoimpl实现类
//获取数据库user里面所有信息
public List<User> getAllUserList() {
List<User> userList = new ArrayList<User>();
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = DBConnectionUtil.getConnection();
String sql = "SELECT * FROM USER";
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
while(rs.next()){
User user = new User();
user.setUserId(rs.getInt("userId"));
user.setUsername(rs.getString("username"));
user.setSex(rs.getInt("sex"));
user.setPassword(rs.getString("password"));
user.setFlag(rs.getInt("flag"));
userList.add(user);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
DBConnectionUtil.closeAll(rs, ps, conn);
}
return userList;
}
@Override
//分页
public List<User> getUserListPage(int pageNO, int pageSize) {
List<User> userList = new ArrayList<User>();
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = DBConnectionUtil.getConnection();
//？？ 第一个是起始 ？是固定值
String sql = "SELECT * FROM USER order by userId asc limit ?,?";
ps = conn.prepareStatement(sql);
ps.setInt(1, (pageNO-1)*pageSize);
ps.setInt(2, pageSize);
rs = ps.executeQuery();
while(rs.next()){
//取集合中的数据
User user = new User();
user.setUserId(rs.getInt("userId"));
user.setUsername(rs.getString("username"));
user.setSex(rs.getInt("sex"));
user.setPassword(rs.getString("password"));
user.setFlag(rs.getInt("flag"));
userList.add(user);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
DBConnectionUtil.closeAll(rs, ps, conn);
}
return userList;
}
@Override
public int getAllUserCount() {//获取数据库里面总共的数据条数
return 0;
}
@Override
public int deleteUserByUserId(int userId) {//根据用户id删除用户信息
int rows = 0;
Connection conn = null;
PreparedStatement ps = null;
try {
conn = DBConnectionUtil.getConnection();
String sql = "delete from user where userId = ?";
ps = conn.prepareStatement(sql);
ps.setInt(1, userId);
rows = ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
DBConnectionUtil.closeAll(null, ps, conn);
}
return rows;
}
}
userService
service接口
public List<User> getAllUserList();
public int getAllUserCount();
public List<User> getUserListPage(int pageNO,int pageSize);
public int deleteUserByUserId(int userId);
*UserServiceImpl*
public class UserServiceImpl implements UserService {
private UserDao userDao = new UserDaoImpl();
@Override
public List<User> getAllUserList() {
return userDao.getAllUserList();
}
@Override
public List<User> getUserListPage(int pageNO, int pageSize) {
return userDao.getUserListPage(pageNO, pageSize);
}
@Override
public int deleteUserByUserId(int userId) {
return userDao.deleteUserByUserId(userId);
}
}
UserServlet
import java.io.IOException;
import java.io.PrintWriter;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.beiruan.entity.User;
import com.beiruan.service.UserService;
import com.beiruan.service.impl.UserServiceImpl;
import com.beiruan.util.PageResult;
public class UserServlet extends HttpServlet {
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doPost(request, response);
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
int flag = 0;
String flagStr = request.getParameter("flag");
response.setContentType("text/html;charset=UTF-8");
PrintWriter out = response.getWriter();
if(flagStr != null){
flag = Integer.parseInt(flagStr);
}
switch (flag) {
case 0:
getUserListPage(request, response);
break;
case 1:
int userId = Integer.parseInt(request.getParameter("userId"));
int rows= deleteUserByUserId(userId);
if(rows==1)
out.print(1);
else
out.print(0);
out.flush();
out.close();
break;
default:
break;
}
}
public void getUserListPage(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException{
int pageNO = 1;
int pageSize = 10;
String pageNOStr = request.getParameter("pageNO");
String pageSizeStr = request.getParameter("pageSize");
if(pageNOStr != null)
pageNO = Integer.parseInt(pageNOStr);
if(pageSizeStr != null)
pageSize = Integer.parseInt(pageSizeStr);
UserService userService = new UserServiceImpl();
List<User> userList = userService.getUserListPage(pageNO, pageSize);
PageResult pageResult = new PageResult();
pageResult.setPageNO(pageNO);
pageResult.setPageSize(pageSize);
pageResult.setResults(userList);
int count = userService.getAllUserList().size();
pageResult.setCount(count);
if(count % pageSize == 0)
pageResult.setPageCount(count / pageSize);
else
pageResult.setPageCount(count / pageSize + 1);
request.setAttribute("pageResult", pageResult);
request.getRequestDispatcher("/userlist.jsp").forward(request, response);
}
public int deleteUserByUserId(int userId){
UserService userService = new UserServiceImpl();
return userService.deleteUserByUserId(userId);
}
}
197
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
