基于MVC模式的資料庫綜合練習

一.準備

        沒什麼好說的，直接上代碼。。。。

       下面是web.xml

<servlet>      

<servlet-name>list_user</servlet-name>      

<servlet-class>servlet.UserList</servlet-class>      

</servlet>      

<display-name>servlet_2</display-name>      

<servlet-mapping>      

<servlet-name>list_user</servlet-name>      

<url-pattern>/userList</url-pattern>      

</servlet-mapping>      

       文檔結構如下

二.代碼實作

package bean;      

import java.util.List;      

public class PageBean {      

private int rowCount;      

private int pageSize;      

private int pageCount;      

private int pageNum;      

private List contents;      

//下面是get、set方法      

}      

package bean;      

import java.util.Date;      

public class User {      

private int id;      

private String username;      

private String password;      

private Date birthday;      

//下面是get/set      

}      

package dao;      

import java.sql.Connection;      

import java.sql.DriverManager;      

import java.sql.ResultSet;      

import java.sql.SQLException;      

import java.sql.Statement;      

public class DbTool {      

public static Connection getConnection(){      

Connection conn = null;      

try{      

Class.forName("com.mysql.jdbc.Driver");      

String url = "jdbc:mysql://127.0.0.1/db_user";      

String uid = "root";      

String pid = "root";      

conn = DriverManager.getConnection(url,uid,pid);      

}catch(Exception exp){      

exp.printStackTrace();      

}      

return conn;      

}      

public static void closeConnection(Connection conn){      

try {      

if(null != conn && !conn.isClosed()){      

conn.close();      

}      

} catch (SQLException e) {      

e.printStackTrace();      

}      

}      

public static Object getOneResult(String cmd){      

Object o = null;      

Connection conn = getConnection();      

try {      

Statement st = conn.createStatement();      

ResultSet rs = st.executeQuery(cmd);      

if(rs.next()){      

o = rs.getObject(1);      

}      

rs.close();      

} catch (SQLException e) {      

e.printStackTrace();      

}      

closeConnection(conn);      

return o;      

}      

public static int execute(String cmd){      

Connection conn = getConnection();      

int result = 0;      

try {      

Statement st = conn.createStatement();      

result = st.executeUpdate(cmd);      

} catch (SQLException e) {      

e.printStackTrace();      

}      

closeConnection(conn);      

return result;      

}      

}      

package dao;      

import java.sql.Connection;      

import java.sql.ResultSet;      

import java.sql.Statement;      

import java.text.SimpleDateFormat;      

import java.util.ArrayList;      

import java.util.List;      

import bean.PageBean;      

import bean.User;      

public class UserDao {      

public static void add(User u){      

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");      

String cmd = "insert into t_user (username,password,birthday) values('"+      

u.getUsername()+"','"+      

u.getPassword()+"','"+      

sdf.format(u.getBirthday())+"')";      

DbTool.execute(cmd);      

}      

public static void delete(int id){      

String cmd = "delete from t_user where id="+id;      

DbTool.execute(cmd);      

}      

public static void update(User u){      

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");      

String cmd = "update t_user set username='"+      

u.getUsername()+"',password='"+      

u.getPassword()+"',birthday='"+      

sdf.format(u.getBirthday())+"' where id="+      

u.getId();      

DbTool.execute(cmd);      

}      

public static int getUsersCount(){      

int count = 0;      

String cmd = "select count(*) from t_user";      

Object o = DbTool.getOneResult(cmd);      

if(null != o){      

count = ((Long)o).intValue();      

}      

return count;      

}      

public static PageBean getSomeUser(int pageSize,int pageNum){      

PageBean pb = new PageBean();      

int rowCount = getUsersCount();      

int pageCount = rowCount%pageSize ==0 ? rowCount/pageSize: rowCount/pageSize+1;      

if(pageNum<1)pageNum = 1;      

if(pageNum>pageCount)pageNum=pageCount;      

int first = (pageNum-1)*pageSize;      

String cmd = "select * from t_user limit "+first+","+pageSize;      

Connection conn = DbTool.getConnection();      

List<User> users = new ArrayList<User>();      

try{      

Statement st = conn.createStatement();      

ResultSet rs = st.executeQuery(cmd);      

while (rs.next()){      

User u = new User();      

u.setId(rs.getInt(1));      

u.setUsername(rs.getString(2));      

u.setPassword(rs.getString(3));      

u.setBirthday(rs.getDate(4));      

users.add(u);      

}      

}catch(Exception exp){      

exp.printStackTrace();      

}      

DbTool.closeConnection(conn);      

pb.setContents(users);      

pb.setPageCount(pageCount);      

pb.setPageNum(pageNum);      

pb.setPageSize(pageSize);      

pb.setRowCount(rowCount);      

return pb;      

}      

}      

package servlet;      

import java.io.IOException;      

import javax.servlet.RequestDispatcher;      

import javax.servlet.ServletException;      

import javax.servlet.http.HttpServlet;      

import javax.servlet.http.HttpServletRequest;      

import javax.servlet.http.HttpServletResponse;      

import dao.UserDao;      

import bean.PageBean;      

public class UserList extends HttpServlet {      

@Override      

protected void service(HttpServletRequest request, HttpServletResponse response)      

throws ServletException, IOException {      

String pn = request.getParameter("pageNum");      

int pageNum = 1;      

if(null != pn && pn.length()>0){      

pageNum = Integer.parseInt(pn);      

}      

PageBean pb = UserDao.getSomeUser(5, pageNum);      

request.setAttribute("pageBean", pb);      

RequestDispatcher rd = request.getRequestDispatcher("user_list.jsp");      

rd.forward(request, response);      

}      

}      

<%@page import="java.util.Date"%>      

<%@page import="java.text.SimpleDateFormat"%>      

<%@page import="bean.User"%>      

<%@page import="java.util.List"%>      

<%@page import="bean.PageBean"%>      

<%@ page language="java" contentType="text/html; charset=UTF-8"      

pageEncoding="UTF-8"%>      

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">      

<html>      

<head>      

<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">      

<title>Insert title here</title>      

</head>      

<body>      

<table>      

<tr>      

<td>id</td>      

<td>使用者名</td>      

<td>密碼</td>      

<td>生日</td>      

</tr>      

<%      

PageBean pb = (PageBean)request.getAttribute("pageBean");      

List<User> users = (List<User>)pb.getContents();      

int pageNum = pb.getPageNum();      

int pageCount = pb.getPageCount();      

int pageSize = pb.getPageSize();      

int rowCount = pb.getRowCount();      

for(User u : users){      

Date bir = u.getBirthday();      

String birthday = new SimpleDateFormat("yyyy-MM-dd").format(bir);      

%>      

<tr>      

<td><%=u.getId() %></td>      

<td><%=u.getUsername() %></td>      

<td><%=u.getPassword()%></td>      

<td><%=birthday %></td>      

</tr>      

<%      

}      

%>      

</table>      

共<%=rowCount %>條記錄，目前是第<%=pageNum %> / <%=pageCount %>頁，每頁顯示<%=pageSize %>條記錄。<br/>      

<a href="userList?pageNum=1">第一頁</a>      

<a href="userList?pageNum=<%=pageNum-1 %>">上一頁</a>      

<a href="userList?pageNum=<%=pageNum+1 %>">下一頁</a>      

<a href="userList?pageNum=<%=pageCount %>">最後頁</a>      

</body>      

</html>      

三.結束語

       雖然技術上不是問題，不過怎麼分包卻是大問題，目前還沒有好辦法。。。。。

​