CODEWITHSUNDEEP
sqlspring-boothsqldbbackend
ion20
ion20

Reputation: 657

"integrity constraint violation: unique constraint or index violation" in Spring HSQLDB

I have a RESTful web service in Springboot with an end-point defined as: 

@RequestMapping(
        value="/api/users",
        method=RequestMethod.POST,
        consumes=MediaType.APPLICATION_JSON_VALUE,
        produces=MediaType.APPLICATION_JSON_VALUE)
public ResponseEntity<User> createUser(
        @Valid @RequestBody User user, BindingResult result) {
    if (result.hasErrors()) {
        return new ResponseEntity<User>(user, HttpStatus.BAD_REQUEST);
    }
    User savedUser = userService.create(user);
    return new ResponseEntity<User>(savedUser, HttpStatus.CREATED);

}

and the User schema defined as:

    CREATE TABLE User (
    userId BIGINT GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL,
    username VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    mobile BIGINT NOT NULL UNIQUE,
    PRIMARY KEY (userId)
);

I want to ensure that users with the same username/email or mobile phone do not get registered. In my Postman client, when I make a POST request with duplicate values, I get a 500 Internal Server Error and the following string of errors appears on my server-side:

    2015-08-11 18:27:10.454  WARN 4644 --- [nio-8080-exec-3] o.h.engine.jdbc.spi.Sql
ExceptionHelper   : SQL Error: -104, SQLState: 23505
2015-08-11 18:27:10.454 ERROR 4644 --- [nio-8080-exec-3] o.h.engine.jdbc.spi.Sql
ExceptionHelper   : integrity constraint violation: unique constraint or index v
iolation; SYS_CT_10094 table: USER
2015-08-11 18:27:10.494 ERROR 4644 --- [nio-8080-exec-3] o.a.c.c.C.[.[.[/].[disp
atcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context
 with path [] threw exception [Request processing failed; nested exception is or
g.springframework.dao.DataIntegrityViolationException: could not execute stateme
nt; SQL [n/a]; constraint [SYS_CT_10094]; nested exception is org.hibernate.exce
ption.ConstraintViolationException: could not execute statement] with root cause


org.hsqldb.HsqlException: integrity constraint violation: unique constraint or i
ndex violation; SYS_CT_10094 table: USER
        at org.hsqldb.error.Error.error(Unknown Source)
        at org.hsqldb.Constraint.getException(Unknown Source)
        at org.hsqldb.index.IndexAVLMemory.insert(Unknown Source)
        at org.hsqldb.persist.RowStoreAVL.indexRow(Unknown Source)
        at org.hsqldb.TransactionManager2PL.addInsertAction(Unknown Source)
        at org.hsqldb.Session.addInsertAction(Unknown Source)
        at org.hsqldb.Table.insertSingleRow(Unknown Source)
        at org.hsqldb.StatementDML.insertRowSet(Unknown Source)
        at org.hsqldb.StatementInsert.getResult(Unknown Source)
        at org.hsqldb.StatementDMQL.execute(Unknown Source)
        at org.hsqldb.Session.executeCompiledStatement(Unknown Source)
        at org.hsqldb.Session.execute(Unknown Source)
        at org.hsqldb.jdbc.JDBCPreparedStatement.fetchResult(Unknown Source)
        at org.hsqldb.jdbc.JDBCPreparedStatement.executeUpdate(Unknown Source)
        at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(
ResultSetReturnImpl.java:208)
        at org.hibernate.id.IdentityGenerator$GetGeneratedKeysDelegate.executeAn
dExtract(IdentityGenerator.java:96)
        at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(Abstr
actReturningDelegate.java:58)
        at org.hibernate.persister.entity.AbstractEntityPersister.insert(Abstrac
tEntityPersister.java:3032)
        at org.hibernate.persister.entity.AbstractEntityPersister.insert(Abstrac
tEntityPersister.java:3558)
        at org.hibernate.action.internal.EntityIdentityInsertAction.execute(Enti
tyIdentityInsertAction.java:98)
        at org.hibernate.engine.spi.ActionQueue.execute(ActionQueue.java:492)
        at org.hibernate.engine.spi.ActionQueue.addResolvedEntityInsertAction(Ac
tionQueue.java:197)
        at org.hibernate.engine.spi.ActionQueue.addInsertAction(ActionQueue.java
:181)
        at org.hibernate.engine.spi.ActionQueue.addAction(ActionQueue.java:216)
        at org.hibernate.event.internal.AbstractSaveEventListener.addInsertActio
n(AbstractSaveEventListener.java:334)
        at org.hibernate.event.internal.AbstractSaveEventListener.performSaveOrR
eplicate(AbstractSaveEventListener.java:289)
        at org.hibernate.event.internal.AbstractSaveEventListener.performSave(Ab
stractSaveEventListener.java:195)
        at org.hibernate.event.internal.AbstractSaveEventListener.saveWithGenera
tedId(AbstractSaveEventListener.java:126)
        at org.hibernate.jpa.event.internal.core.JpaPersistEventListener.saveWit
hGeneratedId(JpaPersistEventListener.java:84)
        at org.hibernate.event.internal.DefaultPersistEventListener.entityIsTran
sient(DefaultPersistEventListener.java:206)
        at org.hibernate.event.internal.DefaultPersistEventListener.onPersist(De
faultPersistEventListener.java:149)
        at org.hibernate.event.internal.DefaultPersistEventListener.onPersist(De
faultPersistEventListener.java:75)
        at org.hibernate.internal.SessionImpl.firePersist(SessionImpl.java:811)
        at org.hibernate.internal.SessionImpl.persist(SessionImpl.java:784)
        at org.hibernate.internal.SessionImpl.persist(SessionImpl.java:789)
        at org.hibernate.jpa.spi.AbstractEntityManagerImpl.persist(AbstractEntit
yManagerImpl.java:1181)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.
java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcces
sorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:483)
        at org.springframework.orm.jpa.ExtendedEntityManagerCreator$ExtendedEnti
tyManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:344)
        at com.sun.proxy.$Proxy76.persist(Unknown Source)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.
java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcces
sorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:483)
        at org.springframework.orm.jpa.SharedEntityManagerCreator$SharedEntityMa
