How to hold MySQL query result in an int variable if possible

Question

I want to know whether a table exist or not before creating another one is there any way of holding result in a variable after execution of command, i am using this code but it keeps giving only true even if table doesn't exists.

public static boolean checkBefore(){
    boolean r = false;
    try{
        query = "SELECT COUNT(*)FROM information_schema.tables WHERE table_schema = 'sms' AND table_name = 'auth';";
        con = connectsms();
        st = con.createStatement();
        ResultSet rs = st.executeQuery(query);
        r = rs.next();
    }catch(SQLException e){
        JOptionPane.showMessageDialog(errorMsg,"Exeption Fount: "+e,"Opps! Exception Found in checkBefore()",JOptionPane.ERROR_MESSAGE); 
    }
    System.out.println(r);
    return r;
}

Your code does not show the logic for creating another table. What exactly do you want to achieve? Check if the statement ResultSet rs = st.executeQuery(query); returns anything? — dryleaf
– dryleaf, Commented Jan 19, 2020 at 23:50
i have not include code to create table first i want to create logic to check and return false if table does not exist. and that statement returns some string but its constant everytime i execute code — Shubham Bhardwaj
– Shubham Bhardwaj, Commented Jan 19, 2020 at 23:56
Did you check this select statement COUNT(*)FROM? add a space before FROM. — dryleaf
– dryleaf, Commented Jan 20, 2020 at 0:01

Andreas · Accepted Answer · 2020-01-20 00:38:00Z

Every JDBC guide will show you that after executing a query, you need to call next() to advance to the next/first row, then call getter methods to retrieve the column values of that row.

Queries with aggregating functions (COUNT, MIN, MAX, etc) without a GROUP BY clause will always return exactly one row, so for those kinds of queries, you don't need to check the return value from next(). For pretty much all other queries, you do.

When calling JDBC methods that return resources, you should use try-with-resources to make sure those resource are cleaned up correctly.

Query string does not need to end with a ; semi-colon.

All that means that your code should be:

public static boolean checkBefore() {
    String sql = "SELECT COUNT(*)" +
                  " FROM information_schema.tables" +
                 " WHERE table_schema = 'sms'" +
                   " AND table_name = 'auth'";
    try ( Connection con = connectsms();
          Statement st = con.createStatement();
          ResultSet rs = st.executeQuery(sql);
    ) {
        rs.next(); // exactly one row returned, so next() always returns true here
        int count = rs.getInt(1); // get value from first column
        System.out.println("count = " + count);
        return (count != 0);
    } catch (SQLException e) {
        JOptionPane.showMessageDialog(errorMsg, "Exeption Fount: " + e,
                                      "Opps! Exception Found in checkBefore()",
                                      JOptionPane.ERROR_MESSAGE);
        return 0;
    }
}

dryleaf · Accepted Answer · 2020-01-20 01:22:28Z

0

Change r = rs.next(); to rs.next(); and then add r = rs.getInt(1) > 0; and it will work.

edited Jan 20, 2020 at 1:22

answered Jan 20, 2020 at 0:08

dryleaf

4234 silver badges18 bronze badges

2 Comments

Andreas Over a year ago

You can't call rs.getInt(...) before calling rs.next()

Shubham Bhardwaj Over a year ago

Should i use this code to full fill my need ``` public static boolean checkBefore(){ boolean r = false; try{ query = "SELECT COUNT(*) FROM userdata"; con = connectsms(); st = con.createStatement(); ResultSet rs = st.executeQuery(query); return true; }catch(SQLException e){ return false; } } ```

Shubham Bhardwaj · Accepted Answer · 2020-01-22 00:07:51Z

This is the query that worked for me:

SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'databse_name';

and the code that i am using and is working correct:

public static boolean checkBefore(){
    boolean result = false;
    try{
        query = "SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'sms'";
        con = connectsms();
        st = con.createStatement();
        ResultSet rs = st.executeQuery(query);
        result = rs.next();
        System.out.println();
    }catch(SQLException e){
       JOptionPane.showMessageDialog(errorMsg,"Exeption Fount: "+e,"Opps! Exception Found in checkBefor()",JOptionPane.ERROR_MESSAGE); 
    }
    try{
        con.close();
        }
    catch(SQLException e){JOptionPane.showMessageDialog(errorMsg,"Exeption Fount: "+e,"unable to close connection",JOptionPane.ERROR_MESSAGE); }

    System.out.println(result);
   return result;
}

Collectives™ on Stack Overflow

How to hold MySQL query result in an int variable if possible

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related