JDBC PrepareStatement Insert Data As A Batch - JAVA(MySQL)

In this tutorial i will show example to add data into database MySQL as a batch. The idea is to prepare the batch data to pump into database one time only. This approach is a best solution if you more than 1 records to insert into database. If you insert one by one data to database, the process will be slow, hence batch process will inprove application performance.

Take a look on the example and try it by your self. =)

The Batch Update Example

public void insertAsBatch()
    {
        String strsql = "INSERT INTO sysusers(UserID,UserEmail,Password) VALUES(?,?,?)";
        PreparedStatement prepStatement = null;
        Connection dbConn = TestConnectionDb();
        int batchSize = 100;//set the batch size to commit intodatabase(optional)
        int IndexCounterBatch = 0;
        try
        {            
            dbConn.setAutoCommit(false);
            prepStatement = dbConn.prepareStatement(strsql);
            for(int i=0; i < 200;i++)
            {               
                prepStatement.setString(1, "developersnote" + Integer.toString(i));
                prepStatement.setString(2, "developersnote" + Integer.toString(i) + "@gmail.com");
                prepStatement.setString(3, "developersnote" + Integer.toString(i));
                prepStatement.addBatch();
                
                //this is optional if you want to set certain size to execute batch               
                IndexCounterBatch++;                
                if (IndexCounterBatch == batchSize) {
                    prepStatement.executeBatch();
                    dbConn.commit();
                    IndexCounterBatch = 0;                    
                }
            }
            prepStatement.executeBatch();
            dbConn.commit();
            System.out.println("Operation Successfully");
        }
        catch (SQLException se) {
            System.out.println(se.getMessage());
        } finally {
            try {
                if (prepStatement != null) {
                    dbConn.close();
                }
            } catch (SQLException se) {
                 System.out.println(se.getMessage());
            }
            try {
                if (dbConn != null) {
                    dbConn.close();
                }
            } catch (SQLException se) {
                 System.out.println(se.getMessage());
            }
        }
    }
    
    public Connection TestConnectionDb() {
        
        String DbConnectionString ="jdbc:mysql://localhost:3306/blog";
        //or you can directly connecto to your database schema like this :
        //String DbConnectionString ="jdbc:mysql://localhost:3306/<schema name>";
        
        String User = "root";
        String Password = "P@ssw0rd";
        
        Connection connection = null;
        try {            
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(DbConnectionString, User, Password);
        } catch (ClassNotFoundException e) {
            System.out.println("MySQL JDBC Driver not found");            
        } catch (SQLException ex) {
            System.out.println("Connection Failed! Err Msg : " + ex.getMessage());            
        }  
        if (connection != null) {
            System.out.println("You made it, take control your database now!");            
        } else {
            System.out.println("Failed to make connection!");            
        }
        return connection;
    }

By Mohd Zulkamal
NOTE : – If You have Found this post Helpful, I will appreciate if you can Share it on Facebook, Twitter and Other Social Media Sites. Thanks =)