hibernate oracle 插入 clob,Oracle CLOB在Hibernate框架下的一次应用

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

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

查询数据

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