JDBC PreparedStatement Example – Batch Insert, Update, Delete

java.sql.PreparedStatement class is more powerful and efficient than java.sql.Statement, This example will show you how to execute PreparedStatement in batch.

Please Note Below Points For Batch Database Operation.

  1. Before you can execute PreparedStatement sql in batch, you need to set auto commit to false use dbConnection.setAutoCommit(false).
  2. After setting all the values for prepared statements use preparedStatement.addBatch() to add it to the batch.
  3. Do not forget commit the batch to database server in the code manually (dbConnection.commit();).
  4. Enable auto commit at last for later database operations (dbConnection.setAutoCommit(true);).
  5. Close PreparedStatement and Connection object.

This example will use PostgreSql to demo how to insert, update and delete with PreparedStatement in batch. You can read article Use JDBC To Connect PostgreSQL Database to learn how to use JDBC to connect PostgreSQL.

This example will read PostfreSQL JDBC connection data saved in a properties file. You can read Article Load JDBC Configuration From Properties File Example for detail introduction.

PreparedStatement Batch Insert Example.

	// PreparedStatement for batch insert example.
	public void preparedStatementBatchInsert()
	{
		try
		{
			Connection dbConn = this.getDBConnectionFromPropertiesFile();
			
			// Set auto commint to false
			dbConn.setAutoCommit(false);
			
			// Create insert sql.
			StringBuffer insertBuf = new StringBuffer();
			insertBuf.append("insert into \"UserAccount\".\"UserInfo\"(\"UserName\", \"Password\") values(?,?)");
			String insertSql = insertBuf.toString();
			
			// Create PreparedStatement object.
			PreparedStatement pStmt = dbConn.prepareStatement(insertSql);
			
			// Add batch insert data.
			pStmt.setString(1, "[email protected]");
			pStmt.setString(2, "abcdefg");
			pStmt.addBatch();
			
			pStmt.setString(1, "[email protected]");
			pStmt.setString(2, "abcdefg");
			pStmt.addBatch();
			
			pStmt.setString(1, "[email protected]");
			pStmt.setString(2, "1234567");
			pStmt.addBatch();
			
			pStmt.setString(1, "[email protected]");
			pStmt.setString(2, "1234567");
			pStmt.addBatch();
			
			pStmt.setString(1, "[email protected]");
			pStmt.setString(2, "[email protected]#$%^&");
			pStmt.addBatch();
			
			pStmt.setString(1, "[email protected]");
			pStmt.setString(2, "&^%$#@!");
			pStmt.addBatch();
			
			// The returned int array store insert sql affected record counts.
			int successCount[] = pStmt.executeBatch();
			int successCountLen = successCount.length;
			for(int i =0; i < successCountLen; i++) { 
                                int success = successCount[i]; 
                                if(success>0)
				{
					System.out.println(" Insert sql operation success compelete. The affected row count is " + success);
				}
			}
			
			dbConn.commit();
			
			dbConn.setAutoCommit(true);
			
			pStmt.close();
			
			dbConn.close();
			
		}catch(Exception ex)
		{
			ex.printStackTrace();
		}
	}

Batch Insert Console Output.

PreparedStatement batch insert console output

Database query after execute PreparedStatement batch insert.

READ :   How To Use JDBC To Connect MySql Database

PreparedStatement batch insert database query

PreparedStatement Batch Update Example.

	// PreparedStatement for batch update example.
	public void preparedStatementBatchUpdate()
	{
		try
		{
			Connection dbConn = this.getDBConnectionFromPropertiesFile();
			
			// Set auto commint to false
			dbConn.setAutoCommit(false);
			
			// Create update sql.
			StringBuffer updateBuf = new StringBuffer();
			updateBuf.append("update \"UserAccount\".\"UserInfo\" set \"Password\" = ? where \"Password\" = ? ");
			String updateSql = updateBuf.toString();
			
			// Create PreparedStatement object.
			PreparedStatement pStmt = dbConn.prepareStatement(updateSql);
			
			// Add batch update data.
			pStmt.setString(1, "abc#123");
			pStmt.setString(2, "1234567");
			pStmt.addBatch();
			
			pStmt.setString(1, "[email protected]#");
			pStmt.setString(2, "abcdefg");
			pStmt.addBatch();
			
			pStmt.setString(1, "163***");
			pStmt.setString(2, "[email protected]#$%^&");
			pStmt.addBatch();
			
			// The returned int array store update sql affected record counts.
			int successCount[] = pStmt.executeBatch();
			int successCountLen = successCount.length;
			for(int i =0; i < successCountLen; i++) { 
                                int success = successCount[i]; 
                                if(success>0)
				{
					System.out.println("Update sql operation success compelete. Updated row count is " + success);
				}
			}
			
			dbConn.commit();
			
			dbConn.setAutoCommit(true);
			
			pStmt.close();
			
			dbConn.close();
			
		}catch(Exception ex)
		{
			ex.printStackTrace();
		}
	}

Batch Update Console Output.

PreparedStatement batch update console output

Database query after execute batch update.

PreparedStatement batch update database query

PreparedStatement Batch Delete Example.

	// PreparedStatement for batch delete exmple.
	public void preparedStatementBatchDelete()
	{
		try
		{
			Connection dbConn = this.getDBConnectionFromPropertiesFile();
			
			// Set auto commint to false
			dbConn.setAutoCommit(false);
			
			// Create update sql.
			StringBuffer updateBuf = new StringBuffer();
			updateBuf.append("delete from \"UserAccount\".\"UserInfo\" where \"Password\" = ? ");
			String updateSql = updateBuf.toString();
			
			// Create PreparedStatement object.
			PreparedStatement pStmt = dbConn.prepareStatement(updateSql);
			
			// Add batch delete data.
			pStmt.setString(1, "abc#123");
			pStmt.addBatch();
			
			pStmt.setString(1, "163***");
			pStmt.addBatch();
			
			// The returned int array store delete sql affected record counts.
			int successCount[] = pStmt.executeBatch();
			int successCountLen = successCount.length;
			for(int i =0; i < successCountLen; i++) { 
                                int success = successCount[i]; 
                                if(success>0)
				{
					System.out.println("Update sql operation success compelete. Updated row count is " + success);
				}
			}
			
			dbConn.commit();
			
			dbConn.setAutoCommit(true);
			
			pStmt.close();
			
			dbConn.close();
			
		}catch(Exception ex)
		{
			ex.printStackTrace();
		}
	}

Batch Delete Console Output.

PreparedStatement batch delete console output

Database query after execute batch delete.

PreparedStatement batch delete database query

Download “JDBC-PreparedStatement-Example-–-Batch-Insert-Update-Delete.zip” JDBC-PreparedStatement-Example-–-Batch-Insert-Update-Delete.zip – Downloaded 71 times – 2 KB

(Visited 1,792 times, 8 visits today)

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.