IBatis sql查询 问题

Question

我用 Struts + spring + ibatis 做了一个页面，报了下面的错，org.springframework.jdbc.BadSqlGrammarException: SqlMapClient operation; bad SQL grammar []; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:   
--- The error occurred in net/szpt/ei/obs/domain/feedBackVo.xml.  
--- The error occurred while applying a result map.  
--- Check the feedBackVo.feedBackVoResult.  
--- Check the result mapping for the 'userId' property.  
--- Cause: java.sql.SQLException: Column 'userId' not found.feedBackVo.xml 原码<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap      
    PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"      
    "http://ibatis.apache.org/dtd/sql-map-2.dtd">
    
<sqlMap namespace="feedBackVo">

<typeAlias alias="feedBackVo" type="net.szpt.ei.obs.vo.FeedBackVo"/>

<resultMap class="feedBackVo" id="feedBackVoResult">
<result property="feedBackId" column="feedBackId"/>
<result property="userId" column="userId"/>
<result property="userName" column="userName"/>
<result property="title" column="title"/>
<result property="content" column="content"/>
<result property="status" column="status"/>
<result property="date" column="date"/>
</resultMap> <select id="queryFeedBack" parameterClass="feedBackVo" resultMap="feedBackVoResult">
SELECT f.feedBackId, u.userName, f.title, f.status 
FROM feedback f, t_user u
WHERE f.userId = u.userId
<dynamic prepend=" ">
<isNotEmpty property="feedBackId" prepend=" AND ">feedBackId = #feedBackId#</isNotEmpty>
<isNotEmpty property="userId" prepend=" AND ">userName = #userName#</isNotEmpty>
<isNotEmpty property="userId" prepend=" AND ">title = #title#</isNotEmpty>
<isNotEmpty property="status" prepend=" AND ">status = #status#</isNotEmpty>
</dynamic>
</select>

<select id="queryFeedBackContent" parameterClass="java.lang.Integer" resultMap="feedBackVoResult">
SELECT *
FROM feedback f, t_user u
WHERE f.userId = u.userId
AND feedBackId = #feedBackId#
</select></sqlMap>    问题是出在 第一个 select 的SELECT f.feedBackId, u.userName, f.title, f.status
如果换成 select * 就没错了请问下这个问题怎么解决

Answer 1

select 语句返回的列与resultMap 定义的不一致
 SELECT f.feedBackId, u.userId, u.userName, f.title, f.status , f.content, f.date

Answer 2

SELECT f.feedBackId, u.userId, u.userName, f.title,f.content,  f.status , f.date

Answer 3

你的xml中的sql部分可能有些不应该的中文全角空白或其它全角字符吧，好好检查一下

Answer 4

SELECT f.feedBackId as feedBackId, 
       u.userName, 
       f.title, 
       f.status
FROM
       ....
没有全角半角引起的问题吗？ 试试我这个先。 再看看情况

Answer 5

select 没有userId列，与ResultMap定义不匹配

Answer 6

<dynamic prepend=" ">
            <isNotEmpty property="feedBackId" prepend=" AND ">feedBackId = #feedBackId#</isNotEmpty>
            <isNotEmpty property="userId" prepend=" AND ">userName = #userName#</isNotEmpty>
            <isNotEmpty property="userId" prepend=" AND ">title = #title#</isNotEmpty>
            <isNotEmpty property="status" prepend=" AND ">status = #status#</isNotEmpty>
        </dynamic>

Answer 7

dynamic prepend=" ">
            <isNotEmpty property="feedBackId" prepend=" AND ">feedBackId = #feedBackId#</isNotEmpty>
            <isNotEmpty property="userId" prepend=" AND ">userName = #userName#</isNotEmpty>
            <isNotEmpty property="userId" prepend=" AND ">title = #title#</isNotEmpty>
            <isNotEmpty property="status" prepend=" AND ">status = #status#</isNotEmpty>
        </dynamic>
两个表的字段有重名吗？

Answer 8

可能我没说清楚，我重新描述一下问题：当我的 SQL 语句写成 ：SELECT f.feedBackId, u.userId, u.userName,  f.title, f.content, date, f.status或者
select *
都不会报错。也就是说，如果 resultMap 里定义的这些字段，如果有任何一个字段没有查询，就会报错但我这里只需要查询其中的某几个字段就可以了，这种情况要怎么解决

Answer 9

    <resultMap class="feedBackVo" id="feedBackVoResult">
        <result property="feedBackId" column="feedBackId"/>
        <result property="userId" column="userId"/>
        <result property="userName" column="userName"/>
        <result property="title" column="title"/>
        <result property="content" column="content"/>
        <result property="status" column="status"/>
        <result property="date" column="date"/>
    </resultMap>而在查询的语句中只返回了 4个字段 上面的红色字段没有返回，映射不能一一对应，这个必须一一对应    <select id="queryFeedBack" parameterClass="feedBackVo" resultMap="feedBackVoResult">
        SELECT f.feedBackId, u.userName, f.title, f.status 
        FROM feedback f, t_user u
        WHERE f.userId = u.userId
        <dynamic prepend=" ">
            <isNotEmpty property="feedBackId" prepend=" AND ">feedBackId = #feedBackId#</isNotEmpty>
            <isNotEmpty property="userId" prepend=" AND ">userName = #userName#</isNotEmpty>
            <isNotEmpty property="userId" prepend=" AND ">title = #title#</isNotEmpty>
            <isNotEmpty property="status" prepend=" AND ">status = #status#</isNotEmpty>
        </dynamic>
    </select>

