JDBC

2007-04-05T01:51:00.000-07:00

JDBC (Java Database Connectivity) is a standard interface for connecting from java to relational database. JDBC provides programmers a facility to generate runtime SQL commands.

The architecture of JDBC has four main components.

Application
Driver Manager
Drivers
Data Source

Application initiates and terminates the connection with the data source. It also set transaction boundaries , submits SQL statements and retries the results.

Driver Manager load the JDBC driver and pass JDBC function calls from application to the correct driver. It also perform the error checking.

Driver establishes the connection with data source

Data source processes the commands from the driver and returns the results.

So one can say that JDBC is nothing but a collection of Java classes and interfaces that enables database access from programs written in the Java language. An Java application works in following steps:

Creates a database connection object
Create a statement object to execute SQL statement by using database connection object.
Access the result from Result Set Object by executing statement object.

This API has a SQLException class to handle the exception at runtime.

Note : In this article all example are MySQL specific

Create database connection

To establish a database connection JDBC API provides Data Source interface and Driver Manager class which has a getConnection() method which returns a Connection object. Here programmer is suppose to register the suitable driver before calling getConnection() method. Driver is nothing but a software program that translate the JDBC calls into DBMS specific calls. There are two ways to register the driver.

DriverManager.registerDriver(new com.mysql.jdbc.Driver());

Class.forName("com.mysql.jdbc.Driver");

Calling the Class.forName automatically creates an instance of a driver and registers it with the DriverManager, so you don't need to create an instance of the class. This way the second way is better than the first because here you will not face any kind of duplication.

After registering the driver first you are suppose to create a URL, this requires info related to authentication to the connection string. Either you can embed the authentication info to the url string or you can use java.util.Property class to pass the info to the getConnection().

Here is the example for both

String url= " jdbc:mysql: //localhost:" + portNumber+ "/dbName? user= dbUser & password ="+ dbPassword ;
Connection connectionObj = DriverManager.getConnection(url);

or

java.util.Properties dbInfo = new java.util.Properties();
info.put ("defaultRowPrefetch","5");
info.put ("user", dbUser);
info.put ("password", dbPassword );

String url= "jdbc:mysql://localhost:"+ portNumber+"/dbName";
Connection connectionObj = DriverManager.getConnection(url , info);

Second method is better than the first because here one can also set the value of number of rows to be fetched while returning the ResultSet object. Here one thing is very important that this feature is driver specific, driver may ignore this count.

In above example I am providing dbName to the Connection URL, in case one doesn't provide the dbName to the connection URL one is suppose to call static SQL statement with following query string to select the database "use database dbName".

Create a statement

JDBC API provides Statement interface to create a static SQL statement. This interface has two sub interfaces PreparedStatement and CallableStatement.

Object of Statement can be get from Connection object. Here is the code fragment to get the Statement object

Statement stmt = connectionObj.createStatement();

This interface has execute(), executeQuery() and executeUpdate() methods to execute SQL statements.

String myQuery = "select * from my_table";
ResultSet rs = stmt.executeQuery(myQuery );

Its good habit to call the close() method to release the statement object as it also clears the ResultSet object automatically.

If one wants to execute the query statement multiple times then it is better to use prepared statements. Because by using Statement every time it does some kind of validation of SQL syntax and pseudo code generation but by using PreparedStatement it will be executed only once and this precompiled SQL statement is stored in PreparedStatement object. Due to this the RDBMS caches the query plan for this execution and makes it performance wise better then the Statement.

Here is pseudo code to create a PreparedStatement

String myQuery = "select * from my_table where id = ?";
PreparedStatement pstmt = connectionObj.prepareStatement(myQuery);

This will create the PreparedStatement object which can be used multiple times. For executing a query initially one is suppose to set the variable value (here it is id) according to its type. Suppose it is a int then one will call

pstmt.setInt(1,idValue);

After setting the value one will call

ResultSet rs = pstmt.executeQuery();

This will execute the query and store the result in ResultSet object. Once you are done with all execution call close() method to release the PreparedStatement object.

JDBC also provide the facility to execute Procedure from your Java program for that it has CallableStatement interface. CallableStatement object can be created from connection object and pseudo code for this is

CallableStatement cstmt = connectionObj.prepareCall("{call my_procedure(?,?)}");

Here we have two input parameters so before calling the execute command one is suppose to set the value of both parameter

cstmt.setInt(1, intValue);
cstmt.setString(2, strValue);

If this procedure is suppose to returns some value in that case programmer has to register that output parameter by calling following command.

cstmt.registerOutParameter(1, Types.INTEGER);
cStmt.registerOutParameter(2, Types.VARCHAR);

After this initialization call execute() method like

boolean hadResults = cstmt.execute();

Here the return type is boolean this will be true if the first result is a ResultSet object and false if the first result is an update count or there is no result and to access the ResultSet

