JDBC and Database related notes

JDBC Connection steps

* loading driver class - Class.ForName("driver class").newInstance();
* Connection - Connection con = DriverManager.getConnection("databaseurl","username","passwprd");
* statement - Statement stmt = con.CreateStatement();
* execute sql - stmt.executeQuery("query");
* get data from resultset - rst.getString("column_name");

Auto Commit

con.setAutoCommit(false);
.........................
.........................
(transaction statements)
.........................
.........................
con.setAutoCommit(true);

Calling Stored Procedure

CallableStatement cs = con.prepareCall("call show_supplier");

Resultset rs = cs.executeQuery();  

Warnings(SQL)

To handle sql warning connection, statement, resultset each object can invoke a method called getwarning. [con.getWarning();]

JDBC driver types

Type1:- JDBC-ODBC bridge
Type2:- Native API partly Java Driver
Type3:- Network Protocol Driver
Type4:- JDBC Net pure Java Driver

Logging JDBC

DriverManager.println();

SQL Locator
A locator is a SQL3 datatype that acts as a logical pointer to data. Used to handle- array, blob and clob data.

Transaction Isolation Level
* 4 levels
* Connection.setIsolationLevel()
* Isolation Levels
TRANSACTION_READ_UNCOMMITTED
TRANSACTOIN_READ_COMMITTED
TRANSACTION_REPEATABLE_READ
TRANSACTION_SERIALIZABLE

Anomalies
* Dirty Reads
* Non-repeatable reads
* Phantom reads

Metadata
* two important classes : DatabaseMetaData and ResultsetMetaData
DatabaseMetaData.getImportedKeys() returns a resultset with data about foreign keys etc.


Locking
Pessimistic Locking: - good for data integrity, bad for concurrency, defensive approach, lock the data and always expect that someone can access the interim data before it gets updated.

optimistic Locking:- exptects that a clash between multiple updates to the same data will seldom occur.

batch updating
stmt.addBatch(SQL statment here);
stmt.addBatch(SQL statment here);
stmt.addBatch(SQL statment here);
................................
................................
stmt.executeBatch();
stmt.clearBatch();