How can I check if a record exist in Oracle database with Java?

Question

I am trying to check if a record exist in a database and I am not sure how to execute this query, It gives error with executeQuery(checkSql). Can you help me, please?

public void addScore(int score, String name)
{
    try {
        con = mgr.getConnection();

        String checkSql = "if EXISTS (select * from HIGHSCORES where name = '"+name+"')";

        Statement st = con.createStatement();
        //ResultSet result = st.executeQuery(checkSql);



        } catch (SQLException e) {
        e.printStackTrace();
    }
    mgr.closeConnection(con);
}

What error does it give? In general, this helps you get a more useful answer. — Jeanne Boyarsky
– Jeanne Boyarsky, Commented May 5, 2013 at 18:57

Jeanne Boyarsky · Accepted Answer · 2013-05-05 19:05:50Z

3

String checkSql = "select count(*) from HIGHSCORES where name = '"+name+"'";

Statement st = con.createStatement();
ResultSet result = st.executeQuery(checkSql);
result.next();
if ( result.getInt(1) == 0) {
  System.out.println("doesn't exist");
} else {
  System.out.println("exists");
}

To make this better, you'd switch to a PreparedStatement which prevents SQL injection

edited May 5, 2013 at 19:05

answered May 5, 2013 at 18:57

Jeanne Boyarsky

12.3k2 gold badges52 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Figen Güngör Over a year ago

I tried this, but now it gives "ORA-00933: SQL command not properly ended" error.

Jeanne Boyarsky Over a year ago

Try again. I missed removing the trailing paren.

Ben Over a year ago

If you add a where rownum < 2 to the query it could be a lot more efficient. You then don't scan the full table but only one record. This code is also wide open to SQL injection from what I can see...

Jeanne Boyarsky Over a year ago

Ideally it would scan a full index not a full table, but your point still applies. However rownum doesn't run on all databases as far as I know. (I remember limit and top.) I try not to use db specific code unless I have to. We don't know there is a performance problem here. There probably isn't since it is a learning example. Also, I already mentioned SQL Injection in my answer. I didn't fix it so the mapping between "before" and "after" code would be clearer.

Sk8erPeter Over a year ago

"To make this better, you'd switch to a PreparedStatement which prevents SQL injection" - yes, that's true, why do you show a bad example - which should be avoided - then? :)

|

greedybuddha · Accepted Answer · 2013-05-05 19:04:13Z

3

Strangely, for many db's exists can only be used as a conditional , not as a result itself. As an example, the following is valid

select * from some_table where exists (select * from HIGHSCORES where name = '"+name+"')

But this is invalid for many systems.

exists (select * from HIGHSCORES where name = '"+name+"')

If this happens though you can just get whether a record exists with the following

select count(*) from HIGHSCORES where name='"+name+"' limit 1

this will get you the count, and will stop after finding at least one record, a small optimization.

answered May 5, 2013 at 19:04

greedybuddha

7,5073 gold badges38 silver badges50 bronze badges

4 Comments

Sk8erPeter Over a year ago

You should definitely use PreparedStatement instead of this solution and avoid concatenating the query string.

greedybuddha Over a year ago

This answer was attempting to be nearly all SQL syntax and to use the op's own code for clarity. If you are using Java then yes PreparedStatements are better but that was not the question asked.

Sk8erPeter Over a year ago

The question was how to do the whole thing in JDBC - which of course includes how to write an appropriate query for the task. So yes, you should use PreparedStatement. AND yes, you should use prepared statements everywhere if it's possible (in every other languages+libraries too). So I think everyone should avoid showing bad examples. :)

greedybuddha Over a year ago

The queries I'm presenting work and are clear SQL examples that show how to approach the problem in a language independent manner(more or less, easily adaptable). You can clarify the answer with your comment to use a PreparedStatement when using specific languages if you would like, but it's not a bad example and is distinct from the accepted answer.

Collectives™ on Stack Overflow

How can I check if a record exist in Oracle database with Java?

2 Answers 2

6 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related