ms access database with java

I am creating a java progra开发者_StackOverflow中文版m, 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 :)

---

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.

---

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.)

---

(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).

---

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();