1.What
are the steps involved in establishing a JDBC connection?
Ans: This action involves
two steps: loading the JDBC driver and making the connection.
2. How
can you load the drivers?
Ans: 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?
Ans: 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?
Ans: 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?
Ans: 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?
Ans: 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?
Ans: Regular statement (use
createStatement method), prepared statement (use prepareStatement method) and
callable statement (use prepareCall)
8.How
can you use PreparedStatement?
Ans: 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.
9.PreparedStatement
updateSales =con.prepareStatement("UPDATE
COFFEES SET SALES = ? WHERE COF_NAME LIKE ?");
11.What
does setAutoCommit do?
Ans: 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);
12.How
do you call a stored procedure from JDBC?
Ans: 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.
13.CallableStatement
cs = con.prepareCall("{call SHOW_SUPPLIERS}");
14.ResultSet
rs = cs.executeQuery();
15.How
do I retrieve warnings?
Ans: 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:
16. 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();
}
}
17 How
can you move the cursor in scrollable result sets?
Ans: 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.
18. What’s
the difference between TYPE_SCROLL_INSENSITIVE , and TYPE_SCROLL_SENSITIVE?
Ans: 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:
32. Statement
stmt =
33. con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
34. ResultSet
srs =
35. stmt.executeQuery("SELECT
COF_NAME, PRICE FROM COFFEES");
36. srs.afterLast();
37. while
(srs.previous())
38. {
39. String
name = srs.getString("COF_NAME");
40. float
price = srs.getFloat("PRICE");
41. System.out.println(name
+ " " + price);
42. }
43. How
to Make Updates to Updatable Result Sets?
Ans: 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.
44. Connection
con =
45. DriverManager.getConnection("jdbc:mySubprotocol:mySubName");
46. Statement
stmt =
47. con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
48. ResultSet
uprs =
stmt.executeQuery("SELECT
COF_NAME, PRICE FROM COFFEES");
