org.postgresql.util。PSQLException:错误:列“id”中的空值违反了非空约束。

I looked around this error extensively on Google/MyBatis forums but could not find a definitive answer. I have table defined as below:

我在谷歌/MyBatis论坛上广泛查阅了这个错误，但没有找到确切的答案。我的表格定义如下:

CREATE TABLE "user" 
(
 id integer NOT NULL DEFAULT nextval('user_userid_seq'::regclass),
 username character varying(256) NOT NULL,
 first_name character varying(128),
 last_name character varying(128),
 id_number character varying(32),
 id_type integer,
 email_verified boolean,
 cellphone_verified boolean,
 token character varying(256),
 CONSTRAINT pk_id PRIMARY KEY (id),
 CONSTRAINT unique_user_username UNIQUE (username)
)
WITH (
 OIDS=FALSE
);

The user_userid_seq is defined as:

user_userid_seq定义为:

     CREATE SEQUENCE user_userid_seq
     INCREMENT 1
     MINVALUE 1
     MAXVALUE 9223372036854775807
     START 1
    CACHE 1;

I use MyBatis generator to generate MapperXML and other classes. However, when I'm trying to insert a record using the auto generated mapper, I get the following error:

我使用MyBatis生成器生成MapperXML和其他类。但是，当我尝试使用自动生成的mapper插入一个记录时，我得到了以下错误:

Error updating database. Cause: org.postgresql.util.PSQLException: 
  ERROR: null value in column "id" violates not-null constraint 
  Detail: Failing row contains (null, [e-mail], Edwin, Eliot, 98098098, 1, f, null, null). 
  The error may involve com.dukstra.xxx.web.data.mapper.UserMapper.insert-Inline 
  The error occurred while setting parameters 
    SQL: insert into public.user (id, username, first_name, last_name, id_number, id_type, email_verified, cellphone_verified, token ) values (?, ?, ?, ?, ?, ?, ?, ?, ? ) 
  Cause: org.postgresql.util.PSQLException: 
    ERROR: null value in column "id" violates not-null constraint 
    Detail: Failing row contains (null, [e-mail], Edwin, Eliot, 98098098, 1, f, null, null).

It seems that upon calling mapper.insert(newUser); the id column is being is passed as null, which causes the error above. What is the best way to fix this? I found several work arounds, but not sure if there is a definitive way? Some solutions suggested specifying an explicit selectKey and setting type to "pre" with the "generatedKey" option. However, this would mean having to write/customize xml for each of the table with a auto generated primary key with sequence. Is there a simpler way to either tell Postgres to automatically use a default key if the value is null or perhaps allow a "DEFAULT" (String) value in the ID field of generated user object? What is the best way to handle this?

似乎在调用mapper.insert(newUser);id列被作为null传递，这会导致上面的错误。解决这个问题的最好方法是什么?我发现了一些工作，但不确定是否有明确的方法?一些解决方案建议用“generatedKey”选项指定一个显式的selectKey和设置类型为“pre”。但是，这意味着必须为每个表编写/定制xml，并使用自动生成的主键。如果值为null，或者允许在生成的user对象的ID字段中允许“默认”(String)值，那么是否有一种更简单的方法可以告诉Postgres自动使用默认密钥?最好的处理方法是什么?

UPDATE: here is the generator config:

更新:这里是生成器配置:

 <?xml version="1.0" encoding="UTF-8" ?>
 <!DOCTYPE configuration
           PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
           "http://mybatis.org/dtd/mybatis-3-config.dtd">
     <configuration>
  <settings>
    <setting name="useGeneratedKeys" value="true" />
   </settings>
    <environments default="development">
    <environment id="development">
        <transactionManager type="JDBC" />
        <dataSource type="POOLED">
            <property name="driver" value="org.postgresql.Driver" />
            <property name="url" value="jdbc:postgresql://localhost:5433/travelnet" /> 
            <property name="username" value="postgres" />
            <property name="password" value="*******" />

        </dataSource>
    </environment>
</environments>

<mappers>
    <package name="com.dukstra.travelnet.web.data.mapper" />

</mappers>

1 个解决方案

#1

I think the best way is to not pass the id column down to the DB when doing an insert. New records will get their id values from the sequence which you have there. How exactly you can achieve this with these particular technologies you're using, I am not sure as I have no experience with them. Probably you can do this through the MapperXML or through some annotations in the entity classes which you have.

我认为最好的方法是在插入时不要将id列传递给DB。新的记录将从您所拥有的序列中获得它们的id值。你用这些特定的技术是如何做到这一点的，我不确定，因为我没有经验。您可能可以通过MapperXML或在您拥有的实体类中的一些注释来实现这一点。