JDBC详解(2)

时间：2022-03-10 18:06

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="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=‘" + 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> ");

        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=‘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();
    }
}

JDBC详解(2),布布扣,bubuko.com