Overview of JSTL SQL Tags
The JSTL SQL tag library provides various tags for accessing a database, creating database tables, and updating, deleting, and querying a database. The syntax for including the JSTL SQL tag library in a JSP page is the taglib directive:
<%@ taglib prefix="sql" uri=" http://java.sun.com/jsp/jstl/sql" %>
Table 1 shows JSTL SQL's different tags. All of the attributes are of type java.lang.String. Attributes are not required unless specified to be required in Table 1.
|
Tag Name
|
Description
|
Attributes
|
|
transaction
|
Creates a
transaction in which a group of SQL statements are run.
|
dataSource-Specifies
a datasource which is either a JNDI datasource or a JDBC DriverManager
datasource.
isolation-Specifies
the transaction isolation level. The default is the isolation level of the
datasource.
|
|
query
|
Runs a SQL query.
The SQL query may
be specified in the sql attribute or in the query element.
|
var(required)-Scoped
variable for the query result.
scope-Scope of
variable.
sql-SQL query
statement
dataSource-Datasource
associated with the query.
startRow-Specifies
the start row of the result returned by the query. The default is ‘0’ which
is also the index of the start row of the original result set.
maxRows-The maximum
number of rows in the result set. The default includes all the rows.
|
|
update
|
Runs a SQL
statement. The SQL statement may be specified in the sql attribute or the
update element. The SQL statement may be a CREATE, UPDATE, INSERT, or DELETE
statement.
|
var-Scoped variable
for the result of the SQL statement which is the update count of the UPDATE,
DELETE, INSERT statement.
scope-Scope of
variable.
sql-SQL statement
which may be a CREATE, UPDATE, INSERT, or DELETE statement.
dataSource-Datasource
associated with the update statement.
|
|
param
|
Specifies a
parameter for a SQL statement.
|
value-Parameter
value
|
|
dateParam
|
Specifies a
java.util.Date parameter.
|
value-Parameter
value for DATE, TIME, or TIMESTAMP database data type.
type-“date” “time”
or “timestamp”
|
|
setDataSource
|
Creates a
datasource for a JSP page.
|
var-Scoped variable
for datasource.
scope-Scope
ofvariable.
dataSource-Datasource.
A JNDI datasource or a JDBC parameters String.
Driver-JDBC driver
class.
url-Database URL
user-Username
password-Password
|
Creating a Database Table
You'll be creating a database table in the MySQL database with the JSTL 1.1 SQL tags in WebLogic server 9.1. The database will be located in catalog.jsp file you created in the previous section.
- Add the taglib directives for the SQL taglib and the JSTL Core taglib to the JSP page.
<%@ taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
- To access the MySQL database, create a datasource with the SQL tag
setDataSource. In the setDataSource tag, specify the JDBC driver, com.mysql.jdbc.Driver, for the MySQL database in the driver attribute. Specify the connection URL as jdbc:mysql://localhost:3306/test for the MySQL database. Also specify the username root for which a password is not required.
The sql:setDataSource tag with the attributes added looks like this:
<sql:setDataSource driver="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/test" user="root"/>
- Create a transaction with the
sql:transaction tag for SQL statements. A transaction runs the SQL queries to create database tables and adds rows to those tables. Add the following sql:transaction tag to the JSP page:
<sql:transaction>
</sql:transaction>
4. The sql:update tag is used to create a database table and add rows to the database table.
To create a database table with the JSTL SQL tags, add the sql:update tag in the sql:transaction tag. sql:update may include the sql, dataSource, var, and scope attributes. The SQL CREATE statement is specified in the sql attribute of the sql:update tag, or the sql:update tag. The sql:update tag to create a database table is:
<sql:update sql="CREATE TABLE Catalog(CatalogId VARCHAR(25)
PRIMARY KEY, Journal VARCHAR(25), Publisher VARCHAR(25),
Edition VARCHAR(25), Title Varchar(45), Author Varchar(25))">
</sql:update>
- Add rows to the database table with the
sql:update tag. The INSERT statement is specified in the sql attribute of the sql:update tag.
<sql:update sql="INSERT INTO Catalog VALUES('catalog1', 'ONJava',
'OReilly', 'Oct 2003', 'BCEL Maven and CSS with Swing', 'Daniel Steinberg')">
</sql:update>
The catalog.jsp page to create a database table and insert rows in the database table is shown in Listing 1.
To run catalog.jsp, copy it to the <weblogic91>\samples\server\examples\build\mainWebApp directory and then go to the URL http://localhost:7001/catalog.jsp. This creates your database table in the MySQL database.
New on the Java Boutique:
New Review:
Time Management Made Easy with the Quartz Enterprise Job Scheduler
Why not just use the Java timer API? This open source scheduling
API boasts simplicity, ease-of-integration, a well-rounded feature
set, and it's free!
New Applet:
Reverse Complement
Reverse Complement is a simple applet that converts DNA or RNA
sequences into three useful formats.
Elsewhere on internet.com:
WebDeveloper Java
Lots of Java information on webdeveloper.com
WDVL Java
Thorough Java resource at the Web Developer's Virtual Library.
ScriptSearch Java
Hundreds of free Java code files to download.
jGuru: Your View of the Java Universe
Customizable portal with online training, FAQs, regular news updates, and tutorials.
|
