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
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

Last call! Secure your stack and shape the future! Help dev teams across the globe navigate their software supply chain security challenges.

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workloads.

Releasing software shouldn't be stressful or risky. Learn how to leverage progressive delivery techniques to ensure safer deployments.

Avoid machine learning mistakes and boost model performance! Discover key ML patterns, anti-patterns, data strategies, and more.

Related

  • How Trustworthy Is Big Data?
  • Fixing Common Oracle Database Problems
  • How to Restore a Transaction Log Backup in SQL Server
  • Why I Built the Ultimate Text Comparison Tool (And Why You Should Try It)

Trending

  • Simplify Authorization in Ruby on Rails With the Power of Pundit Gem
  • Java's Quiet Revolution: Thriving in the Serverless Kubernetes Era
  • Comparing SaaS vs. PaaS for Kafka and Flink Data Streaming
  • Stateless vs Stateful Stream Processing With Kafka Streams and Apache Flink
  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.

By 
Michael Good user avatar
Michael Good
·
Nov. 20, 17 · Tutorial
Likes (2)
Comment
Save
Tweet
Share
69.1K 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

  • How Trustworthy Is Big Data?
  • Fixing Common Oracle Database Problems
  • How to Restore a Transaction Log Backup in SQL Server
  • Why I Built the Ultimate Text Comparison Tool (And Why You Should Try It)

Partner Resources

×

Comments

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • 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: