JDBC-Overall

JDBC: Database Connectivity from a Java Language

To connect to database, a set of interfaces are required available from java.sql package.
There are two layers involved when working with JDBC: Driver Manager Layer and Application Layer.

There should be someone mediating between database and the programming language and that is the Driver. Loading of the driver occurs in the Driver Manager layer. DriverManager is a class. You talk to the driver to talk to the database.

A connection with username and password are required to talk to the database. This connection is done using the Connection Object in the Application Layer.

There are four types of Drivers available:

Type I: JDBC-ODBC Bridge Driver (Java API talks to Native API(ODBC Driver) which talks to database.
Type II: Thick Driver   (Native API which talks to the database)
Type III: Net Protocol Driver (Three-Tier Architecture-The client talks to server and the server uses one of the other three drivers to talk to the database. Used when there is lot of traffic to access the database)
Type IV: Thin/Pure Driver (Complete Java API which talks to the database. This driver is most often used).

To Load the driver we use the Class.forName method from the Class class. It returns the Class object. It can be used to load any Class in general
Class.forName(packageNames.className);

To load Type I driver: Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);

To load Type II and IV driver for Oracle: Class.forName("oracle.jdbc.driver.OracleDriver");

Once the driver is loaded you need to fetch the connection.
For Type I:
String stUrl_= “jdbc:odbc:myDSN”;
Connection connection_ = DriverManager.getConnection(stUrl_, “sa”, “”);
myDsn is the name of ODBC DSN which is defined in the ODBC data sources in the Windows.                                       

For Oracle Driver- You have to specify whether thick or thin driver
String connectionURLThin=jdbc:oracle:thin:@hostName:1521:CUSTDB”;
String connectionURLOCI=”jdbc:oracle:oci8:@CUSTDB”;
String userID             = “scott”;
String userPassword       = “tiger”;

Type II and Type IV is distinguished here:
For Thick:
Connection conn = DriverManager.getConnection(connectionURLOCI,userName,password);

For Thin:
Connection conn = DriverManager.getConnection(connectionURLThin,userName,password);

The DefaultPortNumber for Oracle is 1521 and the DefaulDatabaseInstance is orcl.

Always use a thin driver when using applet because it is written entirely in java. So, the applet does not require the native dll calls or no need to create the dsn. Type I and II driver will only work if it is a trusted applet.

To exeucte a sql statement, first get the connection object. There are three types: Statement, PreparedStatement and CallableStatement.
Statement stmt = conn.createStatement()
PreparedStatement pstmt = conn.prepareStatement(“query”)
CallableStatement cs = conn.prepareCall(“name of procedure or function”)

The PreparedStatement and CallableStatement are pre-compiled and kept and executed every time. They are dynamic, you can pass positional parameters to them at run time.

ResultSet rs = statement.executeQuery(); — for select
int i  = statement.executeUpdate(); — for ddl and dml. The return type is number of rows updated.
boolean b = statement.execute();  — general and used mainly for stored procedures. The return type is true if a select statement is excuted in a database and false if a ddl or dml statement is executed.

One statement object can only have one result set. If you get second result set the previous resultset is closed.If you need two result sets then you need two statement objects.

Once you get the result set you need to browse it using the next method.
rs.next() – to move from one row to another. Initially, the pointer is just before the first record. By default, result set processing is in a forward direction. Since JDBC 2.0, you can also move reverse also.
While browsing the result set do:
rs.getXXX(string);
rs.getXXX(int);

where XXX is data type.

String is the name of the column according to your statement; you might have specified the alias.Or you can use index which starts from 1 in JDBC. It is not a good practice to use index because if the statement changes or you add or remove columns then indexes change.

rs.getString(string/int);  — can be used for any data type

ResultSetMetadata rsmd = rs.getMetaData();
This is information about resultset. For ex, how many cols retrieved, datatypes of columns, name of the columns.

ResultSet can be used in any direction since JDBC 2.0. Also, it is updatabale now.
These methods are previous() — to move back, absolute(int) — move resultset pointer across so many records from the current position first(), last(), beforeFirst(), afterLast()

By default they are read only, to update resultset you need to mention it to be updatabale. The following is used to update a result set:
rs.updatexxx(colName/colunindex,xxx);
xxx- datatype of column

After updating a result set call rs.updateRow to commit the changes to the database. To undo the changes made, call rs.cancelUpdateRow but it should be called before rs.updateRow. Once rs.updateRow is called, the changes will be commited and caneclUpdateRow has no effect. rs.deleteRow is used to delete a row and rs.insertRow is used to insert row .

To insert a row, you need to first move to a staging area and then do the inserts.
rs.moveToInsertRow() — resultset pointer is moved to the staging area

and after the inserts are done call rs.insertRow(). To move back to the row you were previously in the result set:
rs.moveTocurrentRow

The no: of results in result set depends on fetchRow and by default it is set to 10 in Oracle. To change it do rs.setFetchSize(15);

It should’nt be large or small. If the fetch size is 15 then 15 record are retrieved and it again goes back to get other 15 and so on until all the records are retrived. You can do rs.setMaxRow(100) to retrieve only the first 100 records. In JDBC 3.0, they introduced RowSet, you need not be connected to the database. you can close the connection and acces the result, all the result is bought to the client end.
In Jdbc 4.0 , cachedRowSet is introduced, you need not hold the connection for a long period of time, you can get the data locally once and then process it. It is a good idea not to hold connection objects for a long time.

