javax.sql and java.sql

1. javax.sql and java.sql

3. java.sql Interface Connection public interface Connection extends Wrapper A connection (session) with a specific database. SQL statements are executed and results are returned within the context of a connection. A Connection object's database is able to provide information describing its tables, its supported SQL grammar, its stored procedures, the capabilities of this connection, and so on. This information is obtained with the getMetaData method.

4. Interface Connection • Note: When configuring a Connection, JDBC applications should use the appropritate Connection method such as setAutoCommit or setTransactionIsolation. Applications should not invoke SQL commands directly to change the connection's configuration when there is a JDBC method available. By default a Connection object is in auto-commit mode, which means that it automatically commits changes after executing each statement. If auto-commit mode has been disabled, the method commit must be called explicitly in order to commit changes; otherwise, database changes will not be saved.

5. Connection property inspector • getCatalog • StringgetCatalog() throws SQLException • Retrieves this Connection object's current catalog name. • Returns: • the current catalog name or null if there is none • Throws: • SQLException - if a database access error occurs or this method is called on a closed connection

6. Interface Connection • setHoldability • void setHoldability(int holdability) throws SQLException • Changes the default holdability of ResultSet objects created using this Connection object to the given holdability. The default holdability of ResultSet objects can be be determined by invoking DatabaseMetaData.getResultSetHoldability(). • Parameters: • holdability - a ResultSet holdability constant; one of ResultSet.HOLD_CURSORS_OVER_COMMIT or ResultSet.CLOSE_CURSORS_AT_COMMIT • Throws: • SQLException - if a database access occurs, this method is called on a closed connection, or the given parameter is not a ResultSet constant indicating holdability • SQLFeatureNotSupportedException - if the given holdability is not supported

7. Interface Connection • setReadOnly • void setReadOnly(boolean readOnly) throws SQLException • Puts this connection in read-only mode as a hint to the driver to enable database optimizations. Note: This method cannot be called during a transaction. • Parameters: • readOnly - true enables read-only mode; false disables it • Throws: • SQLException - if a database access error occurs, this method is called on a closed connection or this method is called during a transaction • ____________________________________________________________________ • isReadOnly • boolean isReadOnly() throws SQLException • Retrieves whether this Connection object is in read-only mode. • Returns: • true if this Connection object is read-only; false otherwise • Throws: • SQLException - SQLException if a database access error occurs or this method is called on a closed connection

8. Interface Connection • setTransactionIsolation • void setTransactionIsolation(int level) throws SQLException • Attempts to change the transaction isolation level for this Connection object to the one given. The constants defined in the interface Connection are the possible transaction isolation levels. Note: If this method is called during a transaction, the result is implementation-defined. • Parameters: • level - one of the following Connection constants: Connection.TRANSACTION_READ_UNCOMMITTED, Connection.TRANSACTION_READ_COMMITTED, Connection.TRANSACTION_REPEATABLE_READ, or Connection.TRANSACTION_SERIALIZABLE. (Note that Connection.TRANSACTION_NONE cannot be used because it specifies that transactions are not supported.) • Throws: • SQLException - if a database access error occurs, this method is called on a closed connection or the given parameter is not one of the Connection constants

9. Interface Connection • setTypeMap • void setTypeMap(Map<String,Class<?>> map) throws SQLException • Installs the given TypeMap object as the type map for this Connection object. The type map will be used for the custom mapping of SQL structured types and distinct types. • Parameters: • map - the java.util.Map object to install as the replacement for this Connection object's default type map • Throws: • SQLException - if a database access error occurs, this method is called on a closed connection or the given parameter is not a java.util.Map object • SQLFeatureNotSupportedException - if the JDBC driver does not support this method

10. java.sql Interface ResultSet public interface ResultSet extends Wrapper • A table of data representing a database result set, which is usually generated by executing a statement that queries the database. • A ResultSet object maintains a cursor pointing to its current row of data. Initially the cursor is positioned before the first row. The next method moves the cursor to the next row, and because it returns false when there are no more rows in the ResultSet object, it can be used in a while loop to iterate through the result set. • A default ResultSet object is not updatable and has a cursor that moves forward only. Thus, you can iterate through it only once and only from the first row to the last row. It is possible to produce ResultSet objects that are scrollable and/or updatable. The following code fragment, in which con is a valid Connection object, illustrates how to make a result set that is scrollable and insensitive to updates by others, and that is updatable. See ResultSet fields for other options.

11. Result Set types The JDBC 1.0 API provided one result set type—forward-only. The JDBC 2.1 core APIprovides three result set types: forward-only, scroll-insensitive, and scroll-sensitive. Astheir names suggest, the new result set types support scrolling, but they differ in theirability to make changes visible while they are open.

12. Result Set types A scroll-insensitive result set is generally not sensitive to changes that are made while it is open. A scroll-insensitive result set provides a static view of the underlying data it contains. The membership, order, and column values of rows in a scroll-insensitive result set are typically fixed when the result set is created.

