Thursday, March 6, 2014

JDBC ResultSet Interface

ResultSet is an interface in java.sql package which has methods to retrieve and manipulate the results of an executed query. Broadly ResultSet provides  the functionalities for navigation, retrieval and update .
ResultSet exhibit different characteristics. They can be classified into type, concurrency and cursor hold ability.
 
1) ResultSet Types:
 
The type of ResultSet object can be defined on two aspects the navigation of records and how sensitive the ResultSet is to concurrent changes to the underlying data store.
Base on these we have 3 different ResultSet types:
 

  • TYPE_FORWARD_ONLY : The ResultSet  can only be scrolled or navigated in forward   direction. This is the default type and is not sensitive to the underlying data store changes.
 

  • TYPE_SCROLL_INSESITIVE: The ResultSet can be scrolled either in forward or backward direction. We can move to absolute location or relative record position using this type of scrolling. However Result set is insensitive or insulated from the underlying data store changes
 

  • TYPE_SCROLL_SENSITIVE: This type is exactly has the same functionality as the TYPE_SCROLL_INSESITIVE in terms of scrolling however the result set is sensitive to the changes in underlying data store.
2) RsultSet Concurrency:
 
They determine the level of update functionality provided. There on two categories based on this criteria:
 

  • CONCUR_READ_ONLY : The ResultSet type object cannot be used for updates to data store.
  • CONCUR_UPDATABLE: The ResultSet typeobject can be used for updating the data store.
3) Result Set Holdablity :
 
By default if we can Connection.commit() after the transaction, the ResultSet object will get closed automatically. This default behavior may not be desired in some business scenarios. JDBC does provide a way to control this through java application.
The following two constants of ResultSet interface can be passed to Connection methods createStatement,prepareStatement and prepareCall to control this behavior of Connection.commit() call:

  • HOLD_CURSORS_OVER_COMMIT : ResultSet's are not closed on an commit. Instead they are held. These type is very useful if the ResultSet are read only.
  • CLOSE_CURSORS_AT_COMMIT: ResultSet closed immediately on a commit call. This can produce better performance over the later one.

Applying the Above in Coding:


ResultSet can be created by below methods:

1) Using ExecuteQuery() on a Statement type object calling a SELECT query.

2) The second approach is to get a Statement type object from the Connection object using createStatement() method. After a statement is created we can issue getResultSet() call to get the corresponding ResultSet type object.

Example1:
                 Statement st=connection.CreateStatement();
                 Boolean bVal=st.execute("SELECT * FROM EMP");
                 ResultSet st=st.getResultSet();


The Connection type object provides 3 overloaded createStatement() methods:

1) Statement createStatement() : This returns a default Statement type object which produces the  default ResultSet on calling an executeQuery. The returned ResultSet is ForwardOnly,ReadOnly and cannot hold the result type on transaction commit(). The example [Example1] provided above demonstrates this.

2) Statement createStatement(int resultSetType, int resultSetConcurrency)This
  provides flexibility over the plain createStatement() in a way that we can define the ResultType we   want after execution of the query.

 Example 2: If we want a scrollable ,sensitive,updatable ResultSet we can call as below:
            st=connection.CreateStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);

 Please note that we can do the same for a PrepareStatement or CallableStatement using prepareStatement() or prepareCall() respectively.

