How to insert two strings into my Access database

问题:

I am trying to add two strings on two separate columns columns of my database using Java but I'm not sure what I am doing wrong. The code I am using

 try{
    Connection conn = DriverManager.getConnection(
                "jdbc:ucanaccess://C:/Users/nevik/Desktop/databaseJava/Employee.accdb");
        Statement st = conn.createStatement();
        String sql = "Select * from Table2";
        ResultSet rs = st.executeQuery(sql);


        rs.updateString("user", user);
        rs.updateString("pass", pass);

        rs.updateRow();

    }
    catch(SQLException ex){
        System.err.println("Error: "+ ex);
    }

The first column on my database is user and the next one is pass. I am using UCanAccess in order to access my database.

回答1:

This is how you normally update a row in java:

String query = "update Table2 set user = ?, pass= ?";
PreparedStatement preparedStmt = conn.prepareStatement(query);
preparedStmt.setInt   (1, user);
preparedStmt.setString(2, pass);

// execute the java preparedstatement
preparedStmt.executeUpdate();

回答2:

First of, you've not updated the position of the current cursor in the ResultSet, which means that it's pointing to nothing...

You could use...

if (rs.next()) {
   rs.updateString("user", user);
   rs.updateString("pass", pass);
   rs.updateRow();
}

But this assumes two things...

1. You have a database that supports updating values in the ResultSet and
2. You want to update the existing values.

To insert a value into the database, you should be using the INSERT command, for example...

try(Connection conn = DriverManager.getConnection(
            "jdbc:ucanaccess://C:/Users/nevik/Desktop/databaseJava/Employee.accdb")) {
    try (PreparedStatement stmt = conn.prepareStatement("INSERT into Table2 (user, pass) VALUES (?, ?)") {
        stmt.setString(1, user);
        stmt.setString(2, pass);
        int rowsUpdated = stmt.executeUpdate();
    }
}
catch(SQLException ex){
    System.err.println("Error: "+ ex);
}

You might like to take some time to go over a basic SQL tutorial and the JDBC(TM) Database Access trail

As a side note...

1. You should not be storing passwords in Strings, you should keep them in char arrays and
2. You should not be storing passwords in the database without encrypting them in some way

回答3:

@guevarak12 About the original question (how to use updatable ResultSet): your code is wrong, you have to move the cursor in the right position. In particular, if you are inserting a new row you have to call rs.moveToInsertRow(); before rs.updateString("user", user). If you are updating an existent row, you have to move the cursor calling rs.next() and so reach the row to update. Also you have to create the Statement in a different way:

Statement st =conn.createStatement( ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);

See junit examples in the UCanAccess source distribution, class net.ucanaccess.test.CrudTest. All other comments seem to be correct.