45:JDBC

Learning Objectives
After completing this session, you will be able to:
‰  Define JDBC
‰  Apply JDBC API step by step
JDBC
JDBC is a standard Java API for accessing relational database and hides database specific details
from application.
JDBC is a part of Java SE (J2SE). Java SE 6 has JDBC 4.
JDBC API
‰  The JDBC API is a Java API for accessing virtually any kind of tabular data.
‰  The JDBC API consists of a set of classes and interfaces written in the Java
programming language that provide a standard API for tool/database developers and
makes it possible to write industrial-strengthdatabase applications entirely in the Java
programming language.
‰  The Java programming language, being robust, secure, easy to use, easy to
understand, and automatically downloadable on a network, is an excellent language
basis for database applications.
‰  What is needed is a way for Java applications to talk to a variety of different data
sources. The JDBC API provides the mechanism for doing this.
‰  Majority of JDBC API is located in java.sql package, which are DriverManager,
Connection, ResultSet, DatabaseMetaData, ResultSetMetaData, PreparedStatement,
CallableStatement and Types
‰  The javax.sql package provides many advanced functionalities.
o  Example:DataSource
‰  For example, an alternative tothe DriverManager facility, a DataSource object is the
preferred means of getting a connection.
‰  The DataSource interface is implemented by a driver vendor.
‰  With a basic implementation, the connection obtained through a DataSource object is
identical to a connection obtained through the DriverManager facility.
JDBC Driver
JDBC driver is an implementation of JDBC interfaces that is specific to database. Every database
server has corresponding JDBC drivers.
You can see the list of available drivers from http://industry.java.sun.com/products/jdbc/drivers
JDBC Driver
In simplest terms, a JDBC technology-based driver ("JDBC driver") makes it possible to do three
things:
‰  Establish a connection with a data source
‰  Send queries and update statements to the data source
‰  Process the results
The following code fragment gives a simple example of these three steps:
Connection con = DriverManager.getConnection( "jdbc:myDriver:wombat",
"myLogin", "myPassword");
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM Table1");
while (rs.next()) {
int x = rs.getInt("a");
String s = rs.getString("b");
float f = rs.getFloat("c");
}
Database URL
Database URL is used to make a connection to the database and can contain server, port,
protocol, and so on.
jdbc:subprotocol_name:driver_dependant_databasename: Oracle thin driver
jdbc:oracle:thin:@machinename:1521:dbname:Derby
jdbc:derby://localhost:1527/sample: Pointbase
jdbc:pointbase:server://localhost/sample
Steps of Applying JDBC
The steps of applying JDBC are:
1.Load the JDBC driver that is specific to DB
2.Get a Connection object
3.Get a Statement object
4.Execute queries and/or updates
5.Read results
6.Read Meta-data (optional step)
7.Close Statement and Connection objects
Load DB-Specific Database Driver
Loading the driver or drivers you want to use is very simple and involves just one line of code.
If, for example, you want to use the JDBC–ODBC Bridge driver, the following code will load it:
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Your driver documentation will give you the class name to use.
For instance, if the class name is jdbc.DriverXYZ, you would load the driver with the following line
of code:
Class.forName("jdbc.DriverXYZ");
You do not need to create an instance of a driver and register it with the DriverManager because
calling the method Class.forName will do that for you automatically.
If you were to create your own instance, you would be creating an unnecessary duplicate.
When you have loaded a driver, it is available for making a connection with a DBMS.
Get a Connection Object
DriverManager class is responsible for selecting the database and creating the database
connection.
Using DataSource is a preferred means of getting a Connection object (You will talk about
DataSource in the next session)
Create the database connection:
try {
Connection connection =
DriverManager.getConnection("jdbc:derby://localhost:1527/sample",
“app"," app ");
} catch(SQLException sqle) {
System.out.println("" + sqle);
}
DriverManager and Connection
java.sql.DriverManager: getConnection(String url, String user, String password) throws
SQLException
java.sql.Connection:
Statement createStatement() throws SQLException
void close() throws SQLException
void setAutoCommit(boolean b) throws SQLException
void commit() throws SQLException
void rollback() throws SQLException
Get a Statement Object
Create a Statement object from Connection object:
java.sql.Statement:
ResultSet executeQuery(string sql)
int executeUpdate(String sql)
Example: Statement statement = connection.createStatement();
The same Statement object can be used for many unrelated queries
Executing Query or Update
From the Statement object, the two most used commands are:
QUERY (SELECT) :
ResultSet rs = statement.executeQuery("select * from customer_tbl");
ACTION COMMAND (UPDATE/DELETE) :
int iReturnValue = statement.executeUpdate("update manufacture_tbl set
name = ‘IBM' where mfr_num = 19985678");
Reading Results
Loop through ResultSet retrieving information:
java.sql.ResultSet:
boolean next()
xxx getXxx(int columnNumber)
xxx getXxx(String columnName)
void close()
The iterator is initialized to a position before the first row. You must call next() once to move it to
the first row.
Once you have the ResultSet, you can easily retrieve the data by looping through it
while (rs.next()){
// Wrong this will generate an error
String value0 = rs.getString(0);
// Correct!
String value1 = rs.getString(1);
int value2 = rs.getInt(2);
int value3 = rs.getInt(“ADDR_LN1");
}
When retrieving data from the ResultSet, use the appropriate getXXX() method:
‰  getString()
‰  getInt()
‰  getDouble()
‰  getObject()
There is an appropriate getXXX() method of each java.sql.Types data type.
Read ResultSet MetaData and DatabaseMetaData (Optional)
Once you have the ResultSet or Connection objects, you can obtain the metadata about the
database or the query.
This gives valuable information about the data that you are retrieving or the database that you are
using.
ResultSetMetaData rsMeta = rs.getMetaData();
DatabaseMetaData dbmetadata = connection.getMetaData();
There are approximately 150 methods in the DatabaseMetaData class.
ResultSetMetaData Example
ResultSetMetaData meta = rs.getMetaData();
//Return the column count
int iColumnCount = meta.getColumnCount();
for (int i =1 ; i <= iColumnCount ; i++){
System.out.println(“Column Name: " + meta.getColumnName(i));
System.out.println(“Column Type" + meta.getColumnType(i));
System.out.println("Display Size: " +
meta.getColumnDisplaySize(i) );
System.out.println("Precision: " + meta.getPrecision(i));
System.out.println(“Scale: " + meta.getScale(i) );
}
Tips and Tricks:
Provide some key tips while writing SQL statements and executing it using executeUpdate()
methods.
Solution:
‰  When a connection is created using JDBC, bydefault it is in the auto-commit mode.
‰  This means that each individual SQL statementis treated as a transaction by itself,
and will be committed as soon as its execution is finished.
‰  Notice that for the SQL statements CREATE, INSERT, UPDATE, and DELETE, the
execution of the method executeUpdate() will change the state of the database.
‰  Whenever the SQL statement will not fit on one line on the page, you have to split it
into two strings concatenated by a plus sign (+) so that it will compile.
‰  Pay special attention to the space following “INSERT INTO tablename” to separate it
in the resulting string from “VALUES”.
Summary
Advantages of Java and JDBC technologies:
‰  MIS managers like the combination of the Java platform and JDBC technology
because it makes disseminating information easy and economical.
‰  Businesses can continue to use their installed databases and access information
easily even if it is stored on different database management systems or other data
sources.

Test Your Understanding
1.State true or false for the following:
a)Majority of JDBC API is located in java.sql package.
b)DriverManager class is notresponsible for selecting the database and creating the
database connection.
c)In the ResultSet class, there is an appropriate getXXX() method for each
java.sql.Types data type