Programming

Programming Tips for JDBC

programming tipsProgramming Tips for JDBC

 a. Use DataSource whenever Possible

If you have a choice, you always should
use javax.sql.DataSource to get a JDBC connection. Data sources, which were
introduced with JDBC 2.0, make the job of configuring a database connection and
connecting to a database nearly fool proof. Consider the following data source
code fragment:

 

InitialContext ctx = new
InitialContext(  );

DataSource ds =
(DataSource)ctx.lookup(“dsname”);

Connection conn = ds.getConnection(
);

This code contains nothing proprietary,
involves no parsing of configuration files, and uses no information dependent
on the deployment environment other than a data source name. Now, compare that
code to the following:

Connection conn;

Class.forName(“org.gjt.mm.mysql.Driver”).newInstance(
);

conn =
DriverManager.getConnection(“jdbc:mysql://carthage:/db”,
“user”, “password”);

This code is the simplest alternative to
using a data source. However, the latter example is harder to read, contains
proprietary code (i.e., specific reference to the Driver implementation class,
which is the JDBC URL for a specific driver), and requires hard-coded
information about the runtime environment (e.g., the host and database in the
URL, the username, and the password). In short, you have to compile the
DriverManager version for a specific target runtime environment.

b.  Pool Database Connections

A sure way to bog down any JDBC
application is to repeatedly open and close database connections. This problem
is especially troublesome for high-volume applications such as web sites and
enterprise applications. The clear and obvious answer to this problem is
connection pooling. Connection pooling allows your application to use
preexisting database connections by “loaning out” from a pool of connection
objects.

Before JDBC 2.0, you had to write your own
connection pooling support or look for a third-party tool. Put succinctly,
connection pooling was a coding problem. Thanks to JDBC 2.0, however,
connection pooling is now just a configuration issue. This means you simply
have to convert your data source to a pooled data source; the code to access
data from the connection remains the same, regardless of whether you use
connection pooling. However, even though connection pooling is becoming more
commonplace in JDBC code, it should still be included as a JDBC best practice.

How you configure your application to use
a pooled connection depends on the environment in which you are running it.
First, you must use a DataSource that supports connection pooling, which
typically means obtaining an object that implements the
javax.sql.ConnectionPoolDataSource interface. Your application then grabs its
pooled connections from this data source. The only part that varies from one
environment to the next is how to configure the environment to use a particular
data source. For example, in a Java 2 Enterprise Edition (J2EE) application
server, you will typically configure an XML file to store that data source for
JNDI lookups. In other environments, you might manually configure and serialize
a data source to be read later by the application.

c. Statement Pooling

Setting the MaxPooledStatements connection
option enables statement pooling. Enabling statement pooling allows the driver
to re-use PreparedStatement objects. When PreparedStatements are closed they
are returned to the pool instead of being freed and the next PreparedStatement
with the same SQL statement is retrieved from the pool rather than being
instantiated and prepared against the server.

Don’t use PreparedStatements by default!
If your SQL statement doesn’t contain parameters use the Statement object
instead – this avoids a call to internal and wire level prepare() methods and
increases performance!

d. Bulk Loading

Loading large amounts of data into a
database quickly requires something more powerful than standard addBatch().
Database vendors offer a way to bulk load data, bypassing the normal wire
protocol and normal insert procedure. There are 2 ways to use Bulk Loading with
a JDBC driver that supports it:

1.Set enableBulkLoad connect option to
true. This will make addBatch() calls use the bulk load protocol over the wire.

2.Use a Bulk Load object:

// Get Database Connection

Connection con =
DriverManager.getConnection(“jdbc:datadirect:orac

le://server3:1521;ServiceName=ORCL;User=test;Password=secret”);

// Get a DDBulkLoad object

 

DDBulkLoad bulkLoad = DDBulkLoadFactory.getInstance(con);

bulkLoad.setTableName(“GBMAXTABLE”);

bulkLoad.load(“tmp.csv”);

 

// Alternatively, you can load from any
ResultSet object into the

target table:

bulkLoad.load(results);

 

e. Executing a PreparedStatement

Use a PreparedStatement any time you have
optional parameters to specify to the SQL Statement, or values that do not
convert easily to strings, for example BLOBs. It also helps prevent SQL
injection attacks when working with string values.

 

PreparedStatement pstmt =
con.prepareStatement(“INSERT into table2 (ID, lastName, firstName) VALUES
(?,?,?)”);

pstmt.setInt(1, 87);

pstmt.setString(2, “Picard”);

pstmt.setString(3, “Jean-Luc”);

rowsInserted += pstmt.executeUpdate();

 

Comments

comments

HTML Snippets Powered By : XYZScripts.com
%d bloggers like this: