• Select For Update Mysql
• Jdbctemplate Batch Update
• Oracle Sql Select For Update
• Spring Jdbctemplate Batchupdate

In this Spring tutorial, we discuss how to access relational database using JDBC with Spring framework. The example code is tested with Spring JDBC 4.0.3 and MySQL database server 5.5.23 using MySQL Connector Java 5.1.30 library. The sample project is built using Eclipse Kepler and Maven 3.0. Of course the code example will work with newer versions of Spring, JDBC and MySQL database.

1. Working with JDBC in Spring (JdbcTemplate)

Spring framework simplifies JDBC development through the use of JdbcTemplate class which helps programmers to avoid common errors, reduce boilerplate code to focus on writing SQL queries and extracting result set. To use

In this example you will learn how to select records from the database using JdbcTemplate.queryForList method. This method returns a List object which stores information selected from the table in a HashMap object. The key of the map is the table’s field names while the value of the map contains the corresponding table’s field value. The JDBC template is the main API through which we'll access most of the functionality that we're interested in: creation and closing of connections; executing statements and stored procedure calls; iterating over the ResultSet and returning results; Firstly, let’s start with a simple example to see what the JdbcTemplate can do.

JdbcTemplate, we must inject a DataSource reference either via constructor or setter when instantiating a new JdbcTemplate object. For example:Or setting the DataSource reference via setter:Then use variants of the JdbcTemplate’supdate() and query() methods for execute updates and queries. For example:For querying for results, an implementation (typically an anonymous class) of the RowMapper interface must be provided in order to map a row from the result set to an object in the returned list. Let’s see the details by coding a sample project.

2. Setting up Database

Execute the following MySQL script to create a database called ‘contactdb’ with a table named ‘contact’:

3. Configuring Dependencies in Maven

Update your pom.xml file with two dependencies:

• Spring JDBC dependency:
• MySQL Connector Java dependency:

4. Writing Model Class

Code a JavaBean class as follows:This class simply maps a row in the contact table to a Java object.

5. CRUD Examples with JdbcTemplate

Here’s code of a sample program that demonstrates performing CRUD operations using JDBC with Spring JdbcTemplate:For executing a query that select all rows from the tabble and returns a list of domain model class objects, you can use the BeanPropertyRowMapper class greatly simplifies the code, for example:As long as the Contact class has the fields exactly match the columns in the database table.You can also use the

Select For Update Mysql

BeanPropertyRowMapper class for a query that returns a single object, for example:NOTES:

• You should change the database URL, username and password according to settings on your environment.
• For executing SQL Insert statement, you can use the SimpleJdbcInsert class that greatly simplifies the code.
• The JdbcTemplate’s methods throw runtime DataAccessException, so here’s an example if you want to catch this exception explicitly:

For more code readability, you can use Spring NamedParameterJdbcTemplate, instead of using question marks (?) as placeholders.And you can download the sample project in the Attachments section below.You can also watch the video version of this tutorial:

References:

Jdbctemplate Batch Update

Related Spring and Database Tutorials:

• Understand Spring Data JPA with Simple Example
  

Other Spring Tutorials:

Attachments:

[Eclipse-Maven project]

13 kB

Introduction

The JdbcTemplate class executes SQL queries, update statements and stored procedure calls, performs iteration over ResultSets and extraction of returned parameter values. It also catches JDBC exceptions and translates them to the generic, more informative, exception hierarchy defined in the org.springframework.dao package.

Instances of the JdbcTemplate class are threadsafe once configured so it can be safely inject this shared reference into multiple DAOs.

Basic Query methods

Some of the queryFor* methods available in JdbcTemplate are useful for simple sql statements that perform CRUD operations.

Oracle Sql Select For Update

Querying for Date

Querying for Integer

OR

Querying for String

Querying for List

Query for List of Maps

SQLRowSet

OR

Batch operations

JdbcTemplate also provides convenient methods to execute batch operations.

Batch Insert

Batch Update

There are further batchUpdate methods which accept List of object array as input parameters. These methods internally use BatchPreparedStatementSetter to set the values from the list of arrays into sql statement.

NamedParameterJdbcTemplate extension of JdbcTemplate

The NamedParameterJdbcTemplate class adds support for programming JDBC statements using named parameters, as opposed to programming JDBC statements using only classic placeholder ( '?') arguments. The NamedParameterJdbcTemplate class wraps a JdbcTemplate, and delegates to the wrapped JdbcTemplate to do much of its work.

Spring Jdbctemplate Batchupdate