Oracle CLOB在Hibernate框架下的一次应用

读取更新一张表，表里面有两个超长字段，类型为CLOB，表结构如图所示

由于所用系统Hibernate框架有问题，因此不能使用原生的getSession().get(this.entityClass, id)方法和getSession().saveOrUpdate(entity)方法，因此使用最原始的方法拼接sql做插入操作。

查询数据

public List<PlanDetail> queryPlanDetailList(int pageNo, int pageSize) {
        String querySql = "SELECT iw.finance_type AS \"financeType\", fp.name AS \"name\", iw.creator AS \"createPer\", iw.mender AS \"updatePer\", iw.create_time AS \"createTime\", iw.update_time AS \"updateTime\" FROM t_introduce_web iw, finance_plan fp WHERE iw.finance_type = fp.id";
        List<PlanDetail> list = getSession().createSQLQuery(querySql).setResultTransformer(Transformers.aliasToBean(PlanDetail.class)).setFirstResult((pageNo-1)*pageSize).setMaxResults(pageSize).list();
        return list;
}

上述拼接后querySql为：

SELECT iw.finance_type AS "financeType", fp.name AS "name", iw.creator AS "createPer", iw.mender AS "updatePer", iw.create_time AS "createTime", iw.update_time AS "updateTime" FROM t_introduce_web iw, finance_plan fp WHERE iw.finance_type = fp.id

PlanDetail 为放入数据实体类，sql查询获取的字段名必须与实体类的变量名相同

public class PlanDetail implements Serializable{

    private static final long serialVersionUID = 8109897836500448176L;

    public String id;

    public Clob introduce;

    public String introduceStr;

    public Clob commomQuestion;

    public String commomQuestionStr;

    public String financeType;

    public String createPer;

    public String updatePer;

    public String name;

    public Date createTime;

    public Date updateTime;

    private BigDecimal ROWNUM_;
}

此处有一列名为 ROWNUM_， 为Hibernate取分页数据必须。

插入或修改数据

public void updatePlanDetail(PlanDetail planDetail) throws SerialException, SQLException {
        String commonQuestionStr = planDetail.getCommomQuestionStr();
        String introduceStr = planDetail.getIntroduceStr();
        String procedure = "DECLARE introduce CLOB\\:='" + introduceStr + "';commonQues CLOB\\:='" + commonQuestionStr + "';BEGIN "+"\n";
        String updateSql = "UPDATE t_introduce_web SET commons_problems  = commonQues, introduce_web = introduce WHERE finance_type = '" + planDetail.getFinanceType() + "';";
        String updateSql = procedure + updateSql + "\n end;";
        getSession().createSQLQuery(sql).executeUpdate();
    }

上述拼接后updateSql 为：

DECLARE introduce CLOB\:='clob1';commonQues CLOB\:='clob2';BEGIN 
UPDATE t_introduce_web SET commons_problems  = commonQues, introduce_web = introduce, update_time = to_date('2016-11-10 21:27:01', 'yyyy-MM-dd HH24:mi:ss') WHERE finance_type = '655';
 end;

\:表示hibernate转义符 

如果不加，则为报如下错误 org.hibernate.QueryException: Space is not allowed after parameter prefix ‘:’

如果单独在数据库中执行sql，不需要加斜杠。