DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports
Events Video Library
Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
View Events Video Library
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Modern Digital Website Security: Prepare to face any form of malicious web activity and enable your sites to optimally serve your customers.

Containers Trend Report: Explore the current state of containers, containerization strategies, and modernizing architecture.

Low-Code Development: Learn the concepts of low code, features + use cases for professional devs, and the low-code implementation process.

E-Commerce Development Essentials: Considering starting or working on an e-commerce business? Learn how to create a backend that scales.

Related

  • Connection Pooling
  • How To Convert MySQL Database to SQL Server
  • Navigating NoSQL: A Pragmatic Approach for Java Developers
  • Harmonizing Space, Time, and Semantics: Navigating the Complexity of Geo-Distributed IoT Databases

Trending

  • Software Architecture Design Patterns for Serverless System
  • The Top 5 Tools for Automated Front-End Testing
  • Dockerizing ML Models: A Deployment Guide
  • JPA Criteria With Pagination
  1. DZone
  2. Data Engineering
  3. Databases
  4. Batch Updates With JdbcTemplate

Batch Updates With JdbcTemplate

In the example below, we will explore how to insert thousands of records into a MySQL database using batchUpdate.

Michael Good user avatar by
Michael Good
·
Nov. 20, 17 · Tutorial
Like (2)
Save
Tweet
Share
66.8K Views

Join the DZone community and get the full member experience.

Join For Free

There may come a time when you are using JdbcTemplate and want to use a PreparedStatement for a batch update. In the example below, we will explore how to insert thousands of records into a MySQL database using batchUpdate.

First, we must configure the datasource to use in our application.properties.

spring.datasource.url=jdbc:mysql://localhost:3306/myurl
spring.datasource.username=root
spring.datasource.password=mypassword
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect

Now in order to use this datasource in our Service.class, we need to make it Autowired. Below, I place the @Autowired annotation above the declaration of the DataSource, but depending on your preferences, it could also be placed over the method constructor.

@Autowired
private DataSource dataSource;
private JdbcTemplate jdbcTemplate;

public void setDataSource(DataSource dataSource) {
 this.dataSource = dataSource;
 this.jdbcTemplate = new JdbcTemplate(dataSource);
}

In my example below, I have the method set to Private because the method should not be used outside of the class. However, depending on your own needs, you may make this Public.

Here is the full code. Below, I will explain the code line-by-line.

private void loadTheContent(String[] rows) throws SQLException {
 String Query = "INSERT INTO content (id, type, publisher, title) VALUES (?,?,?,?)";
 JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
 jdbcTemplate.batchUpdate(Query, new BatchPreparedStatementSetter() {
  @Override
  public void setValues(PreparedStatement ps, int i) throws SQLException {
   String[] col = new String[4];
   col = rows[i].split("-");
   ps.setString(1, col[0]);
   ps.setString(2, col[1]);
   ps.setString(3, col[2]);
   ps.setString(4, col[3]);
  }

  @Override
  public int getBatchSize() {
   return rows.length;
  }
 });
 return;
}

An an array of Strings is passed into the loadTheContent method:

(String[] rows)

In my example, they are from a processed XML file. Covering XML processing is out of scope for this post, but will be covered in the future.

In order to successfully execute the batch prepared statement, we need to know the batch size — meaning how many records will be processed.

getBatchSize()

This is a request for the batch size. With the code below:

  return rows.length;

...we instruct the program to measure the size of the rows array in order to calculate the batch size.

Next, the query is defined.

String Query = "INSERT INTO content (id, type, publisher, title) VALUES (?,?,?,?)";

Take note that the query String cannot have a keyword such as final next to it as it is not immutable. Also, as this is not using a NamedParameterJdbcTemplate, we must use ? placeholders for the values.

JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

The above instantiation of jdbcTemplate allows us to execute the SQL to our correct database. Since dataSource is autowired, this will execute correctly.

Next, we actually define the batchUpdate. You may notice that the batchUpdate looks somewhat similar to a regular jdbcTemplate update.

BatchPreparedStatmentSetter provides an interface to set values on the PreparedStatement.

jdbcTemplate.batchUpdate(Query, new BatchPreparedStatementSetter() {
   @Override
   public void setValues(PreparedStatement ps, int i) throws SQLException {

The Query parameter is already defined as a String, which I discussed above. The int i is the current row that is being processed.

With this in mind, we set values for each column in the record. Below, you see that I create an Array of four values since I know that each record has four columns.

String[] col = new String[4];
col = rows[i].split("-");
ps.setString(1, col[0]);
ps.setString(2, col[1]);
ps.setString(3, col[2]);
ps.setString(4, col[3]);

I define each value in the col array by splitting at the - character in rows[i]. Next, we use each value in the col array to set the values for the SQL query.

This means that the ? placeholders below are replaced by col[0], etc.

String Query = "INSERT INTO content (id, type, publisher, title) VALUES (?,?,?,?)";

For reference on JdbcTemplate.batchUpdate, see this documentation.

If you have any questions, ask in the comments and I will try to help!

Database

Published at DZone with permission of Michael Good, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

  • Connection Pooling
  • How To Convert MySQL Database to SQL Server
  • Navigating NoSQL: A Pragmatic Approach for Java Developers
  • Harmonizing Space, Time, and Semantics: Navigating the Complexity of Geo-Distributed IoT Databases

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends: