JDBC tutorial - contributed by Pradip Patil

JDBC

JDBC API is the API which is able to access the data in a tabular form. Like relational data or relational database.

Following activities are managed by the JDBC

1. Connect to the database
2. Send queries to the database
3. Retrieve the result of your queries

JDBC Architecture:

In the three-tier model, commands are sent to a "middle tier" then sends the commands to the data source. The data source processes the commands and sends the results back to the middle tier, which then sends them to the user. Advantage is that it simplifies the deployment of applications. In many cases, the three-tier architecture can provide performance advantages.

Three-tier Architecture for Data Access.

Middle tier has often been written in languages such as C or C++, which offers fast performance. Advantage of Java's robustness, multithreading, and security features.

JDBC API is being used more and more in the middle tier of three-tier architecture. It supports for connection pooling, distributed transactions, and disconnected rowsets. The JDBC API also allows access to a data source from a Java middle tier.

The JDBC API — The JDBC API are divided into two packages i.e. java.sql and javax.sql. The JDBC API provides programmatic access to relational data. Applications can execute SQL statements, retrieve results, and propagate changes back to an underlying data source using JDBC API. It also interacts with multiple data sources in a distributed, heterogeneous environment.

Types of JDBC Driver:

Type 1 : JDBC-ODBC bridge: It is used for local connection.
Type 2 : Native API connection driver : It is connected by the Native Module of dependent form of h/w like .dll
Type 3 : Network connection driver : It can interact with multiple databases of different environment.
Type 4 : Database Protocol driver: It is independent from h/w because this driver is in Java.

Steps to create JDBC application:

For the purpose of creating JDBC application following are the major steps .

• Loading Driver: Loading the driver is the 1st step. The JDBC drivers automatically register with the JDBC system when loaded. code for loading the JDBC driver:
  Class.forName(driver).
• Establishing Connection : While establishing connection we logon to the database with user name and password. code used to make the connection with the database:
  con = DriverManager.getConnection(url+db, user, pass);
• Executing Statements:You can run any type of query against database to perform database operations. code execute the statements against database:
  ResultSet res = st.executeQuery( "SELECT * FROM tablename" );
• Getting Results: fetch the records from the recordset object and show on the console
• Closing Database Connection : disconnect from the database and release resources being used.

Types of statement object:

Statement: used for access database. Useful when you are using static SQL statements at runtime.

Statement stmt = null;
try {
stmt = conn.createStatement( );
. . .
}
catch (SQLException e) {
. . .
}

PreparedStatement : Use when you plan to use the SQL statements many times. The PreparedStatement interface accepts input parameters at runtime.

PreparedStatement pstmt = null;
try {
String SQL = "Queries ";
pstmt = conn.prepareStatement(SQL);
. . .
}
catch (SQLException e) {
. . .}

CallableStatement: Use when you want to access database stored procedures. The CallableStatement interface can also accept runtime input parameters.

CallableStatement cstmt = null;
try {
String SQL = "{queries }";
cstmt = conn.prepareCall (SQL);
. . .
}
catch (SQLException e) {
. . .
}

resultset :An object that can be used to get information about the types and properties of the columns in a ResultSet object
ResultSet rs = stmt.executeQuery("Queries ");
ResultSetMetaData rsmd = rs.getMetaData();

Eg.

import java.io.*;
import java.sql.*;

public class a2
{
           public static void main(String a[])
           {
                      Connection con=null;
                      Statement stmt=null;
           try
           {
                      Class.forName("com.mysql.jdbc.Driver");
                      con=DriverManager.getConnection("jdbc:mysql://localhost/STUDENT","root","");
                      stmt=con.createStatement();
                      ResultSet rs = stmt.executeQuery("select * from Student");
                      ResultSetMetaData rsmd = rs.getMetaData();
                      int noOfColumns = rsmd.getColumnCount();
                      System.out.println("Number of columns = " + noOfColumns);
                      for(int i=1; i<=noOfColumns; i++)
                      {
                                 System.out.println("Column No : " + i);
                                 System.out.println("Column Name : " + rsmd.getColumnName(i));
                                 System.out.println("Column Type : " + rsmd.getColumnTypeName(i));
                                 System.out.println("Column display size : " + rsmd.getColumnDisplaySize(i));
                       }
           }
           catch(Exception e)
           {
                      System.out.println(e);
           }
           finally
           {
           try
           {
                      stmt.close();
                      con.close();
           }
           catch(Exception e)
           {

           }
           }
     }
}

Connection pooling :

It's a technique to allow multiple clients to make use of a cached set of shared and reusable connection objects providing access to a database.

Connection pooling has become the standard for middleware database drivers. A connection pool operates by performing the work of creating connections ahead of time.