Tutorials : Accessing a Database with the JSTL 1.1 SQL Tag Library :

Overview of JSTL SQL Tags

<%@ taglib prefix="sql" uri=" http://java.sun.com/jsp/jstl/sql" %>

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

1. 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" %>
   

2. 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"/>
   

3. 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>
   

4. 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.

How to Add Java Applets to Your Site

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.