首页 > 代码库 > JDBC详解

JDBC详解

12.1 init.sql

12.2 listPerson.jsp

<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%><%@ page import="java.sql.*" %><%@ page import="java.sql.Date" %><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><html>  <head>    <title>My JSP ‘listPerson.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">    -->    <script type="text/javascript">            function selectAll_Click(){            var array=document.getElementsByName(id);            for(var i=0; i<array.length; i++){                array[i].checked=true;            }        }                function unSelectAll_Click(){            var array=document.getElementsByName(id);            for(var i=0; i<array.length; i++){                array[i].checked=false;            }        }                function delete_Click(){            return confirm(Confirm to delete selected record(s)?);        }            </script>  </head>    <body>    <table align=right>        <tr>            <td><a href="addPerson.jsp">Add New Person</a></td>        </tr>    </table>    <br />    <br />    <%         Connection conn = null;        Statement stmt = null;        ResultSet rs = null;        try{            DriverManager.registerDriver(new com.mysql.jdbc.Driver());                        conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/databaseWeb", "root", "mysql");            stmt = conn.createStatement();            rs = stmt.executeQuery("select * from tb_person");    %>            <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=left>                    <tr>                        <td>                            <input type=‘hidden‘ value=‘del‘ name=‘action‘>                            <a href=‘#‘ onclick="selectAll_Click()">Select All</a>                            <a href=‘#‘ onclick="unSelectAll_Click()">UnSelect All</a>                            <input type=‘submit‘ onclick="delete_Click()" value=‘Delete‘>                        </td>                    </tr>                </table>            </form>    <%        } catch (SQLException e) {            out.println("Exception: " + e.getMessage());            e.printStackTrace();        } finally {            if (rs != null)                rs.close();            if (stmt != null)                stmt.close();            if (conn != null)                conn.close();        }    %></body></html

常见的数据库连接:

MySQL:         jdbc:mysql://localhost/3306/db
Oracle:         jdbc:oracle:thin:@localhost:1521/db
DB2:           jdbc:db2://localhost:6789/db
PostgreSQl:    jdbc:postgresql://localhost:5432/db
Sybase:jdbc:    jtds:sybase://localhost:2638/db
SQLServer:     jdbc:microsoft:sqlserver://localhost:1433;databaseName=db
SQLServer 2005:jdbc:sqlserver://localhost:1433;databaseName=db

12.3(1) addPerson.jsp

<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><%    // 注意是取 request 参数而不是地址栏参数,因此用 getAttribute 方法而不是 getParameter    String action = (String)request.getAttribute("action");    String id = (String)request.getAttribute("id");    String name = (String)request.getAttribute("name");    String englishName = (String)request.getAttribute("englishName");    String age = (String)request.getAttribute("age");    String sex = (String)request.getAttribute("sex");    String birthday = (String)request.getAttribute("birthday");    String description = (String)request.getAttribute("description");        // 是 添加页面 还是 修改页面,下文中根据此变量做相应的处理    boolean isEdit = "edit".equals(action);%><html>  <head>        <title><%= isEdit ? "Edit Person" : "Add New Person" %></title>        <style type="text/css">        body, td{font-size:12px; }    </style>        <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">    -->  </head>    <body>    <form action="operatePerson.jsp" method="post">        <input type="hidden" name="action"            value="<%=isEdit ? "save" : "add"%>"> <input type="hidden"            name="id" value="<%=isEdit ? id : ""%>">        <fieldset>            <legend><%=isEdit ? "修改人员资料" : "新建人员资料"%></legend>            <table align=center>                <tr>                    <td>姓名</td>                    <td><input type="text" name="name"                        value="<%=isEdit ? name : ""%>" />                    </td>                </tr>                <tr>                    <td>英文名</td>                    <td><input type="text" name="englishName"                        value="<%=isEdit ? englishName : ""%>" />                    </td>                </tr>                <tr>                    <td>性别</td>                    <td>                        <input type="radio" name="sex" value="男" id="sex_male" <%=isEdit && "男".equals(sex) ? "checked" : ""%> />                        <label for="sex_male"></label>                         <input type="radio" name="sex" value="女" id="sex_female" <%=isEdit && "女".equals(sex) ? "checked" : ""%> />                        <label for="sex_female"></label></td>                </tr>                <tr>                    <td>年龄</td>                    <td>                        <input type="text" name="age" value="<%=isEdit ? age : ""%>" />                    </td>                </tr>                <tr>                    <td>描述</td>                    <td>                        <textarea name="description"><%=isEdit ? description : ""%></textarea>                    </td>                </tr>                <tr>                    <td></td>                    <td>                        <input type="submit" value="<%=isEdit ? "保存" : "添加人员信息"%>" />                         <input type="button" value="返回" onclick="history.go(-1); " />                    </td>                </tr>            </table>        </fieldset>    </form></body></html>

