JSP - 数据库访问

上一页
下一页

在本章中，我们将讨论如何使用 JSP 访问数据库。我们假设您已经很好地理解了 JDBC 应用程序的工作原理。在开始通过 JSP 访问数据库之前，请确保您已正确设置了 JDBC 环境以及数据库。

有关如何使用 JDBC 访问数据库及其环境设置的更多详细信息，您可以查阅我们的 JDBC 教程。

为了开始了解基本概念，让我们创建一个表并在该表中创建一些记录，如下所示：

创建表

要创建 EMP 数据库中的 **Employees** 表，请使用以下步骤：

步骤 1

打开 **命令提示符** 并更改到安装目录，如下所示：

C:\>
C:\>cd Program Files\MySQL\bin
C:\Program Files\MySQL\bin>

步骤 2

登录到数据库，如下所示：

C:\Program Files\MySQL\bin>mysql -u root -p
Enter password: ********
mysql>

步骤 3

在 **TEST** 数据库中创建 **Employee** 表，如下所示：

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>

创建数据记录

现在让我们在 **Employee** 表中创建一些记录，如下所示：

mysql> INSERT INTO Employees VALUES (100, 18, 'Zara', 'Ali');
Query OK, 1 row affected (0.05 sec)
 
mysql> INSERT INTO Employees VALUES (101, 25, 'Mahnaz', 'Fatma');
Query OK, 1 row affected (0.00 sec)
 
mysql> INSERT INTO Employees VALUES (102, 30, 'Zaid', 'Khan');
Query OK, 1 row affected (0.00 sec)
 
mysql> INSERT INTO Employees VALUES (103, 28, 'Sumit', 'Mittal');
Query OK, 1 row affected (0.00 sec)
 
mysql>

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.jdbc.Driver"
         url = "jdbc:mysql://127.0.0.1/TEST"
         user = "root"  password = "pass123"/>
 
      <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，将显示以下结果：




Emp ID
First Name
Last Name
Age




100
Zara
Ali
18




101
Mahnaz
Fatma
25


 

102
Zaid
Khan
30




103
Sumit
Mittal
28

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>JINSERT Operation</title>
   </head>
   
   <body>
      <sql:setDataSource var = "snapshot" driver = "com.mysql.jdbc.Driver"
         url = "jdbc:mysql://127.0.0.1/TEST"
         user = "root"  password = "pass123"/>
         <sql:update dataSource = "${snapshot}" var = "result">
         INSERT INTO Employees VALUES (104, 2, 'Nuha', 'Ali');
      </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，将显示以下结果：




Emp ID
First Name
Last Name
Age




100
Zara
Ali
18




101
Mahnaz
Fatma
25


 

102
Zaid
Khan
30




103
Sumit
Mittal
28




104
Nuha
Ali
2

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.jdbc.Driver"
         url = "jdbc:mysql://127.0.0.1/TEST"
         user = "root" password = "pass123"/>
 
      <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，将显示以下结果：




Emp ID
First Name
Last Name
Age




100
Zara
Ali
18




101
Mahnaz
Fatma
25




102
Zaid
Khan
30

UPDATE 操作

以下示例演示了如何在 JSP 编程中使用 JTSL 执行 **SQL UPDATE** 语句：

<%@ 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.jdbc.Driver"
         url = "jdbc:mysql://127.0.0.1/TEST"
         user = "root" password = "pass123"/>
 
      <c:set var = "empId" value = "102"/>
 
      <sql:update dataSource = "${snapshot}" var = "count">
         UPDATE Employees SET WHERE last = 'Ali'
         <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，将显示以下结果：




Emp ID
First Name
Last Name
Age




100
Zara
Ali
18




101
Mahnaz
Fatma
25




102
Zaid
Ali
30
打印页面
上一页
下一页
广告