JDBC删除条目-主键/CONCUR_UPDATABLE问题 [英] JDBC Deleting an Entry - Primary Keys/CONCUR_UPDATABLE issues

问题描述

我正在处理一个具有已运行功能的Swing GUI的小型数据库，从而可以使用它来添加名称和搜索条目.现在，我必须实现delete选项.这是给我的提示:

I´m dealing with a little database with a Swing GUI that is already functional, allowing the use to add a name and search for an entry. Now I have to implement the delete option. Here are the hints that I´m being given:

•确保该语句被创建为可更新.
•确保更新插入语句以包括主键字段.使用单词"default"作为该字段的值(不带引号)
•确保在查询的ResultSet中包含主键字段

•Make sure the Statement is created as updatable.
•Make sure to update the insert statements to include the primary key field. Use the word "default" as the value for the field (no quotes)
•Make sure to include the primary key field in the query for the ResultSet

当前我遇到此错误:

com.mysql.jdbc.NotUpdatable:结果集不可更新(引用表没有主键).此结果集必须来自以结果集类型ResultSet.CONCUR_UPDATABLE创建的语句，查询只能选择一个表，不能使用函数，必须从该表中选择所有主键.有关更多详细信息，请参见JDBC 2.1 API规范的5.6节.在greenDB.PhoneBookFrame $ 2.actionPerformed(PhoneBookFrame.java:119)

com.mysql.jdbc.NotUpdatable: Result Set not updatable (referenced table has no primary keys).This result set must come from a statement that was created with a result set type of ResultSet.CONCUR_UPDATABLE, the query must select only one table, can not use functions and must select all primary keys from that table. See the JDBC 2.1 API Specification, section 5.6 for more details. at greenDB.PhoneBookFrame$2.actionPerformed(PhoneBookFrame.java:119)

现在，这是一个非常简单的错误，但仍然存在修复它的问题.

Now, this is a very straightforward error but still I´m having issues fixing it.

我有多个类，但主要的类是DatabaseManager和可实例化DatabaseManager的PhoneBookGui.在DatabaseManager上，这是创建语句的方式:

I have multiple classes, but the main ones are the DatabaseManager and the PhoneBookGui that instatiates the DatabaseManager. On the DatabaseManager, this is how the statement is created:

stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,  
                            ResultSet.CONCUR_UPDATABLE);

那么，按照上述提示，上面的代码创建了一个可更新的语句，对吗?

So, following the hints described above, the code above creates an updatable statement, correct?

关于主键，我修改了表的创建以添加一个主键，并添加了上面建议的默认值.这是我想出的:

Coming to the primary keys, I modified the table creation to add a primary key adding default values as suggested above. Here´s what I came up with:

"create table Listings (" +
    "PRIMARY KEY (id)" + //was not here before
    "LAST_NAME  varchar (16)," +
    "FIRST_NAME varchar (16)," +
    "AREA_CODE  varchar(3)," +
    "PREFIX     varchar(3)," +
    "SUFFIX     varchar(4))",
"insert into Listings values ('DEFAULT', 'ANDERSON', 'JOHN',  '314', '825', '1695')",
"insert into Listings values ('DEFAULT', 'CABLES',   'WALLY', '212', '434', '9685')",
"insert into Listings values ('DEFAULT', 'FRY',      'EDGAR', '415', '542', '5885')",
"insert into Listings values ('DEFAULT', 'MARTIN',   'EDGAR', '665', '662', '9001')",
"insert into Listings values ('DEFAULT', 'TUCKER',   'JOHN',  '707', '696', '8541')",

现在，假设这是正确的，我进入第三部分，这是确保在ResultSet的查询中包括主键字段.应该在DatabaseManager类上还是在GUI内实例化的数据库上完成此操作?我认为后一种选择是我应该做的.

Now, presuming this is correct, I come to the third part, which is to make sure to include the primary key field in the query for ResultSet. Should this be done on the DatabaseManager class or on the instanted Database inside the GUI? I think the latter option is what I should do.

