Path: csiph.com!eternal-september.org!feeder.eternal-september.org!news.eternal-september.org!.POSTED!not-for-mail From: Rhino Newsgroups: comp.lang.java.databases Subject: Re: Getting the size of a result set Date: Sun, 16 Oct 2016 21:49:39 -0400 Organization: A noiseless patient Spider Lines: 77 Message-ID: References: <3AA7BB0C.3E29B600@sea.ericsson.se> <66367974-130d-4a9d-a06a-db1611553af2@googlegroups.com> Mime-Version: 1.0 Content-Type: text/plain; charset=windows-1252; format=flowed Content-Transfer-Encoding: 8bit Injection-Date: Mon, 17 Oct 2016 01:49:19 -0000 (UTC) Injection-Info: mx02.eternal-september.org; posting-host="5d044bd40b7e7c38d950e4533878730a"; logging-data="11454"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX18e4CM23rPx4LdQvC+EiM/oI5x9ZPtu5lk=" User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:45.0) Gecko/20100101 Thunderbird/45.4.0 In-Reply-To: <66367974-130d-4a9d-a06a-db1611553af2@googlegroups.com> Cancel-Lock: sha1:GzqTs5iIq/oHPCsPXnjD1ztoMBU= Xref: csiph.com comp.lang.java.databases:742 On 2016-02-15 12:15 PM, jagilberte@gmail.com wrote: > El jueves, 8 de marzo de 2001, 18:02:04 (UTC+1), Valentin Todorov escribió: >> Try this: >> >> long getCount(Connection con, String table){ >> Statement stmt = null; >> ResultSet rs = null; >> int count = 0; >> try{ >> stmt = con.createStatement(); >> rs = stmt.executeQuery("SELECT COUNT(*) FROM "+table); >> if(rs.next()) >> count = rs.getInt(1); >> }catch(SQLException e) { >> // error handling >> }finally{ >> // close rs &stmt >> } >> } >> >> HTH >> Valentin >> >> >> Jon Skeet schrieb: >> >>> Hi everyone, >>> >>> I'm not new to Java, but I'm extremely new to JDBC, SQL, and the whole >>> database "thang". I've been tasked with writing a class to implement our >>> own data interface, but accessing a JDBC database. One of the methods I >>> have to implement is (on a ResultSet-type object) getCount() which >>> should return the number of rows in the results. >>> >>> I've been trying to figure out a way of doing this using JDBC, and I >>> haven't got very far yet. As this is only a prototype at the moment, I'm >>> probably okay to use any standard JDBC extensions etc if necessary, >>> although obviously I'd like to keep it as simple as possible. I'd hoped >>> that ResultSetMetaData would have something in it, or that I could >>> specify an OUT parameter in a PreparedStatement which could contain the >>> count. (I realise I can use OUT parameters with a CallableStatement, but >>> I can't force our users to add appropriate CallableStatements, >>> unfortunately.) I can get the count if I don't specify anything else, >>> but I really need the rest of the data as well... >>> >>> I realise this may be an impossible mission, but I thought it would be >>> worth asking. >>> >>> -- >>> Jon Skeet - skeet@pobox.com >>> http://www.pobox.com/~skeet > > Hi, if you want to make a count of rows is better always use COUNT(1) than > COUNT(*). Because the * implies project all the atributes and the 1 avoid project the attributes. > I realize this is months late but, in case anyone reads this later, I would add a caution about this technique. While this technique would get you the count of the rows in the table at the point in time where you executed the query, that count *could* change at any time unless the table was locked against updates. Therefore, if you have in mind some kind of processing where you first get a count of rows and then do an insert, update or delete operation that depends on that row count being accurate, remember that it will only be accurate if no inserts, updates or deletes have taken place between the time you counted the rows and the time you do the updates/deletes/inserts. In my view, it's far better to write the logic using cursors where you build a result set that gets all the rows that qualify to be updated or deleted, regardless of the number, and then, while the rows are still locked by the cursor, do the updates or deletes (or, conceivably inserts). The cursor ensures that the rows you want to work with are locked so they can't change between the time you read them and the time you change them. -- Rhino