完整的CRUD——javaweb
1，总体架构
index是进去的页面，
可以跳转Insert的增加页面，operatePerson是根据传进来的URI来判断增删改查的页面，
DbManager.java是封装的数据库操作类, Pagination.java是页脚的信息栏
2，index页面
final int pageSize = 5;//一页显示多少纪录 int pageNum = 1;//当前页数 int pageCount = 1;//总页数 int recordCount = 0;//总记录数
<form action="operatePerson.jsp" method="post"> <table border="1"> <% for(; resultSet.next();){ out.println("<tr>"); String name = resultSet.getString("name"); out.println("<td>" + name + "</td>"); out.println("<td>" + resultSet.getString("age") + "</td>"); out.println("<td>" + resultSet.getString("sex") + "</td>"); out.println("<td><a href='operatePerson.jsp?action=delete&name=" + name +"'>删除</a></td>"); out.println("<td><a href='operatePerson.jsp?action=edit&name=" + name +"'>修改</a></td>"); out.println("</tr>"); } %> </table> <%=Pagination.getPagination(pageNum, pageCount , recordCount, request.getRequestURI()) %>
画出表，包括页脚，（上面的form其实没有什么用
分页技术使用数据库的 select * from person limit ?,? ----意思是从哪条开始（不包括这一条）的 n条记录
问号使用preparedStatement语句来实现
代码：
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%> <%@ page import="java.sql.DriverManager"%> <%@ page import="java.sql.Connection"%> <%@ page import="java.sql.Statement"%> <%@ page import="java.sql.ResultSet"%> <%@ page import="java.sql.PreparedStatement"%> <%@ page import="java.sql.SQLException"%> <%@ page import="testPackage.DbManager" %> <%@ page import="testPackage.Pagination" %> <% 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 'index.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"> --> </head> <% final int pageSize = 5;//一页显示多少纪录 int pageNum = 1;//当前页数 int pageCount = 1;//总页数 int recordCount = 0; try{//当前页数 pageNum = Integer.parseInt(request.getParameter("pageNum")); System.out.println("0000:" + pageNum); }catch(Exception e){} String sql = null; Connection con = null; PreparedStatement preparedStatement = null; ResultSet resultSet = null; try{ sql = "select count(*) from person"; recordCount = DbManager.getCount(sql); pageCount = (recordCount + pageSize - 1) / pageSize; int startRecord = (pageNum - 1) * pageSize; sql = "select * from person limit ?,?"; con = DbManager.getConnection(); preparedStatement = con.prepareStatement(sql); DbManager.setParams(preparedStatement, startRecord, pageSize); resultSet = preparedStatement.executeQuery(); %> <body> <a href="Insert.jsp">新建一个</a> <form action="operatePerson.jsp" method="post"> <table border="1"> <% for(; resultSet.next();){ out.println("<tr>"); String name = resultSet.getString("name"); out.println("<td>" + name + "</td>"); out.println("<td>" + resultSet.getString("age") + "</td>"); out.println("<td>" + resultSet.getString("sex") + "</td>"); out.println("<td><a href='operatePerson.jsp?action=delete&name=" + name +"'>删除</a></td>"); out.println("<td><a href='operatePerson.jsp?action=edit&name=" + name +"'>修改</a></td>"); out.println("</tr>"); } %> </table> <%=Pagination.getPagination(pageNum, pageCount , recordCount, request.getRequestURI()) %> <br/> <%=pageNum %> </form> <% }catch(SQLException e1){ out.println("0001" + e1.getMessage()); } finally { if (resultSet != null) resultSet.close(); if (preparedStatement != null) preparedStatement.close(); if (con != null) con.close(); } %> </body> </html>
2，operatePerson.jsp页面
我写的时候没有使用DbManager类，在里面的增删改都用了原始的操作
request.setCharacterEncoding("UTF-8");
response.setCharacterEncoding("UTF-8");//一定要
String action = request.getParameter("action");//先接受是什么操作
增：request.getPararmeter("name...")通过这个方法来获得表传进来的数据
删：request.getPararmeter("name")通过这个方法来获得uri传进来的paramer
改：request.getPararmeter("name")通过这个方法来获得uri传进来的paramer，（我设计的时候没有完善好，默认他们不能有同名，所以不能改名字
request.setAttribute("name", resultSet.getString("name")//把传进来的参数穿进去request里面，用forward转向到insert的页面在，那样insert可以从request里面提取数据（同时也把"save"信息传过去)，和真正的插入有了区别
在insert会判断是save的传递，把数据传回来operate页面，做真正的修改
3，insert页面
会根据传过来的action判断是插入还是修改，以此来修改uri，form表给的提交方式要改为post ,因为这样表格的内容就不能形象uri的传递，
value="${name}"的时候，在修改的时候会显示值，注意：el表达式，${name}或者${requestScope.name}而不是${request.name}
4,Pagination.java
页脚，用StringBuffer类，append字符串
5，DbManager.java
数据库管理类
public static Connection getConnection(String dbName, String userName,String password)
public static Connection getConnection() //获得con对象
public static void setParams(PreparedStatement preparedStatement,Object... params) //把preparedStatement放进去，发若干个参数，preparedStatement.setInt.....之类的
public static int executeUpdate(String sql)
public static int executeUpdate(String sql, Object... params) //对上面方法的封装，穿进去sql的字符串就行了
源码：
DbManager
package testPackage; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.sql.Types; public class DbManager { public static Connection getConnection() throws SQLException, ClassNotFoundException { return getConnection("test", "kooing", ""); } public static Connection getConnection(String dbName, String userName, String password) throws SQLException, ClassNotFoundException { String url = "jdbc:mysql://localhost:3306/" + dbName; Class.forName("com.mysql.jdbc.Driver"); Connection con = DriverManager.getConnection(url); return con; } public static void setParams(PreparedStatement preparedStatement, Object... params) throws SQLException { for (int i = 1; i <= params.length; i++) { Object param = params[i - 1]; if (param == null) { preparedStatement.setNull(i, Types.NULL); } else if (param instanceof Integer) { preparedStatement.setInt(i, (Integer) param); } else if (param instanceof String) { preparedStatement.setString(i, (String) param); } } } public static int executeUpdate(String sql) throws SQLException, ClassNotFoundException { return executeUpdate(sql, new Object[] {}); } public static int executeUpdate(String sql, Object... params) throws SQLException, ClassNotFoundException { Connection con = null; PreparedStatement preparedStatement = null; try { con = getConnection(); preparedStatement = con.prepareStatement(sql); setParams(preparedStatement, params); return preparedStatement.executeUpdate(); } finally { if (preparedStatement != null) preparedStatement.close(); if (con != null) con.close(); } } public static int getCount(String sql) throws SQLException, ClassNotFoundException { Connection con = null; Statement statement = null; ResultSet rs = null; try { con = getConnection(); statement = con.createStatement(); rs = statement.executeQuery(sql); rs.next(); return rs.getInt(1); } finally { if (rs != null) rs.close(); if (statement != null) statement.close(); if (con != null) con.close(); } } }
Pagination
package testPackage; public class Pagination { public static String getPagination(int pageNum, int pageCount, int recordCount, String pageUrl) { String url = pageUrl.contains("?") ? pageUrl : pageUrl + "?"; if (!url.endsWith("?") && !url.endsWith("&")) { url += "&"; } StringBuffer buffer = new StringBuffer(); buffer.append("第 " + pageNum + "/" + pageCount + "页，共" + recordCount + "纪录。"); buffer.append(pageNum == 1 ? "第一页" : "<a href='" + url + "pageNum=1'>第一页</a>"); // buffer.append(pageNum == 1 ? "第一页" : "<a href='" + url + "pageNum=" + // 1 // + "'>第一页</a>"); buffer.append(pageNum == 1 ? "上一页" : "<a href='" + url + "pageNum=" + (pageNum - 1) + "'>上一页</a>"); buffer.append(pageNum == pageCount ? "下一页" : "<a href='" + url + "pageNum=" + (pageNum + 1) + "'>下一页</a>"); buffer.append(pageNum == pageCount ? "最后一页" : "<a href='" + url + "pageNum=" + pageCount + "'>最后一页</a>"); return buffer.toString(); } }
index
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%> <%@ page import="java.sql.DriverManager"%> <%@ page import="java.sql.Connection"%> <%@ page import="java.sql.Statement"%> <%@ page import="java.sql.ResultSet"%> <%@ page import="java.sql.PreparedStatement"%> <%@ page import="java.sql.SQLException"%> <%@ page import="testPackage.DbManager" %> <%@ page import="testPackage.Pagination" %> <% 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 'index.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"> --> </head> <% final int pageSize = 5;//一页显示多少纪录 int pageNum = 1;//当前页数 int pageCount = 1;//总页数 int recordCount = 0; try{//当前页数 pageNum = Integer.parseInt(request.getParameter("pageNum")); System.out.println("0000:" + pageNum); }catch(Exception e){} String sql = null; Connection con = null; PreparedStatement preparedStatement = null; ResultSet resultSet = null; try{ sql = "select count(*) from person"; recordCount = DbManager.getCount(sql); pageCount = (recordCount + pageSize - 1) / pageSize; int startRecord = (pageNum - 1) * pageSize; sql = "select * from person limit ?,?"; con = DbManager.getConnection(); preparedStatement = con.prepareStatement(sql); DbManager.setParams(preparedStatement, startRecord, pageSize); resultSet = preparedStatement.executeQuery(); %> <body> <a href="Insert.jsp">新建一个</a> <form action="operatePerson.jsp" method="post"> <table border="1"> <% for(; resultSet.next();){ out.println("<tr>"); String name = resultSet.getString("name"); out.println("<td>" + name + "</td>"); out.println("<td>" + resultSet.getString("age") + "</td>"); out.println("<td>" + resultSet.getString("sex") + "</td>"); out.println("<td><a href='operatePerson.jsp?action=delete&name=" + name +"'>删除</a></td>"); out.println("<td><a href='operatePerson.jsp?action=edit&name=" + name +"'>修改</a></td>"); out.println("</tr>"); } %> </table> <%=Pagination.getPagination(pageNum, pageCount , recordCount, request.getRequestURI()) %> <br/> <%=pageNum %> </form> <% }catch(SQLException e1){ out.println("0001" + e1.getMessage()); } finally { if (resultSet != null) resultSet.close(); if (preparedStatement != null) preparedStatement.close(); if (con != null) con.close(); } %> </body> </html>
operate
<%@page import="java.sql.DriverManager"%> <%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%> <%@ page import="java.sql.DriverManager"%> <%@ page import="java.sql.Connection"%> <%@ page import="java.sql.Statement"%> <%@ page import="java.sql.ResultSet"%> <% String path = request.getContextPath(); String basePath = request.getScheme() + "://" + request.getServerName() + ":" + request.getServerPort() + path + "/"; %> <% request.setCharacterEncoding("UTF-8"); response.setCharacterEncoding("UTF-8"); String action = request.getParameter("action"); String sql = null; if ("add".equals(action)) { String name = request.getParameter("name"); int age = Integer.parseInt(request.getParameter("age")); String sex = request.getParameter("sex"); sql = "insert into person values('" + name + "','" + age + "','" + sex + "')"; try { Class.forName("com.mysql.jdbc.Driver"); Connection con = DriverManager.getConnection( "jdbc:mysql://localhost:3306/test", "kooing", ""); Statement sm = con.createStatement(); int resultSet = sm.executeUpdate(sql); out.println(sql); } catch (Exception e) { } } if ("delete".equals(action)) { String name = request.getParameter("name"); System.out.println(name); sql = "delete from person where name = '" + name + "'"; try { Class.forName("com.mysql.jdbc.Driver"); Connection con = DriverManager.getConnection( "jdbc:mysql://localhost:3306/test", "kooing", ""); Statement sm = con.createStatement(); int resultSet = sm.executeUpdate(sql); out.println(sql); } catch (Exception e) { } } if ("edit".equals(action)) { try { String name = request.getParameter("name"); sql = "select * from person where name ='" + name + "'"; Class.forName("com.mysql.jdbc.Driver"); Connection con = DriverManager.getConnection( "jdbc:mysql://localhost:3306/test", "kooing", ""); Statement sm = con.createStatement(); ResultSet resultSet = sm.executeQuery(sql); out.println(sql); resultSet.next(); request.setAttribute("name", resultSet.getString("name")); request.setAttribute("age", resultSet.getString("age")); request.setAttribute("sex", resultSet.getString("sex")); request.setAttribute("action", action); request.getRequestDispatcher("Insert.jsp").forward(request, response); } catch (Exception e) { } } if("save".equals(action)){ String name = request.getParameter("name"); int age = Integer.parseInt(request.getParameter("age")); String sex = request.getParameter("sex"); sql = "update person set " + "name='" + name + "',age='" + age + "',sex='" + sex + "' where name ='" + name +"'"; System.out.println(sql); Class.forName("com.mysql.jdbc.Driver"); Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "kooing", ""); Statement sm = con.createStatement(); int result = sm.executeUpdate(sql); } %> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <base href="<%=basePath%>"> <title>My JSP 'operatePerson.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"> --> </head> <body> This is my JSP page. <br> <a href="index.jsp">主页</a> </body> </html>
insert
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%> <% 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 'Insert.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"> --> </head> <body> <% String myAction = (String)request.getAttribute("action"); myAction = myAction == null? "add" : "save"; System.out.println(myAction); System.out.println(request.getAttribute("name")); System.out.println(request.getAttribute("age")); System.out.println(request.getAttribute("sex")); %> <form action="operatePerson.jsp?action=<%=myAction %>" method="post"> 姓名<input type="text" name="name" value="${name }"><br/> 年龄<input type="text" name="age" value="${age }"><br/> 性别<input type="text" name="sex" value="${sex }"><br/> <input type="submit"> </form> </body> </html>
