2.4 Processing Queries
-
In
general, to process any SQL statement with JDBC, we follow these steps:
-
The
first thing to do, of course, is to install Java, JDBC and the DBMS on wer
working machines.
-
Since
we want to interface with an Oracle database, we would need a driver for this specific
database as well.
-
First,
establish a connection with the data source we want to use.
-
A
data source can be a DBMS.
-
A
legacy file system, or some other source of data.
-
With
a corresponding JDBC driver.
-
This
connection is represented by a
Connection object.
-
As
we said earlier, before database can be accessed, connection must be opened
between our program (client) and the database (server).
-
This
involves two steps:
1) Load the vendor
specific driver
2) Make the
connection
-
1) Load the vendor
specific driver :
§ Why would we
need this step?
§ To ensure
portability and code reuse.
§ the API was
designed to be as independent of the version
§ or the vendor of
a database as possible.
§ Since different
DBMS's have different behavior.
§ we need to tell
the driver manager which DBMS we wish to use.
§ So that it can
invoke (call up) the correct driver.
§ An Oracle driver
is loaded using the following code snippet:
·
Class.forName("oracle.jdbc.driver.OracleDriver");
2) Make the
connection :
§ Once the driver
is loaded.
§ And ready for a connection
to be made.
§ We may create an
instance of a Connection object using:
§ Connection con =
DriverManager.getConnection(
"jdbc:oracle:thin:@dbaprod1:1544:SHR1_PRD",
username, passwd);
§ The first string
is the URL for the database including the:
·
Protocol
(jdbc),
·
The
vendor (oracle),
·
The
driver (thin),
·
The
server (dbaprod1),
·
The
port number (1521), and
·
A
server instance (SHR1_PRD).
§ The username and
passwd are wer username and password.
§ The same as we
would enter into SQL-PLUS to access wer account.
§ In this code
snippet, con is an open connection
§ And we will use
it below.
§ Note: 1 The values
mentioned above would have different values in other environments.
-
A
Statement is an interface
that represents a SQL statement.
-
We
execute
Statement objects, and they
generate ResultSet objects.
-
Which
is a table of data representing a database result set.
-
We
need a
Connection object to create a
Statement object.
-
A
JDBC Statement object is used to
send wer SQL statements to the DBMS.
-
And
should not to be confused with an SQL statement.
-
A
JDBC Statement object is associated
with an open connection.
-
And
not any single SQL Statement.
-
We
think JDBC Statement object as a channel
sitting on a connection.
-
And
passing one or more SQL statements (which we ask it to execute) to the DBMS.
-
An
active connection is needed to create a Statement object.
-
For
example : stmt =
con.createStatement();
-
A
Statement object exists.
-
But
it does not have an SQL statement to pass on to the DBMS.
-
We
learn how to do that in a following section.
-
There are three different kinds of statements:
1)
Statement:
§ Used to
implement simple SQL statements with no parameters.
2)
PreparedStatement: (Extends Statement.)
:
§ Used for
precompiling SQL statements that might contain input parameters.
§ Sometimes, it is
more convenient or efficient to use a PreparedStatement
object for sending SQL statements to DBMS.
§ Main feature is come
from its superclass Statement, is that, it is given
an SQL statement right.
§ When it is created.
§ SQL statement is
then sent to the DBMS right away, where it is compiled.
§ Thus, in effect,
a PreparedStatement is associated
as a channel with a connection.
§ And a compiled
SQL statement.
§ The advantage
offered is that if we need to use the same, or similar query with different
parameters multiple times, the statement can be compiled and optimized by the
DBMS just once.
§ [Contrast
this with a use of a normal Statement.
§ [where
each use of the same SQL statement requires a compilation all over again.]
§ PreparedStatements are also
created with a Connection method.
§ The following
snippet shows how to create a parameterized SQL statement with three input
parameters:
§
PreparedStatement ps = con.prepareStatement( "UPDATE Sells SET price = ? WHERE bar =
? AND book = ?");
§ Before we can
execute a PreparedStatement, we need to
supply values for the parameters.
§ This can be done
by calling one of the setXXX methods defined in the
class PreparedStatement.
§ Most often used
methods are :
·
setInt,
·
setFloat,
·
setDouble,
·
setString etc.
§ We can set these
values before each execution of the prepared statement.
·
ps.setInt(1,
3);
·
ps.setString(2,
"Barfi");
·
ps.setString(3,
"Bluffmaster");
·
etc…
3)
CallableStatement: (Extends PreparedStatement.):
§ Used to execute
stored procedures that may contain both input and output parameters.
-
To execute a query, call an execute
method from Statement.
-
such as :
1)
execute:§ Returns true.
§ If the first
object that the query returns is a
ResultSet object.
§ Use this method
if the query could return one or more
ResultSet
objects.
§ Retrieve the
ResultSet
objects returned from the query.
§ By repeatedly
calling
Statement.getResultSet.2)
executeQuery:
§ Returns one
ResultSet
object.
3)
executeUpdate:
§ Returns an
integer representing the number of rows affected by the SQL statement.
§ Use this method
if we are using
INSERT, DELETE,
or UPDATE SQL statements.
§
For
example : ResultSet rs = stmt.executeQuery(query);
-
We
access the data in a
ResultSet object through a
cursor.
-
Note
that this cursor is not a database cursor.
-
This
cursor is a pointer that points to one row of data in the
ResultSet
object.
-
Initially,
the cursor is positioned before the first row.
-
We
call various methods defined in the
ResultSet object to
move the cursor.
-
For
example:
- try
- {
- stmt = con.createStatement();
- ResultSet rs = stmt.executeQuery(query);
-
- while (rs.next())
- {
- String coffeeName = rs.getString("COF_NAME");
- int supplierID = rs.getInt("SUP_ID");
- float price = rs.getFloat("PRICE");
- int sales = rs.getInt("SALES");
- int total = rs.getInt("TOTAL");
- System.out.println(coffeeName + "\t" + supplierID +
- "\t" + price + "\t" + sales +
- "\t" + total);
- }
- }
- // ...
-
When
we are finished using a
Statement, call the
method Statement.close to
immediately release the resources it is using.
-
When
we call this method, its
ResultSet objects are
closed.
-
Ensures
that the
Statement object is closed at
the end of the method
-
Regardless
of any
SQLException objects thrown,
by wrapping it in a finally block:
-
For
example :
- }
- finally
- {
- if (stmt != null) { stmt.close(); }
- }
-
JDBC
throws an
SQLException when it
encounters an error during an interaction with a data source.
-
JDBC
4.1, which is available in Java SE release 7 and later.
No comments:
Post a Comment