Who dares, wins! (SAS motto)
Masoud Kalali
Software Engineer - My Weblog - GlassFish Security
Who dares, wins! (SAS motto)
Originally posted by kwame Iwegbue:
thanks guys. but thats so odd that there's no clear method for doing this like getColumnCount() ?!
Originally posted by George Stoianov:
As there is no method... that I know of in java to return the record count and looping through all recs to get a count is not very efficient.
You may create a method that executes a
and returns the result as a java long in java that way you can get counts for any table they will run in the db making them faster and improving the performance of your application.
Please note that in some databases it is advisable to put the primary key in the count function.
Hope this helps,
george
Originally posted by R. M. Menon:
In many cases, getting counts of records can be avoided altogether...
Originally posted by stu derby:
Yes, agreed. However, all the JDBC forums I've ever looked at have people clamoring for the best way to do just that.
A very common problem is where they have a web system and want to display a "page" of results; maybe rows 31 through 40, and they have a business requirement to display "Page n of m" along with the data.
R. M. Menon, how would you do it, generally?
Originally posted by Menon:
"R. M. Menon" sounds very formal - Menon is good enough I go by my last name everywhere. I just changed the display name in my profile - so hopefully for future posts, it would just be Menon.
[OCP 17 book] | [OCP 11 book] | [OCA 8 book] [OCP 8 book] [Practice tests book] [Blog] [JavaRanch FAQ] [How To Ask Questions] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
Originally posted by Jeanne Boyarsky:
Unfortunately, "Menon" doesn't follow our naming policy. A first name (or initials) and last name is required. Can you change it back?
It is ok to sign your posts with Menon as you have been doing, it's just the display name that needs it.
Originally posted by George Stoianov:
Hi Menon,
Can you please eloborate on why count(*) would not return the correct results in Oracle or point to a place to find some explanation.
Thinking about the record count I think it is kind of interesting how one has to approach this, are there examples of this in your book?
In Java you have to abstract and organize the same operations in one single location and reuse them to have good OO and code reuse, but it seems taking into account how Oracle works and the cost you end paying on the database side makes this impossible.
I will give an example so you can comment on that. Let us say I make a method that accepts a connection and a table name in the method I issue a count(*) and retrieve the number of rows which the method returns to my calling method. This in my mind is good OO and reusable functionality that fits well in any program. How would you rework that taking into account the imperfect results from count(*)?
I do a lot of web development and usually a record count is printed when displaying a table yes I can keep a counter and append the count to the string holding the table in a place where it appears on top of the output or bottom whatever, but that means I have to duplicate this code for every loop for every table, that is not good OO and leads to problems. If I later find a better way I then need to go and change my code in all of those places and possibly introduce new problems.
Thanks in advance,
George
Originally posted by George Stoianov:
In Java you have to abstract and organize the same operations in one single location and reuse them to have good OO and code reuse, but it seems taking into account how Oracle works and the cost you end paying on the database side makes this impossible.
There is no emoticon for what I am feeling!
In Oracle, when you do a select - the results are fixed as of the point of time (roughly speaking) your select started(in the default transaction isolation level). Even if you insert and commit records during the select run, it would still show the same result as was true at the point it ran (in the default transaction isolation level). I cover these concepts in detail in my book in a separate chapter on Transactions.
If it is a genuine requirement, you could write a generic procedure to count from a given table but that does not solve the original problem which was for any given query results (remember you can have joins.)
You ridiculous clown, did you think you could get away with it? This is my favorite tiny ad!
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
|