How to get the number of rows of ResultSet object?
It's very strange that we don't have any direct way to get the number of rows of a ResultSet object!
It is strange that there is no direct way to get the amount number of rows of records, when you programming with Java and MySql. For example, now I get one result from one Sql quering, I can use this way to get the number of records:
.............................
Connection con = DriverManager.getConnection(url, username, password);
Statement stmt = this.con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM testtable");
int count = 0;
while(rs.next()) {
count++;
}
System.out.println("The number of records are " + count)
But this seems not be one smart idea, because it will read through all the records rows, but you just need the amount number. And I found one better way to do that.
rs.last();
int count= rs.getRow();
This way will jump to the last record and get the current row number. What ever you use which method, if you want to use the ResultSet again, DO NOT forget to use beforeFirst() before you use it -- it will jump back to the position before the first record. Notice that first() method will just jump to the position of the first record. For example, if you have only one record, run the code below, the count will be zero, because rs.next() has no record:
rs.first(); //has 1 record
while(rs.next()) {
count++;// will be 0
}
This code is right, it will avoid that you missed the first record when you go on process with the records:
rs.beforeFirst(); //has 1 record
while(rs.next()) {
count++;// will be 1
}
So the right code here:
.............................
Connection con = DriverManager.getConnection(url, username, password);
Statement stmt = this.con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM testtable");
rs.last();
int count= rs.getRow();
rs.beforeFirst();
//do something with rs
................................
How to get the columns number in one row?? Here is one good example:
ResultSet rs = stmt.executeQuery("select version();");
int columns = rs.getMetaData().getColumnCount();
String version = "";
while (rs.next()) {
for (int i = 1; i <= columns; i++) {
String tem = rs.getString(i);
if(!tem.trim().equalsIgnoreCase(""))
version = version + "." + tem;
}
}
CommonModel.debug("MySQL database current version is: "+ version);
getMetaData().getColumnCount() will do that job. Clear??
Get Column Count using ResultSet in SQL
import java.sql.*;public class GetColumnCount { public static void main(String[] args) throws Exception { String driver = "com.mysql.jdbc.Driver"; String url = "jdbc:mysql://localhost:3306/"; String username = "vineej"; String password = "vineej"; String dbName= "no"; Class.forName(driver); Connection conn = DriverManager.getConnection(url+dbName, username, password); System.out.println("Connected"); Statement st = conn.createStatement(); ResultSet rs = st.executeQuery("SELECT * FROM Student"); ResultSetMetaData rsmd = rs.getMetaData(); int NumOfCol=rsmd.getColumnCount(); System.out.println("Number of Columns="+NumOfCol); st.close(); conn.close(); }}
