JDBCTemplate find if row exists

Question

I am curious as to how I should use springs jdbctemplate class to determine if a record or row exists already in one of my tables?? I have tried

int count = jdbcTemplate.queryForObject("select * from MyTable
                                  where Param = ?", new Object[] {myParam},
                                  Integer.class);
if(count ==0)
    //record does not exist

The issue is though I keep getting either EmptyResultAccessDataException's, when it doesn't exist so I updated the code to

try{
    jdbcTemplate.queryForObject("select * from MyTable
                                  where Param = ?", new Object[] {myParam},
                                  Integer.class);
} catch(EmptyResultAccessDataException e) {//insert the record}

which then gives me issues if the record does exist. So I guess my real question is what is the best method to search for a records existence in a table as I want to add said record if it doesn't and do nothing if it does.

Solution 1

You may use something like this:

String sql = "SELECT count(*) FROM MyTable WHERE Param = ?";
boolean exists = false;
int count = getJdbcTemplate().queryForObject(sql, new Object[] { "paramValue" }, Integer.class);
exists = count > 0;

Angelo

Solution 2

Using query methods from JdbcTemplate is way better for this situation, because they allow zero rows to be returned (no EmptyResultDataAccessException):

boolean hasRecord =
  jdbcTemplate
    .query("select 1 from MyTable where Param = ?",
      new Object[] { myParam },
      (ResultSet rs) -> {
        if (rs.next()) {
          return true;
        }
        return false;
      }
    );

Solution 3

If database supports exists (like Postgres for example), it is better to use it:

String query = "SELECT EXISTS(SELECT * FROM table_name WHERE ...)";
boolean exists = jdbcTemplate.queryForObject(query, params, Boolean.class);

Fastest check if row exists in PostgreSQL

Comments

[j-money]

I'm curious, this looks very similar to my first attempt, I had a boolean flag as well. The only major difference I see is in your sql statement. I'm in no way a sql expert I have maybe a functional understanding of it. What is the count(*) doing??

[Angelo Immediata]

The count(*) statement is the SQL way to count records. In your example you are trying to retrieve all records matching your criteria. So in your case you are not counting but retrievieng and you should use queryForList method. If you are interested in just counting the records you need to use the count(*) statement. Something about the count (and other) statement can be found here w3schools.com/sql/sql_count_avg_sum.asp

[cнŝdk]

Nice approach, just instead of making a boolean flag, you can just test over count: if(count>0).

[AndrewF]

You don't need to count rows if you're just looking for existence. SELECT 1 FROM table WHERE email = ? LIMIT 1 will do less work. It will return one row early or zero rows if none matched.

[Stunner]

@AngeloImmediata This is bad. Why should I hit the DB twice if I have to do it only for once?

[Stunner]

While I appreciate your efforts to answer a question but your solution is not generic.. Many people use oracle , pirace , miracle etc.. what about them?