上一节:
下一节:

  JSP 数据库访问

  • 数据库访问

    在本章中，我们将讨论如何使用JSP访问数据库。我们假设您对JDBC应用程序的工作原理有很好的了解。在开始通过JSP访问数据库之前，请确保您具有正确的JDBC环境设置以及数据库。有关如何使用JDBC及其环境设置访问数据库的更多详细信息，请阅读JDBC教程。首先，让我们创建一个表并在该表中创建一些记录，如下所示：

  • 建立表格

    要在MySQL test数据库中创建Employees表，请使用以下步骤-
    不熟悉MySQL 数据库 到我们的MySQL教程学习。
    建表： 
    
mysql> use test;
mysql> create table Employees
   (
      id int not null,
      age int not null,
      first varchar (255),
      last varchar (255)
   );
Query OK, 0 rows affected (0.08 sec)
mysql>
    插入数据： 插入四条测试数据。 
    
INSERT INTO Employees VALUES (100, 18, 'Jim', 'Green') , (101, 25, 'Lily', 'Tomas'), (102, 30, 'Lucy', 'Tomas'),(103, 28, 'Lei', 'Li');

  • SELECT 操作

    以下示例显示了如何在JSP编程中使用JTSL 执行SQL SELECT语句- 
    
<%@ page import = "java.io.*,java.util.*,java.sql.*"%>
<%@ page import = "javax.servlet.http.*,javax.servlet.*" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix = "c"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix = "sql"%>
 
<html>
   <head>
      <title>SELECT Operation</title>
   </head>

   <body>
      <sql:setDataSource var = "snapshot" driver = "com.mysql.cj.jdbc.Driver"
         url = "jdbc:mysql://dbHost:dbPort/dbName?useSSL=false&serverTimezone=UTC"
         user = "yourUser"  password = "yourPassword"/>
 
      <sql:query dataSource = "${snapshot}" var = "result">
         SELECT * from Employees;
      </sql:query>
 
      <table border = "1" width = "100%">
         <tr>
            <th>Emp ID</th>
            <th>First Name</th>
            <th>Last Name</th>
            <th>Age</th>
         </tr>
         
         <c:forEach var = "row" items = "${result.rows}">
            <tr>
               <td><c:out value = "${row.id}"/></td>
               <td><c:out value = "${row.first}"/></td>
               <td><c:out value = "${row.last}"/></td>
               <td><c:out value = "${row.age}"/></td>
            </tr>
         </c:forEach>
      </table>
 
   </body>
</html>
    访问上面的JSP，将显示以下结果- 
    
+-----+-----+-------+-------+
| id  | age | first | last  |
+-----+-----+-------+-------+
| 100 |  18 | Jim   | Green |
| 101 |  25 | Lily  | Tomas |
| 102 |  30 | Lucy  | Tomas |
| 103 |  28 | Lei   | Li    |
+-----+-----+-------+-------+

  • INSERT 操作

    以下示例显示了如何在JSP编程中使用JTSL执行SQL INSERT语句- 
    
<%@ page import = "java.io.*,java.util.*,java.sql.*"%>
<%@ page import = "javax.servlet.http.*,javax.servlet.*" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix = "c"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix = "sql"%>
 
<html>
   <head>
      <title>INSERT Operation</title>
   </head>

   <body>
      <sql:setDataSource var = "snapshot" driver = "com.mysql.cj.jdbc.Driver"
         url = "jdbc:mysql://dbHost:dbPort/dbName?useSSL=false&serverTimezone=UTC"
         user = "yourUser"  password = "yourPassword"/>
 
      <sql:update dataSource = "${snapshot}" var = "result">
         INSERT INTO Employees VALUES (104, 2, 'Poly', 'Animas');
      </sql:update>
 
      <sql:query dataSource = "${snapshot}" var = "result">
         SELECT * from Employees;
      </sql:query>
 
      <table border = "1" width = "100%">
         <tr>
            <th>Emp ID</th>
            <th>First Name</th>
            <th>Last Name</th>
            <th>Age</th>
         </tr>
         
         <c:forEach var = "row" items = "${result.rows}">
            <tr>
               <td><c:out value = "${row.id}"/></td>
               <td><c:out value = "${row.first}"/></td>
               <td><c:out value = "${row.last}"/></td>
               <td><c:out value = "${row.age}"/></td>
            </tr>
         </c:forEach>
      </table>
 
   </body>