12.3(2) operationPerson.jsp

<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%><%@ page import="java.sql.*" %><%@ page import="java.sql.Date" %><%!    /** SQL 值中的单引号()需要转化为 \‘  */    public String forSQL(String sql){        return sql.replace("", "\\‘");    }%><%    request.setCharacterEncoding("UTF-8");    String name = request.getParameter("name");    String englishName = request.getParameter("englishName");    String age = request.getParameter("age");    String sex = request.getParameter("sex");    String description = request.getParameter("description");        String action = request.getParameter("action");    if("add".equals(action)){        // INSERT SQL 语句        String sql = "INSERT INTO tb_person " +                    " ( name, english_name, " +                    "   age, sex, " +                    "   description ) values " +                    " ( ‘" + forSQL(name) + "‘, ‘" + forSQL(englishName) + "‘, " +                    "" + age + "‘, ‘" + sex + "‘, " +                    "" + forSQL(description) + "‘ ) " ;                        Connection conn = null;        Statement stmt = null;        int result = 0;                try{                    DriverManager.registerDriver(new com.mysql.jdbc.Driver());            conn = DriverManager.getConnection(                            "jdbc:mysql://localhost:3306/databaseWeb?characterEncoding=UTF-8",                             "root",                             "mysql");                    stmt = conn.createStatement();                        // 使用 Statement 执行 SQL 语句            result = stmt.executeUpdate(sql);                    }catch(SQLException e){            out.println("执行SQL\"" + sql + "\"时发生异常:" + e.getMessage());            return;        }finally{            if(stmt != null)    stmt.close();            if(conn != null)    conn.close();        }                out.println("<html><style>body{font-size:12px; line-height:25px; }</style><body>");        out.println(result + " 条记录被添加到数据库中。");        out.println("<a href=http://www.mamicode.com/‘listPerson.jsp‘>返回人员列表");                // 将执行的 SQL 语句输出到客户端        out.println("<br/><br/>执行的 SQL 语句为:<br/>" + sql);                return;            }    else if("del".equals(action)){                // 取一个或者多个 ID 值        String[] id = request.getParameterValues("id");        if(id == null || id.length == 0){    out.println("没有选中任何行");    return;    }                String condition = "";                for(int i=0; i<id.length; i++){            if(i == 0)    condition = "" + id[i];            else        condition += ", " + id[i];        }                String sql = "DELETE FROM tb_person WHERE id IN (" + condition + ") ";                        Connection conn = null;        Statement stmt = null;                try{            DriverManager.registerDriver(new com.mysql.jdbc.Driver());            conn = DriverManager.getConnection(                                "jdbc:mysql://localhost:3306/databaseWeb?characterEncoding=UTF-8",                                 "root",                                 "mysql");                        stmt = conn.createStatement();                        // 使用 Statement 执行 SQL 语句            int result = stmt.executeUpdate(sql);                out.println("<html><style>body{font-size:12px; line-height:25px; }</style><body>");            out.println(result + " 条记录被删除。");            out.println("<a href=http://www.mamicode.com/‘listPerson.jsp‘>返回人员列表");                        // 将执行的 SQL 语句输出到客户端            out.println("<br/><br/>执行的 SQL 语句为:<br/>" + sql);                    }catch(SQLException e){            out.println("执行SQL\"" + sql + "\"时发生异常:" + e.getMessage());            e.printStackTrace();        }finally{            if(stmt != null)    stmt.close();            if(conn != null)    conn.close();        }    }    else if("edit".equals(action)){                String id = request.getParameter("id");        String sql = "SELECT * FROM tb_person WHERE id = " + id;                Connection conn = null;        Statement stmt = null;        ResultSet rs = null;                try{            DriverManager.registerDriver(new com.mysql.jdbc.Driver());            conn = DriverManager.getConnection(                                "jdbc:mysql://localhost:3306/databaseWeb?characterEncoding=UTF-8",                                 "root",                                 "mysql");                        stmt = conn.createStatement();            rs = stmt.executeQuery(sql);                if(rs.next()){                // 有记录 将响应字段从数据库中取出 保存到 request 中,显示到 修改页面                request.setAttribute("id", rs.getString("id"));                request.setAttribute("name", rs.getString("name"));                request.setAttribute("englishName", rs.getString("english_name"));                request.setAttribute("age", rs.getString("age"));                request.setAttribute("sex", rs.getString("sex"));                request.setAttribute("description", rs.getString("description"));                                request.setAttribute("action", action);                                // 转到修改页面                request.getRequestDispatcher("/addPerson.jsp").forward(request, response);            }            else{                // 没有数据                out.println("没有找到 id 为 " + id + " 的记录。");            }        }catch(SQLException e){            out.println("执行SQL\"" + sql + "\"时发生异常:" + e.getMessage());            e.printStackTrace();        }finally{            if(rs != null)        rs.close();            if(stmt != null)    stmt.close();            if(conn != null)    conn.close();        }    }    else if("save".equals(action)){                String id = request.getParameter("id");                String sql = "UPDATE tb_person SET " +                    "     name = ‘" + forSQL(name) + "‘, " +                    "     english_name = ‘" + forSQL(englishName) + "‘, " +                    "     sex = ‘" + sex + "‘, " +                    "    age = ‘" + age + "‘, " +                    "     description = ‘" + forSQL(description) + "" +                    " WHERE id = " + id;                Connection conn = null;        Statement stmt = null;        try{            DriverManager.registerDriver(new com.mysql.jdbc.Driver());            conn = DriverManager.getConnection(                                "jdbc:mysql://localhost:3306/databaseWeb?characterEncoding=UTF-8",                                 "root",                                 "mysql");                        stmt = conn.createStatement();                        // 使用 Statement 执行 SQL 语句            int result = stmt.executeUpdate(sql);                out.println("<html><style>body{font-size:12px; line-height:25px; }</style><body>");                        if(result == 0)        out.println("影响数目为 0, 修改失败. ");            else    out.println(result + " 条记录被修改。");                        out.println("<a href=http://www.mamicode.com/‘listPerson.jsp‘>返回人员列表");                        // 将执行的 SQL 语句输出到客户端            out.println("<br/><br/>执行的 SQL 语句为:<br/>" + sql);                    }catch(SQLException e){            out.println("执行SQL\"" + sql + "\"时发生异常:" + e.getMessage());            e.printStackTrace();        }finally{            if(stmt != null)    stmt.close();            if(conn != null)    conn.close();        }    }    else{        String id = request.getParameter("id");        String sql = "UPDATE tb_person SET name = ?, english_name = ?, sex = ?, age = ?, description = ? WHERE id = ? ";                Connection conn = null;        PreparedStatement preStmt = null;                try{            DriverManager.registerDriver(new com.mysql.jdbc.Driver());            conn = DriverManager.getConnection(                                "jdbc:mysql://localhost:3306/databaseWeb?characterEncoding=UTF-8",                                 "root",                                 "mysql");                        preStmt = conn.prepareStatement(sql);            preStmt.setString(1, name);            preStmt.setString(2, englishName);            preStmt.setString(3, sex);            preStmt.setInt(4, Integer.parseInt(age));            preStmt.setString(5, description);            preStmt.setInt(6, Integer.parseInt(id));                        // 使用 preStmt 执行 SQL 语句            int result = preStmt.executeUpdate(sql);                out.println("<html><style>body{font-size:12px; line-height:25px; }</style><body>");                        if(result == 0)        out.println("影响数目为 0, 修改失败. ");            else    out.println(result + " 条记录被修改。");                        out.println("<a href=http://www.mamicode.com/‘listPerson.jsp‘>返回人员列表");                        // 将执行的 SQL 语句输出到客户端            out.println("<br/><br/>执行的 SQL 语句为:<br/>" + sql);                    }        catch(SQLException e){            out.println("执行SQL\"" + sql + "\"时发生异常:" + e.getMessage());            e.printStackTrace();        }        finally{            if(preStmt != null)    preStmt.close();            if(conn != null)    conn.close();        }    }%>