Results 1 to 19 of 19

Thread: ResultSet size

  1. #1
    bugger is offline Senior Member
    Join Date
    Nov 2007
    Posts
    111
    Rep Power
    0

    Default ResultSet size

    I am quering a table in database and getting records in a ResultSet object. I want to know the number of records in ResultSet. How to do that?

    I want want to query the table foe te number of records:

    Java Code:
    select count(*) from tablename where ...

  2. #2
    felixtfelix is offline Member
    Join Date
    Dec 2007
    Location
    Mumbai, India
    Posts
    37
    Rep Power
    0

    Default

    Actually There is no direct way to get the no of rows in the resultset. I can suggest one way. Make the resultSet scrollable. Then

    rs.last

    rs.getRow() which will give you the no of the current row.

    Or

    you can make use of a counter in the while loop

    int recordCount =0;
    while(rs.next())
    {

    recordCount++;
    }

    This will do?

  3. #3
    anagani80 is offline Member
    Join Date
    Apr 2008
    Posts
    1
    Rep Power
    0

    Default venkat

    is thare any workaround to find resultset size?

  4. #4
    felixtfelix is offline Member
    Join Date
    Dec 2007
    Location
    Mumbai, India
    Posts
    37
    Rep Power
    0

    Default another way to find result set size

    hi,

    this is the another way to find the resultset size.

    select * from table_name select @@rowcount no_of_rows

    which will give two resultset

    first one is the actual data

    second one is the no of rows in the first resultset.

    eg.

    If the select query have three values

    Emp_no emp_name
    --------------------
    1 AA
    2 BB
    3 CC

    the above one is the first result set

    no_of_rows
    -----------
    3

  5. #5
    rico16135 is offline Member
    Join Date
    Apr 2008
    Posts
    28
    Rep Power
    0

    Default

    can't you get this the size from the ResultSet metadata? I've never done it, but I remotely remember reading something about this.

  6. #6
    felixtfelix is offline Member
    Join Date
    Dec 2007
    Location
    Mumbai, India
    Posts
    37
    Rep Power
    0

    Default

    Hi,
    Otherwise you can use the following code, as i said in the earlier post rs.getRow() will give the no of the row. so you can make the resultset scrollable and use the following code.

    Statement stmt = connection.createStatement(
    ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR _READ_ONLY);

    ResultSet resultSet = stmt.executeQuery("SELECT * FROM my_table");

    resultSet.last();
    int rowCount = resultSet.getRow();

    Will it help.

    Regards,
    Felix T

  7. #7
    fishtoprecords's Avatar
    fishtoprecords is offline Senior Member
    Join Date
    Jun 2008
    Posts
    571
    Rep Power
    7

    Default

    Quote Originally Posted by bugger View Post
    Java Code:
    select count(*) from tablename where ...
    I do this all the time. What problem do you have?
    You have to use a relative resultSet.getInteger(0));
    to pull the value out, since "count(*)" is not named, but it works great.

    Its bad form to get all the individual records (as others have suggested) when all you want is the count(*) value

    You can do this for other functions, such as min(fieldX), max(), etc.

  8. #8
    felixtfelix is offline Member
    Join Date
    Dec 2007
    Location
    Mumbai, India
    Posts
    37
    Rep Power
    0

    Default

    hi,

    If u have some other fields say for example,

    select column1,column2,column3 from <tablename> where ...

    and if u add the count(*) with the above query then always remember that you have to add the other fields in the group by clause which will may make the data irrelevant than what you are looking for as the data may be get grouped.

    Regards

    Felix T

  9. #9
    fishtoprecords's Avatar
    fishtoprecords is offline Senior Member
    Join Date
    Jun 2008
    Posts
    571
    Rep Power
    7

    Default

    Quote Originally Posted by zlanhgn View Post
    这是新加的空白文章19,可以在UBB可视化编辑器中,添加和修改文章内容。
    I know we are international, but isn't there a rule someplace that posts are supposed to be in English, or at least something close to English?

  10. #10
    CaptainMorgan's Avatar
    CaptainMorgan is offline Moderator
    Join Date
    Dec 2007
    Location
    NewEngland, US
    Posts
    835
    Rep Power
    9

    Default

    User has been banned. Unless the topic was Chinese aerospace board meetings and article 19... Please report posts that are inappropriate - it is a tremendous help to us.
    Vote for the new slogan to our beloved Java Forums! (closes on September 4, 2008)
    Want to voice your opinion on your IDE/Editor of choice? Vote now!
    Got a little Capt'n in you? (drink responsibly)

  11. #11
    DevzAbhi's Avatar
    DevzAbhi is offline Member
    Join Date
    Jul 2008
    Posts
    67
    Rep Power
    0

    Default

    ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM TABLE2");


    rs.last();
    rs.getRow();

    I hope this helps....

  12. #12
    fishtoprecords's Avatar
    fishtoprecords is offline Senior Member
    Join Date
    Jun 2008
    Posts
    571
    Rep Power
    7

    Default

    Quote Originally Posted by felixtfelix View Post
    Actually There is no direct way to get the no of rows in the resultset.
    Which is good. The resultset is meta data, you do not want to know the number of rows in the result set itself. You want to know the number of rows in the query answer that is represented by the result set.

    Quote Originally Posted by felixtfelix View Post
    I can suggest one way. Make the resultSet scrollable. Then
    Java Code:
    rs.last
    rs.getRow() which will give you the no of the current row.

    No, No, This is insanely slow. Do a proper
    Java Code:
    select count(*) from foo where...
    and get the first column.

    Both of your suggestions will not scale, and will become ever slower as the number of rows increases.

    Additionally, to the OP, you very rarely need to know the number of rows returned. Doing the count(*) and then another select doubles the amount of work you are making the Sql engine do. You are nearly always better off putting more design time into the question.

    For example, just return the rows into an ArrayList which can dymanically grow as needed. If you might get millions of rows, put a limit clause on the select.

  13. #13
    MuthuKumar is offline Member
    Join Date
    Aug 2008
    Posts
    5
    Rep Power
    0

    Default

    select count(*) from table_name
    ResultSet rs = ps.executeQuery();
    int rowCount = rs.getInt(1);
    this will work

  14. #14
    nishant is offline Member
    Join Date
    Sep 2008
    Posts
    21
    Rep Power
    0

    Cool resultset

    can anybody tell me how should i use result set

  15. #15
    hungleon88 is offline Member
    Join Date
    Aug 2008
    Posts
    41
    Rep Power
    0

    Default

    All you have to do is:
    resultSet = stm.executeQuery("Select colum_name from table_name");

    int row_count = 0;
    while(resultSet.next())
    {
    row_count ++;
    }

    I'll will workwell !

  16. #16
    nishant is offline Member
    Join Date
    Sep 2008
    Posts
    21
    Rep Power
    0

    Default

    thanx....alot

  17. #17
    papabear is offline Member
    Join Date
    Dec 2009
    Posts
    1
    Rep Power
    0

    Unhappy resultset

    I have retrieved data from a database table using resultset and now I want to put the data of resultset into another table. How it can be done???

  18. #18
    kachwahed is offline Member
    Join Date
    Jun 2011
    Posts
    1
    Rep Power
    0

    Default

    @papabear
    you can use SQL instead
    "INSERT INTO someTable SELECT * FROM anotherTable"
    than execute the query using yourStatement.executeUpdate(sql);

  19. #19
    DarrylBurke's Avatar
    DarrylBurke is offline Forum Police
    Join Date
    Sep 2008
    Location
    Madgaon, Goa, India
    Posts
    11,458
    Rep Power
    20

    Default

    Enough is enough. kachwahed, your answer has no relevance to the question originally asked here some 3 years ago. And the person you replied to hasn't been here for more than two years.

    Locking.

    db

Similar Threads

  1. ResultSet to XML
    By Java Tip in forum Java Tip
    Replies: 0
    Last Post: 02-14-2008, 10:50 AM
  2. ResultSet to HTML
    By Java Tip in forum Java Tip
    Replies: 0
    Last Post: 02-12-2008, 10:32 AM
  3. ResultSet to JTable
    By Java Tip in forum Java Tip
    Replies: 0
    Last Post: 02-11-2008, 10:01 AM
  4. Empty ResultSet
    By Java Tip in forum Java Tip
    Replies: 0
    Last Post: 02-09-2008, 09:36 PM
  5. ResultSet example
    By Java Tip in forum Java Tip
    Replies: 0
    Last Post: 01-20-2008, 09:59 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •