java mysql count number of rows

Question

I created this code to allow me calculate the number of rows in my table. However, I'm not able to return the counted number with an error saying "cannot return a value from method whose result type is void." Could someone show me where' my error? Thanks alot!

public void num() 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("jdbc:mysql://localhost/testdb?"
      + "user=root&password=");

      // Statements allow to issue SQL queries to the database
      statement = connect.createStatement();
      resultSet = statement.executeQuery("select * from testdb.emg");
      int count = 0;
      while (resultSet.next()) {
        count++;
      }  
      return count;
  } catch (Exception e) {
  }

Just as a side note: When you just want to count the number of rows, a query like SELECT COUNT(*) FROM testdb.emg is way more efficient — klaustopher
– klaustopher, Commented Jan 22, 2012 at 11:17

Fahim Parkar · Accepted Answer · 2012-12-15 06:21:41Z

19

Try below code

 public int num() 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("jdbc:mysql://localhost/testdb?"
 + "user=root&password=");

 // Statements allow to issue SQL queries to the database
 statement = connect.createStatement();
 resultSet = statement.executeQuery("select count(*) from testdb.emg");

 while (resultSet.next()) {
 return resultSet.getInt(1);
 }
} catch (Exception e) {
}

Below were error

public void num() throws Exception {

should be

public int num() throws Exception {
For counting total rows you should use query select count(*) from testdb.emg

Let me know incase of any problem.

edited Dec 15, 2012 at 6:21

answered Jan 22, 2012 at 11:21

Fahim Parkar

31.8k46 gold badges168 silver badges285 bronze badges

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

Comments

Marcus · Accepted Answer · 2014-04-01 21:39:33Z

5

How to get count(*) mysql data table in java. TRY IT:

   public int getRowNumber(){

   int numberRow = 0;
   Connection mysqlConn = DriverManager.getConnection(HOST, USER_ID, PASSWORD);

try{
    mysqlConn.getConnection();
    String query = "select count(*) from dataTable";
    PreparedStatement st = mysqlConn.preparedStatement(query);
    ResultSet rs = st.executeQuery();
    while(rs.next()){
        numberRow = rs.getInt("count(*)");
    }
}catch (Exception ex){
    System.out.println(ex.getMessage());
}
return numberRow;
}

edited Apr 1, 2014 at 21:39

answered Mar 30, 2014 at 15:43

Marcus

7939 silver badges11 bronze badges

Comments

Adam Zalcman · Accepted Answer · 2012-01-22 11:27:32Z

4

Change

public void num() throws Exception {

to

public int num() throws Exception {

You are returning value from variable count which is of type int therefore the return type of the method should be int as well.

You should also make sure there is a return statement in every execution path through your code including the exception handler in the catch blocks (or you will get a "missing return statement" error message). However, it is best to avoid catch statements which catch all exceptions (like yours). Also, ignoring (i.e. not handling) exceptions in the catch block often leads to hard to diagnose problems and is a bad practice.

There are also other problems with the code: with the exception of count none of your variables have been declared.

Note that you may use the following SQL statement to obtain the number of rows directly:

select count(*) from testdb.emg

This avoids sending all of the data from table testdb.emg to your application and is much faster for big tables.

edited Jan 22, 2012 at 11:27

answered Jan 22, 2012 at 11:15

Adam Zalcman

27.3k4 gold badges75 silver badges95 bronze badges

2 Comments

Eugene Over a year ago

I have change it, however, there's this message "missing return statement." I did put the return statement "return count;"

Adam Zalcman Over a year ago

I've added explanation for this: you're not returning anything from the catch block.

rauschen · Accepted Answer · 2012-01-22 11:16:13Z

0

public void num() throws Exception {

should be

public int num() throws Exception {

answered Jan 22, 2012 at 11:16

rauschen

4,0062 gold badges15 silver badges13 bronze badges

2 Comments

Eugene Over a year ago

I have change it, however, there's this message "missing return statement." I did put the return statement "return count;"

rauschen Over a year ago

you have no return if an Exception is raised .. you can put a return statement in the Exception or put on under the Exception. Maybe you would use 0 or -1 in this case as return value

Mobin E · Accepted Answer · 2020-04-01 08:02:32Z

0

I use Fahim Parker answer with a bit change

`

public int num() throws Exception {
 try {
 Class.forName("com.mysql.jdbc.Driver");
 connect = DriverManager.getConnection("jdbc:mysql://localhost/testdb?"
 + "user=root&password=");

 statement = connect.createStatement();
 resultSet = statement.executeQuery("<your query statement>");

 resultSet.last(); //go to last row;
 return resultSet.getRow(); //get row number which is equal to rows count

} catch (Exception e) {
}

`

answered Apr 1, 2020 at 8:02

Mobin E

1

Collectives™ on Stack Overflow

java mysql count number of rows

5 Answers 5

Comments

Comments

2 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related