CST 302 : Advanced Database Management
Block : Web Database Systems
Unit 3: Java
Database Connectivity
3.0 Introduction
Database access through the Website is one of the essential components for any web-based development. Java being an important tool for web centric development needs to have such a support that is how Java applets and applications can connect to a database. In this unit, let us discuss about the JDBC, a mechanism that allows Java to talk to databases.
3.1 Objectives
At the end of this unit
you will be able to
define the concepts of JDBC
describe the requirements of JDBC
use the methodology of connecting through JDBC
3.2 What is
JDBC?
Java Database Connectivity (JDBC) is a standard Application Programming Interface (API) that is used to access databases, irrespective of the application driver and database product. In other words, JDBC presents a uniform interface to databases, however, if you change the database management system and your applications, you only need to change their driver. JDBC provides cross-DBMS connectivity to a wide range of SQL databases, and other tabular data sources, such as spreadsheets or flat files
There are plenty of drivers now for JDBC that support popular databases. In case you do not find a driver for the database management system you are using you may use a JDBC driver from Sun Microsystems, that is compatible with ODBC, therefore enabling you to connect to any ODBC (Open Database Connectivity) compliant database. JDBC drivers are available for most of the major database management system — such as for Oracle, DB2, Access, Informix, Sybase etc. — as well as for any data source that uses Microsoft's ODBC driver.
This programming interface allows
Java programmers to request a connection with a database, then sends query
statements using SQL and receive the results for processing. JDBC handles the
actual connection, sending queries and data to and from the database.
Programmers using JDBC typically create their own SQL queries, which requires
more work than using visual-oriented tools for building the queries. You need
to write the SQL statements manually, and you have to make sure they're well
formed, valid and correct. Since the queries are executed on the server side,
therefore, any syntactical error is
send to the database and you will get an error back.
JDBC also allows programmers to
update multiple fields with a single command, or even access multiple database
servers with a single transaction. In addition, it allows programmers reuse
database connections, known as connection
pooling, so a new connection doesn't need to be made to a database for each new
JDBC command.
Since Java runs on many different
hardware platforms and operating systems, developers can use JDBC to write
applications that access data across incompatible database management systems
running on different platforms. This feature is a great asset, as the whole
software is not to be redone.
The use of an application server, a
software that sits between the client and the database server accepting and
directing the data requests, have made the life easier for programmer.
Application servers have JDBC support built into them, reducing the amount of
code the programmer needs to write.
The use of application server
increases productivity and may be a dominant means of building new applications, thus, reducing the requirements of JDBC drivers.
The advantages of using JDBC
The basic three advantages of JDBC
may be:
- When there's a need to disseminate information internally in a
large company where departments have standardized on different platforms.
- If a corporation has undergone a merger and finds itself with
different operating systems and databases.
- For e-commerce applications that run over the Internet, where the
company has no control over the software its customers use. The customers
only need the appropriate Java technology, which can be downloaded on the
fly to their computers.
3.3 Accessing databases using JAVA
The database forms the foundation of infrastructure of a company. Almost all companies maintain databases. JAVA comes in, as it is a platform independent language. The various database interacting tools available in the market support one or some kinds of databases. But JAVA as we all know has to be written once then its byte code can be run on any OS, which has JVM installed. In addition, to this we know that other database clients (fat clients) take up a lot of memory and also cost more than JAVA based (thin clients).
From now on, we will proceed by first making a desktop database in Microsoft Access, and then we will populate it and run some queries on it to see how JAVA interacts with the database.
Please follow the following steps on your computer:
Step 1:
Open MSAccess from your Program group in Start Menu, and select a blank database as the choice from the menu presented. Then make the following two table structures in the database:
1. Make the table ProductMaster as shown in the figure below:
2. Make the table CategoryMaster as:
3. Make the SupplierMaster table as:
4. Make the relation table as:
Step 2:
In the tools Menu choose to edit the relations and make the relations as shown:
Step 3:
Now key in some test data into the tables as shown:
Now that we have made a database and have put in some test data, we are ready to test java programs on it but first we have to make an ODBC connection or a system DSN to the database. This is also enumerated step wise in the figures below:
Step4.1:
Open the control panel in Win-98/95 then select ODBC data sources tab.
It looks as follows:
Step4.2:
Now choose System DSN tab and press the Add.. button on the right to get the following screen:
Here depending on the Access version installed on your machine you have to choose Microsoft Access Driver (*.mdb) here it is 4.
Step4.3:
Now we get the database selection screen where you will have to select a database and name the data source you want to refer this data source as. You may or may not give this a description.
Here the database is located at “C:\My Documents\Ignou.mdb” and the data source is called JavaTest. You may call it anything else and your database may be any other name.
Now we have done all the ground work for the JDBC programming. I presume that you have latest Java Development Kit installed. If not then it is freely available from http://sun.java.com or if you buy a good JAVA book the JDK comes in a CD with the book.
Now I will write a small program to access the database and run a query which will show details of all suppliers who supply eatables.
First, formulate the SELECT clause for this query as follows:
SELECT * FROM
SupplierMaster
WHERE SupplierCode IN (
SELECT
SCode
FROM
Relation
WHERE
PCode IN (
SELECT
ProductCode
FROM
ProductMaster
WHERE
ProdCatg IN (
SELECT
CatgID
FROM
CategoryMaster
WHERE
CategoryName = ‘Eatables’)))
If the above query is run in Access itself then the following result is produced:
|
SupplierCode |
SupplierName |
SupplierAddress |
|
2 |
Mela Ram |
1 Subji Mandi New Delhi |
|
3 |
Sanjiv Sharma |
1234 Netaji Nagar |
|
4 |
Bhim Singh |
1 Shivalik New Delhi |
Now this was easy thought this is one of the ways of writing the query you may also write it by using joins. But our purpose is to write a JAVA program to do the same. Write the following piece of code save. It as DBQuery.JAVA then compile and run it.
import
java.sql.*;
class
DBQuery1{
public static void main(String args[])
throws SQLException
{
try{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}catch(ClassNotFoundException
e){};
Connection cnn;
Statement mystmt;
ResultSet myrs;
String op =
"jdbc:odbc:JavaTest";
cnn =
DriverManager.getConnection(op,
"Admin",
"");
mystmt = cnn.createStatement();
String sql;
sql = "SELECT * FROM
SupplierMaster " +
"WHERE SupplierCode IN (
" +
"SELECT
SCode " +
"FROM Relation
" +
"WHERE PCode IN (
" +
"SELECT
ProductCode " +
"FROM
ProductMaster " +
"WHERE
ProdCatg IN ( " +
"SELECT
CatgID " +
"FROM
CategoryMaster " +
"WHERE CategoryName = 'Eatables')))";
myrs = mystmt.executeQuery(sql);
System.out.println(" "+"Sup Code" + " " +"Sup Name" +
" " + "Sup
Address ");
System.out.println("--------------------------------------------------------------------");
String name, add;
int code;
while (myrs.next())
{
code = myrs.getInt("SupplierCode");
name =
myrs.getString("SupplierName");
add =
myrs.getString("SupplierAddress");
System.out.println("
" + code +"
" + name+"
" + add);
}
}
}
The following result will be produced on successful running of the program:
C:\JavaProg>java
DBQuery1
Sup Code Sup Name Sup
Address
--------------------------------------------------------------------
2
Mela Ram 1 Subji Mandi New
Delhi
3
Sanjiv Sharma 1234 Netaji
Nagar
4
Bhim Singh 1 Shivalik New
Delhi
Now let us break the above code and see what each line does:
Ø We begin by importing the sql package this is the package that has built in methods to interact with a database.
Ø The main() method throws an exception of SQL type which is used to catch any errors during execution of SQL commands.
Ø Class.forname() this is the class which tells which JDBC driver to use currently we use the driver given to us by JAVA i.e. Sun’s own JDBC, ODBC bridge driver.
Ø Next we see three new type of variables:
o Connection: As the name suggests this will establish a connection with the database. The DriverManager class’ getConnection() method opens a new connection with the specified url, which may a web address or a DSN name as we have used. We also have to specify User name, Password in this method.
o Statement: This is synonymous with a SQL statement. The executeQuery() method of this class executes a SELECT query.execueUpdate() method executes an update query like INSERT.
o ResultSet: As we can clearly see tis holds the result of a Query and we can move through it to read data in it.
We now move on to another program this will insert a new record in the CategoryMaster and the ProductMaster tables.
We will try to insert a new category of Confectionery and a new Product “Black Forest Cake”.
import
java.sql.*;
class
DBQuery1{
public static void main(String args[])
throws SQLException
{
try{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}catch(ClassNotFoundException e){};
Connection cnn;
Statement mystmt;
ResultSet myrs;
String op =
"jdbc:odbc:JavaTest";
cnn = DriverManager.getConnection(op,
"Admin",
"");
mystmt = cnn.createStatement();
String sql1,sql2,sql3;
int code;
sql = "INSERT INTO
CategoryMaster(CategoryName) VALUES"
+ "(‘Confectionery’);
mystmt.executeUpdate(sql1);
sql2 = "SELECT CatgID FROM
CategoryMaster " +
"WHERE CategoryName =
‘Confectionery’";
myrs = mystmt.executeQuery(sql2);
code =
myrs.getInt("CatgID");
sql3 = "INSERT INTO
ProductMaster(ProductName, " +
ProdCatg) VALUES (‘Black
Forest Cake’, code);
mystmt.executeUpdate(sql3);
System.out.println("\t" +
"Prod Code" + " "
+ "Prod Name"
+ "\t" + "Category
Code");
System.out.println("------------------------------------------------------------");
sql2 = "SELECT * FROM
ProductMaster"
myrs = mystmt.executeQuery(sql2);
while(myrs.next())
{
code = myrs.getInt("ProdCatg");
name =
myrs.getString("ProductName");
catg =
myrs.getDtring("ProdCatg");
System.out.println("\t " + code + " " + name +
" " +
catg);
}
}
}
As we can see the above first adds a row to both product master and category master tables and then opens and prints the product master.
After writing two programs we are redy to read some theory about JDBC. The story begins as:
ANSI told database manufacturers to make databases that could be connected with almost all front ends or in simple word databases should be so made that the database as such becomes independent of the client accessing it. But this is far from what is being made by manufacturers. Though we can connect VB to an Oracle database but it is done only though an ODBC driver which is a bulky file kept in our system. It may appear simple as all of us buy latest systems with fast processors and high memory but imagine a situation where one of your client may not want to waste space and install a big software. In this case each large file that accompanies your VB project becomes a headache. You may try this by making a simple database application in VB and Access, then package it as a setup using the Package & Deployment Wizard. See the amount of space the single cabinet file takes. Now compare it with the space taken by above program.
The above should not make you shift all your database applications from VB to JAVA (VB is treated as the best front end to almost all databases). But still it will make you pause and think, how JAVA does this?
JDBC provides application developers with a single API that is uniform and database independent. The solution is set of JAVA interfaces that are implemented by a driver. The driver translates the standard JDBC calls into the specific calls required by the database it supports. The application may be written once and moved to various drivers. The application remains the same; drivers change.
3.4 The JDBC API components
In general there are two levels of interface in the JDBC API; the Application Layer, where the developer uses the API to make calls to the database via SQL and retrieve the results. The second is the driver layer, this handles all communication with a specific driver. The following illustrates this concept:
As we see form the above figure the Driver layer is totally separated from the Application layer, this gives an added advantage as the Driver writer writes only drivers and is not concerned witht the application developer and the vice-versa is also true. Usually we interact with the Driver class with the DriverManager as another abstraction in between the Driver and the application.
For Your Knowledge (Skip if
you want to):
JDBC drivers are classified into follwing categories:
Ø Type 1 driver: Translates JDBC to ODBC and relies on ODBC to connect to the database. Thi is the driver we used as it is included witht the JDK.
Ø Type 2 driver: This is written partly in JAVA and partly in native code this communicates witht the client API of the database directly. When we use such a driver we have to install client specific library.
Ø Type 3 driver: This is a pure JAVA based client library that uses a database-independent protocol to communicate database requests to server component, which then translates the requests into a database specific protocol.
Ø Type 4 driver: This is a pure JAVA based client that translates JDBC requests directly to a database specific protocol.
Now let us get over with all main methods of the four classes shown in the above diagram. This is the part where I actually marvel Microsoft, you can think with so classes and then their methods, then typing the JAVA code in a text editor. At such times you think of the VB IDE and then you start missing something, any way the following will describe the methods which we will use to do database programming in JAVA:
When we use the preparedStatement we want to execute parametric statements as we are telling JAVA that this statement will be executed again and again, with different parameters. Each parameter is deneoted by a “?” and then we may set the parameters as each of the parameters can then be accessed by referring t an index with the first at the 1st location.
Example: Say in our database we have to find all products belnging to a particular category, but the category is to be entered by the user when the program runs. Then our sql statement will be:
sql = “SELECT * FROM
ProductMaster WHERE ProdCatg = ?”
Then after reading input from the user we will execute the query by first giving the ? a value. Say stmt is a PreparedStatement type object then:
stmt.setInt(1, 2) :will set the ? to 2.
Now if we say: myrs = stmt.executeQuery(sql); then the query will be executed with 2 as category ID.
The setInt that we have used above has many other brothers, like setFloat, setString etc.
Now we know and understand the basic objects in the JDBC package, with their methods. This forms the base of any database application or applet. We will now proceed back to the interesting work of writing code. This will end the first session on JDBC.
Now we will go back to coding (the interesting part). We will deal with two new type of objects, PreparedStatement and CallableStatement. If you have still not understood the meaning or need of the two then read the following:
1. PreparedStatement:
All of us have operated on the database and we know it has both master, transaction, temporaray tables. Now data entry is not the only thing that is done on a database. But reports form a very important and necessary pillar of all DBMS. Reports are nothing but printed queries, queries are nothting but questions asked to the database. A manager will not be interested in a complete listing of all his employees. But he will be definitely interested in all employees who are staying in company quarters. This is a subset of the set of all the employees. Now the manager will often tell the database operator to generate this report. The operator will not always write a new query, but he will make a stored procedure that will take different inputs like which for department the report is to be generated or for what position, all these are parameters to the query and we can make parametric queries in JAVA by using PreparedStatements. PreparedStatements are inherently extension of Statement object.
Let us try a simple example of PreparedStatement:
import java.io.*;
import java.sql.*;
class
DBQuery4
{
public static void main(String args[]) throws
SQLException
{
try{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}
catch(ClassNotFoundException e){};
Connection c;
PreparedStatement pst;
ResultSet rs;
String op="jdbc:odbc:JavaTest";
c=
DriverManager.getConnection(op,"Admin", "");
pst=c.prepareStatement("SELECT * FROM
" +
"SupplierMaster WHERE
"SupplierCode=?");
BufferedReader br = new BufferedReader(new
InputStreamReader(System.in));
int a;
System.out.println("Enter the supplier
code");
a = Integer.parseInt(br.readLine());
pst.setInt(1,a);
rs= pst.executeQuery();
System.out.println(" "+"Sup Code" + "
" +"Sup Name" +" " + "Sup Address ");
System.out.println("----------------------------
------------------------------------------------");
String name, add;
int code;
while (rs.next())
{
code= rs.getInt("SupplierCode");
name=
rs.getString("SupplierName");
add= rs.getString("SupplierAddress");
System.out.println(" " +code + "
" + name
+" " +add);
}
}
}
When executed the query gives the following result:
Enter the
supplier code
6
Sup
Code Sup Name Sup Address
-----------------------------------------------------------------------
6 Mridul 10, Preet Vihar
2. CallableSatement:
The callable statement is used to call stored procedures in the databases. Stored procedures are nothing but queries used most often by more than one person, so that the DBA feels the need to keep that query on the DB server instead of all clients executing it repeatedly. We can define Stored Procedures by using the CREATE PROCEDURE statement of SQL. We may even pass parameters to this statement and return some values. Actually, it is just like a regular programming language sub-routine.
CallableSatement objects help us to execute these built in procedures. Now those who have worked with Access and Microsoft Jet Engine know that Access does not support the CREATE PROCEDURE statement (try writing it in the new query pane and see the result yourself). However, we can create parametric queries these work the same as stored procedures. Let us create a parametric query, which will give me a list of all products supplied by a particular supplier, but the SupplierID of the supplier will be passed to the query at run time. Open the database that we have already made then follow these steps:
· Open a new query window as:
· Double click or just press Enter and get the following:
· Now just click on the close button and right click on the greay pane to get:
· Choose SQL View and write the following statement in it.
· Choose Run from the query menu or you may press the exclamation mark on the toolbar to get this:
· Press Enter after entering 3 to get:
If you have got the above then
bingo! You have created a new query. Now we will proceed to call this from
JAVA, that is important.
Now write the following code in any text editor:
import java.sql.*;
import java.io.*;
class DBQuery6
{
public static void main (String args[]) throws SQLException,
IOException
{
try{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}
catch(ClassNotFoundException e){};
Connection cnn;
CallableStatement cs;
ResultSet rs;
String open="jdbc:odbc:JavaTest";
cnn=DriverManager.getConnection(open,"Admin","");
cs=cnn.prepareCall("{call SuppProd (?)}");
int inp;
BufferedReader d= new BufferedReader(new
InputStreamReader(System.in));
System.out.println("Enter the product code");
inp=Integer.parseInt(d.readLine());
cs.setInt(1,inp);
rs= cs.executeQuery();
System.out.println("
"+"Sup Code" + " " +"Sup Name" +"
" + "Sup Address ");
System.out.println("-----------------------------------------------
---------------------------------");
String name, add;
int code;
while (rs.next())
{
code= rs.getInt("SupplierCode");
name= rs.getString("SupplierName");
add= rs.getString("SupplierAddress");
System.out.println("
" +code + "
" + name +"
" +add);
}
}
}
If executed correctly the query gives the following result:
Enter the product code
3
Sup Code Sup Name
Sup Address
-----------------------------------------------------------------------
2 Mela Ram 1 Subji Mandi New Delhi