mybatis使用注解往postgresql数据库表insert数据[主键自增]的写法

建表SQL：

DROP TABLE IF EXISTS person;
CREATE TABLE person(
 person_id serial PRIMARY KEY NOT NULL,
 person_name VARCHAR(60),
 gender INT,
 person_addr VARCHAR(100),
 birthday DATE
);

注意：在postgresql中建表的时候，将主键id字段设置成serial类型，会自动生成一个关联主键id的序列(如下图中的数据库会创建一个隐含序列"person_person_id_seq")，SERIAL类型的字段和MySQL中的自增唯一ID等价。

当你在你的数据表中定义了一个SERIAL类型的列后，SERIAL的自增功能会被自动添加到数据库。

--------------------------------------------------------------------------------------

接口Controller：

@Component
@Path("/person")
@Produces(MediaType.APPLICATION_JSON + ";charset=utf-8")
public class PersonController {
    @Autowired
    PersonService personService;

    @POST
    @Path("/insert")
    @Consumes(MediaType.APPLICATION_JSON)
    public ResultModel insert() {
        Person person = new Person();
        person.setBirthday(new Date());
        person.setGender(3);
        person.setPersonAddr("广州");
        person.setPersonName("小红");
        personService.insert(person);
        return ResultModel.ok(person);
    }

 

注意上面的Person对象没有为personId赋值。因为person表的主键列person_id是自增的。

下面是使用mybatis的逆向工程生成的PersonMapper接口：

public interface PersonMapper {
    @Insert({
        "insert into person (" +
                "PERSON_ID, " +
                "PERSON_NAME, " +
                "GENDER, " +
                "PERSON_ADDR, " +
                "BIRTHDAY)",
        "values (" +
                "#{personId,jdbcType=INTEGER}, " +
                "#{personName,jdbcType=VARCHAR}, " +
                "#{gender,jdbcType=INTEGER}, " +
                "#{personAddr,jdbcType=VARCHAR}, " +
                "#{birthday,jdbcType=DATE})"
    })
    int insert(Person person);
}

由于person表的主键person_id是自增的，所以访问Controller中的"/person/insert"方法插入数据的时候会报如下错误：

HTTP Status 500 - org.springframework.dao.DataIntegrityViolationException:
type Exception report
message org.springframework.dao.DataIntegrityViolationException:
description The server encountered an internal error that prevented it from fulfilling this request.
exception

javax.servlet.ServletException: org.springframework.dao.DataIntegrityViolationException: 
### Error updating database.  Cause: org.postgresql.util.PSQLException: ERROR: null value in column "person_id" violates not-null constraint
  详细：Failing row contains (null, 小兰, 2, 重庆, 2017-06-04).
### The error may involve com.zhaopin.dao.PersonMapper.insert-Inline
### The error occurred while setting parameters
### SQL: insert into person (PERSON_ID, PERSON_NAME, GENDER, PERSON_ADDR, BIRTHDAY) values (?, ?, ?, ?, ?)
### Cause: org.postgresql.util.PSQLException: ERROR: null value in column "person_id" violates not-null constraint
  详细：Failing row contains (null, 小兰, 2, 重庆, 2017-06-04).
; SQL []; ERROR: null value in column "person_id" violates not-null constraint
  详细：Failing row contains (null, 小兰, 2, 重庆, 2017-06-04).; nested exception is org.postgresql.util.PSQLException: ERROR: null value in column "person_id" violates not-null constraint
  详细：Failing row contains (null, 小兰, 2, 重庆, 2017-06-04).
    org.glassfish.jersey.servlet.WebComponent.service(WebComponent.java:391)
    org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:382)
    org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:345)
    org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:220)
    org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
    org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:85)
    org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
root cause

org.springframework.dao.DataIntegrityViolationException: 
### Error updating database.  Cause: org.postgresql.util.PSQLException: ERROR: null value in column "person_id" violates not-null constraint
  详细：Failing row contains (null, 小兰, 2, 重庆, 2017-06-04).
### The error may involve com.zhaopin.dao.PersonMapper.insert-Inline
### The error occurred while setting parameters
### SQL: insert into person (PERSON_ID, PERSON_NAME, GENDER, PERSON_ADDR, BIRTHDAY) values (?, ?, ?, ?, ?)
### Cause: org.postgresql.util.PSQLException: ERROR: null value in column "person_id" violates not-null constraint
  详细：Failing row contains (null, 小兰, 2, 重庆, 2017-06-04).
