|
JDBC interview questions
1.What are the
steps involved in establishing a JDBC connection? This
action involves two steps: loading the JDBC driver and
making the connection.
2.How can you load the drivers?
Loading the
driver or drivers you want to use is very simple and
involves just one line of code. If, for example, you want to
use the JDBC-ODBC Bridge driver, the following code will
load it:
Class.forName(”sun.jdbc.odbc.JdbcOdbcDriver”);
Your driver documentation will give you the class name to
use. For instance, if the class name is jdbc.DriverXYZ, you
would load the driver with the following line of code:
Class.forName(”jdbc.DriverXYZ”);
3.What will Class.forName do while loading drivers? It is
used to create an instance of a driver and register it with
the
DriverManager. When you have loaded a driver, it is
available for making a connection with a DBMS.
4.How can you make the connection? To establish a connection
you need to have the appropriate driver connect to the DBMS.
The following line of code illustrates the general idea:
String url = “jdbc:odbc:Fred”;
Connection con = DriverManager.getConnection(url, “Fernanda”,
“J8?);
5.How can you create JDBC statements and what are they?
A Statement object is what sends your SQL statement to the
DBMS. You simply create a Statement object and then execute
it, supplying the appropriate execute method with the SQL
statement you want to send. For a SELECT statement, the
method to use is executeQuery. For statements that create or
modify tables, the method to use is executeUpdate. It takes
an instance of an active connection to create a Statement
object. In the following example, we use our Connection
object con to create the Statement object
Statement stmt = con.createStatement();
6.How can you retrieve data from the ResultSet?
JDBC returns results in a ResultSet object, so we need to
declare an instance of the class ResultSet to hold our
results. The following code demonstrates declaring the
ResultSet object rs.
ResultSet rs = stmt.executeQuery(”SELECT COF_NAME, PRICE
FROM COFFEES”);
String s = rs.getString(”COF_NAME”);
The method getString is invoked on the ResultSet object rs,
so getString() will retrieve (get) the value stored in the
column COF_NAME in the current row of rs.
7.What are the different types of Statements?
Regular statement (use createStatement method), prepared
statement (use prepareStatement method) and callable
statement (use prepareCall)
8.How can you use PreparedStatement? This special type of
statement is derived from class Statement.If you need a
Statement object to execute many times, it will normally
make sense to use a PreparedStatement object instead. The
advantage to this is that in most cases, this SQL statement
will be sent to the DBMS right away, where it will be
compiled. As a result, the PreparedStatement object contains
not just an SQL statement, but an SQL statement that has
been precompiled. This means that when the PreparedStatement
is executed, the DBMS can just run the PreparedStatement’s
SQL statement without having to compile it first.
PreparedStatement updateSales =
con.prepareStatement(\"UPDATE COFFEES SET SALES = ? WHERE
COF_NAME LIKE ?\");
9.What does setAutoCommit do?
When a connection is created, it is in auto-commit mode.
This means that each individual SQL statement is treated as
a transaction and will be automatically committed right
after it is executed. The way to allow two or more
statements to be grouped into a transaction is to disable
auto-commit mode:
con.setAutoCommit(false);
Once auto-commit mode is disabled, no SQL statements will be
committed until you call the method commit explicitly.
con.setAutoCommit(false);
PreparedStatement updateSales =
con.prepareStatement( \"UPDATE COFFEES SET SALES = ? WHERE
COF_NAME LIKE ?\");
updateSales.setInt(1, 50); updateSales.setString(2,
\"Colombian\");
updateSales.executeUpdate();
PreparedStatement updateTotal =
con.prepareStatement(\"UPDATE COFFEES SET TOTAL = TOTAL + ?
WHERE COF_NAME LIKE ?\");
updateTotal.setInt(1, 50);
updateTotal.setString(2, \"Colombian\");
updateTotal.executeUpdate();
con.commit();
con.setAutoCommit(true);
10.How do you call a stored procedure from JDBC?
The first step is to create a CallableStatement object. As
with Statement an and PreparedStatement objects, this is
done with an open
Connection object. A CallableStatement object contains a
call to a stored procedure.
CallableStatement cs = con.prepareCall(\"{call
SHOW_SUPPLIERS}\");
ResultSet rs = cs.executeQuery();
11.How do I retrieve warnings?
SQLWarning objects are a subclass of SQLException that deal
with database access warnings. Warnings do not stop the
execution of an
application, as exceptions do; they simply alert the user
that something did not happen as planned. A warning can be
reported on a
Connection object, a Statement object (including
PreparedStatement and CallableStatement objects), or a
ResultSet object. Each of these
classes has a getWarnings method, which you must invoke in
order to see the first warning reported on the calling
object:
SQLWarning warning = stmt.getWarnings();
if (warning != null)
{
System.out.println(\"n---Warning---n\");
while (warning != null)
{
System.out.println(\"Message: \" + warning.getMessage());
System.out.println(\"SQLState: \" + warning.getSQLState());
System.out.print(\"Vendor error code: \");
System.out.println(warning.getErrorCode());
System.out.println(\"\");
warning = warning.getNextWarning();
}
}
12.How can you move the cursor in scrollable result sets?
One of the new features in the JDBC 2.0 API is the ability
to move a result set’s cursor backward as well as forward.
There are also methods that let you move the cursor to a
particular row and check the position of the cursor.
Statement stmt =
con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
ResultSet srs = stmt.executeQuery(”SELECT COF_NAME, PRICE
FROM COFFEES”);
The first argument is one of three constants added to the
ResultSet API to indicate the type of a ResultSet object:
TYPE_FORWARD_ONLY, TYPE_SCROLL_INSENSITIVE , and
TYPE_SCROLL_SENSITIVE. The second argument is one of two
ResultSet constants for specifying whether a result set is
read-only or updatable: CONCUR_READ_ONLY and
CONCUR_UPDATABLE. The point to remember here is that if you
specify a type, you must also specify whether it is
read-only or updatable. Also, you must specify the type
first, and because both parameters are of type int , the
compiler will not complain if you switch the order.
Specifying the constant TYPE_FORWARD_ONLY creates a
nonscrollable result set, that is, one in which the cursor
moves only forward. If you do not specify any constants for
the type and updatability of a ResultSet object, you will
automatically get one that is TYPE_FORWARD_ONLY and
CONCUR_READ_ONLY.
13.What’s the difference between TYPE_SCROLL_INSENSITIVE ,
and TYPE_SCROLL_SENSITIVE?
You will get a scrollable ResultSet object if you specify
one of these ResultSet constants.The difference between the
two has to do with whether a result set reflects changes
that are made to it while it is open and whether certain
methods can be called to detect these changes. Generally
speaking, a result set that is TYPE_SCROLL_INSENSITIVE does
not reflect changes made while it is still open and one that
is TYPE_SCROLL_SENSITIVE does. All three types of result
sets will make changes visible if they are closed and then
reopened:
Statement stmt =
con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
ResultSet srs =
stmt.executeQuery(\"SELECT COF_NAME, PRICE FROM COFFEES\");
srs.afterLast();
while (srs.previous())
{
String name = srs.getString(\"COF_NAME\");
float price = srs.getFloat(\"PRICE\");
System.out.println(name + \" \" + price);
}
14.How to Make Updates to Updatable Result Sets?
Another new feature in the JDBC 2.0 API is the ability to
update rows in a result set using methods in the Java
programming language rather than having to send an SQL
command. But before you can take advantage of this
capability, you need to create a ResultSet object that is
updatable. In order to do this, you supply the ResultSet
constant CONCUR_UPDATABLE to the createStatement method.
Connection con =
DriverManager.getConnection(\"jdbc:mySubprotocol:mySubName\");
Statement stmt =
con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
ResultSet uprs =
stmt.executeQuery(\"SELECT COF_NAME, PRICE FROM COFFEES\");
|
|
