JDBC


Topics interesting only for Java programmers

Link to this posting

Postby Ursego » 19 Aug 2019, 07:25

Copy the following fragment to a text file with .java extension.

To enjoy keywords coloring, open this file in Eclipse. After that, enable word-wrap by pressing Alt + Shift + Y (otherwise you will need to scroll right to read long comments). Set the code editor width so the very first line of the file (with asterisks) fits one line (i.e. is not broken into 2 lines).

You can also open this file in Notepad++. It will understand by the file extension (.java), that it's Java code, and paint keywords. After that, enable word-wrap by marking the menu option View > Word wrap.

In any editor, use the Fixedsys font (as I did) to see straight vertical alignment.

Code: Select all
//////////////////////
// package java.sql //
//////////////////////

// @@@@@@@ Interface Wrapper:

public interface Wrapper

// Interface for JDBC classes which provide the ability to retrieve the delegate instance when the instance in question is in fact a proxy class. The wrapper pattern is employed by many JDBC driver implementations to provide extensions beyond the traditional JDBC API that are specific to a data source. Developers may wish to gain access to these resources that are wrapped (the delegates) as proxy class instances representing the the actual resources. This interface describes a standard mechanism to access these wrapped resources represented by their proxy, to permit direct access to the resource delegates.

<T>      unwrap(Class<T> iface) throws SQLException // Returns an object that implements the given interface to allow access to non-standard methods, or standard methods not exposed by the proxy.
                                       // If the receiver implements the interface then the result is the receiver or a proxy for the receiver. If the receiver is a wrapper and the wrapped object implements
                                       // the interface then the result is the wrapped object or a proxy for the wrapped object. Otherwise return the the result of calling unwrap recursively on the wrapped object
                                       // or a proxy for that result. If the receiver is not a wrapper and does not implement the interface, then an SQLException is thrown.

boolean   isWrapperFor(Class<?> iface) throws SQLException // Returns true if this either implements the interface argument or is directly or indirectly a wrapper for an object that does. Returns false otherwise.
                                             // If this implements the interface then return true, else if this is a wrapper then return the result of recursively calling isWrapperFor() on the wrapped object.
                                             // If this does not implement the interface and is not a wrapper, return false. This method should be implemented as a low-cost operation compared to unwrap so that
                                             // callers can use this method to avoid expensive unwrap() calls that may fail. If this method returns true then calling unwrap() with the same argument should succeed.

// @@@@@@@ Interface Statement:

public interface Statement extends Wrapper, AutoCloseable

// The object used for executing an SQL statement and returning the results it produces. By default, only one ResultSet object per Statement object can be open at the same time. Therefore, if the reading of one ResultSet object is interleaved with the reading of another, each must have been generated by different Statement objects. All execution methods in the Statement interface implicitly close a statment's current ResultSet object if an open one exists.

// Created using createStatement() method of a Connection object.

boolean      execute(String sql) // Executes the given SQL statement, which may return multiple results. Returns true if the first result is a ResultSet object; false if it is an update count or there are no results
boolean      execute(String sql, int autoGeneratedKeys) // Signals the driver that any auto-generated keys should be made available for retrieval.
boolean      execute(String sql, int[] columnIndexes)   // Signals the driver that the auto-generated keys indicated in the given array should be made available for retrieval.
boolean      execute(String sql, String[] columnNames)
int[]      executeBatch() // Submits a batch of commands to the database for execution and if all commands execute successfully, returns an array of update counts.
ResultSet   executeQuery(String sql) // Executes the given SQL statement, which returns a SINGLE ResultSet object.
ResultSet   getResultSet() // Retrieves the current result as a ResultSet object.
int         executeUpdate(String sql) // Executes the given SQL statement, which may be an INSERT, UPDATE, or DELETE statement or an SQL statement that returns nothing, such as an SQL DDL statement.
int         executeUpdate(String sql, int autoGeneratedKeys) // Signals the driver with the given flag about whether the auto-generated keys produced by this Statement object should be made available for retrieval.
int         executeUpdate(String sql, int[] columnIndexes) // Signals the driver that the auto-generated keys indicated in the given array should be made available for retrieval.
int         executeUpdate(String sql, String[] columnNames)
void      cancel() // Cancels this Statement object if both the DBMS and driver support aborting an SQL statement.
void      close() // Releases this Statement object's database and JDBC resources immediately instead of waiting for this to happen when it is automatically closed.

