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，不需要加斜杠。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!