VRB News
Virtual Reality Brisbane
  • Home
  • About us
  • IT news
  • Tech
  • World
  • Contact
No Result
View All Result
  • Home
  • About us
  • IT news
  • Tech
  • World
  • Contact
No Result
View All Result
No Result
View All Result
Home IT news

JdbcTemplate for advanced users

admin by admin
June 16, 2021
in IT news
0
JdbcTemplate for advanced users
0
SHARES
7
VIEWS
Share on FacebookShare on Twitter

Getting Started with Spring Boot, Part 14 JdbcTemplate for Advanced Users

The JdbcTemplate class can be used to formulate database queries in Spring Boot without ugly boilerplate code. But the class can do even more than what we have already shown in the previous article.

Companies on the topic

Even for batch data processing, JdbcTemplate provides its own method.Even for batch data processing, JdbcTemplate provides its own method.

(©monsitj – stock.adobe.com)

The following piece of code demonstrates how to insert a record into the database using JdbcTemplate’s update () method:

String INSERT = "INSERT INTO Person (id, firstname, lastname) VALUES (?, ?, ?)";
jdbcTemplate.update(INSERT, 3, "Rainer", "Wain");

Again, all the boilerpate code to open the database connection, create the statement, and handle the IOException falls away. The instance of JdbcTemplate is injected as shown in the previous post, for example using @Autowired.

Handling long SQL strings as above is common, but also quite error-prone. It becomes especially dangerous if the strings are assembled at runtime. But even in the simple example here there are already pitfalls.

The order of the parameters must be observed when passing the arguments so that the correct question mark is replaced. If an error occurs, this may not be immediately noticeable and the database fills up with incorrect data.

Simple inserts with SimpleJdbcInsert

Fortunately, this is also more elegant, for example with the class SimpleJdbcInsert. An instance of this type is passed the JdbcTemplate and the table name is communicated. The statement is executed with the execute () method, which is passed as an argument a map with column names and values to be set:

SimpleJdbcInsert simpleJdbcInsert = new SimpleJdbcInsert(jdbcTemplate).withTableName("Person");
Map<String, Object> parameters = new HashMap<String, Object>();
parameters.put("ID", 3);
parameters.put("firstname", "Wilma");
parameters.put("lastname", "Ruhe");
simpleJdbcInsert.execute(parameters);

Dropping column names and values in the map does lead to a bit more code, but it’s less error-prone. The primary key was passed above, but you can also have it generated. The generated value is returned after executing the statement with the method executeAndReturnKey() :

SimpleJdbcInsert simpleJdbcInsert = new SimpleJdbcInsert(jdbcTemplate)
            .withTableName("Person")
            .usingGeneratedKeyColumns("Id");
Map<String, Object> parameters = new HashMap<String, Object>();
parameters.put("firstname", "Wilma");
parameters.put("lastname", "Ruhe");
Number newId = simpleJdbcInsert.executeAndReturnKey(parameters);

Sorry, there is no “SimpleJdbcUpdate”for updating records. So here must be on JdbcTemplate.update() can be resorted to:

String UPDATE = "UPDATE Person SET firstname = ?, lastname = ? WHERE Id = ?";
jdbcTemplate.update(UPDATE, "Frank", "Reich", 3);

The same applies to deleting records.

Calling the child by name: NamedParameterJdbcTemplate

To minimize errors with the parameter order and to bring more readability into the code, the NamedParameterJdbcTemplate class is an alternative. The names of the parameters in the SQL string are written out with a preceding colon. Instead of the simple values, the update () method gets a map with the parameter names as keys:

String UPDATE = "UPDATE Person SET firstname = :firstname, lastname = :lastname WHERE Id = :id";Map<String, Object> params = new HashMap<>();
params.put("id", 2);
params.put("firstname", "Andy");
params.put("lastname", "Tür");
namedParameterJdbcTemplate.update(UPDATE, params);

Like JdbcTemplate, NamedParameterJdbcTemplate can also be used for inserts, deletes and queries.

Batch processing

JdbcTemplate offers its own method for mass data processing, such as in the banking environment in nightly runs: batchUpdate (). This allows the same statement to be executed repeatedly with different data. In the following example, the records in the Person table are updated with a passed list of people.

