ibatis 参考

<!--FriendResult -->
  <select id="getList" resultMap="FriendResult">
      select f.userid1,f.userid2,f.state1,f.subdate,f.cardno,b.grade,f.nickname,f.gender,f.age,f.city,b.username,b.userid,b.city,b.state,b.gender,b.cardno,d.*  from friend f  ,basicuserinfo b,detailuserinfo d where  b.userid = f.userid2 and f.userid2=d.userid and f.state1=#state1#
    <dynamic prepend="and">
        <isNotEqual prepend="and" property="userid1" comparevalue="0">
            f.userid1=#userid1#
        </isNotEqual>
        <isNotEqual prepend="and" property="userid2" comparevalue="0">
            f.userid2=#userid2#
        </isNotEqual>

    </dynamic>
  </select>



                 结合IBATIS的LIST遍历实现模糊查询
如何结合IBATIS的LIST遍历实现模糊查询
实例如下:
<iterate prepend="AND" property="keyList" conjunction="AND">
(A.message like #keyList[]# OR A.title like #keyList[]#)
</iterate>

模糊查询如何实现:%#keyList[]# %,’%#keyList[]# %’,本人均试过,均不成功,望高手指点.


可以试试
<iterate prepend="AND" property="keyList" conjunction="AND">
(A.message like’%$keyList[]$%’ OR A.title like ’%$keyList[]$%’)
</iterate>
2004年3月29日 22:57


ibatis模糊查询的实现
ibatis有两种方式，一种是＃，一种是$。用preparestatment来实现的时候是这样的。凡是#的，都作为参数，用setobject方式。而$方式的，则直接替换字符串。
所以。
select col1,col2 from table1 where col1=#col1# and col2 like ’$col2$’
假如参数是col1=2 col2=2
最终的SQL就是 select col1,col2 from table1 where col1=? and col2 like ’2’

如果需要通符，则用 like ’%$col2$%’
2004年3月29日 22:55

select * from table
where name LIKE ’%’ || #username# || ’%’

  -------------------------------------------------------------

import com.ibatis.sqlmap.client.SqlMapClient;
import com.entel.util.SqlMapManager;

public List getMessageList(String messageState) {
        List list = null;
        try {
            SqlMapClient client = SqlMapManager.getClient();
            Message message = new Message();
            message.setMessageState(messageState);
            list = client.queryForList("getMessageList", message);
        }
        catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

public boolean delMessage(Message message) {
        try {
            SqlMapClient client = SqlMapManager.getClient();
            int res = client.delete("delMessage", message);
            System.out.println(res);
        }
        catch (Exception e) {
            e.printStackTrace();
        }
        return true;
    }

    public Message getMessage(int messageId) {
        Message message = new Message();
        message.setMessageTitle("title");
        message.setMessage("asdfasfdasfdasfdasf");
        return message;
    }
    public boolean sendMessage(Message message ) throws Exception{

       try {
           SqlMapClient client = SqlMapManager.getClient();
           Object res= client.insert("insertMessage", message);
           System.out.println(res);
       }
       catch (Exception e) {
           e.printStackTrace();
       }
        return true;
    }
    public boolean sendMessageByListUser(Message message) throws Exception{

        String receiveUser = message.getReceiveUser();
        String sendUser = message.getSendUser();
        UserDAO  userDao = new UserDAO();
        ArrayList receiveUserIdList = userDao.getUserIdListByUsername(receiveUser);
        ArrayList sendUserIdList = userDao.getUserIdListByUsername(receiveUser);
        int sendUserId = ( (Integer)receiveUserIdList.get(0)).intvalue();

        int receiveUserId=0;
        boolean success =true;
        for(int i=0 ;i<receiveUserIdList.size();i++){
            receiveUserId =( (Integer)receiveUserIdList.get(i)).intvalue();
            message.setSendUserid(sendUserId);
            message.setReceiveUserid(receiveUserId);
            if(!sendMessage(message)) success = false;
        }
        return success;
    }
    public int getMessageStatics(String messageState){
        int count =0 ;
        try {
          SqlMapClient client = SqlMapManager.getClient();
          Object res= client.queryForObject("countMessage", messageState);
          count = Integer.parseInt(res.toString());
      }
      catch (Exception e) {
          e.printStackTrace();
          count =0;
      }
        return count;
    }

  <resultMap id="AlbumPhoto" class="com.entel.home.domain.AlbumPhoto">
  <result property="id"       column="id" />
  <result property="username"      column="username" />
  <result property="title"   column="title" />
  <result property="URL"         column="url"   />
  <result property="intro"    column="intro" />
         <result property="uploadTime"    column="uploadtime" />
  <result property="point"    column="point" />
  </resultMap>

  <insert id="insertAlbumPhoto">  
 <selectKey resultClass="int" keyProperty="id">
  select photograph_id.nextval as id from dual
 </selectKey>
        insert into photograph(id, username ,title ,url,intro,uploadtime,point)
        values(#id#,#username#,#title#,’URL’,#intro#,’#uploadTime#’,#point#)
  </insert>



   <select id="countMessage" parameterClass ="string" resultClass="int">
 select count(messageid) as value from message where messagestate =#value#
   </select>
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sql-map
PUBLIC "-//iBATIS.com//DTD SQL Map 1.0//EN"
"http://www.ibatis.com/dtd/sql-map.dtd">
<sql-map name="attachment">
<!-- =============================================
    mapped-statement insert
============================================= -->
<dynamic-mapped-statement name="insertattachmentDao"  >
   insert into ATTACHMENT
    (   <dynamic prepend="">
      <isPropertyAvailable prepend="," property="ATTACHID" >
         <isNotNull prepend="" property="ATTACHID" >
             ATTACHID
         </isNotNull>
      </isPropertyAvailable>
       <isPropertyAvailable prepend="," property="ATTACHDESC" >
         <isNotNull prepend="" property="ATTACHDESC" >
             ATTACHDESC
         </isNotNull>
      </isPropertyAvailable>
</dynamic>
</dynamic-mapped-statement>
<dynamic prepend="and">
        <isNotEqual prepend="and" property="userid1" comparevalue="0">
            f.userid1=#userid1#
        </isNotEqual>
        <isNotEqual prepend="and" property="userid2" comparevalue="0">
            f.userid2=#userid2#
        </isNotEqual>
    </dynamic>
<result property="cardno" column="cardno" nullvalue="-999"/>
<dynamic-mapped-statement name="getSqlLogStatistics" cache-model="sqllog-cache"
         result-map="sqllog-hashmap-result" >
          SELECT * FROM (SELECT ROWNUM count_row_num, w_o_l_f_w.* FROM (
          select * from (
               select PARSED_SQL,COUNT(*) CNT from SQL_STMT
            <dynamic prepend="WHERE">
<isNotNull prepend="AND" property="exetimestart">
                <![CDATA[EXE_TIME >= #exetimestart#]]>
              </isNotNull>
           <isNotNull prepend="AND" property="exetimeend">
             <![CDATA[ EXE_TIME <= #exetimeend# ]]>
              </isNotNull>
              <isNotEmpty prepend="AND" property="sql">
                (SQL like ’%’||#sql#||’%’ or PARSED_SQL like ’%’||#sql#||’%’ )
              </isNotEmpty>
            </dynamic>
            <dynamic prepend="HAVING">
                <isNotEmpty property="countfrom">
                    <![CDATA[ COUNT(*) >= #countfrom# ]]>
                </isNotEmpty>
                <isNotEmpty prepend="AND" property="countto">
                    <![CDATA[ COUNT(*) <= #countto# ]]>
                </isNotEmpty>
            </dynamic>
            GROUP by PARSED_SQL
        
          <![CDATA[
          ) order by cnt  desc  ) w_o_l_f_w
           WHERE ROWNUM < #__EndPoint#)
          WHERE (count_row_num >= #__StartPoint#)
        ]]>
      </dynamic-mapped-statement>
<parameter-map name="insert-params">
        <property name="exeTime" />
        <property name="sql"/>
        <property name="parsedSql"/>
        <property name="sqlvalues"/>
      </parameter-map>
      <mapped-statement name="insertSql" parameter-map="insert-params" >
        insert into SQL_STMT (
          ID,
          EXE_TIME,
          SQL,
          PARSED_SQL,
          SQL_valueS)
         values (
         <!--注意这里，可以使用数据库本身的功能，不受限制-->
          seq_sql_stmt.nextval, ?, ?, ?,?  
        )
      </mapped-statement>
<isNotNull prepend="AND" property="exetimeend">
             <![CDATA[ EXE_TIME <= #exetimeend# ]]>
              </isNotNull>
              <isNotEmpty prepend="AND" property="sql">
                (SQL like ’%’||#sql#||’%’ or PARSED_SQL like ’%’||#sql#||’%’ )
              </isNotEmpty>
            </dynamic>
            <dynamic prepend="HAVING">
                <isNotEmpty property="countfrom">
                    <![CDATA[ COUNT(*) >= #countfrom# ]]>
                </isNotEmpty>
                <isNotEmpty prepend="AND" property="countto">
                    <![CDATA[ COUNT(*) <= #countto# ]]>
                </isNotEmpty>
            </dynamic>
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sql-map PUBLIC "-//iBATIS.com//DTD SQL Map 1.0//EN" "http://www.ibatis.com/dtd/sql-map.dtd">

<sql-map name="EmployeeSQL">
    
    <cache-model name="employee_cache" reference-type="WEAK">
        <flush-interval   hours="24"/>
        <flush-on-execute statement= "insertEmployee" />
        <flush-on-execute statement= "updateEmployee" />
    </cache-model>
  
    <result-map name="employee_result"  class="net.reumann.Employee">
        <property name="id"             column="emp_id"/>
        <property name="name"           column="name"/>
        <property name="deptId"         column="dept_id"/>
        <property name="deptName"       column="dept_name"/>
        <property name="location"       column="location_id" mapped-statement="getLocationById"/>
    </result-map>
    
    <mapped-statement   name="insertEmployee">
        INSERT INTO employee ( id, name, dept_id, location_id )
        valueS  ( #id#, #name#, #deptId#, #locationId# )
    </mapped-statement>
    
    <mapped-statement   name="updateEmployee">
        UPDATE employee SET name = #name#, dept_id = #deptId#, location_id = #locationId#
        WHERE id = #id#
    </mapped-statement>
    
    <mapped-statement  name="getLocationById" result-class="net.reumann.Location">
        SELECT id, name FROM location where id = #value#  
    </mapped-statement>
    
    <dynamic-mapped-statement name="selectEmployeesFromSearch" result-map="employee_result" cache-model="employee_cache">
        SELECT employee.id AS emp_id,
            employee.name AS name,
            employee.dept_id AS  dept_id,
            department.name AS dept_name,
            employee.location_id AS location_id,
            location.name AS location_name
        FROM
            employee, department, location
        WHERE
            employee.dept_id = department.id AND
            employee.location_id = location.id  
        <dynamic>
            <isNotEmpty prepend=" AND " property="id">
                employee.id = #id#    
            </isNotEmpty>
            <isNotEmpty prepend=" AND " property="name">
                employee.name = #name#    
            </isNotEmpty>
            <isNotEmpty prepend=" AND " property="deptId">
                employee.dept_id = #deptId#    
            </isNotEmpty>
            <isNotEmpty prepend=" AND " property="locationId">
                employee.location_id = #locationId#    
            </isNotEmpty>
        </dynamic>
    </dynamic-mapped-statement>
</sql-map>
<cache-model name="employee_cache" reference-type="WEAK">
        <flush-interval   hours="24"/>
        <flush-on-execute statement= "insertEmployee" />
        <flush-on-execute statement= "updateEmployee" />
</cache-model>
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sql-map PUBLIC "-//iBATIS.com//DTD SQL Map 1.0//EN" "http://www.ibatis.com/dtd/sql-map.dtd">

<sql-map name="LabelvalueSQL">
    
    <cache-model name="label_value_cache" reference-type="STRONG">
        <flush-interval  hours="48"/>
    </cache-model>
    
    <result-map name="label_value_result"       class="net.reumann.Labelvalue">
        <property name="value"                  column="id" />
        <property name="label"                  column="name" />
    </result-map>
    
    <mapped-statement   name="selectAllDepartments"   result-map="label_value_result" cache-model="label_value_cache">
        SELECT  id, name FROM department ORDER BY  name
    </mapped-statement>
  
    <mapped-statement   name="selectAllLocations"   result-map="label_value_result" cache-model="label_value_cache">
        SELECT  id, name FROM location ORDER BY name
    </mapped-statement>
    
</sql-map>

<resultMap id="FriendResult" class="com.entel.home.domain.Friend">
    <result property="userid1" column="USERID1"/>
    <result property="userid2" column="USERID2"/>
    <result property="state1" column="STATE1" />
    <result property="subdate" column="SUBDATE"/>
    <result property="userid" column="userid" nullvalue="-999"/>
    <result property="username" column="username" nullvalue="-999"/>
    <result property="gender" column="gender" nullvalue="-999"/>
    <result property="cardno" column="cardno" nullvalue="-999"/>
 <result property="nickname" column="nickname" nullvalue="-999"/>
    <result property="city" column="city" nullvalue="-999"/>
    <result property="grade" column="grade" nullvalue="-999"/>
    <result property="state" column="state" nullvalue="-999"/>
  </resultMap>
<insert id="add" parameterMap="AccountAddParam"> <![CDATA[

        DECLARE

            n_count NUMBER(1);

        BEGIN

            SELECT COUNT(*) INTO n_count

            FROM T_ACCOUNT t

            WHERE t.S_LOGINNAME=?;

            

            IF n_count>0 THEN

                RAISE_APPLICATION_ERROR(-20000,’用户帐号已存在’);

            ELSE

                INSERT INTO

                    T_ACCOUNT(ACCOUNT_ID,S_LOGINNAME,S_PASSWORD,N_ISACTIVE,

                    S_TRUENAME,S_EMAIL,N_REGTIME,S_DESC)

                valueS(?,?,?,?,?,?,?,?);

            END IF;

        END;

        ]]> </insert>

ibatis中执行pl/sql语句块的测试
配置文件：

<?xml version="1.0" encoding="GBK"?>

<!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN"

    "http://www.ibatis.com/dtd/sql-map-2.dtd">

<sqlMap namespace="Test">

    <update id="update"><![CDATA[

        declare

          n_count number;

        begin

             select count(*) into n_count from t_account;

             update t_auth set s_authdesc=’记录数：’||n_count;

        end;

        ]]> </update>

</sqlMap>



测试代码：

public class Test

{



    public static void main(String[] args)

    {

        SqlMapClient sqlMap = SqlMapConfig.getSqlMap();

        try

        {

            sqlMap.startTransaction();

            sqlMap.update("Test.update", null);

            sqlMap.commitTransaction();

        }

        catch (SQLException e)

        {

            e.printStackTrace();

        }

        finally

        {

            try

            {

                sqlMap.endTransaction();

            }

            catch (SQLException e)

            {

                e.printStackTrace();

            }

        }

    }

}

<!—Oracle SEQUENCE Example -->
<insert id="insertProduct-ORACLE" parameterClass="com.domain.Product">
    <selectKey resultClass="int" keyProperty="id" >
    SELECT STOCKIDSEQUENCE.NEXTVAL AS ID FROM DUAL
    </selectKey>
    insert into PRODUCT (PRD_ID,PRD_DESCRIPTION)
        values (#id#,#description#)
</insert>


<!— Microsoft SQL Server IDENTITY Column Example -->
<insert id="insertProduct-MS-SQL" parameterClass="com.domain.Product">
    insert into PRODUCT (PRD_DESCRIPTION)
        values (#description#)
    <selectKey resultClass="int" keyProperty="id" >
        SELECT @@IDENTITY AS ID
    </selectKey>
</insert>