ibatis操作clob

港控/mmm° 2022-09-28 11:45 136阅读 0赞

问题主要是插入和更新，我项目中只做了一个插入的例子，更新应该也是可以的

**1.spring的applicationContext.xml的中加入ibatis的sqlmap**

<bean id="sqlMapClient"  class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">  
    <property name="configLocation" value="classpath:sqlMapConfig.xml"/>  
    <property name="dataSource" ref="dataSource"/>  
  </bean>

**2.ibatis的sqlMapConfig配置**

<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE sqlMapConfig      
 PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN"       
    "[http://ibatis.apache.org/dtd/sql-map-config-2.dtd][http_ibatis.apache.org_dtd_sql-map-config-2.dtd]">

**3.download.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][http_ibatis.apache.org_dtd_sql-map-2.dtd]">

//方法1  
 <insert id="insert" parameterClass="DownloadVo">  
  <selectKey resultClass="String" keyProperty="id">  
   SELECT to\_char(SEQ\_DOWNLOAD\_FILE\_LIST.NEXTVAL,'FM0000') AS id FROM DUAL  
  </selectKey>  
  insert into DOWNLOAD\_FILE\_LIST(ID,MERCHANT\_ID,FILENAME,CONTENT,CONTENT\_TYPE)  
  values (\#id:BLOB\#,\#merchantId\#,\#fileName\#,\#content\#,\#contentType\#)

</insert>

//方法2  
 <insert id="insert" parameterClass="DownloadVo">  
  <selectKey resultClass="String" keyProperty="id">  
   SELECT to\_char(SEQ\_DOWNLOAD\_FILE\_LIST.NEXTVAL,'FM0000') AS id FROM DUAL  
  </selectKey>  
  insert into DOWNLOAD\_FILE\_LIST(ID,MERCHANT\_ID,FILENAME,CONTENT,CONTENT\_TYPE)  
  values (\#id:handler=blobHandler\#,\#merchantId\#,\#fileName\#,\#content\#,\#contentType\#)

</insert>

</sqlMap>

PS：使用方法1的时候，将方法2注销掉，反之依然。

方法1：配置的是全局变量，也就是说在sqlMapConfig有多个不同的子xml文件时，配置一个，其他需要操作blob字段的时候，直接使用就可以了

方法2：配置的是单个文件的，要是只有一个dao需要操作blob字段，就只配置该dao对应的sqlmap就可以了。我这次用的是download.xml，所以使用了第二种方法

** 4.OracleBlobTypeHandlerCallback类 ，该类是实现TypeHandlerCallback，ibatis操作clob、blob时，必须实现此类**

import java.sql.SQLException;

import oracle.sql.BLOB;

import com.ibatis.sqlmap.client.extensions.ParameterSetter;  
import com.ibatis.sqlmap.client.extensions.ResultGetter;  
import com.ibatis.sqlmap.client.extensions.TypeHandlerCallback;

public class OracleBlobTypeHandlerCallback implements TypeHandlerCallback \{

public void setParameter(ParameterSetter setter, Object parameter)  
   throws SQLException \{  
  // TODO Auto-generated method stub  
    
 \}

public Object getResult(ResultGetter getter) throws SQLException \{  
     BLOB blob = (BLOB) getter.getClob();  
     if (blob == null || blob.length() == 0) \{  
       return "";  
     \}  
     else  
       return blob.getBytes(1L, (int) blob.length());

public Object valueOf(String s) \{  
  // TODO Auto-generated method stub  
  return null;  
 \}

**5.dao的实现类DownloadDaoImpl ，操作ibatis**

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

import org.springframework.orm.ibatis.SqlMapClientCallback;  
import org.springframework.orm.ibatis.support.SqlMapClientDaoSupport;

import com.huateng.bussiness.download.entity.DownloadVo;  
import com.ibatis.sqlmap.client.SqlMapExecutor;

public class DownloadDaoImpl extends SqlMapClientDaoSupport implements IDownloadDao \{  
   
 private static final String SELECT\_ALL = "download.select\_all";  
 private static final String SELECT\_BY\_ID = "download.select\_by\_id";  
 private static final String INSERT = "download.insert";  
   
 public List<DownloadVo> findAllData() \{  
  return getSqlMapClientTemplate().queryForList(SELECT\_ALL);  
 \}  
   
 public DownloadVo findById(String id) \{  
  return (DownloadVo)getSqlMapClientTemplate().queryForObject(SELECT\_BY\_ID, id);  
 \}  
   
 public void doInsert(List<DownloadVo> tempList) \{  
  final List<DownloadVo> downloadList = tempList;  
  try \{  
   this.getSqlMapClientTemplate().execute(  
     new SqlMapClientCallback<Object>() \{  
      public Object doInSqlMapClient(SqlMapExecutor executor)  
        throws SQLException \{  
       executor.startBatch();  
       for (DownloadVo temp : downloadList) \{  
        executor.insert(INSERT, temp);  
       \}  
       executor.executeBatch();  
       return null;  
      \}  
     \});  
  \} catch (Exception e) \{  
   e.printStackTrace();  
  \}  
 \}  
\}

**6.实体bean  DownloadVo**

public class DownloadVo \{  
 private String id;  
 private int merchantId;  
 private byte\[\] content;  
 private String contentType;  
 private String fileName;  
   
 public String getId() \{  
  return id;  
 \}  
 public void setId(String id) \{  
  this.id = id;  
 \}  
 public int getMerchantId() \{  
  return merchantId;  
 \}  
 public void setMerchantId(int merchantId) \{  
  this.merchantId = merchantId;  
 \}  
 public String getContentType() \{  
  return contentType;  
 \}  
 public void setContentType(String contentType) \{  
  this.contentType = contentType;  
 \}  
 public byte\[\] getContent() \{  
  return content;  
 \}  
 public void setContent(byte\[\] content) \{  
  this.content = content;  
 \}  
 public String getFileName() \{  
  return fileName;  
 \}  
 public void setFileName(String fileName) \{  
  this.fileName = fileName;  
 \}  
\}

[http_ibatis.apache.org_dtd_sql-map-config-2.dtd]: http://ibatis.apache.org/dtd/sql-map-config-2.dtd
[http_ibatis.apache.org_dtd_sql-map-2.dtd]: http://ibatis.apache.org/dtd/sql-map-2.dtd