最近在项目中做接口对接需要把报文保存到数据库，本来声明列为VARCHAR2类型保存，但是后来在测试的时候发现报文长度过长，orcal中VARCHAR2最大只能保存4000个字符，因此导致错误，后来转为用cloub来保存报文，但是cloub比String操作起来较为麻烦，因此做了一个小demo来整理一下自己实现的方法：

1、建个测试表用来保存，查询数据如下：

-- Create table
create table PRPSSTEST
(
  id             INTEGER not null,
  requestapp     VARCHAR2(20),
  requestappname VARCHAR2(20),
  requestdate    DATE,
  requestdata    CLOB,
  responsedate   DATE,
  responsecode   VARCHAR2(10),
  responsedata   CLOB,
  attr           VARCHAR2(200),
  consumetime    INTEGER
)
tablespace TBS_ZSSALE
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

1、根据cloub字段值查询SQL

例：根据请求报文中包含“111”来查询

select * from prpsstest A where dbms_lob.instr(A.REQUESTDATA,'111',1,1) > 0;
 

2、SSH框架中使用Clob类型字段

实体类如下：

package com.sinosoft.entity;

// Generated by Hibernate Tools 3.2.0.b9 (sinosoft version)

import static javax.persistence.GenerationType.SEQUENCE;

import java.sql.Clob;
import java.util.Date;

import javax.persistence.*;

/**
 * generated by Tools.Don't edit.
 */
@Entity
@Table(name = "PRPSSTEST")
public class PrpsSTest implements java.io.Serializable {
	
	private static final long serialVersionUID = 1L;
	private long id;
	private String requestapp;
	private String requestappname;
    private Date requestdate;
    private Clob requestdata;
    private Date responsedate;
    private String responsecode;
    private Clob responsedata;
    private String attr;
    private int consumetime;
	
	
	
	@SequenceGenerator(name = "generator", allocationSize = 1, sequenceName = "SEQ_prpsstest")
	@Id
	@GeneratedValue(strategy = SEQUENCE, generator = "generator")
	@Column(name = "id")
	public long getId() {
		return id;
	}
	public void setId(long id) {
		this.id = id;
	}
	
	@Column(name = "REQUESTAPP")
	public String getRequestapp() {
		return requestapp;
	}
	public void setRequestapp(String requestapp) {
		this.requestapp = requestapp;
	}
	
	@Column(name = "REQUESTAPPNAME")
	public String getRequestappname() {
		return requestappname;
	}
	public void setRequestappname(String requestappname) {
		this.requestappname = requestappname;
	}
	
	@Column(name = "REQUESTDATE")
	public Date getRequestdate() {
		return requestdate;
	}
	public void setRequestdate(Date requestdate) {
		this.requestdate = requestdate;
	}
	
	@Column(name = "REQUESTDATA")
	public Clob getRequestdata() {
		return requestdata;
	}
	public void setRequestdata(Clob requestdata) {
		this.requestdata = requestdata;
	} 
	
	
	@Column(name = "RESPONSEDATE")
	public Date getResponsedate() {
		return responsedate;
	}
	
	public void setResponsedate(Date responsedate) {
		this.responsedate = responsedate;
	}
	
	@Column(name = "RESPONSECODE")
	public String getResponsecode() {
		return responsecode;
	}
	public void setResponsecode(String responsecode) {
		this.responsecode = responsecode;
	}
	
	@Column(name = "RESPONSEDATA")
	public Clob getResponsedata() {
		return responsedata;
	}
	public void setResponsedata(Clob responsedata) {
		this.responsedata = responsedata;
	} 
	
	
	@Column(name = "ATTR")
	public String getAttr() {
		return attr;
	}
	
	public void setAttr(String attr) {
		this.attr = attr;
	}
	
	@Column(name = "CONSUMETIME")
	public int getConsumetime() {
		return consumetime;
	}
	public void setConsumetime(int consumetime) {
		this.consumetime = consumetime;
	}
 
}

1，java中给cloub字段赋值：

Hibernate.createClob("111")

2、HQL根据CLOUB类型查询条件语句，与sql语句大同小异

3、查询出来的CLOUB字段转为String

public static String ClobToString(Clob clob) {
        String clobStr = "";
        Reader is = null;
        try {
            is = clob.getCharacterStream();
	        BufferedReader br = new BufferedReader(is);
	        String s = null;
            s = br.readLine();
	        StringBuffer sb = new StringBuffer();
	        while (s != null) {
	            sb.append(s);
	            s = br.readLine();
	        }
	        clobStr = sb.toString();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return clobStr;
    }

3、SpingBoot JPA框架中使用Clob类型字段

实体类如下：这里为了简便 直接使用String对应数据库中cloub字段

package com.sinosoft.entity;

// Generated by Hibernate Tools 3.2.0.b9 (sinosoft version)

import static javax.persistence.GenerationType.SEQUENCE;

import java.sql.Clob;
import java.util.Date;

import javax.persistence.*;

/**
 * generated by Tools.Don't edit.
 */
@Entity
@Table(name = "PRPSSTEST")
public class PrpsSTest implements java.io.Serializable {
	
	private static final long serialVersionUID = 1L;
	private long id;
	private String requestapp;
	private String requestappname;
    private Date requestdate;
    private String requestdata;
    private Date responsedate;
    private String responsecode;
    private String responsedata;
    private String attr;
    private int consumetime;

	
	@SequenceGenerator(name = "generator", allocationSize = 1, sequenceName = "SEQ_prpsstest")
	@Id
	@GeneratedValue(strategy = SEQUENCE, generator = "generator")
	@Column(name = "id")
	public long getId() {
		return id;
	}
	public void setId(long id) {
		this.id = id;
	}
	
	@Column(name = "REQUESTAPP")
	public String getRequestapp() {
		return requestapp;
	}
	public void setRequestapp(String requestapp) {
		this.requestapp = requestapp;
	}
	
	@Column(name = "REQUESTAPPNAME")
	public String getRequestappname() {
		return requestappname;
	}
	public void setRequestappname(String requestappname) {
		this.requestappname = requestappname;
	}
	
	@Column(name = "REQUESTDATE")
	public Date getRequestdate() {
		return requestdate;
	}
	public void setRequestdate(Date requestdate) {
		this.requestdate = requestdate;
	}
	@Lob
	@Basic(fetch = FetchType.EAGER)
	@Column(name="REQUESTDATA", columnDefinition="CLOB", nullable=true)
	public String getRequestdata() {
		return requestdata;
	}

	public void setRequestdata(String requestdata) {
		this.requestdata = requestdata;
	}

	@Column(name = "RESPONSEDATE")
	public Date getResponsedate() {
		return responsedate;
	}
	
	public void setResponsedate(Date responsedate) {
		this.responsedate = responsedate;
	}
	
	@Column(name = "RESPONSECODE")
	public String getResponsecode() {
		return responsecode;
	}
	public void setResponsecode(String responsecode) {
		this.responsecode = responsecode;
	}

	@Lob
	@Basic(fetch = FetchType.EAGER)
	@Column(name="RESPONSEDATA", columnDefinition="CLOB", nullable=true)
	public String getResponsedata() {
		return responsedata;
	}

	public void setResponsedata(String responsedata) {
		this.responsedata = responsedata;
	}

	@Column(name = "ATTR")
	public String getAttr() {
		return attr;
	}
	
	public void setAttr(String attr) {
		this.attr = attr;
	}
	
	@Column(name = "CONSUMETIME")
	public int getConsumetime() {
		return consumetime;
	}
	public void setConsumetime(int consumetime) {
		this.consumetime = consumetime;
	}
 
}

1、使用JPA根据Cloub字段值查询语句：

@Query(value = "select * from  PrpsSTest where  dbms_lob.instr(requestdata,?1,1,1) > 0 ",nativeQuery = true)
public PrpsSTest getTestByRequestData(String usercode);