JDBC结合JSP使用（1）

浏览数：14 / 时间：2015年06月12日

1. 添加数据

在jsp页面中添加数据，和在serv中添加数据相似。获得页面中提交的数据以后，把数据保存到数据库表中，JSP的代码如下：

add.jsp

<%@ page language="java" import="java.sql.*" pageEncoding="gb2312"%>
<%
request.setCharacterEncoding("gb2312");
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <base href="<%=basePath%>">
    
    <title>My JSP ‘add.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">
    -->

  </head>
  
  <body>
    <form action="" method="post">
    <h1><label>请输入部门信息：</label></h1>
    <label>部门号：</label><br/>
    <input type="text" name="id"/><br/>
    <label>部门名：</label><br/>
    <input type="text" name="d_name"/><br/>
    <label>部门人数：</label><br/>
    <input type="text" name="empnumber"/><br/>
    <label>地址：</label><br/>
    <input type="text" name="address"/><br/><br/>
    <input type="submit" value="提交"/>
    </form>
  </body>
</html>
<%
    Connection conn = null;  
    PreparedStatement ps = null;
    try{
        Class.forName("com.mysql.jdbc.Driver");
        System.out.println("创建数据库驱动成功！");
        conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/bank","root","1234");
        System.out.println("数据库连接成功！");
        String sql = "insert into dept(id,d_name,address,empnumber) values(?,?,?,?)";
        ps = conn.prepareStatement(sql);
        String id = request.getParameter("id");
        String d_name = request.getParameter("d_name");
        String address = request.getParameter("address");
        int empnumber = Integer.parseInt(request.getParameter("empnumber"));
        ps.setString(1,id);
        ps.setString(2,d_name);
        ps.setString(3,address);
        ps.setInt(4,empnumber);
        int result = ps.executeUpdate();
        if(result == 1)
            out.print("插入数据成功！");
        else
            out.print("插入数据失败，请重新插入！");
        }catch(Exception e){
            out.println("无法连接数据库，请检查数据库连接是否正确！");
        }
%>

2. 显示全部数据

在页面中显示全部数据，也就是要把数据库中的全部数据查询出来，要实现这个功能，需要用到实体类，即数据库和实体对象的映射类。实体类代码如下：

DeptVo.java

package com.cn.vo;

public class DeptVo {
    private String id;
    private String address;
    private int empnumber;
    private String d_name;
    private int d_id;
    
    public String getId() {
        return id;
    }
    public void setId(String id) {
        this.id = id;
    }
    public String getAddress() {
        return address;
    }
    public void setAddress(String address) {
        this.address = address;
    }
    public int getEmpnumber() {
        return empnumber;
    }
    public void setEmpnumber(int empnumber) {
        this.empnumber = empnumber;
    }
    public String getD_name() {
        return d_name;
    }
    public void setD_name(String dName) {
        d_name = dName;
    }
    public int getD_id() {
        return d_id;
    }
    public void setD_id(int dId) {
        d_id = dId;
    }

}

编写好实体类型后，就可以在页面中调用该类，在JSP页面中编写JDBC，连接数据库和查询数据，再用JSTL标签库中的c标签遍历输出数据，使用EL表达式取值。JSP中的代码如下：

showAll.jsp

