JDBC Driver

What is JDBC driver ?

JDBC drivers implement the defined interfaces in the JDBC API for interacting with your
database server. For example, using JDBC drivers enable you to open database connections and
to interact with it by sending SQL or database commands then receiving results with Java.

JDBC Drivers Types:

It has divided the implementation types into four categories known as:

1. JDBC-ODBC bridge plus ODBC driver, also called Type 1.
2. Native-API, partly Java driver, also called Type 2.
3. JDBC-Net, pure Java driver, also called Type 3.
4. Native-protocol, pure Java driver, also called Type 4.

Type 1 driver– the JDBC-ODBC bridge

1. 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. The bridge is usually used when there is no pure-Java driver available for a particular database.
2. The driver is implemented in the sun.jdbc.odbc.JdbcOdbcDriver class and comes with the Java 2 SDK, Standard Edition. The driver is platform-dependent as it makes use of ODBC which in turn depends on native libraries of the operating system.
3. Type 1 is the simplest of all but platform specific i.e only to Microsoft platform.

• Functions:

1.  Translates query obtained by JDBC into corresponding ODBC query, which is then handled by the ODBC driver. 
2.  Sun provides a JDBC-ODBC Bridge driver. sun.jdbc.odbc.JdbcOdbcDriver. This driver is native code and not Java, and is closed source.
3. Client -> JDBC Driver -> ODBC Driver -> Database
4. There is some overhead associated with the translation work to go from JDBC to ODBC.

• Advantages:

Almost any database for which ODBC driver is installed, can be accessed.

• Disadvantages:

1. Performance overhead since the calls have to go through the JDBC overhead bridge to the
   ODBC driver, then to the native database connectivity interface.
2. The ODBC driver needs to be installed on the client machine.
3. Considering the client-side software needed, this might not be suitable for applets.

Type 2 driver – the Native-API Driver

1. The JDBC type 2 driver, also known as the Native-API driver is a database driver implementation that uses the client-side libraries of the database. The driver converts JDBC method calls into native calls of the database API.
2. It converts JDBC calls into calls on the client API for Oracle, Sybase, Informix, DB2, or other DBMS. Note that, like the bridge driver, this style of driver requires that some binary code be loaded on each client machine. It does not have the overhead of the additional ODBC function calls.

• Functions:

1. This type of driver converts JDBC calls into calls to the client API for that database.
2. Client -> JDBC Driver -> Vendor Client DB Library ->database

• Advantage

Better performance than Type 1 since no JDBC to ODBC translation is needed.

• Disadvantages

1. The vendor client library needs to be installed on the client machine.
2. Cannot be used in internet due the client side software needed.
3. Not all databases give the client side library.

Type 3 driver– the Network-Protocol Driver

1. The JDBC type 3 driver, also known as the network-protocol driver is a database driver implementation which makes use of a middle-tier between the calling program and the database. The middle-tier (application server) converts JDBC calls directly or indirectly into the vendor-specific database protocol.
2. This differs from the type 4 driver in that the protocol conversion logic resides not at the client, but in the middle-tier. However, like type 4 drivers, the type 3 driver is written entirely in Java. These drivers use a networking protocol and middleware to communicate with a server. The server then translates the protocol to DBMS function calls specific to DBMS.

• Functions:

1. Follows a three tier communication approach.
2. Can interface to multiple databases – Not vendor specific.
3. The JDBC Client driver written in java, communicates with a middleware-net-server using
   a database independent  protocol, and then this net server translates this request into
   database commands for that database.
4. Thus the client driver to middleware communication is database independent.
5. Client -> JDBC Driver -> Middleware-Net Server -> Any Database

• Advantage

1. Since the communication between client and the middleware server is database independent, there is no need for the vendor DB library on the client machine. Also the client to middleware need not be changed for a new database.
2. The Middleware Server (Can be a full fledged J2EE Application server) can provide typical middleware services like caching (connections, query results, and so on), load balancing, logging, auditing etc..
3. eg. for the above include jdbc driver features in Weblogic.
4. Can be used in internet since there is no client side software needed.
5. At client side a single driver can handle any database.

• Disadvantage

1. Requires database-specific coding to be done in the middle tier.
2.  An extra layer added may result in a time-bottleneck. But typically this is overcome by providing efficient middleware
     services described above.

Type 4 driver – the Native-Protocol Driver

1. The JDBC type 4 driver, also known as the native-protocol driver is a database driver implementation that converts JDBC calls directly into the vendor-specific database protocol.
2. The type 4 driver is written completely in Java and is hence platform independent. It is installed inside the Java Virtual Machine of the client. It provides better performance over the type 1 and 2 drivers as it does not have the overhead of conversion of calls into ODBC or database API calls.
3. A native-protocol fully Java technology-enabled driver converts JDBC technology calls into the network protocol used by DBMSs directly. This allows a direct call from the client machine to the DBMS server and is a practical solution for Intranet access. Since many of these protocols are proprietary the database vendors themselves will be the primary source for this style of driver.
4. Several database vendors have these in progress.
5. The IBM Toolbox for Java JDBC driver is a Type 4 JDBC driver, indicating that the API is a pure Java networking protocol driver.

• Function:

1. Type 4 drivers are entirely written in Java that communicate directly with a vendor’s
   database through socket connections. No translation or middleware layers, are required,
   improving performance.
2. The driver converts JDBC calls into the vendor-specific database protocol so that client
   applications can communicate directly with the database server.
