Auto-Increment: A column in a database table can be specified as auto-increment, which means the value for that column is auto incremented everytime a new record is inserted. It may be useful for some applications and at the same time pose some issues in some applications.
For eg., Consider a simple schema with tables Person, PersonAddress. Schema as given below
PERSON ID INTEGER(5) AUTO-INCREMENT, NAME VARCHAR (50) PERSON_ADDRESS ID INTEGER (5) AUTO-INCREMENT, USER_ID INTEGER(5) REFERENCES PERSON (ID), ADDRESS1 VARCHAR (50), CITY VARCHAR (20), COUNTRY VARCHAR (20)Consider a use-case where a user is submitting the details, & the code is storing those details into these 2 tables in database.
Storing these details is two step process, i.e., First, create a record in PERSON table, and then create a record in PERSON_ADDRESS table, the issue rises when we are to insert record to PERSON_ADDRESS, how do we get the USER_ID that we just created. Since the ID field in PERSON table is auto-incremented, so in the program we dont have the USER_ID right away. So, How do we get this newly created USER_ID to use for PERSON_ADDRESS table? One way is to make a SQL call to database & get the USER_ID for recently created record, but, this is not efficient way of doing it.
The solution I prefer is to NOT use auto-increment feature of the database. Generate the Unique id's for the ID fields by own. In this article I'll show how this can be done in a simple & generic way for the whole application.
1) Table to store recently Used ID's for each table:
CREATE TABLE SEQUENCE ( TABLE_NAME STRING (40) PRIMARY KEY, CURRENT_VALUE INTEGER(10) DEFAULT 0 );SEQUENCE table stores recently used ID for the TABLE_NAME. Everytime the value is fetched, CURRENT_VALUE is incremented by 1. This is done by the utility method. (Shown below)
2) A Utility Method to generate ID (In sequence)
public class DBUtil {
public int generateNextId (String tableName) {
/*
Create connection object & statement objects
Connection conn = .... ;
Statement stmt = ... ;
*/
int nextId = -1;
String sql = "SELECT CURRENT_VALUE FROM SEQUENCE WHERE TABLE_NAME = '" + tableName + "'";
ResultSet rs = stmt.executeQuery(sql);
while(rs.next()) {
int temp = rs.getInt (CURRENT_VALUE);
nextId = temp + 1;
}
if(nextId == -1) return nextId;
// INCREMENT the ID & store back in table
String updateSql = "UPDATE FROM SEQUENCE SET CURRENT_VALUE = ? WHERE TABLE_NAME = '" + tableName + "'";
PreparedStatement pstatement = connection.prepareStatement(updateSql);
pstatement.setString(1, nextId);
int status = pstatement.executeUpdate();
return nextId;
}
}
3)Example: Using Utility method while inserting data
String personId = DBUtil.generateNextId ("PERSON");
String insertPersonSQL = "INSERT INTO PERSON (ID, NAME) VALUES (?,?)";
pstatement1 = connection.prepareStatement(insertPersonSQL);
pstatement1.setString(1, personId);
pstatement1.setString(2, name);
// Now we still have person id handle, can use the same for Person Address record
String addressId = DBUtil.generateNextId ("PERSON_ADDRESS");
String insertAddressSQL = "INSERT INTO PERSON_ADDRESS (ID, PERSON_ID, ADDRESS, CITY) VALUES (?,?,?,?)";
pstatement2 = connection.prepareStatement(insertPersonSQL);
pstatement2.setString(1, addressId);
pstatement2.setString(2, personId);
.
.
Here, in this example, when inserting new records, we generate ID for person table using the utility as DBUtil.generateNextId ("PERSON");, and we have handle for this new Id, which we use for further insertions when required. This wouldn't be possible with default auto-increment feature that database provides.Hope this makes sense & it's useful. Any suggestions/query, feel free to contact me.
Posts