3) Statement createStatement((int resultSetType, int resultSetConcurrency, int resultSetHoldability)

This provides flexibility for even mentioning whether ResultSet is Holdable or not after a commit.








 
 
 
 
 
 
 
 
 
 
    

Wednesday, March 5, 2014

JDBC Sample Java Code to Connect With Oracle

In previous blog I covered different interface of java.sql package that are important for writing jdbc code to get connected to the database. In this blog we will study a sample code on how we can get connected to the Oracle data base and execute a sample query to get the Result set and print it on the console.

For this purpose we are assuming that oracle is installed on our local system and we have a schema called "vinodoracle" and a password "vinodoracle". We also assume we do have a table with name EMP with column names "EMPLOYEEID","NAME" and "SALARY" of types NUMBER,VARCHAR2 and NUMBER respectively.

The table does look as below :


The comments in the below program are self explanatory.

The Line #22 loads the OracleDriver class and initializes the driver. The line #24 supplies the url,uid and pwd and creates a coonection to oracle data base. We assume Oracle is installed on local machine so localhost in our url. Line #27 obtains a statement type object from the connection and Line #29 executes SELECT query and stores the result in ResultSet type object of Oracle. Which can then be printed as we are doing.

We are catching ClassNotFoundException for Class.forName("") call. If we don't have oracle supplied Driver jar in our class path we do catch this exception. So make sure we have ojdbc14jar in our class path .

The url information can be obtained from a file called trans.ora file within oracle installation folder.

Please focus on how we are retrieving the values from the record set. RecordSet interface provides methods like getInt, getString etc.. to retrieve the values from the current record set. We can either pass Column Name or Column Number as parameter. In the below code at Line #35 we use Column Name to get the values of EMPLOYEEID and NAME . But we are getting SALARY by using column number as parameter to getNumber . Also note that column numbers are not zero based . They start from one.



 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
package com.vinod.oracle;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class FirstOracleConnection 
{

	public static void main(String[] args)
	{

		String url="jdbc:oracle:thin:@localhost:1521:XE";
		String uid="ravioracle";
		String pwd="ravioracle";

		try
		{

			//Loading the OracleDriver Class
			Class.forName("oracle.jdbc.driver.OracleDriver");
			//Creating the Connection type object by providing the url , uid and password parameters to DriverManager
			Connection con=DriverManager.getConnection(url, uid, pwd);
			System.out.println("Connection Successful");
			//Get a Statement type object from Connection
			Statement st=con.createStatement();
			//Execute a query
			ResultSet rs=st.executeQuery("SELECT * FROM EMP");
			System.out.println("EMPLOYEEID   EMPLOYEENAME   SALRAY" );
			// Resord set by default is pointed to a Top-1 location. So we need to move next and then access the first record.
			while(rs.next())
			{

				System.out.println(""+rs.getInt("EMPLOYEEID")+"\t     "+rs.getString("NAME")+"\t    "+rs.getInt(3));
			}

			rs.close();
			con.close();

		}
		catch(ClassNotFoundException cnf)
		{

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

}

Monday, March 3, 2014

JDBC Coding Steps

 

Following are different steps involved in a Java Program using JDBC to perform Database operations:
 
  • Load the Driver Class
  • Create a Connection to the Data Base providing the necessary privileges and arguments.
  • Get the Statement type Object from the Connection
  • Execute the Data Base Query on the Statement type object obtained.
  • Get the Result Set from the query executed. Process the records
Now we shall see each step with JDBC coding point of view to connect with Oracle data base.
Please note the interfaces from Java [ provided in java.sql and javax.sql remain the same independent of the Data Base server we use. However the parameters we pass to load the vendor specific implementation will change].
 

Loading the Driver Class:


JDBC suggests us to  use reflection to load the driver class of specific DB vendor. Internally the Driver class would implement some static block executing initializing the Driver when we do load the class. The general syntax is as follows:

Class.forName("<<driverpackage.Driverclass>>");

For loading Oracle database we need to do:

Class.forName("oracle.jdbc.driver.OracleDriver");

where oracle.jdbc.driver.OracleDriver is the DriverClass provided by Oracle following JDBC specifications.

Please make sure the jar that has OracleDriver class which comes with oracle installation should be in our class path.

Creating Connection :


After loading the DriverClass we need to establish connection to the database server. To do this we get a Connection type object of DriverManager. The general syntax is:

Connection con=DriverManager.getConnection(url,uid,pwd);

Where url specifies the url specified by Database vendor to connect to the DataBase Server. uid is the userId and pwd is the password .

Example:
 To get Connection to  Oracle DataBase server running on the localHost:1521 an instance XE and schema (user) admin and password 123 we get Connection as follows:

Connection con=DriverManager.getConnection("jdbc:oracle:thin@localhost:1521:XE","admin","123");


Get Statement type Object:


We need a Statement type object from the Connection type to interface and communicate with the database. Following is the syntax how we get a Statement type from the Connection.

Statement st= con.createStatement();


Execute the Query :


Once we have the Statement we can use execute(),executeQuery() etc.. to send queries to the DataBase.

Example:
ResultSet rs=st.executeQuery("SELECT * FROM EMPLOYEE");

Now with the the user can develop business specific code to handle the data got from the database in the above case ResultSet rs can be used to manipulate and write business specific .

The thing to note here is Connection,Statement and Result set are the interfaces defined by JDBC standard which all the DB vendors need to implement in order for java to support them.



 
 

JDBC Introduction


JDBC:

Introduction:

JDBC provides set of standard Java API’s for connecting and performing transactions with RDBMS. Thus provides flexibility to java developers from switching to different data bases in the project life cycle without much effort.


 

Components of JDBC:

Following are key components of JDBC

1)      JDBC Driver:

JDBC Driver provides set of concrete classes that implement the interfaces defined by the JDBC API to interact with RDBMS. Each RDMS vendors have to implement certain interfaces defined by JDBC, thus each vendor comes up with his own JDBC driver implementation. For example MYSQL gives us their implementation of JDBC drivers and oracle comes up with its own implementation of the drivers. Thus this coding for interfaces provides the flexibility of changing the RDMS on the fly without much effort.

2)      Connections:

After the JDBC driver is loaded, the next step is to make connection to Database server. We can do this by obtaining the connection object of the JDBC API.

3)      Result Set:

Once we do a Read operation or a SELECT query on the RDBMS we get the result set containing the set of records for a particular query.

Although there are about 4 different types of Drivers named Tyep1, Type, Type3 and Type4, we generally deal with type 4 drivers which is a pure java implemented drivers. The Sun Oracle defines certain interfaces and all the different data base vendors have to implement this


standard imposed by SUN through these interfaces. This provides more flexibility as the data base can be changed without any / much changes to the source code and also the performance is much better compared to other Driver types. No need to mention that being a pure java implementation this makes the DB application platform independent.