ibatis query problem

[debraj mallick]

hi all

my query in sql is like:

SELECT MAX(`username`) FROM  `users` WHERE  `username` =  'ww'

but when i am doing the same using ibatis and spring, it is showing exception
query:

@Select("SELECT MAX #username# FROM users WHERE username = #{username} AND userType = #{userType}")
    int checkEmailAvailability(UsersDTO usersDTO);

Exception:

org.springframework.dao.TransientDataAccessResourceException: 
### Error querying database.  Cause: java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).
### The error may involve com.cloudlabz.product.weather.dao.UserDao.checkEmailAvailability-Inline
### The error occurred while setting parameters
### Cause: java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).
; SQL []; Parameter index out of range (1 > number of parameters, which is 0).; nested exception is java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).
	org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:107)
	org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
	org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
	org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
	org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:71)
	org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:358)
	$Proxy5.selectOne(Unknown Source)
	org.mybatis.spring.SqlSessionTemplate.selectOne(SqlSessionTemplate.java:155)
	org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:75)
	org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:25)
	$Proxy12.checkEmailAvailability(Unknown Source)
	com.cloudlabz.product.weather.business.impl.UserServiceImpl.checkEmailAvailability(UserServiceImpl.java:37)
	com.cloudlabz.product.weather.controller.RegistrationController.processRegistration(RegistrationController.java:54)
	sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	java.lang.reflect.Method.invoke(Method.java:597)
	org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:212)
	org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:126)
	org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:96)
	org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:617)
	org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:578)
	org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80)
	org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:900)
	org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:827)
	org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:882)
	org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:789)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
	org.netbeans.modules.web.monitor.server.MonitorFilter.doFilter(MonitorFilter.java:393)

java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).
	com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
	com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
	com.mysql.jdbc.SQLError.createSQLException(SQLError.java:982)
	com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927)
	com.mysql.jdbc.PreparedStatement.checkBounds(PreparedStatement.java:3729)
	com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:3713)
	com.mysql.jdbc.PreparedStatement.setString(PreparedStatement.java:4553)
	org.apache.ibatis.type.StringTypeHandler.setNonNullParameter(StringTypeHandler.java:12)
	org.apache.ibatis.type.StringTypeHandler.setNonNullParameter(StringTypeHandler.java:8)
	org.apache.ibatis.type.BaseTypeHandler.setParameter(BaseTypeHandler.java:23)
	org.apache.ibatis.executor.parameter.DefaultParameterHandler.setParameters(DefaultParameterHandler.java:73)
	org.apache.ibatis.executor.statement.PreparedStatementHandler.parameterize(PreparedStatementHandler.java:61)
	org.apache.ibatis.executor.statement.RoutingStatementHandler.parameterize(RoutingStatementHandler.java:43)
	org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:56)
	org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:40)
	org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:243)
	org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:117)
	org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:72)
	org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:79)
	org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:73)
	org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:39)
	sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	java.lang.reflect.Method.invoke(Method.java:597)
	org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:350)
	$Proxy5.selectOne(Unknown Source)
	org.mybatis.spring.SqlSessionTemplate.selectOne(SqlSessionTemplate.java:155)
	org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:75)
	org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:25)
	$Proxy12.checkEmailAvailability(Unknown Source)
	com.cloudlabz.product.weather.business.impl.UserServiceImpl.checkEmailAvailability(UserServiceImpl.java:37)
	com.cloudlabz.product.weather.controller.RegistrationController.processRegistration(RegistrationController.java:54)
	sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	java.lang.reflect.Method.invoke(Method.java:597)
	org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:212)
	org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:126)
	org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:96)
	org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:617)
	org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:578)
	org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80)
	org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:900)
	org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:827)
	org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:882)
	org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:789)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
	org.netbeans.modules.web.monitor.server.MonitorFilter.doFilter(MonitorFilter.java:393)

Any suggestion would be great.

[Francois Bourgault]

Hi,

We use the following format when we have variables in the where clause:

WHERE USER_ID = #userId:VARCHAR# AND SESSION_ID = #sessionId:VARCHAR#

[debraj mallick]

Thanks Francois, it worked.