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.