java - 使用 DB2 自动增量获取异常

Question

我创建了下表:

"CREATE TABLE ParsonCollection "
                + "(id integer not null GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),"
                + "name varchar(20),"
                + "eye varchar(20),"
                + "hair varchar(20),"
                + "height varchar(20),"
                + "weight varchar(20),"
                + "PRIMARY KEY (id))";

然后我尝试插入表中，这就是我遇到问题的地方。当我尝试更改“id”列时，收到一条错误消息“java.sql.SQLSyntaxErrorException:尝试修改标识列“ID”。”插入语句如下所示:

"insert into ParsonCollection values(" + q_surround(Name) + ","
            + q_surround(Eye) + "," + q_surround(Hair) + "," + q_surround(Height) + "," + q_surround(Weight) + ",1" + ")";

但是，当我删除插入“id”的字段时，出现以下错误:“java.sql.SQLSyntaxErrorException:分配的值的数量与指定或隐含列的数量不同。 ”这个插入语句如下所示:

"insert into ParsonCollection values(" + q_surround(Name) + ","
            + q_surround(Eye) + "," + q_surround(Hair) + "," + q_surround(Height) + "," + q_surround(Weight) + ")";

我该如何克服这个问题？似乎当我解决一个异常时，会弹出另一个异常，反之亦然。谢谢。

Answer 1

您无法分配给标识列。由于无法传递所有值进行插入，因此需要枚举列(省略标识列):

"insert into ParsonCollection (
    name,
    eye,
    hair,
    height
    weight
) values(" 
    + q_surround(Name) 
    + "," + q_surround(Eye) 
    + "," + q_surround(Hair) 
    + "," + q_surround(Height)
    + "," + q_surround(Weight)
+ ")";

旁注:您的代码对 SQL 注入(inject)开放。您应该认真考虑使用准备好的语句和绑定(bind)参数，而不是连接查询字符串。