; SQL []; ERROR: null value in column "person_id" violates not-null constraint
  详细：Failing row contains (null, 小兰, 2, 重庆, 2017-06-04).; nested exception is org.postgresql.util.PSQLException: ERROR: null value in column "person_id" violates not-null constraint
  详细：Failing row contains (null, 小兰, 2, 重庆, 2017-06-04).
    org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:243)
    org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
    org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:74)
    org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:399)
    com.sun.proxy.$Proxy23.insert(Unknown Source)
    org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:253)
    org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:52)
    org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:53)
    com.sun.proxy.$Proxy31.insert(Unknown Source)
    com.zhaopin.service.impl.PersonServiceImpl.insert(PersonServiceImpl.java:30)
    com.zhaopin.api.PersonController.insert(PersonController.java:58)
    sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    java.lang.reflect.Method.invoke(Method.java:498)
    org.glassfish.jersey.server.model.internal.ResourceMethodInvocationHandlerFactory$1.invoke(ResourceMethodInvocationHandlerFactory.java:81)
    org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher$1.run(AbstractJavaResourceMethodDispatcher.java:151)
    org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.invoke(AbstractJavaResourceMethodDispatcher.java:171)
    org.glassfish.jersey.server.model.internal.JavaResourceMethodDispatcherProvider$TypeOutInvoker.doDispatch(JavaResourceMethodDispatcherProvider.java:195)
    org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.dispatch(AbstractJavaResourceMethodDispatcher.java:104)
    org.glassfish.jersey.server.model.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:387)
    org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:331)
    org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:103)
    org.glassfish.jersey.server.ServerRuntime$1.run(ServerRuntime.java:269)
    org.glassfish.jersey.internal.Errors$1.call(Errors.java:271)
    org.glassfish.jersey.internal.Errors$1.call(Errors.java:267)
    org.glassfish.jersey.internal.Errors.process(Errors.java:315)
    org.glassfish.jersey.internal.Errors.process(Errors.java:297)
    org.glassfish.jersey.internal.Errors.process(Errors.java:267)
    org.glassfish.jersey.process.internal.RequestScope.runInScope(RequestScope.java:297)
    org.glassfish.jersey.server.ServerRuntime.process(ServerRuntime.java:252)
    org.glassfish.jersey.server.ApplicationHandler.handle(ApplicationHandler.java:1025)
    org.glassfish.jersey.servlet.WebComponent.service(WebComponent.java:372)
    org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:382)
    org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:345)
    org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:220)
    org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
    org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:85)
    org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
root cause

org.postgresql.util.PSQLException: ERROR: null value in column "person_id" violates not-null constraint
  详细：Failing row contains (null, 小兰, 2, 重庆, 2017-06-04).
    org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2270)
    org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1998)
    org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
    org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:570)
    org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:420)
    org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:413)
    com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:493)
    org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:45)
    org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:73)
    org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:49)
    org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:115)
    org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:75)
    org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:170)
    org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:157)
    sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    java.lang.reflect.Method.invoke(Method.java:498)
    org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:386)
    com.sun.proxy.$Proxy23.insert(Unknown Source)
    org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:253)
    org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:52)
    org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:53)
    com.sun.proxy.$Proxy31.insert(Unknown Source)
    com.zhaopin.service.impl.PersonServiceImpl.insert(PersonServiceImpl.java:30)
    com.zhaopin.api.PersonController.insert(PersonController.java:58)
    sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    java.lang.reflect.Method.invoke(Method.java:498)
    org.glassfish.jersey.server.model.internal.ResourceMethodInvocationHandlerFactory$1.invoke(ResourceMethodInvocationHandlerFactory.java:81)
    org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher$1.run(AbstractJavaResourceMethodDispatcher.java:151)
    org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.invoke(AbstractJavaResourceMethodDispatcher.java:171)
    org.glassfish.jersey.server.model.internal.JavaResourceMethodDispatcherProvider$TypeOutInvoker.doDispatch(JavaResourceMethodDispatcherProvider.java:195)
    org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.dispatch(AbstractJavaResourceMethodDispatcher.java:104)
    org.glassfish.jersey.server.model.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:387)
    org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:331)
    org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:103)
    org.glassfish.jersey.server.ServerRuntime$1.run(ServerRuntime.java:269)
    org.glassfish.jersey.internal.Errors$1.call(Errors.java:271)
    org.glassfish.jersey.internal.Errors$1.call(Errors.java:267)
    org.glassfish.jersey.internal.Errors.process(Errors.java:315)
    org.glassfish.jersey.internal.Errors.process(Errors.java:297)
    org.glassfish.jersey.internal.Errors.process(Errors.java:267)
    org.glassfish.jersey.process.internal.RequestScope.runInScope(RequestScope.java:297)
    org.glassfish.jersey.server.ServerRuntime.process(ServerRuntime.java:252)
    org.glassfish.jersey.server.ApplicationHandler.handle(ApplicationHandler.java:1025)
    org.glassfish.jersey.servlet.WebComponent.service(WebComponent.java:372)
    org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:382)
    org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:345)
    org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:220)
    org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
    org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:85)
    org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
note The full stack trace of the root cause is available in the Apache Tomcat/7.0.53 logs.

Apache Tomcat/7.0.53

意思是说"person_id"这一列违反非空约束,不能为null。

解决办法：修改PersonMapper中的@Insert注解中的SQL语句，首先把person_id列去掉。

然后增加一个@Options注解，声明主键列是自增的：

public interface PersonMapper {
    @Insert({
        "insert into person (" +
                "person_name, " +
                "gender, " +
                "person_addr, " +
                "birthday)",
        "values (#{personName,jdbcType=VARCHAR}, " +
                "#{gender,jdbcType=INTEGER}, " +
                "#{personAddr,jdbcType=VARCHAR}, " +
                "#{birthday,jdbcType=DATE})"
    })
    @Options(useGeneratedKeys=true, keyProperty="personId",keyColumn = "person_id")
    int insert(Person person);
}

 表名都改为了小写，数据库中表字段名也都是小写。保持一致。经测试，如果数据库中的表字段名是大写的话，会报错。

 再次测试插入，成功，接口返回结果：