JSP Tags for SQL to connect to a database

By: Manoj Kumar Emailed: 1645 times Printed: 2110 times    

Latest comments
By: rohit kumar - how this program is work
By: Kirti - Hi..thx for the hadoop in
By: Spijker - I have altered the code a
By: ali mohammed - why we use the java in ne
By: ali mohammed - why we use the java in ne
By: mizhelle - when I exported the data
By: raul - no output as well, i'm ge
By: Rajesh - thanx very much...
By: Suindu De - Suppose we are executing

The JSTL, includes a set of JSP tags that allow you to access a database via SQL directly from your JSP pages. The obvious advantage of the JSP tags for SQL is simplicity. It’s easy to query the database and to throw the results up on a web page. This is great for simple applications that need only to display database data on a web page and make simple database table updates—JSP tags for SQL work well. The <sql:query> tag executes an SQL query and returns a result set object that you can iterate over and display with other JSTL tags. You can also perform database table updates. The <sql:update> tag executes an SQL update.

For larger projects, there are a couple of disadvantages to keep in mind. One disadvantage is that you must embed SQL queries with table and field names into your JSP pages. If database table and field names change, you’ll have to make the corresponding changes in your JSP files. For a small project, this might not be a big problem, but larger projects should consider other data access options. Other data access options, such as O/R frameworks and EJB, can provide some level of indirection so that changes to the database schema do not have as much of an effect on your application code.

Another disadvantage is updates. The JSP tags for SQL allow you to perform updates on a database, but you have to build the SQL update string yourself. Again, for small projects with simple updating needs, this might not be a problem, but larger projects should consider other data access options. Other data access options can provide infrastructure to make updates much easier to program.

JavaServer Pages Tags for SQL Example

Listing below is an example of JSP tags for SQL. This example illustrates how to execute a query, iterate through the results, and display the results in an HTML table.

jstl-example.jsp

<%@ page language="java" %>
<%@ taglib uri="/WEB-INF/c.tld" prefix="c"%>
<%@ taglib uri="/WEB-INF/sql.tld" prefix="sql"%>
<!DOCTYPE HTML PUBLIC "-//w3c//dtd html 4.0 transitional//en">
<html><head><title>jstl-example</title></head>
<body bgcolor="#ffffff">
<sql:query var="items"
dataSource="jdbc:hsqldb:hsql://localhost,org.hsqldb.jdbcDriver,sa">
SELECT TITLE,TIME FROM ITEM ORDER BY TIME
</sql:query>
<h1>News Items</h1>
<table border="1">
<th>Title</th><th>Time</th>
<c:forEach var="row" items="${items.rows}">
<tr>
<td><c:out value="${row.TITLE}"/></td>
<td><c:out value="${row.TIME}"/></td>
</tr>
</c:forEach>
</table>
</body>
</html>

Let’s examine the code more closely. At the top of the page, you see the two taglib directives:

<%@ taglib uri="/WEB-INF/c.tld" prefix="c"%>
<%@ taglib uri="/WEB-INF/sql.tld" prefix="sql"%>

The first directive declares that this example uses JSTL Core tags and the second directive declares use of the JSTL SQL tags. The corresponding TLD files, c.tld and sql.tld, must be in the example web application’s WEB-INF directory.

The next interesting block of code is the actual query itself, shown here, and expressed using the JSTL <sql:query> tag:

<sql:query var="items" dataSource="jdbc:hsqldb:hsql://localhost,org.hsqldb.jdbcDriver,sa">
SELECT TITLE,TIME FROM ITEM ORDER BY TIME
</sql:query>

The tag has two attributes: var and dataSource. The var attribute specifies the name of an object (of type javax.servlet.jsp.jstl.sql.Result) that will be created by the query to hold the results of the query. The dataSource attribute specifies the database connection string. This is a comma separated string with the format connection URL, the JDBC driver class name, the username, and password. In the example, the password is an empty string and can be omitted from the connection string. The <sql:query> tag will use these parameters to obtain a connection from the JDBC driver manager. Within the body of the <sql:query> tag is the SQL query string to be executed: SELECT TITLE,TIME FROM ITEM ORDER BY TIME.