正如我所说，我的GUI具有添加和删除输入的按钮.添加按钮可以正常工作，并且我对代码进行了一些更改以包括主键字段.这是添加按钮上ActionListener的部分代码:

As I said, my GUI has buttons to add and to remove inputs. The add button is working perfectly, and I´ve did some changes on the code to include the primary key field. Here´s part of the code for the ActionListener on the add button:

class GetListener implements ActionListener {
    public void actionPerformed(ActionEvent aEvent) {
      String defaultPrimaryKey = "default"; //was not here before
      (other strings...)
      myDB.doGetQuery(buildQuery(defaultPrimaryKey, last, first, ac, pre ,sfx)); //added the primary key String
      ResultSet rset = myDB.getResultSet();
      (...)

最后，这是删除"按钮的代码:

Finally, this is the code for the remove button:

public void actionPerformed(ActionEvent aEvent) {
   try {
      int selected = table.getSelectedRow();
      ResultSet rset  = myDB.getResultSet();

      if(selected != -1 && selected < tblModel.getRowCount()) {
         rset.absolute(table.getSelectedRow() + 1);
         rset.first();
         rset.deleteRow(); // line where I get the error
         table.repaint();
         table.clearSelection();
      }

有什么想法可以将所有有关主键和可更新语句的信息链接到上面的deleteRow()方法?

Any ideas how I might link all this information regarding primary keys and updatable statements to the deleteRow() method above?

推荐答案

我有相关的解决方案.

同一表中的许多行的主键值不能相同. 主键唯一地定义每行，因此每行的主键字段需要具有不同的值. 为此，在创建表"Listings"时，可以使用

Primary key value can not be the same for many rows in the same table. Primary key defines each row uniquely so each row needs to have different value for the primary key field. For that when you are creating your table "Listings", you can use

"create table Listings (" +
    "LAST_NAME  varchar (16)," +
    "FIRST_NAME varchar (16)," +
    "AREA_CODE  varchar(3)," +
    "PREFIX     varchar(3)," +
    "SUFFIX     varchar(4)," +
    "ID MEDIUMINT NOT NULL AUTO_INCREMENT," + //this is the change you need
            "PRIMARY KEY (ID))",  //this is the change you need
    "insert into Listings (LAST_NAME, FIRST_NAME, AREA_CODE, PREFIX, SUFFIX) values ('ANDERSON', 'JOHN',  '314',  '825', '1965')",
    "insert into Listings (LAST_NAME, FIRST_NAME, AREA_CODE, PREFIX, SUFFIX) values ('CABLES', 'WALLY', '212',  '434', '9585')",
    "insert into Listings (LAST_NAME, FIRST_NAME, AREA_CODE, PREFIX, SUFFIX) values ('FRY', 'EDGAR', '415', '542', '5885')",
     "insert into Listings (LAST_NAME, FIRST_NAME, AREA_CODE, PREFIX, SUFFIX) values ('MARTIN',   'EDGAR', '665', '662', '9001')",
     "insert into Listings (LAST_NAME, FIRST_NAME, AREA_CODE, PREFIX, SUFFIX) values ('TUCKER',   'JOHN',  '707', '696', '8541')",

以下是必不可少的，这是正确的

the following is essential and it is correct

stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,  
                            ResultSet.CONCUR_UPDATABLE);

最后一部分是当您尝试删除行时，请确保在查询中包括主键"ID". 例如:

The last part is when you are trying to delete a row make sure that in your query you include the primary key which is "ID". For example:

String query = "select ID, LAST_NAME, FIRST_NAME, AREA_CODE, PREFIX, SUFFIX from Listings" + whereClause;
resultSet = statement.executeQuery(query);
resultSet.deleteRow();

这篇关于JDBC删除条目-主键/CONCUR_UPDATABLE问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！