shivam sharma
Reputation: 51
Want to make sure the Username is unique
I am trying to add a little feature to my Spring boot application where I want to username to be unique for that I am calling my get List Students method and checking if the newly added user name matches any other user username then it should give an error to the user for that I am getting an error of antlr.NoViableAltException: unexpected token: s Please help me here What I am doing wrong and what should be the best practice for this.. here is my code:
GetListOfStudents:
@SuppressWarnings("unchecked")
@Override
@Transactional
public List<Student> getListOfStudent(Student studentModel) {
List<Student> students =null;
try {
StringBuilder stringBuild = new StringBuilder("SELECT s FROM Student s WHERE s.id !=0 AND s.status="+Constants.StudentStatus.ACTIVE);
if(studentModel !=null) {
if(studentModel.getId()!=null) {
stringBuild.append("AND s.id =:id");
}
if(studentModel.getCourse()!=null) {
stringBuild.append("AND s.course =:course");
}
if(studentModel.getFirstName()!=null) {
stringBuild.append("AND s.firstName =:firstName");
}
if(studentModel.getLastName()!=null) {
stringBuild.append("AND s.lastName =:lastName");
}
if(studentModel.getEmail()!=null) {
stringBuild.append("AND s.email =:email");
}
if(studentModel.getDateOfBirth()!=null) {
stringBuild.append("AND s.dateOfBirth =:dateOfBirth");
}
if(studentModel.getStatus()!=null) {
stringBuild.append("AND s.status =:status");
}
if(studentModel.getAddedAt()!=null) {
stringBuild.append("AND s.addedAt =:addedAt");
}
if(studentModel.getUserName()!=null) {
stringBuild.append("AND s.userName =:userName");
}
if(studentModel.getPassword()!=null) {
stringBuild.append("AND s.password =:password");
}
if(studentModel.getUserType()!=null) {
stringBuild.append("AND s.userType =:userType");
}
}else {
throw new Exception("Model is null ===================" );
}
Query query = entityManage.createQuery(stringBuild.toString());
if(studentModel !=null) {
if(studentModel.getId()!=null) {
query.setParameter("id", studentModel.getId());
}
if(studentModel.getCourse()!=null) {
query.setParameter("course", studentModel.getCourse());
}
if(studentModel.getFirstName()!=null) {
query.setParameter("firstName", studentModel.getFirstName());
}
if(studentModel.getLastName()!=null) {
query.setParameter("lastName", studentModel.getLastName());
}
if(studentModel.getEmail()!=null) {
query.setParameter("email", studentModel.getEmail());
}
if(studentModel.getDateOfBirth()!=null) {
query.setParameter("dateOfBirth", studentModel.getDateOfBirth());
}
if(studentModel.getStatus()!=null) {
query.setParameter("status", studentModel.getStatus());
}
if(studentModel.getAddedAt()!=null) {
query.setParameter("addedAt", studentModel.getAddedAt());
}
if(studentModel.getUserName()!=null) {
query.setParameter("userName", studentModel.getUserName());
}
if(studentModel.getPassword()!=null) {
query.setParameter("password", studentModel.getPassword());
}
if(studentModel.getUserType()!=null) {
query.setParameter("userType", studentModel.getUserType());
}
}
students = query.getResultList();
}catch(Exception exe) {
exe.printStackTrace();
}
// LOGGER.info("Students are : " + students);
return students;
}
StudentController (Where I am trying to check the username)
@RequestMapping(value="/saveStudent",method = {RequestMethod.POST,RequestMethod.GET})
public String SaveStudent(@ModelAttribute("studentFormData") Student student,BindingResult errorList) {
List<Student> students= new ArrayList<Student>();
try {
students =studentService.getListOfStudent(student);
for(Student sts: students ) {
LOGGER.info("STUDENTS USER NAME :::::::::::::::::::::::" + sts.getUserName());
if(sts.getUserName().equals(student.getUserName())) {
// errorList.addError(new ObjectError("error" , " Username is already taken try this user name " + utils.RandomAlphaString()));
LOGGER.info("USER NAME MATCHED _______________________________________________________________________");
errorList.rejectValue("userName", "User name is already in use try with this username : "+ Utils.RandomAlphaString());
}
}
studentService.SaveStudent(student);
if(errorList.hasErrors()) {
return "student/studentForm";
}
} catch (Exception e) {
e.printStackTrace();
}
return "redirect:/Students/list";
}
Console:
antlr.NoViableAltException: unexpected token: s
at org.hibernate.hql.internal.antlr.HqlBaseParser.queryRule(HqlBaseParser.java:864) [hibernate-core-5.4.30.Final.jar:5.4.30.Final]
at org.hibernate.hql.internal.antlr.HqlBaseParser.selectStatement(HqlBaseParser.java:336) [hibernate-core-5.4.30.Final.jar:5.4.30.Final]
at org.hibernate.hql.internal.antlr.HqlBaseParser.statement(HqlBaseParser.java:200) [hibernate-core-5.4.30.Final.jar:5.4.30.Final]
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:294) [hibernate-core-5.4.30.Final.jar:5.4.30.Final]
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:189) [hibernate-core-5.4.30.Final.jar:5.4.30.Final]
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:144) [hibernate-core-5.4.30.Final.jar:5.4.30.Final]
at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:113) [hibernate-core-5.4.30.Final.jar:5.4.30.Final]
at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:73) [hibernate-core-5.4.30.Final.jar:5.4.30.Final]
at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:162) ~[hibernate-core-5.4.30.Final.jar:5.4.30.Final]
at org.hibernate.internal.AbstractSharedSessionContract.getQueryPlan(AbstractSharedSessionContract.java:613) ~[hibernate-core-5.4.30.Final.jar:5.4.30.Final]
java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: s near line 1, column 116 [SELECT s FROM com.Library.LibraryManagement.Entity.Student s WHERE s.id !=0 AND s.status=0AND s.course =:courseAND s.firstName =:firstNameAND s.lastName =:lastNameAND s.email =:emailAND s.dateOfBirth =:dateOfBirthAND s.userName =:userNameAND s.password =:passwordAND s.userType =:userType]
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:138)
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:181)
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:188)
at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:734)
at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:114)
at jdk.internal.reflect.GeneratedMethodAccessor109.invoke(Unknown Source)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:564)
at org.springframework.orm.jpa.ExtendedEntityManagerCreator$ExtendedEntityManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:362)
at com.sun.proxy.$Proxy94.createQuery(Unknown Source)
at jdk.internal.reflect.GeneratedMethodAccessor109.invoke(Unknown Source)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:564)
at org.springframework.orm.jpa.SharedEntityManagerCreator$SharedEntityManagerInvocationHandler.invoke(SharedEntityManagerCreator.java:311)
at com.sun.proxy.$Proxy94.createQuery(Unknown Source)
Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: s near line 1, column 116 [SELECT s FROM com.Library.LibraryManagement.Entity.Student s WHERE s.id !=0 AND s.status=0AND s.course =:courseAND s.firstName =:firstNameAND s.lastName =:lastNameAND s.email =:emailAND s.dateOfBirth =:dateOfBirthAND s.userName =:userNameAND s.password =:passwordAND s.userType =:userType]
at org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:74)
at org.hibernate.hql.internal.ast.ErrorTracker.throwQueryException(ErrorTracker.java:93)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:301)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:189)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:144)
at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:113)
at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:73)
at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:162)
at org.hibernate.internal.AbstractSharedSessionContract.getQueryPlan(AbstractSharedSessionContract.java:613)
at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:725)
... 128 more
org.springframework.transaction.UnexpectedRollbackException: Transaction silently rolled back because it has been marked as rollback-only
at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:752)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:711)
at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:654)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:407)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:692)
at com.Library.LibraryManagement.Serviceimpl.StudentServiceImpl$$EnhancerBySpringCGLIB$$9986e406.getListOfStudent(<generated>)
at com.Library.LibraryManagement.Controller.StudentController.SaveStudent(StudentController.java:66)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:564)
at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:197)
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:141)
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:106)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:894)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:808)
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1060)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:962)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006)
at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:909)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:652)
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:733)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:327)
at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:115)
at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:81)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:336)
at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:119)
at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:113)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:336)
at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:126)
at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:81)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:336)
at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:105)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:336)
at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:149)
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1167)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:641)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.base/java.lang.Thread.run(Thread.java:844)
Upvotes: 0
Views: 827
Answers (2)
RV7
Reputation: 55
I would suggest you - follow another approach to do this work.
Apply a unique constraint to a username column in your database table.
Below is an example of how you can set a unique constraint:
ALTER TABLE Students
ADD CONSTRAINT UC_Username UNIQUE (Username);
For more details, you can refer: https://www.w3schools.com/sql/sql_unique.asp
While saving a new object, if an exception is thrown, then you can simply cancel/ the transaction.
Writing extra code for checking if the username exists or not is not a recommended practice. Simply use database features. It not only serves your purpose but also improves the quality of your database validations.
Upvotes: 2
Scary Wombat
Reputation: 44844
a) SELECT s FROM Student s - that makes even me confused. select * from ...
b) when you are doing stringBuild.append("AND s.id =:id"); you need to allow for spaces so change to
stringBuild.append(" AND s.id =:id ");
and finally use a NativeQuery
Query query = entityManage.createNativeQuery(stringBuild.toString());
Upvotes: 1
Related Questions
- Check for duplicate usernames prevents updating user
- How to handle UsernameNotFoundException spring security
- Getting Exception while adding new User in Spring Security
- How to handle exception while adding duplicate username to a database in spring mvc application
- Getting "Maximum sessions of 1 for this principal exceeded" when logging in with different username
- Cannot get username in spring boot
- Spring Security: Username already exists exception
- JpaRepsitory giving error while using method existsByUserName(String userName);
- Validating username uniqueness
- Spring 3.2 Security - Login with non-unique username and additional info