在本章中,我们将讨论如何使用JSP访问数据库.我们假设您对JDBC应用程序的工作原理有很好的理解.在通过JSP开始数据库访问之前,请确保您具有正确的JDBC环境设置以及数据库.
有关如何使用JDBC及其环境设置访问数据库的更多详细信息,您可以去通过我们的 JDBC教程.
从基本概念开始,让我们创建一个表格,在该表中创建一些记录,如下所示;
在EMP中创建 Employees 表数据库,使用以下步骤 :
打开命令提示符并切换到安装目录跟随 :
C:\> C:\>cd Program Files\MySQL\bin C:\Program Files\MySQL\bin>
按以下方式登录数据库 :
C:\Program Files\MySQL\bin>mysql -u root -p Enter password: ******** mysql>
在 TEST中创建员工表数据库如下 : :
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>
现在让我们在员工表中创建一些记录如下 : :
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>
以下示例显示了如何使用 SQL SELECT 语句执行JSP编程中的JTSL :
<%@ 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://localhost/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 IDFirst NameLast NameAge100ZaraAli18101MahnazFatma25102ZaidKhan30103SumitMittal28
以下示例显示了如何在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://localhost/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 IDFirst NameLast NameAge100ZaraAli18101MahnazFatma25102ZaidKhan30103SumitMittal28104NuhaAli2
以下示例显示了我们如何执行在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://localhost/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 IDFirst NameLast NameAge100ZaraAli18101MahnazFatma25102ZaidKhan30
以下示例显示了我们如何在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://localhost/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 IDFirst NameLast NameAge100ZaraAli18101MahnazFatma25102ZaidAli30