Java Application Database Connectivity(JDBC)

Java Database Connectivity (JDBC) API is Commonly used in java to connect java application to the database .It provides interfaces and classes for creating database-independent connectivity between java applications and wide range of database such as Oracle,MYSQL,Sybase,DB2 etc

JDBC API used to create various kind of applications such as.

Desktop application
Web application
Mobile application
Micro application

The JDBC API is present in the java.sql package. java applications developed by JDBC platform are vendor independent .JDBC API convert java language into SQL understandable format with the help of database drivers.

Java Application Connectivity Through JDBC

JDBC architecture:

JDBC architecture is also known as two-tier or three-tier architecture in the database. Generally

there are two layers in the database architecture.

Application layer
Database layer

In the application layer our java application and JDBC API both are exist in the application layer . Whenever java application wants to connect with database generally we use JDBC API . JDBC API passes the data to the database drivers then drivers are responsible to connect our java application to the database.Every database has our specific driver. Server vendors are responsible to provide drivers.

Types Of drivers:

There are basically four types of database driver in JDBC API.

Type 1: JDBC-ODBC bridge driver
Type 2: Native API driver
Type 3:Network driver
Type 4: Pure java driver

Type 1: JDBC-ODBC bridge driver:

If you want to connect java application to type 1 driver open database connectivity(ODBC) driver should be installed in the client machine and configured correctly. Type 1 driver can not connect directly to the database , it needs JDBC-ODBC bridge driver.It translate JDBC method calls into ODBC method calls.Even though this connects with all type of databases.

In the old versions of OS,ODBC application is already installed by default but in the current versions of OS we have to install ODBC application manually,Otherwise we can not use it.

Connectivity through JDBC-ODBC bridge driver

Type 2 driver:

Type 2 driver is also known as Native-APIs driver, written partially in java and partially in native code. The native API of each database should be installed in the client machine . So in this way, a native API driver is a vendor specific driver and these APIs are available from the database vendors. For example Oracle database will have native APIs of it's database.If the client machine want's to access some other database it should installed native APIs of it's database.

Database connectivity through native API driver

Type 3 Driver:Network or middle ware driver

Type 3 driver use database application server to connect a java application to the database.The middle ware acts as a gateway for various other database servers. The client database access request are sent through the network to the middle ware or server and then the middle ware converts the request to the database specific API.

Database connectivity by using Network driver

Type 4: Pure java driver:

Type 4 driver is also known as pure java based driver . It is directly communicate to the database specific API through a socket. The API for that database provided by the database vendor itself. Type 4 driver is a best performing API compared to other three database drivers. It is generally used in enterprise or business type applications in java.

JDBC Classes and interfaces:

In order to create database applications we need some classes and interfaces. These classes and interfaces are grouped together in a JDBC API.

1. Drivermanager: DriverManager is a class It is use for following points.

It is use to register the drivers in the database
It is used to get connection form the database

2. Driver(Interface):Every database vendor should be implemented Driver interface

3.Connection(Interface): It is basically used to get connection object form the database.

4. Statement(Interface): It is basically use to execute the SQL quires from the database;

5. ResultSet(interface): It holds the data retrieved from a database after executing an sql query using statement object It acts as an iterator to allowing to move through its data.

6. SQLException: This class handles all the exceptions occurs during database connection.

Basic steps in developing JDBC applications:

The are following steps are involved in creating a JDBC application.

1. import the required packages.

2. Register JDBC the driver

3. Get the connection

4. Execute a Query

5. Extract data from the ResultSet.

6.Close database connection

1. import the required packages.

Before starting the application development , the required packages containing JDBC classes and interfaces must be included in the program. To perform CRUD(create ,Read,Update,Delete) operation

we need to import java.sql package.

2. Register the JDBC driver:

The driver is a piece of software that knows how to interact with with the database. The driver of the database should be registered at the beginning itself. The registration of driver is done once and not repeated anywhere in the program.The most common approach to register the driver

Class.forName(String class name) and forName() method .

Ex: Class.forName("com.jdbc.myslq.Driver");

3. Get the connection:

After loading the driver and register the driver, a connection with the database can be established using the statement DriverManager.getConnection() method , The getConnection() method is

contained in the DriverManager class .getConnection() has three overloaded methods.

getConnection(url);
getConnection(url,username,password)
getConnection(url,Properties p)

4. Execute a Query:

Once a connection with the database is established, the application can interact with the database. The following interfaces define the method and properties used to query the data from the database.

By using statement:

It is a genral approch. It doesn't accept any parameter

Ex: Statement st=con.createStatement();

By Using PreparedStatement:

It is also a simple way to create a statement with the database and it allows parameter at the run time,

Ex: PreparedStatement ps=con.PreparedStatement(string);

By Using Callable Statement: :

It is also a simple way to connect with the database and it allows parameter at the run time,

EX: cmp.CallableStatement()l

5. Extract data from the ResultSet.:

Each row is a record, and returned by executing a SQL statement will be store in the project of a REsultSet. The ResultSet object is created by executing an SQL statement.for collecting all the record from the table is the SELECT statement.

ResultSet rs=st.executeQuery("Select * from table-name");

6.Close database connection:

After perform the CRUD operations we should close the database connection by using close() method.

ex: rs.close();

st.close();

cmp.cloase();

Database Connectivity by using JDBC-ODBC Driver:

If you want to Connect your java application to the JDBC-ODBC driver then you must have installed ODBC application in your operating system otherwise you can't access your java application.

How to find ODBC application in our System:

We can find find ODBC application in the following DIrectory structure
Control Panel\All Control Panel Items\Administrative Tools\DDBC data source

2 .Connectivity by using MySQL(TYpe 2 : driver):
import java.sql.;class Best { public static void main(String[] args) throws SQLException { Class.forName("com.mysql.jdbc.Driver"); Connection con=DriverManager.getConnection ("jdbc:mysql://localhost/stu","root",""); Statement st=con.createStatement(); ResultSet sat=st.executeQuery("Select from student"); while(sat.next()) { System.out.println(sat.getInt(1)+" " + sat.getString(2)); } }}
OUTPUT:

st_id		st_name		Marks
101		viren		500

Java Learning

Sunday, 27 October 2019

Java Application Database Connectivity(JDBC)