</html>
    访问上面的JSP，将显示以下结果- 
    
+-----+-----+-------+--------+
| id  | age | first | last   |
+-----+-----+-------+--------+
| 100 |  18 | Jim   | Green  |
| 101 |  25 | Lily  | Tomas  |
| 102 |  30 | Lucy  | Tomas  |
| 103 |  28 | Lei   | Li     |
| 104 |   2 | Poly  | Animas |
+-----+-----+-------+--------+

  • DELETE 操作

    以下示例显示了如何在JSP编程中使用JTSL 执行SQL DELETE语句- 
    
<%@ page import = "java.io.*,java.util.*,java.sql.*"%>
<%@ page import = "javax.servlet.http.*,javax.servlet.*" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix = "c"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix = "sql"%>
 
<html>
   <head>
      <title>DELETE Operation</title>
   </head>

   <body>
      <sql:setDataSource var = "snapshot" driver = "com.mysql.cj.jdbc.Driver"
         url = "jdbc:mysql://dbHost:dbPort/dbName?useSSL=false&serverTimezone=UTC"
         user = "yourUser"  password = "yourPassword"/>
 
      <c:set var = "empId" value = "103"/>
 
      <sql:update dataSource = "${snapshot}" var = "count">
         DELETE FROM Employees WHERE Id = ?
         <sql:param value = "${empId}" />
      </sql:update>
 
      <sql:query dataSource = "${snapshot}" var = "result">
         SELECT * from Employees;
      </sql:query>
 
      <table border = "1" width = "100%">
         <tr>
            <th>Emp ID</th>
            <th>First Name</th>
            <th>Last Name</th>
            <th>Age</th>
         </tr>
         
         <c:forEach var = "row" items = "${result.rows}">
            <tr>
               <td><c:out value = "${row.id}"/></td>
               <td><c:out value = "${row.first}"/></td>
               <td><c:out value = "${row.last}"/></td>
               <td><c:out value = "${row.age}"/></td>
            </tr>
         </c:forEach>
      </table>
 
   </body>
</html>
    访问上面的JSP，将显示以下结果- 
    
+-----+-----+-------+--------+
| id  | age | first | last   |
+-----+-----+-------+--------+
| 100 |  18 | Jim   | Green  |
| 101 |  25 | Lily  | Tomas  |
| 102 |  30 | Lucy  | Tomas  |
| 104 |   2 | Poly  | Animas |
+-----+-----+-------+--------+

  • UPDATE 操作

    以下示例显示了如何在JSP编程中使用JTSL 执行SQL DELETE语句- 
    
<%@ page import = "java.io.*,java.util.*,java.sql.*"%>
<%@ page import = "javax.servlet.http.*,javax.servlet.*" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix = "c"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix = "sql"%>
 
<html>
   <head>
      <title>DELETE Operation</title>
   </head>

   <body>
      <sql:setDataSource var = "snapshot" driver = "com.mysql.cj.jdbc.Driver"
         url = "jdbc:mysql://dbHost:dbPort/dbName?useSSL=false&serverTimezone=UTC"
         user = "yourUser"  password = "yourPassword"/>
 
      <c:set var = "empId" value = "102"/>
 
      <sql:update dataSource = "${snapshot}" var = "count">
         UPDATE Employees SET first = 'Jack' WHERE id = ?
         <sql:param value = "${empId}" />
      </sql:update>
 
      <table border = "1" width = "100%">
         <tr>
            <th>Emp ID</th>
            <th>First Name</th>
            <th>Last Name</th>
            <th>Age</th>
         </tr>
         
         <c:forEach var = "row" items = "${result.rows}">
            <tr>
               <td><c:out value = "${row.id}"/></td>
               <td><c:out value = "${row.first}"/></td>
               <td><c:out value = "${row.last}"/></td>
               <td><c:out value = "${row.age}"/></td>
            </tr>
         </c:forEach>
      </table>
 
   </body>
</html>
    访问上面的JSP，将显示以下结果- 
    
+-----+-----+-------+--------+
| id  | age | first | last   |
+-----+-----+-------+--------+
| 100 |  18 | Jim   | Green  |
| 101 |  25 | Lily  | Tomas  |
| 102 |  30 | Jack  | Tomas  |
| 104 |   2 | Poly  | Animas |
+-----+-----+-------+--------+
上一节:
下一节: