ms access database with java

Question

I am creating a java program, with which I am editing into a Microsoft Access Database. I have a certain case, in which I need to search if a certain record already exists in my table, if it does, I want to update it, and if not, I want to create it from scratch.

I have found this piece of code:

IF EXISTS (SELECT * FROM USERS WHERE USERID=@UID) select 1 else select

This code gives me an error, saying that a SELECT, UPDATE or DELETE statement was expected.

In a code that I have tried my self, I have done the following:

try{
            s = con.createStatement();
            s.executeQuery("SELECT * FROM table WHERE date='" + today + "'");
            rset = s.getResultSet();    

            if (rset.getString("date") == null){

                s = con.createStatement();     
                s.executeUpdate("INSERT INTO table VALUES ('" + today + "','" + cflow + "','" + vat + "','" + cnn + "')");

            }
        }

        catch (SQLException exp)
        {
            System.err.println(exp);
        }

But with this code, when the record does not exist yet, the user input is not updated inside the database.

Thanks for your time :)

Reporter · Accepted Answer · 2011-07-26 20:53:29Z

1

1st: If I can remember right, then is

IF EXISTS (SELECT * FROM USERS WHERE USERID=@UID) select 1 else select

an incomplete transact sql statement -used by the sql engine from a database system.

2nd:

if (rset.getString("date") == null){}

you should avoid this way, because there is a good chance to get a Nullpointer Exception.

In my eyes a better one is a test the size of resultset for zero or the resultset it self for the value of NULL.

In case the UPDATE statement won't also be executed, check your SELECT statement using the database engine -Ms Access, SQL Server, etc.- directly. The advantage is you can exclude a mistake in your SELECT query.

answered Jul 26, 2011 at 20:53

Reporter

3,9345 gold badges36 silver badges50 bronze badges

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

7 Comments

Brian Zammit Over a year ago

Thanks for the speedy reply :) I have already tried testing resultset for null, in fact that was the way it originally was, but didn't work. Now that I tried testing it to 0, java gave me an error saying "java.sql.ResultSet and int"..

mylopeda Over a year ago

@Brian Zammit you should not test if rset equals 0, you should test if the size/count/what they call it is 0 or bigger, if size > 0 then you have one or more rows in the database. Why don't you do a select count(...) and check if you get 0 or something else from that?

Brian Zammit Over a year ago

oh yeah.. I will check into some more detail about that.. thanks.. because I never used the select count, and I am not sure on how to use it...

Brian Zammit Over a year ago

I just found some more info about the select count.. rgagnon.com/javadetails/java-0292.html It actually seems quite easy xD.. I will give it a try..

Brian Zammit Over a year ago

I gave it a try, however as in some cases, the record does not exist, when I try an rs.next(), it is giving me a null pointer exception...

|

Paŭlo Ebermann · Accepted Answer · 2011-07-26 21:20:52Z

0

What about this?

SELECT IF EXISTS (SELECT * FROM USERS WHERE USERID=@UID) THEN 1 ELSE 0 END

or

SELECT IF(EXISTS (SELECT * FROM USERS WHERE USERID=@UID), 1, 0)

(I'm not sure about the real syntax here.)

answered Jul 26, 2011 at 21:20

Paŭlo Ebermann

75k20 gold badges150 silver badges217 bronze badges

Comments

Burleigh Bear · Accepted Answer · 2011-07-27 00:07:47Z

0

(rset.getString("date") == null)

should be

(!rset.next())

rset is positioned 'before' the first result that gets returned. next() returns true if there was a 'next' result to get.

Also, what datatype is your 'date' variable? There's no guarantee that a date.toString() will format the date correctly for MS-Access version of SQL.

Rather, prepare a statement

PreparedStatement ps = connetion.prepareStatement("SELECT * from table where date=?");

and set the date like

ps.setDate(1, date);

then issue the query using the prepared statement.

That saves any toString() issues. (I haven't compiled this, it almost certainly won't work as-is, but the idea is there).

edited Jul 27, 2011 at 0:07

answered Jul 26, 2011 at 21:02

Burleigh Bear

3,30424 silver badges34 bronze badges

1 Comment

Brian Zammit Over a year ago

Yeah, good point.. I feel like an idiot not having thought of this before.. however it still did not work -.- Thanks for your reply

Volker Stolz · Accepted Answer · 2012-05-14 20:44:30Z

0

Here is what i used to find the last ID in a table. IF the table is empty the no ID will be returned. If table is populated then i needed the next ID for new record.

ResultSet mn = stmt.executeQuery("SELECT MAX(ExamID)FROM ExamResults");

if (mn == null){
       jTextField1.setText("1");
} else{
       while (mn.next()) {

         int lastID =Integer.parseInt(""+(mn.getObject(1)));
         jTextField1.setText(""+(lastID+1));
       }
}
// close the objects

mn.close();
stmt.close();
conn.close();

edited May 14, 2012 at 20:44

Volker Stolz

7,4301 gold badge36 silver badges52 bronze badges

answered Mar 16, 2012 at 1:28

Dave Hynes

1

Collectives™ on Stack Overflow

ms access database with java

4 Answers 4

7 Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

7 Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related