Write an example JDBC program demonstrating the batch processing with PreparedStatement object?

---

<p>Grouping related SQL statements into a batch and executing/submitting them at once is known as batch processing. The Statement interface provides methods to perform batch processing such as addBatch(), executeBatch(), clearBatch().</p><p>Follow the steps given below to perform batch updates using the PreparedStatement object:</p><ul class="list"><li><p>Register the driver class using the <strong>registerDriver()</strong> method of the <strong>DriverManager</strong> class. Pass the driver class name to it, as a parameter.</p></li><li><p>Connect to the database using the <strong>getConnection()</strong> method of the <strong>DriverManager</strong> class. Passing URL (String), username (String), password (String) as parameters to it.</p></li><li><p>Set the auto-commit to false using <strong>setAutoCommit()</strong> method of the Connection interface.</p></li><li><p>Create a PreparedStatement object using the <strong>prepareStatement()</strong> method of the <strong>Connection</strong> interface. Pass a query (insert) to it with place holders (?) in it.</p></li><li><p>Set values to the place holders in the above created statement using the setter methods of the <strong>PreparedStatement</strong> interface.</p></li><li><p>Add the required statements to the batch using the <strong>addBatch()</strong> method of the Statement interface.</p></li><li><p>Execute the batch using the <strong>executeBatch()</strong> method. of the Statement interface.</p></li><li><p>Commit the changes made, using the <strong>commit()</strong> method of the Statement interface.</p></li></ul><h3>Example:</h3><p>Suppose we have created a table named <strong>Dispatches</strong> with the description</p><pre class="result notranslate">+-------------------+--------------+------+-----+---------+-------+ | Field &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | Type &nbsp; &nbsp; &nbsp; &nbsp; | Null | Key | Default | Extra | +-------------------+--------------+------+-----+---------+-------+ | Product_Name &nbsp; &nbsp; &nbsp;| varchar(255) | YES &nbsp;| &nbsp; &nbsp; | NULL &nbsp; &nbsp;| &nbsp; &nbsp; &nbsp; | | Name_Of_Customer &nbsp;| varchar(255) | YES &nbsp;| &nbsp; &nbsp; | NULL &nbsp; &nbsp;| &nbsp; &nbsp; &nbsp; | | Month_Of_Dispatch | varchar(255) | YES &nbsp;| &nbsp; &nbsp; | NULL &nbsp; &nbsp;| &nbsp; &nbsp; &nbsp; | | Price &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | int(11) &nbsp; &nbsp; &nbsp;| YES &nbsp;| &nbsp; &nbsp; | NULL &nbsp; &nbsp;| &nbsp; &nbsp; &nbsp; | | Location &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| varchar(255) | YES &nbsp;| &nbsp; &nbsp; | NULL &nbsp; &nbsp;| &nbsp; &nbsp; &nbsp; | +-------------------+--------------+------+-----+---------+-------+</pre><p>Following program inserts data into this table using batch processing (with prepared statement object).</p><pre class="prettyprint notranslate">import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; public class BatchProcessing_PreparedStatement { &nbsp; &nbsp;public static void main(String args[])throws Exception { &nbsp; &nbsp; &nbsp; //Getting the connection &nbsp; &nbsp; &nbsp; String mysqlUrl = &quot;jdbc:mysql://localhost/sampleDB&quot;; &nbsp; &nbsp; &nbsp; Connection con = DriverManager.getConnection(mysqlUrl, &quot;root&quot;, &quot;password&quot;); &nbsp; &nbsp; &nbsp; System.out.println(&quot;Connection established......&quot;); &nbsp; &nbsp; &nbsp; //Setting auto-commit false &nbsp; &nbsp; &nbsp; con.setAutoCommit(false); &nbsp; &nbsp; &nbsp; //Creating a PreparedStatement object &nbsp; &nbsp; &nbsp; PreparedStatement pstmt = con.prepareStatement(&quot;INSERT INTO Dispatches VALUES (?, ?, ?, ?, ?)&quot;); &nbsp; &nbsp; &nbsp; pstmt.setString(1, &quot;Keyboard&quot;); &nbsp; &nbsp; &nbsp; pstmt.setString(2, &quot;Amith&quot;); &nbsp; &nbsp; &nbsp; pstmt.setString(3, &quot;January&quot;); &nbsp; &nbsp; &nbsp; pstmt.setInt(4, 1000); &nbsp; &nbsp; &nbsp; pstmt.setString(5, &quot;Hyderabad&quot;); &nbsp; &nbsp; &nbsp; pstmt.addBatch(); &nbsp; &nbsp; &nbsp; pstmt.setString(1, &quot;Earphones&quot;); &nbsp; &nbsp; &nbsp; pstmt.setString(2, &quot;Sumith&quot;); &nbsp; &nbsp; &nbsp; pstmt.setString(3, &quot;March&quot;); &nbsp; &nbsp; &nbsp; pstmt.setInt(4, 500); &nbsp; &nbsp; &nbsp; pstmt.setString(5,&quot;Vishakhapatnam&quot;); &nbsp; &nbsp; &nbsp; pstmt.addBatch(); &nbsp; &nbsp; &nbsp; pstmt.setString(1, &quot;Mouse&quot;); &nbsp; &nbsp; &nbsp; pstmt.setString(2, &quot;Sudha&quot;); &nbsp; &nbsp; &nbsp; pstmt.setString(3, &quot;September&quot;); &nbsp; &nbsp; &nbsp; pstmt.setInt(4, 200); &nbsp; &nbsp; &nbsp; pstmt.setString(5, &quot;Vijayawada&quot;); &nbsp; &nbsp; &nbsp; pstmt.addBatch(); &nbsp; &nbsp; &nbsp; //Executing the batch &nbsp; &nbsp; &nbsp; pstmt.executeBatch(); &nbsp; &nbsp; &nbsp; //Saving the changes &nbsp; &nbsp; &nbsp; con.commit(); &nbsp; &nbsp; &nbsp; System.out.println(&quot;Records inserted......&quot;); &nbsp; &nbsp;} }</pre><h3>Output</h3><pre class="result notranslate">Connection established...... Records inserted......</pre><p>If you verify the contents of the Dispatches table, you can observe the inserted records as:</p><pre class="result notranslate">+--------------+------------------+-------------------+-------+----------------+ | Product_Name | Name_Of_Customer | Month_Of_Dispatch | Price | Location | +--------------+------------------+-------------------+-------+----------------+ | KeyBoard &nbsp; &nbsp; | Amith | January | 1000 | Hyderabad | | Earphones &nbsp; &nbsp;| SUMITH | March | 500 | Vishakhapatnam | | Mouse &nbsp; &nbsp; &nbsp; &nbsp;| Sudha | September | 200 | Vijayawada | +--------------+------------------+-------------------+-------+----------------+</pre>

• Related Questions & Answers
• Write an example JDBC program demonstrating the batch processing with statement object?
• Write an example JDBC program demonstrating the batch processing with CallableStatement object?
• What is batch processing in JDBC?
• What is Parameterized Batch Update in JDBC? Explain with an example?
• What is PreparedStatement in JDBC?
• Write a C program demonstrating strlen library function
• Write a C program demonstrating examples on pointers
• Batch Inserts Using JDBC Statements
• What are the advantages and limitations of JDBC PreparedStatement?
• Batch Inserts Using JDBC Prepared Statements
• Write an JDBC example to retrieve Clob value from a table using the getCharacterStream() method?
• Write an JDBC example for inserting value for Blob datatype into a table?
• How to process SQL statements with JDBC explain with an example?
• What are batch updates in JDBC? Explain?
• Write an example program on structure using C language