Module 5: J2EE and JDBC(database access)

1.Java Database Connectivity (JDBC)

 Java Database Connectivity (JDBC) is an implementation of the Java programming
language that dictates how databases communicate with each other.
 Through a standardized application programming interface (API), connectivity from
database management systems (DBMS) to a wide range of SQL databases is
accomplished.
 By deploying database drivers laced with JDBC technology, it is possible to connect to
any database -- even in a heterogeneous environment -- and access tables, tabular data,
flat files and more.
 When using JDBC, Java programmers have the ability to request connections to a
database, send queries to the database using SQL statements, and receive results for
advanced processing.

2.JDBC Drivers
 To connect with individual databases, JDBC requires drivers for each database.
 The various driver types are described in the following sections:
 Type I: JDBC-ODBC Bridge
Type II: Native API/JAVA
 Type III: Pure Java driver for database middleware(JDBC protocol)
 Type Four Driver : Direct-to-database pure Java driver (JAVA protocol)
Type 1: JDBC-ODBC Bridge Driver
 In a Type 1 driver, a JDBC bridge is used to access ODBC drivers installed on each client
machine. Using ODBC, requires configuring on your system a Data Source Name (DSN)
that represents the target database.
 When Java first came out, this was a useful driver because most databases only supported
ODBC access but now this type of driver is recommended only for experimental use or
when no other alternative is available.

Dept of CSE,CEC Advanced java and J2EE (15CS553) Page 1

 A JDBC/ODBC bridge provides JDBC API access through one or more ODBC drivers.
Some ODBC native code and in many cases native database client code must be loaded
on each client machine that uses this type of driver.
 The advantage for using this type of driver is that it allows access to almost any database
since the database ODBC drivers are readily available.
 Disadvantages for using this type of driver include the following:
 Performance is degraded since the JDBC call goes through the bridge to
the ODBC driver then to the native database connectivity interface. The
results are then sent back through the reverse process
 Limited Java feature set
 May not be suitable for a large-scale application
Type Two Driver:Native API/JAVA protocal
 In a Type 2 driver, JDBC API calls are converted into native C/C++ API calls, which are
unique to the database. These drivers are typically provided by the database vendors and
used in the same manner as the JDBC-ODBC Bridge. The vendor-specific driver must be
installed on each client machine.
 If we change the Database, we have to change the native API, as it is specific to a
database and they are mostly obsolete now, but you may realize some speed increase with
a Type 2 driver, because it eliminates ODBC's overhead.

Dept of CSE,CEC Advanced java and J2EE (15CS553) Page 2

The Oracle Call Interface (OCI) driver is an example of a Type 2 driver.
Advantages for using this type of driver include the following:
 Allows access to almost any database since the databases ODBC drivers are readily
available
 Offers significantly better performance than the JDBC/ODBC Bridge
 Limited Java feature set
Disadvantages for using this type of driver include the following:
 Applicable Client library must be installed
 Type 2 driver shows lower performance than type 3 or 4

Type 3: JDBC-Net pure Java(JDBC PROTOCAL)

 In a Type 3 driver, a three-tier approach is used to access databases. The JDBC clients
use standard network sockets to communicate with a middleware application server. The
socket information is then translated by the middleware application server into the call
format required by the DBMS, and forwarded to the database server.
 This kind of driver is extremely flexible, since it requires no code installed on the client
and a single driver can actually provide access to multiple databases.

Dept of CSE,CEC Advanced java and J2EE (15CS553) Page 3

Advantages for using this type of driver include the following:
 Allows access to almost any database since the databases ODBC drivers are readily
available
 Offers significantly better performance than the JDBC/ODBC Bridge and Type 2 Drivers
 Advanced Java feature set
 Scalable
 Caching
 Advanced system administration
 Does not require applicable database client libraries
The disadvantage for using this type of driver is that it requires a separate JDBC middleware
server to translate specific native-connectivity interface.
Type 4: Pure Java protocal
 In a Type 4 driver, a pure Java-based driver communicates directly with the vendor's
database through socket connection. This is the highest performance driver available for
the database and is usually provided by the vendor itself.
 This kind of driver is extremely flexible, you don't need to install special software on the
client or server. Further, these drivers can be downloaded dynamically.

Dept of CSE,CEC Advanced java and J2EE (15CS553) Page 4

Advantages for using this type of driver include the following:
 Allows access to almost any database since the databases ODBC drivers are readily
available
 Offers significantly better performance than the JDBC/ODBC Bridge and Type 2 Drivers
 Scalable
 Caching
 Advanced system administration
 Superior performance
 Advance Java feature set
 Does not require applicable database client libraries
The disadvantage for using this type of driver is that each database will require a driver

3.A brief overview of the JDBC process:

This process is divided into five steps:
 Loading the jdbc drivers
 Connecting to dbms
 Creating and executing statements
 Processing data returned by dbms
 Terminating the connection with the dbms

Dept of CSE,CEC Advanced java and J2EE (15CS553) Page 5

Loading the JDBC drivers:
 The jdbc must be loaded before the j2ee components can connect to the dbms.
 The Class.forName() method is used to load the jdbc driver and passing it the name of
driver as an arguments to the method.
 code snippet is shown below:
try
{
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
}
catch(Exception e)
{
S.o.p(e);
}
Connect to the dbms:
 Once driver is loaded, the j2ee components must connect to the dbms using the static
method getConnection().
 Where getConnection() methods belong to class called as DriverManager.
 getConnection() method passed the URL as argument of database and username
,password if necessary to database.Where URL is the string object that contains the driver
name and databse name that is being accessed by the j2ee components.
 DriverManager.getConnection() methods returns a connection interface that is used
throughout the process to reference the database.
 Code snippet is shown below:
try
{
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
Connection c=DriverManager.getConnection(“JDBC:ODBC:CSB);
}
catch(Exception e)
{
S.o.p(e); }

Dept of CSE,CEC Advanced java and J2EE (15CS553) Page 6

Create and execute sql statements:
 After jdbc driver is loaded and connection is successfully made with the databse.
 Now database is managed by the dbms is to send a sql query to the dbms for processing.
 The createStatement() method is used to create the statement object. The
createStatement() method is belongs to connection interface.
 The return value of createStatement() method is the Statement interface.
 The statement object is used to execute queryand return a resultset interface objectthat
conatains the response from the dbms.
 The different methods are used to execute the query are as follows:
 executeQuery(String)
 ExecuteUpadate(String)
 Execute(string)
Code snippet :
try
{
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
Connection c=DriverManager.getConnection(“JDBC:ODBC:CSB”);
Statement s=c.createStatement();
ResultSet r=s.executeQuery(“Select *from emp”);
}
catch(Exception e)
{
S.o.p(e);
}
Process data returned by the dbms:
 ResultSet object is assigned to receive the data from the DBMS after the query processed.
 ResultSet object conatins the method used to intract with the data that is returned by
DBMS to the j2ee components.
 Next() method is used to process the data from the DBMS.it is pointing to the first row of
table. Next() method is always used in iterative process.

Dept of CSE,CEC Advanced java and J2EE (15CS553) Page 7

 getString() methods of ResultSet object is used to copy the value of specified columns in
the current row of the ResultSet to a string object.
 The getString() methods is passed the name of the column or column index in the
ResultSet whose content need to be copied.
Code snippet:
try
{
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
Connection c=DriverManager.getConnection(“JDBC:ODBC:CSB”);
Statement s=c.createStatement();
ResultSet r=s.executeQuery(“Select *from emp”);
while(r.next())
{
String name=r.getString(1);
System.out.println(“name=”+name);
}
catch(Exception e)
{
S.o.p(e);
}
Terminating the connection to the DBMS:
The connection to the dbms is terminated by the close() method of the connection interface once
the j2ee component is finished accessing the dbms.
c.close();

Dept of CSE,CEC Advanced java and J2EE (15CS553) Page 8

program to retrieve the data from the database:
import java.sql.*;
class A
{
A()
{
try
{
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
Connection c=DriverManager.getConnection(“JDBC:ODBC:CSB”);
Statement s=c.createStatement();
ResultSet r=s.executeQuery(“Select *from emp”);
while(r.next())
{
String name=r.getString(1);
String usn=r.getString(2);
System.out.println(“name=”+name);
System.out.println(“USN=”+usn);
}
c.close();
}
catch(Exception e)
{
S.o.p(e);
}
}
public stataic void main(String ar[])
{
A a1=new A();
}
}

Dept of CSE,CEC Advanced java and J2EE (15CS553) Page 9

4.Import JDBC Packages
 The Import statements tell the Java compiler where to find the classes you reference in
your code and are placed at the very beginning of your source code.
 To use the standard JDBC package, which allows you to select, insert, update, and delete
data in SQL tables, add the following imports to your source code −
import java.sql.* ; // for standard JDBC programs
import java.math.* ; // for BigDecimal and BigInteger support

5.Database connection:

 Connection can be established using the DriverManager.getConnection() method.

 The data source that the jdbc components will connect to is defined using the url format.
The url consist of three parts.
 JDBC-Which indicates that the jdbc protocol is to be used to read the url
 <subprotocal>-which indicates the jdbc driver name
 <subname>- which indicates the name of the database.
 the three overloaded DriverManager.getConnection() methods −
 getConnection(String url)
 getConnection(String url, Properties prop)
 getConnection(String url, String user, String password)

Using Only a Database URL

DriverManager.getConnection( ) method requires only a database URL −
getConnection(String url)
try
{
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
Connection c=DriverManager.getConnection(“JDBC:ODBC:CSB”);
}
catch(Exception e)
{
Sysetm.out.println(e);
}

Dept of CSE,CEC Advanced java and J2EE (15CS553) Page 10

Using a Database URL with a username and password
 The most commonly used form of getConnection() requires you to pass a database URL,
a username, and a password:
 Now you have to call getConnection() method with appropriate username and password
to get a Connection object as follows −
 getConnection(String url, String user, String password)
try
{
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
Connection c=DriverManager.getConnection(“JDBC:ODBC:CSB”,”CSB”,”Tiger”);
}
catch(Exception e)
{
Sysetm.out.println(e);
}
Using a Database URL and a Properties Object
 A third form of the DriverManager.getConnection( ) method requires a database URL
and a Properties object −
DriverManager.getConnection(String url, Properties info);
 A Properties object holds a set of keyword-value pairs. It is used to pass driver properties
to the driver during a call to the getConnection() method.
try
{
Properties p = new Properties( );
FileInputStream f=new FileInputStream(“p1.txt”);
p.load(f);
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
Connection c=DriverManager.getConnection(“JDBC:ODBC:CSB”, p);
}
catch(Exception e)
{
Sysetm.out.println(e);
}

Dept of CSE,CEC Advanced java and J2EE (15CS553) Page 11

NOTE:just for reference-Following table lists down the popular JDBC driver names and
database URL.
RDBMS JDBC driver name URL format
MySQL com.mysql.jdbc.Driver jdbc:mysql://hostname/ databaseName
jdbc:oracle:thin:@hostname:port
ORACLE oracle.jdbc.driver.OracleDriver
Number:databaseName
DB2 COM.ibm.db2.jdbc.net.DB2Driver jdbc:db2:hostname:port Number/databaseName
jdbc:sybase:Tds:hostname: port
Sybase com.sybase.jdbc.SybDriver
Number/databaseName
Connection conn = DriverManager.getConnection(URL, USER, PASS);

5.The Statement Objects

 Once connection to the databse is opened,the j2ee component creates and sends
a query to access data contained in database.
 There are three ways statement object are used:
 Statement object
 preparedStatemnt object
 callableStatement object

Creating Statement Object

 Before you can use a Statement object to execute a SQL statement, you need to create
one using the Connection object's createStatement( ) method, as in the following example
Statement s=c.createStatement();
Once you've created a Statement object, you can then use it to execute an SQL statement
with one of its three execute methods.
 boolean execute (String SQL): Returns a boolean value of true if a ResultSet object can
be retrieved; otherwise, it returns false. Use this method to execute SQL DDL statements
or when you need to use truly dynamic SQL.

Dept of CSE,CEC Advanced java and J2EE (15CS553) Page 12

 int executeUpdate (String SQL): Returns the number of rows affected by the execution
of the SQL statement. Use this method to execute SQL statements for which you expect
to get a number of rows affected - for example, an INSERT, UPDATE, or DELETE
statement.
 ResultSet executeQuery (String SQL): Returns a ResultSet object. Use this method
when you expect to get a result set, as you would with a SELECT statement.
Program:
import java.sql.*;
class A
{
A()
{
try
{
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
Connection c=DriverManager.getConnection(“JDBC:ODBC:CSB”);
Statement s=c.createStatement();
ResultSet r=s.executeQuery(“Select *from emp”);
while(r.next())
{
String name=r.getString(1);
String usn=r.getString(2);
System.out.println(“name=”+name);
System.out.println(“USN=”+usn);
}
c.close();
}
catch(Exception e)
{
S.o.p(e);
}

Dept of CSE,CEC Advanced java and J2EE (15CS553) Page 13

}
Public stataic void main(String ar[])
{
A a1=new A();
}
}
The PreparedStatement Objects
 The PreparedStatement interface extends the Statement interface, which gives you added
functionality with a couple of advantages over a generic Statement object.
 This statement gives you the flexibility of supplying arguments dynamically.
PreparedStatement p=new PreparedStatement(“select name from emp
where usn=?”);

 The setXXX() methods bind values to the parameters, where XXX represents the Java
data type of the value you wish to bind to the input parameter.
o setXXX(int,string);
 First parameter represent the column index and second parameter represent the values
that replace the ? mark in the query.
 Next different execut methods of the preparedStatement object are called.
import java.sql.*;
class A
{
A()
{
try
{
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
Connection c=DriverManager.getConnection(“JDBC:ODBC:CSB”);
PreparedStatement p=c.PreaparedStatement(“select name from emp where usn=?”);
p.setSting(2, ”12cs001”);
ResultSet r=p.executeQuery();

Dept of CSE,CEC Advanced java and J2EE (15CS553) Page 14

while(r.next())
{
String name=r.getString(1);
String usn=r.getString(2);
System.out.println(“name=”+name);
System.out.println(“USN=”+usn);
}
c.close();
}
catch(Exception e)
{
S.o.p(e);
}
}
public static void main(String ar[])
{
A a1=new A();
}
}
The CallableStatement Objects
 Just as a Connection object creates the Statement and PreparedStatement objects, it also
creates the CallableStatement object, which would be used to execute a call to a database
stored procedure.
 Three types of parameters exist: IN, OUT, and INOUT. The PreparedStatement object
only uses the IN parameter. The CallableStatement object can use all the three.
 Here are the definitions of each −
Parameter Description
A parameter whose value is unknown when the SQL statement is
IN created. You bind values to IN parameters with the setXXX()
methods.

Dept of CSE,CEC Advanced java and J2EE (15CS553) Page 15

A parameter whose value is supplied by the SQL statement it returns.
OUT You retrieve values from theOUT parameters with the getXXX()
methods.
A parameter that provides both input and output values. You bind
INOUT variables with the setXXX() methods and retrieve values with the
getXXX() methods.
 The following code snippet shows how to employ the Connection.prepareCall() method
to instantiate a CallableStatement object based on the preceding stored procedure −
 If you have IN parameters, just follow the same rules and techniques that apply to a
PreparedStatement object; use the setXXX() method that corresponds to the Java data
type you are binding.
 When you use OUT and INOUT parameters you must employ an additional
CallableStatement method, registerOutParameter(). The registerOutParameter() method
binds the JDBC data type, to the data type that the stored procedure is expected to return.
 Once you call your stored procedure, you retrieve the value from the OUT parameter
with the appropriate getXXX() method. This method casts the retrieved value of SQL
type to a Java data type.

Program:
import java.sql.*;
class A
{
A()
{
try
{
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
Connection c=DriverManager.getConnection(“JDBC:ODBC:CSB”);
CallableStatement p=c.CallableSatement(“Call lastOrderNumber(?)”);
p.registerOutParameter(1,TYPES.VARCHAR);
p.executeQuery();

Dept of CSE,CEC Advanced java and J2EE (15CS553) Page 16

String name=p.getString(1);
System.out.println(“name=”+name);
}
c.close();
}
catch(Exception e)
{
S.o.p(e);
}
}
public stataic void main(String ar[])
{
A a1=new A();
}
}