// More methods: https://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html

// Example:
int rows;
Connection conn = ...
Statement stmt = conn.createStatement(); 

stmt.execute("UPDATE emp SET age = 40 WHERE id in (5, 6)");
System.out.println("Updated rows: " + stmt.getUpdateCount() );

rows = stmt.executeUpdate("INSERT INTO emp (name, age) VALUES ('Kim', 23)");
System.out.println("Rows Inserted: " + rows);

rows = stmt.executeUpdate("UPDATE emp SET age = 35 WHERE id = 17");
System.out.println("Rows Updated: " + rows);

rows = stmt.executeUpdate("DELETE FROM emp WHERE id = 5");
System.out.println("Rows Deleted: " + rows);

ResultSet rs = stmt.executeQuery("SELECT * FROM emp");
while (rs.next()) {
   System.out.println("id : " + rs.getInt("id") + " Name : " + rs.getString("name") + " Age : " + rs.getInt("age"));
}

// @@@@@@@ Interface PreparedStatement:

public interface PreparedStatement extends Statement

// An object that represents a precompiled SQL statement. A SQL statement is precompiled and stored in a PreparedStatement object. This object can then be used to efficiently execute this statement multiple times. The setter methods (setShort, setString, and so on) for setting IN parameter values must specify types that are compatible with the defined SQL type of the input parameter. For instance, if the IN parameter has SQL type INTEGER, then the method setInt should be used. If arbitrary parameter type conversions are required, the method setObject should be used with a target SQL type.

// Created using prepareStatement() method of a Connection object. In the following example of setting a parameter, con represents an active connection:

void setArray(int parameterIndex, Array x) // Sets the designated parameter to the given java.sql.Array object.
void setBigDecimal(int parameterIndex, BigDecimal x) // Sets the designated parameter to the given java.math.BigDecimal value.
void setBoolean(int parameterIndex, boolean x) // Sets the designated parameter to the given Java boolean value.
void setByte(int parameterIndex, byte x) // Sets the designated parameter to the given Java byte value.
void setBytes(int parameterIndex, byte[] x) // Sets the designated parameter to the given Java array of bytes.
void setDate(int parameterIndex, Date x) // Sets the designated parameter to the given java.sql.Date value using the default time zone of the virtual machine that is running the application.
void setDate(int parameterIndex, Date x, Calendar cal) // Sets the designated parameter to the given java.sql.Date value, using the given Calendar object.
void setDouble(int parameterIndex, double x) // Sets the designated parameter to the given Java double value.
void setFloat(int parameterIndex, float x) // Sets the designated parameter to the given Java float value.
void setInt(int parameterIndex, int x) // Sets the designated parameter to the given Java int value.
void setLong(int parameterIndex, long x) // Sets the designated parameter to the given Java long value.
void setNull(int parameterIndex, int sqlType) // Sets the designated parameter to SQL NULL.
void setNull(int parameterIndex, int sqlType, String typeName) // Sets the designated parameter to SQL NULL.
void setObject(int parameterIndex, Object x) // Sets the value of the designated parameter using the given object.
void setObject(int parameterIndex, Object x, int targetSqlType) // Sets the value of the designated parameter with the given object.
void setObject(int parameterIndex, Object x, int targetSqlType, int scaleOrLength) // Sets the value of the designated parameter with the given object.
void setShort(int parameterIndex, short x) // Sets the designated parameter to the given Java short value.
void setString(int parameterIndex, String x) // Sets the designated parameter to the given Java String value.
void setTime(int parameterIndex, Time x) // Sets the designated parameter to the given java.sql.Time value.
void setTime(int parameterIndex, Time x, Calendar cal) // Sets the designated parameter to the given java.sql.Time value, using the given Calendar object.

