JDBC
Java Database Connectivity
Overview
of JDBC and ODBC
Through java application we can store data
into database and can retrieve data from the database. For database
communication java provides special API called JDBC. It is special API for
connection java programs to a database.
The JDBC driver acts as an interface
between a database and a java application.
Types
of Drivers
Whenever a java program requires database
connection it requires a JDBC driver. JDBC drivers are divided into the
following four types:
Type 1: JDBC-ODBC driver
Type 2: Native-API/ partly java driver
Type 3: JDBC Net java driver
Type 4: Pure Java driver
JDBC
ODBC Driver
The jdbc-odbc driver works as an interface
between the java program and the database. When a client java program sends
request to a database, it passes via the JDBC driver and the ODBC driver. The
JDBC-ODBC driver receives request and convert the JDBC API to the ODBC API and
sends it to the database server. In the same way the result set is transmitted
back to the client java program in the reverse order.
The Native API/Partly java Driver
The native API/partly java driver sends the
client JDBC request to the database via the CLI(Call Level Interface). This CLI
is at the client side. When the database receives the request, it processes the
request and the result set is sent back to the client. This result set is
native format of the database server and is converted into the JDBC format and
sent to the client program. This type of communication is faster than the JDBC
ODBC driver communication.
The
JDBC Net Java Driver
The JDBC Net java driver is similar to the
native API/ party java driver, except that here the CLI communication is done
by both client side and server side JDBC drivers and the native CLI is stored
at the database server. The main advantage of this driver is that it does not
require accessing the client local disk for getting the CLI
The
pure java Driver
The pure java driver is purely used JDBC
for database directly communicates with the database
Steps
for JDBC Connection
Step 1: Create the database and its tables
Step 2: Import the jav.sql package
Step 3: Load driver
For loading the jdbc-odbc driver in
the java program you require the forName (“Driver name”) method.
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
For different database different database
drivers are required.
Database Database
Driver
Ms
Access - sun.jdbc.JdbcOdbcDriver
Oracle oracle.jdbc.driver.OracleDriver
MS
SQL Server com.microsoft.sqlserver.jdbc.SQLServerDriver
MySQL org.gjt.mm.mysql.Driver
Step 4: Create database source
1: click on start-> control panel and
select “administrative Tools” from control panel
2: in the administrative tools, select
Datasources (ODBC)
3: Then you will see the ODBC Data Source
dialog box. Click on the Add button to add new MS Access data source
4: Now you will see the Create New Data
Source dialog box. Select “Microsoft Access Driver(*mdb)” and click on the
“finish” button
5: now, the ODBC Microsoft Access Setup
dialog box appears. Here you should give the data source name and select the
database file.
6: After clicking on the select button, the
select databse dialog box appears. Here you should select the database file
from the disk drive.
7: Now click “OK” button on remaining all
dialog boxes.you will get data source “emp” in the dialog box of step 3.
The data source is created with the
name emp. This data source will pass at the time of creating connection with
the database.
Step 5: Establish database connection
Step 6: create statement
Step 7: Execute query
Step 8: Get the ResultSet
Step 9: close the connection
Creating Database Connection (Step-5)
For creating the database connection of
java application or an applet, you should use the get connection method of the
DriverManager class.
The getConnection(String URL) method has
one parameter, “URL”. This URL parameter has three parts, which are described
below.
Protocol Name: the protocol name specifies
the name of the protocol which accesses the database. Here it should be “jdbc”.
It is represented by the following syntax.
jdbc:<sub-Protocol>:<Data
source name>
Sub-Protocol Name: The sub-protocol name
specifies the type of dta source, like MS Access. Here we are using MS Access;
so the sub protocol name is “odbc”
jdbc:odbc:<Data source name>
Data Source Name : The data source name is
the name of the data source which is created in the previous section.
Jdbc:odbc:emp
Create statement (step-6)
In this stage now you are required to send
a database query to the database. For this you require an object of the
statement interface. This object helps to create a database query.
Its syntax is given:
Statement st =con.createStatement();
Statement: the object of this statement is
used to execute the static SQL statements. There are no input or output
parameters used. It returns only ResultSet.
Prepared Statement: the object of this
statement is used to execute the dynamic SQL statements. You can use input
parameters in this type of statement.
Callable statement: the object of this
statement is used to execute the stored procedure which is used by the java
application. You can use input and output parameters in this type of statement.
An object of statement is created by using a method of the Connection
interface.
Methods of connection interface
Statement createStatement():
This method creates the statement object
for sending query to the database.
Void close():
This method close the Connection object and
releases the resources.
Void commit():
This method does all the changes into
database since the last commit or rollback
Executing Statements(Step -7)
After creating the statement, you are
required to execute this statement. For this you are required to call different
methods using the Statement object, and get ResultSet using the syntax given:
ResultSet
rs = st.executeQuery(“select * from emp”);
Methods of the Statement interface
Boolean execute(String sql_query): it
executes the specific sql_query statement.
Resultset getResultSet() : This method gets
the ResultSet generated by the execute() method
ResultSet executeQuery(String sql_query) :
this method executes the specific sql_query and returns result set.
Int executeUpdate(String sql_query): this
methods executes the specific sql_query for insert, update or delete.
Get
Resultset (Step -8)
The
database server returns the result of the query in Resultset. The syntax is
while(rs.next())
{
Code to read the resultset.
}
Where
rs is instance of resultset.
Closing
the Connection (Step-9)
At
the end you need to close all the open resources.
St.close();
// close the statement
Con.close(); //close the connection
Write a program to insert data in the database.
import
java.sql.*;
public
class insertdb
{
public static void main(String args[])
{
Statement st;
ResultSet rs;
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con =
DriverManager.getConnection("jdbc:odbc:testdsn");
st= con.createStatement();
String rd="insert into
testtable(id,name) values(2,'def')";
st.executeUpdate(rd);
con.commit();
rs=st.executeQuery("select * from
testtable");
while(rs.next())
{
System.out.println(rs.getInt("id")+ "\t"+
rs.getString("name"));
}
st.close();
con.close();
}
catch(Exception e)
{
System.out.println(e);
}}}
Write a program to update data in the database.
import
java.sql.*;
public
class updatedb
{
public static void main(String args[])
{
Statement st;
ResultSet rs;
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection("jdbc:odbc:employee");
st= con.createStatement();
String rd="update emptbl set name
='test' where id=2";
st.executeUpdate(rd);
con.commit();
rs=st.executeQuery("select * from
emptbl");
while(rs.next())
{
System.out.println(rs.getInt("id")+ "\t"+
rs.getString("name"));
}
st.close();
con.close();
}
catch(Exception e)
{
System.out.println(e);
}}}
Write a program to delete data in the database.
import
java.sql.*;
public
class deletedb
{
public static void main(String args[])
{
Statement st;
ResultSet rs;
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con =
DriverManager.getConnection("jdbc:odbc:employee");
st= con.createStatement();
String rd="delete * from emptbl where
id=2";
st.executeUpdate(rd);
con.commit();
rs=st.executeQuery("select * from
emptbl");
while(rs.next())
{
System.out.println(rs.getInt("id")+ "\t"+
rs.getString("name"));
}
st.close();
con.close();
}
catch(Exception e)
{
System.out.println(e);
}}}
