What is JDBC?

- Introduction
JDBC stands for Java Database Connectivity, which is a standard Java API for database-independent connectivity
between the Java programming language and a wide range of databases.
The JDBC library includes APIs for each of the tasks mentioned below that are commonly associated with database
usage.
 Making a connection to a database.
 Creating SQL or MySQL statements.
 Executing SQL or MySQL queries in the database.
 Viewing & Modifying the resulting records.
Fundamentally, JDBC is a specification that provides a complete set of interfaces that allows for portable access to
an underlying database. Java can be used to write different types of executables, such as −
 Java Applications
 Java Applets
 Java Servlets
 Java ServerPages (JSPs)
 Enterprise JavaBeans (EJBs).
All of these different executables are able to use a JDBC driver to access a database, and take advantage of the
stored data.
JDBC provides the same capabilities as ODBC, allowing Java programs to contain database-independent code.
Pre-Requisite
Before moving further, you need to have a good understanding of the following two subjects −
 Core JAVA Programming
 SQL or MySQL Database

JDBC Architecture
The JDBC API supports both two-tier and three-tier processing models for database access but in general, JDBC
Architecture consists of two layers −
 JDBC API − This provides the application-to-JDBC Manager connection.
 JDBC Driver API − This supports the JDBC Manager-to-Driver Connection.
The JDBC API uses a driver manager and database-specific drivers to provide transparent connectivity to
heterogeneous databases.
The JDBC driver manager ensures that the correct driver is used to access each data source. The driver manager is
capable of supporting multiple concurrent drivers connected to multiple heterogeneous databases.
Following is the architectural diagram, which shows the location of the driver manager with respect to the JDBC
drivers and the Java application −
Common JDBC Components
The JDBC API provides the following interfaces and classes −
 DriverManager − This class manages a list of database drivers. Matches connection requests from the java
application with the proper database driver using communication sub protocol. The first driver that
recognizes a certain subprotocol under JDBC will be used to establish a database Connection.
 Driver − This interface handles the communications with the database server. You will interact directly with
Driver objects very rarely. Instead, you use DriverManager objects, which manages objects of this type. It also
abstracts the details associated with working with Driver objects.
 Connection − This interface with all methods for contacting a database. The connection object represents
communication context, i.e., all communication with database is through connection object only.
 Statement − You use objects created from this interface to submit the SQL statements to the database. Some
derived interfaces accept parameters in addition to executing stored procedures.
 ResultSet − These objects hold data retrieved from a database after you execute an SQL query using
Statement objects. It acts as an iterator to allow you to move through its data.
 SQLException − This class handles any errors that occur in a database application.
The JDBC 4.0 Packages
The java.sql and javax.sql are the primary packages for JDBC 4.0. This is the latest JDBC version at the time of
writing this tutorial. It offers the main classes for interacting with your data sources.
The new features in these packages include changes in the following areas −
 Automatic database driver loading.
 Exception handling improvements.
 Enhanced BLOB/CLOB functionality.
 Connection and statement interface enhancements.
 National character set support.
 SQL ROWID access.
 SQL 2003 XML data type support.
 Annotations.

JDBC - Environment Setup
To start developing with JDBC, you should setup your JDBC environment by following the steps shown below. We
assume that you are working on a Windows platform.
Install Java
Java SE is available for download for free. To download click here, please download a version compatible with your
operating system.
Follow the instructions to download Java, and run the .exe to install Java on your machine. Once you have installed
Java on your machine, you would need to set environment variables to point to correct installation directories.
Setting Up the Path for Windows 2000/XP
Assuming you have installed Java in c:\Program Files\java\jdk directory −
 Right-click on 'My Computer' and select 'Properties'.
 Click on the 'Environment variables' button under the 'Advanced' tab.
 Now, edit the 'Path' variable and add the path to the Java executable directory at the end of it. For example,
if the path is currently set to C:\Windows\System32, then edit it the following way
C:\Windows\System32;c:\Program Files\java\jdk\bin
Setting Up the Path for Windows 95/98/ME
Assuming you have installed Java in c:\Program Files\java\jdk directory −
 Edit the 'C:\autoexec.bat' file and add the following line at the end −
SET PATH = %PATH%;C:\Program Files\java\jdk\bin
Setting Up the Path for Linux, UNIX, Solaris, FreeBSD
Environment variable PATH should be set to point to where the Java binaries have been installed. Refer to your
shell documentation if you have trouble doing this.
For example, if you use bash as your shell, then you would add the following line at the end of your .bashrc −
export PATH = /path/to/java:$PATH'
You automatically get both JDBC packages java.sql and javax.sql, when you install J2SE Development Kit.
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-5.1.8-bin.jar. Your driver version may vary based on your installation.
Set Database Credential
When we install MySQL database, its administrator ID is set to root and it gives provision to set a password of your
choice.
Using root ID and password you can either create another user 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 guest as ID and guest123 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 TUTORIALSPOINT 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 mysqld.exe 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 TUTORIALSPOINT database by executing the following command −
C:\Program Files\MySQL\bin> mysqladmin create TUTORIALSPOINT -u guest -p
Enter password: ********
C:\Program Files\MySQL\bin>
Create Table
To create the Employees table in TUTORIALSPOINT 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 the database as follows −
C:\Program Files\MySQL\bin>mysql -u guest -p
Enter password: ********
mysql>
Step 3
Create the table Employees as follows −
mysql> use TUTORIALSPOINT;
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>

JDBC - Database Connections

After you've installed the appropriate driver, it is time to establish a database connection using JDBC.
The programming involved to establish a JDBC connection is fairly simple. Here are these simple four steps −
 Import JDBC Packages − Add import statements to your Java program to import required classes in your Java
code.
 Register JDBC Driver − This step causes the JVM to load the desired driver implementation into memory so it
can fulfill your JDBC requests.
 Database URL Formulation − This is to create a properly formatted address that points to the database to
which you wish to connect.
  Create Connection Object − Finally, code a call to the DriverManager object's getConnection( ) method to
establish actual database connection.
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
Register JDBC Driver
You must register the driver in your program before you use it. Registering the driver is the process by which the
Oracle driver's class file is loaded into the memory, so it can be utilized as an implementation of the JDBC
interfaces.
You need to do this registration only once in your program. You can register a driver in one of two ways.
Approach I - Class.forName()
The most common approach to register a driver is to use Java's Class.forName() method, to dynamically load the
driver's class file into memory, which automatically registers it. This method is preferable because it allows you to
make the driver registration configurable and portable.
The following example uses Class.forName( ) to register the Oracle driver −
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
}
catch(ClassNotFoundException ex) {
System.out.println("Error: unable to load driver class!");
System.exit(1);
}
You can use getInstance() method to work around noncompliant JVMs, but then you'll have to code for two extra
Exceptions as follows −
try {
Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
}
catch(ClassNotFoundException ex) {
System.out.println("Error: unable to load driver class!");
System.exit(1);
catch(IllegalAccessException ex) {
System.out.println("Error: access problem while loading!");
System.exit(2);
catch(InstantiationException ex) {
System.out.println("Error: unable to instantiate driver!");
System.exit(3);
}
Approach II - DriverManager.registerDriver()
The second approach you can use to register a driver, is to use the static DriverManager.registerDriver() method.
You should use the registerDriver() method if you are using a non-JDK compliant JVM, such as the one provided by
Microsoft.
The following example uses registerDriver() to register the Oracle driver −
try {
Driver myDriver = new oracle.jdbc.driver.OracleDriver();
 DriverManager.registerDriver( myDriver );
}
catch(ClassNotFoundException ex) {
System.out.println("Error: unable to load driver class!");
System.exit(1);
}
Database URL Formulation
After you've loaded the driver, you can establish a connection using the DriverManager.getConnection() method.
For easy reference, let me list the three overloaded DriverManager.getConnection() methods −
 getConnection(String url)
 getConnection(String url, Properties prop)
 getConnection(String url, String user, String password)
Here each form requires a database URL. A database URL is an address that points to your database.
Formulating a database URL is where most of the problems associated with establishing a connection occurs.
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

ORACLE oracle.jdbc.driver.OracleDriver jdbc:oracle:thin:@hostname:port

Number:databaseName

DB2 COM.ibm.db2.jdbc.net.DB2Driver jdbc:db2:hostname:port

Number/databaseName

Sybase com.sybase.jdbc.SybDriver jdbc:sybase:Tds:hostname: port

Number/databaseName
All the highlighted part in URL format is static and you need to change only the remaining part as per your
database setup.
Create Connection Object
We have listed down three forms of DriverManager.getConnection() method to create a connection object.
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 −
Assuming you are using Oracle's thin driver, you'll specify a host:port:databaseName value for the database
portion of the URL.
If you have a host at TCP/IP address 192.0.0.1 with a host name of amrood, and your Oracle listener is configured
to listen on port 1521, and your database name is EMP, then complete database URL would be −
jdbc:oracle:thin:@amrood:1521:EMP
Now you have to call getConnection() method with appropriate username and password to get
a Connection object as follows −
String URL = "jdbc:oracle:thin:@amrood:1521:EMP";
String USER = "username";
String PASS = "password"
Connection conn = DriverManager.getConnection(URL, USER, PASS);
Using Only a Database URL
A second form of the DriverManager.getConnection( ) method requires only a database URL −
DriverManager.getConnection(String url);
However, in this case, the database URL includes the username and password and has the following general form
−
jdbc:oracle:driver:username/password@database
So, the above connection can be created as follows −
String URL = "jdbc:oracle:thin:username/password@amrood:1521:EMP";
Connection conn = DriverManager.getConnection(URL);
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.
To make the same connection made by the previous examples, use the following code −
import java.util.*;

String URL = "jdbc:oracle:thin:@amrood:1521:EMP";

Properties info = new Properties( );
info.put( "user", "username" );
info.put( "password", "password" );

Connection conn = DriverManager.getConnection(URL, info);

Closing JDBC Connections
At the end of your JDBC program, it is required explicitly to close all the connections to the database to end each
database session. However, if you forget, Java's garbage collector will close the connection when it cleans up stale
objects.
Relying on the garbage collection, especially in database programming, is a very poor programming practice. You
should make a habit of always closing the connection with the close() method associated with connection object.
To ensure that a connection is closed, you could provide a 'finally' block in your code. A finally block always
executes, regardless of an exception occurs or not.
To close the above opened connection, you should call close() method as follows −
conn.close();
Explicitly closing a connection conserves DBMS resources, which will make your database administrator happy.
For a better understanding, we suggest you to study our JDBC - Sample Code tutorial.

JDBC – ResultSet Interface

The SQL statements that read data from a database query, return the data in a result set. The SELECT statement is
the standard way to select rows from a database and view them in a result set. The java.sql.ResultSet interface
represents the result set of a database query.
A ResultSet object maintains a cursor that points to the current row in the result set. The term "result set" refers
to the row and column data contained in a ResultSet object.
The methods of the ResultSet interface can be broken down into three categories −
 Navigational methods − Used to move the cursor around.
 Get methods − Used to view the data in the columns of the current row being pointed by the cursor.
 Update methods − Used to update the data in the columns of the current row. The updates can then be
updated in the underlying database as well.
The cursor is movable based on the properties of the ResultSet. These properties are designated when the
corresponding Statement that generates the ResultSet is created.
JDBC provides the following connection methods to create statements with desired ResultSet −
 createStatement(int RSType, int RSConcurrency);
  prepareStatement(String SQL, int RSType, int RSConcurrency);
 prepareCall(String sql, int RSType, int RSConcurrency);
The first argument indicates the type of a ResultSet object and the second argument is one of two ResultSet
constants for specifying whether a result set is read-only or updatable.
Type of ResultSet
The possible RSType are given below. If you do not specify any ResultSet type, you will automatically get one that
is TYPE_FORWARD_ONLY.
Type Description

ResultSet.TYPE_FORWARD_ONLY The cursor can only move

forward in the result set.

ResultSet.TYPE_SCROLL_INSENSITIVE The cursor can scroll

forward and backward, and
the result set is not
sensitive to changes made
by others to the database
that occur after the result
set was created.

ResultSet.TYPE_SCROLL_SENSITIVE. The cursor can scroll

forward and backward, and
the result set is sensitive to
changes made by others to
the database that occur
after the result set was
created.
Concurrency of ResultSet
The possible RSConcurrency are given below. If you do not specify any Concurrency type, you will automatically
get one that is CONCUR_READ_ONLY.
Concurrency Description

ResultSet.CONCUR_READ_ONLY Creates a read-only result set.

This is the default

ResultSet.CONCUR_UPDATABLE Creates an updateable result

set.
All our examples written so far can be written as follows, which initializes a Statement object to create a forward-
only, read only ResultSet object −
try {
Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
}
catch(Exception ex) {
....
}
finally {
....
}
Navigating a Result Set
There are several methods in the ResultSet interface that involve moving the cursor, including −
S.N. Methods & Description

1 public void beforeFirst() throws SQLException

Moves the cursor just before the first row.

2 public void afterLast() throws SQLException

Moves the cursor just after the last row.

3 public boolean first() throws SQLException

Moves the cursor to the first row.

4 public void last() throws SQLException

Moves the cursor to the last row.

5 public boolean absolute(int row) throws SQLException

Moves the cursor to the specified row.

6 public boolean relative(int row) throws SQLException

Moves the cursor the given number of rows forward or
backward, from where it is currently pointing.

7 public boolean previous() throws SQLException

Moves the cursor to the previous row. This method returns
false if the previous row is off the result set.

8 public boolean next() throws SQLException

Moves the cursor to the next row. This method returns false
if there are no more rows in the result set.

9 public int getRow() throws SQLException

Returns the row number that the cursor is pointing to.

10 public void moveToInsertRow() throws SQLException

Moves the cursor to a special row in the result set that can
be used to insert a new row into the database. The current
cursor location is remembered.

11 public void moveToCurrentRow() throws SQLException

Moves the cursor back to the current row if the cursor is
currently at the insert row; otherwise, this method does
nothing
Viewing a Result Set
The ResultSet interface contains dozens of methods for getting the data of the current row.
There is a get method for each of the possible data types, and each get method has two versions −
 One that takes in a column name.
 One that takes in a column index.
For example, if the column you are interested in viewing contains an int, you need to use one of the getInt()
methods of ResultSet −
S.N. Methods & Description

1 public int getInt(String columnName) throws SQLException

 Returns the int in the current row in the column named
columnName.

2 public int getInt(int columnIndex) throws SQLException

Returns the int in the current row in the specified column
index. The column index starts at 1, meaning the first
column of a row is 1, the second column of a row is 2, and
so on.
Similarly, there are get methods in the ResultSet interface for each of the eight Java primitive types, as well as
common types such as java.lang.String, java.lang.Object, and java.net.URL.
There are also methods for getting SQL data types java.sql.Date, java.sql.Time, java.sql.TimeStamp, java.sql.Clob,
and java.sql.Blob. Check the documentation for more information about using these SQL data types.
For a better understanding, let us study Viewing - Example Code.
Updating a Result Set
The ResultSet interface contains a collection of update methods for updating the data of a result set.
As with the get methods, there are two update methods for each data type −
 One that takes in a column name.
 One that takes in a column index.
For example, to update a String column of the current row of a result set, you would use one of the following
updateString() methods −
S.N. Methods & Description

1 public void updateString(int columnIndex, String s) throws

SQLException
Changes the String in the specified column to the value of s.

2 public void updateString(String columnName, String s)

throws SQLException
Similar to the previous method, except that the column is
specified by its name instead of its index.
There are update methods for the eight primitive data types, as well as String, Object, URL, and the SQL data types
in the java.sql package.
Updating a row in the result set changes the columns of the current row in the ResultSet object, but not in the
underlying database. To update your changes to the row in the database, you need to invoke one of the following
methods.
S.N. Methods & Description

1 public void updateRow()

Updates the current row by updating the corresponding row
in the database.

2 public void deleteRow()

Deletes the current row from the database

3 public void refreshRow()

Refreshes the data in the result set to reflect any recent
changes in the database.

4 public void cancelRowUpdates()

 Cancels any updates made on the current row.

5 public void insertRow()

Inserts a row into the database. This method can only be
invoked when the cursor is pointing to the insert row.

Steps for developing JDBC Application

1. Load and register Driver Class
2. Establish Connection between Java Application and Database
3. Create Statement Object
4. Send and execute SQL Query
5. Process Result from ResultSet
6. Close Connection
1. Load and register Driver Class
JDBC API is a Set of Interfaces defined by Java Vendor. Database Vendor is responsible to provide Implementation.
This Group of Implementation Classes is nothing but “Driver Software”. We have to make this Driver Software
available to our Java Program. For this we have to place corresponding Jar File in the Class Path.
Type-1 Driver is available as the Part of JDK and hence we are not required to set any Class Path explicitly. Every
Driver Software is identified by some special Class, which is nothing but Driver Class. For Type-1 Driver, the
corresponding Driver Class Name is
sun.jdbc.odbc.JdbcOdbcDriver
We can load any Java Class by using Class.forName() Method. Hence by using the same Method we can load Driver
Class.
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Whenever we are loading Driver Class automatically Static Block present in that Driver Class will be executed.
class JdbOdbcDriver {
static {
JdbOdbcDriver driver = new JdbOdbcDriver();
DriverManager.registerDriver(driver);
}
}
Because of this Static Block, whenever we are loading automatically registering with DriverManager will be
happened. Hence we are not required to perform this activity explicitly. If we want to register explicitly without
using Class.forName() then we can do as follows by using registerDriver() Method of DriverManager Class.
JdbOdbcDriver driver = new JdbOdbcDriver();
DriverManager.registerDriver(driver);
Note
From JDBC 4.0 V (Java 1.6 V) onwards Driver Class will be loaded automatically from Class Path and we are not
required to perform this step explicitly.
2: Establish Connection between Java Application and Database
Once we loaded and registered Driver, by using that we can establish Connection to the Database. For this
DriverManager Class contains getConnection() Method.
public static Connection getConnection(String jdbcurl, String username, String pwd) throws SQLException
Example
Connection conn = DriverManager.getConnection(jdbcurl,username,pwd);
Here “Jdbcurl” represents URL of the Database. username and pwd are Credentials to connect to the Database.
JDBC URL Syntax
For Type-1 Driver, JDBC URL is jdbc:odbc:demodsn
E.g
Connection con = DriverManager.getConnection("jdbc:odbc:demodsn","scott","tiger");
Note
 DriverManager will use Driver Class internally to connect with Database.
 DriverManager Class getConnection() Method internally calls Driver Class connect() Method.
DSN (Data Source Name) for Type-1 Driver
Internally Type-1 Driver uses ODBC Driver to connect with Database.

ODBC Driver needs Database Name & its Location to connect with Database. ODBC Driver collect this Information
from DSN i.e. internally ODBC Driver will use DSN to get Database Information (DSN Concept applicable only for
Type-1 Driver). There are 3 Types of DSN
1. User DSN
It is the non-sharable DSN and available only for Current User.
2. System DSN
It is the sharable DSN and it is available for all Users who can access that System. It is also known as Global DSN.
3. File DSN
It is exactly same as User DSN but will be stored in a File with .dsn Extension.
Program To Establish Connection To The Oracle Database By Using Type-1 Driver?
import java.sql.*;
/**
*
* @author ashok.mariyala
*
*/
public class DbConnectDemo {
public static void main(String[] args) throws Exception {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con=DriverManager.getConnection("jdbc:odbc:demodsn","scott","tiger");
if(con != null) {
System.out.println("Connection established Successfully");
} else {
System.out.println("Connection not established");
}
}
}
In the above Program Class.forName() is Optional, because from JDBC] 4.0V/ Java 1.6V on wards Driver Class will
be loaded automatically from the Class Path based on “jdbcurl”.
To Compile and Run above Program we are not required to Place/Set any Jar File in the Class Path, because Type-1
Driver is available by default as the Part of JDK.
Connection is an interface, then how we can get Connection Object?
We are not getting Connection Object and we are getting its Implementation Class Object. This Implementation
Class is available as the Part of Driver Software. Driver Software Vendor is responsible to provide Implementation
Class. We can print corresponding Class Name as follows
System.out.println(conn.getClass().getName());
Output
sun.jdbc.odbc.JdbcOdbcConnection
Advantage Of Using Interface Names In Our Application Instead Of Using Implementation Class Names
Interface Reference can be used to hold implemented Class Object. This Property is called Polymorphism.
Connection ➔ sun.jdbc.odbc.JdbcOdbcConnection ➔ Type-1
Connection ➔ orcale.jdbc.OracleT4Connection ➔ Type-2
In JDBC Programs, Interface Names are fixed and these are provided by JDBC API. But Implementation Classes are
provided by Driver Software Vendor and these Names are varied from Vendor to Vendor. If we Hard Code Vendor
provided Class Names in our Program then the Program will become Driver Software Dependent and won’t work
for other Drivers.
If we want to change Driver Software then Total Program has to rewrite once again, which is difficult. Hence it is
always recommended to use JDBC API provided Interface Names in our Application.
3. Creation of Statement Object
Once we established Connection between Java Application and Database, we have to prepare SQL Query and we
have to send that Query to the Database. Database Engine will execute that Query and send Result to Java
Application.
To send SQL Query to the Database and to bring Results from Database to Java Application some Vehicle must be
required, which is nothing but Statement Object. We can create Statement Object by
using createStatement() Method of Connection Interface.
Example
Statement st = con.createStatement();
4: Prepare, Send and Execute SQL Query
According to Database Specification, all SQL Commands are divided into following Types
1. DDL (Data Definition Language) Commands
E.g: Create Table, Alter Table, Drop Table Etc
2. DML (Data Manipulation Language) Commands
E.g: Insert, Delete, Update
3. DQL (Data Query Language) Commands
E.g: Select
4. DCL (Data Control Language) Commands
E.g: Alter Password, Grant Access Etc..
5. Data Administration Commands
E.g: Start Audit
Stop Audit
6. Transactional Control Commands
Commit, Rollback, Savepoint Etc
According to Java Developer Point of View, all SQL Operations are divided into 2 Types.
1. Select Operations (DQL)
2. Non-Select Operations (DML, DDL Etc)
Once we create Statement Object, we can call the following Methods on that Object to execute our Queries.
1. executeQuery()
2. executeUpdate()
3. execute()
1. executeQuery() Method
We can use this Method for Select Operations. Because of this Method Execution, we will get a Group of Records,
which are represented by ResultSet Object. Hence the Return Type of this Method is ResultSet.
E.g:
ResultSet rs = st.executeQuery("select * from emp");
2. executeUpdate() Method
We can use this Method for Non-Select Operations (Insert|Delete|Update). Because of this Method Execution, we
won’t get a Group of Records and we will get a Numeric Value represents the Number of Rows effected. Hence
Return Type of this Method is int.
E.g
int rowCount = st.executeUpdate("delete from emp where sal<15000");
3. execute() method
We can use this Method for both Select and Non-Select Operations. If we don’t know the Type of Query at the
beginning and it is available dynamically at run time then we should use this execute() Method.
E.g
boolean b = st.execute("dynamically provided query");
if(b==true)//select query {
ResultSet rs=st.getResultSet(); //use rs to get data
} 8) else// non-select query {
int rowCount=st.getUpdateCount();
}
executeQuery() Vs executeUpdate() Vs execute()
 If we know the Type of Query at the beginning and it is always Select Query then we should use