ResultSet rs = cstmt.getResultSet();

In similar manner CallableStatement interface has close() method to release the object of CallableStatement.

Access the result

By execute() method one can get the object of ResultSet.

ResultSet object maintains the cursor to get the data from it. Initially this cursor is positioned before the first row. ResultSet interface provides next() method to access the next row in it.

It has getter method to access the value of the columns from row and input type to these methods are int or String. By this way it provides programmers a facility to access the column value by the column name or by column number. Here is the pseudo code to access the ResultSet.

while(rs.next())
{
emp_id = rs.getInt(1);
emp_name = rs.getString(2);
emp_type = rs.getString("emp_type");
}

Suppose two column have same name in that case whichever column it access first the value of that column will be returned so unless you are sure with the column name its good habit to access the column value by number.

ResultSet interface also provides the facility of updater method by which you can insert and update the data in databse. But the default ResultSet is not updatable and has a cursor that moves forwards only. Thus one can iterate on this only once from first row to last row. It is possible to make this scrollable and updatable. Here is the pseudo code for the same

Statement stmt = connectionObj.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);

String myQuery = "select * from my_table";
ResultSet rs = stmt.executeQuery(myQuery);

By creating Statement like this one can scroll and update it.

Threads

2007-04-02T00:56:00.000-07:00

In recent days I have worked on Threads that makes me to write a blog on it. Here I am sharing some of the basic characteristics of Threads.

Some Definitions

In an operating system a running program is known as Process which internally can have separate runnable tasks. These tasks are knows as Threads so we can say that thread is single sequential flow of process and it is also known as lightweight process.

If there are multiple programs or processes are executed it is known as Multi-Programming and when there is concurrency exists among different threads it is known as Multi-Threading.

Thread

Every Java program has at least one thread which is executed when the main() is invoked. Java also supports Multi-Threading for that it has class ( java.lang.Thread ) and interface ( java.lang.Runnable). It uses java.lang.Object class to call methods like wait() , notify() and notifyAll(). It also has concept of synchronize on an Object which internally provides a facility that only one thread can access that object at any given point in time.

Note : You can't change threads status after it has been started.

Creating a Thread

Java provides two way to instantiate a thread
1. By extending it from Thread class
class myThread extends Thread {
myThread() {
}
}

2. By implementing it from Runnable interface
class myThread implements Runnable {
myThread() {
}

public void run() {

}
}

This is helpful when your class is already extending any other class.

You can also give a particular name to Thread.
Thread myThread = new Thread("MyThread");

If you do not provide any name in that case it will classify Thread name as Thread-m where m is an integer which will vary from 0 to m.

Java also provides a facility to group particular Threads by ThreadGroup class. By this we can handle multiple threads as a unit , all threads in same group can access information about other threads within that group.

Starting a Thread
To actually start a thread you must invoke its start() method. This method internally allocate required system resources and call run() method of the Thread class. You are suppose to override run() method according to your requirement.

Each thread has life cycle and during this life cycle it will be in one of the following state :
1. New
2. Runnable
3. Not Runnable
4. Dead

Calling new will create a new Thread but it will not allocate any system resources to it until we call start() method. One thing you must notice that after calling new you can only able to call start() method otherwise it will throw IllegalThreadStateException.

once the run() method is called the state of Thread becomes Runnable

There are 3 possible way to make a thread to Non Runnable state
1. sleep()
2. wait()
3. Blocked for I/O

It will gain the Runnable state whenever it meets the required conditions. Following possible ways may change the state from Non Runnable to Runnable
1. notify() or notifyAll()
2. When called milliseconds in sleep() method elapsed.
3. When it get the access to Blocked I/O resource.

Dead state will occur when thread comes out from run() method or anyone call destroy() method for that thread.

One thing you must notice that interrupt doesn't kill any thread and any thread can jump from Runnable to Non Runnable or vice versa from two methods
1. Scheduling
2. programming control

Scheduling
It is nothing but execution of multiple threads in order. Java uses priority algorithms for scheduling. High priority threads will have preference on low priority threads. It doesnt mean that high priority thread will run all time it depends on the OS. Scheduling is of two types
1. Preemptive scheduling: In this higher thread will get preference until it gets over only other higher level thread can make that thread to wait.
2. Time slicing : In this each thread get the access for a scheduled period of time.

Ending a Thread
A thread normally ends when its execution completes but there are other methods to stop the execution of running thread.
1. interrupt()
2. join()
3. stop()
4. destroy()
5. suspend() and resume() : depricated
6. yield()
7. setDemon(true)

These are the basic characteristics of threads.

To read more on threads
http://java.sun.com/docs/books/tutorial/essential/concurrency/index.html

Next blog will be on : How Thread Synchronization works.......