UCanAccess reads the last column of a table as the

2019-07-11 04:40发布

问题:

This question already has an answer here:

  • SQL INSERT without specifying columns. What happens? 6 answers

My program always reads the first column in the Access database as the 2nd column, the 2nd one as the 3rd column and so on, but the last column it reads as the first column.

public void actionPerformed(ActionEvent e) {
  try{
    String query = "insert into Staff values ('2', 'w', 'w', 'w', 'e', 'w', 'd','d','end')";
    stmt.executeUpdate(query);
    status.setText("1 row of record have been inserted");
  }catch(Exception ex){
    ex.printStackTrace();
  }
}

But when i specify the column names, it reads normally

public void actionPerformed(ActionEvent e) {
  try{
    String query = "insert into Staff (id, lastName, firstName, mi, address, city, state, telephone, email) "
    + "values ('2', 'w', 'w', 'w', 'e', 'w', 'd','d','end')";
    stmt.executeUpdate(query);
    status.setText("1 row of record have been inserted");
  }catch(Exception ex){
    ex.printStackTrace();
  }
}

回答1:

By default, UCanAccess treats the columns of a table in "DATA" order. That is the order in which the columns have been defined internally in the Access metadata, which can be different from the "DISPLAY" order.

If you want UCanAccess to treat the columns in "DISPLAY" order, then add the argument

;columnOrder=DISPLAY

to the end of your connection URL, e.g.,

jdbc:ucanaccess://c:/db/cico.mdb;columnOrder=DISPLAY

However, you should also realize by now that by not specifying the actual column names in your INSERT statement you are leaving your code vulnerable to breakage by assuming the names (and order) of the columns.

Just as SELECT * FROM is frowned upon, so is INSERT INTO tablename VALUES ( .... You are already specifying the values for each column so you really should be specifying the names of the columns as well.