When you want to send a bunch of SQL queries to database in one database call to execute that’s what batch concept comes.
This reduces network traffic and improves performance of your application
In this post we will learn about JDBC batch update with java.sql.PreparedStatement.
As you know java.sql.PreparedStatement deals with dynamic SQL with place holder(?).
so Here we have one dyamic SQL query with two place holder(?) and these parameter values we will set dynamically
“UPDATE employee_table set salary=? WHERE employee_id=?”
Have look in ClientTest.java class how we have used above SQL Query
DB Script to create employee_table(database schema jdbcdb)
1 2 3 4 5 6 7 8 9 |
CREATE TABLE `employee_table` ( `employee_id` int(11) NOT NULL auto_increment, `employee_name` varchar(60) NOT NULL, `email` varchar(45) NOT NULL, `salary` double default NULL, `date_of_joining` datetime default NULL, `bonus` decimal(10,0) default NULL, PRIMARY KEY (`employee_id`) ) ; |
DBUtil.java class which is responsible to connect with MySQL database.
this util class uses getConnection() method of java.sql.DriverManager class
public static Connection getConnection(String url,String user, String password) throws SQLException
This method takes three parameters database URL,username and password.
Here database username =”root”,password = “root” what password had supplied during MYSQL database installation time and finally database URL =“jdbc:mysql://localhost:3306/jdbcdb” Where jdbc is the API, mysql is the database, localhost is the server name on which mysql database server is running, we may also use IP address instead machine name, 3306 is the port number and jdbcdb(Make you you have created this schema in MySQL database) is the database name. You may use any database name, in that case, You need to replace the jdbcdb with your database name.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
package com.infotech.util; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; public class DBUtil { private static final String DB_DRIVER_CLASS="com.mysql.jdbc.Driver"; private static final String DB_USERNAME="root"; private static final String DB_PASSWORD="root"; private static final String DB_URL ="jdbc:mysql://localhost:3306/jdbcdb"; private static Connection connection = null; static{ try { Class.forName(DB_DRIVER_CLASS); connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD); } catch (ClassNotFoundException | SQLException e) { e.printStackTrace(); } } public static Connection getConnection(){ return connection; } } |
Client program
In this client program we have an dynamic SQL update query with place holder(?) .Here basically we are going to update salary of more than one employees based on his/her employee id.
Every time after setting employee new salary and id. which we have added in PreparedStatement object(ps) by calling addBatch() method and finally when we call executeBatch() method all these updates will send to database in one go.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
package com.infotech.client; import java.sql.Connection; import java.sql.PreparedStatement; import com.infotech.util.DBUtil; public class ClientTest { public static void main(String[] args) { String UPDATESQL="UPDATE employee_table set salary=? WHERE employee_id=?"; try(Connection connection = DBUtil.getConnection(); PreparedStatement ps= connection.prepareStatement(UPDATESQL)) { ps.setDouble(1, 70000.00); ps.setInt(2, 1); ps.addBatch(); ps.setDouble(1, 95000.00); ps.setInt(2, 2); ps.addBatch(); ps.setDouble(1, 75000.00); ps.setInt(2, 20); ps.addBatch(); int[] executeBatch = ps.executeBatch(); for (int i : executeBatch) { System.out.println(i); } } catch (Exception e) { e.printStackTrace(); } } } |
After running client program you will give below output on IDE console:
1
0
0
employee_table in database schema(jdbcdb)
That’s all for this topic JDBC batch update with PreparedStatement.
You May Also Like:
Calling StoredProcedure Using CallableStatement
Get ResultSet By Calling StoredProcedure Using CallableStatement
Calling database custom Function Using CallableStatement
JDBC batch update with Statement
JDBC batch update with CallableStatement
How to retrieve data from JDBC resultset in java
How to update a Row in a Database Table Using an updatable ResultSet
How to insert a Row in a Database Table Using an updatable ResultSet
JDBC ResultSet navigation methods example in java
If you have any feedback or suggestion please feel free to drop in below comment box.