mybatis 传参查询急!
本帖最后由 hjnsst 于 2013-01-11 17:32:24 编辑 Map<String,Object> m=new HashMap<String,Object>();
if (name !=null&&name != "")
m.put("name", name);
m.put("start", p.getStart());
m.put("max", p.getPageSize());
List<Employee> list=employeeMapper.getListPage(m);
-------------------->
<select id="getListPage" parameterType="map" resultMap="employeeMap">
<![CDATA[
select * from employee t1 join dept t2 on(t1.deptNo=t2.deptNo)
where
<if test="name != null"> t1.name = #{name}</if>
ORDER BY t1.id desc limit #{start}, #{max}
]]>
</select>
org.springframework.jdbc.BadSqlGrammarException:
### Error querying database. Cause: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '<if test="name!=null"> t1.name = null</if>
ORDER BY t1.id desc limit' at line 3
### The error may exist in com/employee/dao/EmployeeMapper.xml
### The error may involve com.employee.dao.EmployeeMapper.getListPage-Inline
### The error occurred while setting parameters
### SQL: select * from employee t1 join dept t2 on(t1.deptNo=t2.deptNo) where <if test="name!=null"> t1.name = ?</if> ORDER BY t1.id desc limit ?, ?
### Cause: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '<if test="name!=null"> t1.name = null</if>
ORDER BY t1.id desc limit' at line 3
; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '<if test="name!=null"> t1.name = null</if>
ORDER BY t1.id desc limit' at line 3
org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:233)
org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:71)
org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:365)
$Proxy6.selectList(Unknown Source)
org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:195)
org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:124)
org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:90)
org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:40)
$Proxy12.getListPage(Unknown Source)
com.employee.service.impl.EmployeeServiceImpl.getList(EmployeeServiceImpl.java:84)
com.employee.web.EmployeeAction.list(EmployeeAction.java:177)
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
java.lang.reflect.Method.invoke(Method.java:601)
com.opensymphony.xwork2.DefaultActionInvocation.invokeAction(DefaultActionInvocation.java:446)
com.opensymphony.xwork2.DefaultActionInvocation.invokeActionOnly(DefaultActionInvocation.java:285)
com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248)
org.apache.struts2.interceptor.debugging.DebuggingInterceptor.intercept(DebuggingInterceptor.java:256)
com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:242)
com.opensymphony.xwork2.interceptor.DefaultWorkflowInterceptor.doIntercept(DefaultWorkflowInterceptor.java:176)
com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98)
com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:242)
com.opensymphony.xwork2.validator.ValidationInterceptor.doIntercept(ValidationInterceptor.java:265)
org.apache.struts2.interceptor.validation.AnnotationValidationInterceptor.doIntercept(AnnotationValidationInterceptor.java:68)
com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98)
com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:242)
com.opensymphony.xwork2.interceptor.ConversionErrorInterceptor.intercept(ConversionErrorInterceptor.java:138)
com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:242)
com.opensymphony.xwork2.interceptor.ParametersInterceptor.doIntercept(ParametersInterceptor.java:238)
com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98)
com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:242)
com.opensymphony.xwork2.interceptor.ParametersInterceptor.doIntercept(ParametersInterceptor.java:238)
com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98)
com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:242)
com.opensymphony.xwork2.interceptor.StaticParametersInterceptor.intercept(StaticParametersInterceptor.java:191)
com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:242)
org.apache.struts2.interceptor.MultiselectInterceptor.intercept(MultiselectInterceptor.java:75)
com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:242)
org.apache.struts2.interceptor.CheckboxInterceptor.intercept(CheckboxInterceptor.java:90)
com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:242)
org.apache.struts2.interceptor.FileUploadInterceptor.intercept(FileUploadInterceptor.java:252)
com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:242)
com.opensymphony.xwork2.interceptor.ModelDrivenInterceptor.intercept(ModelDrivenInterceptor.java:100)
com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:242)
com.opensymphony.xwork2.interceptor.ScopedModelDrivenInterceptor.intercept(ScopedModelDrivenInterceptor.java:141)
com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:242)
com.opensymphony.xwork2.interceptor.ChainingInterceptor.intercept(ChainingInterceptor.java:145)
com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:242)
com.opensymphony.xwork2.interceptor.PrepareInterceptor.doIntercept(PrepareInterceptor.java:171)
com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98)
com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:242)
com.opensymphony.xwork2.interceptor.I18nInterceptor.intercept(I18nInterceptor.java:176)
com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:242)
org.apache.struts2.interceptor.ServletConfigInterceptor.intercept(ServletConfigInterceptor.java:164)
com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:242)
com.opensymphony.xwork2.interceptor.AliasInterceptor.intercept(AliasInterceptor.java:193)
com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:242)
com.opensymphony.xwork2.interceptor.ExceptionMappingInterceptor.intercept(ExceptionMappingInterceptor.java:187)
com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:242)
org.apache.struts2.impl.StrutsActionProxy.execute(StrutsActionProxy.java:54)
org.apache.struts2.dispatcher.Dispatcher.serviceAction(Dispatcher.java:544)
org.apache.struts2.dispatcher.ng.ExecuteOperations.executeAction(ExecuteOperations.java:77)
org.apache.struts2.dispatcher.ng.filter.StrutsPrepareAndExecuteFilter.doFilter(StrutsPrepareAndExecuteFilter.java:91)
原因
com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '<if test="name!=null"> t1.name = null</if>
ORDER BY t1.id desc limit' at line 3
com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985)
com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631)
com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)
com.mysql.jdbc.Connection.execSQL(Connection.java:3283)
com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1332)
com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:882)
org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
mybatis parameterType="map" mybatis分页多个参数
[解决办法]
<![CDATA[
select * from employee t1 join dept t2 on(t1.deptNo=t2.deptNo)
where
<if test="name != null"> t1.name = #{name}</if>
ORDER BY t1.id desc limit #{start}, #{max}
]]>
