JSP SQL Actions and DB Beans

All of the function available through these JSP actions is also available in JSP pages by using the jsp:useBean action to create a DBSelect, DBProcedureCall, or DBModify bean and then using scriptlets to execute methods on the beans. The JSP SQL actions are provided for convenience when the full flexibility of the DB Beans is not required. The limitations when using the JSP SQL actions vs. using the beans directly include the following:

Test Tutorial Link

• For any of the JSP SQL actions that require a connection to the database, a connection will be opened when the tag is encountered, and closed after the tag has been processed. Two actions cannot be performed within the same transaction scope, or even using the same JDBC connection. The only exception to this is via the x:batch action. Actions inside the body of the x:batch do share the same connection, and optionally, the same transaction.

  Using the DB Beans directly, you have complete control over when a database connection is opened and closed. You also have complete control over transaction scopes, with the ability to turn AutoCommit on or off and to do explicit commits or rollbacks.

  The reason for limiting the control over connections when using the JSP SQL actions is to insure that transactions and connections are not inadvertently left open, tying up database resources.

• Some of the methods and properties of the DBSelect and DBProcedureCall beans for handling large result sets are not offered via the JSP SQL actions. These methods and properties allow you to limit the number of rows maintained in memory at any one time and to specify how many rows to fetch at once when getting additional rows. This limitation is necessary because of the above limitation that the database connection is closed after each JSP SQL action is processed. If only a subset of the rows is initially fetched into memory, and then the connection is closed, there is no way to later fetch the remaining rows. The JSP SQL actions do provide some support for large result sets via the maxRows attribute of the x:select and x:procedureCall actions. This attribute simply limits the number of rows that will be fetched in any one result set.

• The lockRows property of the DBSelect and DBProcedureCall bean is not offered via the JSP SQL actions. This property causes a database lock to be kept on a row in the result set while it is the current row. For a web application, it is not likely that you would wish to maintain such a lock across user interactions which could span an arbitrary amount of time. Because of the first limitation above, that the database connection is closed after each JSP SQL action is processed, it is not possible for us to maintain such a lock when you use the JSP SQL tags. When row locking is not used, either with the JSP SQL tags or with direct use of the DB Beans, "optimistic" locking is still used to prevent you from updating a row if someone else updates it between the time that you read it and the time that you attempt to update it.

• A greater variety of methods for moving between rows and between result sets is available through direct use of the DB Beans than through the JSP SQL actions. The primary reason for not offering them is simply the expected frequency of use. We want to keep the JSP SQL actions simple, and not clutter the interface with methods that will seldom be used.

You can get and set any properties of the DBSelect, DBModify, and DBProcedureCall beans using the standard JSP actions <jsp:getProperty> and <jsp:setProperty>, but some of them will not prove useful. (For example, you could set the lockRows property of a DBSelect bean, but it would not accomplish anything for the reasons described above.)

You can also mix use of actions in the JSP SQL Tag Library and direct use of the DB Beans. If you create a DBSelect bean using the x:select action, you can execute its methods in scriptlets.