boolean      execute() // Executes the SQL statement in this PreparedStatement object, which may be any kind of SQL statement. The execute method returns a boolean to indicate the form of the first result
                  // (true if the first result is a ResultSet object; false if the first result is an update count or there is no result). You must call either the method getResultSet() or getUpdateCount()
                  // to retrieve the result; you must call getMoreResults to move to any subsequent result(s).
ResultSet   executeQuery() // Executes the SQL query in this PreparedStatement object and returns the ResultSet object generated by the query.
int         executeUpdate() // Executes the SQL statement in this PreparedStatement object, which must be a DML statement, such as INSERT, UPDATE or DELETE; or an SQL statement that returns nothing, such as a DDL statement.
                     // Returns the affected rows count for DML statements or 0 for SQL statements that return nothing.
int         getUpdateCount() // Retrieves the current result as an update count; if the result is a ResultSet object or there are no more results, -1 is returned.

// Example:
PreparedStatement updSalary = con.prepareStatement("UPDATE EMPLOYEES SET SALARY = ? WHERE ID = ?");
updSalary.setBigDecimal(1, 153833.00)
updSalary.setInt(2, 110592)
int rowsUpdated = updSalary.executeUpdate()

// @@@@@@@ Interface CallableStatement:

public interface CallableStatement extends PreparedStatement

// Used to execute SQL stored procedures. The JDBC API provides a stored procedure SQL escape syntax that allows stored procedures to be called in a standard way for all RDBMSs. This escape syntax has one form that includes a result parameter and one that does not. If used, the result parameter must be registered as an OUT parameter. The other parameters can be used for input, output or both. Parameters are referred to sequentially, by number, with the first parameter being 1.
"{?= call <procedure-name>[(?,<?, ...)]}"
"{call <procedure-name>[(?,?, ...)]}"
 
// IN parameter values are set using the set methods inherited from PreparedStatement. The type of all OUT parameters must be registered prior to executing the stored procedure; their values are retrieved after execution via the get methods provided here. A CallableStatement can return one ResultSet object or multiple ResultSet objects. Multiple ResultSet objects are handled using operations inherited from Statement.

// Created using prepareCall() method of a Connection object.

// Methods to register OUT parameters (before the CallableStatement is executed):
void registerOutParameter(int parameterIndex, int sqlType) // Registers the OUT parameter in ordinal position parameterIndex to the JDBC type sqlType (pass a java.sql.Types constant).
void registerOutParameter(int parameterIndex, int sqlType, int scale) // scale - the desired number of digits to the right of the decimal point. It must be greater than or equal to zero.
void registerOutParameter(int parameterIndex, int sqlType, String typeName) // typeName - the fully-qualified name of an SQL structured type (provide it for user-defined and REF parameters)
void registerOutParameter(String parameterName, int sqlType) // Registers the OUT parameter named parameterName to the JDBC type sqlType.
void registerOutParameter(String parameterName, int sqlType, int scale)
void registerOutParameter(String parameterName, int sqlType, String typeName)

