Java Programming - JDBC

Java Programming - JDBC
Structure
8.1 Introduction
Objectives
8.2 Database Management
8.3 Mechanism for connecting to a back end database
8.4 Load the ODBC driver
8.5 Summary
8.6 Terminal Questions
8.1 Introduction
The JDBC API can access any kind of tabular data, especially data stored in a Relational Database. It works on top of ODBC which was the driver for database connectivity since age old days but since ODBC was implemented in C so people from the VB background had some problems in understanding the implementation difficulties.Since JDBC works on top of ODBC we have something called as a JDBC-ODBC bridge to access the database.The details would be seen in the topics to follow.JDBC helps you to write java applications that manage mainly three programming activities listed below namely a)connect to a data source, like a database ,sending queries and updating statements to the database and retrieving and processing the results received from the database in answer to your query
Objectives
In this chapter, you will learn about the:
 How to handle database through Java
 Inserting, deleting and modifying of data
Java Programming Unit 8
Sikkim Manipal University Page No. 186
8.2 Database Management
A database is a collection of related information and a Data Base Management System (DBMS) is a software that provides you with a mechanism to retrieve, and data to the database. There are lots of DBMS/RDBMS products available to you, for example, MS-Access, MS-SQL Server, Oracle, Sybase and Ingres. The list is never-ending. Each of these RDBM‟s stores the data in their own format. MS-Access stores the data in .MDB file format whereas MS-SQL Server stores the data in a .DAT file format.
Will the database alone be of any use to your client?
The answer is NO!
It will be a good idea for you to develop a customized application for the client in which the client is given option to retrieve, add, and modify data at the touch of a key.
To accomplish this, you should have a mechanism of making the application understand and work with the file format of the database i.e. .MDB or .DAT files.
8.3 Mechanism for connecting to a back end database
ODBC
ODBC is an abbreviation of Open Database Connectivity, a standard database access method developed by Microsoft Corporation. The goal of ODBC is to make it possible to access any data from any application, regardless of which database management system (DBMS) is handling the data. ODBC manages this by inserted a middle layer, called a driver, between an application and the DBMS. The purpose of this layer is to translate the queries of the application into commands that the DBMS understands. For this to work, both the application and the DBMS must be
Java Programming Unit 8
Sikkim Manipal University Page No. 187
ODBC-compliant-that is, the application must be capable of issuing ODBC
commands and the DBMS must be capable of responding to them.
JDBC
JDBC provides a database-programming interface for Java programs. Since
the ODBC is written in „C‟ language, a Java program cannot directly
communicate with an ODBC driver.
JavaSoft created the JDBC-ODBC Bridge driver that translates the JDBC
API to the ODBC API. It is used with ODBC drivers.
JDBC Driver Manager
The JDBC driver manager is the backbone of the JDBC architecture. The
function of the JDBC driver manager is to connect a Java application to the
appropriate driver.
JDBC-ODBC Bridge
The JDBC-ODBC bridge allows you to use the ODBC driver as JDBC
drivers.
Java Programming Unit 8
Sikkim Manipal University Page No. 188
JDBC Application Architecture
Connection to a Database
The java.sql package contains classes that help in connecting to a database, sending SQL statements to the database, and processing query results.
The Connection Objects
The Connection object represents a connection with a database. You may have several Connection objects in an application that connects to one or more databases.
Loading the JDBC-ODBC Bridge and Establishing Connection
To establish a connection with a database, you need to register the ODBC-JDBC Driver by calling the forName () method from the Class class and then calling the getConnection () method from the DriverManager class.
The getConnection () method of the DrverManager class attempts to locate the driver that can connect to the database represented by the JDBC URL passed to the getConnection () method.
The JDBC URL
The JDBC URL is a string that provides a way of identifying a database. A JDBC URL is divided into three parts:
::
  in a JDBC URL is always jdbc.
  is the name of the database connectivity mechanism. If the mechanism of retrieving the data is ODBC-JDBC Bridge, the subprotocol must be odbc.
  is used to identify the database.
Example: JDBC URL
String url = “jdbc:odbc:MyDataSource”;
Class.forName (“sun.jdbc.odbc.JdbcOdbcDriver “);
Connection con = DriverManager.getConnection (url);
Java Programming Unit 8
Sikkim Manipal University Page No. 189
Using the Statement Object
You can use the statement object to send simple queries to the database as shown in the sample QueryApp program.
In the above QueryApp example:
 The JDBC-ODBC Bridge driver is loaded.
 The Connection object is initialized using the getConnection () method.
 The Statement object is created using the createStatement () method.
 Finally, a simple query is executed using executeQuery () method of the Statement object.
The Statement object allows you to execute simple queries. It has the following three methods that can be used for the purpose of querying:
 The executeQuery () method executes a simple query and returns a single ResultSet object.
 The executeUpdate () method executes an SQL INSERT, UPDATE or DELETE statement.
 The execute () method executes an SQL statement that may return multiple results.