It is always a dynamic commit in JDBC. To do a manual one, do connection.setAutoCommit(false);
You can do conn.rollBack() or conn.commit() manually, also use savePoints. Cursors have been introduced in JDBC 2.0.

DatabaseMetaData dmd = conn.getMetaData() — Information about the connection. Gives information about driver being used, tables availble, transaction isolation level being used ect. conn.setTransactionIsolation(int) – it tells whether dirty,phantom and non-repeatable reads are possible or not.

dirty read – I’m able to read even before the commit happens
non-repeatable read – make changes to the rows I have read.
phanton read – read records and someone inserted a new record

You need to specify transactionIsolationLevel , this is mostly done at database level itself but you can also specify if you want.
conn.setTransactionIsolation(TRANSACTION_NULL) — NO TRANSACTION
conn.setTransactionIsolation(TRANSACTION_READ_UNCOMMITTED) — ALL THREE dirty,phanton,nr occur
conn.setTransactionIsolation(TRANSACTION_READ_COMMITTED) — phantom and non-repeatabe occur
conn.setTransactionIsolation(TRANSACTION_REPEATABLE_READ) — phantom read occurs
conn.setTransactionIsolation(TRANSACTION_SERIALIZABLE) — none of the three occurs (Performance issue as it locks for everything and concurrency cannot occur)

We can move through the result set in any direction.

rs.setFetchDirection(ResultSet.FORWARD); ONLY IN THE FORWARD
rs.setFetchDirection(ResultSet.REVERSE); ONLY IN THE REVERSE
rs.setFetchDirection(ResultSet.UNKNOWN); BOTH

To make this work, the statement object is overloaded in JDBC 2.0
createStatement(TYPE,CONCURRENCY);
TYPE – TYPE_FORWARD_ONLY (DEFAULT),
TYPE_SENSITIVE, If others are changing you will see it
TYPE_INSENSITIVE If others are changing you will not see it
CONCURRENCY – if my result set is updatabale or not
CONCUR_READ_ONLY (Default),
CONCUR_UPDATABLE
rs.getConcurrency() and rs.getType() tells these values

Example on JDBC:
import java.sql.*;

public class JDBCTest1{

public static void main(String args[]){

try{

//1. load the driver from specific vendor
Class.forName(“oracle.jdbc.driver.OracleDriver”);

//2. open connection to the databse by passing the URL to the database
Connection conn = DriverManager.getConnection(“jdbc:oracle:thin:@hostname:1526:myDB”,userName,password);

Statement stmt = conn.createStatement();
ResultSet rs = stmt.exequteQuery(“select empName,empNo,salary from employee);

//With one statement only one result set can be opened at a time

while(rs.next()){
System.out.println(rs.getString(“empNo”);
System.out.println(rs.getString(“empName”);
System.out.println(rs.getLong(“salary”);

}

rs.close();
stmt.close();

//Don’t leave the connection opened across methods
conn.close();

}catch(Exception ex){

}

}

}

public class JDBCTest2{

public static void main(String args[]){

try{

//1. load the driver from specific vendor
Class.forName(“oracle.jdbc.driver.OracleDriver”);

//2. open connection to the databse by passing the URL to the database
Connection conn = DriverManager.getConnection(“jdbc:oracle:thin:@hostname:1526:myDB”,userName,password);

PreparedStatement pstmt = conn.createPreparedStatement(update employee set salary=? where deptName= ?);

stmt.setLong(1,sal);
stmt.setString(2,”Education”);

//returns the number of rows updated
int i = stmt.executeUpdate();

System.out.println(“Number of rows updated-”+i);

rs.close();
stmt.close();

//Don’t leave the connection opened across methods
conn.close();
}catch(Exception ex){

}

}

}

public class JDBCTest3{

public static void main(String args[]){

try{

//1. load the driver from specific vendor
Class.forName(“oracle.jdbc.driver.OracleDriver”);

//2. open connection to the databse by passing the URL to the database
Connection conn = DriverManager.getConnection(“jdbc:oracle:thin:@hostname:1526:myDB”,userName,password);

CallableStatement stmt = conn.createCallableStatement(“{call procName(?,?)}”);
//If it is a function
CallableStatement stmt = conn.createCallableStatement(“{? = call funcName(?,?)}”);

stmt.setString(1,stockName);

//If using a out parameter
stmt.registerOutParameter(2,Types.Float);

int i = stmt.executeUpdate();
Float stockPrice = stmt.getFloat(2);

rs.close();
stmt.close();

//Don’t leave the connection opened across methods
conn.close();

}catch(Exception ex){

}

}

}

Boolean flag = stmt.execute(callableProcedure/function);
If the first result is a result set then it will return true otherwise false. Depending on true or false you need to call stmt.getUpdateCount or stmt.executeResultSet(this returns the result set object). If it is select then it will return true or false for dml or ddl statement

Booelean flag = Stat.getMoreResults is used to get the results until all the pl/sql blocks are completed.In this case the false will mean a ddl statement happened or no more statements are processed. If you still try to do getUpdateCount on it, then it will throw SQLException and you know that you are done now.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s