Discussions

News: Database Agnostic Key Generation and JDBC

  1. Database Agnostic Key Generation and JDBC (3 messages)

    Implementing a key generation strategy within a Java application changes from database to database, driver to driver. There are many documented tutorials that cover how to support code generation for a specific database and driver. These strategies are well documented here and include:

    • Changing the query to suit the database   
    • Changing the java code to suit the database/driver   
    • Implementing java generated keys

    Also, keys are usually generated on insert only and need to be retrieved somehow to be of use in subsequent portions of your code.

    Hibernate to some extent helps here, however different databases require different XML file mapping configurations, and I would prefer not to have to use different mapping files when I can help it. Also, I am still not a big fan of persistance layers and like to in many cases write my own DAO's.

    My aim is to implement one pattern at the java layer that can be used with all target databases and only have different configurations at the database level. I want to be able to switch databases simply by changing JDBC parameters. I have investigated for Oracle, MySql and SQL Server and have found many articles and approaches, but it took a while. Now I hope to present this information in one place.


    Common Java Code

    This is the only code we will use across all databases:

    // assume you have created a connection con

    Statement stmt = con.createStatement();

    //defines the field value to return to your code after the insert takes place

    String [] pk = {"id"};       

    stmt.executeUpdate("INSERT INTO test (name) VALUES ('test')",pk);

    ResultSet rs = s.getGeneratedKeys();

    if(rs.next()){

         int id_val = rs.getInt(1); 

         System.out.println("Your generated ID passed back to use as you wish: " + id_val);

    }

    rs.close();

    stmt.close();


    MySQL:

    We implement the auto-increment field within SQL Server to generate unique ID's. Easy.

    CREATE TABLE test( 

    id INTEGER auto_increment PRIMARY KEY, 

    description VARCHAR(20)); 

    SQL Server:

    We implement the IDENTITY column property to generate unique ID's. Easy as well.

    CREATE TABLE calendar ( 

     id INTEGER IDENTITY(1, 1) PRIMARY KEY, 

     description VARCHAR(20)

    );

    Oracle:

    Oracle is different in that it does not have auto incremented fields. However, you can implement a sequence, and apply it with a trigger on insert. Only works with JDBC driver 10.2 +

    CREATE TABLE calendar ( 

    id NUMBER PRIMARY KEY, 

    description VARCHAR(20)

    );

     

    CREATE SEQUENCE test_sequence

    START WITH 1INCREMENT BY 1;

     

    CREATE OR REPLACE TRIGGER test_trigger

    BEFORE INSERT

    ON test

    REFERENCING NEW AS NEW

    FOR EACH ROW

    BEGIN

    SELECT test_sequence.nextval INTO :NEW.ID FROM dual;

    END;

    /


    Conclusion:

    Run the above code in any database above and watch the fields auto-generate. Sweet.

    This isnt rocket science, and there is nothing here that hasn't been documented before. It is just a combined tested approach across databases and may appeal to other like me who like to manage our persistence ourselves in a multi-database environment.

    Comments and corrections welcome. 

    Threaded Messages (3)

  2. Yawn

  3. About use of sequence and DB2 i5/OS[ Go to top ]

    From the Java JDBC point of view, ResultSet.getGeneratedKeys is really thebest option.

    From the database portability, I propose to use named sequences instead of auto-incremented column (by the way often backed by a sequence). Your database administrator may appreciate because he has control on sequence parameters like its per connection cache size.

    In my context of DB2 i5/OS, this specific feature is implemented with the SQL vocabulary "select from insert".

    To write DB2 portable code (between UDB Linux/Windows and i5/OS), the only option was to use "nextval for sequencename" in the insert statement - and also for performance reasons because auto-increment column may lock when insertions come concurrently !

    As a result, my insert statement becomes on DB2 i5/OS :   select ID from insert into tablename ( ID, text ) values ( nextval for sequencename, 'data' )

    And I was really expecting the best from that code: insertion and get back generated ID from an efficient sequence in a single AppServer - DatabaseServer round-trip.

    Now the bad news: i5/OS V6R1 implements "select from insert" vocabulary as a temporary table which contains a copy of inserted rows. Ugly ! And really inefficient in CPU and disk consumption - almost 50 ms to insert a row ! So I change to a two-round-trip implementation: select nextval for sequencename first and then insert my row - result: 2 x 7 ms.

    To conclude: you should prefer named sequence and you have to take care to ResultSet.getGeneratedKeys even it is implemented in your driver, you may face an inefficient implementation.

     

  4. Nice, but a bit trivial[ Go to top ]

    Nice writeup and effort, but a bit on the simplistic side. It's not really "database agnostic key generation", but rather "JDBC 3.0 support for fetching autogenerated keys on MySql, SQL Server and Oracle". That's a rather small subset of "agnostic key generation".