Java Programming Unit 8
Sikkim Manipal University Page No. 190
The ResultSet Object
The ResultSet object provides you with methods to access data from the table. Executing a statement usually generates a ResultSet object. It maintains a cursor pointing to its current row of data. Initially the cursor is positioned before the first row. The next () method moves the cursor to the next row. You can access data from the ResultSet rows by calling the getXXX () method where XXX is the data type. The following code queries the database and process the ResultSet.
Using the PreparedStatement Object
You have to develop an application that queries the database according to the search criteria specified by a user. For example, the user supplies the publisher ID and wants to see the details of that publisher.
select * from publishers where pub_id=?
To make it possible, you need to prepare a query statement at runtime with an appropriate value in the wher clause.
Java Programming Unit 8
Sikkim Manipal University Page No. 191
The PreparedStatement object allows you to execute parameterized queries. The PreparedStatement object is created using the PreparedStatement () method of the Connection object.
stat=con.prepareStatement (“select * from publishers where pub_id=?”);
The prepareStatement () ,method of the Connection object takes an SQL statement as a parameter. The SQL statement can contain placeholders that can be replaced by INPUT parameters at runtime.
The „?‟ symbols is a placeholder that can be replaced by the INPUT parameters at runtime.
Passing INPUT Parameters:
Before executing a PreparedStatement object, you must set the value of each „?‟ parameter. This is done by calling an appropriate setXXX () method, where XXX is the data type of the parameter.
stat.setString (1, pid.getText ());
ResultSet result=stat.executeQuery ();
The following code makes use of the PreparedStatement object :
import java.sql.*;
import java.awt.*;
import java.awt.event.*;
public class PreparedQueryApp extends Frame implements ActionListener
{
TextField pid;
TextField pname;
Button query;
static ResultSet result;
static Connection con;
Java Programming Unit 8
Sikkim Manipal University Page No. 192
static PreparedStatement stat;
public PreparedQueryApp ()
{
super (“The Query Application”);
setLayout (new GridLayout (5,1));
pid=new TextField (20);
pname=new TextField (50);
query=new Button (“Query”);
add(new Label (“Publisher ID:”));
add(pid);
add(new Label (“Publisher Name”));
add(pname);
add(query);
query.addActionListener (this);
pack ();
setVisible (true);
}
public static void main (String a[])
{
PreparedQueryApp obj =new PreparedQueryApp ();
try
{
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
Con=DriverManager.getConnection (“jdbc:odbc:MyDataSource”,”sa”,” “);
stat=con.prepareStatement (“select * from publishers where pub_id =?”);
}
catch(Exception e) { }
obj.showRecord (result );
Java Programming Unit 8
Sikkim Manipal University Page No. 193
public void actionPerformed (ActionEvent event)
{
if(event.getSource()==query)
{
try
{
stat.setString (1,pid.getText () );
result = stat.executeQuery ();
result.next ();
}
catch(Exception e){}
showRecord (result);
}
}
public void showRecord (ResultSet result)
{
try
{
pid.setText (result.getString (1));
pname.setText (result.getString (2));
}
catch (Exception e) {}
}
}
In the above example:
 The PreparedStatement object is created using the prepareStatement () method.
Java Programming Unit 8
Sikkim Manipal University Page No. 194
 The parameters of the PreparedStatement object are initialized when the user clicks on the Query button.
 The query is then executed using the executeQuery () method and the result is displayed in the corresponding controls.
Self Assessment Questions
 What are the uses of ODBC, JDBC and Driver Manager?
 What are the result set?
 What are the difference between a prepared statement object and statement object?
8.4 Load the ODBC driver
 Click on the ODBC Data Source icon in the Control Panel.
 Click on Add.
 Select the SQL Server option from the list and click on Finish.
 Select a name for the data source and click on the Next button.
 Select with SQL Server authentication using login ID and Password entered by the user.
 Select the database that you want to use and click on Next.
 Click on Finish.
 Click on Test Data Source to check for proper connectivity and click on OK.
8.5 Summary
JDBC
JDBC provides a database-programming interface for Java Programs. A Java Program can send queries to a database using the JDBC driver.
The java.sql Package
The java.sql package contains classes that help in connecting to a database, send SQL statements to the database, and process the query results.
Java Programming Unit 8
Sikkim Manipal University Page No. 195
The Connection Object
The Connection object represents a connection with a database. It can be initialized using the getConnection () method of the DriverManager class.
The PreparedStatement Object
The PreparedStatement object allows you to execute parameterized queries. It can be initialized using the preparedStatement () method of the Connection object.
The setString () Method
The setString () method sets the query parameters of the PreparedStatement Object.
The executeQuery () Method
The executeQuery () method executes, the query statement present in the PreparedStatement object and returns the number of rows affected by the query.
8.6 Terminal Questions
1. All shoppers who register with the Toy Universe Online Shopping Mall need to be allocated a password. Create a JDBC application that generates a ShopperId and accepts personal details along with a password from a first-time shopper, and stores these details in the Toy Universe database.
You need prepare a test report. You also need to get a review done from your tem leader.
2. Draw and explain the JDBC Application Architecture?
3. Explain the procedure for connecting to the database?