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

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.开发者_Python百科 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;
}

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

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.

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.

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() ....