6. ResultSet
A ResultSet consists of records. Each records contains a set of columns.
A ResultSet can be of a certain type. The type determines some characteristics and abilities of the
ResultSet.

Scrollable ResultSet:
At the time of writing there are three ResultSet types:
1. ResultSet.TYPE_FORWARD_ONLY
2. ResultSet.TYPE_SCROLL_INSENSITIVE
3. ResultSet.TYPE_SCROLL_SENSITIVE
The default type is TYPE_FORWARD_ONLY
 TYPE_FORWARD_ONLY means that the ResultSet can only be navigated forward. That is,
you can only move from row 1, to row 2, to row 3 etc. You cannot move backwards in
the ResultSet.
 TYPE_SCROLL_INSENSITIVE means that the ResultSet can be navigated (scrolled) both
forward and backwards. You can also jump to a position relative to the current position,

Dept of CSE,CEC Advanced java and J2EE (15CS553) Page 17

or jump to an absolute position. The ResultSet is insensitive to changes in the
underlying data source while the ResultSet is open. That is, if a record in the
ResultSet is changed in the database by another thread or process, it will not be
reflected in already opened ResulsSet's of this type.
 TYPE_SCROLL_SENSITIVE means that the ResultSet can be navigated (scrolled) both
forward and backwards. You can also jump to a position relative to the current position,
or jump to an absolute position. The ResultSet is sensitive to changes in the underlying
data source while the ResultSet is open. That is, if a record in the ResultSet is changed
in the database by another thread or process, it will be reflected in already opened
ResulsSet's of this type.

Method Description

absolute() Moves the ResultSet to point at an absolute position. The position is a row
number passed as parameter to the absolute() method.

afterLast() Moves the ResultSet to point after the last row in the ResultSet.

beforeFirst() Moves the ResultSet to point before the first row in the ResultSet.

first() Moves the ResultSet to point at the first row in the ResultSet.

last() Moves the ResultSet to point at the last row in the ResultSet.

next() Moves the ResultSet to point at the next row in the ResultSet.

previous() Moves the ResultSet to point at the previous row in the ResultSet.

relative() Moves the ResultSet to point to a position relative to its current position. The
relative position is passed as a parameter to the relative method, and can be
both positive and negative.

Moves the ResultSet

PROGRAM:
import java.sql.*;

Dept of CSE,CEC Advanced java and J2EE (15CS553) Page 18

class A
{
A()
{
try
{
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
Connection c=DriverManager.getConnection(“JDBC:ODBC:CSB”);
Statement s=c.createStatement(ResultSet.TPYE_SCROLL_SENSITIVE);
ResultSet r=s.executeQuery(“Select *from emp”);
While(r.next())
{
String name=r.getString(1);
String usn=r.getString(2);
System.out.println(“name=”+name);
System.out.println(“USN=”+usn);
}
r.first();
System.out.println(r.getString(1));
r.last();
System.out.println(r.getString(1));
r.previous();
System.out.println(r.getString(1));
r.absolute(2);
System.out.println(r.getString(1));
r.relative(2);
System.out.println(r.getString(1));
r.relative(-2);
System.out.println(r.getString(1));
c.close();
}

Dept of CSE,CEC Advanced java and J2EE (15CS553) Page 19

catch(Exception e)
{
S.o.p(e);
}
}
public static void main(String ar[])
{
A a1=new A();
}
}
Updatable ResultSet :
 The ResultSet concurrency determines whether the ResultSet can be updated, or only
read.
 A ResultSet can have one of two concurrency levels:
1. ResultSet.CONCUR_READ_ONLY
2. ResultSet.CONCUR_UPDATABLE
 CONCUR_READ_ONLY means that the ResultSet can only be read.
 CONCUR_UPDATABLE means that the ResultSet can be both read and updated.
 If a ResultSet is updatable, you can update the columns of each row in the ResultSet. You
do so using the many updateXXX() methods.
 updateRow() is called that the database is updated with the values of the row

import java.sql.*;
class A
{
A()
{
try
{
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
Connection c=DriverManager.getConnection(“JDBC:ODBC:CSB”);

Dept of CSE,CEC Advanced java and J2EE (15CS553) Page 20

Statement s=c.createStatement(ResultSet.CONCUR_UPDATABLE);
ResultSet r=s.executeQuery(“Select *from emp where usn=2”);
r.update(1, ”Avinash”);
r.updateRow();
while(r.next())
{
String name=r.getString(1);
String usn=r.getString(2);
System.out.println(“name=”+name);
System.out.println(“USN=”+usn);
}
c.close();
}
catch(Exception e)
{
S.o.p(e);
}
}
public stataic void main(String ar[])
{
A a1=new A();
}
}
Inserting Rows into a ResultSet
If the ResultSet is updatable it is also possible to insert rows into it. You do so by:
1. update row column values using updateXX(string,string);
2. call ResultSet.insertRow()
import java.sql.*;
class A
{
A()

Dept of CSE,CEC Advanced java and J2EE (15CS553) Page 21

{
try
{
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
Connection c=DriverManager.getConnection(“JDBC:ODBC:CSB”);
Statement s=c.createStatement(ResultSet.CONCUR_UPDATABLE);
ResultSet r=s.executeQuery(“Select *from emp ”);
r.update(1, ”Avinash”);
r.insertRow();
while(r.next())
{
String name=r.getString(1);
String usn=r.getString(2);
System.out.println(“name=”+name);
System.out.println(“USN=”+usn);
}
c.close();
}
catch(Exception e)
{
S.o.p(e);
}
}
public stataic void main(String ar[])
{
A a1=new A();
}
}

Dept of CSE,CEC Advanced java and J2EE (15CS553) Page 22

Deleteing row from a ResultSet:
 Deleterow() method is nused to delete the row from the databse.
 DeleteRow() method pass as an integer argument ,which specify the row to be deleted.
ResultSet.deleteRow(int);
Program:
import java.sql.*;
class A
{
A()
{
Try
{
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
Connection c=DriverManager.getConnection(“JDBC:ODBC:CSB”);
Statement s=c.createStatement(ResultSet.CONCUR_UPDATABLE);
ResultSet r=s.executeQuery(“Select *from emp ”);
r.deleteRow(0);
while(r.next())
{
String name=r.getString(1);
String usn=r.getString(2);
System.out.println(“name=”+name);
System.out.println(“USN=”+usn);
}
c.close();
}
catch(Exception e)
{
S.o.p(e);
}
}

Dept of CSE,CEC Advanced java and J2EE (15CS553) Page 23

public static void main(String ar[])
{
A a1=new A();
}
}
7.Transactions
 A transaction is a set of actions to be carried out as a single, atomic action. Either all of the
actions are carried out, or none of them are.
 Transaction is successfully completed only if each task is comleted successfully. If one of task is
fail, the entire transaction is fail.
 If one of sql is failed, the sql statement that is executed successfully upto the point in the
transaction must be rollback.
 Different methods of Transaction processing are:
 setAutoCommit(boolean)-setAutoCommit() pass the parameter as false intial once all

the transcation is completed. As soon as it invokes the commit() ,the setAutoCommit()

method is set as true.
 setSavePoint(String);-set the save point to the sql statement .

 releaseSavePoint(String);-it realse the save point assing to the sql statement if and only

