我以前是仅仅分页,排序是手动赋值的,没有问题:
接口
public List<FuZhuang> showalldescdesc1(@Param("offset") int offset, @Param("limit") int limit);
sql语句
<select id="showalldescdesc1" resultType="FuZhuang" >
SELECT * from fuzhuang order by price desc,yuexiaoliang desc LIMIT #{offset},#{limit};
</select
但是现在想先排序时传入参数(asc、desc):
接口
public List<FuZhuang> showalldescdesc1(@Param("offset") int offset, @Param("limit") int limit,@Param("str") String str);
sql语句
<select id="showalldescdesc1" resultType="FuZhuang" >
SELECT * from fuzhuang order by price #{str},yuexiaoliang desc LIMIT #{offset},#{limit};
</select>
结果报错了:
org.springframework.jdbc.BadSqlGrammarException:
### Error querying database. Cause: com.mysql.jdbc.exceptions.jdbc4.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 ''desc',yuexiaoliang desc LIMIT 6,6' at line 1
### The error may exist in mybatis/mappers/FuZhuangMapper.xml
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: SELECT * from fuzhuang order by price ?,yuexiaoliang desc LIMIT ?,?;
### Cause: com.mysql.jdbc.exceptions.jdbc4.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 ''desc',yuexiaoliang desc LIMIT 6,6' at line 1
; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.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 ''desc',yuexiaoliang desc LIMIT 6,6' at line 1
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)
$Proxy10.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)
是什么原因啊?难道asc和desc不能以变量的形式传参吗?不会吧?该怎么解决啊!!!求助!!
接口
public List<FuZhuang> showalldescdesc1(@Param("offset") int offset, @Param("limit") int limit);
sql语句
<select id="showalldescdesc1" resultType="FuZhuang" >
SELECT * from fuzhuang order by price desc,yuexiaoliang desc LIMIT #{offset},#{limit};
</select
但是现在想先排序时传入参数(asc、desc):
接口
public List<FuZhuang> showalldescdesc1(@Param("offset") int offset, @Param("limit") int limit,@Param("str") String str);
sql语句
<select id="showalldescdesc1" resultType="FuZhuang" >
SELECT * from fuzhuang order by price #{str},yuexiaoliang desc LIMIT #{offset},#{limit};
</select>
结果报错了:
org.springframework.jdbc.BadSqlGrammarException:
### Error querying database. Cause: com.mysql.jdbc.exceptions.jdbc4.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 ''desc',yuexiaoliang desc LIMIT 6,6' at line 1
### The error may exist in mybatis/mappers/FuZhuangMapper.xml
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: SELECT * from fuzhuang order by price ?,yuexiaoliang desc LIMIT ?,?;
### Cause: com.mysql.jdbc.exceptions.jdbc4.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 ''desc',yuexiaoliang desc LIMIT 6,6' at line 1
; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.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 ''desc',yuexiaoliang desc LIMIT 6,6' at line 1
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)
$Proxy10.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)
是什么原因啊?难道asc和desc不能以变量的形式传参吗?不会吧?该怎么解决啊!!!求助!!
传入的参数要在select中写明,比如我这里传了一个Map,在sql中就可以引用Map中的数据了
SELECT * FROM fuzhuang
<if test="ORDER == 'desc'">
ORDER BY price DESC
</if>
<if test="ORDER == 'asc'">
ORDER BY yuexiaoliang ASC
</if>
LIMIT #{offset},#{limit};
</select>
可能是我没说清楚,我很打算是同时按照price和xiaoliang排序的,你这样的话只是按照一个条件排序了啊,我想知道能否通过传参数,来决定是升序(asc),还是降序(desc)。。求助
SELECT * FROM fuzhuang
<if test="str== 'desc'">
ORDER BY price DESC,yuexiaoliang ASC
</if>
<if test="str== 'asc'">
ORDER BY price ASC,yuexiaoliang ASC
</if>
LIMIT #{offset},#{limit};
</select>
试试应该可以
我试了下 没有报错了 应该可以 还需要测试下 小弟刚刚学mybatis 动态sql不怎么会用就指教 有没有什么好的视频或者书啊 讲的深一点的 还有mybatis要多传几个参数怎么办?parameterType或parameterClass该怎么写?比如5楼的情况????求指教!!!!
比如说
<if test="(str== '1')&&(price=='desc')">
ORDER BY price DESC
</if>
我试过 好像只要写&&就会报错
如果想同时满足两个条件该怎么办呢?
求解!!!!!!
<if test="str== '1' AND price =='desc' ">
ORDER BY price DESC
</if>