In this JDBC Performance Tip, Part 3 of the JDBC Peformance Series, John Goodson discusses JDBC objects and methods that improve performance. He looks at how to use parameter markers as arguments to stored procedures, the benefits of using the Statement vs. PreparedStatement object, how to reduce network overhead by using the addBatch() method and more.

Read Performance Tips for the Data Tier (JDBC) - Part 3: Selecing Functions that Optimize Performance

Retrieving Auto-Generated KeysUnfortunately, not all JDBC drivers (and DBMS's) support this feature -- like Oracle.

Use the Right Data-Type

Again, the Oracle database only has the NUMBER data-type -- no INTEGER, BYTE, SHORT, FLOAT, etc.

Good Luck,
Avi.

It is true that using parameter markers increases performance
but I think it creates maintainability problems. What if order of parameters of stored procedure changes sometime later? Using parameter names makes code readable and maintainable.
Unless and until good documentation is done and certain rules for stored procedure modifications are followed, using parameter markers can create problems.

You can use named parameters from JDBC 3.0 (not all drivers support it).
Or you can write simple JDBC wrapper that supports named parameters. I wrote one myself and it is easy.

MC

If it is supported, use databaseMetaData to get the columns for a specific procedure and then store the name, index, type and paramtype(IN,OUT,INOUT) of each column grouped into an object stored in a hashmap keyed by the procedure name.

--Calum

I was just wondering , in a enterprise project where Java developer and database DBA guys are two end, How easy to maintain the coulmn number in your code .
I would rather use the column name.

I was just wondering , in a enterprise project where Java developer and database DBA guys are two end, How easy to maintain the coulmn number in your code . I would rather use the column name.

I found it is not a problem in practice. You can validate coulmn numbers on application startup automaticaly too.

I was just wondering , in a enterprise project where Java developer and database DBA guys are two end, How easy to maintain the coulmn number in your code . I would rather use the column name.

I found it is not a problem in practice. You can validate coulmn numbers on application startup automaticaly too.

When you need to change your code, you mean you don't need to know the column number and the order? Automaticaly? How do you make it?
I just want to know it.

I was just wondering , in a enterprise project where Java developer and database DBA guys are two end, How easy to maintain the coulmn number in your code . I would rather use the column name.

I found it is not a problem in practice. You can validate coulmn numbers on application startup automaticaly too.

When you need to change your code, you mean you don't need to know the column number and the order? Automaticaly? How do you make it? I just want to know it.

There are a few ways, the most simple way is to use static data structure (bean) and utils to populate it.
I use framework to do this, it can validate all queries before to start application too. But you can use some class for metadata and compare it to resultset metadata retrieved from DB at application startup (it can be generated from DB metadata too).

 rs.getString( Metadata.EMAIL_INDEX );


This progamming style works good in practice too.

 int index = 1;
 email = rs.getString( index ++ );
 name = rs.getString( index ++ );

It works because you add fields in the most of cases.

Bl..dy typos...
>> I relay
should be: 'I realy'

When will TheServerSide allow post editing ?

Bl..dy typos...
>> I relay
should be: 'I realy'

should be: 'I really' ;-)

In general, do not write code that relies on the number of result rows from a query because drivers must fetch all rows in a result set to know how many rows the query will return.

What is the best way to do pagination (in web pages) without relying on number of rows?

In general, do not write code that relies on the number of result rows from a query because drivers must fetch all rows in a result set to know how many rows the query will return.

What is the best way to do pagination (in web pages) without relying on number of rows?

You do not relying on number of rows for pagination if you use something like "LIMIT/OFFSET".

In general, do not write code that relies on the number of result rows from a query because drivers must fetch all rows in a result set to know how many rows the query will return.

What is the best way to do pagination (in web pages) without relying on number of rows?

You do not relying on number of rows for pagination if you use something like "LIMIT/OFFSET".

And you keep the full list in the session or application context and the pagination (and sort order) state in the session context. If you keep the full list in the application context keep in mind that if can be accessed by multiple users.

MC

In general, do not write code that relies on the number of result rows from a query because drivers must fetch all rows in a result set to know how many rows the query will return.

What is the best way to do pagination (in web pages) without relying on number of rows?

You do not relying on number of rows for pagination if you use something like "LIMIT/OFFSET".

You can do next/previous buttons using this method, but you wouldn't be able to do a "paging" thing where you've got links for 1-10, 11-20, etc along the bottom without knowing the total number of rows.

I guess what this is trying to say is not do get a recordset and get the number of rows from it to determine how many rows you have, instead do a "select count(foo) ..." as that won't result in the data coming down the pipe.

On Oracle, using PreparedStatement like in the following example might not be performant if the table in which you insert has a lot of columns (whatever you use addBatch or not)

PreparedStatement ps = conn.prepareStatement(
   "INSERT into employees values (?, ?, ?)");

for (n = 0; n < 100; n++) {

  ps.setString(name[n]);
  ps.setLong(id[n]);
  ps.setInt(salary[n]);
  ps.addBatch();
}
ps.executeBatch();

Indeed, we experienced performance degrade using this technique on tables with 50 or more colums (in our case, these tables were storing banking products, so it was normal that we had so many business columns in one table). The time spent in all the set... method calls was the bottleneck.

It was more performant to use Statement and litteral values in combination with addBatch when inserting in these tables.

Maybe I'm a bid simple minded, but I don't see why anyone would create tables without primary keys. If that's really the case, I'm sure you have other problems than performance.

Further, the solution explained in the article only works (in my opinion) if you implement the DB access directly using JDBC. Using, for example, CMP Entity Beans, this approach does not work, since you have no real control over the statement used to create the dataset. I wonder if that's also true for O/R mapping tools (TopLink, Cocobase, Hibernate, etc). Does anyone have experience with that?

Regards,
    Dirk

Maybe parameterized queries have a lot of overhead to compile, but composing queries with all that quote junk is awful. Trying to compose a given vendor's proper date query format is irritating enough alone; a simple call to setDate() (or better yet, setObject()) is so much easier and totally cross-platform. And there's issues with decimals, etc., etc....

Furthermore, composing sql "by hand" (at least theoretically) opens you up to attacks like somebody inserting raw sql into a user interface query field and fooling your code into adding it to the end of a query.

It seems like JDBC should provide a single-execution-optimized PreparedStatement that can take advantage of all the benefits of PreparedStatement.setObject() without the overhead issues.

Then again, I wonder if JDBC drivers/databases can gain any performance benefit when the same query is passed many times to Connection.prepareStatement() when using a JDBC 3.x connection pool? It isn't really feasible to hang onto statements & connections between http requests in a server-side app like I am building.

Either way, my stuff is fast enough that I doubt I'll stop using PreparedStatement. I'd sooner upgrade all the CPU's and buy a new network hub.