Struts2+Spring+Mybatis问题

jobnicer 2014-07-30 04:29:00
最近项目老大分配了个任务,用Struts2+Spring+Mybatis完成,但是在调试时遇到下面问题
ERROR [http-8086-2] (Slf4jLogger.java:37) - Exception occurred during processing request:
### Error querying database. Cause: java.sql.SQLException: ORA-00936: 缺失表达式

### The error may exist in ssm/fbms/dao/FlightDao.xml
### The error may involve ssm.fbms.dao.FlightDao.searchFlightList-Inline
### The error occurred while setting parameters
### SQL: select flightid as flightId, to_char(execdate,'yyyy-mm-dd') as execDate, flightno as flightNo, case when isoffin='A' then '进港' else '出港'end as isOffin, case when flightattr='2401' then '国际' when flightattr='2403' then '国内' when flightattr='2404' then '专机' end as flightAttr, task as task, aerocraftno as aerocraftNo, crafttype as craftType, to_char(startrealtakeofftime,'yyyy-mm-dd hh24:mi') as startRealTakeoffTime, to_char(terminalreallandintime,'yyyy-mm-dd hh24:mi') as terminalRealLandinTime, airlineshort as airlineShort, (select cnshortname from sbd_flightstate where code = innerprovidingstate) as innerProvidingState, (select cnshortname from sbd_flightstate where code = innerabnormalstate) as innerAbnormalState, (select cnshortname from sbd_flightstate where code = intlinnerprovidingstate) as intlInnerProvidingState, (select cnshortname from sbd_flightstate where code = intlinnerabnormalstate) from SOR_FLIGHTINFO where
### Cause: java.sql.SQLException: ORA-00936: 缺失表达式

; bad SQL grammar []; nested exception is java.sql.SQLException: ORA-00936: 缺失表达式

org.springframework.jdbc.BadSqlGrammarException:
### Error querying database. Cause: java.sql.SQLException: ORA-00936: 缺失表达式

### The error may exist in ssm/fbms/dao/FlightDao.xml
### The error may involve ssm.fbms.dao.FlightDao.searchFlightList-Inline
### The error occurred while setting parameters
### SQL: select flightid as flightId, to_char(execdate,'yyyy-mm-dd') as execDate, flightno as flightNo, case when isoffin='A' then '进港' else '出港'end as isOffin, case when flightattr='2401' then '国际' when flightattr='2403' then '国内' when flightattr='2404' then '专机' end as flightAttr, task as task, aerocraftno as aerocraftNo, crafttype as craftType, to_char(startrealtakeofftime,'yyyy-mm-dd hh24:mi') as startRealTakeoffTime, to_char(terminalreallandintime,'yyyy-mm-dd hh24:mi') as terminalRealLandinTime, airlineshort as airlineShort, (select cnshortname from sbd_flightstate where code = innerprovidingstate) as innerProvidingState, (select cnshortname from sbd_flightstate where code = innerabnormalstate) as innerAbnormalState, (select cnshortname from sbd_flightstate where code = intlinnerprovidingstate) as intlInnerProvidingState, (select cnshortname from sbd_flightstate where code = intlinnerabnormalstate) from SOR_FLIGHTINFO where
### Cause: java.sql.SQLException: ORA-00936: 缺失表达式

; bad SQL grammar []; nested exception is java.sql.SQLException: ORA-00936: 缺失表达式


我的.xml数据库查询语句如下
	<select id="searchFlightList" parameterType="ssm.fbms.bean.FlightBean" resultType="ssm.fbms.dto.FlightDto">