if all sql statement are executed successfully.

 commit();-once all sql statement are executed successfully,rollback is not possible.

 rollback();-if one of the sql statement is failed,then rollback() method is invoked and

control goes back to the fail sql statement for further execution.
Program:
import java.sql.*;
class A
{
A()
{
try
{
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);

Dept of CSE,CEC Advanced java and J2EE (15CS553) Page 24

Connection c=DriverManager.getConnection(“JDBC:ODBC:CSB”);
Statement s=c.createStatement();
c.setAutoCommit(false);
c.setSavePoint(“csb”);
ResultSet r=s.executeQuery(“Select *from emp where usn=2”);
r=s.executeQuery(“Select *from emp”);
c.releaseSavePoint(“csb”);
c.commit();
}
c.close();
}
catch(Exception e)
{
S.o.p(e);
c.rollback();
}
}
public static void main(String ar[])
{
A a1=new A();
}
}

8.Metadata:
Metadata is data about data. J2ee component can access metadata by using
 DatabaseMetaData interface.
 ResultSetMetaData interface
DatabaseMetaData interface:
 The DatabaseMetaData interface is used to retrieve information about database,table,columns
and index amoung other information about dbms.

Dept of CSE,CEC Advanced java and J2EE (15CS553) Page 25

 J2ee component retrives metadata about the database by calling getMetaData() method of the
connection interface object. The getMetaData() method return a DatabaseMetaData object that
contain information of database and components.
 Most commonly used DatabaseMetaData interface methods as follows:
 getDataBaseProductName()- returns the product name of the database.
 getUserName()-returns the username of database
 getURL()- returns the URL of the database
 getSchemas()-returns the all schemas of the database which are available
 getPrimaryKeys()-returns the primary key available in the database
 getTables()-returns the table name in the database
program:
import java.sql.*;
class A
{
A()
{
try
{
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
Connection c=DriverManager.getConnection(“JDBC:ODBC:CSB”);
Statement s=c.createStatement();
ResultSet r=s.executeQuery(“Select *from emp”);
DatabaseMetaData d=c.getMetaData();
System.out.println(d.getUserNAme());
System.out.println(d.getTables());
System.out.println(d.getURL());
}
c.close();
}
catch(Exception e)
{

Dept of CSE,CEC Advanced java and J2EE (15CS553) Page 26

S.o.p(e);
}
}
public static void main(String ar[])
{
A a1=new A();
}
}

ResultSetMetaData interface:
 ResultSetMetaData interface is used to retrieve the information by calling the getMetaData()
method of ResultSet interface.
 Different methods in the ResultSetMetaData inetface are as follows:
 getColunmCount()-returns the number of column available in the table
 getColunmName(int)-returns the name of column specified by the column
number
 getColunmTye(int)-returns the type of column specified by the column number
program:
import java.sql.*;
class A
{
A()
{
try
{
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
Connection c=DriverManager.getConnection(“JDBC:ODBC:CSB”);
Statement s=c.createStatement();
ResultSet r=s.executeQuery(“Select *from emp”);
ResultSetMetaData d=r.getMetaData();
System.out.println(d.getColunmName(1));

Dept of CSE,CEC Advanced java and J2EE (15CS553) Page 27

System.out.println(d.getColunmCount());
System.out.println(d.getColunmType(1));
}
c.close();
}
catch(Exception e)
{
S.o.p(e);
}
}
public static void main(String ar[])
{
A a1=new A();
}
}

Data Types:
The JDBC driver converts the Java data type to the appropriate JDBC type, before sending it to the
database. It uses a default mapping for most data types. The following table summarizes the default
JDBC data type that the Java data type is converted to, when you call the setXXX() method.

SQL JDBC/Java
VARCHAR String
CHAR String
LONGVARCHAR String
BIT boolean
NUMERIC java.math.BigDecimal
TINYINT byte
SMALLINT short
INTEGER int
BIGINT long
REAL float
FLOAT float
DOUBLE double

Dept of CSE,CEC Advanced java and J2EE (15CS553) Page 28

VARBINARY byte[ ]
BINARY byte[ ]
DATE java.sql.Date
TIME java.sql.Time
TIMESTAMP java.sql.Timestamp
CLOB java.sql.Clob
BLOB java.sql.Blob

Exception :

SQLException Methods
An SQLException can occur both in the driver and the database. When such an exception occurs,
an object of type SQLException will be passed to the catch clause.

The passed SQLException object has the following methods available for retrieving additional
information about the exception −

Method Description
getErrorCode( ) Gets the error number associated with the exception.
Gets the JDBC driver's error message for an error,
getMessage( ) handled by the driver or gets the Oracle error number
and message for a database error.
Gets the XOPEN SQLstate string. For a JDBC driver
error, no useful information is returned from this
getSQLState( )
method. For a database error, the five-digit XOPEN
SQLstate code is returned. This method can return null.
getNextException( ) Gets the next Exception object in the exception chain.
Prints the current exception, or throwable, and it's
printStackTrace( )
backtrace to a standard error stream.
Prints this throwable and its backtrace to the print
printStackTrace(PrintStream s)
stream you specify.
Prints this throwable and it's backtrace to the print
printStackTrace(PrintWriter w)
writer you specify.

Dept of CSE,CEC Advanced java and J2EE (15CS553) Page 29

DatabaseMetaData Interface in Java-JDBC

DatabaseMetaData in Java, which resides in java.sql package, provides information about the
database (DB meta data) you are connected to.

Using the methods provided by Java DatabaseMetaData interface you can get information
about-

• Database like DB name and version

• JDBC driver like the driver’s name and version,
• names of DB schemas,
• name of tables in any DB schema,
• names of views,
• information about the procedures.

In this post we’ll see examples of some of the commonly used methods. You can get the list
of full methods here-
https://docs.oracle.com/javase/9/docs/api/java/sql/DatabaseMetaData.html

Table of contents

1. How to get DatabaseMetaData object in JDBC

2. DatabaseMetaData example-Getting DB product and version information
3. DatabaseMetaData example-Getting driver information
4. Example to get tables using DatabaseMetaData in JDBC
5. Example to get Procedures using DatabaseMetaData in JDBC

How to get DatabaseMetaData object in JDBC

You can get the DatabaseMetaData instance by calling the getMetaData() method of the
Connection class.

DatabaseMetaData dbMetaData = connection.getMetaData();

DatabaseMetaData example-Getting DB product and version information

This example code shows how you can get DB name and version information using
DatabaseMetaData in JDBC.

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBMetaData {

public static void main(String[] args) {

Connection connection = null;
try {
// Loading driver
Class.forName("com.mysql.jdbc.Driver");

// Creating connection
connection =
DriverManager.getConnection("jdbc:mysql://localhost:3306/netjs",
"root", "admin");

DatabaseMetaData dbMetaData = connection.getMetaData();

System.out.println("Database Name - " +

dbMetaData.getDatabaseProductName());
System.out.println("Database Version - " +
dbMetaData.getDatabaseProductVersion());
System.out.println("Database Major Version - " +
dbMetaData.getDatabaseMajorVersion());
System.out.println("Database Minor Version - " +
dbMetaData.getDatabaseMinorVersion());
System.out.println("Database User - " + dbMetaData.getUserName());

} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
if(connection != null){
//closing connection
try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
} // if condition
}// finally
}
}

DatabaseMetaData example - Getting driver information

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBMetaData {

public static void main(String[] args) {
Connection connection = null;
try {
// Loading driver
Class.forName("com.mysql.jdbc.Driver");

// Creating connection
connection =
DriverManager.getConnection("jdbc:mysql://localhost:3306/netjs",
"root", "admin");

DatabaseMetaData dbMetaData = connection.getMetaData();

System.out.println("Driver Name - " + dbMetaData.getDriverName());

System.out.println("Driver Version - " +
dbMetaData.getDriverVersion());
System.out.println("Driver Major Version - " +
dbMetaData.getDriverMajorVersion());
System.out.println("Driver Minor Version - " +
dbMetaData.getDriverMinorVersion());
System.out.println("JDBC Major Version - " +
dbMetaData.getJDBCMajorVersion());
System.out.println("JDBC Minor Version - " +
dbMetaData.getJDBCMinorVersion());
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
if(connection != null){
//closing connection
try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
} // if condition
}// finally
}
}

Example to get tables using DatabaseMetaData in JDBC

For getting tables you can use getTables(String catalog, String schemaPattern, String
tableNamePattern, String[] types) method. You can provide null as value for all the
parameters, that way you don’t narrow the search and all the tables are returned. If you want
to narrow your search to get specific tables then you can provide values for these parameters.

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DBMetaData {

public static void main(String[] args) {
Connection connection = null;
try {
// Loading driver
Class.forName("com.mysql.jdbc.Driver");

// Creating connection
connection =
DriverManager.getConnection("jdbc:mysql://localhost:3306/world",
"root", "admin");

DatabaseMetaData dbMetaData = connection.getMetaData();

ResultSet rs = dbMetaData.getTables(null, null, null, null);

while (rs.next()){
System.out.println("Table name " + rs.getString(3));
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
if(connection != null){
//closing connection
try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
} // if condition
}// finally
}
}

Output

Table name city

Table name country
Table name countrylanguage

Here I am connecting to “world” schema in MySQL and getting all the tables.

Each table description in the returned ResultSet has the following columns:

Table Description Columns

Column Name Type Description
TABLE_CAT String table catalog (may be null)
TABLE_SCHEM String table schema (may be null)
TABLE_NAME String table name
table type. Typical types are
TABLE_TYPE String
"TABLE", "VIEW" etc.
explanatory comment on the table
REMARKS String
(may be null)
TYPE_CAT String the types catalog (may be null)
TYPE_SCHEM String the types schema (may be null)
TYPE_NAME String type name (may be null)
name of the designated "identifier"
SELF_REFERENCING_COL_NAME String
column of a typed table (may be null)
specifies how values in
REF_GENERATION String SELF_REFERENCING_COL_NAME
are created.

That’s why column index is 3 while getting result from ResultSet as TABLE_NAME is at
number 3.

Example to get Procedures using DatabaseMetaData in JDBC

For getting procedures you can use getProcedures(String catalog, String schemaPattern,
String procedureNamePattern) method. Again you can pass null as value for all the
parameters if you don’t want to narrow the search.

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DBMetaData {

public static void main(String[] args) {

Connection connection = null;
try {
// Loading driver
Class.forName("com.mysql.jdbc.Driver");

// Creating connection
connection =
DriverManager.getConnection("jdbc:mysql://localhost:3306/netjs",
"root", "admin");

DatabaseMetaData dbMetaData = connection.getMetaData();

ResultSet rs = dbMetaData.getProcedures(null, null, null);

while (rs.next()){
System.out.println("Procedure name " + rs.getString(3));
}

} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
if(connection != null){
//closing connection
try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
} // if condition
}// finally
}
}

Each procedure description in the returned ResultSet has the following columns:

Procedure Description Columns

Column Name Type Description
PROCEDURE_CAT String procedure catalog (may be null)
PROCEDURE_SCHEM String procedure schema (may be null)
PROCEDURE_NAME String procedure name
reserved for future use
reserved for future use
reserved for future use
REMARKS String explanatory comment on the procedure
PROCEDURE_TYPE short type name (may be null)
The name which uniquely identifies this procedure
SPECIFIC_NAME String
within its schema.
DBC Meta Data is the collective information about the data structure and property of a
column available in table. The meta data of any table tells you the name of the
columns,datatype used in column and constraint used to enter the value of data into column
of the table.

Understand with Example

The Tutorial helps you to know understand an example from JDBC Metadata Get table. In
this program, the code describe you JDBC Meta Data Get tables that explain the column
property and structure of table. The class Jdbc MetaDataGettables include the list of methods
to get the meta data property of table as given follow -

Loading a driver by calling a class.forname( ),this accept driver class as argument.

