How to check database from java if exists row with given criteria?

Question

I have table VIDEO (VideoID int Primary Key, Address Varchar(100)) and I want to search table to see if there is video with given address. But I am not sure if this code works good, and if this could be better done. Here is my code:

public boolean checkIfVideoExist(String address) throws SQLException {
    int count = 0;
    Statement stmt = connection.createStatement();
    ResultSet rset = stmt
            .executeQuery("SELECT Count(VideoID) from VIDEO WHERE Address='"
                    + address + "'");
    if (rset.next())
        count = rset.getInt(1);
    if (count == 0)
        return false;
    else
        return true;
}

Please use the code formatting button to format your code: {} — Kiril
– Kiril, Commented Aug 5, 2011 at 15:12
You're wide open to SQL injection, but otherwise there's not very much you can do to optimize things further. — Marc B
– Marc B, Commented Aug 5, 2011 at 15:15
@Lirik I tried to format code inserting 4 space characters but, this didn't change anything — Иван Бишевац
– Иван Бишевац, Commented Aug 5, 2011 at 15:20
@Marc B I know how to avoid SQL Injection when use INSERT INTO statement, but don't know hot to use it with SELECT — Иван Бишевац
– Иван Бишевац, Commented Aug 5, 2011 at 15:20
@Ivan Bishevac, when you use spaces instead of the code format button, you need to end the last sentence before the code with two spaces and a new line, then you can use 4 spaces when writing your code and it will appear formatted (or you could just select your code and click on the code format button). — Kiril
– Kiril, Commented Aug 5, 2011 at 15:24

vanje · Accepted Answer · 2011-08-05 18:05:36Z

3

Be sure you have an index set on column ADDRESS. Then your query should run fast.

It is better to use a prepared statement to pass the address value to the query. And you should close the result set and the statement.

And

if (count == 0)
  return false;
else
  return true;

looks a bit strange.

public boolean checkIfVideoExist(String address) throws SQLException {
  int count = 0;
  PreparedStatement stmt = null;
  ResultSet rset = null;
  try {
    stmt = connection.prepareStatement(
        "SELECT Count(VideoID) from VIDEO WHERE Address=?");
    stmt.setString(1, address);
    rset = stmt.executeQuery();
    if (rset.next())
      count = rset.getInt(1);
    return count > 0;
  } finally {
    if(rset != null) {
      try {
        rset.close();
      } catch(SQLException e) {
        e.printStackTrace();
      }
    }        
    if(stmt != null) {
      try {
        stmt.close();
      } catch(SQLException e) {
        e.printStackTrace();
      }
    }        
  }    
}

edited Aug 5, 2011 at 18:05

answered Aug 5, 2011 at 15:26

vanje

10.4k2 gold badges34 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Иван Бишевац Over a year ago

You have syntax error in code, it should be prepareStatement, not preparedStatement. Second problem is: rset = stmt.execute();, I suppose there should be rset = stmt.executeQuery();

vanje Over a year ago

Sorry, you are right. I updated the code. stmt.execute() can be used for INSERT statements, but for SELECT queries stmt.executeQuery() should be used.

JB Nizet · Accepted Answer · 2011-08-05 15:36:11Z

3

The code is fine, except for the way you're embedding strings in your query. If address contains a quote character, the query will become invalid. And this is only a small part of the problem. Doing it like this opens the door to SQL injection attacks, where malicious users could enter an address which completely changes the meaning of the query.

Always use prepared statements to bind parameters:

PreparedStatement stmt = connection.prepareStatement("SELECT Count(VideoID) from VIDEO WHERE Address=?");
stmt.setString(1, address); // proper escaping is done for you by the JDBC driver
ResultSet rset = stmt.executeQuery();

Also, you should use finally blocks to close your result sets and statements.

edited Aug 5, 2011 at 15:36

answered Aug 5, 2011 at 15:17

JB Nizet

694k94 gold badges1.3k silver badges1.3k bronze badges

1 Comment

Marc B Over a year ago

"embedding strings", there's no binding taking place and definitely no parameters.

Michael Borgwardt · Accepted Answer · 2011-08-05 15:17:19Z

2

Your code is vulnerable to SQL Injection, it should use a prepared statement instead of building the SQL query via string concatenation.

Apart from that, it looks OK.

answered Aug 5, 2011 at 15:17

Michael Borgwardt

347k81 gold badges491 silver badges726 bronze badges

Comments

PeterMmm · Accepted Answer · 2011-08-05 15:22:32Z

0

ResultSet rset = stmt.executeQuery("SELECT * from VIDEO WHERE Address='" + address + "'");
return rset.next();

then there is at least one matching record and you are done. No need for aggregate function count() ....

edited Aug 5, 2011 at 15:22

answered Aug 5, 2011 at 15:16

PeterMmm

24.7k15 gold badges77 silver badges115 bronze badges

5 Comments

Michael Borgwardt Over a year ago

I don't think this is true, he's doing a COUNT()

JB Nizet Over a year ago

No. select count will return 0. And comparing a boolean with true is bad practice. Simply using if (rset.next()) like the OP did is fine.

JB Nizet Over a year ago

For the posterity: this is really the worst way to do it: it will load a whole bunch of data just to initialize a boolean value. And it's open to SQL injection attacks.

Иван Бишевац Over a year ago

I agree. I did Count because of that, I think it's better for database because it's not fetching all records, just one row with one column.

PeterMmm Over a year ago

AFAIK there is no gurantee at all that count() performance better than select a priori. All depends on the concrete JDBC/RDBMS implementation.

Collectives™ on Stack Overflow

How to check database from java if exists row with given criteria?

4 Answers 4

2 Comments

1 Comment

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related