return a boolean - jdbcTemplate

Question

I would like to return a boolean value using in this method:

public Boolean isSizeOk(String transactionId){ 
    String sqlQuery = "SELECT true FROM customer_pool WHERE id = "+ transactionID + " AND level = 13)";

//The next line is the problem. 
    //If I am returning a Boolean List, I can write

    List <Boolean> sizeResult = jdbcTemplate.queryForList(sqlQuery, Boolean.class, transactionId);

    //But since I only want a boolean value, what will the statement be?
     Boolean sizeResult = jdbcTemplate......?

    return sizeResult;
}

Kindly help. Thanks.

Thanks so much... your solutions are both right. I now don't know who to award the correct answer to :). I appreciate your help! — bdfios, Apr 04 '14 at 10:52

kostya · Accepted Answer · 2015-07-02T03:33:32.577

28

If you want to write a method that checks that a record exists in the database you can use the following code:

Integer cnt = jdbcTemplate.queryForObject(
    "SELECT count(*) FROM customer_pool WHERE id = ? AND level = 13)", Integer.class, id);
return cnt != null && cnt > 0

edited Jul 02 '15 at 03:33

answered Apr 04 '14 at 10:34

kostya

9,221
1
29
36

Ok this looks ok. But What will it return if the record does not exist? – bdfios Apr 04 '14 at 10:38
the query will return 0 and "cnt != null && cnt > 0" will return false – kostya Apr 04 '14 at 10:44
@orbfish, there was an error in my original answer (missing id argument). I've corrected the answer. Hopefully it is clear now. – kostya Jul 02 '15 at 03:34
4

Isn't cnt != null redundant as Count(*) will always return an integer? – Pepster Sep 16 '15 at 15:28
1

`count(*)` will read all matching records, add `limit 1` as an optimization so the DB can stop counting after it finds the first record. – LaFayette Sep 08 '19 at 06:59

score 8 · Answer 2 · answered Apr 15 '16 at 20:32

8

Counting rows in SQL just in order to get simple information about non-emptiness of result may be unnecessary overkill, you want just ask result set for first row and finish. For simple queries by primary key or other index the performance might be similar, however, for complex queries, or full table scan queries it might be slow. In Spring I prefer simple utility method

public boolean exists(String sql, Object... args) {
    boolean result = query(sql, args, new ResultSetExtractor<Boolean>() {
        @Override
        public Boolean extractData(ResultSet rs) throws SQLException,DataAccessException {
            boolean result = rs.next();
            return result;
        }
    });
    return result;
}

(Google "sql exists vs count" for more info.)

answered Apr 15 '16 at 20:32

Tomáš Záluský

10,735
2
36
64

4

I think it is mention worthy that in Java 8 this can be written as "return jdbcTemplate.query(searchQuery, args, ResultSet::next)" – David Maes Nov 21 '17 at 12:28
This should be marked as a correct answer, although, it must be `return result.getBoolean(1)` because `next()` returns if the new current row is valid — you still need to read the value in that row. – dehumanizer Jan 25 '20 at 01:00
@dehumanizer I don't fully understand your point. According to javadoc, _When a call to the next method returns false, the cursor is positioned after the last row._ So I think first call of `rs.next()` returns `true` on nonempty resultset, `false` on empty result set. This is independent on what columns are actually on result set. `getBoolean(1)` assumes first column of query of type `boolean` (or perhaps convertible to `boolean`), which (1) is too restrictive and (2) produces wrong result for instance for `select false` on Postgres. – Tomáš Záluský Jan 25 '20 at 17:15
@TomášZáluský, my point is if, for example, you do `select exists (...)` kind of query, then you will always get `true` even when `select exists` query would return false, because just `rs.next()` would just move the cursor. – dehumanizer Jan 25 '20 at 23:02
@dehumanizer The purpose of method is to check if result has rows, that's all. My goal was to not apply any restrictions on SQL passed in parameter, just assume it is returns some data, for example `select * from all_the_things`. If you wrap such query yourself, i.e. `select exists (select * from all_the_things)`, you actually move burden of evaluation onto SQL engine and query returns metadata instead of data, hence you don't need method in this form or you have to read `getBoolean(1)`. I'm uncertain if this approach makes things simpler, thinking of SQL performance and string concatenation. – Tomáš Záluský Jan 26 '20 at 01:03

score 6 · Answer 3 · answered Feb 14 '16 at 20:07

What about

// Change query accordingly
String query = "SELECT 1 FROM " + tableName + " WHERE " + idColumnName + " = ? LIMIT 1";
try {
    jdbcTemplate.queryForObject(query, new Object[]{id}, Long.class);
    return true;
} catch (EmptyResultDataAccessException e) {
    return false;
}

score 0 · Answer 4 · answered Apr 04 '14 at 10:31

0

Case 1: In case you are returning boolean: Just check the size of sizeResult List, if the size is greater than 0 return true else return false.

Case 2: If you are returning boolean list then return type must be a boolean List.You must write the method as:

public List<Boolean> isSizeOk(String transactionId, int sizeLimit){ 
String sqlQuery = "SELECT true FROM customer_pool WHERE id = ? AND level = 13)";


List <Boolean> sizeResult = jdbcTemplate.queryForList(sqlQuery, Boolean.class, transactionId);

 Boolean sizeResult = jdbcTemplate......?

return sizeResult;

}

answered Apr 04 '14 at 10:31

Akash Goswami

306
3
14

Thanks Akash. Yes, it actually works when I returned the value as a boolean list, but there is no reason returning it as a list since I know that the answer I want is either TRUE or FALSE. That is why I want a proper way to return it as boolean, not as boolean list. – bdfios Apr 04 '14 at 10:34
Then you can use the Case 1 solution. – Akash Goswami Apr 04 '14 at 10:39

return a boolean - jdbcTemplate

4 Answers4