DriverManager.getConnection ( ) -This method return you a connection object and built a

connection between url and database. Once a connection is built, a front end can access,
insert ,update and retrieve the data in the backend database.

con.createStatement ( ) -This is used to create a sql object. An object of connection class is

used to send and create a sql query in the database backend.

executeQuery ( ) -The method retrieve a record set from a table in database. The retrieve
record set is assigned to a result set.

getMetaData ( ) - The Result Set call get Metadata( ),which return you the property of the
retrieve record set (length,field,column).Meta Data account for data element and its attribute.

getcolumncount ( ) -The method return you a integer data type and provides you the
number of column in the Result set object.

Finally the println print the table name, field, size and data type.

In case there is an exception in the try block The subsequent catch block caught and handle
the exception.

JdbcMetaDataGettables.java
import java.sql.*;

public class JdbcMetaDataGettables {

static public final String driver = "com.mysql.jdbc.Driver";

static public final String connection =
"jdbc:mysql://localhost:3306/komal";
static public final String user = "root";
static public final String password = "root";

public static void main(String args[]) {

try {
Class.forName(driver);
Connection con =
DriverManager.getConnection(connection, user, password);
Statement st = con.createStatement();

String sql = "select * from person";

ResultSet rs = st.executeQuery(sql);
ResultSetMetaData metaData = rs.getMetaData();

int rowCount = metaData.getColumnCount();

System.out.println("Table Name : " + metaData.getTableName(2));

System.out.println("Field \tsize\tDataType");

for (int i = 0; i < rowCount; i++) {

System.out.print(metaData.getColumnName(i + 1) + " \t");
System.out.print(metaData.getColumnDisplaySize(i + 1) + "\t");
System.out.println(metaData.getColumnTypeName(i + 1));
}

} catch (Exception e) {
System.out.println(e);
}
}
}
What Is CallableStatement In Java?
CallableStatement is an interface in JDBC API which is used to call the stored procedures
from the Java code. CallableStatement extends PreparedStatement which in turn extends
Statement. Below image shows the template to how to get CallableStatement object, how to
set IN parameters, how to register OUT parameters and how to retrieve the result returned by
the stored procedure.

Now, let’s see some examples on how to call stored procedures with IN, OUT and IN OUT
parameters.

Java Program To Call Stored Procedure With No

Parameters :
Stored Procedure :

1CREATE OR REPLACE PROCEDURE P1 AS

2BEGIN
3INSERT INTO STUDENT VALUES(123, 'RAHUL', 'SHARMA', 'FIRST');
4UPDATE STUDENT SET FIRST_NAME = 'KIRAN' WHERE ID = 123;
END;
5

Java Program :

1 import java.sql.*;
2
3 public class CallableStatementExample
{
4 static
5 {
6 //STEP 1 : Registering The Driver Class
7
8 try
{
9 Class.forName("oracle.jdbc.driver.OracleDriver");
10 }
11 catch (ClassNotFoundException e)
12 {
13 System.out.println("Unable To Load The Driver class");
}
14 }
15
16 public static void main(String[] args)
17 {
18 Connection con = null;
19
20 CallableStatement cstmt = null;
21
try
22 {
23 //Database Credentials
24
25 String URL = "jdbc:oracle:thin:@localhost:1521:XE";
26
27 String username = "username";
28
29 String password = "password";
30
//STEP 2 : Creating The Connection Object
31
32
con = DriverManager.getConnection(URL, username, password);
33
34 //STEP 3 : Creating The CallableStatement Object
35
36 cstmt = con.prepareCall("{call P1}");
37
38 //STEP 4 : Executing The CallableStatement
39
40 cstmt.execute();
41 }
catch (SQLException e)
42 {
43 e.printStackTrace();
44 }
45 finally
{
46 //STEP 5 : Closing The DB Resources
47
48 //Closing the CallableStatement object
49
50 try
51 {
52 if(cstmt!=null)
{
53 cstmt.close();
54 cstmt=null;
55 }
56 }
57 catch (SQLException e)
{
58 e.printStackTrace();
}
59
60
//Closing the Connection object
61
62 try
63 {
64 if(con!=null)
65 {
con.close();
66 con=null;
67 }
68 }
69 catch (SQLException e)
70 {
e.printStackTrace();
71 }
72 }
73 }
74}
75
76
77
78
79
80
81
82
83
84
85
86

Java Program To Call Stored Procedure With IN

Parameters :
Stored Procedure :

1CREATE OR REPLACE PROCEDURE P2(PARAMETER1 IN VARCHAR, PARAMETER2 NUMBER)

2AS
3BEGIN
4UPDATE STUDENT SET FIRST_NAME = PARAMETER1 WHERE ID = PARAMETER2;
END;
5

Java Program :

1 import java.sql.*;
2
3 public class CallableStatementExample
{
4 static
5 {
6 //STEP 1 : Registering The Driver Class
7
8 try
9 {
Class.forName("oracle.jdbc.driver.OracleDriver");
10 }
11 catch (ClassNotFoundException e)
12 {
13 System.out.println("Unable To Load The Driver class");
}
14 }
15
16 public static void main(String[] args)
17 {
18 Connection con = null;
19
20 CallableStatement cstmt = null;
21
try
22 {
23 //Database Credentials
24
25 String URL = "jdbc:oracle:thin:@localhost:1521:XE";
26
27 String username = "username";
28
29 String password = "password";
30
//STEP 2 : Creating The Connection Object
31
32
con = DriverManager.getConnection(URL, username, password);
33
34 //STEP 3 : Creating the CallableStatement object by passing
35IN parameters
36
37 cstmt = con.prepareCall("{call P2('MyName', 123)}");
38
39 //STEP 4 : Executing The CallableStatement
40
41 cstmt.execute();
}
42 catch (SQLException e)
43 {
44 e.printStackTrace();
45 }
finally
46
{
47 //STEP 5 : Closing The DB Resources
48
49 //Closing the CallableStatement object
50
51 try
52 {
if(cstmt!=null)
53 {
54 cstmt.close();
55 cstmt=null;
56 }
57 }
catch (SQLException e)
58 {
59 e.printStackTrace();
60 }
61
62 //Closing the Connection object
63
64 try
{
65 if(con!=null)
66 {
67 con.close();
68 con=null;
69 }
}
70 catch (SQLException e)
71 {
72 e.printStackTrace();
73 }
}
74
}
75}
76
77
78
79
80
81
82
83
84
85
86

Java Program To Call Stored Procedure With IN And

OUT Parameters :
Stored Procedure :

1CREATE OR REPLACE PROCEDURE P3(PARAMETER1 IN VARCHAR, PARAMETER2 OUT VARCHAR)

2AS
3BEGIN
4SELECT GRADE INTO PARAMETER2 FROM STUDENT WHERE ID = PARAMETER1;
END;
5

Java Program :

1 import java.sql.*;
2
public class CallableStatementExample
3 {
4 static
5 {
6 //STEP 1 : Registering The Driver Class
7
8 try
{
9 Class.forName("oracle.jdbc.driver.OracleDriver");
10 }
11 catch (ClassNotFoundException e)
12 {
System.out.println("Unable To Load The Driver class");
13 }
14 }
15
16 public static void main(String[] args)
17 {
18 Connection con = null;
19
CallableStatement cstmt = null;
20
21 try
22 {
23 //Database Credentials
24
25 String URL = "jdbc:oracle:thin:@localhost:1521:XE";
26
27 String username = "username";
28
String password = "password";
29
30
//STEP 2 : Creating The Connection Object
31
32 con = DriverManager.getConnection(URL, username, password);
33
34 //STEP 3 : Creating The CallableStatement Object
35
36 cstmt = con.prepareCall("{call P3(?, ?)}");
37
38 //Setting IN Parameter
39
40 cstmt.setInt(1, 123);
41
//Registering OUT parameters
42
43 cstmt.registerOutParameter(2, Types.VARCHAR);
44
45 //STEP 4 : Executing The CallableStatement
46
47 cstmt.execute();
48
49 //Retrieving the result returned by stored procedure
50
51 String grade = cstmt.getString(2);
52
53 System.out.println(grade);
}
54 catch (SQLException e)
55 {
56 e.printStackTrace();
}
57 finally
58 {
59 //STEP 5 : Closing The DB Resources
60
61 //Closing the CallableStatement object
62
63 try
{
64 if(cstmt!=null)
65 {
66 cstmt.close();
67 cstmt=null;
68 }
}
69 catch (SQLException e)
70 {
71 e.printStackTrace();
72 }
73
74 //Closing the Connection object
75
try
76 {
77 if(con!=null)
78 {
79 con.close();
con=null;
80 }
81 }
82 catch (SQLException e)
83 {
84 e.printStackTrace();
}
85 }
86 }
87 }
88
89
90
91
92
93
94
95
96
97
98
99
100

Java Program To Call Stored Procedure With IN OUT

Parameter :
Stored Procedure :

1CREATE OR REPLACE PROCEDURE P4(PARAMETER1 IN NUMBER, PARAMETER2 IN OUT

2VARCHAR)
AS
3BEGIN
4UPDATE STUDENT SET GRADE = PARAMETER2 WHERE ID = PARAMETER1;
5SELECT GRADE INTO PARAMETER2 FROM STUDENT WHERE ID = PARAMETER1;
6END;

Java Program :

1 import java.sql.*;
2
3 public class CallableStatementExample
{
4 static
5 {
6 //STEP 1 : Registering The Driver Class
7
8 try
9 {
Class.forName("oracle.jdbc.driver.OracleDriver");
10 }
11 catch (ClassNotFoundException e)
12 {
13 System.out.println("Unable To Load The Driver class");
}
14 }
15
16 public static void main(String[] args)
17 {
18 Connection con = null;
19
20 CallableStatement cstmt = null;
21
try
22 {
23 //Database Credentials
24
25 String URL = "jdbc:oracle:thin:@localhost:1521:XE";
26
27 String username = "username";
28
29 String password = "password";
30
//STEP 2 : Creating The Connection Object
31
32
con = DriverManager.getConnection(URL, username, password);
33
34 //STEP 3 : Creating The CallableStatement Object
35
36 cstmt = con.prepareCall("{call P4(?, ?)}");
37
38 //Setting IN Parameters
39
40 cstmt.setInt(1, 123);
41
42 cstmt.setString(2, "LAST");
43
//Registering OUT parameters
44
45 cstmt.registerOutParameter(2, Types.VARCHAR);
46
47 //STEP 4 : Executing The CallableStatement
48
49 cstmt.execute();
50
51 //Retrieving OUT parameter
52
53 String grade = cstmt.getString(2);
54
55 System.out.println(grade);
}
56 catch (SQLException e)
57 {
e.printStackTrace();
58 }
59 finally
60 {
61 //STEP 5 : Closing The DB Resources
62
63 //Closing the CallableStatement object
64
try
65 {
66 if(cstmt!=null)
67 {
68 cstmt.close();
69 cstmt=null;
}
70 }
71 catch (SQLException e)
72 {
73 e.printStackTrace();
}
74
75
//Closing the Connection object
76
77 try
78 {
79 if(con!=null)
80 {
con.close();
81 con=null;
82 }
83 }
84 catch (SQLException e)
85 {
e.printStackTrace();
86 }
87 }
88 }
89 }
90
91
92
93
94
95
96
97
98
99
100
101
102
Why did JDBC come into existence?
In earlier days, the front-end applications are connected to the Databases using the functions
provided by the database vendors. For example, the C and C++ applications are connected to
the databases using a set of functions given by Oracle Corporation called orcl.h header
file. But, by this, the application becomes database dependent because every DB vendor
gives its own set of functions for communication. To overcome this, Microsoft with Simba
Technologies has provided us with the ODBC (Open Database Connectivity) community
with which we can connect and communicate with Database in an independent manner. Now
the general question arises like,

