Win a copy of Rust Web Development this week in the Other Languages forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Tim Cooke
  • Campbell Ritchie
  • Ron McLeod
  • Liutauras Vilda
  • Jeanne Boyarsky
Sheriffs:
  • Junilu Lacar
  • Rob Spoor
  • Paul Clapham
Saloon Keepers:
  • Tim Holloway
  • Tim Moores
  • Jesse Silverman
  • Stephan van Hulst
  • Carey Brown
Bartenders:
  • Al Hobbs
  • Piet Souris
  • Frits Walraven

How to get no of Rows ?

 
Ranch Hand
Posts: 231
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 !!
 
Ranch Hand
Posts: 333
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
 
omkar patkar
Ranch Hand
Posts: 231
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 333
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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:

and then done


AVG, SUM, etc...
 
omkar patkar
Ranch Hand
Posts: 231
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 ?
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic