基于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>

文档结构如下

基于MVC模式的数据库综合练习

二.代码实现

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>

三.结束语

虽然技术上不是问题,不过怎么分包却是大问题,目前还没有好办法。。。。。

上一篇:Mysql数据库一个表字段中存了id,并以逗号分隔,id对应的详细信息在另一个表中


下一篇:POJ - 2031 Building a Space Station 三维球点生成树Kruskal