首页 > 代码库 > JDBC详解(2)
JDBC详解(2)
12.4 listPagedPerson.jsp
<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%><%@ page import="java.sql.*" %><%@ page import="java.sql.Date" %><%@ page import="com.helloben.util.*" %><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><html> <head> <title>My JSP ‘listPagedPerson.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="http://www.mamicode.com/styles.css"> --> <style type="text/css"> body, td, th, input { font-size:12px; text-align:center; } </style> </head> <body> <% final int pageSize = 10; int pageNum = 1; int pageCount = 1; int recordCount = 0; try { pageNum = Integer.parseInt(request.getParameter("pageNum")); } catch (Exception e){ } Connection conn = null; PreparedStatement preStmt = null; ResultSet rs = null; String sql = null; try { sql = "select count(*) from tb_person "; recordCount = DbManager.getCount(sql); pageCount = (recordCount + pageSize - 1) / pageSize; int startRecord = (pageNum - 1) * pageSize; sql = "select * from tb_person limit ?, ?"; conn = DbManager.getConnection(); preStmt = conn.prepareStatement(sql); DbManager.setParams(preStmt, startRecord, pageSize); rs = preStmt.executeQuery(); %> <form action="operatePerson.jsp" method="get"> <table bgcolor="#CCCCCC" cellspacing=1 cellpadding=5 width=100%> <tr bgcolor=#DDDDDD> <th></th> <th>ID</th> <th>Name</th> <th>English Name</th> <th>Gender</th> <th>Age</th> <th>Birthday</th> <th>Comment</th> <th>Created Time</th> <th>Operation</th> </tr> <% while (rs.next()){ int id = rs.getInt("id"); int age = rs.getInt("age"); String name = rs.getString("name"); String englishName = rs.getString("english_name"); String sex = rs.getString("sex"); String description = rs.getString("description"); Date birthday = rs.getDate("birthday"); Timestamp createTime = rs.getTimestamp("create_time"); %> <tr bgcolor=#FFFFFF> <td><input type="checkbox" name="id" value="<%= id %>"/></td> <td><%= id %></td> <td><%= name %></td> <td><%= englishName %></td> <td><%= sex %></td> <td><%= age %></td> <td><%= birthday %></td> <td><%= description %></td> <td><%= createTime %></td> <td> <a href=‘operatePerson.jsp?action=del&id=" + <%= id %> + "‘ onclick=‘delete_Click()‘>Delete</a> <a href=‘operatePerson.jsp?action=edit&id=" + <%= id %> + "‘>Edit</a> </td> </tr> <% } %> </table> <table align=right> <tr> <td><%=Pagination.getPagination(pageNum, pageCount, recordCount, request.getRequestURI())%> </td> </tr> </table> <br /> <br /> <br /> <table align=left> <tr> <td>SQL: <%=sql%></td> </tr> </table> </form> <% } catch (SQLException e){ out.println("Exception occurs when execute the SQL of " + sql + ", the message is: " + e.getMessage()); e.printStackTrace(); } finally { if (rs != null) rs.close(); if (preStmt != null) preStmt.close(); if (conn != null) conn.close(); } %> </body></html>
12.5 DbManager.java
package com.helloben.util;import java.sql.Connection;import java.sql.Date;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.sql.Timestamp;import java.sql.Types;import com.mysql.jdbc.Driver;public class DbManager { public static Connection getConnection() throws SQLException { return getConnection("databaseWeb", "root", "mysql"); } public static Connection getConnection(String dbName, String userName, String password) throws SQLException { String url = "jdbc:mysql://localhost:3306/" + dbName + "?characterEncoding=utf-8"; DriverManager.registerDriver(new Driver()); return DriverManager.getConnection(url, userName, password); } public static void setParams(PreparedStatement preStmt, Object... params) throws SQLException { if (params == null || params.length == 0) return; for (int i = 1; i <= params.length; i++) { Object param = params[i - 1]; if (param == null) { preStmt.setNull(i, Types.NULL); } else if (param instanceof Integer) { preStmt.setInt(i, (Integer) param); } else if (param instanceof String) { preStmt.setString(i, (String) param); } else if (param instanceof Double) { preStmt.setDouble(i, (Double) param); } else if (param instanceof Long) { preStmt.setDouble(i, (Long) param); } else if (param instanceof Timestamp) { preStmt.setTimestamp(i, (Timestamp) param); } else if (param instanceof Boolean) { preStmt.setBoolean(i, (Boolean) param); } else if (param instanceof Date) { preStmt.setDate(i, (Date) param); } } } public static int executeUpdate(String sql) throws SQLException { return executeUpdate(sql, new Object[] {}); } public static int executeUpdate(String sql, Object... params) throws SQLException { Connection conn = null; PreparedStatement preStmt = null; try { conn = getConnection(); preStmt = conn.prepareStatement(sql); setParams(preStmt, params); return preStmt.executeUpdate(); } finally { if (preStmt != null) preStmt.close(); if (conn != null) conn.close(); } } public static int getCount(String sql) throws SQLException { Connection conn = null; Statement stmt = null; ResultSet rs = null; try { conn = getConnection(); stmt = conn.createStatement(); rs = stmt.executeQuery(sql); rs.next(); return rs.getInt(1); } finally { if (rs != null) rs.close(); if (stmt != null) stmt.close(); if (conn != null) conn.close(); } }}
12.6 Pagination.java
package com.helloben.util;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=http://www.mamicode.com/‘" + url + "pageNum=1‘>第一页</a> "); buffer.append(pageNum == 1 ? " 上一页 " : " <a href=http://www.mamicode.com/‘" + url + "pageNum=" + (pageNum - 1) + "‘>上一页</a> "); buffer.append(pageNum == pageCount ? " 下一页 " : " <a href=http://www.mamicode.com/‘" + url + "pageNum=" + (pageNum + 1) + "‘>下一页</a> "); buffer.append(pageNum == pageCount ? " 最后一页 " : " <a href=http://www.mamicode.com/‘" + url + "pageNum=" + pageCount + "‘>最后一页</a> "); buffer.append(" 到 <input type=‘text‘ "); buffer.append(" name=‘helloben_goto_input‘ "); buffer.append(" style=‘width:25px; text-align:center; ‘> 页 "); buffer.append(" <input type=‘button‘"); buffer.append(" name=‘helloben_goto_button‘ value=http://www.mamicode.com/‘Go‘>"); buffer.append("<script language=‘javascript‘>"); buffer.append("function helloben_enter(){"); buffer.append(" if(event.keyCode == 13){"); buffer.append(" helloben_goto();"); buffer.append(" return false;"); buffer.append(" }"); buffer.append(" return true;"); buffer.append("} "); buffer.append("function helloben_goto(){"); buffer.append(" var numText = document.getElementsByName(‘helloben_goto_input‘)[0].value;"); buffer.append(" var num = parseInt(numText, 10);"); buffer.append(" if(!num){"); buffer.append(" alert(‘页数必须为数字‘); "); buffer.append(" return;"); buffer.append(" }"); buffer.append(" if(num<1 || num>" + pageCount + "){"); buffer.append(" alert(‘页数必须大于 1,且小于总页数 " + pageCount + " ‘); "); buffer.append(" return;"); buffer.append(" }"); buffer.append(" location=‘" + url + "pageNum=‘ + num;"); buffer.append("}"); buffer.append("document.getElementsByName(‘helloben_goto_input‘)[0].onkeypress = helloben_enter;"); buffer.append("document.getElementsByName(‘helloben_goto_button‘)[0].onclick = helloben_goto;"); buffer.append("</script>"); return buffer.toString(); }}
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。