jdbc question

Discussion in 'Java' started by jmt, Dec 29, 2004.

  1. jmt

    jmt Guest

    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
     
    jmt, Dec 29, 2004
    #1
    1. Advertising

  2. jmt

    ByteCoder Guest

    jmt wrote:
    > 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.

    --
    -------------
    - ByteCoder - ...I see stupid people
    -------------
    Curiosity *Skilled* the cat
     
    ByteCoder, Dec 29, 2004
    #2
    1. Advertising

  3. jmt

    Chris Uppal Guest

    jmt wrote:

    > 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
     
    Chris Uppal, Dec 29, 2004
    #3
  4. jmt

    Big Jim Guest

    "ByteCoder" <ByteCoder@127.0.0.1> wrote in message
    news:...
    > jmt wrote:
    > > 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.
    >
    > --
    > -------------
    > - ByteCoder - ...I see stupid people
    > -------------
    > Curiosity *Skilled* the cat


    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?
     
    Big Jim, Dec 29, 2004
    #4
  5. jmt

    Virgil Green Guest

    jmt wrote:
    > 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
     
    Virgil Green, Dec 29, 2004
    #5
  6. jmt

    Peter Guest

    "Chris Uppal" <-THIS.org> skrev i en
    meddelelse news:...
    > jmt wrote:
    >
    > > 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).


    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
     
    Peter, Dec 29, 2004
    #6
  7. jmt

    Virgil Green Guest

    Peter wrote:
    > "Chris Uppal" <-THIS.org> skrev i en
    > meddelelse news:...
    >> jmt wrote:
    >>
    >>> 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).

    >
    > 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
     
    Virgil Green, Dec 29, 2004
    #7
  8. Virgil Green wrote:
    > Peter wrote:
    >
    >>"Chris Uppal" <-THIS.org> skrev i en
    >>meddelelse news:...
    >>
    >>>jmt wrote:
    >>>
    >>>
    >>>>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).

    >>
    >>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.
    >


    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
     
    Mark Thornton, Dec 29, 2004
    #8
  9. jmt

    ByteCoder Guest

    jmt wrote:
    > 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.

    --
    -------------
    - ByteCoder - ...I see stupid people
    -------------
    Curiosity *Skilled* the cat
     
    ByteCoder, Dec 29, 2004
    #9
  10. jmt

    Virgil Green Guest

    Mark Thornton wrote:
    > Virgil Green wrote:
    >> Peter wrote:
    >>
    >>> "Chris Uppal" <-THIS.org> skrev i en
    >>> meddelelse news:...
    >>>
    >>>> jmt wrote:
    >>>>
    >>>>
    >>>>> 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).
    >>>
    >>> 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.
    >>

    >
    > 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
     
    Virgil Green, Dec 29, 2004
    #10
  11. jmt

    Ann Guest

    "jmt" <> wrote in message
    news:cqud3s$i58$...
    > 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.
     
    Ann, Dec 30, 2004
    #11
  12. jmt

    Sudsy Guest

    Thomas Kellerer wrote:
    <snip>
    > 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.
     
    Sudsy, Dec 30, 2004
    #12
  13. jmt

    Virgil Green Guest

    Mark Thornton wrote:
    > Chris Uppal wrote:
    >> Mark Thornton wrote:
    >>
    >>
    >>> 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.

    >>
    >>
    >> Or, more accuately, /not/ work on most databases ;-) It leaves a
    >> hole where another app could insert the to-be-duplicate row into the
    >> DB between your query and your insert.

    >
    > 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
     
    Virgil Green, Dec 30, 2004
    #13
  14. Sudsy wrote on 30.12.2004 15:05:

    >> 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.


    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
     
    Thomas Kellerer, Dec 30, 2004
    #14
  15. jmt

    Sudsy Guest

    Thomas Kellerer wrote:
    <snip>
    > 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!
     
    Sudsy, Dec 30, 2004
    #15
  16. Virgil Green wrote:
    > Mark Thornton wrote:
    >
    >>Chris Uppal wrote:
    >>
    >>>Mark Thornton wrote:
    >>>
    >>>
    >>>
    >>>>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.
    >>>
    >>>
    >>>Or, more accuately, /not/ work on most databases ;-) It leaves a
    >>>hole where another app could insert the to-be-duplicate row into the
    >>>DB between your query and your insert.

    >>
    >>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?
    >


    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.
     
    Mark Thornton, Dec 30, 2004
    #16
  17. 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
    --
    The most horrifying thing about Unix is that, no matter how many times you hit
    yourself over the head with it, you never quite manage to lose consciousness.
    It just goes on and on. -- Patrick Sobalvarro
     
    Dimitri Maziuk, Dec 30, 2004
    #17
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Keith Wansbrough

    Re: jdbc help:sun.jdbc.odbc.JdbcOdbcDriver

    Keith Wansbrough, Aug 16, 2004, in forum: Java
    Replies:
    0
    Views:
    781
    Keith Wansbrough
    Aug 16, 2004
  2. Betty
    Replies:
    1
    Views:
    9,899
    Juha Laiho
    May 21, 2005
  3. Jesus M. Salvo Jr.
    Replies:
    2
    Views:
    4,303
    robert
    Feb 11, 2006
  4. Bruce
    Replies:
    4
    Views:
    779
    Bjorn Abelli
    Mar 25, 2006
  5. Lew
    Replies:
    0
    Views:
    534
Loading...

Share This Page