// Methods to read OUT parameters (after the CallableStatement is executed):
String      getString(String parameterName)      // Retrieves the value of a JDBC CHAR, VARCHAR, or LONGVARCHAR   parameter as a   String
boolean      getBoolean(String parameterName)   // Retrieves the value of a JDBC BIT or BOOLEAN               parameter as a   boolean
byte      getByte(String parameterName)      // Retrieves the value of a JDBC TINYINT                  parameter as a   byte
short      getShort(String parameterName)      // Retrieves the value of a JDBC SMALLINT                  parameter as a   short
int         getInt(String parameterName)      // Retrieves the value of a JDBC INTEGER                  parameter as an   int
long      getLong(String parameterName)      // Retrieves the value of a JDBC BIGINT                     parameter as a   long
float      getFloat(String parameterName)      // Retrieves the value of a JDBC FLOAT                     parameter as a   float
double      getDouble(String parameterName)      // Retrieves the value of a JDBC DOUBLE                     parameter as a   double
byte[]      getBytes(String parameterName)      // Retrieves the value of a JDBC BINARY or VARBINARY         parameter as an   array of byte values
Date      getDate(String parameterName)      // Retrieves the value of a JDBC DATE                     parameter as a   java.sql.Date object
Time      getTime(String parameterName)      // Retrieves the value of a JDBC TIME                     parameter as a   java.sql.Time object
BigDecimal   getBigDecimal(String parameterName)   // Retrieves the value of a JDBC NUMERIC                  parameter as a   java.math.BigDecimal object with as many digits to the right of the decimal point as the value contains
Array      getArray(String parameterName)      // Retrieves the value of a JDBC ARRAY                     parameter as an   Array object
Object      getObject(String parameterName)      // Retrieves the value of a                              parameter as an Object. If the value is an SQL NULL, the driver returns a Java null.
                                          // This method returns a Java object whose type corresponds to the JDBC type that was registered for this parameter using the method registerOutParameter.
                                          // By registering the target JDBC type as java.sql.Types.OTHER, this method can be used to read database-specific abstract data types.
Object      getObject(String parameterName, Map<String,Class<?>> map) // Uses map for the custom mapping of the parameter value.

// Example:
Connection conn = ...
CallableStatement stmt = conn.prepareCall("{call addEmployeeInfo(?,?,?)};");
stmt.setString(1, name);
stmt.setString(2, email);
stmt.setInt(3, salary);
stmt.executeUpdate();
stmt.close();
conn.close();

// Example with OUT parameter:
/*
CREATE PROCEDURE 'EMP'.'getEmpName'
   (IN EMP_ID INT, OUT EMP_FIRST VARCHAR(255))
BEGIN
   SELECT first INTO EMP_FIRST
   FROM Employees
   WHERE ID = EMP_ID;
END
*/
Connection conn = ...
CallableStatement stmt = conn.prepareCall("{call getEmpName (?, ?)}");
stmt.setInt(1, empID);
stmt.registerOutParameter(2, java.sql.Types.VARCHAR);
stmt.execute();
String empName = stmt.getString(2);
stmt.close();
conn.close();

// @@@@@@@ Interface Connection:

public interface Connection extends Wrapper, AutoCloseable

// A connection (session) with a specific database. SQL statements are executed and results are returned within the context of a connection. 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.

int               getHoldability() // Retrieves the current holdability of ResultSet objects created using this Connection object (ResultSet.HOLD_CURSORS_OVER_COMMIT or ResultSet.CLOSE_CURSORS_AT_COMMIT)
void            setHoldability(int holdability) // Changes the default holdability of ResultSet objects created using this Connection object to the given holdability.
Statement          createStatement() // Creates a Statement object for sending SQL statements to the database.
Statement          createStatement(int resultSetType, int resultSetConcurrency) // Creates a Statement object that will generate ResultSet objects with the given type
                              // (ResultSet.TYPE_FORWARD_ONLY, ResultSet.TYPE_SCROLL_INSENSITIVE, or ResultSet.TYPE_SCROLL_SENSITIVE) and concurrency (ResultSet.CONCUR_READ_ONLY or ResultSet.CONCUR_UPDATABLE).
