|
|
: 0px; margin-bottom: 0px; -ms-text-justify: inter-ideograph;"><jsp:scriptlet>
Class.forName("org.gjt.mm.mysql.Driver");
Connection connection =
DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "", "");
Statement statement = connection.createStatement();
int changed =
statement.executeUpdate("update counters set hitCount = hitCount + 1 " +
"where page like '" + request.getRequestURI() + "'");
if (changed == 0) statement.executeUpdate("insert counters(page) values('" +
request.getRequestURI() + "')");
ResultSet rs =
statement.executeQuery("select hitCount from counters where page like '" +
request.getRequestURI() + "'");
rs.next();
int hitCount = rs.getInt(1);
statement.close();
connection.close();
</jsp:scriptlet>
<HTML>
<HEAD>
<TITLE>JDBC scriptlet example</TITLE>
</HEAD>
<BODY>
<P>This page has been hit
<jsp:expression>hitCount</jsp:expression>
times. The page counter is implemented by a JSP scriptlet calling
the JDBC package directly.</P>
</BODY>
</HTML>
This page has a scriptlet in it (the first highlighted section), which connects to the database, creates a statement, and attempts to update a counter record keyed by the page's URI. If the update affects no rows, this example assumes there is no such record and adds one. Finally, the scriptlet queries the database for the current hit count and assigns the result to a local variable. Further down, in the "presentation" portion of this JSP page, the highlighted JSP expression is used to render the value of the hit counter.
|
|
|
Although it is functional, this JSP page has a number of problems. First, the scriptlet is not something that a nonprogramming page designer is going to want in the page. Frankly, it isn't even something that programmers should want in the page, because it clutters up the page's real content. Second, to keep the example simple, this page lacks the exception handling that should be part of any real-world JDBC code. Third, the implementation of the hit counter is literally embedded in the JSP page, so any change to the hit counter would need to be propagated to every JSP page for which we maintain a hit counter.
So how can we fix this JSP page? One oft-touted solution is to use a tag library to eliminate scriptlets. In our next example, we'll look at this alternative solution.
A tag library example using DBTags
One of the first things that a new JSP programmer hears, usually from well-intentioned friends and experts, is not to use scriptlets. Instead, they tell the new JSP programmer to use custom tags. Custom tags are a means by which the JSP platform's capabilities are extended: custom XML-style tags, tied to code libraries, implement the desired functionality. We'll see how well they work, in our next example.
The Jakarta TagLibs Project is a subproject of the Jakarta Project (see Resources), the official reference implementation of the Java Servlet and JavaServer Pages technologies.
One of the packages developed under the auspices of the Jakarta TagLibs Project is the DBTags custom tag library (formerly known as the JDBC tag library). The JSP page in Listing 4 implements the same hit counter as in Listing 3, replacing the scriptlet with custom tags.
Listing 4. JSP page using DBTags
| <HTML> <HEAD> <TITLE>Jakarta DBTags example</TITLE> </HEAD> <BODY> <%@ taglib uri="http://jakarta.apache.org/taglibs/dbtags" prefix="sql" %> <%-- open a database connection --%> <sql:connection id="conn1"> <sql:url>jdbc:mysql://localhost/test</sql:url> <sql:driver>org.gjt.mm.mysql.Driver</sql:driver> </sql:connection> <%-- insert a row into the database --%> <sql:statement id="stmt1" conn="conn1"> <%-- set the SQL query --%> <sql:query> insert counters(page,hitCount) values('<%=request.getRequestURI()%>', 0) </sql:query> <%-- the insert may fail, but the page will continue --%> <sql:execute ignoreErrors="true"/> </sql:statement> <%-- update the hit counter --%> <sql:statement id="stmt1" conn="conn1"> <%-- set the SQL query --%> <sql:query> update counters set hitCount = hitCount + 1 where page like '<%=request.getRequestURI()%>' </sql:query> <%-- execute the query --%> <sql:execute/> </sql:statement> <P>This page has been hit <%-- query the hit counter --%> <sql:statement id="stmt1" conn="conn1"> <sql:query> select hitCount from counters where page like '<%=request.getRequestURI()%>' </sql:query> <%-- process only the first row of the query --%> <sql:resultSet id="rset2" loop="false"> <sql:getColumn position="1"/> </sql:resultSet> </sql:statement> times. The page counter is implemented using the Jakarta Project's DBTags tag library, calling JDBC indirectly.</P> <%-- close a database connection --%> <sql:closeConnection conn="conn1"/> </BODY> </HTML> |
I don't know about you, but I'm feeling a bit let down. That seems even less clear to me than the scriptlet example, and I don't know any nonprogramming HTML page designers who would be pleased with it, either. But what went wrong? After all, we followed people's advice: we got rid of the scriptlet and replaced it with custom tags.
|
|
|
What they don't tell you about tag libraries is this: tag design is language design. Most tag libraries written to date have been written by programmers for programmers; the semantics of those tags are geared toward other programmers. Furthermore, remember the separation of model and presentation? That isn't well supported by DBTags. The sql:getColumn tag is analogous to the jsp:getProperty action: it emits the tag's result directly into the output stream. That makes it difficult to separate using DBTags from rendering output into the desired form. Finally, notice that the logic differs between Listing 3 and Listing 4. The DBTags execute tag consumes the update count from any update statement sent via JDBC; only query results can be retrieved. That means we cannot find out how many rows were updated by the update statement. So we have to switch the update and insert statements; we always try to insert a new record, force DBTags to ignore any error, and then perform the update.
In fairness to the DBTags tag library, it is not a bad tag library for programmers. Aside from its consumption of the update count, the code provides a fairly good mapping to JDBC. Therein lies the problem, however: the tags provide little more than a direct translation of the JDBC package. Other than hiding some exception handling, the tag library doesn't really provide any abstraction over scriptlets. It certainly doesn't help separate presentation from function.
So, the real issue is not whether to use scriptlets or tags; that question is a consequence, not a cause, of the problem of separating function from presentation. The solution is to provide higher-level functionality to presentation-page authors at an appropriate level of discourse. The reason tags are considered better than scriptlets is that scriptlets, by definition, are programming, whereas tags can represent high-level concepts.
Hiding JDBC from presentation pages
When integrating JDBC with JSP technology, we want to hide as much of that integration from the presentation author as possible. Where we do expose database concepts, we want to expose them at a suitable level of abstraction. This approach leads to our next example.
In the example in Listing 5, we hide the JDBC integration from the presentation page. (A live version of this page is located on the JavaServer Pages Developers Guide Web site.)
Listing 5. JSP page with hidden JDBC
| <jsp:directive.include file="/pagelets/hitCounter.jsp" /> <HTML> <HEAD> <TITLE>JDBC hidden example</TITLE> </HEAD> <BODY> <P>This page has been hit <jsp:getProperty name="hitCounter" property="int" /> times. The page counter is implemented indirectly: a JavaBeans component containing the hit count is inserted into the environment and referenced within the page using the JSP getProperty action. The JSP page doesn't have any exposure to JDBC.</P> </BODY> </HTML> |
The included hitCounter.jsp file takes care of setting up the environment. The contents can be a scriptlet, tags, or nothing more than a taglib directive; the contents can be anything that establishes the desired environment for the presentation page. If you wish, you can replace the getProperty action with a custom tag; for example:
| This page has been hit <page:hitcounter /> times. |
As I observed earlier, these hit-counter examples are purely for illustration; performing such database operations for each page would be an unnecessary expense. The above example shows how you might actually want to expose a hit counter. By hiding it in a custom tag, we've completely hidden the implementation. Now we can aggregate the hit-count information at run time and update the database periodically (at the end of each session, for example). Even the means of storage (database or other) is hidden from the presentation-page author. That is how we implement hit counters at DevTech: we have bean classes implementing hit-counter model behavior. Tags tie that behavior into our pages.
Integrating with JavaBeans components
The examples so far have been fairly simple, but most database operations are going to be more sophisticated than these simple queries and updates. So now that we've covered some basic principles of using JDBC with JSP pages, let's conclude with a slightly more complex, and certainly more common, type of application.
The example for this section (Listing 9, below) will show one way to support visitor-supplied content on a Web site. In other words, we want to allow visitors to read database content associated with a URI and to contribute additional content. Such content is fairly common on modern Web sites. The same basic parts can be used to construct:
Review pages, such as those found on Amazon.com
Links pages
Bulletin boards
Wikiwebs
An only slightly more elaborate version of the JSP components in this example can implement Web pages that seem very different, authored by designers of varying technical backgrounds. All that the pages would appear to have in common is a provision for visitor-contributed content.
Our annotation example uses an HTML form. When using HTML forms with JSP, it is convenient to use a bean whose properties map to the form fields. This allows the setProperty tag to do its magic:
Listing 6. Bean instance that maps to a form
| <%-- setup a bean instance that matches our form --%> <jsp:useBean id="instance-name" class="bean-class" ... /> <%-- set all bean properties that match a form field --%> <jsp:setProperty name="instance-name" property="*" /> |
|
|
|
Integration with JavaBeans components is one of the better-designed aspects of JSP technology. Unfortunately, the integration between beans and JDBC is not seamless at all, so for our JDBC work at DevTech, we developed a package that provides not only integration between beans and JDBC but also the necessary exception handling, relieving the programmer from having to deal with the details.
The annotation example uses two of the query and update methods from the com.devtech.sql package. The particular query method used passes a bean class, an SQL query, and an Object array to fill in the placeholders in the query. In this case, the only placeholder is for the page's URL. The result is a database cursor object, which is essentially a type of iterator.
Listing 7. Database cursor object
| dataBase.queryCursor(AnnotationDBBean.class, new String[] { URL }, "select page, author, annotation, DATE_FORMAT(whenPosted, '%W %d%b%y %T')" + " as whenPosted from annotations where page like ?"); |
What makes this query method interesting is that the specified type of bean will be instantiated for you, and any bean properties whose names match column names in the ResultSet will have their values set automatically. Each time you use the cursor to select the next row, the bean's properties are automatically set from the ResultSet.
The particular update method used takes a bean instance, a String array, and an update statement. The values of the String array specify the desired bean properties to be used to fill in the placeholders in the update. In this case, the page, author, and annotation properties are selected from the bean.
Listing 8. Update method
| int count = dataBase.update(annotationBean, new String[] { "page", "author", "annotation" }, "insert into annotations(page, author, annotation) values(?, ?, ?)"); |
Our example JSP page, annotations.jsp, is shown in Listing 9. The highlighted sections indicate a couple of scriptlets that could be replaced with custom tags, as shown in Listing 10. The remainder of the page consists of some JSP comments provided to assist the page designer, getProperty actions to place dynamic content onto the page, and standard HTML. JSP comments are used because they are private and won't appear in the output stream.
Listing 9. JSP page for annotations
| <jsp:directive.include file="/pagelets/annotate.jsp" /> <%-- By the time we arrive here, the annotation bean has been established, and if the form is submitted, the contents will be posted to the database. The page property is initialized. If the author is known during this session, that property is also initialized. Bean:"annotation" Properties:String page; String author; String annotation; String whenPosted; Access to any bean property follows the format: <jsp:getProperty name="annotation" property="property-name" /> --%> <HTML> <HEAD> <TITLE>Comments for <jsp:getProperty name="annotation" property="page" /></TITLE> </HEAD> <BODY> <p align="left"><font size="+1"> Comments for <i><jsp:getProperty name="annotation" property="page" /></i> </font>.</p> <CENTER><HR WIDTH="100%"></CENTER> <!-- Annotation Submission Form --> <FORM method="POST"> <TABLE> <TR> <TH align="left">Name:</TH> <TD><INPUT type=text name=author size=50 maxlength=60 value="<jsp:getProperty name="annotation" property="author" />"> </TD> </TR> <TR> <TH valign="top" align="left">Note:</TH> <TD><TEXTAREA name=annotation cols=40 rows=5 wrap=virtual> <jsp:getProperty name="annotation" property="annotation" /></TEXTAREA></TD> </TR> <TR> <TD align="center" colspan="2"><INPUT type=submit value="Add Comment"></TD> </TR> </TABLE> </FORM> <!-- End of Annotation Submission Form --> <!-- beginning of annotations --> <%-- The following section iterates through all annotations in the database for the requested page. To change the look of the page, just change anything in the demarcated area. --%> <jsp:scriptlet> Database.Cursor annotations = annotation.getCursor(); while (annotations.next(annotation) != null) { </jsp:scriptlet> <%-- beginning of annotation change area --%> <CENTER><HR WIDTH="100%"></CENTER> From: <jsp:getProperty name="annotation" property="author" /></A> at <jsp:getProperty name="annotation" property="whenPosted" /><BR> <jsp:getProperty name="annotation" property="annotation" /><BR> <%-- end of annotation change area --%> <jsp:scriptlet> } annotations.close(); </jsp:scriptlet> <!-- end of annotations --> </BODY> </HTML> |
The custom tags equivalent is clear but uninformative:
Listing 10. Custom tags equivalent
| <sql:results queryName="annotations" bean="annotation"> <CENTER><HR WIDTH="100%"></CENTER> From: <jsp:getProperty name="annotation" property="author" /></A> at <jsp:getProperty name="annotation" property="whenPosted" /><BR> <jsp:getProperty name="annotation" property="annotation" /><BR> </sql:results> |
We've used scriptlets in this example only to show you, a programmer, what is happening. If they were replaced with declarative tags, they would be clear to the page designer, but uninformative to you.
The logic is straightforward. The annotation.getCursor() call acquires a connection to the server, issues the query, and sets up a database cursor object, annotations, on the result set. Each time annotations.next() is called, a new row is fetched from the result set, and its values moved into a bean whose reference is returned from the method. The particular next() method being used takes a bean parameter to populate. Although we could have the cursor instantiate a new bean for each row, reusing the bean is more efficient.
Notice that neither the actual query, nor the update, is present in the presentation page. The included page, which sets up the environment for the presentation page, also includes the setProperty and update actions. Those actions are independent of the presentation page; only the contract embodied by the annotation bean's properties is significant. This is in keeping with a policy to separate presentation from model behavior. The page designer is fully able to change how the presentation is rendered but has no access to how the database is integrated. If a change is to be effected in updating or querying the database, it is delegated to a JSP programmer.
This concludes an introduction to combining the JavaServer Pages, JavaBeans, and JDBC technologies to generate dynamic content through relational databases. We started with the most obvious approach for the new JSP programmer: scriptlets. We saw how the uncontrolled use of scriptlets intertwines logic and presentation, making both of them hard to maintain. We also saw that tag libraries don't necessarily improve MVC separation, and that the pages using them may not be understandable to page designers if the tags are expressed in programming terms. Finally, we looked at more complex examples that illustrate a few ways to separate database access from the presentation of content.
You should now have some basic ideas about how to integrate database content into a Web site while hiding the actual database access from page designers. Note, too, that the least informative examples for you, a programmer, are the ones most appropriate for a page designer. When you plan your JSP solutions, keep your page designers in mind.
,Introduction to JDBC
This document illustrates the basics of the JDBC (Java Database Connectivity) API (Application Program Interface). Here, you will learn to use the basic JDBC API to create tables, insert values, query tables, retrieve results, update tables, create prepared statements, perform transactions and catch exceptions and errors.
This document draws from the official Sun tutorial on JDBC Basics.
Overview
Establishing a Connection
Creating a JDBC Statement
Creating a JDBC PreparedStatement
Executing CREATE/INSERT/UPDATE Statements
Executing SELECT Statements
Notes on Accessing ResultSet
Transactions
Handling Errors with Exceptions
Sample Code and Compilation Instructions
Overview
Call-level interfaces such as JDBC are programming interfaces allowing external access to SQL database manipulation and update commands. They allow the integration of SQL calls into a general programming environment by providing library routines which interface with the database. In particular, Java based JDBC has a rich collection of routines which make such an interface extremely simple and intuitive.
Here is an easy way of visualizing what happens in a call level interface: You are writing a normal Java program. Somewhere in the program, you need to interact with a database. Using standard library routines, you open a connection to the database. You then use JDBC to send your SQL code to the database, and process the results that are returned. When you are done, you close the connection.
Such an approach has to be contrasted with the precompilation route taken with Embedded SQL. The latter has a precompilation step, where the embedded SQL code is converted to the host language code(C/C++). Call-level interfaces do not require precompilation and thus avoid some of the problems of Embedded SQL. The result is increased portability and a cleaner client-server relationship.
Establishing A Connection
The first thing to do, of course, is to install Java, JDBC and the DBMS on your working machines. Since we want to interface with an Oracle database, we would need a driver for this specific database as well. Fortunately, we have a responsible administrator who has already done all this for us on the Leland machines.
As we said earlier, before a database can be accessed, a connection must be opened between our program(client) and the database(server). This involves two steps:
Load the vendor specific driver
Why would we need this step? To ensure portability and code reuse, the API was designed to be as independent of the version or the vendor of a database as possible. Since different DBMS's have different behavior, we need to tell the driver manager which DBMS we wish to use, so that it can invoke the correct driver.
An Oracle driver is loaded using the following code snippet:
Class.forName("oracle.jdbc.driver.OracleDriver")
Make the connection
Once the driver is loaded and ready for a connection to be made, you may create an instance of a Connection object using:
Connection con = DriverManager.getConnection(
"jdbc:oracle:thin:@dbaprod1:1544:SHR1_PRD", username, passwd);
Okay, lets see what this jargon is. The first string is the URL for the database including the protocol (jdbc), the vendor (oracle), the driver (thin), the server (dbaprod1), the port number (1521), and a server instance (SHR1_PRD). The username and passwd are your username and password, the same as you would enter into SQLPLUS to access your account.
That's it! The connection returned in the last step is an open connection which we will use to pass SQL statements to the database. In this code snippet, con is an open connection, and we will use it below. Note:The values mentioned above are valid for our (Leland) environment. They would have different values in other environments.
Creating JDBC Statements
A JDBC Statement object is used to send your SQL statements to the DBMS, and should not to be confused with an SQL statement. A JDBC Statement object is associated with an open connection, and not any single SQL Statement. You can think of a JDBC Statement object as a channel sitting on a connection, and passing one or more of your SQL statements (which you ask it to execute) to the DBMS.
An active connection is needed to create a Statement object. The following code snippet, using our Connection object con, does it for you:
Statement stmt = con.createStatement() ;
At this point, a Statement object exists, but it does not have an SQL statement to pass on to the DBMS. We learn how to do that in a following section.
Creating JDBC PreparedStatement
Sometimes, it is more convenient or more efficient to use a PreparedStatement object for sending SQL statements to the DBMS. The main feature which distinguishes it from its superclass Statement, is that unlike Statement, it is given an SQL statement right when it is created. This SQL statement is then sent to the DBMS right away, where it is compiled. Thus, in effect, a PreparedStatement is associated as a channel with a connection and a compiled SQL statement.
The advantage offered is that if you need to use the same, or similar query with different parameters multiple times, the statement can be compiled and optimized by the DBMS just once. Contrast this with a use of a normal Statement where each use of the same SQL statement requires a compilation all over again.
PreparedStatements are also created with a Connection method. The following snippet shows how to create a parameterized SQL statement with three input parameters:
PreparedStatement prepareUpdatePrice = con.prepareStatement(
"UPDATE Sells SET price = ? WHERE bar = ? AND beer = ?");
Before we can execute a PreparedStatement, we need to supply values for the parameters. This can be done by calling one of the setXXX methods defined in the class PreparedStatement. Most often used methods are setInt, setFloat, setDouble, setString etc. You can set these values before each execution of the prepared statement.
Continuing the above example, we would write:
prepareUpdatePrice.setInt(1, 3);
prepareUpdatePrice.setString(2, "Bar Of Foo");
prepareUpdatePrice.setString(3, "BudLite");
Executing CREATE/INSERT/UPDATE Statements
Executing SQL statements in JDBC varies depending on the ``intention'' of the SQL statement. DDL (data definition language) statements such as table creation and table alteration statements, as well as statements to update the table contents, are all executed using the method executeUpdate. Notice that these commands change the state of the database, hence the name of the method contains ``Update''.
The following snippet has examples of executeUpdate statements.
Statement stmt = con.createStatement();
stmt.executeUpdate("CREATE TABLE Sells " +
"(bar VARCHAR2(40), beer VARCHAR2(40), price REAL)" );
stmt.executeUpdate("INSERT INTO Sells " +
"VALUES ('Bar Of Foo', 'BudLite', 2.00)" );
String sqlString = "CREATE TABLE Bars " +
"(name VARCHAR2(40), address VARCHAR2(80), license INT)" ;
stmt.executeUpdate(sqlString);
Since the SQL statement will not quite fit on one line on the page, we have split it into two strings concatenated by a plus sign(+) so that it will compile. Pay special attention to the space following "INSERT INTO Sells" to separate it in the resulting string from "VALUES". Note also that we are reusing the same Statement object rather than having to create a new one.
When executeUpdate is used to call DDL statements, the return value is always zero, while data modification statement executions will return a value greater than or equal to zero, which is the number of tuples affected in the relation.
While working with a PreparedStatement, we would execute such a statement by first plugging in the values of the parameters (as seen above), and then invoking the executeUpdate on it.
int n = prepareUpdatePrice.executeUpdate() ;
Executing SELECT Statements
As opposed to the previous section statements, a query is expected to return a set of tuples as the result, and not change the state of the database. Not surprisingly, there is a corresponding method called executeQuery, which returns its results as a ResultSet object:
String bar, beer ;
float price ;
ResultSet rs = stmt.executeQuery("SELECT * FROM Sells");
while ( rs.next() ) {
bar = rs.getString("bar");
beer = rs.getString("beer");
price = rs.getFloat("price");
System.out.println(bar + " sells " + beer + " for " + price + " Dollars.");
}
The bag of tuples resulting from the query are contained in the variable rs which is an instance of ResultSet. A set is of not much use to us unless we can access each row and the attributes in each row. The ResultSet provides a cursor to us, which can be used to access each row in turn. The cursor is initially set just before the first row. Each invocation of the method next causes it to move to the next row, if one exists and return true, or return false if there is no remaining row.
We can use the getXXX method of the appropriate type to retrieve the attributes of a row. In the previous example, we used getString and getFloat methods to access the column values. Notice that we provided the name of the column whose value is desired as a parameter to the method. Also note that the VARCHAR2 type bar, beer have been converted to Java String, and the REAL to Java float.
Equivalently, we could have specified the column number instead of the column name, with the same result. Thus the relevant statements would be:
bar = rs.getString(1);
price = rs.getFloat(3);
beer = rs.getString(2);
While working with a PreparedStatement, we would execute a query by first plugging in the values of the parameters, and then invoking the executeQuery on it.
ResultSet rs = prepareUpdatePrice.executeQuery() ;
Notes on Accessing ResultSet
JDBC also offers you a number of methods to find out where you are in the result set using getRow, isFirst, isBeforeFirst, isLast, isAfterLast.
There are means to make scroll-able cursors allow free access of any row in the result set. By default, cursors scroll forward only and are read only. When creating a Statement for a Connection, you can change the type of ResultSet to a more flexible scrolling or updatable model:
Statement stmt = con.createStatement(
ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
ResultSet rs = stmt.executeQuery("SELECT * FROM Sells");
The different options for types are TYPE_FORWARD_ONLY, TYPE_SCROLL_INSENSITIVE, and TYPE_SCROLL_SENSITIVE. You can choose whether the cursor is read-only or updatable using the options CONCUR_READ_ONLY, and CONCUR_UPDATABLE. With the default cursor, you can scroll forward using rs.next(). With scroll-able cursors you have more options:
rs.absolute(3); // moves to the third tuple
rs.previous(); // moves back one tuple (tuple 2)
rs.relative(2); // moves forward two tuples (tuple 4)
rs.relative(-3); // moves back three tuples (tuple 1)
There are a great many more details to the scroll-able cursor feature. Scroll-able cursors, though useful for certain applications, are extremely high-overhead, and should be used with restraint and caution. More information can be found at the New Features in the JDBC 2.0 API, where you can find a more detailed tutorial on the cursor manipulation techniques.
Transactions
JDBC allows SQL statements to be grouped together into a single transaction. Thus, we can ensure the ACID (Atomicity, Consistency, Isolation, Durability) properties using JDBC transactional features.
Transaction control is performed by the Connection object. When a connection is created, by default it is in the auto-commit mode. This means that each individual SQL statement is treated as a transaction by itself, and will be committed as soon as it's execution finished. (This is not exactly precise, but we can gloss over this subtlety for most purposes).
We can turn off auto-commit mode for an active connection with :
con.setAutoCommit(false) ;
and turn it on again with :
con.setAutoCommit(true) ;
Once auto-commit is off, no SQL statements will be committed (that is, the database will not be permanently updated) until you have explicitly told it to commit by invoking the commit() method:
con.commit() ;
At any point before commit, we may invoke rollback() to rollback the transaction, and restore values to the last commit point (before the attempted updates).
Here is an example which ties these ideas together:
con.setAutoCommit(false);
Statement stmt = con.createStatement();
stmt.executeUpdate("INSERT INTO Sells VALUES('Bar Of Foo', 'BudLite', 1.00)" );
con.rollback();
stmt.executeUpdate("INSERT INTO Sells VALUES('Bar Of Joe', 'Miller', 2.00)" );
con.commit();
con.setAutoCommit(true);
Lets walk through the example to understand the effects of various methods. We first set auto-commit off, indicating that the following statements need to be considered as a unit. We attempt to insert into the Sells table the ('Bar Of Foo', 'BudLite', 1.00) tuple. However, this change has not been made final (committed) yet. When we invoke rollback, we cancel our insert and in effect we remove any intention of inserting the above tuple. Note that Sells now is still as it was before we attempted the insert. We then attempt another insert, and this time, we commit the transaction. It is only now that Sells is now permanently affected and has the new tuple in it. Finally, we reset the connection to auto-commit again.
We can also set transaction isolation levels as desired. For example, we can set the transaction isolation level to TRANSACTION_READ_COMMITTED, which will not allow a value to be accessed until after it has been committed, and forbid dirty reads. There are five such values for isolation levels provided in the Connection interface. By default, the isolation level is serializable. JDBC allows us to find out the transaction isolation level the database is set to (using the Connection method getTransactionIsolation) and set the appropriate level (using the Connection method setTransactionIsolation method).
Usually rollback will be used in combination with Java's exception handling ability to recover from (un)predictable errors. Such a combination provides an excellent and easy mechanism for handling data integrity. We study error handling using JDBC in the next section.
Handling Errors with Exceptions
The truth is errors always occur in software programs. Often, database programs are critical applications, and it is imperative that errors be caught and handled gracefully. Programs should recover and leave the database in a consistent state. Rollback-s used in conjunction with Java exception handlers are a clean way of achieving such a requirement.
The client(program) accessing a server(database) needs to be aware of any errors returned from the server. JDBC give access to such information by providing two levels of error conditions: SQLException and SQLWarning. SQLExceptions are Java exceptions which, if not handled, will terminate the application. SQLWarnings are subclasses of SQLException, but they represent nonfatal errors or unexpected conditions, and as such, can be ignored.
In Java, statements which are expected to ``throw'' an exception or a warning are enclosed in a try block. If a statement in the try block throws an exception or a warning, it can be ``caught'' in one of the corresponding catch statements. Each catch statement specifies which exceptions it is ready to ``catch''.
Here is an example of catching an SQLException, and using the error condition to rollback the transaction:
try {
con.setAutoCommit(false) ;
stmt.executeUpdate("CREATE TABLE Sells (bar VARCHAR2(40), " +
"beer VARHAR2(40), price REAL)") ;
stmt.executeUpdate("INSERT INTO Sells VALUES " +
"('Bar Of Foo', 'BudLite', 2.00)") ;
con.commit() ;
con.setAutoCommit(true) ;
}catch(SQLException ex) {
System.err.println("SQLException: " + ex.getMessage()) ;
con.rollback() ;
con.setAutoCommit(true) ;
}
In this case, an exception is thrown because beer is defined as VARHAR2 which is a mis-spelling. Since there is no such data type in our DBMS, an SQLException is thrown. The output in this case would be:
Message: ORA-00902: invalid datatype
Alternatively, if your datatypes were correct, an exception might be thrown in case your database size goes over space quota and is unable to construct a new table. SQLWarnings can be retrieved from Connection objects, Statement objects, and ResultSet objects. Each only stores the most recent SQLWarning. So if you execute another statement through your Statement object, for instance, any earlier warnings will be discarded. Here is a code snippet which illustrates the use of SQLWarnings:
ResultSet rs = stmt.executeQuery("SELECT bar FROM Sells") ;
SQLWarning warn = stmt.getWarnings() ;
if (warn != null)
System.out.println("Message: " + warn.getMessage()) ;
SQLWarning warning = rs.getWarnings() ;
if (warning != null)
warning = warning.getNextWarning() ;
if (warning != null)
System.out.println("Message: " + warn.getMessage()) ;
SQLWarnings (as opposed to SQLExceptions) are actually rather rare -- the most common is a DataTruncation warning. The latter indicates that there was a problem while reading or writing data from the database.
Sample Code and Compilation Instructions
Hopefully, by now you are familiar enough with JDBC to write serious code. Here is a simple program which ties all the ideas in the tutorial together.
We have a few more pieces of sample code written by Craig Jurney at ITSS for educational purposes. Feel free to use sample code as a guideline or even a skeleton for code that you write in the future, but make a note that you were basing your solution on provided code.
SQLBuilder.java - Creation of a Relation
SQLLoader.java - Insertion of Tuples
SQLRunner.java - Processes Queries
SQLUpdater.java - Updating Tuples
SQLBatchUpdater.java - Batch Updating
SQLUtil.java - JDBC Utility Functions
Don't forget to use source /usr/class/cs145/all.env, which will correctly set your classpath. By adding this to your global classpath you simplify commands:
elaine19:~$ javac SQLBuilder.java
elaine19:~$ java SQLBuilder
instead of:
elaine19:~$ javac SQLBuilder.java
elaine19:~$ java -classpath /usr/pubsw/apps/oracle/8.1.5/jdbc/lib/classes111.zip:. SQLBuilder
There are static final values in each of the .java files for USERNAME and PASSWORD. These must be changed to your own username and your own password so that you can access the database.
This document was written originally by Nathan Folkert for Prof. Jennifer Widom's CS145 class, Spring 2000. Subsequently, it was hacked by Mayank Bawa for Prof. Jeff Ullman's CS145 class, Fall 2000. Jim Zhuang made a minor update for Summer 2005. Thanks to Matt Laue for typo correction.
Dynamic Web-based data access using JSP and JDBC technologies
This article discusses using the JSP and JDBC technologies to integrate static, dynamic, and database content in Web sites. For the purposes of simplicity and illustration, the JSP pages here use short scriptlets to expose the JSP developer to the underlying JDBC concepts instead of hiding them in custom tags. The author introduces a key design approach that integrates JavaBeans components with JDBC, similar to the way that JavaServer Pages technology already uses beans with HTTP. He also provides code for implementing this integration.
Building on the Java Servlet technology, JavaServer Pages (JSP) technology is the core server-side Java architecture for generating dynamic content. One source of dynamic content is the relational database. To manage everything from online communities to e-commerce transactions, Web sites use relational databases to store all sorts of information: catalog items, images, text, data about registered members, and so on. This article discusses the application of JSP technology to relational databases through Java Database Connectivity (JDBC). JDBC is the means by which Java programs work with relational databases.
To get the most out of this article, you should be familiar with JDBC and SQL.
JDBC basics
JDBC is the bridge between Java code and SQL databases. The primary JDBC objects represent connections to a database and the statements performed using those connections. The two basic kinds of statements used with a relational database are queries and updates. As a prerequisite to each, you first need to establish a connection to the database, which is done with the java.sql.DriverManager class. Connections take a long time (in computer time) to establish, so in a transaction-intensive environment like a Web server, you want to reuse connections whenever possible. Such reuse is called connection pooling.
If your JDBC skills are a bit rusty, the code snippet in Listing 1 illustrates how to establish a connection with a test database, create a statement object to use with that connection, issue an SQL query, process the results, and release the JDBC resources:
Listing 1. Simple JDBC code
| Connection connection = DriverManager.getConnection(URL, user, password); Statement statement = connection.createStatement(); ResultSet results = statement.executeQuery(sqlQuery); while (results.next()) { ... process query results ... logSQLWarnings(results.getWarnings()); } results.close(); statement.close(); connection.close(); |
In real life, JDBC code is not this simple; exceptions and warning conditions need to be handled listing 2 illustrates the same JDBC example but adds handling for JDBC exceptions and warnings. In this example, exceptions and warnings are simply logged and, in the case of exceptions, we abort the operation. However, the finally{} clauses ensure that resource cleanup proceeds.
The actual processing of the results is only hinted at here; we'll be looking at it more closely later on in this article. If we were performing a database update instead of a query, we would replace the while loop with the following:
int count = statement.executeUpdate(sqlUpdate);
|
|
|
The executeUpdate() method returns the number of rows affected by the update statement.
If the material in these code listings seems unfamiliar, you may want to spend some time reviewing some of the JDBC tutorial information found in the resources section
Using JDBC with JSP pages
So how do we combine JDBC and JSP technologies so that our dynamic content comes from a database?
As a general rule, good JSP practice suggests that you separate presentation from model behavior. This is analogous to the Model-View-Controller (MVC) paradigm in object-oriented programming. One reason for the separation is that applications based on JSP technology are likely to have the Model and Controller components authored by programmers, whereas the View components will be authored by page designers. In the case of JSP application architectures, the role of View, whose responsibility is presentation, is handled by a JSP page. The role of Controller, whose responsibility is reacting to requests, is often played by a servlet, but many JSP practitioners are coming to realize the advantages of using a JSP page in the Controller role. The role of Model, whose responsibility is modeling the behavior of application entities, is typically played by JavaBeans components.
In addition to deciding where in the MVC paradigm to interact with the database, you have several choices for integrating JDBC technology into your JSP pages. For example, you can insert JDBC using scriptlets, insert it using a tag library, or hide it within custom tags or other classes. We'll next look at examples of several approaches and discuss their use.
The first thing a new JSP programmer is likely to do is write a scriptlet to access JDBC. Perhaps it will be something like this example in Listing 3, which uses JDBC to implement a "hit counter" for the page. (A live version of this page is located on the JavaServer Pages Developers Guide Web site.)
Listing 3. JSP page using JDBC in a scriptlet
