jdbc question

[jmt]

Using JDBC , what would be a more elegant way of determining whether or
not a record already exists in a DB table before inserting it?

Jeff
Registered Linux user number 366042

 

[ByteCoder]

Using JDBC , what would be a more elegant way of determining whether or
not a record already exists in a DB table before inserting it?

Jeff
Registered Linux user number 366042

There is a findByPrimaryKey method, if I am not mistaken.

 

[Chris Uppal]

Using JDBC , what would be a more elegant way of determining whether or
not a record already exists in a DB table before inserting it?

Just attempt to insert it and catch and recover from the error (if any).

-- chris

 

[Big Jim]

There is a findByPrimaryKey method, if I am not mistaken.

--

I thought findByPrimaryKey was a J2EE method provided in the ejb container
by server vendors as it's required by the J2EE spec. I didn't think it had
anything to do with JDBC specifically. Please correct me if I'm wrong.

As for the original post, more elegant than what?

 

[Virgil Green]

Using JDBC , what would be a more elegant way of determining whether
or not a record already exists in a DB table before inserting it?

Jeff
Registered Linux user number 366042

You really have to ask yourself how often this will occur. In DBMSs I've
worked with, a failure to find a given record was much more expensive than a
hit. That means that checking for existence before inserting when there are
few "collisions" invokes a lot of unnecessary expense. If there a lot of
collisions then you might be okay with the existence check.

Personally, I'd do the insert and then check for success or failure.

- Virgil

 

[Peter]

Just attempt to insert it and catch and recover from the error (if any).

And if there is an error, I assume it is an SQLException. How does the
application know the reason for the exception, and therefore can determine
the action to take to recover? As far as I can determine there are no
sub-classes of SQLException, or "type" values, or any other indication of
the actual cause of the exception: for example, primary key exception, row
not found, sql error....

Peter

 

[Virgil Green]

And if there is an error, I assume it is an SQLException. How does the
application know the reason for the exception, and therefore can
determine the action to take to recover? As far as I can determine
there are no sub-classes of SQLException, or "type" values, or any
other indication of the actual cause of the exception: for example,
primary key exception, row not found, sql error....

By examining the properties of SQLException. The methods getErrorCode()
and/or getSQLState would lead you to the underlying cause of the exception.

- Virgil

 

[Mark Thornton]

By examining the properties of SQLException. The methods getErrorCode()
and/or getSQLState would lead you to the underlying cause of the exception.

Not easily. There are several ways you might do this:
1. getErrorCode() this is dependent on the database vendor (and perhaps
the JDBC driver as well).
2. getSqlState. There are two possible standards for these codes.
Possible values for an already existing row might be "23000", "40002" or
"44000".
3. getMessage() This again is database and JDBC driver dependent.

I don't see any "Write Once Run Anywhere" option here. In most cases
this approach will require individual testing and coding for each
database/driver combination. By contrast the query to find if the row
exists can be written to work unchanged on most databases, although as
you point out it is likely to be slower. Where a number of rows are to
be inserted/updated a batch query could be used to determine row
existence for many rows at a time. This would reduce the number of round
trips to the database and thus improve performance (at least where batch
queries are available and implemented sensibly).

Mark Thornton

 

[ByteCoder]

Using JDBC , what would be a more elegant way of determining whether or
not a record already exists in a DB table before inserting it?

Jeff
Registered Linux user number 366042

A last resort solution might be to run a query against the database to
check if the record already exists.

 

[Virgil Green]

Not easily. There are several ways you might do this:
1. getErrorCode() this is dependent on the database vendor (and
perhaps the JDBC driver as well).
2. getSqlState. There are two possible standards for these codes.
Possible values for an already existing row might be "23000", "40002"
or "44000".
3. getMessage() This again is database and JDBC driver dependent.

Quite true...

I don't see any "Write Once Run Anywhere" option here.

If WORA is the goal, then you must, indeed, code to the least common
denominator. I expect that most code is written with a target database in
mind and a small likelihood of another database replacing it. Every
situation would have different requirements.

In most cases
this approach will require individual testing and coding for each
database/driver combination.

Values to compare against as well as instructions for which property of the
SQLException should be checked can be stored in a configuration file.
Perhaps the same file in which you stored the URL of the JDBC driver.
Database access could even be abstracted so that you don't have to worry
about the differences in the majority of your code.

- Virgil

 

[Ann]

Using JDBC , what would be a more elegant way of determining whether or
not a record already exists in a DB table before inserting it?

Jeff
Registered Linux user number 366042

Why do you want to know? Let me suggest that you don't.
What would you do if you found out that the record existed
already? My guess is that you would not insert it. And if
the record did not exist? Then you would insert it. The
result is the same, the record exists when you are done.
So just do the insert and ignore the error. Of course it
is perfectly legal for two rows in the same table to
contain the same values, so if you only want one row,
you would need to set that in the database itself.

 

[Sudsy]

Thomas Kellerer wrote:

Why not do it the other way round? Update the row first, then check the
number of affected rows (which is returned by the executeUpdate() method
and is DBMS indepdent). If zero records were affected then the record is
not there and you can insert it. Thus you do not need to rely on error
codes for specific DBMS when catching an error during the insert.

I was wondering if/when someone would make this suggestion! The problem
is, as others have mentioned, a matter of timing. Suppose you try an
update and it fails. Between that point in time and the time you try an
insert, another database client inserts a record with the same primary
key. So now your insert fails and you have to fallback to performing
the update again. KISS and perform an insert; iff it fails then try to
update.

 

[Virgil Green]

Not true --- it will work wherever transactions are properly
supported. You simply have to turn off the auto commit and commit the
transaction yourself after doing the update(s). Admittedly you may
still have to watch for serialization exceptions (deadlock), but that
should be rare.

I'm not familiar with any database that can maintain the integrity of a
transaction by guaranteeing that a record continues to *not* exist
throughout the transaction. Prevent deletion and updates to an existing
record outside the transaction? Yes. Ensure that a record that didn't exist
at the beginning of the tranaction is only created within the transaction?
Not to my knowledge. Which ones do this?

- Virgil

 

[Thomas Kellerer]

Sudsy wrote on 30.12.2004 15:05:

I was wondering if/when someone would make this suggestion! The problem
is, as others have mentioned, a matter of timing. Suppose you try an
update and it fails. Between that point in time and the time you try an
insert, another database client inserts a record with the same primary
key. So now your insert fails and you have to fallback to performing
the update again. KISS and perform an insert; iff it fails then try to
update.

Without specific requirements there is no way telling which version is
better. If this is e.g. a data load that is performed during the night, the
the solution is absolutely valid. If this is a use case with highly
partioned data (each user edits different records) then it's valid as well,
if the situation is a highly dynamic entry system where the situation that
you sketched is very likely to happen, then obviously this is not a good
idea. On the other hand: if you expect more updates then inserts to be
successful, the overall performance would be *much* better this way because
the index lookup for the primary key is only needed once for the update but
twice for the inser/update combo.

I was simply offering a different solution. If it's feasible for the
situation of the OP is up to him (and he did not give information to us in
order to judge that)

Thomas

 

[Sudsy]

Thomas Kellerer wrote:

Without specific requirements there is no way telling which version is
better. If this is e.g. a data load that is performed during the night,
the the solution is absolutely valid. If this is a use case with highly
partioned data (each user edits different records) then it's valid as
well, if the situation is a highly dynamic entry system where the
situation that you sketched is very likely to happen, then obviously
this is not a good idea. On the other hand: if you expect more updates
then inserts to be successful, the overall performance would be *much*
better this way because the index lookup for the primary key is only
needed once for the update but twice for the inser/update combo.

I was simply offering a different solution. If it's feasible for the
situation of the OP is up to him (and he did not give information to us
in order to judge that)

Thomas,
You raise an excellent point. You can't optimize your approach unless
you know how the database is going to be used. In some situations you're
going to have a lot of inserts (e-commerce, for example) while in others
you're only performing updates (e.g. banking).
Goodness knows, if you're doing a batch load on some databases then
you're better off dropping the indices, performing the load, then
recreating the indices. It takes less time than updating the indices
when loading records individually.
Which only goes to show that JDBC programmers should be familiar
with underlying database concepts. Thanks for sharing your ideas!

 

[Mark Thornton]

I'm not familiar with any database that can maintain the integrity of a
transaction by guaranteeing that a record continues to *not* exist
throughout the transaction. Prevent deletion and updates to an existing
record outside the transaction? Yes. Ensure that a record that didn't exist
at the beginning of the tranaction is only created within the transaction?
Not to my knowledge. Which ones do this?

SQL Server for one, but not in its default state. It depends on the
transaction isolation level (which you can specify via JDBC). Only the
highest level of isolation has the correct property.

Mark Thornton

From SQLServer "books online":

SERIALIZABLE

Places a range lock on the data set, preventing other users from
updating or inserting rows into the data set until the transaction is
complete. This is the most restrictive of the four isolation levels.
Because concurrency is lower, use this option only when necessary. This
option has the same effect as setting HOLDLOCK on all tables in all
SELECT statements in a transaction.

 

[Dimitri Maziuk]

Chris Uppal sez:
....

Before this thread, I hadn't realised just how badly broken the JDBC stuff
is -- insert-and-catch-error is a natural technique, but it seems to be more
than a little tricky to code it up in JDBC.

Here's another one:
try { statement.executeUpdate( "DROP TABLR FOO" ); }
catch( SQLException e ) { /* ignore -- table may not exist yet */ }

Most of it is SQL suckage, though. JDBC at least looks like it was
written by people who actually know how to write code (unlike some
other APIs in Java). Those few bits of SQL I've seen (I'm not paying
for the privilege to see the standard, but that's another rant) look
like they were written by software engineering PhDs from IT Dept. of
a Prestigious Non-accredited School of Business and Accounting.

Dima