BLOB和CLOB都是大字段类型,BLOB是按二进制来存储的,而CLOB是可以直接存储文字的。
其实两个是可以互换的的,或者可以直接用LOB字段代替这两个。但是为了更好的管理
ORACLE数据库,
通常像图片、文件、音乐等信息就用BLOB字段来存储,先将文件转为二进制再存储进去。
而像文章或者是较长的文字,就用CLOB存储,这样对以后的查询更新
存储等操作都提供很大的方便。
CLOB 定义
数据库中的一种保存文件所使用的类型。
Character Large Object
SQL 类型 CLOB 在 JavaTM 编程语言中的映射关系。SQL CLOB 是内置类型,它将字符大对象 (Character Large Object) 存储为数据库表某一行中的一个列值。默认情况下,驱动程序使用 SQL locator(CLOB) 实现 Clob 对象,这意味着 CLOB 对象包含一个指向 SQL CLOB 数据的逻辑指针而不是数据本身。Clob 对象在它被创建的事务处理期间有效。
在一些数据库系统里,也使用Text 作为CLOB的别名,比如SQL Server
BLOB 定义
BLOB (binary large object),二进制大对象,是一个可以存储二进制文件的容器。
在计算机中,BLOB常常是数据库中用来存储二进制文件的字段类型。
BLOB是一个大文件,典型的BLOB是一张图片或一个声音文件,由于它们的尺寸,必须使用特殊的方式来处理(例如:上传、下载或者存放到一个数据库)。
根据Eric Raymond的说法,处理BLOB的主要思想就是让文件处理器(如数据库管理器)不去理会文件是什么,而是关心如何去处理它。
但也有专家强调,这种处理大数据对象的方法是把双刃剑,它有可能引发一些问题,如存储的二进制文件过大,会使数据库的性能下降。在数据库中存放体积较大的多媒体对象就是应用程序处理BLOB的典型例子。
CLOB和BLOB的区别
CLOB使用CHAR来保存数据。 如:保存XML文档。
BLOB就是使用二进制保存数据。 如:保存位图。
JAVA里面对CLOB的操作
在绝大多数情况下,使用2种方法使用CLOB
1 相对比较小的,可以用String进行直接操作,把CLOB看成字符串类型即可
2 如果比较大,可以用 getAsciiStream 或者 getUnicodeStream 以及对应的 setAsciiStream 和 setUnicodeStream 即可
读取数据
ResultSet rs = stmt.executeQuery(“SELECT TOP 1 * FROM Test1”);
rs.next();
Reader reader = rs.getCharacterStream(2);
插入数据
PreparedStatement pstmt = con.prepareStatement(“INSERT INTO test1 (c1_id, c2_vcmax) VALUES (?, ?)”);
pstmt.setInt(1, 1);
pstmt.setString(2, htmlStr);
pstmt.executeUpdate();
更新数据
Statement stmt = con.createStatemet();
ResultSet rs = stmt.executeQuery(“SELECT * FROM test1”);
rs.next();
Clob clob = rs.getClob(2);
long pos = clob.position(“dog”, 1);
clob.setString(1, “cat”, len, 3);
rs.updateClob(2, clob);
rs.updateRow();
JAVA中如何使用oracle中Clob类型字段
原文(全)
https://blog.csdn.net/jungeCSND/article/details/112567383
最近在项目中做接口对接需要把报文保存到数据库,本来声明列为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);