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.

Woolfell

Answer 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

Berton

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?? – Woolfell

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 – Berton

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

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. – Benthamism

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

Answer 2

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

Gondola

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? – Gallo

Answer 3

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

Mohammed

Answer 4

Let me combine several answers by Anton Yuriev, Reginaldo Santos, Angelo Immediata and give a short solution of this problem with Kotlin.
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.

Activism