建表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();

        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);

}

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

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

如果觉得本文对您有帮助，不妨扫描下方微信二维码打赏点，您的鼓励是我前进最大的动力：

最新文章

  1. Rafy 领域实体框架演示(2) - 新功能展示
  2. 【分享】iTOP-4412开发板使用之初体验[多图]
  3. 函数fsp_try_extend_data_file
  4. java学习--抽象类与接口
  5. 线性代数（高斯消元）：JSOI2008 球形空间产生器sphere
  6. 零基础学习云计算及大数据DBA集群架构师【Linux Bash Shell编程及系统自动化2015年1月21日周四】
  7. 跨域访问之JSONP
  8. 修改ElementUI源码实践
  9. Shell 整数比较、字符串比较
  10. javascript之prototype原型属性
  11. Linux系统文件和目录管理
  12. Vuex速学篇:(2)利用state保存新闻数据
  13. VC++ 实现INI文件读写操作
  14. 实践：IIS7下访问ashx页面，显示404
  15. 读写分离,读写分离死锁解决方案,事务发布死锁解决方案,发布订阅死锁解决方案|事务(进程 ID *)与另一个进程被死锁在 锁 资源上，并且已被选作死锁牺牲品。请重新运行该事务
  16. [转]数据库中Schema（模式）概念的理解
  17. Android Tab与TabHost
  18. Python学习---django之admin简介
  19. URL参数带加号“+”AJAX传值失败的解决方法
  20. Mybatis 使用技巧总结

热门文章

  1. ios 中手势用法
  2. 使用perf + FlameGraph生成进程火焰图
  3. c++ 静态类成员函数(static member function) vs 名字空间 (namespace)
  4. 弹窗相应menu点击事件
  5. 【DeepLearning】Exercise:Softmax Regression
  6. linux中DHCP服务配置文件/etc/dhcpd.conf详细说明
  7. 要成为linux网站运维工程师必须要掌握的技能
  8. hadoop执行wordcount例子
  9. 一步一步掌握java的线程机制(二)----Thread的生命周期
  10. CentOS下Redisserver安装配置