3. Completely implemented in Java to achieve platform independence.
4. e.g include the widely used Oracle thin driver – oracle.jdbc.driver. OracleDriver which
   connect to jdbc:oracle:thin URL format.
5. Client Machine -> Native protocol JDBC Driver -> Database server

• Advantage

These drivers don’t translate the requests into db request to ODBC or pass it to client api for the
db, nor do they need a middleware layer for request indirection. Thus the performance is
considerably improved.

• Disadvantage

At client side, a separate driver is needed for each database.

Which Driver should be used?

1. If you are accessing one type of database, such as Oracle, Sybase, or IBM, the preferred driver type is 4.
2. If your Java application is accessing multiple types of databases at the same time, type 3 is the preferred driver.
3. Type 2 drivers are useful in situations where a type 3 or type 4 driver is not available yet for your database.
4. The type 1 driver is not considered a deployment-level driver and is typically used for development and testing purposes only.

Following table lists down popular JDBC driver names and database URL.

Creating JDBC Application:

There are following six steps involved in building a JDBC application:

1. Import the packages . Requires that you include the packages containing the JDBC classes needed for database programming. Most often, using import java.sql.* will suffice.
2. Register the JDBC driver . Requires that you initialize a driver so you can open a communications channel with the database.
3. Open a connection . Requires using the DriverManager.getConnection() method to create a Connection object, which represents a physical connection with the database.
4. Execute a query . Requires using an object of type Statement for building and
   submitting an SQL statement to the database.
5. Extract data from result set . Requires that you use the
   appropriateResultSet.getXXX() method to retrieve the data from the result set.
6. Clean up the environment . Requires explicitly closing all database resources versus
   relying on the JVM’s garbage collection.

Sample code:

 import java.sql.*;
class ExecuteCreate{
    public static void main(String[]args){
        try{
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3360/sqdb","root","pass123");
            System.out.println("connection created");
            Statement st=con.createStatement();
            System.out.println("statement Created");
            String query="create table account(accon int,balance float,acctype varchar)";
            boolean result=st.execute(query);
            if(result=true)
                System.out.println("Table created");
            else
                System.out.println("Table in not craeted");
            st.close();
            con.close();
        }
        catch(Exception ex)
        {System.out.println(ex);}

        }
    }

Connection interface:

A Connection is the session between java application and database. The Connection interface is a factory of Statement, PreparedStatement, and DatabaseMetaData i.e. object of Connection can be used to get the object of Statement and DatabaseMetaData. The Connection interface provide many methods for transaction management like commit(), rollback() etc.

Commonly used methods of Connection interface:

1) public Statement createStatement(): creates a statement object that can be used to execute SQL queries.
2) public Statement createStatement(int resultSetType,int resultSetConcurrency): Creates a Statement object that will generate ResultSet objects with the given type and concurrency.
3) public void setAutoCommit(boolean status): is used to set the commit status.By default it is true.
4) public void commit(): saves the changes made since the previous commit/rollback permanent.
5) public void rollback(): Drops all changes made since the previous commit/rollback.
6) public void close(): closes the connection and Releases a JDBC resources immediately.

Connecting to the MySql database in java:

step1:

Two ways to load the jar file:

1. paste the mysqlconnector.jar file in jre/lib/ext folder
2. set classpath

1) paste the mysqlconnector.jar file in JRE/lib/ext folder:

Download the mysqlconnector.jar file. Go to jre/lib/ext folder and paste the jar file here.

2) set classpath:

There are two ways to set the classpath:
⭕temporary: Type following command on command prompt

set classpath= C:\folder\mysql-connector.jar;.;

⭕permanent
Go to environment variable then click on new tab. In variable name write classpath and in variable value paste the path to the mysqlconnector.jar file by appending mysqlconnector.jar;.; as C:\folder\mysql-connector.jar;.;

Step 2:

Create a table in the mysql database, but before creating table, we need to create database
first.
Open MySql prompt and type following:

1. create database sydb;  
2. use sydb;  
3. create table student(rollno int(5),name varchar(40),marks int(3));

Program which connects to MySql database with database name=sydb id=root and
password=pass123:

import java.sql.*;
class MysqlCon
{
public static void main(String args[])
{
try
{
Class.forName("com.mysql.cj.jdbc.Driver");

Connection
con=DriverManager.getConnection("jdbc:mysql://localhost:3306/sydb","root";,"pass123&");

Statement stmt=con.createStatement();
ResultSet rs=stmt.executeQuery("select * from student");
while(rs.next())
System.out.println(rs.getInt(1)+""+rs.getString(2)+""+rs.getString(3));

con.close();
}
catch(Exception e)
{
System.out.println(e);
}
}
}

Note: If you are doing in Netbeans then download mysql-connector.jar and add to to
project library.

Once a connection is obtained we can interact with the database. The JDBC Statement,
CallableStatement, and PreparedStatement interfaces define the methods and properties that enable you to send SQL or PL/SQL commands and receive data from your database.
They also define methods that help bridge data type differences between Java and SQL data types used in a database.
Following table provides a summary of each interface’s purpose to understand how do you decide which interface to use?

For download mysql connector

Watch this video for JDBC connection with MYSQL in java

https://www.youtube.com/watch?v=QWf1WfAu7JY&list=PLSDyGb_vtanyH_S2QXu5sf1Rm2oiBpEX8&index=8