Java Database Connectivity(JDBC) Tutorial

JDBC drivers:

Types:

   1. JDBC-ODBC bridge:
The JDBC type 1 driver, also known as the JDBC-ODBC bridge, is a database driver implementation that employs the ODBC driver to connect to the database. The driver converts JDBC method calls into ODBC function calls.

Advantages:

• Almost any database for which an ODBC driver is installed can be accessed, and data can be retrieved.

Disadvantages:

2.     Native-API Driver

Advantages:

As there is no implementation of jdbc-odbc bridge, its considerably faster than a type 1 driver.

Disadvantages:

The vendor client library needs to be installed on the client machine.

 3 .     Network-Protocol Driver(MiddleWare Driver)

Advantages:

Disadvantages:

4.     Database-Protocol Driver(Pure Java Driver)

Schematic of the Native-Protocol driver.The JDBC type 4 driver, also known as the Direct to Database Pure Java Driver, is a database driver implementation that converts JDBC calls directly into a vendor-specific database protocol.

Advantages:

Disadvantages:

• Drivers are database dependent, as different database vendors use widely different (and usually proprietary) network protocols.

JDBC Basics – Java Database Connectivity Steps:

Step 1: Loading a database driver: The first thing you need to do before you can open a database connection is to load the JDBC driver for the database.

Class.forName(”sun.jdbc.odbc.JdbcOdbcDriver”);

Step 2: Opening the Connection
To open a database connection you use the java.sql.DriverManager class.

String url      = "jdbc:h2:~/test";  
String user     = "sa";
String password = "";

Connection connection =
    DriverManager.getConnection(url, user, password);

Step 3: Creating a jdbc Statement object: Once a connection is obtained we can interact with the database.

Statement statement = connection.createStatement();

A statement object is used to send and execute SQL statements to a database.

Statement: Execute simple sql queries without parameters.
Statement createStatement()
Creates an SQL Statement object.

Prepared Statement: Execute precompiled sql queries with or without parameters.
PreparedStatement prepareStatement(String sql)
returns a new PreparedStatement object. PreparedStatement objects are precompiled
SQL statements.

Callable Statement: Execute a call to a database stored procedure.
CallableStatement prepareCall(String sql)
returns a new CallableStatement object. CallableStatement objects are SQL stored procedure call statements.

Step 4: Closing the Connection
Once you are done using the database connection you should close it. This is done by calling the Connection.close() method, like this:

connection.close();

Example-

import java.sql.*;

class TestRetrieve
{
 public static void main(String[] args)
 {
  try
  {
   Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
   Connection c=DriverManager.getConnection("Jdbc:Odbc:mydsn");
   Statement s=c.createStatement();
   ResultSet result1=s.executeQuery("select * from emp");
   while(result1.next())
   {
    System.out.println(result1.getString(1));
    System.out.println(result1.getString(2));
   }
  }catch(SQLException e)
     {
      System.out.println(e);
     }
   catch(Exception i)
   {
    System.out.println(i);
   }
  
 }
}