Statement          createStatement(int resultSetType, int resultSetConcurrency, int resultSetHoldability) // Creates a Statement object that will generate ResultSet objects with the given type, concurrency, and holdability.
PreparedStatement   prepareStatement(String sql) // Creates a PreparedStatement object for sending parameterized SQL statements to the database.
PreparedStatement   prepareStatement(String sql, String[] columnNames) // Creates a default PreparedStatement object capable of returning the auto-generated keys designated by the given array.
PreparedStatement   prepareStatement(String sql, int[] columnIndexes) // Creates a default PreparedStatement object capable of returning the auto-generated keys designated by the given array.
PreparedStatement   prepareStatement(String sql, int autoGeneratedKeys) // Creates a default PreparedStatement object that has the capability to retrieve auto-generated keys.
PreparedStatement   prepareStatement(String sql, int resultSetType, int resultSetConcurrency)
PreparedStatement   prepareStatement(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability)
CallableStatement   prepareCall(String sql) // Creates a CallableStatement object for calling database stored procedures.
CallableStatement   prepareCall(String sql, int resultSetType, int resultSetConcurrency) // Creates a CallableStatement object that will generate ResultSet objects with the given type and concurrency.
CallableStatement   prepareCall(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) // Creates a CallableStatement object that will generate ResultSet objects with the given type and concurrency.

// commit(), rollback(), setAutoCommit(boolean autoCommit), close() etc. - https://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html

// @@@@@@@ Interface ResultSet:

public interface ResultSet extends Wrapper, AutoCloseable

// 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.

// Constants:
static int   CONCUR_READ_ONLY // The concurrency mode for a ResultSet object that may NOT be updated.
static int   CONCUR_UPDATABLE // The concurrency mode for a ResultSet object that may be updated.
static int   FETCH_FORWARD // The rows in a result set will be processed in a forward direction; first-to-last.
static int   FETCH_REVERSE // The rows in a result set will be processed in a reverse direction; last-to-first.
static int   FETCH_UNKNOWN // The order in which rows in a result set will be processed is unknown.
static int   HOLD_CURSORS_OVER_COMMIT // The open ResultSet objects with this holdability will remain open when the current transaction is commited.
static int   CLOSE_CURSORS_AT_COMMIT // The open ResultSet objects with this holdability will be closed when the current transaction is commited.
static int   TYPE_FORWARD_ONLY // The type for a ResultSet object whose cursor may move only forward.
static int   TYPE_SCROLL_INSENSITIVE // The type for a ResultSet object that is scrollable but generally not sensitive to changes to the data that underlies the ResultSet.
static int   TYPE_SCROLL_SENSITIVE // The type for a ResultSet object that is scrollable and generally sensitive to changes to the data that underlies the ResultSet.

// 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:
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery("SELECT first_name, last_name FROM emp"); // rs will be scrollable, will not show changes made by others, and will be updatable

// Methods (ALL throw SQLException):
boolean   next() // Moves the cursor forward one row from its current position. Moves the cursor forward one row from its current position. A ResultSet cursor is initially positioned before the first row;
            // the first call to the method next makes the first row the current row; the second call makes the second row the current row, and so on. When a call to the next method returns false,
            // the cursor is positioned after the last row. Any invocation of a ResultSet method which requires a current row will result in a SQLException being thrown.
boolean   previous() // Moves the cursor to the previous row in this ResultSet object. When a call to the previous method returns false, the cursor is positioned before the first row.
boolean absolute(int row) // Moves the cursor to the given row number in this ResultSet object.
boolean   relative(int rows) // Moves the cursor a relative number of rows, either positive or negative. Attempting to move beyond the first/last row in the result set positions the cursor before/after the the first/last row.
                     // Calling the method relative(1) is identical to calling the method next() and calling the method relative(-1) is identical to calling the method previous().
boolean   first() // Moves the cursor to the first row in this ResultSet object.
boolean   last() // Moves the cursor to the last row in this ResultSet object.
boolean   isFirst() // Reports whether the cursor is on the first row of this ResultSet object.
boolean   isLast() // Reports whether the cursor is on the last row of this ResultSet object
int      getRow() // Reports the current row number. The first row is number 1, the second number 2, and so on.

void   moveToInsertRow() // Moves the cursor to the insert row. The current cursor position is remembered while the cursor is positioned on the insert row. The insert row is a special row associated with an updatable result set.
                     // It is essentially a buffer where a new row may be constructed by calling the updater methods prior to inserting the row into the result set. Only the updater, getter, and insertRow methods may be
                     // called when the cursor is on the insert row. All of the columns in a result set must be given a value each time this method is called before calling insertRow.
                     // An updater method must be called before a getter method can be called on a column value.
