This chapter assumes that you already have a certain understanding of JDBC. Before starting to learn JSP database access, please ensure that the JDBC environment has been configured correctly.
First, let's follow the steps below to create a simple table and insert a few simple records:
Create an Employees table in the database. The steps are as follows:
Open CMD and enter the database installation directory:
C:>C:>cd Program FilesMySQLbinC:Program FilesMySQLbin>
C:Program FilesMySQLbin>mysql -u root -pEnter password: ********mysql>
Use the create database statement to create a new database TEST:
mysql> create database TEST
Create the Employee table in the TEST database:
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>
After creating the Employee table, insert several records into the table:
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>
The following example shows us how to use JSTL SQL tags to run SQL SELECT statements:
<%@
page language
=
"java"
contentType
=
"text/html; charset=UTF-8"
pageEncoding
=
"UTF-8"
%>
<%@
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:3306/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>
Access this JSP example and the results are as follows:
This example shows us how to use JSTL SQL tags to run SQL INSERT statements:
<%@
page language
=
"java"
contentType
=
"text/html; charset=UTF-8"
pageEncoding
=
"UTF-8"
%>
<%@
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.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>
Access this JSP example and the results are as follows:
This example shows us how to use JSTL SQL tags to run SQL DELETE statements:
<%@
page language
=
"java"
contentType
=
"text/html; charset=UTF-8"
pageEncoding
=
"UTF-8"
%>
<%@
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>
Access this JSP example and the results are as follows:
This example shows us how to use JSTL SQL tags to run SQL UPDATE statements:
<%@
page language
=
"java"
contentType
=
"text/html; charset=UTF-8"
pageEncoding
=
"UTF-8"
%>
<%@
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>
UPDATE 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 last = 'Ali' 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>
Access this JSP example and the results are as follows:
