oracle存储大数据类型(Clob/Blob)
oracle本身支持对大数据类型的各种处理，但是平常大家可能用的不多，其中clob(一般用于大类型的字符型的存取)和blob(一般用于大类型的二进制类型的存取)我想应该就是用的比较少的。所以我写下这篇博文，主要是对这两个数据类型的文件进行的操作，如，保存这类文件进入数据库，读取这类文件到本地磁盘。本来按照老习惯是该先上效果图的，可惜我的机器现在巨卡，10g跑起来基本让我泪流满面，所以我就偷下懒，图就给省略了 -_-!
第一步，新建一个java项目(为啥是java项目而不是web呢，主要因为它便于测试，出效果也快...)orclTest
第二步，新建一个数据库连接的类，我这里是com.test.InitDB.java，代码如下：
class InitDB{ private static Connection con = null; private static Statement stmt = null; private static ResultSet rs = null; //链接oracle数据库 InitDB() { try { Class.forName("oracle.jdbc.driver.OracleDriver").newInstance(); String url = "jdbc:oracle:thin:@localhost:1521:YYSMID"; String user = "zhangsan"; String password = "Lc123456"; con = (Connection) DriverManager.getConnection(url, user, password); InitDB.setCon(con); } catch (Exception e) { e.printStackTrace(); } } public void closCon() { try { con.close(); } catch (Exception e) { e.printStackTrace(); } } public void stmt() { try { con.close(); } catch (Exception e) { e.printStackTrace(); } } public void rs() { try { con.close(); } catch (Exception e) { e.printStackTrace(); } } public static Connection getCon() { return con; } public static void setCon(Connection con) { InitDB.con = con; } public static ResultSet getRs() { return rs; } public static void setRs(ResultSet rs) { InitDB.rs = rs; } public static Statement getStmt() { return stmt; } public static void setStmt(Statement stmt) { InitDB.stmt = stmt; }} 第三步，以及接下来的几步，我们来分别编写clob和blob的插入数据库和读出数据库的代码，新建com.test.InsertBlobData.java，顾名思义，它是用来插入blob类型数据的类。代码如下：
class InsertBlobData{ private ResultSet rs = null; private InitDB idb = null; InsertBlobData() { idb = new InitDB(); } public void insertBlob(String sql1) throws SQLException { Connection con = idb.getCon(); try { con.setAutoCommit(false);// 不设置自动提交 BLOB blob = null; // 插入空的Blob PreparedStatement pstmt = con .prepareStatement("insert into cdl_test(sid,img) values(?,empty_blob())"); pstmt.setString(1, "100"); pstmt.executeUpdate(); pstmt.close(); rs = con.createStatement().executeQuery(sql1); while (rs.next()) { System.out.println("rs length is:"); oracle.sql.BLOB b = (oracle.sql.BLOB) rs.getBlob("img"); System.out.println("cloblength is:" + b.getLength()); File f = new File("d:\\1.jpg"); //1.jpg一张QQ的截图 System.out.println("file path is:" + f.getAbsolutePath()); BufferedInputStream in = new BufferedInputStream( new FileInputStream(f)); BufferedOutputStream out = new BufferedOutputStream(b .getBinaryOutputStream()); int c; while ((c = in.read()) != -1) { out.write(c); } in.close(); out.close(); } con.commit(); } catch (Exception e) { con.rollback();// 出错回滚 e.printStackTrace(); } }}
第四步，新建com.test.InsertClobData.java，用于插入clob数据类型的，代码如下：
class InsertClobData{ private ResultSet rs = null; private InitDB idb = null; InsertClobData() { idb = new InitDB(); } public void insertClob(String sql1) throws SQLException { Connection con = idb.getCon(); try { con.setAutoCommit(false);// 不设置自动提交 BLOB blob = null; // 插入空的Clob PreparedStatement pstmt = con .prepareStatement("insert into cdl_test(sid,doc) values(?,empty_clob())"); pstmt.setString(1, "101"); pstmt.executeUpdate(); pstmt.close(); rs = con.createStatement().executeQuery(sql1); while (rs.next()) { System.out.println("sdfasdfas"); oracle.sql.CLOB cb = (oracle.sql.CLOB) rs.getClob("doc"); File f = new File("d:\\1.txt"); //1.txt一本小说《风云》马荣成 System.out.println("file path is:" + f.getAbsolutePath()); BufferedWriter out = new BufferedWriter(cb .getCharacterOutputStream()); BufferedReader in = new BufferedReader(new FileReader(f)); int c; while ((c = in.read()) != -1) { out.write(c); } in.close(); out.close(); } con.commit(); } catch (Exception e) { con.rollback();// 出错回滚 e.printStackTrace(); } }}
第五步，新建com.test.ReadBlobData.java，用于读取blob类型的数据，代码如下：
class ReadBlobData{ private ResultSet rs = null; private InitDB idb = null; ReadBlobData() { idb = new InitDB(); } public void getBlob(String sql2) throws SQLException { Connection con = idb.getCon(); con.setAutoCommit(false); try { System.out.println("sq2 is:" + sql2); System.out.println("stmt is:" + con); rs = con.createStatement().executeQuery(sql2); while (rs.next()) { System.out.println("rs length is:"); Blob b = (Blob) rs.getBlob("img"); File f = new File("D:\\1.jpg"); FileOutputStream fos = new FileOutputStream(f); InputStream is = b.getBinaryStream();// 读出数据后转换为二进制流 byte[] data = new byte; while (is.read(data) != -1) { fos.write(data); } fos.close(); is.close(); } con.commit();// 正式提交 } catch (Exception e) { e.printStackTrace(); } finally { // rs.close(); } }}
第六步，新建com.test.ReadClobData.java，用于读取clob类型的数据，代码如下：
class ReadClobData{ private ResultSet rs = null; private InitDB idb = null; ReadClobData() { idb = new InitDB(); } public void getClob(String sql2) throws SQLException { Connection con = idb.getCon(); try { con.setAutoCommit(false);// 不设置自动提交 System.out.println("sq2 is:" + sql2); rs = con.createStatement().executeQuery(sql2); while (rs.next()) { oracle.sql.CLOB clob = (oracle.sql.CLOB) rs.getClob("doc"); File f = new File("d:\\1.txt"); BufferedReader in = new BufferedReader(clob .getCharacterStream()); // setCharacterStream()方法，可用于将CLOB字段与字节流相关联， BufferedWriter out = new BufferedWriter(new FileWriter(f)); int c; while ((c = in.read()) != -1) { out.write(c); } out.close(); in.close(); } con.commit();// 正式提交 rs.close(); } catch (Exception e) { e.printStackTrace(); con.rollback(); } }}
最后，就是新建测试类了，每每用着main方法，我就感觉这世界是多么的有爱啊，junit什么的，最讨厌了.....
public class TestBlob_Clob{ public static void main(String[] args) { String sql1 = "select * from cdl_testfor update";// 悲观锁锁定需更新的行 String sql2 = "select * from cdl_test"; System.out.println("\t\t\t欢迎使用:"); System.out.println("1:插入图片"); System.out.println("2:插入文本"); System.out.println("3:读取图片"); System.out.println("4:读取文本"); System.out.println("5:退出"); System.out.println("请选择:"); while (true) { try { Scanner sc = new Scanner(System.in); int i = sc.nextInt(); System.out.println("sss:" + i); switch (i) { case 1: InsertBlobData isd = new InsertBlobData(); //插入图片 isd.insertBlob(sql1); break; case 2: InsertClobData icd = new InsertClobData(); //插入小说 icd.insertClob(sql1); break; case 3: ReadBlobData rb = new ReadBlobData(); //得到图片 rb.getBlob(sql2); break; case 4: ReadClobData rc = new ReadClobData(); //得到小说 rc.getClob(sql2); break; case 5: System.exit(0); } } catch (Exception e) { e.printStackTrace(); } } }}
页:
[1]