How do I Insert values into a Microsoft Access Database through JDBC?

Question

I have created a letter game in Java and I need to include a high score function in it using a Microsoft Access database through JDBC. The table in the database contains the following fields

Table Name: Scores

Name       (Text)
Difficulty (Text)
Characters (Number)
Accuracy   (Text)
Time       (Text)
Score      (Text)

I need a SQL statement to insert values from Java to update the Microsoft Access Database table.

Answer 1

to INSERT

INSERT INTO Scores (Name, Difficulty, Characters, Accuracy, Time, Score)
VALUES ('Name Value', 'Difficulty Value', 3, 'High', '10:30', '235');

in order to UPDATE, you will need a PRIMARY KEY in the Scores table, so that you only update a specific record (unless it is your intention to update more than one record). I would advise having a PK on the table regardless.

In addition, I notice that Difficulty, Accuracy, Time and Score are all text-based fields - Now I don't know what your database schema/design is like, but Difficulty, Accuracy and Score strike me as being numerical in nature, whilst Time sounds like a Date/Time. Take a look at the MS Access (JET DB Engine) Data types for more information.

EDIT: Taken from the comments

Looking here, it looks like the best way to parameterize a SQL statement in JDBC would be to write a stored procedure (prepared statement) using JDBC API, and then set the values you need to insert as parameters on the prepared statement.

Here's a JDBC tutorial on updating and a tutorial on inserting.

Answer 2

Try something like the following:

String dataSourceName = "my_ODBC_DSN_name";
String username = "username";
String password = "password";
String url = "jdbc:odbc:" + dataSourceName;

Connection con = null;
try {
  Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
  con = DriverManager.getConnection(url, username, password);

  PreparedStatement pst = con.prepareStatement("INSERT INTO Scores "
           + "(Name, Difficulty, Characters, Accuracy, Time, Score) "
           + "VALUES (?, ?, ?, ?, ?, ?)");

  pst.setString(1, "Name String");
  pst.setString(2, "Difficulty String");
  pst.setInt(3, 20);
  pst.setString(4, "Acurracy String");
  pst.setString(5, "Time String");
  pst.setString(6, "Score String");

  pst.executeUpdate();
} catch (SQLException e) {
  e.printStackTrace();
} catch (ClassNotFoundException e) {
  e.printStackTrace();
} finally {
  if (con != null)
    try {
      con.close();
    } catch (SQLException e) {
      e.printStackTrace();
    }
}

You have to set up an ODBC DSN first in the Windows Control Panel. It's in Control Panel -> Administrative Tools -> Data Sources.

As an aside to your main question, I also second Russ Cam's opinion that you should probably revise your Database schema, so that you use the appropriate column types for the Time and Score columns and possibly for Accuracy and Difficulty. In a normalized design, "Name" would also be a foreign key (something like player_id) to another table which will hold the details for each player.

Answer 3

any body can give a script to insert a data into msaccess database using javascript