How to get no of Rows ?

[omkar patkar]

Hi Guys,
i am using postgresql 8.1 and using "postgresql8.1-405.jdbc.jar" i have written following code :-

package postGresConn;

import java.beans.Statement;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;


/**
* @author OMKAR
*
*/
public class DbConn {

/**
* @param args
*/
public static void main(String[] args) {

try {
System.out.println("Inside void main");
Class.forName("org.postgresql.Driver");
} catch (ClassNotFoundException cnfe) {
System.err.println("Couldn't find driver class:");
cnfe.printStackTrace();
}
String jdbcConnString = "jdbc:postgresql://localhost/arrkdb";
Connection c = null;

try
{
c = DriverManager.getConnection(jdbcConnString,"postgres","postgres");
java.sql.Statement s = c.createStatement();
ResultSet rs = s.executeQuery("SELECT * from user_master");

while(rs.next())
{
System.out.println(" "+rs.getString("um_user_first_name"));
}



rs.close();
s.close();
c.close();
}
catch(SQLException sqe)
{
System.out.println("Could NOt establish connection \n"+sqe.getStackTrace());
}


}

}

************************************************************
Now, How can i get result of query like :-
"SELECT count(*) from user_master" which returns no of rows ???

The return Type of "executeQuery()" is ResultSet and not "int" ...then how do i get no of rows ? ....Above query works perfectly fine when executed in Query Analyser of Postgres !
Please Some one help !!

Thanks in advance !!

[stu derby]

int count = 0;
 
String SQL = "SELECT count(*) from user_master";
PreparedStatement ps = connection.prepareStatement(SQL);
ResultSet rs = ps.executeQuery();
 
if (rs.next() )
{
  count = rs.getInt(1);
}

[omkar patkar]

Hi Stu,
Thanks a lot ! ...but i did not get --- Why "preparedStatement" ?
and ps.executeQuery(); also returns "ResultSet" then using rs.getInt(1) how can no of rows be obtained ? Also, if i want use any other aggregate function like "Average() or Sum()" ....where the result expected is integer, then should i always use ...PreparedStatement() ?

[stu derby]

Originally posted by omkar patkar:
Hi Stu,
Thanks a lot ! ...but i did not get --- Why "preparedStatement" ?
and ps.executeQuery(); also returns "ResultSet" then using rs.getInt(1) how can no of rows be obtained ? Also, if i want use any other aggregate function like "Average() or Sum()" ....where the result expected is integer, then should i always use ...PreparedStatement() ?

PreparedStatement - using Statement when there are data values in the SQL is a very bad thing. Even though there are no data values in this SQL, I always use PreparedStatement for all INSERT/UPDATE/DELETE/SELECT statements. It standardizes the code and I have to think less about JDBC and therefore can think more about the problem I'm trying to solve. Nothing to do with counting rows, just a "best practice".

getInt - select count(*) ... returns a ResultSet counting one row; rs.next() advances the ResultSet to that first (and only row) and getInt(1) gets the value produced by "count(*)". I could have written the SQL as:

String SQL = "SELECT count(*) AS UM_COUNT from user_master";

and then done

count = rs.getInt("UM_COUNT");

AVG, SUM, etc...

int count = 0;
int avg_login = 0;
int sum_login = 0;
 
String SQL = "SELECT count(*) AS UM_COUNT, sum(login_count) AS SUM_LOGIN, avg(login_count) AS AVG_LOGIN from user_master";
PreparedStatement ps = connection.prepareStatement(SQL);
ResultSet rs = ps.executeQuery();
 
if (rs.next() )
{
  count = rs.getInt("UM_COUNT");
  sum_login = rs.getInt("SUM_LOGIN");
  avg_login = rs.getInt("AVG_LOGIN");
}

[omkar patkar]

Thanks a lot Stu !
One more doubt ... the Database server is on another PC whose IP address is :- 10.0.2.21 and my IP address is :- 10.0.2.253 . So how do i connecet using JDBC?

Now both these PCs ( ...i.e., my client PC and the PC on which the postgres Database Server is hosted ) are in one network, and the external IP address of this entire network is :- 59.163.23.73
So if i want to access the database using JDBC from a PC external to this network ( ...i.e., 59.163.23.73 ) then how do i do it ?