JDBC : The Steps
In
this article we are highlighting the steps to connect and process data in JDBC.
For this article we are using Oracle Database. For other databases the drivers
will vary but the steps will remain the same.
The
steps involved in the process of connecting to a database and executing a query
are as follows:
·
Load and register
the JDBC driver.
·
Open a connection
to the database.
·
Create a
statement object to perform a query.
·
Execute the
statement object and return a query resultset.
·
Process the
resultset.
·
Close the
resultset and statement objects.
·
Close the
connection.
Load and Register the JDBC Driver
The
first step is to establish a communication between the JDBC program and the
database. This is done by using the static registerDriver() method of the
DriverManager class of the JDBC API.
DriverManager.registerDriver(new
oracle.jdbc.driver.OracleDriver());
|
Alternatively,
we can use the forName() method of the java.lang.Class class can be used to
load and register the JDBC driver:
Class.forName("oracle.jdbc.driver.OracleDriver");
|
Connecting to a Database
Once
the JDBC driver has been loaded and registered, a database connection needs be
established. This is done by using the getConnection() method of the
DriverManager class. A call to this method creates an object instance of the
java.sql.Connection class. The getConnection() requires three input parameters,
namely, a connect string, a username, and a password.
Connection conn =
DriverManager.getConnection(URL, username, passwd);
|
Connection conn =
DriverManager.getConnection
("jdbc:oracle:thin:@training:1521:Oracle",
"oratest", "oratest");
|
Querying the Database
Querying
the database involves two steps:
·
Creating a
statement object to perform a query
·
Executing the
query and returning a resultset.
Creating
a Statement Object
The
next step is to instantiate objects that run the query against the database
connected. This is done by the createStatement() method of the Connection
object created above. A call to this method creates an object instance of the
Statement
Statement stmt =
conn.createStatement();
|
Executing
the Query and Returning a ResultSet
Once
a Statement object has been constructed, the next step is to execute the query.
This is done by using the executeQuery() method of the Statement object. A call
to this method takes as parameter a SQL SELECT statement and returns a JDBC
ResultSet object.
ResultSet rset = stmt.executeQuery
("SELECT empno, ename, sal, deptno FROM emp ");
|
Processing the Results of a Database
Query That Returns Multiple Rows
Once
the query has been executed, there are two steps to be carried out:
After
the query execution we need to perform 2 things
·
Process the
output resultset to fetch the rows
·
Retrievethe
column values of the current row
The
first step is using the next() method of the ResultSet object. A call to next()
is executed in a loop to fetch the rows one row at a time, with each call to
next() advancing the control to the next available row.
The
second step is to fetch the values in the columnsby using the getXXX() methods
of the JDBC rset object. Here getXXX() corresponds to the getInt(), getString()
etc with XXX being replaced by a Java datatype.
String str;
while (rset.next())
{
str = rset.getInt(1)+ " "+
rset.getString(2)+ "
"+rset.getFloat(3)+ "
"rset.getInt(4)+ "\n";
}
|
Here
1, 2, 3, and 4 in rset.getInt(), rset.getString(), getFloat(), and getInt()
respectively denote the position of the columns in the SELECT statement, that
is, the first column empno, second column ename, third column sal, and fourth
column deptno of the SELECT statement respectively.
The
parameters for the getXXX() methods can be specified by position of the
corresponding columns as numbers 1, 2, and so on, or by directly specifying the
column names enclosed in double quotes, as getString("ename") and so
on, or a combination of both.
Closing the ResultSet and Statement
Once
the ResultSet and Statement objects have been used, they must be closed.
rset.close();
stmt.close();
|
Closing the Connection
The
last step is to close the database connection which is done by a call to the
close() method.
conn.close();
|
Here
is a complete example of JDBC Select using Type 4 driver and Oracle Database
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBC01 {
public static void main(String[] args) {
try
{
Class.forName("oracle.jdbc.OracleDriver");
}
catch (ClassNotFoundException e) {
e.printStackTrace();
}
try
{
Connection con =DriverManager.getConnection
("jdbc:oracle:thin:@localhost:1521:orcl","scott","tiger");
Statement
s=con.createStatement();
String
query01="select * from emp";
ResultSet
rs=s.executeQuery(query01);
while(rs.next()){
System.out.println(rs.getInt("empno")+"
"+rs.getString("ename")+" "+rs.getDouble("sal"));
}
}
catch (SQLException e) {
e.printStackTrace();
}
}
}
|