select
flightid as flightId,
to_char(execdate,'yyyy-mm-dd') as execDate,
flightno as flightNo,
case when isoffin='A' then '进港' else '出港'end as isOffin,
case
when flightattr='2401' then '国际'
when flightattr='2403' then '国内'
when flightattr='2404' then '专机' end as flightAttr,
task as task,
aerocraftno as aerocraftNo,
crafttype as craftType,
to_char(startrealtakeofftime,'yyyy-mm-dd hh24:mi') as startRealTakeoffTime,
to_char(terminalreallandintime,'yyyy-mm-dd hh24:mi') as terminalRealLandinTime,
airlineshort as airlineShort,
(select cnshortname from sbd_flightstate where code = innerprovidingstate)
as innerProvidingState,
(select cnshortname from sbd_flightstate where code = innerabnormalstate)
as innerAbnormalState,
(select cnshortname from sbd_flightstate where code = intlinnerprovidingstate)
as intlInnerProvidingState,
(select cnshortname from sbd_flightstate where code = intlinnerabnormalstate)
from
SOR_FLIGHTINFO
where
execdate <![CDATA[>=]]> to_date(#{execDateStart,jdbcType=VARCHAR}, 'yyyy-mm-dd') and
execdate <![CDATA[<=]]> to_date(#{execDateEnd,jdbcType=VARCHAR}, 'yyyy-mm-dd')
<if test="isOffin != null and isOffin != ''"> and isoffin = #{isOffin}</if>
<if test="airlinesId != null and airlinesId != ''"> and airlinesid = #{airlinesId,jdbcType=NUMERIC}</if>
<if test="flightNo != null and flightNo != ''"> and flightno = #{flightNo,jdbcType=NUMERIC}</if>
<if test="task != null and task != ''"> and task = #{task,jdbcType=VARCHAR}</if>
<if test="flightAttr != null and flightAttr != ''"> and flightattr = #{flightAttr,jdbcType=VARCHAR}</if>
<if test="craftTypeId !=null and craftTypeId != ''"> and crafttypeid = #{craftTypeId,jdbcType=VARCHAR}</if>
<if test="airlineShort != null and airlineShort != ''"> and airlineshort = #{airlineShort,jdbcType=VARCHAR}</if>
</select>

...全文
183 6 打赏 收藏 转发到动态 举报
写回复
用AI写文章
6 条回复
切换为时间正序
请发表友善的回复…
发表回复
剑神一笑 2014-07-30
  • 打赏
  • 举报
回复
看看是不是where后面连了个and造成的
jobnicer 2014-07-30
  • 打赏
  • 举报
回复
引用 1 楼 cbxjj 的回复:
execdate <![CDATA[>=]]> to_date(#{execDateStart,jdbcType=VARCHAR}, 'yyyy-mm-dd') and execdate <![CDATA[<=]]> to_date(#{execDateEnd,jdbcType=VARCHAR}, 'yyyy-mm-dd') execdate 这是 什么东东 >= 用>= <= 用<= 转义即可 不需要写<![CDATA[>=]]>这个 try again
我测试的时候就没用那个查询条件,只用了<if test="isOffin != null and isOffin != ''"> isoffin = #{isOffin}</if>做测试,其他注释了的
jobnicer 2014-07-30
  • 打赏
  • 举报
回复
客服端可以的
jobnicer 2014-07-30
  • 打赏
  • 举报
回复
我测试的时候就没用那个查询条件,只用了<if test="isOffin != null and isOffin != ''"> isoffin = #{isOffin}</if>做测试,其他注释了的
Fate- 2014-07-30
  • 打赏
  • 举报
回复
最好就是把sql 复制出去 在客户端执行一下,便于找到问题,sql的某个位置写错了
剑神一笑 2014-07-30
  • 打赏
  • 举报
回复
execdate <![CDATA[>=]]> to_date(#{execDateStart,jdbcType=VARCHAR}, 'yyyy-mm-dd') and execdate <![CDATA[<=]]> to_date(#{execDateEnd,jdbcType=VARCHAR}, 'yyyy-mm-dd') execdate 这是 什么东东 >= 用>= <= 用<= 转义即可 不需要写<![CDATA[>=]]>这个 try again

67,512

社区成员

发帖
与我相关
我的任务
社区描述
J2EE只是Java企业应用。我们需要一个跨J2SE/WEB/EJB的微容器,保护我们的业务核心组件(中间件),以延续它的生命力,而不是依赖J2SE/J2EE版本。
社区管理员
  • Java EE
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