“executeQuery() Method”.
 If we know the Type of Query at the beginning and it is always Non-Select Query then we should use
executeUpdate() Method.
 If we don’t know the Type of SQL Query at the beginning and it is available dynamically at Runtime (May be
from Properties File OR From Command Prompt Etc) then we should go for execute() Method.
Note
Based on our Requirement we have to use corresponding appropriate Method.
 st.executeQuery();
 st.executeUpdate();
 st.execute();
 st.getResultSet();
 st.getUpdateCount();
Case 1 : executeQuery() Vs Non-Select Query
Usually we can use executeQuery() Method for Select Queries. If we use for Non-Select Queries then we cannot
expect exact Result. It is varied from Driver to Driver.
E.g
ResultSet rs = st.executeQuery("delete from emp where sal<15000");
For Type-1 Driver we will get SQLException. But for Type-4 Driver we won’t get any Exception and Empty ResultSet
will be returned.
Case 2: executeUpdate() Vs Select Query
Usually we can use executeUpdate() Method for Non-Select Queries. But if we use for Select Queries then we
cannot expect the Result and it is varied from Driver to Driver.
E.g
int rowCount = st.executeUpdate("select * from emp");
For Type-1 Driver we will get SQLException where as for Type-4 Driver we won’t get any Exception and simply
returns the Number of Rows selected.
Case 3: executeUpdate() Vs DDL Queries
If we use executeUpdate() Method for DDL Queries like Create Table, Alter Table, Drop Table Etc, then Updated
Record Count is not applicable. The Result is varied from Driver to Driver.
E.g

int rowCount = st.executeUpdate("create table emp(eno number,ename varchar2(20)");

For Type-1 Driver, we will get -1 and For Type-4 Driver, we will get 0.
5. Process Result from ResultSet
After executing Select Query, Database Engine will send Result back to Java Application. This Result is available in
the form of ResultSet.
i.e., ResultSet holds Result of executeQuery() Method, which contains a Group of Records. By using ResultSet we
can get Results. ResultSet is a Cursor always locating Before First Record (BFR). To check whether the next Record
is available OR not, we have to use rs.next() Method.This Method Returns True if the next Record is available,
otherwise returns False.
If next Record is available then we can get Data from that Record by using the following Getter Methods.
1. getXxx(String columnName)
2. getXxx(int columnIndex)
Like getInt(), getDouble(), getString() etc.
Note
In JDBC, Index is always one based but not Zero based i.e. Index of First Column is 1 but not 0.
while(rs.next()) {

System.out.println(rs.getInt("eno")+".."+rs.getString("ename")+".."+rs.getDouble("sal")+".."+rs.getString("Addr"));
OR
System.out.println(rs.getInt(1)+".."+rs.getString(2)+".."+rs.getDouble(3)+".."+rs.getString(4));
}
Readability wise it is recommended to use Column Names, but Performance wise it is recommended to use
Column Index. (Because comparing Numbers is very easy than comparing String Values). Hence if we are handling
very large Number of Records then it is highly recommended to use Index.
If we know Column Name then we can find corresponding Index as follows.
int columnIndex = rs.findColumn(String columnName);
Conclusions
1. ResultSet follows “Iterator” Design Pattern.
2. ResultSet Object is always associated with Statement Object.
3. Per Statement only one ResultSet is possible at a time. if we are trying to open another ResultSet then
automatically first ResultSet will be closed.
E.g
Statement st = con.createStatement();
ResultSet rs1 = st.executeQuery("select * from emp");
ResultSet rs2 = st.executeQuery("select * from student");
In the above Example rs1 will be closed automatically whenever we are trying to open rs2.
6. Close the Connection
After completing Database Operations it is highly recommended to close the Resources whatever we opened in
reverse order of opening.
1.rs.close();
It closes the ResultSet and won’t allow further processing of ResultSet
2. st.close();
It closes the Statement and won’t allow sending further Queries to the Database.
3. conn.close();
It closes the Connection and won’t allow for further Communication with the Database.
Conclusions
 Per Statement only one ResultSet is possible at a time.
 Per Connection multiple Statement Objects are possible.
 Whenever we are closing Statement Object then automatically the corresponding ResultSet will be closed.
 Similarly, whenever we are closing Connection Object automatically corresponding Statement Objects will
be closed.
 Hence we required to use only con.close();