Introduction

1. Oracle Sql Select For Update
2. Jdbctemplate Batch Update
3. Select For Update Nowait
4. Spring Jdbctemplate Select For Update Example

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.

P.S You may also interested in this Spring Boot JDBC Examples. Query for Single Row. In Spring, we can use jdbcTemplate.queryForObject to query a single row record from database, and convert the row into an object via row mapper. Update method in spring JdbcTemplate class is suitable for DML non-select i mean insert, update, delete. operation on the database update method accepts either static or dynamic sql commands as parameter. FOR UPDATE doesn't update anything, it only locks selected rows as if it was updated. So you can't set something to true using FOR UPDATE, you would need to execute a separate UPDATE statement. In any case, things work the same using spring-jdbc, as it would executing statements directly. As it stands, it is unclear what you're really asking.

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

Jul 13, 2020 In the post Spring JdbcTemplate Insert, Update And Delete Example I have already discussed how JdbcTemplate can be used for inserting and updating data in the DB. I left behind the part to read from Database using Select query.

Basic Query methods

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

Querying for Date

Querying for Integer

OR

Querying for String

Cached. Querying for List

Batch operations

JdbcTemplate also provides convenient methods to execute batch operations.

Oracle Sql Select For Update

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.

Query for List of Maps

SQLRowSet

OR

In this Spring CRUD Example, we will build a Simple Spring Application and perform CRUD operations using Spring JdbcTemplate. We will create a simple Employee management application which has abilities to create a new employee, update the existing employee, get a particular employee/ all employee and finally delete the existing employee.

Creating table

Create EMPLOYEETable, simply Copy and Paste the following SQL query in the query editor to get the table created.

Folder Structure:

1. Create a simple Maven Project “SpringJDBC”by selecting maven-archetype-quickstart and create a package for our source files “com.javainterviewpoint” under src/main/java
2. Now add the following dependency in the POM.xml
3. Create the Java classes Employee.java,EmployeeDAOImpl.java andSpringJDBCExample.java under com.javainterviewpointfolder.

Spring CRUD Example

Employee.java

Our Employee class is a simple POJO class consisting getters and setters of Employee properties id, name, age, dept.

SpringConfig.xml

In our configuration file, we have defined the three beans

1. DriverManagerDataSource – DriverManagerDataSource contains database related configurations such as driver class name, connection URL, username and password.
2. JdbcTemplate – We will be referencing the dataSource id (DriverManagerDataSource ) to the property dataSource of the JdbcTemplate class.
3. EmployeeDAOImpl – We will be referencing the jdbcTemplate id to the property jdbcTemplate of the EmployeeDAOImpl class.

EmployeeDAO.java

EmployeeDAOImpl.java

EmployeeDAOImpl class implements the interface EmployeeDAO and overrides all the unimplemented methods. We have the below methods in our EmployeeDAOImpl class

• setJdbcTemplate() – Through Spring setter injection we will be injecting the jdbcTemplate from the Spring configuration file.
• getAllEmployee() – In order to fetch all the records from the database we just need to pass the SQL and the instance of the ResultSetExtractor to the query() method of jdbcTemplate. ResultSetExtractor interface accepts the ResultSet and returns a Java List. We need to override the extractData() method and map each ResultSet to an Employee object add to a list.
• getEmployeeById() – In order to fetch a particular record we just need to pass the SQL and the instance of the RowMapper to the queryForObject() method of jdbcTemplate. RowMapper interface internally iterates the ResultSet and adds it to the Collection (Map). Hence there is no need for us to iterate the ResultSet as we do in the ResultSetExtractor.
• updateEmployee() – We will be updating the corresponding employee by calling the update() method of the jdbcTemplate passing the SQL and the parameters.
• deleteEmployee() – In order to delete an employee, we need to call the update() method of the jdbcTemplate passing the SQL and the id.

SpringJDBC.java

Jdbctemplate Batch Update

• ClassPathXmlApplicationContext class reads our Configuration File(SpringConfig.xml)
• We will get our EmployeeDAOImpl Class instance by calling the getBean() method over the context.
• Call the saveEmployee(), getEmployeeById(), getAllEmployees(),updateEmployee() and deleteEmployee() methods over the EmployeeDAOImpl instance which we got above.

Select For Update Nowait

Output:

Spring Jdbctemplate Select For Update Example

Download Source Code