1.dao层连接数据库
public class BaseDao {
private static String driver;
private static String url;
private static String username;
private static String password;
static { //类静态加载
Properties properties = new Properties();
InputStream is = BaseDao.class.getClassLoader().getResourceAsStream("db.properties");
try {
properties.load(is);
} catch (Exception e) {
e.printStackTrace();
}
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;
}
//查询公共类
public static ResultSet executeQuery(Connection connection,String sql,Object[] params,ResultSet resultSet,PreparedStatement preparedStatement) throws SQLException {
preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < params.length; i++) {
preparedStatement.setObject(i+1,params[i]);
}
resultSet=preparedStatement.executeQuery();
return resultSet;
}
//释放资源
public static boolean closeResource(Connection connection,PreparedStatement preparedStatement,ResultSet resultSet){
boolean flag=true;
if (resultSet!=null){
try {
resultSet.close();
resultSet=null;
} catch (SQLException e) {
e.printStackTrace();
flag=false;
}
}
if (preparedStatement!=null){
try {
preparedStatement.close();
preparedStatement=null;
} catch (SQLException e) {
e.printStackTrace();
flag=false;
}
}
if (connection!=null){
try {
connection.close();
connection=null;
} catch (SQLException e) {
e.printStackTrace();
flag=false;
}
}
return flag;
}
}
db.properties
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/...
username=root
password=123456
2.dao层
(1)userDao接口
public interface UserDao {
public User getLoginUser(Connection connection,String userCode);
//查询所有
public List<User> queryUserList(Connection connection,int startIndex,int pageSize);
//查询总人数
public int UserCount(Connection connection);
}
(2)userDaoImpl实现类
public class UserDaoImpl implements UserDao{
public List<User> queryUserList(Connection connection,int startIndex,int pageSize) {
List<User> usersList = new ArrayList<User>();
PreparedStatement pstm=null;
ResultSet rs=null;
User user=null;
if (connection!=null){
String sql="select * from user limit ?,?";
Object[] params={startIndex,pageSize};
try {
rs = BaseDao.executeQuery(connection, sql, params, rs, pstm);
while (rs.next()){
User user1=new User();
user1.setUserCode(rs.getString("userCode"));
user1.setUserName(rs.getString("userName"));
user1.setSex(rs.getString("sex"));
user1.setAge(rs.getInt("age"));
user1.setWork(rs.getString("work"));
user1.setPassword(rs.getString("password"));
usersList.add(user1);
}
BaseDao.closeResource(null,pstm,rs);
} catch (SQLException e) {
e.printStackTrace();
}
}
return usersList;
}
public int UserCount(Connection connection) {
PreparedStatement pstm=null;
ResultSet rs=null;
int totalCount=0;
if (connection!=null) {
String sql = "select count(1) as total from user";
Object[] params = {};
try {
rs = BaseDao.executeQuery(connection, sql, params, rs, pstm);
while (rs.next()){
totalCount=rs.getInt("total");break;
}
BaseDao.closeResource(null, pstm, rs);
} catch (SQLException e) {
e.printStackTrace();
}
}
return totalCount;
}
}
3.service层
(1)userService接口
public interface UserService {
public User login(String userCode,String password);
//查询所有
public List<User> queryUserList(int startIndex,int pageSize);
//查询总人数
public int UserCount();
}
(2)userServiceImpl实现类
public class UserServiceImpl implements UserService{
private UserDao userDao;
public UserServiceImpl() {
userDao=new UserDaoImpl();
}
public List<User> queryUserList(int startIndex,int pageSize) {
List<User> usersList = new ArrayList<User>();
Connection connection=null;
connection=BaseDao.getConnection();
usersList=userDao.queryUserList(connection,startIndex,pageSize);
BaseDao.closeResource(connection,null,null);
return usersList;
}
public int UserCount() {
Connection connection=null;
connection=BaseDao.getConnection();
int count = userDao.UserCount(connection);
BaseDao.closeResource(connection,null,null);
return count;
}
}
4.servlet层
public class UserServlet extends HttpServlet {
private UserServiceImpl userService = new UserServiceImpl();
private int total=0;
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
String method = req.getParameter("method");
System.out.println("method==>"+method);
if (method.equals("queryUserList")){
this.queryUserList(paging("first",1),req,resp);
}else if (method.equals("paging")){
String pageMethod = req.getParameter("pageMethod");
String page = req.getParameter("page");
this.queryUserList(paging(pageMethod,Integer.parseInt(page)),req,resp);
}
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
doGet(req, resp);
}
public void queryUserList(int page,HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
List<User> userList=new ArrayList<User>();
System.out.println("total==>"+total);
System.out.println("page===>"+page);
//int currentPage=total/5+1;
if (total%5!=0 && page==(total/5+1)){
userList = userService.queryUserList((page-1)*5, (total-(page-1)*5));
}else {
userList = userService.queryUserList((page-1)*5, 5);
}
for (User user : userList) {
System.out.println(user.toString());
}
req.setAttribute("page",page);
req.setAttribute("userList",userList);
req.getRequestDispatcher("userList.jsp").forward(req,resp);
}
public int paging(String pageMethod,int page){ //分页控制上下翻页
int totalPage=1;
int totalCount = userService.UserCount();
total=totalCount;
if (totalCount%5==0){ //假设每页有5条信息
totalPage=totalCount/5;
}else {
totalPage=totalCount/5+1;
}
System.out.println("totalPage==>"+totalPage);
if (pageMethod.equals("first")){
page=1;
}else if (pageMethod.equals("reduce")){
if (page<2){
page=1;
}else {
page--;
}
}else if (pageMethod.equals("add")){
if (page>=totalPage){
page=totalPage;
}else {
page++;
}
}else if (pageMethod.equals("last")){
page=totalPage;
}
return page;
}
}
5.前端jsp
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>用户列表</title>
<link href="https://cdn.staticfile.org/twitter-bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet">
</head>
<body>
<div class="container">
<div class="row clearfix">
<div class="col-md-12 column">
<div class="page-header">
<h1><small>用户列表——————显示所有用户</small></h1>
</div>
</div>
</div>
<div class="row">
<div class="col-md-4 column">
<form class="form-inline" action="/user" method="get" style="float: left">
<input type="hidden" name="method" value="queryUserList">
<input type="submit" id="btn" value="查询" class="btn btn-primary">
</form>
</div>
<div class="col-md-4 column">
</div>
<div class="col-md-4 column">
</div>
</div>
<div class="row clearfix">
<div class="col-md-12 column">
<table class="table table-hover table-striped">
<thead>
<tr>
<th>用户编码</th>
<th>用户名字</th>
<th>用户性别</th>
<th>用户年龄</th>
<th>用户职业</th>
<th>用户密码</th>
</tr>
</thead>
<tbody>
<c:forEach var="user" items="${userList}">
<tr>
<td>${user.userCode}</td>
<td>${user.userName}</td>
<td>${user.sex}</td>
<td>${user.age}</td>
<td>${user.work}</td>
<td>${user.password}</td>
</tr>
</c:forEach>
</tbody>
</table>
<a href="/user?method=paging&pageMethod=first&page=${page}" class="btn btn-primary">首页</a>
<a href="/user?method=paging&pageMethod=reduce&page=${page}" class="btn btn-primary">上一页</a>
<a href="/user?method=paging&pageMethod=add&page=${page}" class="btn btn-primary">下一页</a>
<a href="/user?method=paging&pageMethod=last&page=${page}" class="btn btn-primary">尾页</a>
</div>
</div>
</div>
</body>
</html>
6.效果图:点击查询,显示分页查询结果,可以上下翻页。