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