首页 > 代码库 > 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();    }}