39

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.

informatik01
  • 16,038
  • 10
  • 74
  • 104
Annamalai Thangaraj
  • 522
  • 1
  • 5
  • 10

5 Answers5

44

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
}
Dhanush Gopinath
  • 5,652
  • 6
  • 37
  • 68
  • 53
    Sorry to say that but neither are best practice ;) while Dhanushs answer is ok, String concatenation for a query is a NOGO! This just asks for an sql injection attack and also you loose performance since the query statement cannot be cached. Take a look at (named)parameters to solve both issues. – Martin Frey Aug 30 '13 at 06:24
  • 7
    @MartinFrey - You are right. String concatenation is not the best practice. My intention was to point out the difference between the two approaches given in the question :) – Dhanush Gopinath Aug 30 '13 at 07:09
  • I was scratching my head because of this problem. You helped me solve it. Thank you – viper Apr 09 '16 at 10:19
  • If we catch `EmptyResultDataAccessException`, isn't entire transaction rolled back by Spring? How to get rid of that? For example, lets say we are making running few other queries/updates to db in the same transaction where one of them throws `EmptyResultDataAccessException`. – Munish Chandel Aug 12 '19 at 02:23
  • 1
    @Munish Chandel - Spring will by default roll back when a RuntimeException occurs. When you catch EmptyResultDataAccessException (which is a RuntimeException) then no RuntimeException occurs, and the transaction will succeed. By the way the OP does not mention transactions... – Jan Peter May 13 '20 at 07:27
  • queryForObject will throw nullpointerexception if the result is null, so this is not a good practice. – Philip Rego Apr 29 '21 at 18:42
  • This is an old question, but might help someone. Please see: https://stackoverflow.com/questions/37686305/spring-jdbc-returning-0-or-1-rows for using the spring method which returns null, instead of throwing exception when no data found. Martin Frey has already mentioned a good point about using namedParameterJdbcTemplate as another best practice – JavaTec Aug 03 '23 at 15:48
9

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

Community
  • 1
  • 1
Ankit
  • 2,126
  • 4
  • 33
  • 53
  • Wouldn't calling rs.next() move the cursor forward for a second time after the JDBC template already moved it once, resulting in missing the first row? – kermit11 Dec 14 '20 at 11:55
  • Question, was for getting a single row. For multiple rows, we can use the option of rs.hasNext – Ankit Dec 15 '20 at 05:22
7

This is queryForObject method source code

@Nullable
public <T> T queryForObject(String sql, RowMapper<T> rowMapper) throws 
DataAccessException {
    List<T> results = this.query(sql, rowMapper);
    return DataAccessUtils.nullableSingleResult(results);
}

DataAccessUtils.nullableSingleResult

    @Nullable
public static <T> T nullableSingleResult(@Nullable Collection<T> results) throws IncorrectResultSizeDataAccessException {
    if (CollectionUtils.isEmpty(results)) {
        throw new EmptyResultDataAccessException(1);
    } else if (results.size() > 1) {
        throw new IncorrectResultSizeDataAccessException(1, results.size());
    } else {
        return results.iterator().next();
    }
}

dunno why they throw exception on empty collection, probably this is just a copy-paste from method above

    public static <T> T requiredSingleResult(@Nullable Collection<T> results) throws IncorrectResultSizeDataAccessException {
    if (CollectionUtils.isEmpty(results)) {
        throw new EmptyResultDataAccessException(1);
    } else if (results.size() > 1) {
        throw new IncorrectResultSizeDataAccessException(1, results.size());
    } else {
        return results.iterator().next();
    }
}

One more step above the method they shoult have used

    @Nullable
public static <T> T singleResult(@Nullable Collection<T> results) throws IncorrectResultSizeDataAccessException {
    if (CollectionUtils.isEmpty(results)) {
        return null;
    } else if (results.size() > 1) {
        throw new IncorrectResultSizeDataAccessException(1, results.size());
    } else {
        return results.iterator().next();
    }
}

NOW SOLUTION helped me: Extend JdbcTemlate class (you can construct it with DataSource injected) and overrride the queryForObject method:

    @Nullable
public <T> T queryForObject(String sql, RowMapper<T> rowMapper) throws DataAccessException {
    List<T> results = this.query(sql, rowMapper);
    return DataAccessUtils.singleResult(results);
}

now work with your implementation Don't forget to check if it works on spring version update (very unlikely IMHO)

1

Better way to Use ifNull in query so if there is null then you get 0 Eg.-

sql = "SELECT ifNull(id,0) FROM tableName WHERE column_name ='"+ coulmn value+ "'";

Using this way you can get as default 0 otherwise your Id

TDG
  • 5,909
  • 3
  • 30
  • 51
Vikram
  • 179
  • 1
  • 7
0

The best way to get row from a table by condition in case there might be no data in this table - use query with ResultSetExtractor

     fun findDailyReport(date: LocalDate): String? {
        val sql = """select * from reports_table where report_date = :date"""
        val map = MapSqlParameterSource("date", date)
        return jdbcTemplate.query(sql, map, ResultSetExtractor { rs ->
            if (rs.next()) {
                rs.getString("report")
            } else {
                log.warn("There is no daily report for the date: $date")
                null
            }
        })
    }

If you need to check if a row exists in a table, here is a good solution. In this case, all records in the table are unique:

        fun checkReportExists(date: LocalDate): Boolean {
        val sql = """select 1 from reports_table where report_date = :date"""
        val map = MapSqlParameterSource("date", date)
        return jdbcTemplate.query(sql, map, ResultSetExtractor { rs -> rs.next() })!!
    }

Second solution:

        fun checkReportExists(date: LocalDate): Boolean {
        val sql = """select count(1) from reports_table where report_date = :date"""
        val map = MapSqlParameterSource("date", date)
        return jdbcTemplate.queryForObject(sql, map, Int::class.java)!! > 0

Last one solution:

        fun checkReportExists(date: LocalDate): Boolean {
        val sql = """select exists(select 1 from reports_table where report_date = :date)"""
        val map = MapSqlParameterSource("date", date)
        return jdbcTemplate.queryForObject(sql, map, Boolean::class.java)!!

P.S. Last solution is the fastest for checking if row exists.

gearbase
  • 21
  • 3