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.



 

 

 

 

 

 

 

 

Thursday, August 15, 2013

Just In Time Compiler

We discussed in my previous blog about how JVM makes java program platform independent but "everything in life comes at a cost". Java's two pass execution to achieve this platform independence,The process of Java Compiler first compiling the java source code into byte code and then the JVM interpreting the .class files into native processor does take a toll on execution time of a typical Java program. Thus java programs run always slower than the native compiled  languages like C. And adding to it interpreting the .class file's byte code instructions made the  Java programs really very slow.

So a lot of effort is and was invested on improving the execution speed of java programs. One of the breakthroughs of this effort is Just in Time Compilation.

Now before moving to this topic a general question most of us will surely have is :

If interpreter is slow why not compile Byte code first and then run?
   Please remember that we are not concerned about the programmer on how much time is spent on compiling a java code. All we are focused is about the user who runs our program and JVM comes into picture when JRE on a native platform is trying to run our program. Imagine how much time it may take to compile a whole bunch of .class files first and then load and start running it. The user may sleep while this happen. And the program loading will be terrible. 

So we must strike a right balance between this interpreting and compiling. And the answer as for as now with its own limitation which we will not cover here is Just in Time compilers.

The concept is derived from an inventory strategy companies employ to increase efficiency and decrease waste by receiving goods only when they are needed in production process there by reducing inventory costs.

Not all of code is used in a run cycle of a program. For example the error handling or certain conditional branching never happens in a particular run time of a program . So why compile entire code. 

"So JIT compilers of a JVM will compile the byte code into machine code only when it is referenced. And then JVM will execute it into native hardware. Functions are the smallest units and are compiled only when they are referenced."

Though JIT looks concept wise simple there are lot of pitfalls of JIT especially during compile optimizations. This is not covered in this discussion. However people interested can go through the below post.


Hope things are clear. Please share your comments on this article.


About Jdk,jre and jvm

   Things to Know about JDK,JRE and JVM

This is a favorite interview topic . Even though this is the most basic  information a java programmer must know, many fail to answer it perfectly. I am making an attempt to rewrite this information so that it will refresh the concept of few experienced and will help java beginners to know concept  in depth.
The below diagram should make things clear.

JDK:
Java Development Kit (JDK) = Java Runtime Environment (JRE) + Tools like compilers (JavaC)and debuggers necessary for developing java programs.
JRE:
JRE = Java Virtual Machine(JVM) + Java Runtime Libraries  + Components necessary to execute programs or applications written in java language.
JVM:
Programs written in Java are not directly compiled by Java Compiler (JavaC) for a particular hardware and operating system platform for execution.
Instead Java programs are compiled for an intermediate language called byte code which can be interpreted  by an virtual machine [A software ] called Java Virtual Machine (JVM). And this interpreted code can now run on the actual hardware and OS.
JVM thus makes java programs independent of the underlying hardware or operating system . So Java programs written on one platform need not change while attempted running it on a different code. Because Java programs are written for JVM's rather than actual machine it self.
Different platforms are provided with its on JRE which in turn includes the JVM for a particular platform. Means to say JVM for windows 32 bit machines and JVM for Linux 64 bit machines are different. However a java program written on the windows system can run on Linux 64 bit machine without any issues because, both JVM's can understand byte code.
However while interpreting into actual machine language , individual JVM's will take care of interpreting it correctly.
The below diagram should make things clear.
But one thing with interpreting the byte code is that it will make the execution slower. Now newer JRE's have capability for Just in Time compilation [JIT] which makes the compilation much faster.
I will write on this in my next blog.
Please share in your thoughts. Hope it benefited you.