JDBC and Java

JDBC defines how a Java program can communicate with a database. This section focuses mainly on JDBC 2.0 API.  
JDBC API provides two packages that are java.sql and javax.sql. By using JDBC API, you can connect virtually 
any database, send SQL queries to the database and process the results.

JDBC architecture defines different layers to work with any database and java, 
1) First Layer: JDBC API interfaces to work with Java
2) Second Layer: JDBC driver which is at middle layer (implements the JDBC API interfaces that maps 
   java to database specific language which are vendor dependent)
3) End/Bottom Layer: Database to store physical data.

JDBC API provides interfaces and classes to work with databases. 
Connection interface encapsulates database connection functionality, 
Statement interface encapsulates SQL query representation and execution functionality and 
ResultSet interface encapsulates retrieving data which comes from execution of SQL query using Statement.

The following are the basic steps to write a JDBC program
        1. Import java.sql and javax.sql packages
        2. Load JDBC driver
        3. Establish connection to the database using Connection interface
        4. Create a Statement by passing SQL query
        5. Execute the Statement
        6. Retrieve results by using ResultSet interface
        7. Close Statement and Connection

Here we will see types of drivers, availability of drivers, use of drivers in different situations, and 
then we will discuss about which driver suits your application best.

Driver is the key player in a JDBC application, it acts as a mediator between Java application and database. 
It implements JDBC API interfaces for a database, for example Oracle driver for oracle database, 
Sybase driver for Sybase database. 

JDBC defines four types of drivers to work with. Depending on your requirement you can choose one among them.

Here is a brief description of each type of driver :
Type 1:  JDBC-ODBC:     This driver converts JDBC calls to ODBC calls through JDBC-ODBC Bridge driver 
                        which in turn converts to database calls. Client requires ODBC libraries.
                        
Type 2:  Native API (Partly - Java driver):  This driver converts JDBC calls to database specific native 
                                             calls. Client requires database specific libraries.
                                             
Type 3:     JDBC-Net-All Java driver:     This driver passes calls to proxy server through network protocol 
                                    which in turn converts to database calls and passes through database 
                                    specific protocol. Client doesn't require any driver.
                                    
Type 4:  Native protocol-All-Java driver:     This driver directly calls database. Client doesn't require any 
                                            driver. Also called Thin driver.

Obviously the choice of choosing a driver depends on availability of driver and requirement. 
Generally all the databases support their own drivers or from third party vendors. If you don't 
have driver for your database, JDBC-ODBC driver is the only choice because all most all the vendors 
support ODBC. If you want to connect to database from java applet, then you have to use Type four 
driver because it is only the driver which supports that feature. 

Type 3 & 4 drivers are faster than other drivers because Type 3 gives facility for optimization 
techniques provided by application server such as connection pooling, caching, load balancing etc 
and Type 4 driver need not translate database calls to ODBC or native connectivity interface. 
Type 1 drivers are slow because they have to convert JDBC calls to ODBC through JDBC-ODBC Bridge 
driver initially and then ODBC Driver converts them into database specific calls. 
Type 2 drivers give average performance when compared to Type 3 & 4 drivers because the database 
calls have to be converted into database specific calls. 
Type 2 drivers give better performance than Type 1 drivers. 

So the hierarchy is in better performance wise: Type4 > Type3 > Type2 > Type1.
----------------------------------------------------------------------------------

Below I am writing a complete coding using Type4 driver in Mysql and Oracle database:

Mysql and JDBC connection:
Create a Java file named "DBConnection.java". The example is very basic for connectivity.
=Add the "mysql-connector-java-5.1.5-bin.jar" file to class path. 

-----------------------Start of DBConnection.java----------------
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class DBConnection {
    Connection con=null;
    public DBConnection () throws Exception{
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        con = DriverManager.getConnection("jdbc:mysql://192.168.111.117:3306/deepak","root","password");
        System.out.println("Connected");
        
        //Driver for Oracle
        //Class.forName("oracle.jdbc.OracleDriver");
        //String connQuery = "jdbc:oracle:thin:@192.168.12.37:1521:XE";  //XE is the schema name.
        //Connection conn = DriverManager.getConnection(connQuery,"username","password");        
    }
    public static void main(String[] args) throws Exception {
        new DBConnection();
    }
}
-----------------------End of DBConnection.java----------------
Please see below for complete DB operation I mean query update and fetching from Database.
If you are using PreparedStatements, it will improve performance:
A query execution in DB has to pass through 3 steps:
    1) Lexical and Syntax check(also refers as Compilation of query),
    2) Query optimization,
    3) Execution of query.

If you use PreparedStatements, Step 1 and 2 will be executed only once and 3 will be repeated 
but in Statement API all three steps will be repeated for all queries. So PreparedStatements 
will improve the performance of database while executing query. See below code snippet..
--------------------------------
try{
    connection.setAutoCommit(false);
    PreparedStatement ps = connection.preareStatement( "UPDATE employee SET Address=? WHERE name=?");
    ps.setString(1,"Bangalore, RT Nagar");
    ps.setString(2,"Deepak.Modi");
    ps.executeUpdate();

    PreparedStatement ps1 = connection.prepareStatement( "UPDATE account SET salary=? WHERE name=?");
    ps1.setDouble(1, 50000.00);
    ps1.setString(2,"Deepak.Modi");
    ps1.executeUpdate();

    connection.commit();
    connection.setAutoCommit(true);
}
catch(SQLException e){ connection.rollback();}
finally{
       if(ps != null){ ps.close();}
       if(ps1 != null){ps1.close();}
       if(connection != null){connection.close();}
}
--------------------------------
Below is complete URL can be set while making DB connection for CACHING queries and enhancement
of PreparedStatements...

"jdbc:mysql://192.168.111.17:3306/deepak?useServerPrepStmts=true&cachePrepStmts=true&prepStmtCacheSize=200&prepStmtCacheSqlLimit=1000"

useServerPrepStmts=true //Use server-side prepared statements if the server supports them.
cachePrepStmts=true     //Keeps caching of prepared statements.
prepStmtCacheSize=200     //No of sql queries to cache.
prepStmtCacheSqlLimit=1000 //Maximum no of characters in one query to be cached.

------------------END-------------------------------