Get documents about "jdbc
Document Sample
This Download is from www.downloadmela.com . The main motto of this website is to provide free
download links of
ebooks,video tutorials,magazines,previous papers,interview related content. To download more visit
the website.
If you like our services please help us in 2 ways.
1.Donate money.
Please go through the link to donate
http://www.downloadmela.com/donate.html
2.Tell about this website to your friends,relatives.
Thanks for downloading. Enjoy the reading.
What is JDBC?
JDBC may stand for Java Database Connectivity. It is also a trade mark. 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's the JDBC 3.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.
JDBC (Java Database Connectivity) is the standard for communication between a Java application and
a relational database. The JDBC API is released in two versions; JDBC version 1.22 (released with
JDK 1.1.X in package java.sql) and version 2.0 (released with Java platform 2 in packages java.sql and
javax.sql). It is a simple and powerful largely database-independent way of extracting and inserting
data to or from any database.
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.
Visit http://www.downloadmela.com/ for more papers
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.)
What is new in JDBC 2.0?
With the JDBC 2.0 API, you will be able to do the following:
Scroll forward and backward in a result set or move to a specific row
(TYPE_SCROLL_SENSITIVE,previous(), last(), absolute(), relative(), etc.)
Make updates to database tables using methods in the Java programming language instead of using
SQL commands.(updateRow(), insertRow(), deleteRow(), etc.)
Send multiple SQL statements to the database as a unit, or batch (addBatch(), executeBatch())
Use the new SQL3 datatypes as column values like Blob, Clob, Array, Struct, Ref.
How to move the cursor in scrollable resultset ?
a. create a scrollable ResultSet object.
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
ResultSet srs = stmt.executeQuery("SELECT COLUMN_1,
COLUMN_2 FROM TABLE_NAME");
b. use a built in methods like afterLast(), previous(), beforeFirst(), etc. to scroll the resultset.
srs.afterLast();
while (srs.previous()) {
String name = srs.getString("COLUMN_1");
float salary = srs.getFloat("COLUMN_2");
//...
c. to find a specific row, use absolute(), relative() methods.
srs.absolute(4); // cursor is on the fourth row
int rowNum = srs.getRow(); // rowNum should be 4
srs.relative(-3);
int rowNum = srs.getRow(); // rowNum should be 1
srs.relative(2);
int rowNum = srs.getRow(); // rowNum should be 3
d. use isFirst(), isLast(), isBeforeFirst(), isAfterLast() methods to check boundary status.
Visit http://www.downloadmela.com/ for more papers
How to update a resultset programmatically?
a. create a scrollable and updatable ResultSet object.
Statement stmt = con.createStatement
(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet uprs = stmt.executeQuery("SELECT COLUMN_1,
COLUMN_2 FROM TABLE_NAME");
b. move the cursor to the specific position and use related method to update data and then, call
updateRow() method.
uprs.last();
uprs.updateFloat("COLUMN_2", 25.55);//update last row's data
uprs.updateRow();//don't miss this method, otherwise,
// the data will be lost.
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.
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.
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
Visit http://www.downloadmela.com/ for more papers
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 to insert and delete a row programmatically? (new feature in JDBC 2.0)
Make sure the resultset is updatable.
1. move the cursor to the specific position.
uprs.moveToCurrentRow();
2. set value for each column.
uprs.moveToInsertRow();//to set up for insert
uprs.updateString("col1" "strvalue");
uprs.updateInt("col2", 5);
...
3. call inserRow() method to finish the row insert process.
uprs.insertRow();
To delete a row: move to the specific position and call deleteRow() method:
uprs.absolute(5);
uprs.deleteRow();//delete row 5
To see the changes call refreshRow();
uprs.refreshRow();
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 vendor driver must provide implementations of the
java.sql.Connection,Statement,PreparedStatement, CallableStatement, ResultSet and Driver.
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
Visit http://www.downloadmela.com/ for more papers
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.
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
Process results
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
How to use JDBC to connect Microsoft Access?
There is a specific tutorial at javacamp.org. Check it out.
What are four types of JDBC driver?
Type 1 Drivers
Bridge drivers such as the jdbc-odbc bridge. They rely on an intermediary such as ODBC to transfer
the SQL calls to the database and also often rely on native code. It is not a serious solution for an
application
Type 2 Drivers
Use the existing database API to communicate with the database on the client. Faster than Type 1, but
need native code and require additional permissions to work in an applet. Client machine requires
software to run.
Type 3 Drivers
JDBC-Net pure Java driver. It translates JDBC calls to a DBMS-independent network protocol, which
is then translated to a DBMS protocol by a server. Flexible. Pure Java and no native code.
Type 4 Drivers
Native-protocol pure Java driver. It converts JDBC calls directly into the network protocol used by
DBMSs. This allows a direct call from the client machine to the DBMS server. It doesn't need any
special native code on the client machine.
Recommended by Sun's tutorial, driver type 1 and 2 are interim solutions where direct pure Java
drivers are not yet available. Driver type 3 and 4 are the preferred way to access databases using the
JDBC API, because they offer all the advantages of Java technology, including automatic installation.
Visit http://www.downloadmela.com/ for more papers
For more info, visit Sun JDBC page
Which type of JDBC driver is the fastest one?
JDBC Net pure Java driver(Type IV) is the fastest driver because it converts the jdbc calls into vendor
specific protocol calls and it directly interacts with the database.
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. In
addition, 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.
What is the query used to display all tables names in SQL Server (Query analyzer)?
select * from information_schema.tables
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.
How many types of JDBC Drivers are present and what are they?
There are 4 types of JDBC Drivers
Type 1: JDBC-ODBC Bridge Driver
Type 2: Native API Partly Java Driver
Type 3: Network protocol Driver
Type 4: JDBC Net pure Java Driver
What is the fastest type of JDBC driver?
JDBC driver performance will depend on a number of issues:
(a) the quality of the driver code,
Visit http://www.downloadmela.com/ for more papers
(b) the size of the driver code,
(c) the database server and its load,
(d) network topology,
(e) the number of times your request is translated to a different API.
In general, all things being equal, you can assume that the more your request and response change
hands, the slower it will be. This means that Type 1 and Type 3 drivers will be slower than Type 2
drivers (the database calls are make at least three translations versus two), and Type 4 drivers are the
fastest (only one translation).
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 Java 2 SDK, 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-ODBC Bridge only for
experimental prototyping or when you have no other driver available.
What is the best way to generate a universally unique object ID? Do I need to use an external
resource like a file or database, or can I do it all in memory?
1: Unique down to the millisecond. Digits 1-8 are the hex encoded lower 32 bits of the
System.currentTimeMillis() call.
2: Unique across a cluster. Digits 9-16 are the encoded representation of the 32 bit integer of the
underlying IP address.
3: Unique down to the object in a JVM. Digits 17-24 are the hex representation of the call to
System.identityHashCode(), which is guaranteed to return distinct integers for distinct objects within a
JVM.
4: Unique within an object within a millisecond. Finally digits 25-32 represent a random 32 bit integer
generated on every method call using the cryptographically strong java.security.SecureRandom class.
Answer1
There are two reasons to use the random number instead of incrementing your last. 1. The number
Visit http://www.downloadmela.com/ for more papers
would be predictable and, depending on what this is used for, you could be opening up a potential
security issue. This is why ProcessIDs are randomized on some OSes (AIX for one). 2. You must
synchronize on that counter to guarantee that your number isn't reused. Your random number generator
need not be synchronized, (though its implementation may be).
Answer2
1) If your using Oracle You can create a sequence ,by which you can generate unique primary key or
universal primary key. 2) you can generate by using random numbers but you may have to check the
range and check for unique id. ie random number generate 0.0 to 1.0 u may have to make some logic
which suits your unique id 3) Set the maximum value into an XML file and read that file at the time of
loading your application from xml .
What happens when I close a Connection application obtained from a connection Pool? How
does a connection pool maintain the Connections that I had closed through the application?
Answer1
It is the magic of polymorphism, and of Java interface vs. implementation types. Two objects can both
be "instanceof" the same interface type, even though they are not of the same implementation type.
When you call "getConnection()" on a pooled connection cache manager object, you get a "logical"
connection, something which implements the java.sql.Connection interface.
But it is not the same implementation type as you would get for your Connection, if you directly called
getConnection() from a (non-pooled/non-cached) datasource.
So the "close()" that you invoke on the "logical" Connection is not the same "close()" method as the
one on the actual underlying "physical" connection hidden by the pool cache manager.
The close() method of the "logical" connection object, while it satisfies the method signature of close()
in the java.sql.Connection interface, does not actually close the underlying physical connection.
Answer2
Typically a connection pool keeps the active/in-use connections in a hashtable or other Collection
mechanism. I've seen some that use one stack for ready-for-use, one stack for in-use.
When close() is called, whatever the mechanism for indicating inuse/ready-for-use, that connection is
either returned to the pool for ready-for-use or else physically closed. Connections pools should have a
minimum number of connections open. Any that are closing where the minimum are already available
should be physically closed.
Some connection pools periodically test their connections to see if queries work on the ready-for-use
connections or they may test that on the close() method before returning to the ready-for-use pool.
How do I insert a .jpg into a mySQL data base? I have tried inserting the file as byte[], but I
recieve an error message stating that the syntax is incorrect.
Binary data is stored and retrieved from the database using
streams in connection with prepared statements and resultsets.
This minimal application stores an image file in the database,
then it retrieves the binary data from the database and converts
it back to an image.
import java.sql.*;
import java.io.*;
import java.awt.*;
Visit http://www.downloadmela.com/ for more papers
import java.awt.Image;
/**
* Storing and retrieving images from a MySQL database
*/
public class StoreBinary {
private static String driverName = "sun.jdbc.odbc.JdbcOdbcDriver";
private Statement stmt = null;
private Connection conn = null;
public StoreBinary() {}
/**
* Strips path prefix from filenames
* @param fileName
* @return the base filename
*/
public static String getBaseName(String fileName) {
int ix=fileName.lastIndexOf("\\");
if (ix < 0) return fileName;
return fileName.substring(ix+1);
}
/**
* Store a binary (image) file in the database using a
* prepared statement.
* @param fileName
* @return true if the operation succeeds
* @throws Exception
*/
public boolean storeImageFile(String fileName) throws Exception {
if (!connect("test", "root", "")) {
return false;
}
FileInputStream in = new FileInputStream(fileName);
int len=in.available();
String baseName=StoreBinary.getBaseName(fileName);
PreparedStatement pStmt = conn.prepareStatement
("insert into image_tab values (?,?,?)");
pStmt.setString(1, baseName);
pStmt.setInt(2,len);
pStmt.setBinaryStream(3, in, len);
pStmt.executeUpdate();
in.close();
System.out.println("Stored: "+baseName+", length: "+len);
return true;
}
Visit http://www.downloadmela.com/ for more papers
/**
* Retrieve the biary file data from the DB and convert it to an image
* @param fileName
* @return
* @throws Exception
*/
public Image getImageFile(String fileName) throws Exception {
String baseName=StoreBinary.getBaseName(fileName);
ResultSet rs=stmt.executeQuery("select * from image_tab
where image_name='"+baseName+"'");
if (!rs.next()) {
System.out.println("Image:"+baseName+" not found");
return null;
}
int len=rs.getInt(2);
byte [] b=new byte[len];
InputStream in = rs.getBinaryStream(3);
int n=in.read(b);
System.out.println("n: "+n);
in.close();
Image img=Toolkit.getDefaultToolkit().createImage(b);
System.out.println("Image: "+baseName+" retrieved ok, size: "+len);
return img;
}
/**
* Establish database connection
* @param dbName
* @param dbUser
* @param dbPassword
* @return true if the operation succeeds
*/
public boolean connect(String dbName, String dbUser, String dbPassword) {
try {
Class.forName(driverName);
}
catch (ClassNotFoundException ex) {
ex.printStackTrace();
return false;
}
try {
conn = DriverManager.getConnection("jdbc:odbc:" + dbName,
dbUser,
dbPassword);
Visit http://www.downloadmela.com/ for more papers
stmt = conn.createStatement();
}
catch (SQLException ex1) {
ex1.printStackTrace();
return false;
}
return true;
}
/******************************************
* MAIN stub driver for testing the class.
*/
public static void main(String[] args) {
String fileName="c:\\tmp\\f128.jpg";
StoreBinary sb = new StoreBinary();
try {
if (sb.storeImageFile(fileName)) {
// stored ok, now get it back again
Image img=sb.getImageFile(fileName);
}
}
catch (Exception ex) {
ex.printStackTrace();
}
}
}
How can I know when I reach the last record in a table, since JDBC doesn't provide an EOF
method?
Answer1
You can use last() method of java.sql.ResultSet, if you make it scrollable.
You can also use isLast() as you are reading the ResultSet.
One thing to keep in mind, though, is that both methods tell you that you have reached the end of the
current ResultSet, not necessarily the end of the table. SQL and RDBMSes make no guarantees about
the order of rows, even from sequential SELECTs, unless you specifically use ORDER BY. Even then,
that doesn't necessarily tell you the order of data in the table.
Answer2
Assuming you mean ResultSet instead of Table, the usual idiom for iterating over a forward only
resultset is:
ResultSet rs=statement.executeQuery(...);
while (rs.next()) {
// Manipulate row here
}
Where can I find info, frameworks and example source for writing a JDBC driver?
There a several drivers with source available, like MM.MySQL, SimpleText Database, FreeTDS, and
Visit http://www.downloadmela.com/ for more papers
RmiJdbc. There is at least one free framework, the jxDBCon-Open Source JDBC driver framework.
Any driver writer should also review For Driver Writers.
How can I create a custom RowSetMetaData object from scratch?
One unfortunate aspect of RowSetMetaData for custom versions is that it is an interface. This means
that implementations almost have to be proprietary. The JDBC RowSet package is the most commonly
available and offers the sun.jdbc.rowset.RowSetMetaDataImpl class. After instantiation, any of the
RowSetMetaData setter methods may be used. The bare minimum needed for a RowSet to function is
to set the Column Count for a row and the Column Types for each column in the row. For a working
code example that includes a custom RowSetMetaData,
How does a custom RowSetReader get called from a CachedRowSet?
The Reader must be registered with the CachedRowSet using
CachedRowSet.setReader(javax.sql.RowSetReader reader). Once that is done, a call to
CachedRowSet.execute() will, among other things, invoke the readData method.
How do I implement a RowSetReader? I want to populate a CachedRowSet myself and the
documents specify that a RowSetReader should be used. The single method accepts a
RowSetInternal caller and returns void. What can I do in the readData method?
"It can be implemented in a wide variety of ways..." and is pretty vague about what can actually be
done. In general, readData() would obtain or create the data to be loaded, then use CachedRowSet
methods to do the actual loading. This would usually mean inserting rows, so the code would move to
the insert row, set the column data and insert rows. Then the cursor must be set to to the appropriate
position.
How can I instantiate and load a new CachedRowSet object from a non-JDBC source?
The basics are:
* Create an object that implements javax.sql.RowSetReader, which loads the data.
* Instantiate a CachedRowset object.
* Set the CachedRowset's reader to the reader object previously created.
* Invoke CachedRowset.execute().
Note that a RowSetMetaData object must be created, set up with a description of the data, and attached
to the CachedRowset before loading the actual data.
The following code works with the Early Access JDBC RowSet download available from the Java
Developer Connection and is an expansion of one of the examples:
// Independent data source CachedRowSet Example
import java.sql.*;
import javax.sql.*;
import sun.jdbc.rowset.*;
public class RowSetEx1 implements RowSetReader
{
CachedRowSet crs;
int iCol2;
RowSetMetaDataImpl rsmdi;
String sCol1,
Visit http://www.downloadmela.com/ for more papers
sCol3;
public RowSetEx1()
{
try
{
crs = new CachedRowSet();
crs.setReader(this);
crs.execute(); // load from reader
System.out.println(
"Fetching from RowSet...");
while(crs.next())
{
showTheData();
} // end while next
if(crs.isAfterLast() == true)
{
System.out.println(
"We have reached the end");
System.out.println("crs row: " +
crs.getRow());
}
System.out.println(
"And now backwards...");
while(crs.previous())
{
showTheData();
} // end while previous
if(crs.isBeforeFirst() == true)
{ System.out.println(
"We have reached the start");
}
crs.first();
if(crs.isFirst() == true)
{ System.out.println(
"We have moved to first");
}
System.out.println("crs row: " +
Visit http://www.downloadmela.com/ for more papers
crs.getRow());
if(crs.isBeforeFirst() == false)
{ System.out.println(
"We aren't before the first row."); }
crs.last();
if(crs.isLast() == true)
{ System.out.println(
"...and now we have moved to the last");
}
System.out.println("crs row: " +
crs.getRow());
if(crs.isAfterLast() == false)
{
System.out.println(
"we aren't after the last.");
}
} // end try
catch (SQLException ex)
{
System.err.println("SQLException: " +
ex.getMessage());
}
} // end constructor
public void showTheData() throws SQLException
{
sCol1 = crs.getString(1);
if(crs.wasNull() == false)
{ System.out.println("sCol1: " + sCol1); }
else { System.out.println("sCol1 is null"); }
iCol2 = crs.getInt(2);
if (crs.wasNull() == false)
{ System.out.println("iCol2: " + iCol2); }
else { System.out.println("iCol2 is null"); }
sCol3 = crs.getString(3);
if (crs.wasNull() == false)
Visit http://www.downloadmela.com/ for more papers
{
System.out.println("sCol3: " +
sCol3 + "\n" );
}
else
{ System.out.println("sCol3 is null\n"); }
} // end showTheData
// RowSetReader implementation
public void readData(RowSetInternal caller)
throws SQLException
{
rsmdi = new RowSetMetaDataImpl();
rsmdi.setColumnCount(3);
rsmdi.setColumnType(1, Types.VARCHAR);
rsmdi.setColumnType(2, Types.INTEGER);
rsmdi.setColumnType(3, Types.VARCHAR);
crs.setMetaData( rsmdi );
crs.moveToInsertRow();
crs.updateString( 1, "StringCol11" );
crs.updateInt( 2, 1 );
crs.updateString( 3, "StringCol31" );
crs.insertRow();
crs.updateString( 1, "StringCol12" );
crs.updateInt( 2, 2 );
crs.updateString( 3, "StringCol32" );
crs.insertRow();
crs.moveToCurrentRow();
crs.beforeFirst();
} // end readData
public static void main(String args[])
{
new RowSetEx1();
}
Visit http://www.downloadmela.com/ for more papers
} // end class RowSetEx1
Can I set up a connection pool with multiple user IDs? The single ID we are forced to use causes
problems when debugging the DBMS.
Since the Connection interface ( and the underlying DBMS ) requires a specific user and password,
there's not much of a way around this in a pool. While you could create a different Connection for each
user, most of the rationale for a pool would then be gone. Debugging is only one of several issues that
arise when using pools.
However, for debugging, at least a couple of other methods come to mind. One is to log executed
statements and times, which should allow you to backtrack to the user. Another method that also
maintains a trail of modifications is to include user and timestamp as standard columns in your tables.
In this last case, you would collect a separate user value in your program.
How can I protect my database password ? I'm writing a client-side java application that will
access a database over the internet. I have concerns about the security of the database passwords.
The client will have access in one way or another to the class files, where the connection string to
the database, including user and password, is stored in as plain text. What can I do to protect my
passwords?
This is a very common question.
Conclusion: JAD decompiles things easily and obfuscation would not help you. But you'd have the
same problem with C/C++ because the connect string would still be visible in the executable.
SSL JDBC network drivers fix the password sniffing problem (in MySQL 4.0), but not the decompile
problem. If you have a servlet container on the web server, I would go that route (see other discussion
above) then you could at least keep people from reading/destroying your mysql database.
Make sure you use database security to limit that app user to the minimum tables that they need, then at
least hackers will not be able to reconfigure your DBMS engine.
Aside from encryption issues over the internet, it seems to me that it is bad practice to embed user ID
and password into program code. One could generally see the text even without decompilation in
almost any language. This would be appropriate only to a read-only database meant to be open to the
world. Normally one would either force the user to enter the information or keep it in a properties file.
Detecting Duplicate Keys I have a program that inserts rows in a table. My table has a column
'Name' that has a unique constraint. If the user attempts to insert a duplicate name into the table,
I want to display an error message by processing the error code from the database. How can I
capture this error code in a Java program?
A solution that is perfectly portable to all databases, is to execute a query for checking if that unique
value is present before inserting the row. The big advantage is that you can handle your error message
in a very simple way, and the obvious downside is that you are going to use more time for inserting the
record, but since you're working on a PK field, performance should not be so bad.
You can also get this information in a portable way, and potentially avoid another database access, by
capturing SQLState messages. Some databases get more specific than others, but the general code
portion is 23 - "Constraint Violations". UDB2, for example, gives a specific such as 23505, while
others will only give 23000.
What driver should I use for scalable Oracle JDBC applications?
Sun recommends using the thin ( type 4 ) driver.
* On single processor machines to avoid JNI overhead.
* On multiple processor machines, especially running Solaris, to avoid synchronization bottlenecks.
Visit http://www.downloadmela.com/ for more papers
Can you scroll a result set returned from a stored procedure? I am returning a result set from a
stored procedure with type SQLRPGLE but once I reach the end of the result set it does not
allow repositioning. Is it possible to scroll this result set?
A CallableStatement is no different than other Statements in regard to whether related ResultSets are
scrollable. You should create the CallableStatement using Connection.prepareCall(String sql, int
resultSetType, int resultSetConcurrency).
How do I write Greek ( or other non-ASCII/8859-1 ) characters to a database?
From the standard JDBC perspective, there is no difference between ASCII/8859-1 characters and
those above 255 ( hex FF ). The reason for that is that all Java characters are in Unicode ( unless you
perform/request special encoding ). Implicit in that statement is the presumption that the data store can
handle characters outside the hex FF range or interprets different character sets appropriately. That
means either:
* The OS, application and database use the same code page and character set. For example, a Greek
version of NT with the DBMS set to the default OS encoding.
* The DBMS has I18N support for Greek ( or other language ), regardless of OS encoding. This has
been the most common for production quality databases, although support varies. Particular DBMSes
may allow setting the encoding/code page/CCSID at the database, table or even column level. There is
no particular standard for provided support or methods of setting the encoding. You have to check the
DBMS documentation and set up the table properly.
* The DBMS has I18N support in the form of Unicode capability. This would handle any Unicode
characters and therefore any language defined in the Unicode standard. Again, set up is proprietary.
How can I insert images into a Mysql database?
This code snippet shows the basics:
File file = new File(fPICTURE);
FileInputStream fis = new FileInputStream(file);
PreparedStatement ps =
ConrsIn.prepareStatement("insert into dbPICTURE values (?,?)");
// ***use as many ??? as you need to insert in the exact order***
ps.setString(1,file.getName());
ps.setBinaryStream(2,fis,(int)file.length());
ps.close();
fis.close();
Is possible to open a connection to a database with exclusive mode with JDBC?
I think you mean "lock a table in exclusive mode". You cannot open a connection with exclusive mode.
Depending on your database engine, you can lock tables or rows in exclusive mode.
In Oracle you would create a statement st and run
st.execute("lock table mytable in exclusive mode");
Then when you are finished with the table, execute the commit to unlock the table. Mysql, Informix
and SQLServer all have a slightly different syntax for this function, so you'll have to change it
depending on your database. But they can all be done with execute().
What are the standard isolation levels defined by JDBC?
Visit http://www.downloadmela.com/ for more papers
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
Update fails without blank padding. Although a particular row is present in the database for a
given key, executeUpdate() shows 0 rows updated and, in fact, the table is not updated. If I pad
the Key with spaces for the column length (e.g. if the key column is 20 characters long, and key is
msgID, length 6, I pad it with 14 spaces), the update then works!!! Is there any solution to this
problem without padding?
In the SQL standard, CHAR is a fixed length data type. In many DBMSes ( but not all), that means that
for a WHERE clause to match, every character must match, including size and trailing blanks. As
Alessandro indicates, defining CHAR columns to be VARCHAR is the most general answer.
What isolation level is used by the DBMS when inserting, updating and selecting rows from a
database?
The answer depends on both your code and the DBMS. If the program does not explicitly set the
isolation level, the DBMS default is used. You can determine the default using
DatabaseMetaData.getDefaultTransactionIsolation() and the level for the current Connection with
Connection.getTransactionIsolation(). If the default is not appropriate for your transaction, change it
with Connection.setTransactionIsolation(int level).
How can I determine the isolation levels supported by my DBMS?
Use DatabaseMetaData.supportsTransactionIsolationLevel(int level).
Connecting to a database through the Proxy I want to connect to remote database using a
program that is running in the local network behind the proxy. Is that possible?
I assume that your proxy is set to accept http requests only on port 80. If you want to have a local class
behind the proxy connect to the database for you, then you need a servlet/JSP to receive an HTTP
request and use the local class to connect to the database and send the response back to the client.
You could also use RMI where your remote computer class that connects to the database acts as a
remote server that talks RMI with the clients. if you implement this, then you will need to tunnel RMI
through HTTP which is not that hard.
In summary, either have a servlet/JSP take HTTP requests, instantiate a class that handles database
connections and send HTTP response back to the client or have the local class deployed as RMI server
and send requests to it using RMI.
How do I receive a ResultSet from a stored procedure?
Stored procedures can return a result parameter, which can be a result set. For a discussion of standard
JDBC syntax for dealing with result, IN, IN/OUT and OUT parameters, see Stored Procedures.
How can I write to the log used by DriverManager and JDBC drivers?
The simplest method is to use DriverManager.println(String message), which will write to the current
log.
How can I get or redirect the log used by DriverManager and JDBC drivers?
As of JDBC 2.0, use DriverManager.getLogWriter() and DriverManager.setLogWriter(PrintWriter
out). Prior to JDBC 2.0, the DriverManager methods getLogStream() and setLogStream(PrintStream
Visit http://www.downloadmela.com/ for more papers
out) were used. These are now deprecated.
What does it mean to "materialize" data?
This term generally refers to Array, Blob and Clob data which is referred to in the database via SQL
locators "Materializing" the data means to return the actual data pointed to by the Locator.
For Arrays, use the various forms of getArray() and getResultSet().
For Blobs, use getBinaryStream() or getBytes(long pos, int length).
For Clobs, use getAsciiStream() or getCharacterStream().
Why do I have to reaccess the database for Array, Blob, and Clob data?
Most DBMS vendors have implemented these types via the SQL3 Locator type
Some rationales for using Locators rather than directly returning the data can be seen most clearly with
the Blob type. By definition, a Blob is an arbitrary set of binary data. It could be anything; the DBMS
has no knowledge of what the data represents. Notice that this effectively demolishes data
independence, because applications must now be aware of what the Blob data actually represents. Let's
assume an employee table that includes employee images as Blobs.
Say we have an inquiry program that presents multiple employees with department and identification
information. To see all of the data for a specific employee, including the image, the summary row is
selected and another screen appears. It is only at this pont that the application needs the specific image.
It would be very wasteful and time consuming to bring down an entire employee page of images when
only a few would ever be selected in a given run.
Now assume a general interactive SQL application. A query is issued against the employee table.
Because the image is a Blob, the application has no idea what to do with the data, so why bring it
down, killing performance along the way, in a long running operation?
Clearly this is not helpful in those applications that need the data everytime, but these and other
considerations have made the most general sense to DBMS vendors.
What is an SQL Locator?
A Locator is an SQL3 data type that acts as a logical pointer to data that resides on a database server.
Read "logical pointer" here as an identifier the DBMS can use to locate and manipulate the data. A
Locator allows some manipulation of the data on the server. While the JDBC specification does not
directly address Locators, JDBC drivers typically use Locators under the covers to handle Array, Blob,
and Clob data types.
How do I set properties for a JDBC driver and where are the properties stored?
A JDBC driver may accept any number of properties to tune or optimize performance for the specific
driver. There is no standard, other than user and password, for what these properties should be.
Therefore, the developer is dependent on the driver documentation to automatically pass properties. For
a standard dynamic method that can be used to solicit user input for properties, see What properties
should I supply to a database driver in order to connect to a database?
In addition, a driver may specify its own method of accepting properties. Many do this via appending
the property to the JDBC Database URL. However, a JDBC Compliant driver should implement the
connect(String url, Properties info) method. This is generally invoked through
DriverManager.getConnection(String url, Properties info).
The passed properties are ( probably ) stored in variables in the Driver instance. This, again, is up to the
driver, but unless there is some sort of driver setup, which is unusual, only default values are
remembered over multiple instantiations.
What is the JDBC syntax for using a literal or variable in a standard Statement?
Visit http://www.downloadmela.com/ for more papers
First, it should be pointed out that PreparedStatement handles many issues for the developer and
normally should be preferred over a standard Statement.
Otherwise, the JDBC syntax is really the same as SQL syntax. One problem that often affects newbies (
and others ) is that SQL, like many languages, requires quotes around character ( read "String" for
Java ) values to distinguish from numerics. So the clause:
"WHERE myCol = " + myVal
is perfectly valid and works for numerics, but will fail when myVal is a String. Instead use:
"WHERE myCol = '" + myVal + "'"
if myVal equals "stringValue", the clause works out to:
WHERE myCol = 'stringValue'
You can still encounter problems when quotes are embedded in the value, which, again, a
PreparedStatement will handle for you.
How do I check in my code whether a maximum limit of database connections have been
reached?
Use DatabaseMetaData.getMaxConnections() and compare to the number of connections currently
open. Note that a return value of zero can mean unlimited or, unfortunately, unknown. Of course,
driverManager.getConnection() will throw an exception if a Connection can not be obtained.
Why do I get UnsatisfiedLinkError when I try to use my JDBC driver?
The first thing is to be sure that this does not occur when running non-JDBC apps. If so, there is a
faulty JDK/JRE installation. If it happens only when using JDBC, then it's time to check the
documentation that came with the driver or the driver/DBMS support. JDBC driver types 1 through 3
have some native code aspect and typically require some sort of client install. Along with the install,
various environment variables and path or classpath settings must be in place. Because the
requirements and installation procedures vary with the provider, there is no reasonable way to provide
details here. A type 4 driver, on the other hand, is pure Java and should never exhibit this problem. The
trade off is that a type 4 driver is usually slower.
Many connections from an Oracle8i pooled connection returns statement closed. I am using
import oracle.jdbc.pool.* with thin driver. If I test with many simultaneous connections, I get an
SQLException that the statement is closed.
ere is an example of concurrent operation of pooled connections from the
OracleConnectionPoolDataSource. There is an executable for kicking off threads, a DataSource, and
the workerThread.
The Executable Member
package package6;
/**
* package6.executableTester
*
*/
public class executableTester {
protected static myConnectionPoolDataSource dataSource = null;
static int i = 0;
Visit http://www.downloadmela.com/ for more papers
/**
* Constructor
*/
public executableTester() throws java.sql.SQLException
{
}
/**
* main
* @param args
*/
public static void main(String[] args) {
try{
dataSource = new myConnectionPoolDataSource();
}
catch ( Exception ex ){
ex.printStackTrace();
}
while ( i++ < 10 ) {
try{
workerClass worker = new workerClass();
worker.setThreadNumber( i );
worker.setConnectionPoolDataSource
( dataSource.getConnectionPoolDataSource() );
worker.start();
System.out.println( "Started Thread#"+i );
}
catch ( Exception ex ){
ex.printStackTrace();
}
}
}
}
The DataSource Member
package package6;
import oracle.jdbc.pool.*;
/**
* package6.myConnectionPoolDataSource.
*
*/
Visit http://www.downloadmela.com/ for more papers
public class myConnectionPoolDataSource extends Object {
protected OracleConnectionPoolDataSource ocpds = null;
/**
* Constructor
*/
public myConnectionPoolDataSource()
throws java.sql.SQLException {
// Create a OracleConnectionPoolDataSource instance
ocpds = new OracleConnectionPoolDataSource();
// Set connection parameters
ocpds.setURL("jdbc:oracle:oci8:@mydb");
ocpds.setUser("scott");
ocpds.setPassword("tiger");
}
public OracleConnectionPoolDataSource
getConnectionPoolDataSource() {
return ocpds;
}
}
The Worker Thread Member
package package6;
import oracle.jdbc.pool.*;
import java.sql.*;
import javax.sql.*;
/**
* package6.workerClass .
*
*/
public class workerClass extends Thread {
protected OracleConnectionPoolDataSource ocpds = null;
protected PooledConnection pc = null;
protected Connection conn = null;
protected int threadNumber = 0;
/**
* Constructor
*/
Visit http://www.downloadmela.com/ for more papers
public workerClass() {
}
public void doWork( ) throws SQLException {
// Create a pooled connection
pc = ocpds.getPooledConnection();
// Get a Logical connection
conn = pc.getConnection();
// Create a Statement
Statement stmt = conn.createStatement ();
// Select the ENAME column from the EMP table
ResultSet rset = stmt.executeQuery ("select ename from emp");
// Iterate through the result and print the employee names
while (rset.next ())
// System.out.println (rset.getString (1));
;
// Close the RseultSet
rset.close();
rset = null;
// Close the Statement
stmt.close();
stmt = null;
// Close the logical connection
conn.close();
conn = null;
// Close the pooled connection
pc.close();
pc = null;
System.out.println( "workerClass.thread#
"+threadNumber+" completed..");
}
public void setThreadNumber( int assignment ){
threadNumber = assignment;
Visit http://www.downloadmela.com/ for more papers
}
public void setConnectionPoolDataSource
(OracleConnectionPoolDataSource x){
ocpds = x;
}
public void run() {
try{
doWork();
}
catch ( Exception ex ){
ex.printStackTrace();
}
}
}
The OutPut Produced
Started Thread#1
Started Thread#2
Started Thread#3
Started Thread#4
Started Thread#5
Started Thread#6
Started Thread#7
Started Thread#8
Started Thread#9
Started Thread#10
workerClass.thread# 1 completed..
workerClass.thread# 10 completed..
workerClass.thread# 3 completed..
workerClass.thread# 8 completed..
workerClass.thread# 2 completed..
workerClass.thread# 9 completed..
workerClass.thread# 5 completed..
workerClass.thread# 7 completed..
workerClass.thread# 6 completed..
workerClass.thread# 4 completed..
The oracle.jdbc.pool.OracleConnectionCacheImpl class is another subclass of the
oracle.jdbc.pool.OracleDataSource which should also be looked over, that is what you really what to
use. Here is a similar example that uses the oracle.jdbc.pool.OracleConnectionCacheImpl. The general
construct is the same as the first example but note the differences in workerClass1 where some
statements have been commented ( basically a clone of workerClass from previous example ).
Visit http://www.downloadmela.com/ for more papers
The Executable Member
package package6;
import java.sql.*;
import javax.sql.*;
import oracle.jdbc.pool.*;
/**
* package6.executableTester2
*
*/
public class executableTester2 {
static int i = 0;
protected static myOracleConnectCache
connectionCache = null;
/**
* Constructor
*/
public executableTester2() throws SQLException
{
}
/**
* main
* @param args
*/
public static void main(String[] args) {
OracleConnectionPoolDataSource dataSource = null;
try{
dataSource = new OracleConnectionPoolDataSource() ;
connectionCache = new myOracleConnectCache( dataSource );
}
catch ( Exception ex ){
ex.printStackTrace();
}
while ( i++ < 10 ) {
try{
workerClass1 worker = new workerClass1();
worker.setThreadNumber( i );
worker.setConnection( connectionCache.getConnection() );
worker.start();
Visit http://www.downloadmela.com/ for more papers
System.out.println( "Started Thread#"+i );
}
catch ( Exception ex ){
ex.printStackTrace();
}
}
}
protected void finalize(){
try{
connectionCache.close();
} catch ( SQLException x) {
x.printStackTrace();
}
this.finalize();
}
}
The ConnectCacheImpl Member
package package6;
import javax.sql.ConnectionPoolDataSource;
import oracle.jdbc.pool.*;
import oracle.jdbc.driver.*;
import java.sql.*;
import java.sql.SQLException;
/**
* package6.myOracleConnectCache
*
*/
public class myOracleConnectCache extends
OracleConnectionCacheImpl {
/**
* Constructor
*/
public myOracleConnectCache( ConnectionPoolDataSource x)
throws SQLException {
initialize();
}
public void initialize() throws SQLException {
setURL("jdbc:oracle:oci8:@myDB");
setUser("scott");
setPassword("tiger");
Visit http://www.downloadmela.com/ for more papers
//
// prefab 2 connection and only grow to 4 , setting these
// to various values will demo the behavior
//clearly, if it is not
// obvious already
//
setMinLimit(2);
setMaxLimit(4);
}
}
The Worker Thread Member
package package6;
import oracle.jdbc.pool.*;
import java.sql.*;
import javax.sql.*;
/**
* package6.workerClass1
*
*/
public class workerClass1 extends Thread {
// protected OracleConnectionPoolDataSource
ocpds = null;
// protected PooledConnection pc = null;
protected Connection conn = null;
protected int threadNumber = 0;
/**
* Constructor
*/
public workerClass1() {
}
public void doWork( ) throws SQLException {
// Create a pooled connection
// pc = ocpds.getPooledConnection();
// Get a Logical connection
// conn = pc.getConnection();
Visit http://www.downloadmela.com/ for more papers
// Create a Statement
Statement stmt = conn.createStatement ();
// Select the ENAME column from the EMP table
ResultSet rset = stmt.executeQuery
("select ename from EMP");
// Iterate through the result
// and print the employee names
while (rset.next ())
// System.out.println (rset.getString (1));
;
// Close the RseultSet
rset.close();
rset = null;
// Close the Statement
stmt.close();
stmt = null;
// Close the logical connection
conn.close();
conn = null;
// Close the pooled connection
// pc.close();
// pc = null;
System.out.println( "workerClass1.thread#
"+threadNumber+" completed..");
}
public void setThreadNumber( int assignment ){
threadNumber = assignment;
}
// public void setConnectionPoolDataSource
(OracleConnectionPoolDataSource x){
// ocpds = x;
// }
public void setConnection( Connection assignment ){
conn = assignment;
Visit http://www.downloadmela.com/ for more papers
}
public void run() {
try{
doWork();
}
catch ( Exception ex ){
ex.printStackTrace();
}
}
}
The OutPut Produced
Started Thread#1
Started Thread#2
workerClass1.thread# 1 completed..
workerClass1.thread# 2 completed..
Started Thread#3
Started Thread#4
Started Thread#5
workerClass1.thread# 5 completed..
workerClass1.thread# 4 completed..
workerClass1.thread# 3 completed..
Started Thread#6
Started Thread#7
Started Thread#8
Started Thread#9
workerClass1.thread# 8 completed..
workerClass1.thread# 9 completed..
workerClass1.thread# 6 completed..
workerClass1.thread# 7 completed..
Started Thread#10
workerClass1.thread# 10 completed..
DB2 Universal claims to support JDBC 2.0, But I can only get JDBC 1.0 functionality. What can
I do?
DB2 Universal defaults to the 1.0 driver. You have to run a special program to enable the 2.0 driver
and JDK support. For detailed information, see Setting the Environment in Building Java Applets and
Applications. The page includes instructions for most supported platforms.
How do I disallow NULL values in a table?
Null capability is a column integrity constraint, normally applied at table creation time. Note that some
databases won't allow the constraint to be applied after table creation. Most databases allow a default
value for the column as well. The following SQL statement displays the NOT NULL constraint:
CREATE TABLE CoffeeTable (
Visit http://www.downloadmela.com/ for more papers
Type VARCHAR(25) NOT NULL,
Pounds INTEGER NOT NULL,
Price NUMERIC(5, 2) NOT NULL
)
How to get a field's value with ResultSet.getxxx when it is a NULL? I have tried to execute a
typical SQL statement:
select * from T-name where (clause);
But an error gets thrown because there are some NULL fields in the table.
You should not get an error/exception just because of null values in various columns. This sounds like
a driver specific problem and you should first check the original and any chained exceptions to
determine if another problem exists. In general, one may retrieve one of three values for a column that
is null, depending on the data type. For methods that return objects, null will be returned; for numeric
( get Byte(), getShort(), getInt(), getLong(), getFloat(), and getDouble() ) zero will be returned; for
getBoolean() false will be returned. To find out if the value was actually NULL, use
ResultSet.wasNull() before invoking another getXXX method.
How do I insert/update records with some of the columns having NULL value?
Use either of the following PreparedStatement methods:
public void setNull(int parameterIndex, int sqlType) throws SQLException
public void setNull(int paramIndex, int sqlType, String typeName) throws SQLException
These methods assume that the columns are nullable. In this case, you can also just omit the columns in
an INSERT statement; they will be automatically assigned null values.
Is there a way to find the primary key(s) for an Access Database table? Sun's JDBC-ODBC
driver does not implement the getPrimaryKeys() method for the DatabaseMetaData Objects.
// Use meta.getIndexInfo() will
//get you the PK index. Once
// you know the index, retrieve its column name
DatabaseMetaData meta = con.getMetaData();
String key_colname = null;
// get the primary key information
rset = meta.getIndexInfo(null,null, table_name, true,true);
while( rset.next())
{
String idx = rset.getString(6);
if( idx != null)
{
//Note: index "PrimaryKey" is Access DB specific
// other db server has diff. index syntax.
if( idx.equalsIgnoreCase("PrimaryKey"))
{
key_colname = rset.getString(9);
setPrimaryKey( key_colname );
Visit http://www.downloadmela.com/ for more papers
}
}
}
Why can't Tomcat find my Oracle JDBC drivers in classes111.zip?
TOMCAT 4.0.1 on NT4 throws the following exception when I try to connect to Oracle DB from JSP.
javax.servlet.ServletException : oracle.jdbc.driver.OracleDriver
java.lang.ClassNotFoundException: oracle:jdbc:driver:OracleDriver
But, the Oracle JDBC driver ZIP file (classes111.zip)is available in the system classpath.
Copied the Oracle Driver class file (classes111.zip) in %TOMCAT_Home - Home%\lib directory and
renamed it to classess111.jar.
Able to connect to Oracle DB from TOMCAT 4.01 via Oracle JDBC-Thin Driver.
I have an application that queries a database and retrieves the results into a JTable. This is the
code in the model that seems to be taken forever to execute, especially for a large result set:
while ( myRs.next() ) {
Vector newRow =new Vector();
for ( int i=1;i<=numOfCols;i++ )
{
newRow.addElement(myRs.getObject(i));
}
allRows.addElement(newRow);
}
fireTableChanged(null);
newRow stores each row of the resultset and allRows stores all the rows.
Are the vectors here the problem?
Is there another way of dealing with the result set that could execute faster?
java.util.Vector is largely thread safe, which means that there is a greater overhead in calling
addElement() as it is a synchronized method. If your result set is very large, and threading is not an
issue, you could use one of the thread-unsafe collections in Java 2 to save some time.
java.util.ArrayList is the likeliest candidate here.
Do not use a DefaultTableModel as it loads all of your data into memory at once, which will obviously
cause a large overhead - instead, use an AbstractTableModel and provide an implementation which
only loads data on demand, i.e. when (if) the user scrolls down through the table.
How does one get column names for rows returned in a ResultSet?
ResultSet rs = ...
...
ResultSetMetaData rsmd = rs.getMetaData();
int numCols = rsmd.getColumnCount();
for (int i = 1; i <= numCols; i++)
{
System.out.println("[" + i + "]" +
rsmd.getColumnName(i) + " {" +
rsmd.getColumnTypeName(i) + "}");
Visit http://www.downloadmela.com/ for more papers
}
What are the considerations for deciding on transaction boundaries?
Transaction processing should always deal with more than one statement and a transaction is often
described as a Logical Unit of Work ( LUW ). The rationale for transactions is that you want to know
definitively that all or none of the LUW completed successfully. Note that this automatically gives you
restart capability. Typically, there are two conditions under which you would want to use transactions:
* Multiple statements involving a single file - An example would be inserting all of a group of rows or
all price updates for a given date. You want all of these to take effect at the same time; inserting or
changing some subset is not acceptable.
* Multiple statements involving multiple files - The classic example is transferring money from one
account to another or double entry accounting; you don't want the debit to succeed and the credit to fail
because money or important records will be lost. Another example is a master/detail relationship,
where, say, the master contains a total column. If the entire LUW, writing the detail row and updating
the master row, is not completed successfully, you A) want to know that the transaction was
unsuccessful and B) that a portion of the transaction was not lost or dangling.
Therefore, determining what completes the transaction or LUW should be the deciding factor for
transaction boundaries.
How can I determine where a given table is referenced via foreign keys?
DatabaseMetaData.getExportedKeys() returns a ResultSet with data similar to that returned by
DatabaseMetaData.getImportedKeys(), except that the information relates to other tables that reference
the given table as a foreign key container.
How can I get information about foreign keys used in a table?
DatabaseMetaData.getImportedKeys() returns a ResultSet with data about foreign key columns, tables,
sequence and update and delete rules.
Can I use JDBC to execute non-standard features that my DBMS provides?
The answer is a qualified yes. As discussed under SQL Conformance: "One way the JDBC API deals
with this problem is to allow any query string to be passed through to an underlying DBMS driver. This
means that an application is free to use as much SQL functionality as desired, but it runs the risk of
receiving an error on some DBMSs. In fact, an application query may be something other than SQL, or
it may be a specialized derivative of SQL designed for specific DBMSs (for document or image
queries, for example)."
Clearly this means either giving up portability or checking the DBMS currently used before invoking
specific operations.
What is DML?
DML is an abbreviation for Data Manipulation Language. This portion of the SQL standard is
concerned with manipulating the data in a database as opposed to the structure of a database. The core
verbs for DML are SELECT, INSERT, DELETE, UPDATE, COMMIT and ROLLBACK.
What is the significance of DataBaseMetaData.tableIndexStatistics? How to obtain and use it?
To answer the second question first, the tableIndexStatistic constant in the TYPE column will identify
one of the rows in the ResultSet returned when DatabaseMetaData.getIndexInfo() is invoked. If you
analyze the wordy API, a tableIndexStatistic row will contain the number of rows in the table in the
CARDINALITY column and the number of pages used for the table in the PAGES column.
What types of DataSource objects are specified in the Optional Package?
Visit http://www.downloadmela.com/ for more papers
* Basic - Provides a standard Connection object.
* Pooled - Provides a Connection pool and returns a Connection that is controlled by the pool.
* Distributed - Provides a Connection that can participate in distributed transactions ( more than one
DBMS is involved). It is anticipated, but not enforced, that a distributed DataSource will also provide
pooling.
However, there are no standard methods available in the DataSource class to determine if one has
obtained a pooled and/or distributed Connection.
What is a JDBC 2.0 DataSource?
The DataSource class was introduced in the JDBC 2.0 Optional Package as an easier, more generic
means of obtaining a Connection. The actual driver providing services is defined to the DataSource
outside the application ( Of course, a production quality app can and should provide this information
outside the app anyway, usually with properties files or ResourceBundles ). The documentation
expresses the view that DataSource will replace the common DriverManager method.
Does the database server have to be running Java or have Java support in order for my remote
JDBC client app to access the database?
The answer should always be no. The two critical requirements are LAN/internet connectivity and an
appropriate JDBC driver. Connectivity is usually via TCP/IP, but other communication protocols are
possible. Unspoken, but assumed here is that the DBMS has been started to listen on a communications
port. It is the JDBC driver's job to convert the SQL statements and JDBC calls to the DBMS' native
protocol. From the server's point of view, it's just another data request coming into the port, the
programming language used to send the data is irrelevant at that point.
Which Java and java.sql data types map to my specific database types?
JDBC is, of necessity, reliant on the driver and underlying DBMS. These do not always adhere to
standards as closely as we would like, including differing names for standard Java types. To deal with
this, first, there are a number of tables available in the JDK JDBC documentation dealing with types.
Are the code examples from the JDBC API Tutorial and Reference, Second Edition available
online?
Yes.
When an SQL select statement doesn't return any rows, is an SQLException thrown?
No. If you want to throw an exception, you could wrap your SQL related code in a custom class and
throw something like ObjectNotFoundException when the returned ResultSet is empty.
Why should I consider optimistic versus pessimistic approaches to database updates?
In a modern database, possibly the two most important issues are data integrity and concurrency
( multiple users have access to and can update the data ). Either approach can be appropriate,
depending on the application, but it is important to be aware of possible consequences to avoid being
blindsided.
A pessimistic approach, with locks, is usually seen as good for data integrity, although it can be bad for
concurrency, especially the longer a lock is held. In particular, it guarantees against 'lost updates' -
defined as an update performed by one process between the time of access and update by another
process, which overwrites the interim update. However, other users are blocked from updating the data
and possibly reading it as well if the read access also tries to acquire a lock. A notorious problem can
arise when a user accesses data for update and then doesn't act on it for a period of time. Another
situation that occurred with one of my clients is that a batch ( non-interactive ) process may need to
Visit http://www.downloadmela.com/ for more papers
update data while an interactive user has an update lock on the same data. In that case, data integrity
goes out the window and, depending on how the application is written, more problems may be
introduced. ( No, we did not write the interactive update program and yes, we had recovery procedures
in place. )
An optimstic approach can alleviate lock concurrency problems, but requires more code and care for
integrity. The "optimistic" definition usually says that expectations of update clashes are rare, but I
view them as normal occurrances in a heavily used database. The basics are that any changes between
time of access and time of update must be detected and taken into account. This is often done by
comparing timestamps, but one must be sure that the timestamp is always changed for an update and,
of course, that the table contains a timestamp column. A more involved, but more complete method
involves saving the original columns and using them in the 'Where' clause of the Update statement. If
the update fails, the data has changed and the latest data should be reaccessed.
What is optimistic concurrency?
An optimistic approach dispenses with locks ( except during the actual update ) and usually involves
comparison of timestamps, or generations of data to ensure that data hasn't changed between access and
update times. It's generally explained that the term optimistic is used because the expectation is that a
clash between multiple updates to the same data will seldom occur.
What is pessimistic concurrency?
With a pessimistic approach, locks are used to ensure that no users, other than the one who holds the
lock, can update data. It's generally explained that the term pessimistic is used because the expectation
is that many users will try to update the same data, so one is pessimistic that an update will be able to
complete properly. Locks may be acquired, depending on the DBMS vendor, automatically via the
selected Isolation Level. Some vendors also implement 'Select... for Update', which explicitly acquires
a lock.
Can I get information about a ResultSet's associated Statement and Connection in a method
without having or adding specific arguments for the Statement and Connection?
Yes. Use ResultSet.getStatement(). From the resulting Statement you can use
Statement.getConnection().
How can I tell if my JDBC driver normalizes java.sql.Date and java.sql.Time objects?
To actually determine the values, the objects must be converted to a java.util.Date and examined. See
What does normalization mean for java.sql.Date and java.sql.Time? for the definition of normalization.
Notice that even a debugger will not show whether these objects have been normalized, since the
getXXX methods in java.sql.Date for time elements and in java.sql.Time for date elements throw an
exception.
So, while a java.sql.Date may show 2001-07-26, it's normalized only if the java.util.Date value is:
Thu Jul 26 00:00:00 EDT 2001
and while a java.sql.Time may show 14:01:00, it's normalized only if the java.util.Date value is:
Thu Jan 01 14:01:00 EST 1970
What is the most efficient method of replicating data between databases using JDBC?
Within Java, the most efficient method would be, opening connections using the JDBC and inserting or
updating the records from one database to the other database, but it depends upon the databases being
replicated. If you are using Oracle databases, it has standard methods for replication, and you do not
need the JDBC for the replication. Use snapshots like updateable and read-only.
There are different kind of replication. Let us consider the most widely used ones:
Visit http://www.downloadmela.com/ for more papers
A) One Master - One slave
I) If there is not a significant difference between the structure of the database tables, the following
method would be useful.
FromDatabase=A; ToDatabase=B
1) Open JDBC connections between the databases A and B.
2) Read a record (RA ) from A using an SQL query.
3) Store the values in the local variables in the Java program.
4) Insert the record in B if PK does not exist for the record RA in B.
5) If the PK exists in B, update the record in B.
6) Repeat the steps 2-5 'til all the records are read by the query.
7) If there are multiple tables to be replicated, repeat steps 2-7 using the different queries.
II)If there is significant difference between the structure of the database tables, the following method
would be useful.
FromDatabase=A; ToDatabase=B
1) Open the JDBC connections to the databases A.
2) Read a record ( RA ) from A using an SQL query.
3) Write the output to an XML file-XMLA, according to the DTD for the records for the database A
structure.
4) Repeat steps 2 & 3 'til all the records are written to XMLA.
5) If there are more queries, repeat steps repeat steps from 2-4 and write the records to the different
entities in the XML file.
6) Transform the XMLA file using the XSL and XSLT to the format useful for the database B and
write to the XML file-XMLB.
7) Open the second JDBC connection to the Database B.
8) Read the XMLB file, one record at a time.
9) Insert the record in B if PK does not exist for the record RA in B.
10) If the PK exists in B, update the record in B.
B) One Master - Multiple slaves
The difference here is to open multiple JDBC connections to write to the different databases one record
at a time.
C) Multiple Masters:
For multiple masters, use timestamps to compare the times of the records to find out which is the latest
record when a record is found in all the master databases. Alternatively, create a column to store the
time and date a record is inserted or updated. When records are deleted, record the event in a log file
along with the PK.
Prepared statements and batch updates should be used wherever possible in this scenario.
What is the difference between setMaxRows(int) and SetFetchSize(int)? Can either reduce
processing time?
setFetchSize(int) defines the number of rows that will be read from the database when the ResultSet
needs more rows. The method in the java.sql.Statement interface will set the 'default' value for all the
ResultSet derived from that Statement; the method in the java.sql.ResultSet interface will override that
value for a specific ResultSet. Since database fetches can be expensive in a networked environment,
fetch size has an impact on performance.
setMaxRows(int) sets the limit of the maximum nuber of rows in a ResultSet object. If this limit is
exceeded, the excess rows are "silently dropped". That's all the API says, so the setMaxRows method
Visit http://www.downloadmela.com/ for more papers
may not help performance at all other than to decrease memory usage. A value of 0 (default) means no
limit.
Since we're talking about interfaces, be careful because the implementation of drivers is often different
from database to database and, in some cases, may not be implemented or have a null implementation.
Always refer to the driver documentation.
What is JDO?
JDO provides for the transparent persistence of data in a data store agnostic manner, supporting object,
hierarchical, as well as relational stores.
When I intersperse table creation or other DDL statements with DML statements, I have a
problem with a transaction being commited before I want it to be. Everything ( commit and
rollback ) works fine as long as I don't create another table. How can I resolve the issue?
While the questioner found a partially workable method for his particular DBMS, as mentioned in the
section on transactions in my JDBC 2.0 Fundamentals Short Course:
DDL statements in a transaction may be ignored or may cause a commit to occur. The behavior is
DBMS dependent and can be discovered by use of
DatabaseMetaData.dataDefinitionCausesTransactionCommit() and
DatabaseMetaData.dataDefinitionIgnoredInTransactions(). One way to avoid unexpected results is to
separate DML and DDL transactions.
The only generally effective way to "rollback" table creation is to delete the table.
What's the best way, in terms of performance, to do multiple insert/update statements, a
PreparedStatement or Batch Updates?
Because PreparedStatement objects are precompiled, their execution can be faster than that of
Statement objects. Consequently, an SQL statement that is executed many times is often created as a
PreparedStatement object to increase efficiency.
A CallableStatement object provides a way to call stored procedures in a standard manner for all
DBMSes. Their execution can be faster than that of PreparedStatement object.
Batch updates are used when you want to execute multiple statements together. Actually, there is no
conflict here. While it depends on the driver/DBMS engine as to whether or not you will get an actual
performance benefit from batch updates, Statement, PreparedStatement, and CallableStatement can all
execute the addBatch() method.
I need to have result set on a page where the user can sort on the column headers. Any ideas?
One possibility: Have an optional field in your form or GET url called (appropriately) ORDER with a
default value of either "no order" or whatever you want your default ordering to be (i.e. timestamp,
username, whatever). When you get your request, see what the value of the ORDER element is. If it's
null or blank, use the default. Use that value to build your SQL query, and display the results to the
page. If you're caching data in your servlet, you can use the Collection framework to sort your data (see
java.util.Collections) if you can get it into a List format. Then, you can create a Collator which can
impose a total ordering on your results.
What are the components of the JDBC URL for Oracle's "thin" driver and how do I use them?
Briefly: jdbc:oracle:thin:@hostname:port:oracle-sid
1. in green the Oracle sub-protocol (can be oracle:oci7:@, oracle:oci8:@, racle:thin:@, etc...) is related
on the driver you are unsign and the protocol to communicate with server.
2. in red the network machine name, or its ip address, to locate the server where oracle is running.
3. in blue the port (it is complementary to the address to select the specific oracle service)
Visit http://www.downloadmela.com/ for more papers
4. in magenta the sid, select on which database you want to connect.
example:
jdbc:oracle:thin:@MyOracleHost:1521:MyDB
IHere's an example:
jdbc:oracle:thin:scott/tiger@MyOracleHost:1521:MyDB
where user=scott and pass=tiger.
Why doesn't JDBC accept URLs instead of a URL string?
In order for something to be a java.net.URL, a protocol handler needs to be installed. Since there is no
one universal protocol for databases behind JDBC, the URLs are treated as strings. In Java 1.4, these
URL strings have a class called java.net.URI. However, you still can't use a URI to load a JDBC driver,
without converting it to a string.
What JDBC objects generate SQLWarnings?
Connections, Statements and ResultSets all have a getWarnings method that allows retrieval. Keep in
mind that prior ResultSet warnings are cleared on each new read and prior Statement warnings are
cleared with each new execution. getWarnings() itself does not clear existing warnings, but each object
has a clearWarnings method.
What's the fastest way to normalize a Time object?
Of the two recommended ways when using a Calendar( see How do I create a java.sql.Time object? ),
in my tests, this code ( where c is a Calendar and t is a Time ):
c.set( Calendar.YEAR, 1970 );
c.set( Calendar.MONTH, Calendar.JANUARY );
c.set( Calendar.DATE, 1 );
c.set( Calendar.MILLISECOND, 0 );
t = new java.sql.Time( c.getTime().getTime() );
was always at least twice as fast as:
t = java.sql.Time.valueOf(
c.get(Calendar.HOUR_OF_DAY) + ":" +
c.get(Calendar.MINUTE) + ":" +
c.get(Calendar.SECOND) );
When the argument sent to valueOf() was hardcoded ( i.e. valueOf( "13:50:10" ), the time difference
over 1000 iterations was negligible.
What does normalization mean for java.sql.Date and java.sql.Time?
These classes are thin wrappers extending java.util.Date, which has both date and time components.
java.sql.Date should carry only date information and a normalized instance has the time information set
to zeros. java.sql.Time should carry only time information and a normalized instance has the date set to
the Java epoch ( January 1, 1970 ) and the milliseconds portion set to zero.
How do I create a java.sql.Date object?
java.sql.Date descends from java.util.Date, but uses only the year, month and day values. There are two
methods to create a Date object. The first uses a Calendar object, setting the year, month and day
Visit http://www.downloadmela.com/ for more papers
portions to the desired values. The hour, minute, second and millisecond values must be set to zero. At
that point, Calendar.getTime().getTime() is invoked to get the java.util.Date milliseconds. That value is
then passed to a java.sql.Date constructor:
Calendar cal = Calendar.getInstance();
// set Date portion to January 1, 1970
cal.set( cal.YEAR, 1970 );
cal.set( cal.MONTH, cal.JANUARY );
cal.set( cal.DATE, 1 );
cal.set( cal.HOUR_OF_DAY, 0 );
cal.set( cal.MINUTE, 0 );
cal.set( cal.SECOND, 0 );
cal.set( cal.MILLISECOND, 0 );
java.sql.Date jsqlD =
new java.sql.Date( cal.getTime().getTime() );
The second method is java.sql.Date's valueOf method. valueOf() accepts a String, which must be the
date in JDBC time escape format - "yyyy-mm-dd". For example,
java.sql.Date jsqlD = java.sql.Date.valueOf( "2010-01-31" );
creates a Date object representing January 31, 2010. To use this method with a Calendar object, use:
java.sql.Date jsqlD = java.sql.Date.valueOf(
cal.get(cal.YEAR) + ":" +
cal.get(cal.MONTH) + ":" +
cal.get(cal.DATE) );
which produces a Date object with the same value as the first example.
How do I create a java.sql.Time object?
java.sql.Time descends from java.util.Date, but uses only the hour, minute and second values. There
are two methods to create a Time object. The first uses a Calendar object, setting the year, month and
day portions to January 1, 1970, which is Java's zero epoch. The millisecond value must also be set to
zero. At that point, Calendar.getTime().getTime() is invoked to get the time in milliseconds. That value
is then passed to a Time constructor:
Calendar cal = Calendar.getInstance();
// set Date portion to January 1, 1970
cal.set( cal.YEAR, 1970 );
cal.set( cal.MONTH, cal.JANUARY );
cal.set( cal.DATE, 1 );
cal.set( cal.MILLISECOND, 0 );
java.sql.Time jsqlT =
new java.sql.Time( cal.getTime().getTime() );
Visit http://www.downloadmela.com/ for more papers
The second method is Time's valueOf method. valueOf() accepts a String, which must be the time in
JDBC time escape format - "hh:mm:ss". For example,
java.sql.Time jsqlT = java.sql.Time.valueOf( "18:05:00" );
creates a Time object representing 6:05 p.m. To use this method with a Calendar object, use:
java.sql.Time jsqlT = java.sql.Time.valueOf(
cal.get(cal.HOUR_OF_DAY) + ":" +
cal.get(cal.MINUTE) + ":" +
cal.get(cal.SECOND) );
which produces a Time object with the same value as the first example.
What scalar functions can I expect to be supported by JDBC?
JDBC supports numeric, string, time, date, system, and conversion functions on scalar values. For a list
of those supported and additional information, see section A.1.4 Support Scalar Functions in the JDBC
Data Access API For Driver Writers. Note that drivers are only expected to support those scalar
functions that are supported by the underlying DB engine.
What does setFetchSize() really do?
The API documentation explains it pretty well, but a number of programmers seem to have a
misconception of its functionality. The first thing to note is that it may do nothing at all; it is only a
hint, even to a JDBC Compliant driver. setFetchSize() is really a request for a certain sized blocking
factor, that is, how much data to send at a time.
Because trips to the server are expensive, sending a larger number of rows can be more efficient. It may
be more efficient on the server side as well, depending on the particular SQL statement and the DB
engine. That would be true if the data could be read straight off an index and the DB engine paid
attention to the fetch size. In that case, the DB engine could return only enough data per request to
match the fetch size. Don't count on that behavior. In general, the fetch size will be transparent to your
program and only determines how often requests are sent to the server as you traverse the data.
Also, both Statement and ResultSet have setFetchSize methods. If used with a Statement, all ResultSets
returned by that Statement will have the same fetch size. The method can be used at any time to change
the fetch size for a given ResultSet. To determine the current or default size, use the getFetchSize
methods.
Is there a practical limit for the number of SQL statements that can be added to an instance of a
Statement object
While the specification makes no mention of any size limitation for Statement.addBatch(), this seems
to be dependent, as usual, on the driver. Among other things, it depends on the type of
container/collection used. I know of at least one driver that uses a Vector and grows as needed. I've
seen questions about another driver that appears to peak somewhere between 500 and 1000 statements.
Unfortunately, there doesn't appear to be any metadata information regarding possible limits. Of
course, in a production quality driver, one would expect an exception from an addBatch() invocation
that went beyond the command list's limits.
How can I determine whether a Statement and its ResultSet will be closed on a commit or
rollback?
Use the DatabaseMetaData methods supportsOpenStatementsAcrossCommit() and
supportsOpenStatementsAcrossRollback().
Visit http://www.downloadmela.com/ for more papers
How do I get runtime information about the JDBC Driver?
Use the following DatabaseMetaData methods:
getDriverMajorVersion()
getDriverMinorVersion()
getDriverName()
getDriverVersion()
How do I create an updatable ResultSet?
Just as is required with a scrollable ResultSet, the Statement must be capable of returning an updatable
ResultSet. This is accomplished by asking the Connection to return the appropriate type of Statement
using Connection.createStatement(int resultSetType, int resultSetConcurrency). The
resultSetConcurrency parameter must be ResultSet.CONCUR_UPDATABLE. The actual code would
look like this:
Statement stmt = con.createStatement( ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE );
Note that the spec allows a driver to return a different type of Statement/ResultSet than that requested,
depending on capabilities and circumstances, so the actual type returned should be checked with
ResultSet.getConcurrency().
How can I connect to an Oracle database not on the web server from an untrusted applet?
You can use the thin ORACLE JDBC driver in an applet (with some extra parameters on the JDBC
URL). Then, if you have NET8, you can use the connection manager of NET8 on the web server to
proxy the connection request to the database server.
How can I insert multiple rows into a database in a single transaction?
//turn off the implicit commit
Connection.setAutoCommit(false);
//..your insert/update/delete goes here
Connection.Commit();
a new transaction is implicitly started.
JDBC 2.0 provides a set of methods for executing a batch of database commands. Specifically, the
java.sql.Statement interface provides three methods: addBatch(), clearBatch() and executeBatch().
Their documentation is pretty straight forward.
The implementation of these methods is optional, so be sure that your driver supports these.
How do I display and parse a date?
The Java I18N way is to use a DateFormat. While SimpleDateFormat, which is generally returned,
creates a large number of objects, it is locale aware and will handle most of your needs. The following
sample code initially creates a java.sql.Date object and formats it for the default locale. An initial
actionPerformed call additionally formats/displays it for a German locale and also displays the
resulting java.sql.Date in standard escape format. Other dates can be entered and parsed after the initial
display.
// JDFDP.java - Display and Parse java.sql.Date
import java.sql.*;
Visit http://www.downloadmela.com/ for more papers
import javax.swing.*;
import java.awt.*;
import java.awt.event.*;
import java.text.*;
import java.util.*;
public class JDFDP extends JFrame
implements ActionListener,
WindowListener
{
// create a java.sql.Date
java.sql.Date jsqlDate = new java.sql.Date(
System.currentTimeMillis() );
DateFormat dfLocal = DateFormat.getDateInstance(
DateFormat.SHORT );
DateFormat dfGermany = DateFormat.getDateInstance(
DateFormat.SHORT, Locale.GERMANY );
JButton jb = new JButton( "Go" );
JLabel jlI = new JLabel("Input a Date:"),
jlD = new JLabel("Display German:"),
jlP = new JLabel("Parsed:");
JPanel jp = new JPanel();
JTextField jtI = new JTextField( 10 ),
jtD = new JTextField( 10 ),
jtP = new JTextField( 10 );
public JDFDP()
{
super( "JDFDP" );
addWindowListener( this );
jb.addActionListener( this );
jp.add(jlI);
jp.add(jtI);
jp.add(jb);
jp.add(jlD);
jp.add(jtD);
jp.add(jlP);
jp.add(jtP);
Visit http://www.downloadmela.com/ for more papers
getContentPane().add( jp, BorderLayout.CENTER );
pack();
// set text by sending dummy event
jtI.setText( dfLocal.format( jsqlDate ) );
actionPerformed(
new ActionEvent( this, 12, "12" ) );
show();
} // end constructor
// ActionListener Implementation
public void actionPerformed(ActionEvent e)
{
jtD.setText( "" );
jtP.setText( "" );
try
{
java.util.Date d = dfLocal.parse(
jtI.getText() );
jtI.setText( dfLocal.format( d ) );
jtD.setText( dfGermany.format( d ) );
d = dfGermany.parse( jtD.getText() );
// get new java.sql.Date
jsqlDate = new java.sql.Date( d.getTime() );
jtP.setText( jsqlDate.toString() );
}
catch( ParseException pe ) { jtI.setText( "" ); }
} // End actionPerformed
// Window Listener Implementation
public void windowOpened(WindowEvent e) {}
public void windowClosing(WindowEvent e)
{
dispose();
System.exit(0);
}
public void windowClosed(WindowEvent e) {}
public void windowIconified(WindowEvent e) {}
public void windowDeiconified(WindowEvent e) {}
public void windowActivated(WindowEvent e) {}
Visit http://www.downloadmela.com/ for more papers
public void windowDeactivated(WindowEvent e) {}
// End Window Listener Implementation
public static void main(String[] args)
{
new JDFDP();
}
} // end class JDFDP
How can I retrieve string data from a database in Unicode format?
The data is already in Unicode when it arrives
in your program. Conversion from and to the
encoding/charset/CCSID in the database from/to
Unicode in the program is part of the JDBC driver's job.
If, for some reason, you want to see the data in
'\uHHHH' format ( where 'H' is the hex value ),
the following code, while not very efficient,
should give you some ideas:
public class UniFormat
{
public static void main( String[] args )
{
char[] ac = args[0].toCharArray();
int iValue;
String s = null;
StringBuffer sb = new StringBuffer();
for( int ndx = 0; ndx < ac.length; ndx++ )
{
iValue = ac[ndx];
if( iValue < 0x10 )
{
s = "\\u000";
}
else
if( iValue < 0x100 )
{
s = "\\u00";
}
else
Visit http://www.downloadmela.com/ for more papers
if( iValue < 0x1000 )
{
s = "\\u0";
}
sb.append( s + Integer.toHexString( iValue ) );
} // end for
System.out.println("The Unicode format of " +
args[0] + " is " + sb );
} // end main
} // end class UniFormat
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
Visit http://www.downloadmela.com/ for more papers
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();
Visit http://www.downloadmela.com/ for more papers
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.
How can I connect to an Excel spreadsheet file using jdbc?
Let's say you have created the following Excel spreadsheet in a worksheet called Sheet1 (the default
sheet name). And you've saved the file in c:\users.xls.
USERID FIRST_NAME LAST_NAME
pkua Peter Kua
jlsmith John Smith
gh2312 Everett Johnson
chimera Faiz Abdullah
roy6943 Roy Sudirman
Since Excel comes with an ODBC driver, we'll use the JDBC-ODBC bridge driver that comes
packaged with Sun's JDK to connect to our spreadsheet.
In Excel, the name of the worksheet is the equivalent of the database table name, while the header
names found on the first row of the worksheet is the equivalent of the table field names. Therefore,
when accessing Excel via jdbc, it is very important to place your data with the headers starting at row
1.
1. Create a new ODBC Data Source using the Microsoft Excel Driver. Name the DSN "excel", and
have it point to c:\users.xls.
2. Type in the following code:
package classes;
import java.sql.*;
Visit http://www.downloadmela.com/ for more papers
public class TestServer
{
static
{
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}
catch (Exception e) {
System.err.println(e);
}
}
public static void main(String args[]) {
Connection conn=null;
Statement stmt=null;
String sql="";
ResultSet rs=null;
try {
conn=DriverManager.getConnection("jdbc:odbc:excel","","");
stmt=conn.createStatement();
sql="select * from [Sheet1$]";
rs=stmt.executeQuery(sql);
while(rs.next()){
System.out.println(rs.getString("USERID")+
" "+ rs.getString("FIRST_NAME")+" "+
rs.getString("LAST_NAME"));
}
}
catch (Exception e){
System.err.println(e);
}
finally {
try{
rs.close();
stmt.close();
conn.close();
rs=null;
stmt=null;
conn=null;
}
catch(Exception e){}
}
}
Visit http://www.downloadmela.com/ for more papers
}
Notice that we have connected to the Excel ODBC Data Source the same way we would connect to any
normal database server.
The only significant difference is in the SELECT statement. Although your data is residing in the
worksheet called "Sheet1", you'll have to refer to the sheet as Sheet1$ in your SQL statements. And
because the dollar sign symbol is a reserved character in SQL, you'll have to encapsulate the word
Sheet1$ in brackets, as shown in the code.
How do I execute stored procedures?
Here is an example on how to execute a stored procedure with JDBC (to use this in a servlet is the
same the only thing is that you create the connection and callable statement in the init() of the servlet):
package DBTest;
import java.sql.*;
public class JdbcTest {
private String msDbUrl = "jdbc:odbc:ms";
private String msJdbcClass = "sun.jdbc.odbc.JdbcOdbcDriver";
private Connection mcDbAccess;
private CallableStatement msProcedure;
public JdbcTest() {
try {
Class.forName( msDbUrl ).newInstance();
mcDbAccess = DriverManager.getConnection( msJdbcClass, "milestone", "milestone" );
msProcedure = mcDbAccess.prepareCall(
"{? = call sp_sav_Bom_Header( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) }"
);
msProcedure.registerOutParameter( 1, java.sql.Types.VARCHAR );
msProcedure.setInt( 2, -1 );
msProcedure.setInt( 3, 39 );
msProcedure.setString( 4, "format" );
long ltTest = new java.util.Date().getTime();
System.out.println( "Today: " + ltTest );
msProcedure.setTimestamp( 5, new Timestamp( ltTest ) );
msProcedure.setString( 6, "type" );
msProcedure.setString( 7, "submitter" );
msProcedure.setString( 8, "email" );
msProcedure.setString( 9, "phone" );
msProcedure.setString( 10, "comments" );
msProcedure.setString( 11, "label" );
msProcedure.setInt( 12, 52 );
msProcedure.setBoolean( 13, true );
msProcedure.setBoolean( 14, false );
msProcedure.setInt( 15, 53 );
msProcedure.setString( 16, "runtime" );
msProcedure.setString( 17, "configuration" );
Visit http://www.downloadmela.com/ for more papers
msProcedure.setBoolean( 18, true );
msProcedure.setBoolean( 19, false );
msProcedure.setString( 20, "special instructions" );
msProcedure.setInt( 21, 54 );
ResultSet lrsReturn = null;
System.out.println( "Execute: " + (lrsReturn = msProcedure.executeQuery() ) );
while( lrsReturn.next() ) {
System.out.println( "Got from result set: " + lrsReturn.getInt( 1 ) );
}
System.out.println( "Got from stored procedure: " + msProcedure.getString( 1 ) );
} catch( Throwable e ) {
e.printStackTrace();
}
}
public static void main(String[] args) {
new JdbcTest();
}
}
I also tried it by using a native JDBC driver (i-net) and it also works fine. The only problem we
encounter with JDBC-ODBC bridge is that a stored procedure pads spaces to the full length of a
VARCHAR but the native JDBC behaves right. Therefore I suggest to use JDBC native drivers.
The above example uses the MS SQL Server.
How can I get data from multiple ResultSets?
With certain database systems, a stored procedure can return multiple result sets, multiple update
counts, or some combination of both. Also, if you are providing a user with the ability to enter any SQL
statement, you don't know if you are going to get a ResultSet or an update count back from each
statement, without analyzing the contents. The Statement.execute() method helps in these cases.
Method Statement.execute() returns a boolean to tell you the type of response:
* true indicates next result is a ResultSet
Use Statement.getResultSet to get the ResultSet
* false indicates next result is an update count
Use Statement.getUpdateCount to get the update count
* false also indicates no more results
Update count is -1 when no more results (usually 0 or positive)
After processing each response, you use Statement.getMoreResults to check for more results, again
returning a boolean. The following demonstrates the processing of multiple result sets:
boolean result = stmt.execute(" ... ");
int updateCount = stmt.getUpdateCount();
while (result || (updateCount != -1)) {
Visit http://www.downloadmela.com/ for more papers
if(result) {
ResultSet r = stmt.getResultSet();
// process result set
} else if(updateCount != -1) {
// process update count
}
result = stmt.getMoreResults();
updateCount = stmt.getUpdateCount();
}
How can resultset records be restricted to certain rows?
The easy answer is "Use a JDBC 2.0 compliant driver".
With a 2.0 driver, you can use the setFetchSize() method within a Statement or a ResultSet object.
For example,
Statement stmt = con.createStatement();
stmt.setFetchSize(400);
ResultSet rs = stmt.executeQuery("select * from customers");
will change the default fetch size to 400.
You can also control the direction in which the rows are processed. For instance:
stmt.setFetchDirection(ResultSet.FETCH_REVERSE)
will process the rows from bottom up.
The driver manager usually defaults to the most efficient fetch size...so you may try experimenting with
different value for optimal performance.
How do I insert an image file (or other raw data) into a database?
All raw data types (including binary documents or images) should be read and uploaded to the database
as an array of bytes, byte[]. Originating from a binary file,
1. Read all data from the file using a FileInputStream.
2. Create a byte array from the read data.
3. Use method setBytes(int index, byte[] data); of java.sql.PreparedStatement to upload the data.
How can I connect from an applet to a database on the server?
There are two ways of connecting to a database on the server side.
1. The hard way. Untrusted applets cannot touch the hard disk of a computer. Thus, your applet cannot
use native or other local files (such as JDBC database drivers) on your hard drive. The first alternative
solution is to create a digitally signed applet which may use locally installed JDBC drivers, able to
connect directly to the database on the server side.
2. The easy way. Untrusted applets may only open a network connection to the server from which they
were downloaded. Thus, you must place a database listener (either the database itself, or a middleware
server) on the server node from which the applet was downloaded. The applet would open a socket
connection to the middleware server, located on the same computer node as the webserver from which
the applet was downloaded. The middleware server is used as a mediator, connecting to and extract
data from the database.
Can I use the JDBC-ODBC bridge driver in an applet?
Short answer: No.
Longer answer: You may create a digitally signed applet using a Certicate to circumvent the security
Visit http://www.downloadmela.com/ for more papers
sandbox of the browser.
Which is the preferred collection class to use for storing database result sets?
When retrieving database results, the best collection implementation to use is the LinkedList. The
benefits include:
* Retains the original retrieval order
* Has quick insertion at the head/tail
* Doesn't have an internal size limitation like a Vector where when the size is exceeded a new internal
structure is created (or you have to find out size beforehand to size properly)
* Permits user-controlled synchronization unlike the pre-Collections Vector which is always
synchronized
Basically:
ResultSet result = stmt.executeQuery("...");
List list = new LinkedList();
while(result.next()) {
list.add(result.getString("col"));
}
If there are multiple columns in the result set, you'll have to combine them into their own data structure
for each row. Arrays work well for that as you know the size, though a custom class might be best so
you can convert the contents to the proper type when extracting from databse, instead of later.
The java.sql package contains mostly interfaces. When and how are these interfaces implemented
while connecting to database?
The implementation of these interfaces is all part of the driver. A JDBC driver is not just one class - it
is a complete set of database-specific implementations for the interfaces defined by the JDBC.
These driver classes come into being through a bootstrap process. This is best shown by stepping
through the process of using JDBC to connect to a database, using Oracle's type 4 JDBC driver as an
example:
* First, the main driver class must be loaded into the VM:
Class.forName("oracle.jdbc.driver.OracleDriver");
The specified driver must implement the Driver interface. A class initializer (static code block) within
the OracleDriver class registers the driver with the DriverManager.
* Next, we need to obtain a connection to the database:
String jdbcURL = "jdbc:oracle:thin:@www.jguru.com:1521:ORCL";
Connection connection = DriverManager.getConnection(jdbcURL);
DriverManager determines which registered driver to use by invoking the acceptsURL(String url)
method of each driver, passing each the JDBC URL. The first driver to return "true" in response will be
used for this connection. In this example, OracleDriver will return "true", so DriverManager then
invokes the connect() method of OracleDriver to obtain an instance of OracleConnection. It is this
database-specific connection instance implementing the Connection interface that is passed back from
the DriverManager.getConnection() call.
* The bootstrap process continues when you create a statement:
Statement statement = connection.createStatement();
Visit http://www.downloadmela.com/ for more papers
The connection reference points to an instance of OracleConnection. This database-specific
implementation of Connection returns a database-specific implementation of Statement, namely
OracleStatement
* Invoking the execute() method of this statement object will execute the database-specific code
necessary to issue an SQL statement and retrieve the results:
ResultSet result = statement.executeQuery("SELECT * FROM TABLE");
Again, what is actually returned is an instance of OracleResultSet, which is an Oracle-specific
implementation of the ResultSet interface.
So the purpose of a JDBC driver is to provide these implementations that hide all the database-specific
details behind standard Java interfaces.
How can I manage special characters (for example: " _ ' % ) when I execute an INSERT query?
If I don't filter the quoting marks or the apostrophe, for example, the SQL string will cause an
error.
In JDBC, strings containing SQL commands are just normal strings - the SQL is not parsed or
interpreted by the Java compiler. So there is no special mechanism for dealing with special characters;
if you need to use a quote (") within a Java string, you must escape it.
The Java programming language supports all the standard C escapes, such as \n for newline, \t for tab,
etc. In this case, you would use \" to represent a quote within a string literal:
String stringWithQuote =
"\"No,\" he replied, \"I did not like that salted licorice.\"";
This only takes care of one part of the problem: letting us control the exact string that is passed on to
the database. If you want tell the database to interpret characters like a single quote (') literally (and not
as string delimiters, for instance), you need to use a different method. JDBC allows you to specify a
separate, SQL escape character that causes the character following to be interpreted literally, rather
than as a special character.
An example of this is if you want to issue the following SQL command:
SELECT * FROM BIRDS
WHERE SPECIES='Williamson's Sapsucker'
In this case, the apostrophe in "Williamson's" is going to cause a problem for the database because SQL
will interpret it as a string delimiter. It is not good enough to use the C-style escape \', because that
substitution would be made by the Java compiler before the string is sent to the database.
Different flavors of SQL provide different methods to deal with this situation. JDBC abstracts these
methods and provides a solution that works for all databases. With JDBC you could write the SQL as
follows:
Statement statement = // obtain reference to a Statement
statement.executeQuery(
"SELECT * FROM BIRDS WHERE SPECIES='Williamson/'s Sapsucker' {escape '/'}");
The clause in curly braces, namely {escape '/'}, is special syntax used to inform JDBC drivers what
character the programmer has chosen as an escape character. The forward slash used as the SQL escape
Visit http://www.downloadmela.com/ for more papers
has no special meaning to the Java compiler; this escape sequence is interpreted by the JDBC driver
and translated into database-specific SQL before the SQL command is issued to the database.
Escape characters are also important when using the SQL LIKE clause. This usage is explicitly
addressed in section 11.5 of the JDBC specification:
The characters "%" and "_" have special meaning in SQL LIKE clauses (to match zero or more
characters, or exactly one character, respectively). In order to interpret them literally, they can be
preceded with a special escape character in strings, e.g. "\". In order to specify the escape character
used to quote these characters, include the following syntax on the end of the query:
{escape 'escape-character'}
For example, the query
SELECT NAME FROM IDENTIFIERS WHERE ID LIKE '\_%' {escape '\'}
finds identifier names that begin with an underbar.
How can I make batch updates using JDBC?
One of the more advanced features of JDBC 2.0 is the ability to submit multiple update statements to
the database for processing as a single unit. This batch updating can be significantly more efficient
compared to JDBC 1.0, where each update statement has to be executed separately.
Consider the following code segment demonstrating a batch update:
try {
dbCon.setAutoCommit(false);
Statement stmt= dbCon.createStatement();
stmt.addBatch("INSERT INTO bugs "+
"VALUES (1007, 'Server stack overflow', 1,2,{d '1999-01-01'})");
stmt.addBatch("INSERT INTO bugs "+
"VALUES (1008,'Cannot load DLL', 3,1,{d '1999-01-01'})");
stmt.addBatch("INSERT INTO bugs "+
"VALUES (1009,'Applet locks up',2,2,{d '1999-01-01'})");
int[] updCnt = stmt.executeBatch();
dbCon.commit();
} catch (BatchUpdateException be) {
//handle batch update exception
int[] counts = be.getUpdateCounts();
for (int i=0; I counts.length; i++) {
System.out.println("Statement["+i+"] :"+counts[i]);
}
dbCon.rollback();
}
catch (SQLException e) {
//handle SQL exception
dbCon.rollback();
}
Visit http://www.downloadmela.com/ for more papers
Before carrying out a batch update, it is important to disable the auto-commit mode by calling
setAutoCommit(false). This way, you will be able to rollback the batch transaction in case one of the
updates fail for any reason. When the Statement object is created, it is automatically associated a
"command list", which is initially empty. We then add our SQL update statements to this command list,
by making successive calls to the addBatch() method. On calling executeBatch(), the entire command
list is sent over to the database, and are then executed in the order they were added to the list. If all the
commands in the list are executed successfully, their corresponding update counts are returned as an
array of integers. Please note that you always have to clear the existing batch by calling clearBatch()
before creating a new one.
If any of the updates fail to execute within the database, a BatchUpdateException is thrown in response
to it. In case there is a problem in returning the update counts of each SQL statement, a SQLException
will be thrown to indicate the error.
How do I extract SQL table column type information?
Use the getColumns method of the java.sql.DatabaseMetaData interface to investigate the column type
information of a particular table. Note that most arguments to the getColumns method (pinpointing the
column in question) may be null, to broaden the search criteria. A code sample can be seen below:
public static void main(String[] args) throws Exception
{
// Load the database driver - in this case, we
// use the Jdbc/Odbc bridge driver.
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
// Open a connection to the database
Connection conn = DriverManager.getConnection("[jdbcURL]",
"[login]", "[passwd]");
// Get DatabaseMetaData
DatabaseMetaData dbmd = conn.getMetaData();
// Get all column types for the table "sysforeignkeys", in schema
// "dbo" and catalog "test".
ResultSet rs = dbmd.getColumns("test", "dbo", "sysforeignkeys", "%");
// Printout table data
while(rs.next())
{
// Get dbObject metadata
String dbObjectCatalog = rs.getString(1);
String dbObjectSchema = rs.getString(2);
String dbObjectName = rs.getString(3);
String dbColumnName = rs.getString(4);
String dbColumnTypeName = rs.getString(6);
int dbColumnSize = rs.getInt(7);
int dbDecimalDigits = rs.getInt(9);
Visit http://www.downloadmela.com/ for more papers
String dbColumnDefault = rs.getString(13);
int dbOrdinalPosition = rs.getInt(17);
String dbColumnIsNullable = rs.getString(18);
// Printout
System.out.println("Col(" + dbOrdinalPosition + "): " + dbColumnName
+ " (" + dbColumnTypeName +")");
System.out.println(" Nullable: " + dbColumnIsNullable +
", Size: " + dbColumnSize);
System.out.println(" Position in table: " + dbOrdinalPosition
+ ", Decimal digits: " + dbDecimalDigits);
}
// Free database resources
rs.close();
conn.close();
}
How can I investigate the parameters to send into and receive from a database stored procedure?
Use the method getProcedureColumns in interface DatabaseMetaData to probe a stored procedure for
metadata. The exact usage is described in the code below.
NOTE! This method can only discover parameter values. For databases where a returning ResultSet is
created simply by executing a SELECT statement within a stored procedure (thus not sending the
return ResultSet to the java application via a declared parameter), the real return value of the stored
procedure cannot be detected. This is a weakness for the JDBC metadata mining which is especially
present when handling Transact-SQL databases such as those produced by SyBase and Microsoft.
public static void main(String[] args) throws Exception
{
// Load the database driver - in this case, we
// use the Jdbc/Odbc bridge driver.
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver")
;
// Open a connection to the database
Connection conn = DriverManager.getConnection("[jdbcURL]",
"[login]", "[passwd]");
// Get DatabaseMetaData
DatabaseMetaData dbmd = conn.getMetaData();
// Get all column definitions for procedure "getFoodsEaten" in
// schema "testlogin" and catalog "dbo".
System.out.println("Procedures are called '" + dbmd.getProcedureTerm() +"' in the DBMS.");
ResultSet rs = dbmd.getProcedureColumns("test", "dbo", "getFoodsEaten", "%");
// Printout table data
Visit http://www.downloadmela.com/ for more papers
while(rs.next())
{
// Get procedure metadata
String dbProcedureCatalog = rs.getString(1);
String dbProcedureSchema = rs.getString(2);
String dbProcedureName = rs.getString(3);
String dbColumnName = rs.getString(4);
short dbColumnReturn = rs.getShort(5);
String dbColumnReturnTypeName = rs.getString(7);
int dbColumnPrecision = rs.getInt(8);
int dbColumnByteLength = rs.getInt(9);
short dbColumnScale = rs.getShort(10);
short dbColumnRadix = rs.getShort(11);
String dbColumnRemarks = rs.getString(13);
// Interpret the return type (readable for humans)
String procReturn = null;
switch(dbColumnReturn)
{
case DatabaseMetaData.procedureColumnIn:
procReturn = "In";
break;
case DatabaseMetaData.procedureColumnOut:
procReturn = "Out";
break;
case DatabaseMetaData.procedureColumnInOut:
procReturn = "In/Out";
break;
case DatabaseMetaData.procedureColumnReturn:
procReturn = "return value";
break;
case DatabaseMetaData.procedureColumnResult:
procReturn = "return ResultSet";
default:
procReturn = "Unknown";
}
// Printout
System.out.println("Procedure: " + dbProcedureCatalog + "." + dbProcedureSchema
+ "." + dbProcedureName);
System.out.println(" ColumnName [ColumnType(ColumnPrecision)]: " + dbColumnName
+ " [" + dbColumnReturnTypeName + "(" + dbColumnPrecision + ")]");
System.out.println(" ColumnReturns: " + procReturn + "(" + dbColumnReturnTypeName + ")");
System.out.println(" Radix: " + dbColumnRadix + ", Scale: " + dbColumnScale);
Visit http://www.downloadmela.com/ for more papers
System.out.println(" Remarks: " + dbColumnRemarks);
}
// Close database resources
rs.close();
conn.close();
}
How do I check what table-like database objects (table, view, temporary table, alias) are present
in a particular database?
Use java.sql.DatabaseMetaData to probe the database for metadata. Use the getTables method to
retrieve information about all database objects (i.e. tables, views, system tables, temporary global or
local tables or aliases). The exact usage is described in the code below.
NOTE! Certain JDBC drivers throw IllegalCursorStateExceptions when you try to access fields in the
ResultSet in the wrong order (i.e. not consecutively). Thus, you should not change the order in which
you retrieve the metadata from the ResultSet.
public static void main(String[] args) throws Exception
{
// Load the database driver - in this case, we
// use the Jdbc/Odbc bridge driver.
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
// Open a connection to the database
Connection conn = DriverManager.getConnection("[jdbcURL]",
"[login]", "[passwd]");
// Get DatabaseMetaData
DatabaseMetaData dbmd = conn.getMetaData();
// Get all dbObjects. Replace the last argument in the getTables
// method with objectCategories below to obtain only database
// tables. (Sending in null retrievs all dbObjects).
String[] objectCategories = {"TABLE"};
ResultSet rs = dbmd.getTables(null, null, "%", null);
// Printout table data
while(rs.next())
{
// Get dbObject metadata
String dbObjectCatalog = rs.getString(1);
String dbObjectSchema = rs.getString(2);
String dbObjectName = rs.getString(3);
String dbObjectType = rs.getString(4);
// Printout
Visit http://www.downloadmela.com/ for more papers
System.out.println("" + dbObjectType + ": " + dbObjectName);
System.out.println(" Catalog: " + dbObjectCatalog);
System.out.println(" Schema: " + dbObjectSchema);
}
// Close database resources
rs.close();
conn.close();
}
What does ResultSet actually contain? Is it the actual data of the result or some links to
databases? If it is the actual data then why can't we access it after connection is closed?
A ResultSet is an interface. Its implementation depends on the driver and hence ,what it "contains"
depends partially on the driver and what the query returns.
For example with the Odbc bridge what the underlying implementation layer contains is an ODBC
result set. A Type 4 driver executing a stored procedure that returns a cursor - on an oracle database it
actually returns a cursor in the databse. The oracle cursor can however be processed like a ResultSet
would be from the client. Closing a connection closes all interaction with the database and releases any
locks that might have been obtained in the process.
How do I extract a BLOB from a database?
A BLOB (Binary Large OBject) is essentially an array of bytes (byte[]), stored in the database. You
extract the data in two steps:
1. Call the getBlob method of the Statement class to retrieve a java.sql.Blob object
2. Call either getBinaryStream or getBytes in the extracted Blob object to retrieve the java byte[] which
is the Blob object.
Note that a Blob is essentially a pointer to a byte array (called LOCATOR in database-talk), so the
java.sql.Blob object essentially wraps a byte pointer. Thus, you must extract all data from the database
blob before calling commit or
<div align="center">
private void runGetBLOB()
{
try
{ // Prepare a Statement:
PreparedStatement stmnt = conn.prepareStatement("select aBlob from BlobTable");
// Execute
ResultSet rs = stmnt.executeQuery();
while(rs.next())
{
try
{
// Get as a BLOB
Visit http://www.downloadmela.com/ for more papers
Blob aBlob = rs.getBlob(1);
byte[] allBytesInBlob = aBlob.getBytes(1, (int) aBlob.length());
}
catch(Exception ex)
{
// The driver could not handle this as a BLOB...
// Fallback to default (and slower) byte[] handling
byte[] bytes = rs.getBytes(1);
}
}
// Close resources
rs.close();
stmnt.close();
}
catch(Exception ex)
{
this.log("Error when trying to read BLOB: " + ex);
}
}
</div>
How do I extract the SQL statements required to move all tables and views from an existing
database to another database?
The operation is performed in 9 steps:
1. Open a connection to the source database. Use the DriverManager class.
2. Find the entire physical layout of the current database. Use the DatabaseMetaData interface.
3. Create DDL SQL statements for re-creating the current database structure. Use the
DatabaseMetaData interface.
4. Build a dependency tree, to determine the order in which tables must be setup. Use the
DatabaseMetaData interface.
5. Open a connection to the target database. Use the DriverManager class.
6. Execute all DDL SQL statements from (3) in the order given by (4) in the target database to setup
the table and view structure. Use the PreparedStatement interface.
7. If (6) threw exceptions, abort the entire process.
8. Loop over all tables in the physical structure to generate DML SQL statements for re-creating the
data inside the table. Use the ResultSetMetaData interface.
9. Execute all DML SQL statements from (8) in the target database.
How do I check what table types exist in a database?
Use the getTableTypes method of interface java.sql.DatabaseMetaData to probe the database for table
types. The exact usage is described in the code below.
public static void main(String[] args) throws Exception
{
// Load the database driver - in this case, we
Visit http://www.downloadmela.com/ for more papers
// use the Jdbc/Odbc bridge driver.
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
// Open a connection to the database
Connection conn = DriverManager.getConnection("[jdbcURL]",
"[login]", "[passwd]");
// Get DatabaseMetaData
DatabaseMetaData dbmd = conn.getMetaData();
// Get all table types.
ResultSet rs = dbmd.getTableTypes();
// Printout table data
while(rs.next())
{
// Printout
System.out.println("Type: " + rs.getString(1));
}
// Close database resources
rs.close();
conn.close();
}
What is the advantage of using a PreparedStatement?
For SQL statements that are executed repeatedly, using a PreparedStatement object would almost
always be faster than using a Statement object. This is because creating a PreparedStatement object by
explicitly giving the SQL statement causes the statement to be precompiled within the database
immediately. Thus, when the PreparedStatement is later executed, the DBMS does not have to
recompile the SQL statement and prepared an execution plan - it simply runs the statement.
Typically, PreparedStatement objects are used for SQL statements that take parameters. However, they
can also be used with repeatedly executed SQL statements that do not accept parameters.
How do I find all database stored procedures in a database?
Use the getProcedures method of interface java.sql.DatabaseMetaData to probe the database for stored
procedures. The exact usage is described in the code below.
public static void main(String[] args) throws Exception
{
// Load the database driver - in this case, we
// use the Jdbc/Odbc bridge driver.
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
// Open a connection to the database
Connection conn = DriverManager.getConnection("[jdbcURL]",
"[login]", "[passwd]");
Visit http://www.downloadmela.com/ for more papers
// Get DatabaseMetaData
DatabaseMetaData dbmd = conn.getMetaData();
// Get all procedures.
System.out.println("Procedures are called '"
+ dbmd.getProcedureTerm() +"' in the DBMS.");
ResultSet rs = dbmd.getProcedures(null, null, "%");
// Printout table data
while(rs.next())
{
// Get procedure metadata
String dbProcedureCatalog = rs.getString(1);
String dbProcedureSchema = rs.getString(2);
String dbProcedureName = rs.getString(3);
String dbProcedureRemarks = rs.getString(7);
short dbProcedureType = rs.getShort(8);
// Make result readable for humans
String procReturn = (dbProcedureType == DatabaseMetaData.procedureNoResult
? "No Result" : "Result");
// Printout
System.out.println("Procedure: " + dbProcedureName
+ ", returns: " + procReturn);
System.out.println(" [Catalog | Schema]: [" + dbProcedureCatalog
+ " | " + dbProcedureSchema + "]");
System.out.println(" Comments: " + dbProcedureRemarks);
}
// Close database resources
rs.close();
conn.close();
}
How can I investigate the physical structure of a database?
The JDBC view of a database internal structure can be seen in the image below.
* Several database objects (tables, views, procedures etc.) are contained within a Schema.
* Several schema (user namespaces) are contained within a catalog.
* Several catalogs (database partitions; databases) are contained within a DB server (such as Oracle,
MS SQL
The DatabaseMetaData interface has methods for discovering all the Catalogs, Schemas, Tables and
Stored Procedures in the database server. The methods are pretty intuitive, returning a ResultSet with a
single String column; use them as indicated in the code below:
Visit http://www.downloadmela.com/ for more papers
public static void main(String[] args) throws Exception
{
// Load the database driver - in this case, we
// use the Jdbc/Odbc bridge driver.
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
// Open a connection to the database
Connection conn = DriverManager.getConnection("[jdbcURL]",
"[login]", "[passwd]");
// Get DatabaseMetaData
DatabaseMetaData dbmd = conn.getMetaData();
// Get all Catalogs
System.out.println("\nCatalogs are called '" + dbmd.getCatalogTerm()
+ "' in this RDBMS.");
processResultSet(dbmd.getCatalogTerm(), dbmd.getCatalogs());
// Get all Schemas
System.out.println("\nSchemas are called '" + dbmd.getSchemaTerm()
+ "' in this RDBMS.");
processResultSet(dbmd.getSchemaTerm(), dbmd.getSchemas());
// Get all Table-like types
System.out.println("\nAll table types supported in this RDBMS:");
processResultSet("Table type", dbmd.getTableTypes());
// Close the Connection
conn.close();
}
public static void processResultSet(String preamble, ResultSet rs)
throws SQLException
{
// Printout table data
while(rs.next())
{
// Printout
System.out.println(preamble + ": " + rs.getString(1));
}
// Close database resources
rs.close();
}
How does the Java Database Connectivity (JDBC) work?
The JDBC is used whenever a Java application should communicate with a relational database for
Visit http://www.downloadmela.com/ for more papers
which a JDBC driver exists. JDBC is part of the Java platform standard; all visible classes used in the
Java/database communication are placed in package java.sql.
Main JDBC classes:
* 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 (such as odbc or dbAnywhere/dbaw) 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
* Statement. Encapsulates an SQL statement which is passed to the database to be parsed, compiled,
planned and executed.
* ResultSet. The answer/result from a statement. A ResultSet is a fancy 2D list which encapsulates all
outgoing results from a given SQL query.
What is Metadata and why should I use it?
Metadata ('data about data') is information about one of two things:
1. Database information (java.sql.DatabaseMetaData), or
2. Information about a specific ResultSet (java.sql.ResultSetMetaData).
Use DatabaseMetaData to find information about your database, such as its capabilities and structure.
Use ResultSetMetaData to find information about the results of an SQL query, such as size and types of
columns.
How do I create a database connection?
The database connection is created in 3 steps:
1. Find a proper database URL (see FAQ on JDBC URL)
2. Load the database driver
3. Ask the Java DriverManager class to open a connection to your database
In java code, the steps are realized in code as follows:
1. Create a properly formatted JDBR URL for your database. (See FAQ on JDBC URL for more
information). A JDBC URL has the form
jdbc:someSubProtocol://myDatabaseServer/theDatabaseName
2.
try {
Class.forName("my.database.driver");
}
catch(Exception ex)
{
System.err.println("Could not load database driver: " + ex);
}
3. Connection conn = DriverManager.getConnection("a.JDBC.URL", "databaseLogin",
"databasePassword");
Visit http://www.downloadmela.com/ for more papers
