Best practice to select data using Spring JdbcTemplate

Question

I want to know what is the best practice to select records from a table. I mentioned two methods below from that I want to know which one is best practice to select the data from a table using Spring JdbcTemplate. First example ``` try { String sql = "SELECT id FROM tableName WHERE column_name = '" + coulmn value + "'"; long id = jdbcTemplate.queryForObject(sql, Long.class); } catch (Exception e) { if (log.isDebugEnabled()) { log.debug(e); } } ``` This throws the following exception: Expected 1 actual 0 like when table doesn't contain any data. My friend told this is not the best practice to select the data. He suggested that the below mentioned code is the only best practice to select data. Second example ``` try { String countQuery = "SELECT COUNT(id) FROM tableName"; int count = jdbcTemplate.queryForInt(countQuery); if (count > 0) { String sql = "SELECT id FROM tableName WHERE column_name = '" + coulmn value + "'"; long id = jdbcTemplate.queryForObject(sql, Long.class); } } catch (…

Accepted Answer

Definitely the first way is the best practice, because in the second way you are hitting the database twice where you should actually hit it only once. This can cause performance issues.

What you need to do is catch the exception `EmptyResultDataAccessException` and then return null back. Spring JDBC templates throws back an EmptyResultDataAccessException exception if it doesn't find the data in the database.

Your code should look like this.

```
try {
     sql = "SELECT id FROM tableNmae WHERE column_name ='"+ coulmn value+ "'";
     id= jdbcTemplate.queryForObject(sql, Long.class);
} 
catch (EmptyResultDataAccessException e) {
   if(log.isDebugEnabled()){
       log.debug(e);
   }
   return null
}
```

Best practice to select data using Spring JdbcTemplate

Solution

Problem

Related problems