JAVA - insert into DB problem

Question

I've got a problem where I'm inserting data into a database. It's returning that 1 row is being inserted, but when I actually check the database, nothing new has actually been inserted.

Here's my update function:

public int update(String sqlStatement) {
    int rows = 0;
    try {
        Statement st = this.conn.createStatement();
        rows = st.executeUpdate(sqlStatement);
        this.conn.commit();
        st.close();
            } catch (Exception err) {
        System.out.println("Got err doing update: " + err.getMessage());
    }   
    return rows;
}

And here's the function calling it via it's object:

            db = new Database();
            int rows = 0;
            String sql = "INSERT INTO tblStudent (firstName, lastName, username, password, isAdmin) ";
         sql += String.format("VALUES ('%s', '%s', '%s', '%s', %d)", fName, lName, username, passwd, isAdmin);
            System.out.println("Trying " + sql);
            if((rows = db.update(sql)) == 0) {
                System.out.println("Could not create new user");
                throw new Exception();
            }
            System.out.println("Rows " + rows);

As I said, it's reporting that a single row is inserted into the DB, but nothing is actually there. The DB is a MS Access DB.

Any help appreciated.

G

Answer 1

Okay, fixed the problem.

Seems you have to close the connection. Even if isn't being reused.

I added the following function to the Database class:

    public void close() {
    try {
        this.conn.close();
    } catch (Exception err) {
        System.out.println("Error while closing connection: " + err.getMessage());
    }
}

And then added the close to the other functions in a finally clause:

        try {
            db = new Database();
            int rows = 0;
            String sql = "INSERT INTO tblStudent (firstName, lastName, username, password, isAdmin) ";
            sql += String.format("VALUES ('%s', '%s', '%s', '%s', %d)", fName, lName, username, passwd, isAdmin);
            System.out.println("Trying " + sql);
            if((rows = db.update(sql)) == 0) {
                System.out.println("Could not create new user");
                throw new Exception();
            }
            System.out.println("Rows " + rows);
        } catch (Exception err) {
            System.out.println("Got err in registerUser: " + err.getMessage());
            return false;
        } finally {
            db.close();
        }

Makes me wonder why there's even the option to commit then, since it actually doesn't do a commit even though you've told it to commit.