Put a MySQL query into an if Statement in Java-CodePudding

I'm having the below issue. I already have the database in MySQL and now I want to let the user choose the data, if it exists. The program runs to the "true" case and the "false" case for the rest. I put a MySQL query into an if Statement in Java, but it causes a problem when I put it in. No matter what I choose the data, it always returns to "true" and does not run into the "false" case. When I try to use .isNullOrEmpty it always returns to "false". Any other solution in this situation?

Here is my code

try {
    Connection conn = null;
    conn = DriverManager.getConnection(DB_URL, USER_NAME, PASSWORD);
    Statement stmt = null;
    stmt = conn.createStatement();
    String MSV = scanner.nextLine().toUpperCase();
    String COUNT = "SELECT COUNT(MSV) FROM project.student where MSV = '" MSV "';";
    stmt.executeQuery(COUNT);               
    ResultSet resultSet = stmt.executeQuery(COUNT);                             
    if (stmt.executeQuery(COUNT) != null)  {                    
        System.out.println("true");
     } else {                   
        System.out.println("false");
     } conn.close();                
} catch (SQLException e) {
    e.printStackTrace();
}

CodePudding user response：

ResultSet resultSet = stmt.executeQuery(COUNT);                             
           if (stmt.executeQuery(COUNT) != null)  {

This runs the executeQuery method, which returns a resultSet. You store this resultset in a local variable named resultSet and then proceed to do nothing with it. That first line (ResultSet resultSet = stmt.executeQuery(COUNT);) doesn't do anything useful - worse, it creates a resource that you don't close.

You then run the executeQuery method a second time, and check if it returns null. It never will. It always returns a resultset.

The resultset is a 'cursor' - a tool that you can use to traverse the excelsheet. Because all queries on a database always return a result set - a thing with rows and columns (or, if you prefer SQL style thinking, a query itself returns a table: A thing with rows and columns).

This is a peculiar excel sheet - it has just one column (namely: COUNT(MSV)) and given that you're aggregating over the entire result, it has just the one row. You may therefore think: Nono, this query doesn't return an excel sheet, it returns a single result.

No, it doesn't - the way the ResultSet API works is that you always get a result set. Perhaps with just one cell filled in (one column, one row), but always 'an excel sheet'.

Therefore, you must [A] forward this 'cursor' to the next (first) row, given that they always start before the first row, and then [B] invoke the right get method to obtain the contents of the cell, and then [C] presumably, check if that value is 0 or not.

Thus, instead of the above quoted snippet, you want instead:

int value;
try (var resultSet = stmt.executeQuery(COUNT)) {
  if (!resultSet.next()) {
    value = 0; // no row somehow
  } else {
    value = resultSet.getInt(1); // get first (and only) column value
  }
}

if (value != 0) {

Yeah that looks a bit unwieldy. JDBC isn't meant to be nice to use. It's a low level API. You don't program in assembler either. Consider using an abstraction built on top of JDBC that makes it nice to use, such as JDBI or JOOQ.

CodePudding user response：

count wont give result of null even though query result is null/nothing. The count will return 0. Trying adding 0 to your if condition, it might work :)

String COUNT = "SELECT COUNT(MSV) as count_name FROM project.student where MSV = '" MSV "';";
stmt.executeQuery(COUNT);               
ResultSet resultSet = stmt.executeQuery(COUNT);                         
if (resultSet.next()) {
    int count= rs.getInt("count_name");
if (count > 0)  {                    
                System.out.println("true");
             } else {                   
                System.out.println("false");
             }
       }

CodePudding user response：

if condition should be this

if (stmt.executeQuery(COUNT) > 0) {
  //
}