Introduction to JDBC API


The JDBC API works with two and three tier architecture and support four types of drivers to help you work with DBMS without much difficulty. It uses java.sql and javax.sql package to work help you out. This post introduces you to the JDBC basics.

Java DataBase Connectivity

The JDBC api is provided with JAVA SE and EE as a core API. it is available in two packages, java.sql and javax.sql. However our scope of discussion will be limited to java.sql package. but before that let us know about the JDBC and its architecture. Later on we will tell you more about the type of drivers we use for the JDBC operations.

The JDBC API and why we need it?

The world has moved on from simple file based application to database oriented softwares. This makes the operation easy and data management and organization really easy. Most of the time we use the Relational Database. The relational database is easy to use and understand. This name relational indicates that the data in the database is related to each other in some way or other. But before we dive deeply in the Relational database concepts let us know more about why we need JDBC.

So, we know there is something known as Databases and we also know we must use them to make the data management easier for long term as we can not rely on the temporary data. So JDBC API gives the power to interect with DBMS via SQL. you will write the SQL queries and get result set from the execution of that query. This process will occur through the JDBC API. There are two type of architecture for JDBC which we know as Two-Tier Architecture and Three-Tier Architecture.

Two-Tier Architecture

In two Tier Architecture the Application directly communicates to the DBMS throught the JDBC. In this case generally the DBMS and the Java application are both supposed to be but not necessarily on the same machine. As you can see in the Diagram the JDBC is with the Java application. The DBMS may be on the same machine or can be accessed through the Internet.

Two Tier Architecture

Three Tier Architecture

In Three Tier Architecture the application usually access the DBMS through a middle ware where this middle ware is a servlet application. The front end may or may not be Java applet. The data if first communicated to the middleware through a web protocol like http, and then servlet connects to the DBMS using the JDBC API as you can see in the Image.

three tier architecture

Note: The Images are taken from official java tutorials hosted by Oracle. You may read there to understand the concept more deeply, though we have tried to keep it as simple as possible.

Type of Drivers

The drivers are necessary as the DBMS software you are using may be different for different applications. There are four types of Drivers not named differently but in types as you may see below.

Type 1: This is a JDBC-ODBC bridge type driver. In this type the JDBC API is mapped on the platform specific API. So this is always the platform dependent. You will need a different Driver if you want to move on to a different platform.

Type 2: This is Partly written in Java and partly in the native language like C or C++. This makes it fast and more compatible but again not so good for the portable Apps.

Type 3: These drivers are created in Java and they use a middleware to communicate with the database server.

Type 4: This is the Final type of drivers which directly communicates to DBMS and are written in java which makes them best to be used with java application.

Now that we know about the JDBC Basics you must know some basics for using JDBC. Please mind the following steps these are handy for you if you are looking forward to JDBC programmin.

Step 1: Call the Driver for your DBMS type. For Example if you are using MySql the driver call will be executed using a string "com.mysql.jdbc.Driver". This string will call the driver for Mysql DBMS.

Step 2: Because there is always a chance for the Exception as the driver string may not be found and throw the ClassNotFoundException or else the SQLException may be thrown if something goes wrong with SQL statements. So you better be prepared already. Put the code under try-catch block.

Step 3: Now create the connection with the Database using the DriverManager class. Don't worry if you don't know this just yet. We'll explain in a bit.

Step 4: Finally you can create the statement and execute that statement. Which will return a result set with the resuld from that query.

Thats pretty much what you need. But first please pay attention to following Note.

IMPORTANT: This is really important that you have the driver file included. What we mean by that is, for every type of database there is a driver class provided by the vendor. This is usually in the form of JAR files which must be added to the class path of the system. Of course there is other way that you can include it in the build file but that is out of our scope of discussion, yet You may google "Using Ant to build java application", this will help you with it. And if you are using the IDE software there is always an option to include external JAR file, which will help you out easily. The whole point is to avoid ClassNotFoundException on the Class.forName("driver-string"); when you try to call the driver for your DBMS.

you can download the mysql driver which is known as MySql Connector here. We will be using mysql Database for our demonstration. This is necessary that you have installed mysql server software.

We are using phpmyadmin which is GUI interface for the MySql. So be careful for the mysql and the configuration before proceeding.

Now we are first creating the Database by the name of student.

create Database student

after creating database you may create a sample table student_info which we will be using for some sample operations.

CREATE TABLE `student_info` (
 `rollnum` int(11) NOT NULL AUTO_INCREMENT,
 `Name` varchar(20) NOT NULL,
 `Class` varchar(10) NOT NULL,
 PRIMARY KEY (`rollnum`)
)

Now this will give you a table. however you may also create table using the JDBC program, which we are going to demonstrate now.

Look at following program this will demonstrate the prepration you need before you may proceed to query writing.


package jdbc;

import java.sql.*;

public class TestJDBC {
    public static void main(String[] args){
        try{
            Class.forName("com.mysql.jdbc.Driver");
            System.out.println("Driver Accessed");
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost/student","root","");
            System.out.println(con);
        }
        catch(Exception e){
            System.out.println(e);
        }
    }
}

The output of this program on our system is like this:

Driver Accessed
com.mysql.jdbc.JDBC4Connection@23d4616f

Now the Driver was found if the classpath for the connecter would not be set it would have thrown a ClassNotFoundExceptino but it found the driver and then it was able to create the connection. Now what you need is to pay attention at the getConnection() string, which will connect to the database and will return a connection reference, where connection is an interface used to keep the connection alive until we are finished up with the operations. As you can see by the object we printing that the driver is of type JDBC4.

We are leaving it here for this Tutorial but the next part will tell you more about the Queries and their types in MySql. you can read the next part here, The JDBC Query Processing

Reference:
Official java tutorials