Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Best practice to select data using Spring JdbcTemplate

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 (Exception e) {     if (log.isDebugEnabled()) {         log.debug(e);     } } 


I'm eager to know the right one or any other best practice.

like image 383
Annamalai Thangaraj Avatar asked Aug 29 '13 06:08

Annamalai Thangaraj


People also ask

How do you write a select query using JdbcTemplate?

Use the queryForList(String sql) API method of JdbcTemplate class to execute a query for a result list, with the given static SQL select query. The results will be mapped to a List (one entry for each row) of result objects, each of them matching the specified element type.

Is JdbcTemplate faster than JPA?

At work we use Hibernate JDBCTemplate because it has more flexibility. It also has better performance than JPA because you are not "loading" a lot of unnecessary data into your app. In the JDBCTemplate case, your SQL skills go a long way in giving you exactly what you need at the right speed.

How does JdbcTemplate select multiple columns?

List<Map<String, Object>> rows = jdbcTemplate. queryForList("SELECT name, middle, family FROM table"); Every Map in this List represents a row in the returned query, the key represents the column name, and the value is the value of that column for that row.


2 Answers

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 } 
like image 177
Dhanush Gopinath Avatar answered Sep 29 '22 01:09

Dhanush Gopinath


I am facing similar scenario and found a cleaner solution when using ResultSetExtractor instead of RowMapper

jdbcTemplate.query(DBConstants.GET_VENDOR_DOCUMENT, new Object[]{vendorid}, rs -> {              if(rs.next()){                 DocumentPojo vendorDoc = new DocumentPojo();                 vendorDoc.setRegDocument(rs.getString("registrationdoc"));                 vendorDoc.setMsmeLetter(rs.getString("msmeletter"));                 vendorDoc.setProprietorshipDocument(rs.getString("propertiershipformat"));                 vendorDoc.setNeftDocument(rs.getString("neftdoc"));                 vendorDoc.setPanCardDocument(rs.getString("pancard"));                 vendorDoc.setCancelledChequeDoc(rs.getString("cheque"));                 return vendorDoc;             }             else {                 return null;             }      }); 

If no result is found from database, I had put a if condition for resultset and return null reference. So, I didn't need to try catch the code and pass two queries to database.

Main advantage of ResultSetExtractor (in this scenario) is with ResultsetExtractor you will need to iterate through the result set yourself, say in while loop.

More Points can be found here here

like image 21
Ankit Avatar answered Sep 29 '22 01:09

Ankit