NamedParameterJDBCTemplate In Spring

While working with JdbcTemplate, if we want to pass any values to the queries while executing, we need to declare the (?) place holders in the query and need to pass the values for these place holders in an Object array. It would be tough to correlate the values you are passing the object array to a (?) field, as we completely dependent on order of passing, this approach is easily error prone and future changes involves lot of effort.

In order to overcome the above disadvantage spring has introduces a class NamedParameterJdbcTemplate, it a wrapper on JdbcTemplate class, which means it exactly contains similar set of features that JdbcTemplate. In addition, NamedParameterJdbcTemplate allows you to declare named parameters in yhe query instead of (?) as “:varName”.

So, while executing the query you can replace the “:varName” with the value. So that you will be sure that for which named parameter what is the value being passed and you don’t need to rely on order of passing.

Basically you need to pass values for Named Parameters declare in a query. One way of passing the values is Map where string represents the named Parameter name and object to represents the value you want to pass for it.

Apart from this you can create an object of SqlParameterSource implementation class object; one of the implementation is MapSqlParameterSource, which represents data in terms of Map of key and values. Below examples shows how to pass values for named parameters declared in the query.

Another implementation of SqlParameterSource is BeanPropertySource this takes input as Object and creates Map of key and value where key is the Object attribute name and value as its value.

The below example depicts all these scenarios.

StudentDao.java

import com.npjt.business.StudentBO;

   public class StudentDao {
          private final String SQL_SEARCH_STUDENT_BY_NAME = “SELECT STUDENT_ID, NAME FROM STUDENT WHERE NAME LIKE :STUDENT_NAME”;
          private final String SQL_INSERT-STUDENT = “INSERT INTO STUDENTS(STUDENT_ID, NAME) VALUES(:studentId, :studentName)”;

          private NamedParameterJdbcTemplate npJdbcTemplate;

          public StudentDao(NamedParameterJdbcTemplate npJdbcTemplate) {
                 this.npJdbcTemplate = npJdbcTemplate;
          }        

          public List<StudentBO> searchStudentByName(String name) {
                 sqlParameterSource paramMap = new MapSqlParameterSource(“STUDENT_NAME”, name);

                 return npJdbcTemplate.query(SQL_SEARCH_STUDENT_BY_NAME, paramMap, new StudentRowMapper());
          }

          public int insert(StudentBO student) {
                 SqlParameteSource paramSource = new BeanPropertySqlParameterSource(student);
                 return npJdbcTemplate.update(SQL_INSERT_STUDENT, paramSource);
          }

          private static final class StudentRowMapper implements RowMapper<StudentBO> {
                  @Override
                  public StudentBO mapRow(ResultSet resultset, int rowNum) throws SQLException {
                         return new StudentBO(resultSet.getInt(“STUDENT_ID”), resultSet.getString(“NAME”));
                  }
         }
   }

Thanks for reading. If you like this post please follow us for more updates about technology related updates.