ibatis操作clob

来源：互联网发布：程序员和黑客谁厉害编辑：程序博客网时间：2024/05/21 10:19

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

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">

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">

//方法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;
}
}