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 'and (A.developer_id="李白" or T.contact_name like "%李白%")' at line 17
### The error may exist in class path resource [mybatis/AppInfoMapper.xml]
### The error may involve com.xiaomi.mina.center.mapper.AppInfoMapper.listFirstOnlineAppInfo-Inline
### The error occurred while setting parameters
### SQL: select A.*,if(A.app_version_code=B.min_app_version_code,0,2) as version_state, T.contact_name from app_info A join (select min(app_version_code) as min_app_version_code,app_id from app_info where status=4 group by app_id) B on A.app_id=B.app_id inner join developer_info T on T.developer_id = A.developer_id and A.app_version_code=B.min_app_version_code and A.status=4 where date_format(from_unixtime(A.update_time/1000),'%Y%m%d') >= ? and date_format(from_unixtime(A.update_time/1000),'%Y%m%d') <= ? limit 0,20 and (A.developer_id="李白" or T.contact_name like "%李白%")
### 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 'and (A.developer_id="李白" or T.contact_name like "%李白%")' at line 17
感觉应该是sql语句的问题,但是一直无法定位到问题,mapper.xml中对应内容为:
<select id="listFirstOnlineAppInfo" parameterType="com.xiaomi.mina.center.base.AppInfoQUERY"
resultMap="BaseResultMap">
select A.*,if(A.app_version_code=B.min_app_version_code,0,2) as version_state from app_info A
join (select min(app_version_code) as min_app_version_code,app_id from app_info
where status=4 group by app_id) B on A.app_id=B.app_id
inner join developer_info T on T.developer_id = A.developer_id
and A.app_version_code=B.min_app_version_code and A.status=4
where
date_format(from_unixtime(A.update_time/1000),'%Y%m%d') >= #{updateBeginDate}
and date_format(from_unixtime(A.update_time/1000),'%Y%m%d') <= #{updateEndDate}
<if test="appKeywords != null">
and (A.app_id="${appKeywords}" or A.package_name like "%${appKeywords}%" or A.app_name like
"%${appKeywords}%")
</if>
<if test="type != null">
and A.type=#{type}
</if>
<if test="cardType != null">
and A.app_type=#{cardType}
</if>
<if test="order != null and prop != null">
order by A.${prop} ${order}
</if>
<if test="startNum != null">
limit ${startNum},${pageSize}
</if>
<if test="developerKeywords != null">
and (A.developer_id="${developerKeywords}" or T.contact_name like "%${developerKeywords}%")
</if>
</select>
生成的sql语句有问题,
select A.*,if(A.app_version_code=B.min_app_version_code,0,2) as version_state, T.contact_name from app_info A join (select min(app_version_code) as min_app_version_code,app_id from app_info where status=4 group by app_id) B on A.app_id=B.app_id inner join developer_info T on T.developer_id = A.developer_id and A.app_version_code=B.min_app_version_code and A.status=4 where date_format(from_unixtime(A.update_time/1000),'%Y%m%d') >= ? and date_format(from_unixtime(A.update_time/1000),'%Y%m%d') <= ? limit 0,20 and (A.developer_id="李白" or T.contact_name like "%李白%")
加的那句起码应该在limit后面啊,我也太马虎了。。。遇到这种问题首先检查sql语句是否有问题!!
修改后:
<select id="listFirstOnlineAppInfo" parameterType="com.xiaomi.mina.center.base.AppInfoQUERY"
resultMap="BaseResultMap">
select A.*,if(A.app_version_code=B.min_app_version_code,0,2) as version_state from app_info A
join (select min(app_version_code) as min_app_version_code,app_id from app_info
where status=4 group by app_id) B on A.app_id=B.app_id
inner join developer_info T on T.developer_id = A.developer_id
and A.app_version_code=B.min_app_version_code and A.status=4
where
date_format(from_unixtime(A.update_time/1000),'%Y%m%d') >= #{updateBeginDate}
and date_format(from_unixtime(A.update_time/1000),'%Y%m%d') <= #{updateEndDate}
<if test="appKeywords != null">
and (A.app_id="${appKeywords}" or A.package_name like "%${appKeywords}%" or A.app_name like
"%${appKeywords}%")
</if>
<if test="type != null">
and A.type=#{type}
</if>
<if test="cardType != null">
and A.app_type=#{cardType}
</if>
<if test="developerKeywords != null">
and (T.developer_id="${developerKeywords}" or T.contact_name like "%${developerKeywords}%")
</if>
<if test="order != null and prop != null">
order by A.${prop} ${order}
</if>
<if test="startNum != null">
limit ${startNum},${pageSize}
</if>
</select>
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)