Why is ODBC not used in Java Applications?

ODBC API is written in C Language with pointers. But Java applications do not contain
pointers, so the Java code is being converted to pointers code internally which is time-
consuming and poor in performance. Also, ODBC is platform-dependent and database-
independent. In order to overcome this problem, Sun Microsystems introduced the JDBC
technology to make the Java programs platform and database independent.

JDBC is a Java API which offers a natural Java interface for working with SQL.

What is JDBC?
Primarily, JDBC is an API (Application Programming Interface) or channel with which we
can connect Java applications to various databases. It is a Java API that accesses the data
from various kinds of Relational databases. It works with Java on various kinds of platforms
like Windows, Linux, MacOS etc.,

Before reading this tutorial, you need to have the basic knowledge of the Core Java concepts
like Abstraction, Interfaces, Collection Framework, Exception Handling etc.,

In this tutorial, we will discuss the JDBC Architecture, SQL Syntax, Environment set-up,
Drivers used, Connections, and Statements to query the Database. Let’s get started.

Learn JDBC in Java & Get A Free Certificate

JDBC Architecture:
The JDBC API proposed will support both two-tier and three-tier architecture flows. So, in
general the architecture is comprised of two layers,

1. JDBC API – Java application to Driver Manager Connection

2. JDBC Driver Manager – JDBC Manager to Driver Connection

JDBC API provides transparent connectivity using the Driver Manager and database-specific
drivers.

JDBC Driver Manager provides assurance for the usage of the correct database driver to
access each data source.

Common Components through which JDBC interacts with database:

1. JDBC API
1. The API provides standard methods and interfaces to smoothen the
communication with the database.
2. The process is simplified by providing two packages java.sql. * and
javax.sql.*.
3. The two packages exhibit WORA (Write Once Run Everywhere) capability
which contains both Java SE and Java EE support.
2. JDBC Driver Manager
1. The Driver Manager manages the list of database drivers.
2. It loads the database-specific Driver in Java application to establish the
connectivity with the database.
3. We use this Manager to make a call to the database.
3. JDBC Test Suite
1. Test Suite is used for Testing purposes (such as Insertion, deletion etc.,).

Types of JDBC Architecture:

1. Two-Tier Architecture:
1. The Java Application directly communicates with the Database.
2. When the user sends in a query, the results are sent back to the user directly.
3. It is similar to the Client-Server model where Client is the User Machine and
Server is the Data source machine.
2. Three-Tier Architecture:
1. The Java Application communicates with the middleware rather than the
database itself.
2. When the user sends in a query, it hits the middle ware which in turn
hits/commands the Data source to get the results back and follows the same
path to the user.
3. This model is most predominant due to its abstraction at each layer.

Structured Query Language (SQL):

SQL is basically elaborated as Structured Query Language. It allows the users/programmers
to perform various operations on the database. The operations can be creating the records,
reading the entries, updating the content and Deleting the entries.

Why SQL?
1. Used by almost all Relational databases.
2. Allows users/programmers to write the database code independent on the underlying
database.

There is a fact that without learning the SQL we cannot understand the concepts of JDBC.
We will have a quick and basic overview of the SQL, SQL Operations often referred to as
CRUD Operations and their syntax.

1. CREATE Database

The CREATE DATABASE statement is generally used for creating a new database. The
syntax is,

SQL> CREATE DATABASE DATABASE_NAME;

2. DROP Database

The DROP DATABASE statement is generally used for deleting an existing database. The
syntax is,

SQL> DROP DATABASE DATABASE_NAME;

3. CREATE Table

The CREATE TABLE statement is used for creating a table within a database. The syntax is,

SQL> CREATE TABLE table_name

column_name1 coulumn_data_type,

column_name2 coulumn_data_type1,

);

4. DROP Table

The DROP TABLE statement is used for deleting an existing table in the database. The
syntax is,

SQL> DROP TABLE table_name;

5. INSERT Data

The INSERT statement is used for inserting the data into the table. It represents what new
data should appear in the respective columns. The syntax is,

SQL> INSERT into TABLE_NAME VALUES (coulmn1, column2, …);

6. SELECT Data

The SELECT statement is basically used to retrieve or fetch the data from the database. The
syntax is,

SQL> SELECT column_name1, column_name2, …

FROM table_name

WHERE conditions;

(OR)

SQL> SELECT *

FROM table_name

WHERE conditions;

7. UPDATE Data
The UPDATE statement is used in order to update the data. The syntax is,

SQL> UPDATE table_name

SET column_name1 = value1, column_name2 = value2, …

WHERE conditions;

8. DELETE Data

The DELETE statement is used to delete the data from the tables. The syntax is,

SQL> DELETE FROM table_name

WHERE conditions;

From the above details we can conclude that SQL provides us the capability to Create, Read,
Update and Delete the data from the databases. These operations are basically referred to as
CRUD Operations.

Environmental setup:
To start with JDBC, you need to set-up the environment by following the below steps. Please
note that the below environment set-up is subjected to only Windows platform.

1. Install Java
2. Install Database
3. Install Database Drivers
4. Set the Database Credentials

We will delve into each step below,

1. Installing Java environment

1. Install the latest JDK (Java Development Kit) of the JDK version you wish to
work upon from the Oracle official site by accepting the terms and conditions.
https://www.oracle.com/java/technologies/javase-downloads.html#JDK15.
2. After the download, make sure the environmental variables mentioned below are set
as follows,
1. JAVA_HOME: C:\Program Files\Java\jdk1.15.0.
2. CLASSPATH: C:\Program Files\Java\jdk1.15.0_220\jre\lib.
3. PATH: C:\Program Files\Java\jre1.15.0_220\bin.
4. If the above-mentioned variables are already present then, please make sure to
verify them if they are correct.

3. By the above installation or verification, you will automatically get the java.sql and
javax.sql packages along with the JDK you installed.
4. Installing the Target Database
1. The most important thing in JDBC is you need a Database where you create a
Database/Table and execute your own queries and modify them accordingly.
2. You have plenty of choices to install the most suitable database for you.
1. MySQL Database: It is an open-source database. It can be downloaded
from http://dev.mysql.com/downloads/mysql. In addition, also
download the MySQL Administrator from
https://dev.mysql.com/downloads/workbench/ and MySQL Query
Browser. Finally download and unzip the MySQL Connector/J from
https://dev.mysql.com/downloads/windows/installer/8.0.html.
Accordingly set the CLASSPATH variable C:\Program
Files\MySQL\mysql-connector-java-8.0.22\mysql-connector-java-
8.0.22.bin.jar. The Driver version may vary based on your installation.
3. Installing the Database Drivers
1. The latest JDK specification includes the JDBC-ODBC Bridge Drivers that
makes ODC drivers available for the programmers using the JDBC API for
Development and Testing purposes.
2. Now-a-days the most of the JDBC drivers are shipped along with the database
installation only.
4. Setting the Database Credentials

Assuming that we are going to use the MySQL database,

1. By Default, the administrator is set to root and provision is allowed to choose the
password.
2. By using the above root id and password, you can create another user id and password
of your choice.
3. After this, you can navigate to the installation directory of MySQL and create the
Database and Tables further for the experimental purposes.

Now, you guys are ready for experimenting with JDBC Let’s get started with the in-detail
concepts involved in JDBC API. Now, we will have a sample JDBC Program with which you
can get a complete idea on how to establish a connection to the database and perform the
operations.
JDBC Sample Code:
In this example, you will see how to implement he five basic steps to connect with the Oracle
database using JDBC in Java program.

1. Create Table STUDENT_DETAILS.

create table student_details (studentNumnumber(10), lastName varchar(50), firstName

varchar(50), class number(3), total_marks number(5));

2. Insert data into the STUDENT_DETAILS table

insert into student_details values (1, ‘ABC’, ‘A’, 2, 99);

insert into student_details values (2, ‘XYZ’, ‘X’, 2, 80);

3. Implement Java Program

1 // import sqlstandard package to use it in our program

