|
JDBC Interview Questions and Answers
Can ResultSets be passed between methods of a class?
Are there any special usage
Yes. There is no reason that a ResultSet can't be used
as a method parameter just like any other object
reference. You must ensure that access to the ResultSet
is synchronized. This should not be a problem is the
ResultSet is a method variable passed as a method
parameter - the ResultSet will have method scope and
multi-thread access would not be an issue.
As an example, say you have several methods that obtain
a ResultSet from the same table(s) and same columns, but
use different queries. If you want these ResultSets to
be processed the same way, you would have another method
for that. This could look something like:
public List getStudentsByLastName(String lastName) {
ResultSet rs = ... (JDBC code to retrieve students by
last name);
return processResultSet(rs);
}
public List getStudentsByFirstName(String firstName) {
ResultSet rs = ... (JDBC code to retrieve students by
first name);
return processResultSet(rs);
}
private List processResultSet(ResultSet rs) {
List l = ... (code that iterates through ResultSet to
build a List of Student objects);
return l;
}
Since the ResultSet always has method scope -
sychronization is never an issue.
1. There is only one ResultSet. Dont assume that the
ResultSet is at the start (or in any good state...) just
because you received it as a parameter. Previous
operations involving the ResultSet will have had the
side-effect of changing its state.
2. You will need to be careful about the order in which
you close the ResultSet and CallableStatement/PreparedStatement/etc
From my own experience using the Oracle JDBC drivers and
CallableStatements the following statements are true:
* If you close the CallableStatement the ResultSet
retrieved from that CallableStatement immediately goes
out-of-scope.
* If you close the ResultSet without reading it fully,
you must close the CallableStatement or risk leaking a
cursor on the database server.
* If you close the CallableStatement without reading
it's associated ResultSet fully, you risk leaking a
cursor on the database server.
No doubt, these observations are valid only for Oracle
drivers. Perhaps only for some versions of Oracle
drivers.
The recommended sequence seems to be:
* Open the statement
* Retrieve the ResultSet from the statement
* Read what you need from the ResultSet
* Close the ResultSet
* Close the Statement
How can I convert a java array to a java.sql.Array?
A Java array is a first class object and all of the
references basically use PreparedStatement.setObject()
or ResultSet.updateObject() methods for putting the
array to an ARRAY in the database. Here's a basic
example:
String[] as = { "One", "Two", "Three" };
...
PreparedStatement ps = con.prepareStatement(
"UPDATE MYTABLE SET ArrayNums = ? WHERE MyKey = ?" );
...
ps.setObject( 1, as );
Could we get sample code for retrieving more than one
parameter from a stored procedure?
Assume we have a stored procedure with this signature:
MultiSP (IN I1 INTEGER, OUT O1 INTEGER, INOUT IO1
INTEGER)
The code snippet to retrieve the OUT and INOUT
parameters follows:
CallableStatement cs = connection.prepareCall( "(CALL
MultiSP(?, ?, ?))" );
cs.setInt(1, 1); // set the IN parm I1 to 1
cs.setInt(3, 3); // set the INOUT parm IO1 to 3
cs.registerOutParameter(2, Types.INTEGER); // register
the OUT parm O1
cs.registerOutParameter(3, Types.INTEGER); // register
the INOUT parm IO1
cs.execute();
int iParm2 = cs.getInt(2);
int iParm3 = cs.getInt(3);
cs.close();
The code really is just additive; be sure that for each
IN parameter that setXXX() is called and that for each
INOUT and OUT parameter that registerOutParameter() is
called.
What is the difference between client and server
database cursors?
What you see on the client side is the current row of
the cursor which called a Result (ODBC) or ResultSet
(JDBC). The cursor is a server-side entity only and
remains on the server side.
How can I pool my database connections so I don't have
to keep reconnecting to the database?
There are plenty of connection pool implementations
described in books or availalble on the net. Most of
them implement the same model. The process is always the
same :
* you gets a reference to the pool
* you gets a free connection from the pool
* you performs your different tasks
* you frees the connection to the pool
Since your application retrieves a pooled connection,
you don't consume your time to connect / disconnect from
your data source. You can find some implementation of
pooled connection over the net, for example:
* Db Connection Broker (http://www.javaexchange.com/), a
package quite stable ( I used it in the past to pool an
ORACLE database on VMS system)
You can look at the JDBC 2.0 standard extension API
specification from SUN which defines a number of
additional concepts.
Page Numbers :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Have a Question ?
post your questions here. It
will be answered as soon as possible.
Check
Java Interview
Questions for more Java Interview Questions with answers
Check
Structs Interview
Questions for more Structs Interview Questions with answers
Check
Servlet Interview
Questions for more Servlet Interview Questions with answers
|
