JSP Tutorial

Database access in JSPs

Database access is very common in JSPs.   Most database access these days is done using SQL.  Therefore, if you do not know SQL, the first step is to learn SQL.  Teaching SQL is outside the scope of this tutorial, but there are many excellent references available on the web.  (See the further reading page if you need some pointers.)

Once you know how to write SQL queries, all you then need is to be able to execute SQL query strings from Java programs or JSP pages, and to be able to examine and manipulate any returned values.

In Java, SQL access is provided via JDBC (the java.sql.* package.)  One approach to database access in JSP is simply to use JDBC, by putting JDBC calls in Java scriptlets.

Because of tag libraries, in JSP it is typically a little easier to use SQL.  Therefore it is not necessary to do the full JDBC setup.  In this page, we will see how to use the Blazix tag library for SQL access.  (The sample file is only for Windows computers, users of other systems would need to create test databases on their systems with advice from someone familiar with doing this on their system.)

The first step is to download the provided sample database file jspsqlsample.mdb, and configure an ODBC connection to it named "jspsql".  If you do not know how to configure ODBC connections, visit the setting up ODBC connections page.

Once you have your ODBC connection configured, add the following lines to your web configuration (web.ini) file:

dataSource.name: myDataSource
dataSource.myDataSource.odbc:  jspsql

This tells the server to use the ODBC connection named "jspsql".

The sample database file contains a table SingleItem which contains a single row of data.  (If you have Microsoft Access available on your machine, you should be able to open and look at the database file.)

The following query will retrieve a single item from this table.

SELECT Goal FROM SingleItem

Write and try out the following JSP.

<%@ taglib prefix="blx" uri="/blx.tld" %>
<HTML>
<BODY>
<P>
<blx:sqlConnection jndiName="myDataSource"> 
<P>The goal is <blx:sqlGet query="SELECT Goal FROM SingleItem"/> 
</blx:sqlConnection> 
</BODY>
</HTML>

Here, the blx:sqlConnection tag is specifying the "myDataSource" datasource, so the tag library will know that we want to access our jspsqlsample.mdb file.  The blx:sqlGet is retrieving the result of our query.

Often queries will return multiple rows, and will contain multiple items in each row.  For such queries, the tag blx:sqlQuery can be utilized.  Try the following JSP.

<%@ taglib prefix="blx" uri="/blx.tld" %>
<HTML>
<BODY>
<P>
<blx:sqlConnection jndiName="myDataSource"> 

<blx:sqlQuery id="sampleQuery"> 
SELECT DayNumber,TaskLearned FROM Jsptut 
</blx:sqlQuery> 

<TABLE> 
<TR><TD>Day Number</TD><TD>Task Learned</TD></TR> 
<blx:sqlExecuteQuery resultSet="rs" queryRef="sampleQuery"> 
<TR> 
<TD><%= rs.getInt("DayNumber") %></TD> 
<TD><%= rs.getString("TaskLearned") %></TD> 
</TR> 
</blx:sqlExecuteQuery> 
</TABLE> 
</blx:sqlConnection> 
</BODY>
</HTML>

The blx:sqlQuery tag is being used here to write out the query itself.  Then the blx:sqlExecuteQuery is being used to retrieve the rows of the query.  Everything between <blx:sqlExecuteQuery> and </blx:sqlExecuteQuery> will be repeatedly executed, once for each row of the query.  Therefore there will be many rows in the table, once for each row in the database.  Within the body of the blx:sqlExecuteQuery, we have access to the Java variable "rs", which is of type java.sql.resulSet.  We can retrieve the items from the row using either the column number or the name.  We could also have used the following to retrieve the items:

<TD><%= rs.getInt(1) %></TD>
<TD><%= rs.getString(2) %></TD>

To execute queries that do not return any values (such as INSERT, DELETE and UPDATE statements,) use the blx:executeUpdate tag.

Exercise:  1)  Write a page to execute and display the result of the following query thar returns a single item:

SELECT DayNumber FROM Jsptut WHERE TaskLearned='Scriptlets'

2) The Jsptut table has a third column named Url.  Modify the table sample above to display this column as well.  Make the URLs come out as hyperlinks.
3) Write a set of JSP pages that lets a user update the day number for any given task, and then displays the updated table. (Hint: You will need a WHERE clause to compare the task name, like in exercise 1 above.  You will also need the form processing skills you learned in earlier lessons.)
4)  Modify your JSP pages so users can add rows to the Jsptut table.
5)  Add a facility to delete rows also.
 
 
Contents