J2ee Unit-3.
Database Programming With JDBC
JDBC (JAVA DATABASE CONNECTIVITY)
Introduction and Need Of JDBC
Currently there are no. Of DataBase Management Systems are available. Like Oracle, MS SQL,
Sybase, DB2, My SQL and many other also.
But till 1990 Sun Microsystems, Inc. did not have any unique high level code, which can able to
access or all popular DBMSs. Another major challenge is the Language Barrier, in which each
DBMS had defined their own low level code to communicate with database.
So to as resolution to this problem Sun had introduced JDBC Driver and API. Because without it
JAVA will never get the industrial strength.
But the JDBC driver is one kind of interpreter or translator, it means JDBC API provides the low
level service and send to JDBC Driver which confirm specification and then JDBC Driver translate
service into low level message which can easily processed by DBMS.
“JDBC is an Application Programming Interface between Java Program and Database.”
JDBC is a call level interface, it means program uses method or functions to access feature of
database through embedded SQL statements which translated by pre-compiler.
So programmer uses JAVA class known as JDBC driver to connect to the database.
The great advantage is that it provides standard interface to all database management systems.
JDBC Driver
A driver is Java class usually supplied by database vendor, which implements the java.sql.driver
interface. The primary function of driver is provide connectivity to Database and return
java.sql.connection object.
Drivers are not called directly by application program. But they are first registered with
DriverManager , which determines the appropriate driver for particular connection request.
Types Of Drivers:
According to their architecture they are divided into four types:
1) JDBC-ODBC Bridge
ODBC (Open Database Connectivity) is the basis from which Sun Microsystems, Inc. created
JDBC, Both ODBC and JDBC have similar driver specifications and an API. It is used to
translate DBMS calls between the JDBC specification and the ODBC specification.
Study Notes By :-Ravi Talsania
J2ee Unit-3.Database Programming With JDBC
2) Native API, Partly JAVA
Native API means it use Java classes to generate platform specific code, that is code only
understand by specific DBMS. It means vendor specific driver. And for this vendor provide
this native API to create platform specific code.
3) Pure JAVA To Database Middleware
This type of driver communicate using network protocol to middleware serve, which in turn
communicate to one or more database system.
If we described the above statements then, It converts SQL quries into JDBC formatted
Statements. And then this JDBC formatted statements are translated into DBMS format.
4) Pure JAVA Direct To Database
This type of driver directly call into native protocols used by DBMS.
Its similar to type-3 driver, but its efficient as compared to above drive because , here SQL
queries are directly convert into DBMS format.
JDBC Package and Essential JDBC Classes
JDBC API contained in two packages:
1) Java.sql
It includes core java data objects that provide the basics for connecting to DBMS and
interacting with data stored in the DBMS. Java.sql is part of J2SE.
2) Javax.sql
It includes java data objects that interact with JNDI and also manage connection pooling,
among other JDBC features.
Essential classes are:
Conection:
• A connection object is created by calling either DriverManager.getConnection() or
dataSource.getConnection().
• It provides an acrive link to database through java program can read-write data and also
explore the database structure.
DriverManager:
• A class that registered JDBC Driver and supplies connection that can handle specific JDBC
urls. This can be achieve by DriverManager.getConnection() method.
Study Notes By :-Ravi Talsania
J2ee Unit-3.Database Programming With JDBC
Statement:
• An object allow SQL statements to be sent through conection,
• Here three types of statements are exists:
• Statements – Used to execute static sql strng
• Prepared Statements – It uses for precompiled SQL quries with dynamically set i/p
parameters.
• Callable Statements – It invokes the stored procedure.
ResultSet:
• An ordered set of table rows produced by SQL query. It mostly encounterd as return value
of statement.executeQuery() method.
ResultSet MetaData:
• It describes the no. of columns, column’s name, size and data type. This can be obtained
by calling getMetaData() method.
Database MetaData:
• It provides information about structure and capabilities of database. It achieve by
getMetadata() method with the object of Database MetaData.
Basic JDBC Operation / JDBC Processes
We already know that each JDBC Drivers are different, but its use the same unique processes for
interacting with DBMS. This process is divided into 5 steps. Which are:
1) Load JDBC Driver.
2) Use That Driver To Open Connection / Connecting To DBMS.
3) Create n Fire SQL Statements / Creating and Execute a statements.
4) Process The Result sets / Processing Data Returned By DBMS.
5) Terminate Connection.
1) Load JDBC Driver:
The JDBC driver must be loaded before the J2EE component can connect to the DBMS. The
driver is loaded by calling the Class. forName() method and passing it the name of the
driver, as shown in the following code segment:
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
Class.forName("com.mysql.jdbc.Driver");
Study Notes By :-Ravi Talsania
J2ee Unit-3.Database Programming With JDBC
2) Use That Driver To Open Connection / Connecting To DBMS.
After that use that diver to open an connection to particular database. This is done with
calling static getConnection(url) method by DriverManager class.
In getConnection(url), url means specific form that indicates driver type and the data
source to use.
Connection con = DriverManager.getConnection(“jdbc:odbc: data source”,userID,
password);
Connection con = DriverManager.getConnection
("jdbc:mysql://localhost:3306/mysql",userID, password);
3) Create n Fire SQL Statements / Creating and Execute a statements.
Now issue SQL statements through the connection. Once the connection is established, it
can be use to create statements object which SQL commands can be made.
Statement stmt = con.createStatement();
ResultSet rs=stmt.executeQuery(“select * from tablename”);
4) Process The Result sets / Processing Data Returned By DBMS.
The java.sql.ResultSet object is assigned the results received from the DBMS after the
query is processed. Now in the ResultSet, rs is already assigned so here first time next()
method is called because rs always point to first row. If newt() returns false then no data in
database is available.
However a true value returned by the next() method means at least one row of data is
present in the ResultSet, which causes the code to enter the do...while loop. The
getStringO method of the ResultSet object is used to copy the value of a specified column
in the current row of the ResultSet to a String object.
The getString() method passed the name of the column or no. of column in the ResultSet
whose content needs to be copied and the getString() method returns the value from the
specified column.
while(rs.next())
Name=rs.getString(“name”);
Study Notes By :-Ravi Talsania
J2ee Unit-3.Database Programming With JDBC
5) Terminate Connection.
The connection to DBMS is terminated by using the close() method. Closing the database
connection automatically close the result set but its better to close the result set before
closing connection.
Stmt.close();
rs.close();
con.close();
Database Connection
A J2EE component does not directly connect to a DBMS. Instead, the J2EE component connects
with the JDBC driver that is associated with the DBMS. However, before this connection is made/
the JDBC driver must be loaded and registered with the DriverManager.
Try{
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
Catch(ClassNotFoundException e){
System.out.println(“drivers not found”);
The Connection
• After the JDBC driver is successfully loaded and registered, the J2EE component must connect
to the database. The database must be associated with the JDBC driver.
• The data source that the JDBC component will connect to is defined using the URL format. The
URL consists of three parts. These are
I. jdbc which indicates that the JDBC protocol is to be used to read the URL.
II. <sub protocol> which is the JDBC driver name.
III. <sub name> which is the name of the database.
Study Notes By :-Ravi Talsania
J2ee Unit-3.Database Programming With JDBC
• The connection to the database is established by using one of three getConnection() methods
of the DriverManager object. The getConnection() method requests access to the database
from the DBMS. If not then getConnection() method throws a SQL Exception.
Connection con;
Try{
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
Con=DriverManager.getConnection(“jdbc:odbc:hari”);
Catch(ClassNotFoundException e){
System.out.println(“drivers not founf”);
Catch(SQLException e){
System.out.println(“can not connect to database”);
• Other databases limit access to authorized users and require the J2EE to supply a user ID and
password. And for that we have to change the parameter in getConnection(String url, String
user, String password) method.
Connection con;
Try{
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
Con=DriverManager.getConnection(“jdbc:odbc:hari”,”un”,”pwd”);
Catch(ClassNotFoundException e){
System.out.println(“drivers not found”);
Catch(SQLException e){
System.out.println(“can not connect to database”);
Study Notes By :-Ravi Talsania
J2ee Unit-3.Database Programming With JDBC
Associating the JDBC/ODBC Bridge
You use the ODBC Data Source Administrator to create the association between the database and
the JDBC/ODBC bridge. For that steps are:
Select Start I Settings, and then, the Control Panel.
Select ODBC 32 to display the ODBC Data Source Administrator.
Add a new user by selecting the Add button.
Select the driver then select Finish. Use the Microsoft Access Driver if you areusing Microsoft
Access; otherwise, select the driver for the DBMS that you are using. If you don't find the
driver for your DBMS on the list, you'll need to install the driver.
Enter the name of the database as the Data Source name in the ODBC Microsoft Access Setup
dialog box. This is the name that will be used within your Java database program to connect to
the DBMS.
Enter a description for the data source. This is optional, but will be a reminder of the kind of
data that is stored in the database.
Click the Select button. You'll be prompted to browse the directory of each hard drive
connected to your computer in order to define the direct path to the database. Click OK once
you locate the database, and the directory path and the name of the database will be displayed
in the ODBC Microsoft Access Setup dialog box.
Since this is your database, you can determine if a login name and password is required to
access database.
If so then click the Advance Button to display Set Advance Options Dialog Box.
When the-ODBC Microsoft Access Setup dialog box appears, select OK.
Select OK to close ODBC Data Source administrator dialog Box.
Statements
• Once a connection to the database is opened, the J2EE component creates and sends a
query written using SQL to access data contained in the database. Here three types of
statements are exists:
Statements – Used to execute static SQL string or execute a query immediately.
Prepared Statements – It uses for precompiled SQL queries with dynamically set i/p
parameters.
Callable Statements – It invokes the stored procedure.
Study Notes By :-Ravi Talsania
J2ee Unit-3.Database Programming With JDBC
• Statement Object
The Statement object is used whenever a J2EE component needs to immediately
execute a query without first having the query compiled. The Statement object
contains:
executeQuery() method, which is passed the query as an argument. And it returns
one ResultSet object that contains rows, columns, and metadata that represent data
requested by query.
execute() method of the Statement object is used when there may be multiple
results returned.
executeUpdate() method is used to execute queries that contain UPDATE and
DELETE SQL statements, which changes values in a row and removes a row
respectively. The executeUpdate() method returns an integer indicating the number
of rows that were updated by the querv. The executeUpdateQ is used to INSERT,
UPDATE, DELETE, and DDL statements.
executeBatch() method is used to allow multiple update statements to be executed
in batch. The update counts are returned in an array. It has three methods are,
clearBatch() to reset batches at empty states, addBatch() to add an update
statement to the batch. executeBatch() which submits the batch and collects
update counts.
Prepared Statements :
o It uses pre-compiled SQL used to execute SQL Query which have dynamic values.
o PreparedStatements stmt=con.preparedStatement(s);
o Here String s to be executed may contains substitution parameters, which are indicated
by presence of a question mark(?) in the string.
o These parameters act as placeholders in the statement and must be filled with values
before they are executed. And to perform this we have,
o setXXXXX(int parameter index, value); where XXXXXX is Java Data Type.
Study Notes By :-Ravi Talsania
J2ee Unit-3.Database Programming With JDBC
o The setXXXXX() method of the PreparedStatement object is used to replace the
question mark with the value passed to the setXXXXX() method. There are a
number ofsetXXXXX() methods available in the PreparedSfcatement object, each of
which specifies the data type of the value that is being passed to the setXXXXX()
method.
o The advantage of using this is that the query is precompiled once and the setXXXXX()
method called as needed to change appropriate value of query without need to
recompile the query.
• Callable Statements :
The CallableStatement object is used to call a stored procedure from within a J2EE
object. A stored procedure is a block of code and is identified by a unique name. The
type and style of code depends on the DMBS vendor and can be written in PL/SQL. The
stored procedure is executed by invoking the name of the stored procedure.
The CallableStatement object uses three types of parameters when calling a stored
procedure. These parameters are IN, OUT, and INOUT. The IN parameter contains any
data that needs to be passed to the stored procedure and whose value is assigned using
the setXXXXX() method as described in the previous section.
The OUT parameter contains the value returned by the stored procedures, if any. The
OUT parameter must be registered using the registerOutParameter() method and then
is later retrieved by the J2EE component using the getXXXXX() method.
The INOUT parameter is a single parameter that is used to both pass information to the
stored procedure and retrieve information from a stored procedure using the techniques
described in the previous two paragraphs.
Example:
First we create stored procedure that display that per of the person:
Create or replace procedure
Pdisp(in_name IN varchar, out_per OUT number)
is temp_name varchar(15);
temp_per number(3);
begin
select per into temp_per from result where name =in_name;
out_per:=temp_per;
end;
Study Notes By :-Ravi Talsania
J2ee Unit-3.Database Programming With JDBC
Now we going to execute this stored procedure:
Import java.sql.*;
Public class callstmt
{
Public static void main(String a[])
{
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con=DriverManager.getConnection("jdbc:odbc:hari");
CallableStatement stmt=con.PrepareCall(“{callpdisp(?,?)}”);
stmt.setstring(1,”abc”);
stmt.registerOutParameter(2, Types.NUMBER);
stmt.execute();
String s=stmt.getString(2);
System.out.println(s);
}
catch(Exception e)
{
Systm.out.println(e.getMessage().toString());
}
}
}
DatabaseMetaData :-
A MetaData means data about data.
The database metadata is used to display the information about your databases like database product
name,version,driver name,user name etc..
This class is comming from java.sql.* packages.
To create the object of database metadata
DatabaseMetaData dmd;
To initialize the database meta data object
dmd=con.getMetaData();
Study Notes By :-Ravi Talsania
J2ee Unit-3.Database Programming With JDBC
The following method is used for getting database information:
1)String getDatabaseProductName()
2)String getDatabaseProductVersion()
3)String getDriverName()
4)String getUserName()
The below display example of database meta data class.
import java.sql.*;
class dbmetadata
{
public static void main(String args[])throws Exception
{
Connection con;
DatabaseMetaData dmd;
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con=DriverManager.getConnection("jdbc:odbc:DataMetaDSN");
dmd=con.getMetaData();
System.out.println("\n----Database Information----");
System.out.println("\nProduct Name\t=\t"+dmd.getDatabaseProductName());
System.out.println("\nProduct Version\t=\t"+dmd.getDatabaseProductVersion());
System.out.println("\nDriver Name\t=\t"+dmd.getDriverName());
System.out.println("\nUserName\t=\t"+dmd.getUserName());
System.out.println("\nURL\t\t=\t"+dmd.getURL());
con.close();
}
}
Study Notes By :-Ravi Talsania
J2ee Unit-3.Database Programming With JDBC
ResultSetMetaData :-
The resultset metadata class is used to give the information about database tables like
table,column,name,type,length etc...
You can also get total no.of table field type information in variable.
To create object of result set meta data
ResultSetMetaData rsmd;
To initialize result set meta data
rsmd=rs.getMetaData();
The following method is used to get information about table.
1)int getColumnCount()
2)String getColumnName(int col)
3)int getColumnType(int col)
4)String getColumnTypeName(int col)
5)String getColumnLabel(int col)
The below display example of ResultSetMetaData
import java.sql.*;
class rsmetadata
{
public static void main(String args[])throws Exception
{
Connection con;
Statement stmt;
ResultSet rs;
ResultSetMetaData rsmd;
String s;
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con=DriverManager.getConnection("jdbc:odbc:DataMetaDSN");
stmt=con.createStatement();
s="select * from info";
Study Notes By :-Ravi Talsania
J2ee Unit-3.Database Programming With JDBC
rs=stmt.executeQuery(s);
rsmd=rs.getMetaData();
System.out.println("\n-----Table Information-----");
int c=rsmd.getColumnCount();
System.out.println("\nTotal Column\t=\t"+c);
System.out.println("\nTable Name\t=\t"+rsmd.getTableName(1));
System.out.println("\n------------------------------");
System.out.println("\nName\t\tType");
System.out.println("\n------------------------------");
for(int i=1; i<=c; i++)
{
System.out.println(rsmd.getColumnName(i)+"\t\t"+rsmd.getColumnTypeName(i));
}
con.close();
}
}
Study Notes By :-Ravi Talsania
