最近在使用JDBC来访问数据,处理LOB字段的时候出现了问题,Google了一下,发现下面写得比较好的文章,非常值得借鉴,文章出处:
https://blog.jooq.org/2015/04/27/lets-review-how-to-insert-clob-or-blob-via-jdbc/
LOBs are a PITA in all databases, as well as in JDBC. Handling them correctly takes a couple of lines of code, and you can be sure that you’ll get it wrong eventually. Because you have to think of a couple of things:
- Foremost, LOBs are heavy resources that need special lifecycle management. Once you’ve allocated a LOB, you better “free” it as well to decrease the pressure on your GC. This article shows more about why you need to free lobs
- The time when you allocate and free a lob is crucial. It might have a longer life span than any of your
ResultSet
,PreparedStatement
, orConnection
/ transaction. Each database manages such life spans individually, and you might have to read up the specifications in edge cases - While you may use
String
instead ofClob
, orbyte[]
instead ofBlob
for small to medium size LOBs, this may not always be the case, and may even lead to some nasty errors, like Oracle’s dreaded ORA-01461: can bind a LONG value only for insert into a LONG column
So, if you’re working on a low level using JDBC (instead of abstracting JDBC via Hibernate or jOOQ), you better write a small utility that takes care of proper LOB handling.
We’ve recently re-discovered our own utility that we’re using for jOOQ integration testing, at least in some databases, and thought this might be very useful to a couple of our readers who operate directly with JDBC. Consider the following class:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
|
public class LOB implements AutoCloseable {
private final Connection connection;
private final List<Blob> blobs;
private final List<Clob> clobs;
public LOB(Connection connection) {
this .connection = connection;
this .blobs = new ArrayList<>();
this .clobs = new ArrayList<>();
}
public final Blob blob( byte [] bytes)
throws SQLException {
Blob blob;
// You may write more robust dialect
// detection here
if (connection.getMetaData()
.getDatabaseProductName()
.toLowerCase()
.contains( "oracle" )) {
blob = BLOB.createTemporary(connection,
false , BLOB.DURATION_SESSION);
}
else {
blob = connection.createBlob();
}
blob.setBytes( 1 , bytes);
blobs.add(blob);
return blob;
}
public final Clob clob(String string)
throws SQLException {
Clob clob;
if (connection.getMetaData()
.getDatabaseProductName()
.toLowerCase()
.contains( "oracle" )) {
clob = CLOB.createTemporary(connection,
false , CLOB.DURATION_SESSION);
}
else {
clob = connection.createClob();
}
clob.setString( 1 , string);
clobs.add(clob);
return clob;
}
@Override
public final void close() throws Exception {
blobs.forEach(JDBCUtils::safeFree);
clobs.forEach(JDBCUtils::safeFree);
}
} |
This simple class has some nice treats:
- It’s
AutoCloseable
, so you can free your lobs with the try-with-resources statement - It abstracts over the creation of LOBs across SQL dialects. No need to remember the Oracle way
To use this class, simply write something like the following:
1
2
3
4
5
6
7
8
9
|
try (
LOB lob = new LOB(connection);
PreparedStatement stmt = connection.prepareStatement(
"insert into lobs (id, lob) values (?, ?)" )
) { stmt.setInt( 1 , 1 );
stmt.setClob( 2 , lob.clob( "abc" ));
stmt.executeUpdate();
} |
That’s it! No need to keep references to the lob, safely freeing it if it’s not null, correctly recovering from exceptions, etc. Just put the LOB
container in the try-with-resources statement, along with the PreparedStatement
and done.
If you’re interested in why you have to call Clob.free()
or Blob.free()
in the first place, read our article about it. It’ll spare you one or twoOutOfMemoryErrors
相关推荐
clob和blob在jdbc的应用~~~~~~
CLOB、BLOB___CLOB与BLOB的区别
DELPHI的CLOB和BLOB存取处理
Mybatis 处理 CLOB、BLOB 类型数据
Oracle导出Clob,Blob等大字段工具,自己写的工具,方便大家下载使用
JDBC中操作Blob、Clob等对象 实例详细JDBC中操作Blob、Clob等对象 实例详细JDBC中操作Blob、Clob等对象 实例详细JDBC中操作Blob、Clob等对象 实例详细JDBC中操作Blob、Clob等对象 实例详细JDBC中操作Blob、Clob等...
java中(注解符)处理Clob(blob)类型
修改clob blob 字段 修改clob blob 字段 修改clob blob 字段
JDBC中操作Blob、Clob等对象
简单写的一个小工具,把图片存入oracle中,按clob和blob两种方式存储,并读取图片
oracle中使用jdbc读写clob字段,很多细节介绍,内容全面。
Oracle导出Clob,Blob工具 ,支持导出CLob工具版本2,解决上个版本导出时间有问题的bug
CLOB与BLOB的存储与读取,String与CLOB的想换转换,字节码文件的存储与读取等等
oracle的jdbc驱动程序,用这个版本的操作Clob,blob类型的数据很方便。 博文链接:https://zhenjw.iteye.com/blog/173419
NULL 博文链接:https://shihuan830619.iteye.com/blog/1662937
spring 中对大数据的处理,包括clob,blob的数据。比之jdbc下简便很多。
UTL_RAW.CAST_TO_VARCHAR2
完整通过JAVA读写ORACLE中CLOB、BLOB字段的方法,提供数据库连接池的方法
Oracle,Clob,Blob数据操作例子