I know a similar thread already exists, but it doesn't fully match my problem.
I esstentially want to run a count query which counts how many times a username appears in database (should be either 1 or 0). From there I'm wanting to perform a few more commands if the record exists. My problem lies with not knowing how to execute the count query and getting the result back as an integer. I'm kind of new to JDBC so I'm sorry if this is a silly question.
Josh Hughes wrote:
I esstentially want to run a count query which counts how many times a username appears in database (should be either 1 or 0).
so you have a SQL statement along the lines of
and you need help with the JDBC code to run it?
Do you have some java code where you try to execute this? If so, post the code and we can help you with it.
SELECT COUNT(*) FROM.... has to read the whole table (or at least all the index entries) to count how many records there are that match your query criteria.
If you really only need to know if at least one entry exists, you can do e.g. SELECT 1 FROM .... and make sure you just request one row at a time from the cursor. If you get 1 row, you know a matching record exists and you don't need to tell the database to look for any more rows. Depending on your database and Java middleware, the query may try to pre-fetch a number of rows, but you should be able to tell it to fetch just 1 at a time.
Of course, if no matching entry exists, then the SELECT will still have to read the whole table/index to find this out.
In a small table replacing SELECT COUNT(*)... with SELECT 1... won't make much difference to performance, as the Java/database communications will be taking up most of the elapsed time anyway. But on a big table, you really want to think carefully about how much data you have to read to get the results you want, so only count rows if you really need to know how many there are.
0
