oracle遊标cursor用ibatis擷取

oracle遊标是個非常強大的功能，之前曾經用來擷取資料，卻發現傳回的資料（ResultSet）沒法處理，現在看到一個老項目使用，于是給自己建一個demo。

<select id="getBuyInfoListForProduct" parameterClass="int" resultMap="buyProductResult">
        SELECT t1.*,
       CURSOR (SELECT manager.sitename,
                      manager.sitepic,
                      cps.price,
                      cps.cpsurl,
                      cps.productname,
                      manager.id siteId,
                      manager.imageicon,
                      row_number() over(partition by cps.cpswebsiteid order by cps.price asc) rn
                 FROM cps_dynamic cps, product p, cps_manager manager
                WHERE cps.productid = p.id
                  AND manager.id = cps.cpswebsiteid
                  AND p.id = t1.id
                ORDER BY manager.sitepriority DESC) buyinfolist
  FROM (SELECT t.*
          FROM (SELECT pt.id,
                       nvl(pt.name2, pt.name) productName,
                       getproducturlnew(pt.id) indexUrl,
                       pv.pv30,
                       (SELECT COUNT()
                          FROM cps_dynamic cd
                         WHERE cd.productid = pt.id) bcount

                  FROM product pt, productpageview pv
                 WHERE pt.id = pv.productid
                   AND pt.seriesid = #seriesId#) t
         WHERE bcount >= 
         ORDER BY bcount DESC, pv30 DESC 
    <![CDATA[  ) t1 WHERE rownum<=   ]]>
    </select>
           

很明顯，上面的代碼是sqlmap檔案中sql，其中大家應該可以看到，resultMap才是核心。

<resultMap class="productbing"  id="buyProductResult" >
        <result property="id"       column="id" ></result>
        <result property="indexUrl"     column="indexUrl" ></result>
        <result property="bcount"   column="bcount" ></result>
        <result property="productName"      column="productname" ></result>
        <result property="buyInfoList" column="buyinfolist"
                javaType="java.util.ArrayList"
    typeHandler="dao.impl.handler.ProductBuyInfoTypeHanler" />
    </resultMap>
           

可以看到，cursor對應的java類型被設定為ArrayList，我猜也可以設定為其他集合類型，以後有機會的話會驗證。

下面讓我們看下typeHandler的實作：

import com.ibatis.sqlmap.client.extensions.ParameterSetter;
import com.ibatis.sqlmap.client.extensions.ResultGetter;
import com.ibatis.sqlmap.client.extensions.TypeHandlerCallback;
import model.common.BuyInfo;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ProductBuyInfoTypeHanler implements TypeHandlerCallback {

    @Override
    public Object getResult(ResultGetter resultGetter) throws SQLException {
        ResultSet rsOut = resultGetter.getResultSet();

        try{
            //注意：字元串值就是cursor所在的列名
            return  getProductParamList((ResultSet) rsOut.getObject("buyinfolist"));
        }catch(Exception e){
            return null;
        }
    }

    /**
     * 從參數和傳回值可以很明顯的看到，此函數就是用來将ResultSet轉換為List<T>對象并傳回
     */
    private List<BuyInfo> getProductParamList(ResultSet result) throws SQLException{
        List<BuyInfo> productParams = new ArrayList<BuyInfo>();
        BuyInfo buyInfo = null;
        try{
            while(result.next()){
                buyInfo = new BuyInfo();
                try{
                    //因為公司的資料庫編碼是iso，是以還包括了轉碼
                    buyInfo.setSiteName(getGBKStr(result.getString("sitename")));
                    buyInfo.setProductname(getGBKStr(result.getString("productname")));
                    buyInfo.setSitePic(result.getString("sitepic"));
                    buyInfo.setProductPrice(result.getString("price"));
                    buyInfo.setSiteUrl(result.getString("cpsurl"));
                    buyInfo.setImageicon(result.getString("imageicon"));
                    buyInfo.setRn(Integer.parseInt(result.getString("rn")));
                }catch(Exception e){
                }
                productParams.add(buyInfo);
            }
        }catch(SQLException e){
            throw e;
        }finally{
            if(result != null)
                result.close();
        }

        return productParams;
    }

    private String getGBKStr(String str){
        return new String(str.getBytes("ISO-8859-1"),"GBK"));
    }

    @Override
    public void setParameter(ParameterSetter arg0, Object arg1)
            throws SQLException {
        // TODO Auto-generated method stub
    }

    @Override
    public Object valueOf(String arg0) {
        // TODO Auto-generated method stub
        return null;
    }
}
           

如果大家有什麼問題或建議，歡迎在評論下方指出。