A JDBC driver is the set of classes that implement the JDBC interface for a particular database. There are four different types of JDBC drivers. They are follows:
1. Type 1 dirver : A type 1 driver is a JDBC-ODBC bridge driver. This type of driver enables a client to connect to an ODBC database via Java calls and JDBC neither the database nor middle tier need to be java compliant. However, ODBC binary code must be installed on each client machine that uses this driver.
2. Type 2 dirver : It converts JDBC calls into calls for a specific database. This driver is referred to as a native-API, partly Java dirver. As with the Type 1 driver, binary code may be required on the client machine, which means this type of driver is not suitable for downloading over a network to a client.
3. Type 3 dirver : It is a JDBC-Net pure Java driver, which translates JDBC callls into a database independent net protocol. Vendors of database middleware products can implement this type of driver into their products to provide interoperability with the greatest number fo database servers.
4. Type 4 driver : It is a native protocol, pure java driver converts JDBC calls into the network protocol used by the database directly. A type 4 driver requires no client software, so it’s ideal for deployment to browsers at runtime. Each of these driver types has its own optimal usage scenarios, and will affect the way you deploy a given java application.
What are the steps involved in establishing a JDBC connection? This action involves two steps: loading the JDBC driver and making the connectioion
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”);
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.
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?);
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();
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.
What are the different types of Statements?
Regular statement (use createStatement method), prepared statement (use
prepareStatement method) and callable statement (use prepareCall)
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 ?");
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);
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();
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();
}
}
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.
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);
}
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");
What is the JDBC?
Java Database Connectivity (JDBC) is a standard Java API to interact
with relational databases form Java. JDBC has set of classes
and interfaces which can use from Java application and talk to database without
learning RDBMS details and using Database Specific JDBC Drivers.
What are the new features added to JDBC 4.0?
The major features added in JDBC 4.0 include :
- Auto-loading of JDBC driver class
- Connection management enhancements
- Support for RowId SQL type
- DataSet implementation of SQL using Annotations
- SQL exception handling enhancements
- SQL XML support
Explain Basic Steps in writing a Java program using JDBC? JDBC makes the interaction with RDBMS simple and intuitive. When a Java application needs to access database :
- Load the RDBMS specific JDBC driver because this driver actually communicates with the database (Incase of JDBC 4.0 this is automatically loaded).
- Open the connection to database which is then used to send SQL statements and get results back.
- Create JDBC Statement object. This object contains SQL query.
- Execute statement which returns resultset(s). ResultSet contains the tuples of database table as a result of SQL query.
- Process the result set.
- Close the connection.
What are the main components of JDBC ?
DriverManager: Manages a list of database drivers. Matches connection requests from the java application with the proper database driver using communication subprotocol. The first driver that recognizes a certain subprotocol under JDBC will be used to establish a database Connection.
Driver: The database communications link, handling all
communication with the database. Normally, once the driver is loaded, the
developer need not call it explicitly.
Connection : Interface with all methods for contacting a
database.The connection object represents communication context, i.e., all
communication with database is through connection object only.
Statement : Encapsulates an SQL statement which is passed to
the database to be parsed, compiled, planned and executed.
ResultSet: The ResultSet represents set of rows retrieved due to
query execution.
Exaplain the JDBC Architecture.
The JDBC Architecture consists of two layers:
The JDBC API, which provides the application-to-JDBC Manager connection.
The JDBC Driver API, which supports the JDBC Manager-to-Driver Connection.
The JDBC API uses a driver manager and database-specific drivers to provide
transparent connectivity to heterogeneous databases. The JDBC driver manager
ensures that the correct driver is used to access each data source. The driver
manager is capable of supporting multiple concurrent drivers connected to
multiple heterogeneous databases.
How the JDBC application works?
A JDBC application can be logically divided into two layers:
Driver layer
Application layer
- Driver layer consists of DriverManager class and the available JDBC drivers.
- The application begins with requesting the DriverManager for the connection.
- An appropriate driver is choosen and is used for establishing the connection. This connection is given to the application which falls under the application layer.
- The application uses this connection to create Statement kind of objects, through which SQL commands are sent to backend and obtain the results.
How do I load a database driver with JDBC 4.0 / Java 6?
Provided the JAR file containing the driver is properly configured, just
place the JAR file in the classpath. Java developers NO longer
need to explicitly load JDBC drivers using code like Class.forName() to
register a JDBC driver.The DriverManager class takes care of this by
automatically locating a suitable driver when the DriverManager.getConnection()
method is called. This feature is backward-compatible, so no changes are needed
to the existing JDBC code.
What is JDBC Driver interface?
The JDBC Driver interface provides vendor-specific implementations of the
abstract classes provided by the JDBC API. Each vendor driver must provide
implementations of the java.sql.Connection,Statement,PreparedStatement,
CallableStatement, ResultSet and Driver.
What does the connection object represents?
The connection object represents communication context, i.e., all
communication with database is through connection object only.
What is Statement ?
Statement acts like a vehicle through which SQL commands can be sent.
Through the connection object we create statement kind of objects.
Through the connection object we create statement kind of objects.
Through the connection object we create statement kind of objects.
Statement stmt = conn.createStatement();
This method returns object which implements statement interface.
What is PreparedStatement?
A prepared statement is an SQL statement that is precompiled by the
database. Through precompilation, prepared statements improve the performance
of SQL commands that are executed multiple times (given that the database
supports prepared statements). Once compiled, prepared statements can be
customized prior to each execution by altering predefined SQL parameters.
PreparedStatement pstmt = conn.prepareStatement("UPDATE EMPLOYEES SET
SALARY = ? WHERE ID = ?");
pstmt.setBigDecimal(1, 153833.00);
pstmt.setInt(2, 110592);
Here: conn is an instance of the Connection class and "?" represents
parameters.These parameters must be specified before execution.
What is the difference between a Statement and a PreparedStatement?
Statement
|
PreparedStatement
|
A standard Statement is used to create a Java representation of a literal
SQL statement and execute it on the database.
|
A PreparedStatement is a precompiled statement. This means that
when the PreparedStatement is executed, the RDBMS can just run the
PreparedStatement SQL statement without having to compile it first.
|
Statement has to verify its metadata against the database every time.
|
While a prepared statement has to verify its metadata against the
database only once.
|
If you want to execute the SQL statement once go for STATEMENT
|
If you want to execute a single SQL statement multiple number of times,
then go for PREPAREDSTATEMENT. PreparedStatement objects can be reused with
passing different values to the queries
|
What are callable statements ?
Callable statements are used from JDBC application to invoke stored
procedures and functions.
How to call a stored procedure from JDBC ? PL/SQL stored procedures are called from within JDBC programs by means of the prepareCall() method of the Connection object created. A call to this method takes variable bind parameters as input parameters as well as output variables and creates an object instance of the CallableStatement class.
The following line of code illustrates this:
CallableStatement stproc_stmt = conn.prepareCall("{call
procname(?,?,?)}");
Here conn is an instance of the Connection class.
Does the JDBC-ODBC Bridge support multiple concurrent open statements per
connection?
No. You can open only one Statement object per connection when you are
using the JDBC-ODBC Bridge.
Which is the right type of driver to use and when?
·
Type I driver is handy for prototyping
·
Type III driver adds security, caching, and connection
control
·
Type III and Type IV drivers need no pre-installation
What are the standard isolation levels defined by JDBC?
The values are defined in the class java.sql.Connection and are:
·
TRANSACTION_NONE
·
TRANSACTION_READ_COMMITTED
·
TRANSACTION_READ_UNCOMMITTED
·
TRANSACTION_REPEATABLE_READ
·
TRANSACTION_SERIALIZABLE
Any given database may not support all of these levels.
What is resultset ?
The ResultSet represents set of rows retrieved due to query execution.
ResultSet rs = stmt.executeQuery(sqlQuery);
What are the types of resultsets?
The values are defined in the class java.sql.Connection and are:
·
TYPE_FORWARD_ONLY specifies that a resultset is not
scrollable, that is, rows within it can be advanced only in the forward
direction.
·
TYPE_SCROLL_INSENSITIVE specifies that a resultset is
scrollable in either direction but is insensitive to changes committed by other
transactions or other statements in the same transaction.
·
TYPE_SCROLL_SENSITIVE specifies that a resultset is
scrollable in either direction and is affected by changes committed by other
transactions or statements within the same transaction.
Note: A TYPE_FORWARD_ONLY resultset is always
insensitive.
What’s the difference between
TYPE_SCROLL_INSENSITIVE and TYPE_SCROLL_SENSITIVE?
TYPE_SCROLL_INSENSITIVE
|
TYPE_SCROLL_SENSITIVE
|
An insensitive resultset is like the snapshot of the data in the
database when query was executed.
|
A sensitive resultset does NOT represent a snapshot of data, rather it
contains points to those rows which satisfy the query condition.
|
After we get the resultset the changes made to data are not visible
through the resultset, and hence they are known as insensitive.
|
After we obtain the resultset if the data is modified then such
modifications are visible through resultset.
|
Performance not effected with insensitive.
|
Since a trip is made for every ‘get’ operation, the
performance drastically get affected.
|
What is rowset?
A RowSet is an object that encapsulates a set of rows from either Java
Database Connectivity (JDBC) result sets or tabular data sources like a file or
spreadsheet. RowSets support component-based development models like JavaBeans,
with a standard set of properties and an event notification mechanism.
What are the different types of RowSet ?
There are two types of RowSet are there. They are:
Connected - A connected RowSet object connects to the database once
and remains connected until the application terminates.
Disconnected - A disconnected RowSet object connects to the
database, executes a query to retrieve the data from the database and then
closes the connection. A program may change the data in a disconnected RowSet
while it is disconnected. Modified data can be updated in the database after a
disconnected RowSet reestablishes the connection with the database.
What is the need of BatchUpdates?
The BatchUpdates feature allows us to group SQL statements together and
send to database server in one single trip.
What is a DataSource?
A DataSource object is the representation of a data source in the Java
programming language. In basic terms,
- A DataSource is a facility for storing data.
- DataSource can be referenced by JNDI.
- Data Source may point to RDBMS, file System , any DBMS etc..
What are the advantages of DataSource?
The few advantages of data source are :
- An application does not need to hardcode driver information, as it does with the DriverManager.
- The DataSource implementations can easily change the properties of data sources. For example: There is no need to modify the application code when making changes to the database details.
- The DataSource facility allows developers to implement a DataSource class to take advantage of features like connection pooling and distributed transactions.
What is connection pooling? what is the main advantage of using connection
pooling?
A connection pool is a mechanism to reuse connections created. Connection
pooling can increase performance dramatically by reusing connections rather
than creating a new physical connection each time a connection is requested.
The SELECT statement lets you select a set of values from a table in a
database. The values selected from the database table would depend on the
various conditions that are specified in the SQL query.
| |
SELECT * FROM people WHERE empname LIKE '%ab%'
Would return a recordset with records consisting empname the sequence 'ab' in empname . |
|
What is the INSERT statement?
| |
The INSERT statement lets you insert information into a database.
|
|
Use the DELETE statement to remove records or any particular column
values from a database.
|
|
The SELECT statement in conjunction with DISTINCT lets you select a set
of distinct values from a table in a database. The values selected from the
database table would of course depend on the various conditions that are
specified in the SQL query. Example
SELECT DISTINCT empname FROM emptable |
|
You can sort the results and return the sorted results to your program by
using ORDER BY keyword thus saving you the pain of carrying out the sorting
yourself. The ORDER BY keyword is used for sorting.
SELECT empname, age, city FROM emptable ORDER BY empname |
|
You could use the COUNT keyword , example
SELECT COUNT(*) FROM emp WHERE age>40 |
|
The GROUP BY keywords have been added to SQL because aggregate functions
(like SUM) return the aggregate of all column values every time they are
called. Without the GROUP BY functionality, finding the sum for each
individual group of column values was not possible.
|
|
Dropping : (Table structure + Data are deleted),
Invalidates the dependent objects ,Drops the indexes
Truncating: (Data alone deleted), Performs an automatic
commit, Faster than delete
Delete : (Data alone deleted), Doesn’t perform automatic
commit
|
|
Blob and Clob.
|
|
Having clause is used only with group functions whereas Where is not used
with.
|
|
Both primary key and unique enforce uniqueness of the column on which
they are defined. But by default primary key creates a clustered index on the
column, where are unique creates a nonclustered index by default. Another
major difference is that, primary key doesn't allow NULLs, but unique key
allows one NULL only.
|
|
Cursors allow row-by-row prcessing of the resultsets.
Types of cursors: Static, Dynamic, Forward-only, Keyset-driven. See books
online for more information.
Disadvantages of cursors: Each time you fetch a row from the cursor, it
results in a network roundtrip, where as a normal SELECT query makes only one
rowundtrip, however large the resultset is. Cursors are also costly because
they require more resources and temporary storage (results in more IO
operations). Furthere, there are restrictions on the SELECT statements that
can be used with some types of cursors.
Most of the times, set based operations can be used instead of cursors.
|
|
Triggers are special kind of stored procedures that get executed
automatically when an INSERT, UPDATE or DELETE operation takes place on a
table.
Triggers can't be invoked on demand. They get triggered only when an
associated action (INSERT, UPDATE, DELETE) happens on the table on which they
are defined.
Triggers are generally used to implement business rules, auditing.
Triggers can also be used to extend the referential integrity checks, but
wherever possible, use constraints for this purpose, instead of triggers, as
constraints are much faster.
|
|
Joins are used in queries to explain how different tables are related.
Joins also let you select data from a table depending upon data from another
table.
Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs. OUTER JOINs are
further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER
JOINS.
|
|
Self join is just like any other join, except that two instances of the
same table will be joined in the query.
| |
What are the steps in the JDBC connection?
|
|
While making a JDBC connection we go through the following steps :
Step 1 : Register the database driver by using :
Class.forName(\" driver classs for that specific database\" );
Step 2 : Now create a database connection using :
Connection con = DriverManager.getConnection(url,username,password);
Step 3: Now Create a query using :
Statement stmt = Connection.Statement(\"select * from TABLE
NAME\");
Step 4 : Exceute the query :
stmt.exceuteUpdate();
|
What is JDBC?
JDBC is a layer of abstraction that allows users to
choose between databases. It allows you to change to a different database
engine and to write to a single API. JDBC allows you to write database
applications in Java without having to concern yourself with the underlying
details of a particular database.
What are the two major components of JDBC?
One implementation interface for database manufacturers,
the other implementation interface for application and applet writers.
What is JDBC Driver interface?
The JDBC Driver interface provides vendor-specific
implementations of the abstract classes provided by the JDBC API. Each vendors
driver must provide implementations of the
java.sql.Connection,Statement,PreparedStatement, CallableStatement, ResultSet
and Driver.
What are the common tasks of JDBC?
Create an instance of a JDBC driver or load JDBC drivers
through jdbc.drivers
·
Register a driver
·
Specify a database
·
Open a database connection
·
Submit a query
·
Receive results
What packages are used by JDBC?
There are 8 packages: java.sql.Driver,
Connection,Statement, PreparedStatement, CallableStatement, ResultSet,
ResultSetMetaData, DatabaseMetaData.
What are the flow statements of JDBC?
A URL string
-->getConnection-->DriverManager-->Driver-->Connection-->Statement-->executeQuery-->ResultSet.
What are the steps involved in establishing a connection?
This involves two steps: (1) loading the driver and (2)
making the connection.
How can you create JDBC statements?
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. E.g. 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 stmt :
Statement stmt = con.createStatement();
How can you retrieve data from the ResultSet?
First 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.
E.g. ResultSet
rs = stmt.executeQuery("SELECT COF_NAME, PRICE FROM COFFEES");
Second: 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
What are the different types of Statements?
1. Statement (use createStatement method)
2. Prepared Statement (Use prepareStatement method)
3. Callable Statement (Use prepareCall)
How can you use PreparedStatement?
This special type of statement is derived from the more
general class, Statement. If you want to execute a Statement object many times,
it will normally reduce execution time 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.
E.g.PreparedStatement
updateSales = con.prepareStatement("UPDATE COFFEES SET SALES = ? WHERE
COF_NAME LIKE ?");
How to 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;
E.g. CallableStatement
cs = con.prepareCall("{call SHOW_SUPPLIERS}");
ResultSet rs = cs.executeQuery();
How to 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
E.g.SQLWarning warning =
stmt.getWarnings();
if (warning != null) {
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());
warning
= warning.getNextWarning();
}
}
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.
E.g. Connection
con = DriverManager.getConnection("jdbc:mySubprotocol:mySubName");
Statement stmt =
con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
ResultSet uprs = ("SELECT COF_NAME, PRICE FROM
COFFEES");
What's the JDBC 2.0 API?
The JDBC 3.0 API is the latest update of the JDBC API. It
contains many features, including scrollable result sets and the SQL:1999 data
types.
Does the JDBC-ODBC Bridge support
the new features in the JDBC 3.0 API?
The JDBC-ODBC Bridge provides a limited subset of the JDBC 3.0 API.
Can the JDBC-ODBC Bridge be
used with applets?
Use of the JDBC-ODBC bridge from an untrusted applet
running in a browser, such as Netscape Navigator, isn't allowed. The JDBC-ODBC
bridge doesn't allow untrusted code to call it for security reasons. This is
good because it means that an untrusted applet that is downloaded by the
browser can't circumvent Java security by calling ODBC. Remember that ODBC is
native code, so once ODBC is called the Java programming language can't
guarantee that a security violation won't occur. On the other hand, Pure Java
JDBC drivers work well with applets. They are fully downloadable and do not
require any client-side configuration. Finally, we would like to note that it
is possible to use the JDBC-ODBC bridge with applets that will be run in
appletviewer since appletviewer assumes that applets are trusted. In general,
it is dangerous to turn applet security off, but it may be appropriate in
certain controlled situations, such as for applets that will only be used in a
secure intranet environment. Remember to exercise caution if you choose this
option, and use an all-Java JDBC driver whenever possible to avoid security
problems.
How do I start debugging problems related to the JDBC
API?
A good way to find out what JDBC calls are doing is to
enable JDBC tracing. The JDBC trace contains a detailed listing of the activity
occurring in the system that is related to JDBC operations. If you use the
DriverManager facility to establish your database connection, you use the
DriverManager.setLogWriter method to enable tracing of JDBC operations. If you
use a DataSource object to get a connection, you use the
DataSource.setLogWriter method to enable tracing. (For pooled connections, you
use the ConnectionPoolDataSource.setLogWriter method, and for connections that
can participate in distributed transactions, you use the
XADataSource.setLogWriter method.)
How can I use the JDBC API to access a desktop database
like Microsoft Access over the network?
Most desktop databases currently require a JDBC solution
that uses ODBC underneath. This is because the vendors of these database
products haven't implemented all-Java JDBC drivers. The best approach is to use
a commercial JDBC driver that supports ODBC and the database you want to use.
See the JDBC drivers page for a list of available JDBC drivers. The JDBC-ODBC
bridge from Sun's Java Software does not provide network access to desktop
databases by itself. The JDBC-ODBC bridge loads ODBC as a local DLL, and
typical ODBC drivers for desktop databases like Access aren't networked. The
JDBC-ODBC bridge can be used together with the RMI-JDBC bridge, however, to
access a desktop database like Access over the net. This RMI-JDBC-ODBC solution
is free.
What JDBC
technology-enabled drivers are available?
See our web page on JDBC technology-enabled drivers for a
current listing.
What documentation is available for the JDBC API?
See the JDBC technology home page for links to
information about JDBC technology. This page links to information about
features and benefits, a list of new features, a section on getting started,
online tutorials, a section on driver requirements, and other information in
addition to the specifications and javadoc documentation.
Are there any ODBC drivers that do not work with the JDBC-ODBC Bridge?
Most ODBC 2.0 drivers should work with the Bridge. Since
there is some variation in functionality between ODBC drivers, the
functionality of the bridge may be affected. The bridge works with popular PC
databases, such as Microsoft Access and FoxPro.
What causes the
"No suitable driver" error?
"No suitable driver" is an error that usually
occurs during a call to the DriverManager.getConnection method. The cause can
be failing to load the appropriate JDBC drivers before calling the
getConnection method, or it can be specifying an invalid JDBC
URL--one that isn't recognized by your JDBC driver. Your
best bet is to check the documentation for your JDBC driver or contact your
JDBC driver vendor if you suspect that the URL you are specifying is not being
recognized by your JDBC driver. In addition, when you are using the JDBC-ODBC Bridge,
this error can occur if one or more the the shared libraries needed by the
Bridge cannot be loaded. If you think this is the cause, check your
configuration to be sure that the shared libraries are accessible to the
Bridge.
10. Why isn't the java.sql.DriverManager class being
found?
This problem can be caused by running a JDBC applet in a
browser that supports the JDK 1.0.2, such as Netscape Navigator 3.0. The JDK
1.0.2 does not contain the JDBC API, so the DriverManager class typically isn't
found by the Java virtual machine running in the browser. Here's a solution
that doesn't require any additional configuration of your web clients. Remember
that classes in the java.* packages cannot be downloaded by most browsers for
security reasons. Because of this, many vendors of all-Java JDBC drivers supply
versions of the java.sql.* classes that have been renamed to jdbc.sql.*, along
with a version of their driver that uses these modified classes. If you import
jdbc.sql.* in your applet code instead of java.sql.*, and add the jdbc.sql.*
classes provided by your JDBC driver vendor to your applet's codebase, then all
of the JDBC classes needed by the applet can be downloaded by the browser at
run time, including the DriverManager class. This solution will allow your
applet to work in any client browser that supports the JDK 1.0.2. Your applet
will also work in browsers that support the JDK 1.1, although you may want to
switch to the JDK 1.1 classes for performance reasons. Also, keep in mind that
the solution outlined here is just an example and that other solutions are
possible.
How do I retrieve a whole row of data at once, instead of
calling an individual
ResultSet.getXXX method for each column?
The ResultSet.getXXX methods are the only way to retrieve
data from a ResultSet object, which means that you have to make a method call
for each column of a row. It is unlikely that this is the cause of a
performance problem, however, because it is difficult to see how a column could
be fetched without at least the cost of a function call in any scenario. We
welcome input from developers on this issue.
Why does the ODBC driver manager return 'Data source name
not found and no default driver specified Vendor: 0'
This type of error occurs during an attempt to connect to
a database with the bridge. First, note that the error is coming from the ODBC
driver manager. This indicates that the bridge-which is a normal ODBC
client-has successfully called ODBC, so the problem isn't due to native
libraries not being present. In this case, it appears that the error is due to
the fact that an ODBC DSN (data source name) needs to be configured on the
client machine. Developers often forget to do this, thinking that the bridge
will magically find the DSN they configured on their remote server machine
Are all the required JDBC drivers to establish
connectivity to my database part of the JDK?
No. There aren't any JDBC technology-enabled drivers bundled
with the JDK 1.1.x or Java 2 Platform releases other than the JDBC-ODBC Bridge.
So, developers need to get a driver and install it before they can connect to a
database. We are considering bundling JDBC technology- enabled drivers in the
future.
Is the JDBC-ODBC Bridge multi-threaded?
No. The JDBC-ODBC Bridge does not support concurrent access from different
threads. The JDBC-ODBC Bridge uses synchronized methods to serialize all of the calls
that it makes to ODBC. Multi-threaded Java programs may use the Bridge, but
they won't get the advantages of multi-threading. Inaddition, deadlocks can
occur between locks held in the database and the semaphore used by the Bridge.
We are thinking about removing the synchronized methods in the future. They
were added originally to make things simple for folks writing Java
programs that use a single-threaded ODBC driver.
Does the JDBC-ODBC Bridge support
multiple concurrent open statements per
connection?
No. You can open only one Statement object per connection
when you are using the JDBC-ODBC Bridge.
Why can't I invoke the ResultSet methods afterLast and
beforeFirst when the
method next works?
You are probably using a driver implemented for the JDBC
1.0 API. You need to upgrade to a JDBC 2.0 driver that implements scrollable
result sets. Also be sure that your code has created scrollable result sets and
that the DBMS you are using supports them.
How can I retrieve a String or other object type without
creating a new object each time?
Creating and garbage collecting potentially large numbers
of objects (millions) unnecessarily can really hurt performance. It may be
better to provide a way to retrieve data like strings using the JDBC API
without always allocating a new object. We are studying this issue to see if it
is an area in which the JDBC API should be improved. Stay tuned, and please
send us any comments you have on this question.
There is a method getColumnCount in the JDBC API. Is
there a similar method to find the number of rows in a result set?
No, but it is easy to find the number of rows. If you are
using a scrollable result set, rs, you can call the methods rs.last and then
rs.getRow to find out how many rows rs has. If the result is not scrollable,
you can either count the rows by iterating through the result set or get the
number of rows by submitting a query with a COUNT column in the SELECT
clause.
I would like to download the JDBC-ODBC Bridge for the
J2SDK, Standard Edition (formerly JDK 1.2). I'm a beginner with the JDBC API,
and I would like to start with the Bridge. How do I do it?
The JDBC-ODBC Bridge is bundled with the Java 2 SDK, Standard Edition, so
there is no need to download it separately.
If I use the JDBC API, do I have to use ODBC underneath?
No, this is just one of many possible solutions. We recommend
using a pure Java JDBC technology-enabled driver, type 3 or 4, in order to get
all of the benefits of the Java programming language and the JDBC API.
Once I have the Java 2 SDK, Standard Edition, from Sun,
what else do I need to connect to a database?
You still need to get and install a JDBC
technology-enabled driver that supports the database that you are using. There
are many drivers available from a variety of sources. You can also try using
the JDBC-ODBC Bridge if you have ODBC connectivity set up already. The Bridge
comes with the Java 2 SDK, Standard Edition, and Enterprise Edition, and it
doesn't require any extra setup itself. The Bridge is a normal ODBC client.
Note, however, that you should use the JDBC-ODBCBridge only
for experimental prototyping or when you have no other driver available.
No comments:
Post a Comment