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="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="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=‘listPerson.jsp‘>返回人员列表</a>"); // 将执行的 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=‘listPerson.jsp‘>返回人员列表</a>"); // 将执行的 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=‘listPerson.jsp‘>返回人员列表</a>"); // 将执行的 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=‘listPerson.jsp‘>返回人员列表</a>"); // 将执行的 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(); } } %>