Welcome to the Java Programming Forums


The professional, friendly Java community. 21,500 members and growing!


The Java Programming Forums are a community of Java programmers from all around the World. Our members have a wide range of skills and they all have one thing in common: A passion to learn and code Java. We invite beginner Java programmers right through to Java professionals to post here and share your knowledge. Become a part of the community, help others, expand your knowledge of Java and enjoy talking with like minded people. Registration is quick and best of all free. We look forward to meeting you.


>> REGISTER NOW TO START POSTING


Members have full access to the forums. Advertisements are removed for registered users.

Results 1 to 2 of 2

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

  1. #1
    Junior Member
    Join Date
    Feb 2023
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default 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

  2. #2
    Junior Member
    Join Date
    Mar 2023
    Location
    Vancouver, Canada
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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).

Similar Threads

  1. Replies: 0
    Last Post: July 24th, 2019, 03:29 AM
  2. Replies: 0
    Last Post: February 20th, 2014, 12:34 PM
  3. [SOLVED] Problem with update table after inserting new values
    By justyStepi in forum JDBC & Databases
    Replies: 4
    Last Post: September 10th, 2012, 03:52 PM
  4. change JTable column name, gui does not update at once
    By renars in forum What's Wrong With My Code?
    Replies: 3
    Last Post: May 23rd, 2011, 10:39 AM
  5. Update table in database
    By CTheSky in forum JDBC & Databases
    Replies: 4
    Last Post: February 24th, 2011, 02:02 AM

Tags for this Thread