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); }
表名都改为了小写，数据库中表字段名也都是小写。保持一致。经测试，如果数据库中的表字段名是大写的话，会报错。
再次测试插入，成功，接口返回结果：
如果觉得本文对您有帮助，不妨扫描下方微信二维码打赏点，您的鼓励是我前进最大的动力：