After the query has been executed and the results are available in the items object, you display the HTML table by using the JSTL <c:forEach> tag to iterate through the rows that are contained in the items object:

<table border="1">
<th>Title</th><th>Time</th>
<c:forEach var="row" items="${items.rows}">
<tr>
<td><c:out value="${row.TITLE}"/></td>
<td><c:out value="${row.TIME}"/></td>
</tr>
</c:forEach>
</table>

For each row of data, you use the <c:out> tag to display each column of data. Listing above gives a brief example of how to use a few of the tags. If you want to learn more about the JSTL, you can find more information at Sun’s JSTL web page (http://java.sun.com/products/jsp/jstl/). The Sun Web Services tutorial (http://java.sun.com/webservices/docs/1.0/tutorial/index.html) also covers JSTL and includes a section on the JSTL SQL tags.


JSP Home | All JSP Tutorials | Latest JSP Tutorials

Sponsored Links

If this tutorial doesn't answer your question, or you have a specific question, just ask an expert here. Post your question to get a direct answer.



Bookmark and Share

Comments(0)


Be the first one to add a comment

Your name (required):


Your email(required, will not be shown to the public):


Your sites URL (optional):


Your comments:



More Tutorials by Manoj Kumar
Importing Keystore example using keytool in Java
for loop example in Java
Result Sets, Cursors and Transactions in SQL
JSP Tags for SQL to connect to a database
Using the qsort() and bsearch() functions with values - example program in C
Using qsort() and bsearch() with strings - example program in C
scanf and sscanf sample program in C
Use of {0} is required and Accessing resource bundles programmatically in Struts
Character Arrays in C
Pointers to functions in C++
atoi(), itoa() in C++
Ticker sample program in J2ME
DateFormat sample program in Java
Random class sample program in Java - nextGaussian(), nextBoolean(), nextBytes(), nextInt(), nextLong(), nextFloat(), nextDouble()
What Is a Reference in C++?

More Tutorials in JSP
LifecycleException: service.getName(): "Catalina"; Protocol handler start failed: `java.net.BindException: Permission denied <null>:80
JSP Alert Example
JSP CheckBox Example
Uploading an Image to a Database using JSP
Uploading a file to a server using JSP
A JSP page that gets properties from a bean
The page Directive in JSP
The taglib, tag, include, attribute and the variable Directive in JSP
Declarations in JSP
Scriptlets and Expressions in JSP
Tag Libraries in JSP
The Request Object in JSP
The Response Object in JSP
The Out Object in JSP
The Session Object in JSP

More Latest News
Most Viewed Articles (in JSP )
JSP Alert Example
JSP Program for display Date
Arithmetic Evaluation Using the Expression Language in JSP
JSP Example to connect to MS SQL database and retrieve records
What are the different scopes in JSP?
JSP CheckBox Example
JSP Tags for SQL to connect to a database
Sending Email using JSP
Using a DataSource from WebLogic in a JSP
IIS and Tomcat - how to configure to work together
The Session Object in JSP
Uploading an Image to a Database using JSP
What is JSP?
Cookies using JSP or Java Bean
Embedding java codes in jsp sciptlets
Most Emailed Articles (in JSP)
LifecycleException: service.getName(): "Catalina"; Protocol handler start failed: `java.net.BindException: Permission denied <null>:80
Tags using in jsp
Tag libraries
What is JSP?
Click to Activate and Use this control
Closing Windows
Frame Object
Introduction to JSP expression language
Disabling Scriptlets in JSP using web.xml
Getting HTTP Request Headers in a JSP
What is JSP?
Cookies using JSP or Java Bean
Syntax For JSP Declaratives
JSP Program for display Date
Sessions in JSP