Proper way to search rowcount in Java JDBC

Question

I tried different ways to get the number of rows in java JDBC, the nut seems to be giving the correct result. Is something wrong, what am I doing?

Even though the client table is empty and I have to get the row value as 0, I don’t understand why I get a non-zero rowcount value.

Method 1 -

query = "SELECT * FROM customer WHERE username ='"+username+"'"; rs = stmt.executeQuery(query); ResultSetMetaData metaData = rs.getMetaData(); rowcount = metaData.getColumnCount(); 

Method 2 -

 query = "SELECT * FROM customer WHERE username ='"+username+"'"; rs = stmt.executeQuery(query); rowcount = rs.last() ? rs.getRow() : 0; 

Answer 1

See this code snippet:

 import java.io.*; import java.sql.*; public class CountRows{ public static void main(String[] args) { System.out.println("Count number of rows in a specific table!"); Connection con = null; int count = 0; try { Class.forName("com.mysql.jdbc.Driver"); con = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctutorial","root","root"); try { Statement st = con.createStatement(); BufferedReader bf = new BufferedReader(new InputStreamReader(System.in)); System.out.println("Enter table name:"); String table = bf.readLine(); ResultSet res = st.executeQuery("SELECT COUNT(*) FROM "+table); while (res.next()){ count = res.getInt(1); } System.out.println("Number of row:"+count); } catch (SQLException s){ System.out.println("SQL statement is not executed!"); } } catch (Exception e){ e.printStackTrace(); } } } 

Answer 2

When you are working with JDBC that does not support TYPE_FORWARD_ONLY , use this method to get rowcount.

 Statement s = cd.createStatement(); ResultSet r = s.executeQuery("SELECT COUNT(*) AS rowcount FROM TableName"); r.next(); int count = r.getInt("rowcount"); r.close(); System.out.println("MyTable has " + count + " row(s)."); 

You can get the number of rows using the above method.

Thanks..

Answer 3

This is the way I use to count the number of rows in Java:

 String query = "SELECT * FROM yourtable"; Statement st = sql.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY ); ResultSet = st.executeQuery(query); int rows = 0 rs.last(); rows = rs.getRow(); rs.beforeFirst(); System.out.println("Your query have " + rows + " rows."; 

Hope this helps you.

Sincerely.

Answer 4

As the method name metaData.getColumnCount() total number of columns in the result set will be returned, but not the total number of rows (number).