Answer 10

重新定义一个 <resultMap class="feedBackVo" id="feedBackVoResult1">
  <result property="feedBackId" column="feedBackId"/>
  <result property="userName" column="userName"/>
  <result property="title" column="title"/>
  <result property="status" column="status"/>
  </resultMap>然后在 <select id="queryFeedBack" parameterClass="feedBackVo" resultMap="feedBackVoResult1">resultMap  可以根据自己的需要定义多个

Answer 11

你在第一种中不要将parameterClass="feedBackVo"和resultMap="feedBackVoResult"属性同时用，去掉一个

Answer 12

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap      
    PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"      
    "http://ibatis.apache.org/dtd/sql-map-2.dtd">
    
<sqlMap namespace="feedBackVo">
    
    <typeAlias alias="feedBackVo" type="net.szpt.ei.obs.vo.FeedBackVo"/>
    
    <resultMap class="feedBackVo" id="feedBackVoResult">
        <result property="feedBackId" column="feedBackId"/>
        <result property="userId" column="userId"/>
        <result property="userName" column="userName"/>
        <result property="title" column="title"/>
        <result property="content" column="content"/>
        <result property="status" column="status"/>
        <result property="date" column="date"/>
    </resultMap>
    <resultMap class="feedBackVo" id="feedBackVoResult1">
        <result property="feedBackId" column="feedBackId"/>
        <result property="userName" column="userName"/>
        <result property="title" column="title"/>
        <result property="status" column="status"/>
    </resultMap>    <select id="queryFeedBack" parameterClass="feedBackVo" resultMap="feedBackVoResult1">
        SELECT f.feedBackId, u.userName, f.title, f.status 
        FROM feedback f, t_user u
        WHERE f.userId = u.userId
        <dynamic prepend=" ">
            <isNotEmpty property="feedBackId" prepend=" AND ">feedBackId = #feedBackId#</isNotEmpty>
            <isNotEmpty property="userId" prepend=" AND ">userName = #userName#</isNotEmpty>
            <isNotEmpty property="userId" prepend=" AND ">title = #title#</isNotEmpty>
            <isNotEmpty property="status" prepend=" AND ">status = #status#</isNotEmpty>
        </dynamic>
    </select>
    
    <select id="queryFeedBackContent" parameterClass="java.lang.Integer" resultMap="feedBackVoResult">
        SELECT *
        FROM feedback f, t_user u
        WHERE f.userId = u.userId
            AND feedBackId = #feedBackId#
    </select></sqlMap>  
这样就可以了 

Answer 13

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap      
    PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"      
    "http://ibatis.apache.org/dtd/sql-map-2.dtd">
    
<sqlMap namespace="feedBackVo">
    
    <typeAlias alias="feedBackVo" type="net.szpt.ei.obs.vo.FeedBackVo"/>
    
    <resultMap class="feedBackVo" id="feedBackVoResult">
        <result property="feedBackId" column="feedBackId"/>
        <result property="userId" column="userId"/>
        <result property="userName" column="userName"/>
        <result property="title" column="title"/>
        <result property="content" column="content"/>
        <result property="status" column="status"/>
        <result property="date" column="date"/>
    </resultMap>
<resultMap class="feedBackVo" id="feedBackVoResult1">
        <result property="feedBackId" column="feedBackId"/>
        <result property="userName" column="userName"/>
        <result property="title" column="title"/>
        <result property="status" column="status"/>
    </resultMap>    <select id="queryFeedBack" parameterClass="feedBackVo" resultMap="feedBackVoResult1">
        SELECT f.feedBackId, u.userName, f.title, f.status 
        FROM feedback f, t_user u
        WHERE f.userId = u.userId
        <dynamic prepend=" ">
            <isNotEmpty property="feedBackId" prepend=" AND ">feedBackId = #feedBackId#</isNotEmpty>
            <isNotEmpty property="userId" prepend=" AND ">userName = #userName#</isNotEmpty>
            <isNotEmpty property="userId" prepend=" AND ">title = #title#</isNotEmpty>
            <isNotEmpty property="status" prepend=" AND ">status = #status#</isNotEmpty>
        </dynamic>
    </select>
    
    <select id="queryFeedBackContent" parameterClass="java.lang.Integer" resultMap="feedBackVoResult">
        SELECT *
        FROM feedback f, t_user u
        WHERE f.userId = u.userId
            AND feedBackId = #feedBackId#
    </select></sqlMap>  

Answer 14

嗯，就是不要将所有的映射都放在一个resultMap中，然后在查时用parameterClass="feedBackVo"和resultMap="feedBackVoResult"这两个属性，一个是从vo，一个是从resultMap，那样会出错

Answer 15

直接resultClass hashmap（虽然官方不建议）