<%@ page language="java" import="java.util.*" pageEncoding="gb2312"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<%@ page import="com.cn.vo.*" %>
<%@ page import="java.sql.*" %>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <base href="<%=basePath%>">
    
    <title>My JSP ‘showAll.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">
    -->

  </head>
  <%
  List<DeptVo> list = new ArrayList<DeptVo>();
  try{
      Class.forName("com.mysql.jdbc.Driver");
      Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/bank","root","1234");
      Statement stmt = con.createStatement();
      ResultSet rs = stmt.executeQuery("SELECT * FROM dept");
      while(rs.next()){
          DeptVo deptVo = new DeptVo();
          deptVo.setId(rs.getString("id"));
          deptVo.setAddress(rs.getString("address"));
          deptVo.setD_id(rs.getInt("d_id"));
          deptVo.setD_name(rs.getString("d_name"));
          deptVo.setEmpnumber(rs.getInt("empnumber"));
          list.add(deptVo);
          }
      request.setAttribute("list",list); //把list集合放入request对象中
      }catch(Exception e){
          e.printStackTrace();
      }
   %>
  <body>
    <table border="1" align="center" width="70%">
        <tr>
            <td>部门编号</td>
            <td>部门地址</td>
            <td>部门人数</td>
            <td>部门名称</td>
            <td>部门id</td>
        </tr>
        <c:forEach items="${list}" var="list">
        <tr>
            <td>${list.id }</td>
            <td>${list.address }</td>
            <td>${list.empnumber }</td>
            <td>${list.d_name }</td>
            <td>${list.d_id }</td>
        </tr>
        </c:forEach>
    </table>
  </body>
</html>

3. 显示单条数据信息

显示单条信息就是根据数据的唯一标示符来查询出单条数据的详细信息。在dept表中，主键d_id的值是自动增长的，不会有重复，可以根据d_id查询出单条数据信息。首先要在页面中输入要查询的d_id值，然后根据d_id来查询数据的详细信息。输入查询条件的JSP文件代码如下：

item.jsp

<%@ page language="java" import="java.util.*" pageEncoding="gb2312"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <base href="<%=basePath%>">
    
    <title>My JSP ‘item.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">
    -->

  </head>
  
  <body>
    <form action="ShowById.jsp" method="post">
        <label>请输入部门id：</label><br/><br/>
        <input type="text" name="d_id"/><br/><br/>
        <input type="submit" value="查找"/>
    </form>
  </body>
</html>

显示在同一页面中的ShowById.jsp文件的代码如下：

<%@ page language="java" import="java.util.*" pageEncoding="gb2312"%>
<%@ page import="com.cn.vo.*" %>
<%@ page import="java.sql.*" %>
<%@page import="javax.servlet.jsp.tagext.TryCatchFinally"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <base href="<%=basePath%>">
    
    <title>My JSP ‘ShowById.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">
    -->

  </head>
  <%
      int d_id = Integer.parseInt(request.getParameter("d_id"));
      Connection con = null;
      PreparedStatement pstmt = null;
      ResultSet rs = null;
      try{
          Class.forName("com.mysql.jdbc.Driver");
          System.out.println("创建驱动成功！");
          con = DriverManager.getConnection("jdbc:mysql://localhost:3306/bank","root","1234");
          System.out.println("数据库连接成功！");
          String sql = "select * from dept where d_id =?";
          pstmt = con.prepareStatement(sql);
          pstmt.setInt(1,d_id);
          rs = pstmt.executeQuery();
          while(rs.next()){
              DeptVo deptVo = new DeptVo();
              deptVo.setId(rs.getString("id"));
              deptVo.setAddress(rs.getString("address"));
              deptVo.setD_id(rs.getInt("d_id"));
              deptVo.setD_name(rs.getString("d_name"));
              deptVo.setEmpnumber(rs.getInt("empnumber"));
              request.setAttribute("deptVo",deptVo);
              System.out.println(deptVo.getD_id());
              }
          }catch(Exception e){
              e.printStackTrace();
          }
   %>
  <body>
    <jsp:include flush="true" page="item.jsp"></jsp:include>
    <hr/>
    <h2>d_id值为<%=d_id%>的数据详细信息</h2>
        <table border="1" align="center" width="70%">
            <tr>
                <td>部门编号</td>
                <td>部门地址</td>
                <td>部门人数</td>
                <td>部门名称</td>
                <td>部门id</td>
            </tr>
            <tr>
                <td>${deptVo.id }</td>
                <td>${deptVo.address }</td>
                <td>${deptVo.empnumber }</td>
                <td>${deptVo.d_name }</td>
                <td>${deptVo.d_id }</td>
            </tr>
        </table>
  </body>
</html>