void   insertRow() // Inserts the contents of the insert row into this ResultSet object AND INTO THE DATABASE. The cursor must be on the insert row when this method is called.
void   moveToCurrentRow() // Moves the cursor to the remembered cursor position, usually the current row. This method has no effect if the cursor is not on the insert row.

void   updateRow() // Updates the underlying database with the new contents of the current row of this ResultSet object.
void   close() // Releases this ResultSet object's database and JDBC resources immediately instead of waiting for this to happen when it is automatically closed.

// GETTER METHODS: getBoolean, getString, getInt, getLong, getFloat, getDouble, getTime, getByte and getObject:
// Return value of the designated column in the current row.  Each method has 2 overloads: one accepts int (returns the value by the column index), another - String (returns the value by the column name). In general, using the column index will be more efficient. Columns are numbered from 1. For maximum portability, result set columns within each row should be read in left-to-right order, and each column should be read only once. Column names used as input to getter methods are case insensitive. The column name option is designed to be used when column names are used in the SQL query that generated the result set. For columns that are NOT explicitly named in the query, it is best to use column numbers. If column names are used, the programmer should take care to guarantee that they uniquely refer to the intended columns, which can be assured with the SQL AS clause. A ResultSet object is automatically closed when the Statement object that generated it is closed, re-executed, or used to retrieve the next result from a sequence of multiple results.

// UPDATER METHODS: updateInt, updateLong, updateObject, updateShort, updateTime:
// Update the designated column with the new value. Each method have 2 parameters: the column (by name or by index) and the new value. For example, updateInt has 2 overloads: (int columnIndex, int x) and (String columnLabel, int x). Also, there is the method updateNull with one parameter (column) - 2 overloads as well. The updater methods may be used in two ways:

// 1. to update a column value in the CURRENT row. In a scrollable ResultSet object, the cursor can be moved backwards and forwards, to an absolute position, or to a position relative to the current row. The following code fragment updates the NAME column in the fifth row of the ResultSet object rs and then uses the method updateRow to update the data source table from which the RecordSet was derived:

rs.absolute(5); // moves the cursor to the fifth row of the RecordSet
rs.updateString("NAME", "AINSWORTH"); // updates the NAME column of row 5 to be AINSWORTH
rs.updateRow(); // updates the row in the data source

// 2. to insert column values into the INSERT row. An updatable ResultSet object has a special row associated with it that serves as a staging area for building a row to be inserted. The following code fragment moves the cursor to the insert row, builds a three-column row, and inserts it into the RecordSet and into the data source table using the method insertRow.

rs.moveToInsertRow(); // moves cursor from the current row to the insert row; the current row is remembered to allow the cursor to return to it after the new row has been inserted
rs.updateString(1, "AINSWORTH"); // updates the first column of the insert row to be AINSWORTH
rs.updateInt(2, 35); // updates the second column to be 35
rs.updateBoolean(3, true); // updates the third column to true
rs.insertRow(); // insert the contents of the insert row into the ResultSet and the DB table
rs.moveToCurrentRow(); // return to the remembered cursor position

// @@@@@@@ Class JDBCConnectionPool:

public interface JDBCConnectionPool extends Serializable

// Interface for implementing connection pools to be used with SQLContainer. All Known Implementing Classes: J2EEConnectionPool, SimpleJDBCConnectionPool.

void      destroy() // Destroys the connection pool: close() is called an all the connections in the pool, whether available or reserved.
void      releaseConnection(Connection conn) // Releases a connection that was retrieved earlier.
Connection   reserveConnection() throws SQLException // Retrieves a connection.
User avatar
Ursego
Site Admin
 
Posts: 143
Joined: 19 Feb 2013, 20:33



Ketones are a more high-octane fuel for your brain than glucose. Become a biohacker and upgrade yourself to version 2.0!



cron
Traffic Counter

eXTReMe Tracker