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.

Answer 1

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

Answer 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;
      }
    );

Answer 3

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