File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Check if record exists in database Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Check if record exists in database" Watch "Check if record exists in database" New topic

Check if record exists in database

Josh Hughes

Joined: May 25, 2011
Posts: 1
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.
Tim McGuire
Ranch Hand

Joined: Apr 30, 2003
Posts: 820

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.
Mike Zal
Ranch Hand

Joined: May 04, 2011
Posts: 144

Here is an Example of using JDBC

That will show you how to create a statement, execute a query, and then retrieve the value from the ResultSet.

chris webster

Joined: Mar 01, 2009
Posts: 2289

Just one tip:

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.

No more Blub for me, thank you, Vicar.
I agree. Here's the link:
subject: Check if record exists in database
It's not a secret anymore!