What is JDBC Driver?
JDBC drivers implement the defined interfaces in the JDBC API for interacting with your database server.
For example, using JDBC drivers enable you to open database connections and to interact with it by sending SQL or
database commands then receiving results with Java.
The [Link] package that ships with JDK contains various classes with their behaviors defined and their actual
implementaions are done in third-party drivers. Third party vendors implement the [Link] interface in their
database driver.
JDBC Drivers Types:
JDBC driver implementations vary because of the wide variety of operating systems and hardware platforms in which
Java operates. Sun has divided the implementation types into four categories, Types 1, 2, 3, and 4, which is
explained below:
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.
The JDBC-ODBC bridge that comes with JDK 1.2 is a good example of this kind of driver.
Type 2: JDBC-Native API:
In a Type 2 driver, JDBC API calls are converted into native C/C++ API calls which are unique to the database.
These drivers 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.
The Oracle Call Interface (OCI) driver is an example of a Type 2 driver.
Type 3: JDBC-Net pure Java:
In a Type 3 driver, a three-tier approach is used to accessing databases. The JDBC clients use standard network
sockets to communicate with an 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.
You can think of the application server as a JDBC "proxy," meaning that it makes calls for the client application. As a
result, you need some knowledge of the application server's configuration in order to effectively use this driver type.
Your application server might use a Type 1, 2, or 4 driver to communicate with the database, understanding the
nuances will prove helpful.
Type 4: 100% pure Java:
In a Type 4 driver, a pure Java-based driver that communicates directly with 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.
MySQL's Connector/J driver is a Type 4 driver. Because of the proprietary nature of their network protocols, database
vendors usually supply type 4 drivers.
Which Driver should be used?
If you are accessing one type of database, such as Oracle, Sybase, or IBM, the preferred driver type is 4.
If your Java application is accessing multiple types of databases at the same time, type 3 is the preferred driver.
Type 2 drivers are useful in situations where a type 3 or type 4 driver is not available yet for your database.
The type 1 driver is not considered a deployment-level driver and is typically used for development and testing
purposes only.
Install Database:
The most important thing you will need, of course is an actual running database with a table that you can query and
modify.
Install a database that is most suitable for you. You can have plenty of choices and most common are:
MySQL DB: MySQL is an open source database. You can download it from MySQL Official Site. We recommend
downloading the full Windows installation.
In addition, download and install MySQL Administrator as well as MySQL Query Browser. These are GUI based tools
that will make your development much easier.
Finally, download and unzip MySQL Connector/J (the MySQL JDBC driver) in a convenient directory. For the purpose
of this tutorial we will assume that you have installed the driver at C:\Program Files\MySQL\mysql-connector-java-
5.1.8.
Accordingly set CLASSPATH variable to C:\Program Files\MySQL\mysql-connector-java-5.1.8\mysql-connector-java-
[Link]. Your driver version may vary based on your installation.
PostgreSQL DB: PostgreSQL is an open source database. You can download it fromPostgreSQL Official Site.
The Postgres installation contains a GUI based administrative tool called pgAdmin III. JDBC drivers are also included
as part of the installation.
Oracle DB: Oracle DB is an commercial database sold by Oracle . We assume that you have the necessary
distribution media to install it.
Oracle installation includes a GUI based administrative tool called Enterprise Manager. JDBC drivers are also
included as part of the installation.
Install Database Drivers:
The latest JDK includes a JDBC-ODBC Bridge driver that makes most Open Database Connectivity (ODBC) drivers
available to programmers using the JDBC API.
Now a days most of the Database vendors are supplying appropriate JDBC drivers along with Database installation.
So you should not worry about this part.
Set Database Credential:
For this tutorial we are going to use MySQL database. When you install any of the above database, its administrator
ID is set to root and gives provision to set a password of your choice.
Using root ID and password you can either create another users ID and password or you can use root ID and
password for your JDBC application.
There are various database operations like database creation and deletion, which would need administrator ID and
password.
For rest of the JDBC tutorial we would use MySQL Database with username as ID and password as password.
If you do not have sufficient privilege to create new users then you can ask your Database Administrator (DBA) to
create a user ID and password for you.
Create Database:
To create the EMP database, use the following steps:
Step 1:
Open a Command Prompt and change to the installation directory as follows:
C:\>
C:\>cd Program Files\MySQL\bin
C:\Program Files\MySQL\bin>
Note: The path to [Link] may vary depending on the install location of MySQL on your system. You can also
check documentation on how to start and stop your database server.
Step 2:
Start the database server by executing the following command, if it is already not running.
C:\Program Files\MySQL\bin>mysqld
C:\Program Files\MySQL\bin>
Step 3:
Create the EMP database by executing the following command
C:\Program Files\MySQL\bin> mysqladmin create EMP -u root -p
Enter password: ********
C:\Program Files\MySQL\bin>
Create Table
To create the Employees table in EMP database, use the following steps:
Step 1:
Open a Command Prompt and change to the installation directory as follows:
C:\>
C:\>cd Program Files\MySQL\bin
C:\Program Files\MySQL\bin>
Step 2:
Login to database as follows
C:\Program Files\MySQL\bin>mysql -u root -p
Enter password: ********
mysql>
Step 3:
Create the table Employee as follows:
mysql> use EMP;
mysql> create table Employees
-> (
-> id int not null,
-> age int not null,
-> first varchar (255),
-> last varchar (255)
-> );
Query OK, 0 rows affected (0.08 sec)
mysql>
Create Data Records
Finally you create few records in Employee table as follows:
mysql> INSERT INTO Employees VALUES (100, 18, 'Zara', 'Ali');
Query OK, 1 row affected (0.05 sec)
mysql> INSERT INTO Employees VALUES (101, 25, 'Mahnaz', 'Fatma');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO Employees VALUES (102, 30, 'Zaid', 'Khan');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO Employees VALUES (103, 28, 'Sumit', 'Mittal');
Query OK, 1 row affected (0.00 sec)
mysql>
For a complete understanding on MySQL database, study MySQL Tutorial.
Now you are ready to start experimenting with JDBC. Next tutorial would give your a sample example on JDBC
Programming.
JDBC PROCESS:
There are following six steps involved in building a JDBC application:
Import the packages . Requires that you include the packages containing the JDBC classes needed for database
programming. Most often, using import [Link].* will suffice.
Register the JDBC driver . Requires that you initialize a driver so you can open a communications channel with the
database.
Open a connection . Requires using the [Link]() method to create a Connection object,
which represents a physical connection with the database.
Execute a query . Requires using an object of type Statement for building and submitting an SQL statement to the
database.
Extract data from result set . Requires that you use the appropriate [Link]() method to retrieve the data
from the result set.
Clean up the environment . Requires explicitly closing all database resources versus relying on the JVM's garbage
collection.
Sample Code:
This sample example can serve as a template when you need to create your own JDBC application in the future.
This sample code has been written based on the environment and database setup done in previous chapter.
Copy and past following example in [Link], compile and run as follows:
//STEP 1. Import required packages
import [Link].*;
public class FirstExample {
// JDBC driver name and database URL
static final String JDBC_DRIVER = "[Link]";
static final String DB_URL = "jdbc:mysql://localhost/EMP";
// Database credentials
static final String USER = "username";
static final String PASS = "password";
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try{
//STEP 2: Register JDBC driver
[Link]("[Link]");
//STEP 3: Open a connection
[Link]("Connecting to database...");
conn = [Link](DB_URL,USER,PASS);
//STEP 4: Execute a query
[Link]("Creating statement...");
stmt = [Link]();
String sql;
sql = "SELECT id, first, last, age FROM Employees";
ResultSet rs = [Link](sql);
//STEP 5: Extract data from result set
while([Link]()){
//Retrieve by column name
int id = [Link]("id");
int age = [Link]("age");
String first = [Link]("first");
String last = [Link]("last");
//Display values
[Link]("ID: " + id);
[Link](", Age: " + age);
[Link](", First: " + first);
[Link](", Last: " + last);
}
//STEP 6: Clean-up environment
[Link]();
[Link]();
[Link]();
}catch(SQLException se){
//Handle errors for JDBC
[Link]();
}catch(Exception e){
//Handle errors for [Link]
[Link]();
}finally{
//finally block used to close resources
try{
if(stmt!=null)
[Link]();
}catch(SQLException se2){
}// nothing we can do
try{
if(conn!=null)
[Link]();
}catch(SQLException se){
[Link]();
}//end finally try
}//end try
[Link]("Goodbye!");
}//end main
}//end FirstExample
Now let us compile above example as follows:
C:\>javac [Link]
C:\>
When you run FirstExample, it produces following result:
C:\>java FirstExample
Connecting to database...
Creating statement...
ID: 100, Age: 18, First: Zara, Last: Ali
ID: 101, Age: 25, First: Mahnaz, Last: Fatma
ID: 102, Age: 30, First: Zaid, Last: Khan
ID: 103, Age: 28, First: Sumit, Last: Mittal
C:\>
DatabaseMetaData interface:
DatabaseMetaData interface provides methods to get meta data of a database such as
database product name, database product version, driver name, name of total number of
tables, name of total number of views etc.
Commonly used methods of DatabaseMetaData interface
public String getDriverName()throws SQLException: it returns the name of
the JDBC driver.
public String getDriverVersion()throws SQLException: it returns the version
number of the JDBC driver.
public String getUserName()throws SQLException: it returns the username
of the database.
public String getDatabaseProductName()throws SQLException: it returns
the product name of the database.
public String getDatabaseProductVersion()throws SQLException: it returns
the product version of the database.
public ResultSet getTables(String catalog, String schemaPattern, String
tableNamePattern, String[] types)throws SQLException: it returns the
description of the tables of the specified catalog. The table type can be TABLE,
VIEW, ALIAS, SYSTEM TABLE, SYNONYM etc.
How to get the object of DatabaseMetaData:
The getMetaData() method of Connection interface returns the object of
DatabaseMetaData. Syntax:
1. public DatabaseMetaData getMetaData()throws SQLException
Simple Example of DatabaseMetaData interface :
1. import [Link].*;
2. class Dbmd{
3. public static void main(String args[]){
4. try{
5. [Link]("[Link]");
6.
7. Connection con=[Link](
8. "jdbc:oracle:thin:@localhost:1521:xe","system","oracle");
9.
10. DatabaseMetaData dbmd=[Link]();
11.
12. [Link]("Driver Name: "+[Link]());
13. [Link]("Driver Version: "+[Link]());
14. [Link]("UserName: "+[Link]());
15. [Link]("Database Product Name: "+[Link]());
16. [Link]("Database Product Version: "+[Link]());
17.
18. [Link]();
19.
20. }catch(Exception e){ [Link](e);}
21.
22. }
23. }
Output:Driver Name: Oracle JDBC Driver
Driver Version: [Link].0XE
Database Product Name: Oracle
Database Product Version: Oracle Database 10g Express Edition
Release [Link].0 -Production
download this example
Example of DatabaseMetaData interface that prints total number
of tables :
1. import [Link].*;
2. class Dbmd2{
3. public static void main(String args[]){
4. try{
5. [Link]("[Link]");
6.
7. Connection con=[Link](
8. "jdbc:oracle:thin:@localhost:1521:xe","system","oracle");
9.
10. DatabaseMetaData dbmd=[Link]();
11. String table[]={"TABLE"};
12. ResultSet rs=[Link](null,null,null,table);
13.
14. while([Link]()){
15. [Link]([Link](3));
16. }
17.
18. [Link]();
19.
20. }catch(Exception e){ [Link](e);}
21.
22. }
23. }
download this example
Example of DatabaseMetaData interface that prints total number
of views :
1. import [Link].*;
2. class Dbmd3{
3. public static void main(String args[]){
4. try{
5. [Link]("[Link]");
6.
7. Connection con=[Link](
8. "jdbc:oracle:thin:@localhost:1521:xe","system","oracle");
9.
10. DatabaseMetaData dbmd=[Link]();
11. String table[]={"VIEW"};
12. ResultSet rs=[Link](null,null,null,table);
13.
14. while([Link]()){
15. [Link]([Link](3));
16. }
17.
18. [Link]();
19.
20. }catch(Exception e){ [Link](e);}
21.
22. }
23. }
DatabaseMetaData interface:
DatabaseMetaData interface provides methods to get meta data of a database such as
database product name, database product version, driver name, name of total number of
tables, name of total number of views etc.
Commonly used methods of DatabaseMetaData interface
public String getDriverName()throws SQLException: it returns the name of
the JDBC driver.
public String getDriverVersion()throws SQLException: it returns the version
number of the JDBC driver.
public String getUserName()throws SQLException: it returns the username
of the database.
public String getDatabaseProductName()throws SQLException: it returns
the product name of the database.
public String getDatabaseProductVersion()throws SQLException: it returns
the product version of the database.
public ResultSet getTables(String catalog, String schemaPattern, String
tableNamePattern, String[] types)throws SQLException: it returns the
description of the tables of the specified catalog. The table type can be TABLE,
VIEW, ALIAS, SYSTEM TABLE, SYNONYM etc.
How to get the object of DatabaseMetaData:
The getMetaData() method of Connection interface returns the object of
DatabaseMetaData. Syntax:
1. public DatabaseMetaData getMetaData()throws SQLException
Simple Example of DatabaseMetaData interface :
1. import [Link].*;
2. class Dbmd{
3. public static void main(String args[]){
4. try{
5. [Link]("[Link]");
6.
7. Connection con=[Link](
8. "jdbc:oracle:thin:@localhost:1521:xe","system","oracle");
9.
10. DatabaseMetaData dbmd=[Link]();
11.
12. [Link]("Driver Name: "+[Link]());
13. [Link]("Driver Version: "+[Link]());
14. [Link]("UserName: "+[Link]());
15. [Link]("Database Product Name: "+[Link]());
16. [Link]("Database Product Version: "+[Link]());
17.
18. [Link]();
19.
20. }catch(Exception e){ [Link](e);}
21.
22. }
23. }
Output:Driver Name: Oracle JDBC Driver
Driver Version: [Link].0XE
Database Product Name: Oracle
Database Product Version: Oracle Database 10g Express Edition
Release [Link].0 -Production
download this example
Example of DatabaseMetaData interface that prints total number
of tables :
1. import [Link].*;
2. class Dbmd2{
3. public static void main(String args[]){
4. try{
5. [Link]("[Link]");
6.
7. Connection con=[Link](
8. "jdbc:oracle:thin:@localhost:1521:xe","system","oracle");
9.
10. DatabaseMetaData dbmd=[Link]();
11. String table[]={"TABLE"};
12. ResultSet rs=[Link](null,null,null,table);
13.
14. while([Link]()){
15. [Link]([Link](3));
16. }
17.
18. [Link]();
19.
20. }catch(Exception e){ [Link](e);}
21.
22. }
23. }
What are Servlets?
Java Servlets are programs that run on a Web or Application server and act as a middle layer between a request
coming from a Web browser or other HTTP client and databases or applications on the HTTP server.
Using Servlets, you can collect input from users through web page forms, present records from a database or another
source, and create web pages dynamically.
Java Servlets often serve the same purpose as programs implemented using the Common Gateway Interface (CGI).
But Servlets offer several advantages in comparison with the CGI.
Performance is significantly better.
Servlets execute within the address space of a Web server. It is not necessary to create a separate process to handle
each client request.
Servlets are platform-independent because they are written in Java.
Java security manager on the server enforces a set of restrictions to protect the resources on a server machine. So
servlets are trusted.
The full functionality of the Java class libraries is available to a servlet. It can communicate with applets, databases,
or other software via the sockets and RMI mechanisms that you have seen already.
Servlets Architecture:
Following diagram shows the position of Servelts in a Web Application.
Servlets Tasks:
Servlets perform the following major tasks:
Read the explicit data sent by the clients (browsers). This includes an HTML form on a Web page or it could also
come from an applet or a custom HTTP client program.
Read the implicit HTTP request data sent by the clients (browsers). This includes cookies, media types and
compression schemes the browser understands, and so forth.
Process the data and generate the results. This process may require talking to a database, executing an RMI or
CORBA call, invoking a Web service, or computing the response directly.
Send the explicit data (i.e., the document) to the clients (browsers). This document can be sent in a variety of
formats, including text (HTML or XML), binary (GIF images), Excel, etc.
Send the implicit HTTP response to the clients (browsers). This includes telling the browsers or other clients what
type of document is being returned (e.g., HTML), setting cookies and caching parameters, and other such tasks.
Servlets Packages:
Java Servlets are Java classes run by a web server that has an interpreter that supports the Java Servlet
specification.
Servlets can be created using the [Link] and [Link] packages, which are a standard part of the
Java's enterprise edition, an expanded version of the Java class library that supports large-scale development
projects.
These classes implement the Java Servlet and JSP specifications. At the time of writing this tutorial, the versions are
Java Servlet 2.5 and JSP 2.1.
Java servlets have been created and compiled just like any other Java class. After you install the servlet packages
and add them to your computer's Classpath, you can compile servlets with the JDK's Java compiler or any other
current compiler.
Servlets - Life Cycle
A servlet life cycle can be defined as the entire process from its creation till the destruction. The following are the
paths followed by a servlet
The servlet is initialized by calling the init () method.
The servlet calls service() method to process a client's request.
The servlet is terminated by calling the destroy() method.
Finally, servlet is garbage collected by the garbage collector of the JVM.
Now let us discuss the life cycle methods in details.
The init() method :
The init method is designed to be called only once. It is called when the servlet is first created, and not called again
for each user request. So, it is used for one-time initializations, just as with the init method of applets.
The servlet is normally created when a user first invokes a URL corresponding to the servlet, but you can also specify
that the servlet be loaded when the server is first started.
When a user invokes a servlet, a single instance of each servlet gets created, with each user request resulting in a
new thread that is handed off to doGet or doPost as appropriate. The init() method simply creates or loads some data
that will be used throughout the life of the servlet.
The init method definition looks like this:
public void init() throws ServletException {
// Initialization code...
}
The service() method :
The service() method is the main method to perform the actual task. The servlet container (i.e. web server) calls the
service() method to handle requests coming from the client( browsers) and to write the formatted response back to
the client.
Each time the server receives a request for a servlet, the server spawns a new thread and calls service. The service()
method checks the HTTP request type (GET, POST, PUT, DELETE, etc.) and calls doGet, doPost, doPut, doDelete,
etc. methods as appropriate.
Here is the signature of this method:
public void service(ServletRequest request,
ServletResponse response)
throws ServletException, IOException{
}
The service () method is called by the container and service method invokes doGe, doPost, doPut, doDelete, etc.
methods as appropriate. So you have nothing to do with service() method but you override either doGet() or doPost()
depending on what type of request you receive from the client.
The doGet() and doPost() are most frequently used methods with in each service request. Here is the signature of
these two methods.
The doGet() Method
A GET request results from a normal request for a URL or from an HTML form that has no METHOD specified and it
should be handled by doGet() method.
public void doGet(HttpServletRequest request,
HttpServletResponse response)
throws ServletException, IOException {
// Servlet code
}
The doPost() Method
A POST request results from an HTML form that specifically lists POST as the METHOD and it should be handled by
doPost() method.
public void doPost(HttpServletRequest request,
HttpServletResponse response)
throws ServletException, IOException {
// Servlet code
}
The destroy() method :
The destroy() method is called only once at the end of the life cycle of a servlet. This method gives your servlet a
chance to close database connections, halt background threads, write cookie lists or hit counts to disk, and perform
other such cleanup activities.
After the destroy() method is called, the servlet object is marked for garbage collection. The destroy method definition
looks like this:
public void destroy() {
// Finalization code...
}
Architecture Digram:
The following figure depicts a typical servlet life-cycle scenario.
First the HTTP requests coming to the server are delegated to the servlet container.
The servlet container loads the servlet before invoking the service() method.
Then the servlet container handles multiple requests by spawning multiple threads, each thread executing the
service() method of a single instance of the servlet.
