Java: MySQL Query and return

Question

I'm trying to access my database, inject some SQL Code and return the value out of it.

First of all, I'm a new to this stuff but I've came up with the following code:

  public static ResultSet checkCmdAmount() throws Exception {
        try {
          // This will load the MySQL driver, each DB has its own driver
          Class.forName("com.mysql.jdbc.Driver");
          // Setup the connection with the DB
          connect = DriverManager.getConnection(""+MyBot.mysqlDbPath+"",""+MyBot.mysqlDbUsername+"",""+MyBot.mysqlDbPassword+"");
          PreparedStatement zpst=null;
          ResultSet zrs=null;
          zpst=connect.prepareStatement("SELECT COUNT(1) FROM eigenebenutzerbefehle");
          zrs=zpst.executeQuery();
             return zrs;
        }catch (Exception e) {
              throw e;
            } finally {
              close();
            }
      }

In my return, I get the following:

ResultSet: com.mysql.jdbc.JDBC4ResultSet@196da649

But I want actually the Amount of rows in my table. When I execute the sql code through phpmyadmin I get 3 which is correct.

What is wrong here?

You shouldn't include the answer in your question. There are answers to your question and if you feel that some answer fixed the problem for you, accept that answer instead of incorporating that in your question :) — Rahul
– Rahul, Commented Apr 30, 2014 at 12:28

Rafa Paez · Accepted Answer · 2014-03-23 02:30:44Z

2

You need to read and get the desired values from the ResultSet. Do it as below:

public static int checkCmdAmount() throws Exception {
  // ...

  int count = 0;
  while (zrs.next()) {
    // Get the values from the current row...
    count = zrs.getInt(1);
  }
  return count;
}

edited Mar 23, 2014 at 2:30

answered Mar 23, 2014 at 2:23

Rafa Paez

4,87023 silver badges35 bronze badges

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

2 Comments

Federico Nafria Over a year ago

In this case String count = zrs.getString(1);

user3220962 Over a year ago

oh thats some seconds to late, i changed the return type to int and added if(zrs.next()){ return zrs.getInt(1); } which works now also, but thanks! :D

Ankur Kumawat · Accepted Answer · 2014-03-24 02:05:09Z

A ResultSet object contains all rows returned by executing an SQL query using a PreparedStatment or Statement from a database.

So when you executed

ResultSet zrs=null;
zpst=connect.prepareStatement("SELECT COUNT(1) FROM eigenebenutzerbefehle");
zrs=zpst.executeQuery();
return zrs;

as you said your SQL query will return number of rows, and that information is stored in ResultSet object zrs, but a ResultSet object's job is to store all the rows containing values from all columns specified or all rows in case of using *.
And when you are returning zrs you are returning a ResultSet object, and when you try and print an object what you get is the default value for an object's toString() conversion, which is in most cases objects types fully qualified name + a few extra characters.

And your updated code executes

if(zrs.next()){
    return zrs.getInt(1);
}
else{
    return -1;
}

here zrs.next() call moves the zrs to valid next record(or row) from where values can be retrieved, it also returns true or false depending upon the presence of record. In your example if you add one more call to zrs.next() then it would be returning false. zrs.getInt(1) will return the value in the row the zrs pointing to and the value of the first column, it has in that row, which is in your case only column.

Collectives™ on Stack Overflow

Java: MySQL Query and return

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related