nagerInvocationHandler.invoke(SharedEntityManagerCreator.java:291)
        at com.sun.proxy.$Proxy76.persist(Unknown Source)
        at org.springframework.data.jpa.repository.support.SimpleJpaRepository.s
ave(SimpleJpaRepository.java:433)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.
java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcces
sorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:483)
        at org.springframework.data.repository.core.support.RepositoryFactorySup
port$QueryExecutorMethodInterceptor.executeMethodOn(RepositoryFactorySupport.jav
a:436)
        at org.springframework.data.repository.core.support.RepositoryFactorySup
port$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:421)
        at org.springframework.data.repository.core.support.RepositoryFactorySup
port$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:393)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(
ReflectiveMethodInvocation.java:179)
        at org.springframework.data.repository.core.support.RepositoryFactorySup
port$DefaultMethodInvokingMethodInterceptor.invoke(RepositoryFactorySupport.java
:506)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(
ReflectiveMethodInvocation.java:179)
        at org.springframework.transaction.interceptor.TransactionInterceptor$1.
proceedWithInvocation(TransactionInterceptor.java:99)
        at org.springframework.transaction.interceptor.TransactionAspectSupport.
invokeWithinTransaction(TransactionAspectSupport.java:281)
        at org.springframework.transaction.interceptor.TransactionInterceptor.in
voke(TransactionInterceptor.java:96)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(
ReflectiveMethodInvocation.java:179)
        at org.springframework.dao.support.PersistenceExceptionTranslationInterc
eptor.invoke(PersistenceExceptionTranslationInterceptor.java:136)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(
ReflectiveMethodInvocation.java:179)
        at org.springframework.data.jpa.repository.support.CrudMethodMetadataPos
tProcessor$CrudMethodMetadataPopulatingMethodIntercceptor.invoke(CrudMethodMetad
ataPostProcessor.java:122)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(
ReflectiveMethodInvocation.java:179)
        at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invok