importjava.sql.*;
2 publicclassSample_JDBC_Program {
3 publicstaticvoidmain(String[] args) throwsClassNotFoundException,
4 SQLException {
5 // store the SQL statement in a string
String QUERY = "select * from student_details";
6
//register the oracle driver with DriverManager
7 Class.forName("com.mysql.jdbc.Driver");
8 // If we have use>Java 7 we open the connection in try statement
9 try(Connection conn =
1 DriverManager.getConnection("jdbc:mysql:thin:system/pass@123localhost:152
1:XE"))
0 {
1 Statement statemnt1 = conn.createStatement();
1 // After Creating the statement, execute it
1 ResultSet rs1 = statemnt1.executeQuery(QUERY);
2 {
//Get the values of the record using while loop from
1 result set
3 while(rs1.next())
1 {
4 intempNum = rs1.getInt("studentNum");
String lastName = rs1.getString("lastName");
1 String firstName = rs1.getString("firstName");
5 String email = rs1.getString("email");
1 String class = rs1.getString("class");
6 String totalMarks= rs1.getString("total_marks");
1 //store the values which are retrieved using
ResultSet and print them
7 System.out.println(empNum + ","+lastName+
1 ","+firstName+ ","+class number +","+class +","+totalMarks);
8 }
1 }
}
9 catch(SQLException e) {
2 //If any exception occurs,we can catch it and exit the
0 program
2 e.printStackTrace();
}
1 }
2 }
2
2
3
2
4
2
5
2
6
2
7
2
8
2
9
3
0
3
1
3
2
3
3
3
4
3
5

JDBC Driver Types:

JDBC Driver is the key part of the language, which is implemented with defined interfaces in
JDBC API and through which we can interact with the user’s database server. As we know
that JDBC is just a standard specification, so it provides the java.sql.* package for with the
required interfaces and classes without any implementations. The Third-party Drivers are
responsible for performing these implementations. To put it in a simple manner, JDBC
Drivers are responsible for opening the database connections and sending in the SQL queries
and then retrieve the required results with Java.

Types of JDBC Drivers:

1. Type 1: JDBC-ODBC bridge driver

2. Type 2: JDBC Native-API driver
3. Type 3: JDBC-Net pure driver
4. Type 4: JDBC Thin driver or 100% Pure Java driver
So, why do we have these many drivers? Well, the answer is since we have a variety of
operating systems and hardware platforms where Java operates. Sun Microsystems have
categorized the driver implementations into the above four categories.

1. Type 1: JDBC-ODBC bridge driver:

A Type 1 JDBC driver translates the JDBC interface calls to ODBC calls. In this type, the
JDBC bridge is used to access the ODBC drivers that are installed on the Client machine.
There is a requirement that if we are using ODBC, we need to configure the Data source
name (i.e., name of the target database). This driver was proved useful in the early times as
most of the databases only supported the ODBC access. But in recent times, this type of
driver is only recommended for experimental purposes. As per the JDK8 specification, it will
be removed as it was considered as only the traditional solution. Oracle recommends us to
use the JDBC drivers provided by the vendors of the database only instead of this JDBC-
ODBC bridge.

Sun microsystems provides us the JDBC-ODBC bridge driver in the

sun.jdbc.odbc.JdbcOdbcDriver and this driver is close-source, that is it uses the native code
language rather than Java programming language.
Advantages:

1. This type of driver is extremely easy-to-use.

2. It can be easily connected to any of the databases.

Disadvantages:

1. The performance can be degraded because of the JDBC-ODBC translation of function

calls.
2. Explicitly the ODBC driver needs to be installed on the client machine. This driver is
platform dependent as it makes use of ODBC and requires the use of native libraries
on which the JVM relies upon.
3. Type 2: JDBC Native-API driver

The Type 2 JDBC Native-API driver translates the JDBC method calls to the Native calls of
the database Native-API. The Type 2 driver is similar to the JDBC-ODBC bridge driver
except that the ODBC driver is replaced with the Native API calls (C/C++ etc.). These API
calls are unique to the database. It is made sure that the native code part is targeted for the
specific database product. That is, it uses the client-side libraries of the database product.
This architecture eliminated the need for the ODBC driver configuration on the client
machine, and the native libraries that are to be used by the database are shipped along by the
vendors. This practice was quickly adopted by the DB vendors because of the
inexpensiveness and quickness of the reusability of drivers.
Advantages:

1. The Type 2 driver performance is high as it eliminates the ODBC overhead.

Disadvantages:

1. This driver also needs to be installed on the client-machine.

2. The vendor-client library provided by the DB vendors needs to be installed on each
client’s machine.
3. Any change in the database, we must change the Native-API as it is database specific
now.
3. Type 3: JDBC-Net Pure Java Driver

The Type 3 JDBC-Net Pure Java driver is also called a Middleware translation driver as it
uses the three-tier approach. In this, Java sends the JDBC interface calls to an intermediate
server. The intermediate server then connects to the database instead of the JDBC driver. In
this sort of driver, the JDBC clients use quality network sockets for communication with the
middleware application server. This type of communication in which the
intermediate/middleware is being used is also called a ‘PROXY’ server communication.

The Type 3 driver proved to be the 100% Java solution, but it does not gain much traction
then.

Advantages:

1. There is no requirement for any client-side library to be installed on the client

machine.

Disadvantages:
1. Network support requirement on the client machine.
2. Database-specific coding needs to be done in the intermediate/middle server.
3. Maintenance is expensive as we use the database-specific coding and the performance
also remains the same.
4. Type 4: JDBC Thin driver or 100% Pure Java driver

The Type 4 100% Pure Java Driver is also called Direct to database Pure Java Driver.
It translates the JDBC calls directly to the vendor-specific database calls. These
drivers are implemented specifically to a target database and are the most widely used
drivers now. These are written completely in Java and are platform-independent. The
portability is achieved as these drivers are part of the JVM. When compared to the
Type 3 drivers, it does not require any additional (intermediate server) layer to work
upon. The architecture is composed of all the logic of the direct database
communication within a single driver encapsulated in the JDBC API implementation.
This approach streamlines the easy development and deployment with only a single-
tier 100% pure Java package.

Advantages:

1. The Type 4 driver is too flexible due to its portable nature.

2. It can also be downloaded dynamically.
3. This driver has the highest performance when aligned with the other types of drivers
above.

Disadvantages:
1. The Type 4 drivers are proprietary in nature aligned to their specific network
protocols.
2. These are again the database-specific drivers.

Driver Use cases:

As we have four types of drivers with their own merits and demerits, questions do arise like
which type of drivers should I prefer/use for my Java applications. No problem, we will let
you know which driver can be used based on your purpose.

• Type 4 drivers can be used if you are preferring to use Oracle, Sybase, IBM
databases. This is suggested when you are using only one type of database in your
application.
• Type 3 drivers can be used if your Java application accesses multiple databases at the
same time.
• Type 2 drivers can be used in situations where the above two drivers Type 4, and
Type 3 drivers are not yet available.
• Type 1 driver as already mentioned as a not widely used driver. It can be used as a
traditional solution only for Development and Testing purposes.

JDBC Connections:
The JDBC Database Connections come into picture after the installation of the JDBC Driver
in the system.

The programming involved in establishing the JDBC Connection is simple and comprises
five steps depicted below.

1. Import the Packages

2. Load the Driver
3. Establish the Connection
4. Create and Execute the Statement
5. Close the Connection

Now we will dive into these five steps now,

• Import the Packages:

Firstly, we should import the existing packages in our Java program/application. These
import statements help the Java Compiler where to find the classes you reference in the code.
By this, we can make sure that the JDBC API classes, sub-classes are available in the
program. Irrespective of JDBC Driver being installed, we import the below package, which is
the standard JDBC package that allows us to perform CRUD operations in our code.

import java.sql.*

We can import other classes based on the functionality we use in our Java program.

The JDBC API 4.0 mainly provides us with two standard java packages.
• java.sql.*

This package contains most of the standard JDBC functions used for Creating, Executing the
queries etc.,

Classes/ Interfaces Description

BLOB It represents the SQL BLOB value in the Java program
CallableStatement It is used to execute the SQL Stored Procedures
CLOB It represents the SQL CLOB value in the Java program
Connection It creates a connection with a specific database
Date It provides support for Date SQL type
Driver It creates an instance of a Driver with DriverManager
DriverManager It provides the service to manage the set of Drivers
It is an Object which is used to get the types and properties of each
ParameterMetaData
parameter used in the PreparedStatement
ResultSet It is used to retrieve the result row by row
It is used to get the details about the types and properties of the
ResultSetMetaData
ResultSet Object
RowId It specifies the SQL Row id value
SavePoint It is the save point in the SQL transaction
SQLData It is used to map the SQL type to the class in the Java program
SQLXML It represents the SQL XML type
Statement It is used to execute the static SQL statement
DriverPropertyInfo It specifies the Driver properties to make a connection
SQLException It provides the database error information
Sub-class of SQLExceptionand is thrown when the timeout specified
SQLTimeoutException
is expired by the Statement
This exception provides information on the database access
SQLWarning
warnings
It is a Standard mapping of the Java program for SQL structured
Struct
type

• javax.sql.*

This package is a JDBC extension API which provides server-side data access and processing
in Java applications.

Classes/ Interfaces Description

It is an interface which define the common methods between
CommonDataSource
DataSource, XADataSource and ConnectionPoolDataSource
ConnectionPoolDataSource It is a factory of PooledConnection objects
DataSource It is a factory of Connections to a physical datasource
PooledConnection Used to manage the Connection pool
Provides support to the JDBC API for Java Components bean
RowSet
model
It specifies the information about the columns in the RowSet
RowSetMetaData
object
Provides the details about the occurrence about the connection-
ConnectionEvent
related events
ConnectionEventListener Used to register the PooledConnection object events
RowSetEvent It generates when an event occurs to the RowSet object
It is sent to all the StatementEventListeners which are registered
StaementEvent
when PooledConnection is generated

• Load the Driver:

In order to make a connection to the database, we need to load/register the driver. We need to
register it only once per database in the program. To put it in a simple way,
registering/Loading the Driver means the process by which the Driver class file gets loaded
into the memory and hence, we can utilize it as a form of an implementation of the JDBC
interfaces. The need of registration is only once and the ways of registering it are two. It
entirely depends on the user to choose among the two.

• Class.forName()

This is the most common approach to register the driver. By using this, the Driver class can
be implicitly loaded at runtime into the memory. While Loading itself the Driver gets
registered automatically. The fact that it is most preferable is that it permits the user to make
the driver registration configurable and portable.

Note: forName() is only suitable for JDK Compliant Virtual Machines.

Here is an example,

try { Class.forName(“com.mysql.jdbc.Driver”);} catch (Exception e) {

System.out.println(“Unable to load the Driver class!”); System.exit(1);}

Below are the Driver names for various Databases,

DataBase Name JDBC driver Name

MySQL com.mysql.jdbc.Driver
Oracle oracle.jdbc.driver.OracleDriver
Microsoft SQL Server com.microsoft.sqlserver.jdbc.SQLServerDriver
MS Access net.ucanaccess.jdbc.UcanaccessDriver
PostgreSQL org.postgresql.Driver
SyBase com.sybase.jdbcSybDriver

• DriverManager.registerDriver()
This approach provides the users to register the drivers using the in-built class
DriverManager available in java.sql package. This acts as a mediator between the Java
Application and the target database.

public static void registerDriver(driver) – This method loads the Driver class of the
database and establishes the connection with the DB. If the driver is already registered, it will
not take any action. Two exceptions are thrown by this method.

i. SQLException: When the database error occurs.

ii. NullpointerException: If the Driver is not available or not set to the classpath.

Here is an example,

try { Driver driver = new com.mysql.jdbc.Driver; DriverManager.registerDriver(driver);}

catch (ClassNotFoundException e) { System.out.println(“Unable to load the Driver class!”);
System.exit(1);}

• Establish the Connection:

After the Driver has been loaded, the Connection should be created and established. This step
needs to be performed once the required packages are imported and the Drivers are loaded
and registered only. The getConnection() method in DriverManager class is used to get the
connection with Database. The getConnection() method is an overloaded method.

1. getConnection(url);
2. getConnection(url, username, password);

Let’s explore the Connection interface.

Connection Interface:

• A Connection is nothing but a session between Java application and the Database.
• Its available in the java.sql package.
• It is an interface with a factory of Statement, PreparedStatement, MetaData etc.,
• It also provides methods related to the Transaction Management like commit(),
rollback() etc.,

Let’s also explore the DriverManager class.

Driver Manager class:

• The Driver Manager class acts as an interface between the user and the drivers.
• It keeps track of all the set of drivers and maintains the registered drivers and also
handles the establishment of connection between the database and the driver.

Here is an example,
Connection con = DriverManager.getConnection(jdbc:thin:@localhost:1521:xe, System,
pass@123);

• thin refers to the Driver type (oracle, mysql etc.,).

• localhost is where the Oracle database is running.
• 1521 – port number to connect to DB.
• xe – SID (optional)
• system – Username to connect to the Oracle Database.
• pass@123 – Password

The below table lists the connection URL for various databases,

Database Connection string/ DB URL

MySQL jdbc:mysql://hostname/databaseName
Oracle jdbc:oracle:thin:@hostname:portNumber:databaseName
Microsoft SQL Server jdbc:db2:hostname:port Number/databaseName
PostgreSQL jdbc:sybase:Tds:hostname: port Number/databaseName

Note that the Database URL is an address that usually points to the user’s database.

• Create and Execute the Statement:

After the establishment of the database connection, now user can interact with the database.
For this, we need to create a Statement to perform the SQL query and then execute the
statement.

• Creating a Statement:

We use the methods from Connection class to create the queries. As part of the java.sql
package we have three statement interfaces available.

• Statement

It is used to implement simple statements with no parameters and return the ResultSet object.

Statement statement1 = conn.createStatement();

• Prepared Statement

This is extended from the Statement interface with extra features of using the parameterized
statements and pre-compiled SQL queries. The performance of this is high as it pre-compiles
the queries before-hand.

String query = “select * from employees emp where emp.id =1”;PreparedStatement

statement2 = conn.prepareStatement(query);

• Callable Statement
This is extended from the PreparedStatement interface with extra features of implementing
the parameterized SQL statements to invoke and execute the Stored procedures or functions
in the database.

CallableStatement statement3 = conn.prepareCall(“call procedures(?,?)”);

Note: We will investigate these Statements in-depth going further.

• Executing the Statement:

The query developed can be executed for updating/inserting in the database and retrieving the
data. There are four important methods for executing the SQL query.

• ResultSetexecuteQuery (String sql)

This is the method in the Statement interface and is used to retrieve the results from the
database. It is similar to the SELECT query in SQL.

• int executeUpdate (String sql)

This is used for specified operations like INSERT, UPDATE, DELETE (DML statements)
and DDL statements that return nothing. It is mostly used to insert and update the record
entries in the database.

• boolean execute (String sql)

This is used to execute the SQL query. It returns TRUE if it is a SELECT query. It returns
FALSE if t s UPDATE or INSERT query.

• int[] executeBatch ()

This method is used to execute the batch of SQL queries. If all the queries are successful
then, it returns the array of update counts. It is mostly used to insert or update the records in
bulk.

• Retrieving the Results:

The executeQuery() method in the Statement interface will return the results in the form of a
ResultSet Object. The returned object will never be NULL even if there are no matching
records in the database.

ResultSetrs = statement1.executeQuery(QUERY);

This method is only used for SELECT query. If any INSERT/UPDATE query is given as
input, then it throws the SQLException saying that ‘this method cannot be used for
UPDATE’. ResultSet is an Enumerator Object.

• Close the Connection:

Finally, after performing various operations and manipulations on the database. Now, we
need to close the connection. The resources opened should be closed as we may end-up
getting out of connection exceptions and errors at a later point of time. If we close the
connection object, then the Statement and ResultSet objects are going to be automatically
closed.

conn.close();

After Java 7, the JDBC connections can be automatically closed if we use the try-catch block.
This feature is called Automatic resource management.

try (Connection conn = DriverManager.getConnection(url, user, password)){ //database

connection and operation}

JDBC Statements:
JDBC Statements are used to execute the SQL queries. The JDBC API provides us with three
types of statements.

1. Statement interface

• The Statement interface is used in-order to get the general-purpose access to the target
database.
• It is proven as useful when we have static SQL queries which needs to be run at run
time.
• No parameters can be sent in to retrieve the results based on the conditions.

Here is an example,

Statement statement1 = null;

try {

statement1 = conn.createStatement();

} catch (SQLExcetion e) {

} finally {

statement1.close();

• After the creation of the statement object the user needs to execute it. We can use any
of the three below methods to do that operation.
o booleanexecute(String sql)
o int execcuteUpdate(String sql)
o ResultSetexecuteQuery(String sql)

2. Prepared Statement interface

o This interface is used if the user plans to use the SQL queries and execute
them several times.
o The main difference is that the PreparedStatement accepts the parameters at
rum time.
o Generally, all the parameters in JDBC are denoted by ‘?’symbol. This symbol
is known as PARAMETER MARKER/ REPLACEMENT OPERATOR/
PLACE RESOLUTION OPERATOR. These values should be binded before
the execution of the SQL statement.
o In-order to bind the values together with the parameters, we use setXXX()
method is used, where XXX is the Java Data type of the value that the user
wishes to bind.
o Please remember that if the user did not send any values to the parameters then
SQ Exception will occur.
o Each of the parameter markers is referred to by the ordinal positions. For
example, the first marker is position 1 and so on.
o All of the Statement object’s methods are executed by execute(),
executeUpdate() and executeQuery() methods only.
o Prepared Statement Object uses only IN statements.

Here is an example,

PreparedStatementpStatement = null;

try {

String SQLStatement = “SELECT * FROM STUDENT_DETAILS sd WHERE

sd.studentNum=1”;

pStatement = conn.prepapredStatement(SQLStatement);

} catch (SQLException e) {

} finally {

conn.close();

3. Callable Statement interface

o This interface is used whenever the user wants to access the Database
Procedures and Functions.
o This is also known for accepting the input parameters at the run-time.
o Unlike as PreparedStatemet, Callable Statement uses the IN, OUT and INOUT
Parameters.
Parameter Description
A parameter with value is not known while the SQL statement is being created.
IN The programmersare responsible to bind values to IN parameters with the
setXXX() methods.
A parameter whose value is being supplied by the SQL statement that it returns.
OUT The programmer generally retrieve values from theOUT parameters with the
getXXX() methods.
A parameter thatdelivers both input and the output values. The
INOUT variablesbasically bind with the setXXX() methods and retrieve values with the
getXXX() methods.

Here is an example,

CallableStatementcStatement = null;

try {

String SQLStatement = “call {MY_PROCEDURE(?, ?)}”;

cStatement = conn.prepareCall(SQLStatement);

} catch (SQLException e) {

} finally {

conn.close();

• The Callable Statement with only IN parameters behaves the same as the
PreparedStaement only.
• When the SQL statement has either OUT or INOUT parameters, it is mandatory that
we need to employ the registerOutParameter() method.
• The registerOutParamter() is generally used to bind the JDBC data type to the data
type which the Stored Procedure is expected to return.

The relationship between these three statements is,

The above three statements are part of the java.sql package.

Note: Ensure proper clean-up of the resources if you are using the Java versions below 7.
After version 7, Java try with resources block ensures that the resources opened are
automatically closed when not used.
XML Basics
XML stands for Extensible Markup Language and is a text-based markup language
derived from Standard Generalized Markup Language (SGML).

XML is a software- and hardware-independent tool for storing and transporting data.

  XML is a markup language much like HTML

  XML was designed to store and transport data
  XML was designed to be self-descriptive
 XML is a W3C Recommendation

XML tags identify the data and are used to store and organize the data, rather than
specifying how to display it like HTML tags, which are used to display the data. XML
is not going to replace HTML in the near future, but it introduces new possibilities by
adopting many successful features of HTML.

There are three important characteristics of XML that make it useful in a variety of
systems and solutions:

 XML is extensible: XML allows you to create your own self-descriptive tags, or
language, that suits your application.

 XML carries the data, does not present it: XML allows you to store the data
irrespective of how it will be presented.

 XML is a public standard: XML was developed by an organization called the
World Wide Web Consortium (W3C) and is available as an open standard.

XML Usage

A short list of XML usage says it all:

 XML can work behind the scene to simplify the creation of HTML documents for
 large web sites.
 XML can be used to exchange the information between organizations and
 systems.
  XML can be used for offloading and reloading of databases.
 XML can be used to store and arrange the data, which can customize your data
 handling needs.
 XML can easily be merged with style sheets to create almost any desired
output.
 Virtually, any type of data can be expressed as an XML document.

1
The Difference between XML and HTML

XML and HTML were designed with different goals:

  XML was designed to carry data - with focus on what data is
  HTML was designed to display data - with focus on how data looks
  XML tags are not predefined like HTML tags
 XML documents form a tree structure that starts at "the root" and branches to
"the leaves".

XML Syntax:

Following is a complete XML document:

<?xml version="1.0"?>
<contact_info>
<name>Rajesh</name>
<company>TCS</company>
<phone>9333332354</phone>
</contact_info>

You can notice there are two kinds of information in the above example:

 markup, like <contact-info> and


 the text, like Rajesh etc.

The following diagram depicts the syntax rules to write different types of markup and
text in an XML document.

2
)

Let us see each component of the above diagram in detail:

XML Declaration
The XML document can optionally have an XML declaration. It is written as below:

<?xml version="1.0" encoding="UTF-8"?>

Where version is the XML version and encoding specifies the character encoding used
in the document.

Syntax Rules for XML declaration

 The XML declaration is case sensitive and must begin with "<?xml>" where
"xml" is written in lower-case.

 If document contains XML declaration, then it strictly needs to be the first
statement of the XML document.

 The XML declaration strictly needs be the first statement in the XML document.

 An HTTP protocol can override the value of encoding that you put in the XML
declaration.

Tags and Elements

An XML file is structured by several XML-elements, also called XML-nodes or XML-

tags. XML-elements' names are enclosed by triangular brackets < > as shown below:

<element>

Syntax Rules for Tags and Elements

Element Syntax: Each XML-element needs to be closed either with start or with end
elements as shown below:

<element>....</element>

or in simple-cases, just this way:

<element/>

Nesting of elements:

An XML-element can contain multiple XML-elements as its children, but the children
elements must not overlap. i.e., an end tag of an element must have the same name
as that of the most recent unmatched start tag.

Following example shows incorrect nested tags:

3
<?xml version="1.0"?>
<contact_info>
<company>TCS
<contact_info>
</company>

Following example shows correct nested tags:

<?xml version="1.0"?>
<contact_info>
<company>TCS</company>
<contact_info>

Root element:

An XML document can have only one root element. For example, following is not a
correct XML document, because both the x and y elements occur at the top level
without a root element:

<x>...</x>
<y>...</y>

The following example shows a correctly formed XML document:

<root>
<x>...</x>
<y>...</y>
</root>

Case sensitivity:

The names of XML-elements are case-sensitive. That means the name of the start
and the end elements need to be exactly in the same case.

For example <contact_info> is different from <Contact_Info>.

Attributes
An attribute specifies a single property for the element, using a name/value pair.
An XML-element can have one or more attributes. For example:

<a href="http://www.tutorialspoint.com/">Tutorialspoint!</a>

Here href is the attribute name and http://www.tutorialspoint.com/ is attribute

value.

4
Syntax Rules for XML Attributes

Attribute names in XML (unlike HTML) are case sensitive. That is, HREF and href are
considered two different XML attributes.

Same attribute cannot have two values in a syntax. The following example shows
incorrect syntax because the attribute b is specified twice:
<a b="x" c="y" b="z">....</a>

Attribute names are defined without quotation marks, whereas attribute values must
always appear in quotation marks. Following example demonstrates incorrect xml
syntax:
<a b=x>....</a>

In the above syntax, the attribute value is not defined in quotation marks.

XML References
References usually allow you to add or include additional text or markup in an XML
document. References always begin with the symbol "&" ,which is a reserved
character and end with the symbol ";". XML has two types of references:

Entity References: An entity reference contains a name between the start and the
end delimiters. For example &amp; where amp is name. The name refers to a
predefined string of text and/or markup.

Character References: These contain references, such as &#65;, contains a hash

mark (“#”) followed by a number. The number always refers to the Unicode code of
a character. In this case, 65 refers to alphabet "A".

XML Text

 The names of XML-elements and XML-attributes are case-sensitive, which

means the name of start and end elements need to be written in the same
case.

 To avoid character encoding problems, all XML files should be saved as Unicode
UTF-8 or UTF-16 files.

 Whitespace characters like blanks, tabs and line-breaks between XML-elements
and between the XML-attributes will be ignored.

 Some characters are reserved by the XML syntax itself. Hence, they cannot be
used directly. To use them, some replacement-entities are used, which are
listed below:

5
not allowed character replacement-entity character description

< &lt; less than

> &gt; greater than

& &amp; ampersand

' &apos; apostrophe

" &quot; quotation mark

XML Tree Structure:

6
An Example XML Document

The image above represents books in this XML:

--------------------------------------------------------------------------

<?xml version="1.0" encoding="UTF-8"?>

<bookstore>
<book category="cooking">
<title lang="en">Everyday Italian</title>
<author>Giada De Laurentiis</author>
<year>2005</year>
<price>30.00</price>
</book>
<book category="children">
<title lang="en">Harry Potter</title>
<author>J K. Rowling</author>
<year>2005</year>
<price>29.99</price>
</book>
<book category="web">
<title lang="en">Learning XML</title>
<author>Erik T. Ray</author>
<year>2003</year>
<price>39.95</price>
</book>
</bookstore>

---------------------------------------------------------------

XML documents are formed as element trees.

An XML tree starts at a root element and branches from the root to child elements.

All elements can have sub elements (child elements):

<root>
<child>
<subchild>.....</subchild>
</child>
</root>

The terms parent, child, and sibling are used to describe the relationships between
elements.

Parents have children. Children have parents. Siblings are children on the same
level (brothers and sisters).

7
XML Namespaces
Name Conflicts

In XML, element names are defined by the developer. This often results in a conflict when
trying to mix XML documents from different XML applications.

This XML carries HTML table information:

<table>
<tr>
<td>Apples</td>
<td>Bananas</td>
</tr>
</table>

This XML carries information about a table (a piece of furniture):

<table>
<name>African Coffee Table</name>
<width>80</width>
<length>120</length>
</table>

If these XML fragments were added together, there would be a name conflict. Both
contain a <table> element, but the elements have different content and meaning.

A user or an XML application will not know how to handle these differences.

Solving the Name Conflict Using a Prefix

Name conflicts in XML can easily be avoided using a name prefix.

This XML carries information about an HTML table, and a piece of furniture:

<h:table>
<h:tr>
<h:td>Apples</h:td>
<h:td>Bananas</h:td>
</h:tr>
</h:table>

<f:table>
<f:name>African Coffee Table</f:name>
<f:width>80</f:width>
<f:length>120</f:length>
</f:table>

In the example above, there will be no conflict because the two <table> elements have
different names.

8
XML Namespaces - The xmlns Attribute

When using prefixes in XML, a namespace for the prefix must be defined.

The namespace can be defined by an xmlns attribute in the start tag of an element.

The namespace declaration has the following syntax. xmlns:prefix="URI".

-----------------------------------------------------

<root>
<h:table xmlns:h="http://www.w3.org/TR/html4/">
<h:tr>
<h:td>Apples</h:td>
<h:td>Bananas</h:td>
</h:tr>
</h:table>

<f:table xmlns:f="https://www.w3schools.com/furniture">
<f:name>African Coffee Table</f:name>
<f:width>80</f:width>
<f:length>120</f:length>
</f:table>
</root>

--------------------------------------------------

In the example above:

The xmlns attribute in the first <table> element gives the h: prefix a qualified
namespace.

The xmlns attribute in the second <table> element gives the f: prefix a qualified
namespace.

When a namespace is defined for an element, all child elements with the same prefix are
associated with the same namespace.

9
XML Validator

Use our XML validator to syntax-check your XML.

Well Formed XML Documents

An XML document with correct syntax is called "Well Formed".

The syntax rules were described in the previous chapters:

 XML documents must have a root element

  XML elements must have a closing tag
  XML tags are case sensitive
  XML elements must be properly nested
 XML attribute values must be quoted

Example 1:

<?xml version=”1.0”?>

<book>
<title>Java</Title>
<author>James</book>
<pirce>570
</author>

The above XML document is not a well formed document. Reasons given below...
  tags are not matching <title> … </Title>
  There is no proper nesting <author>….</book>
 Tag doesn’t closed <price>

Example 2:

<?xml version=”1.0”?>
<book>
<title>Java</title>
<author>James</author>
<price>500</price>
</book>

The above XML document is a well formed document.

Valid XML Documents

A "well formed" XML document is not the same as a "valid" XML document.

A "valid" XML document must be well formed. In addition, it must conform to a document
type definition.

10
There are two different document type definitions that can be used with XML:

 DTD - The original Document Type Definition

 XML Schema - An XML-based alternative to DTD

A document type definition defines the rules and the legal elements and attributes for an
XML document.

XML DTD: (Document Type Definition)

 An XML document with correct syntax is called "Well Formed".


 An XML document validated against a DTD is both "Well Formed" and "Valid".

 A "Valid" XML document is a "Well Formed" XML document, which also conforms to
the rules of a DTD.

 DTD is the basic building block of XML.

 The purpose of a DTD is to define the structure of an XML document. It defines the
structure with a list of legal elements.

--------------------------------------------------------------------------

<!DOCTYPE book
[
<!ELEMENT book
(title,author,price)> <!ELEMENT
title (#PCDATA)> <!ELEMENT author
(#PCDATA)> <!ELEMENT price
(#PCDATA)> ]>

-------------------------------------------------------------

The DTD above is interpreted like this:

  !DOCTYPE book defines that the root element of the document is book 
  !ELEMENT book defines that the book element must contain the elements:
 "title, author, price”
  !ELEMENT title defines the title element to be of type "#PCDATA"
  !ELEMENT author defines the author element to be of type "#PCDATA"
 !ELEMENT price defines the price element to be of type "#PCDATA"

Note: PCDATA: Parse able Character Data, CDATA: Character Data.

There are two types of DTDs:

1) Internal / Embedded DTD.

2) External DTD.

11
1) Internal / Embedded DTD.

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE student [
<!ELEMENT student (id,name,age,addr,email,ph)>
<!ELEMENT id (#PCDATA)>
<!ELEMENT name (#PCDATA)>
<!ELEMENT age (#PCDATA)>
<!ELEMENT addr (#PCDATA)>
<!ELEMENT email (#PCDATA)>
<!ELEMENT ph (#PCDATA)> ]>

<student>
<id>543</id>
<name>Ravi</name>
<age>21</age>
<addr>Guntur</addr>
<email>[email protected]</email>
<ph>9855555</ph>
<gender>male</gender>
</student>

2) External DTD.

<!ELEMENT student (id,name,age,addr,email)>

<!ELEMENT id (#PCDATA)>
<!ELEMENT name (#PCDATA)>
<!ELEMENT age (#PCDATA)>
<!ELEMENT addr (#PCDATA)>
<!ELEMENT email (#PCDATA)>

Save the above code as “student.dtd” and prepare “student.xml” as follows...

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE student SYSTEM "student.dtd">
<student>
<id>543</id>
<name>Ravi</name>
<age>21</age>
<addr>Guntur</addr>
<email>[email protected]</email>
</student>

In the above example we are using <!DOCTYPE student SYSTEM "student.dtd">

which is used to provide “student.dtd” code in our “student.xml” file.

If the above xml code follows the exact rules defined in DTD then we can conclude
that our xml document is a valid document. Otherwise it is an invalid document.

12
When to Use a DTD/Schema?

 With a DTD, independent groups of people can agree to use a standard DTD for
interchanging data.

 With a DTD, you can verify that the data you receive from the outside world is valid.

 You can also use a DTD to verify your own data.

XML Schema

An XML Schema describes the structure of an XML document, just like a DTD.

An XML document with correct syntax is called "Well Formed".

An XML document validated against an XML Schema is both "Well Formed" and
"Valid".

XML Schema is commonly known as XML Schema Definition (XSD). It is used to

describe and validate the structure and the content of XML data. XML schema defines
the elements, attributes and data types. Schema element supports Namespaces. It is
similar to a database schema that describes the data in a database.

XML Schema is an XML-based alternative to DTD:

Syntax
You need to declare a schema in your XML document as follows:

<xs:schema>

<xs:element name="book">
<xs:complexType>
<xs:sequence>
<xs:element name="title" type="xs:string"/>
<xs:element name="author" type="xs:string"/>
<xs:element name="price" type="xs:integer"/>
<xs:element name="edition" type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>

The Schema above is interpreted like this:

 <xs:element name="book"> defines the element called "book" is a root.

 <xs:complexType> the "book" element is a complex type i.e. root
 <xs:sequence> the complex type is a sequence of elements i.e. childrens
 <xs:element name="title" type="xs:string"> the element "title" is of type string (text)
 <xs:element name="author" type="xs:string"> the element "author" is of type string 
 <xs:element name="price" type="xs:integer"> the element "price" is of type integer. 
 <xs:element name="edition" type="xs:string"> the element "edition" is of type string

13
Example:

---------------------------------------------------------------------------------------------
<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
targetNamespace="http://www.w3schools.com" elementFormDefault="qualified">
<xs:element name="student">
<xs:complexType>
<xs:sequence>
<xs:element name="name" type="xs:string"/>
<xs:element name="age" type="xs:integer"/>
<xs:element name="addr">
<xs:complexType>
<xs:sequence>
<xs:element name="city" type="xs:string"/>
<xs:element name="pincode" type="xs:long"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="ph" type="xs:integer"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
-----------------------------------------------------------------------------------------------------

Save the above code as “student.xsd”

Prepare “student.xml” as follows…

-----------------------------------------------------------------------

<?xml version="1.0" encoding="UTF-8"?>

<student xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.w3schools.com student.xsd">
<name>rajesh</name>
<age>25</age>
<addr>
<city>mylavaram</city>
<pincode>53333</pincode>
</add>
<ph>9343434</ph>
</student>
--------------------------------------------------------------------------------------------------------

If the above xml code follows the exact rules defined in “student.xsd” then we can
conclude that our xml document is a valid document. Otherwise it is an invalid
document.

14
XSLT Introduction

XSL (EXtensible Stylesheet Language) is a styling language for XML.

XSLT stands for XSL Transformations. CSS = Style Sheets for HTML

HTML uses predefined tags. The meaning of, and how to display each tag is well
understood.

CSS is used to add styles to HTML elements.

XSL = Style Sheets for XML

XML does not use predefined tags, and therefore the meaning of each tag is not well
understood.

A <table> element could indicate an HTML table, a piece of furniture, or something else -
and browsers do not know how to display it!

So, XSL describes how the XML elements should be displayed.

What is XSLT?

 XSLT stands for XSL Transformations


 XSLT is the most important part of XSL

 XSLT transforms an XML document into another XML document

 XSLT uses XPath to navigate in XML documents

 XSLT is a W3C Recommendation

Example:

<?xml version="1.0" encoding="UTF-8"?>

<?xml-stylesheet type="text/xsl" href="book.xsl"?>

<book_store>
<book>
<title>JAVA</title>
<author>James</author>
</book>
<book>
<title>DBMS</title>
<author>Raghu</author>

</book>
</book_store>

Save the above code as “book.xml” and prepare the style sheet for this xml file.

15
<?xml version="1.0" encoding="UTF-8"?> <xsl:stylesheet
version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/">
<html>
<body>
<h2>Book Details</h2>
<table border="1">
<tr>
<th>Title</th>
<th>Author</th>
</tr>
<xsl:for-each select="book_store/book">
<tr>
<td><xsl:value-of select="title"/></td>
<td><xsl:value-of select="author"/></td>
</tr>
</xsl:for-each>
</table>
</body>
</html>
</xsl:template>
</xsl:stylesheet>

Save the above file as “book.xsl”

Now open “book.xml” file through any browser, observe the output as given below

Title Author
Java James
DBMS Raghu

XML DOM Parser

The DOM defines a standard for accessing and manipulating documents:

The HTML DOM defines a standard way for accessing and manipulating HTML documents.
It presents an HTML document as a tree-structure.

The XML DOM defines a standard way for accessing and manipulating XML documents. It
presents an XML document as a tree-structure.

The HTML DOM

All HTML elements can be accessed through the HTML DOM.

This example changes the value of an HTML element with id="demo":

16
Example:
<!DOCTYPE html>
<html>
<body>

<h1 id="demo">This is a Heading</h1>

<button type="button"
onclick="document.getElementById('demo').innerHTML = 'Hello World!'">Click Me!
</button>

</body>
</html>

The XML DOM

All XML elements can be accessed through the XML DOM.

The XML DOM is:

 A standard object model for XML

 A standard programming interface for XML
  Platform- and language-independent
 A W3C standard

In other words: The XML DOM is a standard for how to get, change, add, or
delete XML elements.

Programming Interface

The DOM models XML as a set of node objects. The nodes can be accessed with
JavaScript or other programming languages.

The programming interface to the DOM is defined by a set standard properties and
methods.

Properties are often referred to as something that is (i.e. nodename is "book").

Methods are often referred to as something that is done (i.e. delete "book").

XML DOM Properties

These are some typical DOM properties:

  x.nodeName - the name of x

 x.nodeValue - the value of x
 x.parentNode - the parent node of x
  x.childNodes - the child nodes of x
 x.attributes - the attributes nodes of x

Note: In the list above, x is a node object.

17
XML DOM Methods

  x.getElementsByTagName(name) - get all elements with a specified tag name

  x.appendChild(node) - insert a child node to x
 x.removeChild(node) - remove a child node from x

Note: In the list above, x is a node object.

DOM Example:

<html>
<body>

<p id="demo">this is paragraph text</p>

<button type="button" onclick="myfun()">click me</button>

<script>
function myfun()
{
var text, parser, xmlDoc;

text = "<bookstore><book>" +
"<title>Java</title>" +
"<author>James</author>" +
"<year>1991</year>" +
"<book>" +
"<title>DBMS</title>" +
"<author>Raghu</author>" +
"<year>1970</year>" +
"</book>"+
"</book></bookstore>";

parser = new DOMParser();

xmlDoc = parser.parseFromString(text,"text/xml");

document.getElementById("demo").innerHTML =
xmlDoc.getElementsByTagName("year")[0].childNodes[0].nodeValue;
}
</script>
</body>
</html>
Output:
1991

Example Explained

  xmlDoc - the XML DOM object created by the parser.

  getElementsByTagName("year")[0] - get the first <year> element
  childNodes[0] - the first child of the <year> element (the text node)
 nodeValue - the value of the node (the text itself)

18