13. Result Set types On the other hand, a scroll-sensitive result set is sensitive to changes that are made while it is open, and provides a ‘dynamic’ view of the underlying data. For example, when using a scroll-sensitive result set, changes in the underlying column values of rows are visible. The membership and ordering of rows in the result set may be fixed— this is implementation defined.

14. Examples • The example below illustrates creation of a result set that is forward-only and uses readonlyconcurrency. Connection con = DriverManager.getConnection( "jdbc:my_subprotocol:my_subname"); Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery( "SELECT emp_no, salary FROM employees");

15. Examples • The next example creates a scrollable result set that is updatable and sensitive to updates. • Rows of data are requested to be fetched twenty-five at-a-time from the database. Connection con = DriverManager.getConnection( "jdbc:my_subprotocol:my_subname"); Statement stmt = con.createStatement( ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); stmt.setFetchSize(25); ResultSet rs = stmt.executeQuery( "SELECT emp_no, salary FROM employees");

16. Examples • The example below creates a result set with the same attributes as the previous example,however, a prepared statement is used to produce the result set. • JDBC 2.1 Core API • Sun Microsystems Inc. 14 October 5, 1999 PreparedStatement pstmt = con.prepareStatement( "SELECT emp_no, salary FROM employees where emp_no = ?", ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); pstmt.setFetchSize(25); pstmt.setString(1, "100010"); ResultSet rs = pstmt.executeQuery();

17. Due to differences in database implementations, the JDBC API does not specify an exactset of SQL queries which must yield anupdatable result set for JDBC drivers thatsupport updatability.Developers can, however, generally expect queries which meetthe following criteria to produce an updatable result set: 1. The query references only a single table in the database. 2. The query does not contain any join operations. 3. The query selects the primary key of the table it references.In addition, an SQL query should also satisfy the conditions listed below if inserts areto be performed. 4. The query selects all of the non-nullable columns in the underlying table. 5. The query selects all columns that don’t have a default value. http://minitorn.tlu.ee/~jaagup/kool/java/abiinfo/docs/6/docs/api/java/sql/ResultSet.html

18. javax.sql.ConnectionEvent • javax.sql.ConnectionEventListener • javax.sql.ConnectionPoolDataSurce • javax.sql.DataSource • javax.sql.PooledConnection • javax.sql.RowSet • javax.sql.RowSetEvent • javax.sql.RowSetInternal • javax.sql.RowSetListener • javax.sql.RowSetMetaData • javax.sql.RowSetReader • javax.sql.RowSetWriter • javax.sql.XAConnection • javax.sql.XADataSource

19. javax.sql Interface PooledConnection • public interface PooledConnection • An object that provides hooks for connection pool management. A PooledConnection object represents a physical connection to a data source. The connection can be recycled rather than being closed when an application is finished with it, thus reducing the number of connections that need to be made. • An application programmer does not use the PooledConnection interface directly; rather, it is used by a middle tier infrastructure that manages the pooling of connections. • When an application calls the method DataSource.getConnection, it gets back a Connection object. If connection pooling is being done, that Connection object is actually a handle to a PooledConnection object, which is a physical connection. • The JDBC driver implementing ConnectionPoolDataSource creates a new PooledConnection object and returns a handle to it.

20. Interface PooledConnection • When an application closes a connection, it calls the Connection method close. When connection pooling is being done, the connection pool manager is notified because it has registered itself as a ConnectionEventListener object using the ConnectionPool method addConnectionEventListener. The connection pool manager deactivates the handle to the PooledConnection object and returns the PooledConnection object to the pool of connections so that it can be used again. Thus, when an application closes its connection, the underlying physical connection is recycled rather than being closed. • The physical connection is not closed until the connection pool manager calls the PooledConnection method close. This method is generally called to have an orderly shutdown of the server or if a fatal error has made the connection unusable.

21. Interface PooledConnection Method Summary voidaddConnectionEventListener(ConnectionEventListener listener)           Registers the given event listener so that it will be notified when an event occurs on this PooledConnection object. _______________________________________________________________ voidaddStatementEventListener(StatementEventListener listener)           Registers a StatementEventListener with this PooledConnection object. _______________________________________________________________ voidclose()           Closes the physical connection that this PooledConnection object represents. _______________________________________________________________ ConnectiongetConnection()           Creates and returns a Connection object that is a handle for the physical connection that this PooledConnection object represents.

22. Interface PooledConnection Method Summary voidremoveConnectionEventListener(ConnectionEventListener listener)           Removes the given event listener from the list of components that will be notified when an event occurs on this PooledConnection object. ________________________________________________________________ voidremoveStatementEventListener(StatementEventListener listener)           Removes the specified StatementEventListener from the list ofcomponents that will be notified when the driver detects that aPreparedStatement has been closed or is invalid.