The batchUpdate () method used expects an implementation of the BatchPreparedStatementSetter interface after the SQL string. It includes the setValues () methods for setting each attribute and the getBatchSize () method, which returns the number of records to update:

public int[] batchUpdate(final List<Person> persons) {
   int[] updateCounts = jdbcTemplate.batchUpdate("UPDATE Person SET firstname = ?, lastname = ? where id = ?",
      new BatchPreparedStatementSetter() {
         public void setValues(PreparedStatement ps, int i) throws SQLException {
            ps.setString(1, persons.get(i).getFirstname());
            ps.setString(2, persons.get(i).getLastname());
            ps.setLong(3, persons.get(i).getId().longValue());
         }
         public int getBatchSize() {
            return persons.size();
         }
      });
   return updateCounts;
}

An analog method with a customized SQL statement can also be used to insert records:

public int[] batchInsert(final List<Person> persons) {
   int[] updateCounts = jdbcTemplate.batchUpdate("INSERT INTO Person (firstname, lastname ) VALUES (?, ?)",
      new BatchPreparedStatementSetter() {
         public void setValues(PreparedStatement ps, int i) throws SQLException {
            ps.setString(1, persons.get(i).getFirstname());
            ps.setString(2, persons.get(i).getLastname());
         }
         public int getBatchSize() {
            return persons.size();
         }
      });
   return updateCounts;
}

As the examples presented show, JdbcTemplate and the related classes not only help reduce stupidem code without a subject, they also offer clever solutions to common problems when working with databases. Anyone who cannot or does not want to use JPA for whatever reason has a powerful extension of the JDBC API at hand.

(ID: 47397822)

Previous Post

Yor tags cloud resources in IaC frameworks

Next Post

Leaked: PSVR 2 will be released in 2022 with OLED displays

admin

admin

Related Posts

How ChatGPT can help you in everyday life
IT news

How ChatGPT can help you in everyday life

May 25, 2023
Amazon reveals the free Prime Gaming games of May 2023
IT news

Amazon reveals the free Prime Gaming games of May 2023

May 25, 2023
Safety Shoes in UAE
IT news

Protective safety footwear: Combining comfort and safety

May 23, 2023
WhatsApp will soon allow you to edit messages!
IT news

WhatsApp will soon allow you to edit messages!

May 19, 2023
Imagelaser technology: German technology regulates the iPhone display: Apple raves about a Swabian family-owned iPhone company giving details about its suppliers in Europe for the first time in years. A traditional company from Baden-Württemberg is doing particularly well. More… By Joachim Hofer and Martin-W. Buchenau
IT news

Imagelaser technology: German technology regulates the iPhone display: Apple raves about a Swabian family-owned iPhone company giving details about its suppliers in Europe for the first time in years. A traditional company from Baden-Württemberg is doing particularly well. More… By Joachim Hofer and Martin-W. Buchenau

May 17, 2023
Next Post
Leaked: PSVR 2 will be released in 2022 with OLED displays

Leaked: PSVR 2 will be released in 2022 with OLED displays

Premium Content

Manage SQL Server with Azure Data Studio

Manage SQL Server with Azure Data Studio

June 30, 2021
Etee: new controller XR with tracking fingers

Etee: new controller XR with tracking fingers

August 20, 2020
Haunted Mansion Demo by our partner Hyperlight

Haunted Mansion Demo by our partner Hyperlight

June 30, 2022

Browse by Category

  • Games
  • IT news
  • Tech
  • World

VRB News is ready to cooperate with webmasters and content creators. Send an email to info@virtualrealitybrisbane.com

Categories

  • Games
  • IT news
  • Tech
  • World

Recent Posts

  • How ChatGPT can help you in everyday life
  • Amazon reveals the free Prime Gaming games of May 2023
  • Protective safety footwear: Combining comfort and safety

© 2021 - The project has been developed ServReality

No Result
View All Result
  • Home
  • About us
  • IT news
  • Tech
  • World
  • Contact

© 2021 - The project has been developed ServReality

Are you sure want to unlock this post?
Unlock left : 0
Are you sure want to cancel subscription?