jdbc操作Blob和Clob字段与字符串的转换

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/u010454030/article/details/84450159
[b][size=large]Oracle的数据库里对于大字段存储,通常有3种类型,一种就是所谓的Blob类型,另一种就是Clob类型,第三种是NClob类型,关于这三者者的使用范围,应该说没有严格意义上的要求,但大部分场景下我用经常使用Blob存储二进制数据类型的东西,例如图片,单子流等,而使用Clob来存储大型文本数据,例如一篇文献,或一个xml等等,但是更好的情况下建议使用NClob来存储双字节的文本数据,三者的存储方式如下表格所示

[table]
|BLOB|byte|
|CLOB|char|
|NCLOB|double char|
[/table]
使用时候,需要注意,一般使用char类型,或者double char类型,不会出现乱码情况,而使用byte类型存储文本类型,非常容易出现乱码,这一点,笔者深有体会,也许你存文本进BLOB字段时你可能感觉没问题,但是在取出来转换时一些特殊符号,有时候就莫名其妙的乱码了。所以对于文本类型,建议还是使用CLOB,或者NCLOB存储,以避免一些乱码问题.
[/size][/b]

[b][size=large]另外需要注意的是:
如果你的单数据量只有几千个字符,建议按照varchar/varchar2方式存储,这样提高访问速度。但是对于几万或者更多的字符量,建议按照lob(clob/nclob)方式。

以下有几点请注意:
(1)面对插入的情况,应该首先对插入的blob/clob所在字段,赋予一个empty_blob()或empty_clob。再以更新方式,流形式插入
(2)所有的blob/clob对象(jdbc基本标准)没有实现对象序列化,一般第三方(vender)类包可能会重新实现,比如weblogic的jDriver for oracle
(3)在从数据库获取clob/blob字段的时候,不一定就以blob/clob对象字节流获取(有可能就是以其它字节流方式获取) [/size][/b]

[b][size=large]下面给出blob与clob字段,在向数据库插入,以及读取时与字符串互相转换代码[/size][/b]

[b](1) 插入BLOB字段类型[/b]
 File file = new File("E:\\测试图片\\dog.jpg");  
int length = (int)file2.length();
InputStream f = new FileInputStream(file);
ps.setBinaryStream(1, f, length);


[b](2) 数据库插入CLOB字段类型[/b]
 File file = new File("E:\\测试数据\\文献评估.txt");  
int length = (int)file.length();
InputStream f = new FileInputStream(file);
ps.setAsciiStream(1, f, length);

[b](3) BLOB字段转换成String字符串[/b]
 /**
* Blob字段的通用转换
* 注意可能出现乱码
* @return 转好的字符串,
* **/
public String BlobToString(Blob blob){
StringBuffer str=new StringBuffer();
//使用StringBuffer进行拼接
InputStream in=null;//输入字节流
try {
in = blob.getBinaryStream();
//一般接下来是把in的字节流写入一个文件中,但这里直接放进字符串
byte[] buff=new byte[(int) blob.length()];
// byte[] buff=new byte[1024];
// byte[] b = new byte[blob.getBufferSize()];
for(int i=0;(i=in.read(buff))>0;){
str=str.append(new String(buff));
}
return str.toString();


}catch (Exception e) {
e.printStackTrace();
} finally{
try{
in.close();
}catch(Exception e){
System.out.println("转换异常");
e.printStackTrace();
}
}
return null;
}


[b](4) CLOB字段转换成String字符串[/b]
/**
* Clob字段的通用转换
* @return 转好的字符串,
* **/
public static String ClobToString(CLOB clob) throws SQLException, IOException {
String reString = ""; //拼接变量
Reader is = clob.getCharacterStream();// 得到流
BufferedReader br = new BufferedReader(is);
String s = br.readLine();
StringBuffer sb = new StringBuffer();
while (s != null) {
sb.append(s);
s = br.readLine();
}
reString = sb.toString(); //转换成字符串,进行返回
return reString;
}


[b](5) 将字符串转换成BLOB,或者CLOB字段类型[/b]
 /**注意笔者是 Oracle11g
* @param 需要转换的参数
* 字符串转换成BLOB,CLOB,以及BLOB,CLOB转换成字符串
* @throws Exception
*
* */
public void covert(String str) throws Exception{

try {
Clob c = new SerialClob(str.toCharArray());//String 转 clob
Blob b = new SerialBlob(str.getBytes("GBK"));//String 转 blob
// 也可以这样不传字符集名称,默认使用系统的
// Blob b = new SerialBlob(s1.getBytes());

String clobString = c.getSubString(1, (int) c.length());//clob 转 String
String blobString = new String(b.getBytes(1, (int) b.length()),"GBK");//blob 转 String
// 前面若没传入字符集名称,则这里也不需要传入,以免出错
// String blobString = new String(b.getBytes(1, (int) b.length()));

System.out.println(clobString);
System.out.println(blobString);

} catch (SerialException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}


[b][color=red][size=x-large]至此,大字段的数据类型的转换,插入,都可以实现了,另外需要注意的是,笔者的测试是在JDK7的版本和Oracle 11g的数据库下测试的,一些低的版本的JDK有些API可能不一样。[/size][/color][/b]
展开阅读全文

ClobBlob字段的存储问题

08-07

最近写程序时,插入clob字段与blob字段时,老报错,程序如下:各位帮我看看:rnpublic class FileLob extends HibernateUtilrn public FileLob() rn super();rn rnrn /*将文件插入clob*/rn public void InsertClob(InputStream fileInputStream, String filename,rn WriteFile file) throws Exception rn file.setNewscontent(Hibernate.createClob(" "));rn try rn Session session = HibernateUtil.currentSession();rn Transaction tx = null;rn tx=session.beginTransaction();rn session.saveOrUpdate(file); session.flush();rn session.refresh(file, LockMode.UPGRADE);rn rn rn java.sql.Clob clob = (java.sql.Clob)file.getNewscontent();rn BufferedWriter outClob = new BufferedWriter(clob.getCharacterStream());rn if (filename.endsWith(".doc")) rn //FileInputStream inClob = new FileInputStream(infile);rn WordExtractor extractor = new WordExtractor();rn String str = extractor.extractText(fileInputStream);rn outClob.write(str);rn outClob.flush();rn rn else if (filename.endsWith(".pdf")) rn PdfExtractor pf = new PdfExtractor();rn String str = pf.GetTextFromPdf(fileInputStream);rn outClob.write(str);rn outClob.flush();rn rn else if (filename.endsWith(".txt") || filename.endsWith(".htm")) rn // BufferedReader inClob = new BufferedReader(new FileReader(infile));rn BufferedReader inClob = new BufferedReader(new InputStreamReader(fileInputStream));rn int c;rn while ( (c = inClob.read()) != -1) rn outClob.write(c);rn rn inClob.close();rn outClob.close();rn rn else rn // BufferedReader inClob = new BufferedReader(new FileReader(infile));rn BufferedReader inClob = new BufferedReader(new InputStreamReader(fileInputStream));rn int c;rn while ( (c = inClob.read()) != -1) rn outClob.write(c);rn rn inClob.close();rn outClob.close();rn rn endTransaction(true);rn rn catch (Exception he) rn he.printStackTrace();rn if (hsession != null) rn endTransaction(false);rn rn rn rnrn /*将文件插入blob*/rn public void InsertBlob(InputStream fileInputStream, WriteFile file) throwsrn Exception rn file.setNewsimage(Hibernate.createBlob(new byte[1])); //blobrn try rn Session session = HibernateUtil.currentSession();rn Transaction tx = null;rn tx=session.beginTransaction();rn session.saveOrUpdate(file); rn session.flush(); rn session.refresh(file, LockMode.UPGRADE);rn Blob blob = (Blob) file.getNewsimage();rn BufferedOutputStream out = new BufferedOutputStream(blob.getBinaryStream());rn BufferedInputStream in = new BufferedInputStream(fileInputStream);rn byte[] buf = new byte[1024]; //10k缓存rn int len = 0;rn while ( (len = in.read(buf, 0, 1024)) != -1) rn out.write(buf, 0, len);rn rn in.close();rn out.close();rn endTransaction(true);rn rn catch (Exception he) rn he.printStackTrace();rn if (hsession != null) rn endTransaction(false);rn rn rn 论坛

JDBC处理BLOB字段

04-17

通过jdbc odbc桥连接,数据表如下 rnCREATE TABLE try ( rn id tinyint(3) NOT NULL auto_increment, rn name varchar(10) default NULL, rn size varchar(10) default NULL, rn discription text, rn bin blob, rn UNIQUE KEY id (id) rn) rnrnrnrnimport java.sql.Connection; rnimport java.sql.DriverManager; rnimport java.sql.SQLException; rnimport java.sql.Statement; rnimport java.sql.ResultSet; rnimport java.sql.ResultSetMetaData; rnimport java.sql.Blob; rnimport java.sql.Clob; rnimport java.sql.PreparedStatement; rnrnpublic class blobtest extends Object rnrn public static void main (String args[]) rn //For the JDBC-ODBC bridge, use rn //driverName = "sun.jdbc.odbc.JdbcOdbcDriver" rn //and rn //connectURL = "jdbc:odbc:pricing" rn String driverName = "sun.jdbc.odbc.JdbcOdbcDriver"; rn String connectURL = "jdbc:odbc:testdsn"; rn Connection db = null; rn try rn Class.forName(driverName); rn db = DriverManager.getConnection(connectURL); rn catch (ClassNotFoundException e) rn System.out.println("Error creating class: "+e.getMessage()); rn catch (SQLException e) rn System.out.println("Error creating connection: "+e.getMessage()); rn rn PreparedStatement statement = null; rn ResultSet rs = null; rn ResultSetMetaData resultmetadata = null; rnrn try rn statement = db.prepareStatement("select bin from try"); rnrs = statement.executeQuery(); rnresultmetadata = rs.getMetaData(); rnint numCols = resultmetadata.getColumnCount(); rnString colVal = null; rnwhile (rs.next()) rn //Output data by referencing the ResultSet columns by name rnSystem.out.println(rs.getBlob(1));/* 这里会抛出异常 rn异常如下: rn---------- java运行 ---------- rnjava.lang.UnsupportedOperationException rnat sun.jdbc.odbc.JdbcOdbcResultSet.getBlob(JdbcOdbcResultSet.java:4219) rnat blobtest.main(blobtest.java:41) rnException in thread "main" Normal Termination rn输出完成(耗费 0 秒)。 rnrnSystem.out.println(rs.getBinaryStream(5));//这里输出的不是bin(blob类型)的内容*/ rnSystem.out.println(rs.getColumnType(1));/*这里不能运行 rn出错如下: rn---------- javac编译 ---------- rnblobtest.java:43: cannot resolve symbol rnsymbol : method getColumnType (int) rnlocation: interface java.sql.ResultSet rnSystem.out.println(rs.getColumnType(1)); rn ^ rn1 error rnNormal Termination rn输出完成(耗费 2 秒)。*/ rnrn rnrn catch (SQLException e) rn System.out.println("SQL Error: "+e.getMessage()); rn rn finally rn //System.out.println("Closing connections..."); rn try rn db.close(); rn catch (SQLException e) rn //System.out.println("Can't close connection."); rn rn rn rnrnrnrn 论坛

没有更多推荐了,返回首页