How to Specify Named Parameters Using the NamedParameterJdbcTemplate

Overview

Spring JdbcTemplate is a powerful mechanism to connect to the database and execute SQL queries. It internally uses JDBC API but eliminates a lot of problems of JDBC API. It helps to avoid writing boilerplate code for such as creating the connection, statement, closing resultset, connection, etc.. 

With JdbcTemple, we generally do pass the parameter values with "?" (question mark). However, it is going to introduce the SQL injection problem. So, Spring provides another way to insert data by the named parameter. In that way, we use names instead of "?". So it is better to remember the data for the column. This can be done using NamedParameterJdbcTemplate.

In this article, we will be learning how to use NamedParameterJdbcTemplate to pass named parameter.

Prerequisites

• JDK 1.8

• Spring-Boot Basic Knowledge

• Gradle

• Any IDE (Eclipse, VSD)

Gradle Dependency

This project needs a standard spring-boot-starter-web along with spring-boot-starter-jdbc and h2 database driver. I am using spring-boot version springBootVersion = '2.1.2.RELEASE' for this exercise:

dependencies {
implementation 'org.springframework.boot:spring-boot-starter-jdbc'
implementation 'org.springframework.boot:spring-boot-starter-web'
implementation 'com.h2database:h2'
testImplementation 'org.springframework.boot:spring-boot-starter-test'
}

Configuration

We are using H2 as the database. And H2 provides a web interface called H2 Console to see the data. Let’s enable h2 console in the application.properties

/src/main/resources/application.properties:

# Enabling H2 Console
spring.h2.console.enabled=true

server.port=8100

Also, We have configured the port as 8100 for this application. We will see how to use the H2 Console later. For now, let's put other required code.

Domain

Let's create a domain class User. We will use this class to map with DB table USERS and insert each field of this object to this table:

public class User {

    private int id;
    private String name;
    private String address;
    private String email;
  // standard setters and getters
}

DAO

Now, let's create a DAO interface UserDao which defines what all methods need to be implemented:

public interface UserDao {
    public User create(final User user) ;
    public List<User> findAll() ;
    public User findUserById(int id);
}

DAOImpl

Now, let's create the implementation class. This will implement all the UserDao methods. Here, we will be using NamedParameterJdbcTemplate to create and retrieve the record. NamedParameterJdbcTemplate has many methods. We have used 3 methods here to demonstrate how to pass named parameters:

@Repository
public class UserDaoImpl implements UserDao {

private final String INSERT_SQL = "INSERT INTO USERS(name, address, email)  values(:name,:address,:email)";
private final String FETCH_SQL = "select record_id, name, address, email from users";
private final String FETCH_SQL_BY_ID = "select * from users where record_id = :id";

@Autowired
private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

public User create(final User user) {
KeyHolder holder = new GeneratedKeyHolder();
SqlParameterSource parameters = new MapSqlParameterSource()
.addValue("name", user.getName())
.addValue("address", user.getAddress())
.addValue("email", user.getEmail());
namedParameterJdbcTemplate.update(INSERT_SQL, parameters, holder);
user.setId(holder.getKey().intValue());
return user;
}

public List<User> findAll() {
return namedParameterJdbcTemplate.query(FETCH_SQL, new UserMapper());
}

public User findUserById(int id) {
Map<String, Integer> parameters = new HashMap<String, Integer>();
parameters.put("id", id);
return (User) namedParameterJdbcTemplate.queryForObject(FETCH_SQL_BY_ID, parameters, new UserMapper());
}

}

class UserMapper implements RowMapper {

@Override
public User mapRow(ResultSet rs, int rowNum) throws SQLException {
User user = new User();
user.setId(rs.getInt("record_id"));
user.setName(rs.getString("name"));
user.setAddress(rs.getString("address"));
user.setEmail(rs.getString("email"));
return user;
}

}

1. update() — We have used the update() method to insert the data to USERS table. If we used regular JDBCTemplate, we will be using

StringINSERT_SQL  = "INSERT INTO USERS(name, address, email) values (?,?,?)"; 

But we have used NamedParameterJdbcTemplate so our query string will be like this:   INSERT_SQL = "INSERT INTO USERS(name, address, email)values(:name,:address,:email)"; We see that we are using the named parameters instead of "?".  

2. query() —  We have used the query() method to retrieve all the User records from USERS table. Here our query string will be:   String FETCH_SQL = "select record_id, name, address, email from users";  

This will be the same as any other jdbcTemplate query. No special handling here.

3. queryForObject() — We have used queryForObject() method to retrieve User based on the record_id field. Here we will have query string as below:

 String FETCH_SQL_BY_ID = "select * from users where record_id = :id"; 
If you notice, here we have used the named parameter "id" instead of the "?".

Please also do note that we have used RowMapper to map the resultset with the User object.

Controller

Now, let's quickly expose this functionality through REST so that we can test it easily:

@RestController
public class UserController {
    @Autowired
    private UserDaoImpl userDao;

    @PostMapping("/users")
    public ResponseEntity<User> createUser() {
        User user   =   userDao.create(getUser());
        return ResponseEntity.ok(user);
    }

    @GetMapping("/users")
    public List<User> retrieveAllUsers() {
        return userDao.findAll();

    }
    @GetMapping("/users/{id}")
    public User retrieveUserById(@PathVariable int id ) {
        return userDao.findUserById(id);
    }

    private User getUser() {
User user = new User();
user.setAddress("Marathahalli, Bangalore");
user.setEmail("rajesh.bhojwani@gmail.com");
user.setName("Rajesh Bhojwani");
return user;
}

}

Build and Test

To build this application, we need to run Gradle build command:

 gradlew clean build 

It will generate the jar file under build/libs.

To start the application, we need to run the command:   java -jar build/libs/springbootjdbc-0.0.1-SNAPSHOT.jar 

Now, before testing the application, we need to create the USERS table in the h2 database. So, let's start the h2-console at http://localhost:8100/h2-console. 

Make sure JDBC URL is updated to jdbc:h2:mem:testdb. And, then click on Connect button.  Now, we can create the schema of the table USERS by running ddl statement.

 CREATE TABLE users (record_id bigint NOT NULL AUTO_INCREMENT, name varchar(100), address varchar(250), email varchar(100), PRIMARY KEY (record_id)); Once the table is created, now we can test our REST APIs to create and retrieve User record.

1. POST call -   http://localhost:8100/users  

2. GET call -    http://localhost:8100/users 

3. GET Call -   http://localhost:8100/users/1 

Conclusion

To summarize, we have seen in this article how to pass named parameters by using NamedParameterJdbcTemplate. 

As usual, the code can be found over Github.