e(ExposeInvocationInterceptor.java:92)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(
ReflectiveMethodInvocation.java:179)
        at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynami
cAopProxy.java:207)
        at com.sun.proxy.$Proxy81.save(Unknown Source)
        at favorite.ws.service.UserServiceBean.create(UserServiceBean.java:28)
        at favorite.ws.web.api.UserController.createUser(UserController.java:73)

        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.
java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcces
sorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:483)
        at org.springframework.web.method.support.InvocableHandlerMethod.doInvok
e(InvocableHandlerMethod.java:221)
        at org.springframework.web.method.support.InvocableHandlerMethod.invokeF
orRequest(InvocableHandlerMethod.java:137)
        at org.springframework.web.servlet.mvc.method.annotation.ServletInvocabl
eHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:110)
        at org.springframework.web.servlet.mvc.method.annotation.RequestMappingH
andlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:776)
        at org.springframework.web.servlet.mvc.method.annotation.RequestMappingH
andlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:705)
        at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapt
er.handle(AbstractHandlerMethodAdapter.java:85)
        at org.springframework.web.servlet.DispatcherServlet.doDispatch(Dispatch
erServlet.java:959)
        at org.springframework.web.servlet.DispatcherServlet.doService(Dispatche
rServlet.java:893)
        at org.springframework.web.servlet.FrameworkServlet.processRequest(Frame
workServlet.java:967)
        at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServ
let.java:869)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:648)
        at org.springframework.web.servlet.FrameworkServlet.service(FrameworkSer
vlet.java:843)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appl
icationFilterChain.java:291)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationF
ilterChain.java:206)
        at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52
)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appl
icationFilterChain.java:239)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationF
ilterChain.java:206)
        at favorite.ws.filters.SimpleCORSFilter.doFilter(SimpleCORSFilter.java:2
2)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appl
icationFilterChain.java:239)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationF
ilterChain.java:206)
        at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInterna
l(HiddenHttpMethodFilter.java:77)
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerR
equestFilter.java:107)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appl
icationFilterChain.java:239)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationF
ilterChain.java:206)
        at org.springframework.web.filter.CharacterEncodingFilter.doFilterIntern
al(CharacterEncodingFilter.java:85)
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerR
equestFilter.java:107)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appl
icationFilterChain.java:239)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationF
ilterChain.java:206)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperV
alve.java:219)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextV
alve.java:106)
        at org.apache.catalina.authenticator.AuthenticatorBase.invoke(Authentica
torBase.java:502)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.j
ava:142)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.j
ava:79)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineVal
ve.java:88)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.jav
a:518)
        at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp
11Processor.java:1091)
        at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(
AbstractProtocol.java:668)
        at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpo
int.java:1521)
        at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoin
t.java:1478)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.
java:1142)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor
.java:617)
        at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskTh
read.java:61)
        at java.lang.Thread.run(Thread.java:745)

Can someone tell me what's wrong? Why am I getting a 500 Internal Server Error and not a 400 Bad Request Error? How do I fix this?

If a user selects a username (for example) which is already in use, I want the server to send a response telling him that. How do I achieve this? Tell him that the username isn't unique or the email is already registered (i.e. different error messages for username/email/mobile)?

Thanks!

Upvotes: 0

Views: 11773

Answers (1)

fredt
fredt

Reputation: 24372

On the database side, you need to give names to the UNIQUE constraints so they can be used later to tell which field was already in the database.

CREATE TABLE User (
userId BIGINT GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL,
username VARCHAR(100) NOT NULL,
password VARCHAR(100) NOT NULL,
email VARCHAR(100) NOT NULL,
mobile BIGINT NOT NULL,
CONSTRAINT PK PRIMARY KEY (userId), 
CONSTRAINT USERKEY UNIQUE(USERNAME),
CONSTRAINT EMAILKEY UNIQUE(EMAIL),
CONSTRAINT MOBILEKEY UNIQUE(MOBILE)
);

I don't know the answer to the servlet return code.

Upvotes: 0

Related Questions