Thread: How to best update a database table column with different values

I wrote a Java class method that, while it works, I don't know how to get it to constantly work. I am reading from a flat-file CSV that will contain "intro_id" that will update the table "intro" field "intro_id" (not to be confused with the primary key "id" in "intro") from top to bottom of the flat file. I assume that the SQL statement will be:

UPDATE intro
SET intro_id = ?,
accomplishment = ?
WHERE id = ?

Using PreparedStatement to populate the placeholders.

This only works, though, as long as the row "id" in "intro" remains purely incremental (e.g., don't delete Row 109 or else the search for Row 109 will come up empty and offset the entire update from there on down).

I tried to write my own AutoIncrementer class that should reorder the row ID back to incremental (the row ID is not a foreign key anywhere else nor will ever be)

Here is the code:

	private void updateIntroIdDB() throws SQLException, Exception {		
		int minId = 0;
		String minIdSQL = "SELECT MIN(id) AS id FROM " + DandyLabsIntroCreator._DB_TABLE_INTRO + " LIMIT 1";
		PreparedStatement stmt1 = DandyLabsIntroCreator.conn.prepareStatement(minIdSQL);
		ResultSet rs = stmt1.executeQuery();
		while (rs.next()) {
			minId = Integer.parseInt(rs.getString(1));
		}
		if (rs != null) {
			rs.close();
			rs = null;
		}
		if (stmt1 != null) {
			stmt1.close();
			stmt1 = null;
		}
 
		// RUN AutoIncrementer FIRST TO ENSURE INCREMENTAL ROW IDs TO PERFORM UPDATE SUFFICIENTLY
		AutoIncrementer auto = new AutoIncrementer(100, DandyLabsIntroCreator._DB_TABLE_INTRO, DandyLabsIntroCreator.conn, false);
		auto.perform();
 
		IntroBean bean = null;
		PreparedStatement stmt2 = DandyLabsIntroCreator.conn.prepareStatement(DandyLabsIntroCreator.SQL_UPDATE_INTRO_ID);
		for (int i = 0; i < this.size(); i++) {
			bean = (IntroBean) this.get(i);
			stmt2.setString(1, bean.getIntroId());
			stmt2.setString(2, bean.getAccomplishment());
			stmt2.setInt(3, minId);
			stmt2.addBatch();
			minId++;
		}
		stmt2.executeBatch();
		DandyLabsIntroCreator.conn.commit();
 
		if (stmt2 != null) {
			stmt2.close();
			stmt2 = null;
		}
	}

Would it be better, instead, to simply delete ALL records from "intro" and then reinsert them all back in one batch, or is my above-mentioned approach better?

Please advise, thanks

I'm not sure I fully understand what you are wanting to do, but for auto-incrementing the ID field, if not able to use the database's built-in functionally, I would probably create a utility table which would hold the value of next ID to assign:

CREATE TABLE nextid (id INTEGER NOT NULL);

prime it (once):

INSERT INTO nextid VALUES (10);

grab the value from the table:

SELECT id FROM nextid;

and then increment that after that value after it had been used:

UPDATE nextid SET id = (SELECT id FROM nextid ) +1;

If you need this to be atomic (in case you have multiple threads/instances trying to get the next id concurrently), the SELECT and UPDATE would need to be wrapped-up in a stored procedure.

If the IDs don't need to be sequential (maybe not even numeric), alternatively, you could generate the id based on a hash of System.currentTimeMillis (maybe plus some random value), or use a UUID (java.util.UUID).