前段时间碰到这个问题,用Java写了两个sample,以下是在Oracle下对这两种类型的插入和读取操作。
Blob的插入和读取:
- import java.io.File;
- import java.io.FileInputStream;
- import java.io.FileNotFoundException;
- import java.io.FileOutputStream;
- import java.io.IOException;
- import java.io.InputStream;
- import java.io.OutputStream;
- import java.sql.Connection;
- import java.sql.DriverManager;
- import java.sql.ResultSet;
- import java.sql.SQLException;
- import java.sql.Statement;
- public class BlobTest {
- String driver = "oracle.jdbc.driver.OracleDriver";
- String url = "jdbc:oracle:thin:@192.168.38.243:1521:orcl";
- String username = "fm";
- String password = "manager";
- Connection con = null;
- Statement stmt = null;
- public BlobTest() {
- con = getConnection();
- try {
- stmt = con.createStatement();
- } catch (SQLException e) {
- e.printStackTrace();
- }
- }
- public static void main(String[] args) {
- BlobTest bt = new BlobTest();
- bt.insertBolb();
- bt.getBlob();
- bt.close();
- }
- //insert blob
- private void insertBolb() {
- try {
- //insert an empty blob
- String sql1 = "insert into blobtest values ('001',empty_blob())";
- stmt.executeUpdate(sql1);
- //query the empty blob for update
- String sql2 = "select filedata from blobtest where fileid = '001' for update";
- ResultSet rs = stmt.executeQuery(sql2);
- //read the empty bolb
- while(rs.next()) {
- //get connection from weblogic by jndi,use weblogic's jar
- //OracleThinBlob blob = (weblogic.jdbc.vendor.oracle.OracleThinBlob) rs.getBlob(1);
- oracle.sql.BLOB blob = (oracle.sql.BLOB)rs.getBlob("filedata");
- OutputStream os = blob.getBinaryOutputStream();
- //read outter file
- InputStream is = new FileInputStream(new File("test.txt"));
- //write inputstream to outputstream
- byte[] b = new byte[blob.getBufferSize()];
- int len = 0;
- while((len = is.read(b)) != -1) {
- os.write(b, 0, len);
- }
- is.close();
- os.flush();
- os.close();
- con.commit();
- }
- } catch (SQLException e) {
- e.printStackTrace();
- } catch (FileNotFoundException e) {
- e.printStackTrace();
- } catch (IOException e) {
- e.printStackTrace();
- }
- }
- //read bolb
- private void getBlob() {
- try {
- //get blob from db
- String sql = "select filedata from blobtest where fileid = '001'";
- ResultSet rs = stmt.executeQuery(sql);
- while(rs.next()) {
- //get inputstream from resultset
- oracle.sql.BLOB blob = (oracle.sql.BLOB)rs.getBlob("filedata");
- InputStream is = blob.getBinaryStream();
- //create fileoutputstream
- OutputStream os = new FileOutputStream(new File("out.txt"));
- //write to file
- byte[] b = new byte[1024];
- int len;
- while((len = is.read(b)) != -1) {
- os.write(b, 0, len);
- }
- os.flush();
- os.close();
- is.close();
- }
- } catch (SQLException e) {
- e.printStackTrace();
- } catch (FileNotFoundException e) {
- e.printStackTrace();
- } catch (IOException e) {
- e.printStackTrace();
- }
- }
- //get connection
- private Connection getConnection() {
- try {
- Class.forName(driver);
- con = DriverManager.getConnection(url,username,password);
- } catch (ClassNotFoundException e) {
- e.printStackTrace();
- } catch (SQLException e) {
- e.printStackTrace();
- }
- return con;
- }
- //close connection
- private void close() {
- if(stmt != null) {
- try {
- stmt.close();
- } catch(SQLException e) {
- e.printStackTrace();
- } finally {
- if(con != null) {
- try {
- con.close();
- } catch (SQLException e) {
- e.printStackTrace();
- }
- }
- }
- }
- }
- }
对Clob的插入和读取:
- import java.io.BufferedReader;
- import java.io.FileInputStream;
- import java.io.IOException;
- import java.io.InputStream;
- import java.io.Reader;
- import java.io.Writer;
- import java.sql.Connection;
- import java.sql.DriverManager;
- import java.sql.ResultSet;
- import java.sql.SQLException;
- import java.sql.Statement;
- public class CLobTest {
- String driver = "oracle.jdbc.driver.OracleDriver";
- String url = "jdbc:oracle:thin:@192.168.38.243:1521:orcl";
- String username = "fm";
- String password = "manager";
- Connection con = null;
- Statement stmt = null;
- private static String str = "bbbBBBBBBBBBBBBBBBBBB";
- public CLobTest() {
- con = getConnection();
- try {
- stmt = con.createStatement();
- } catch (SQLException e) {
- e.printStackTrace();
- }
- }
- public static void main(String[] args) {
- CLobTest ct = new CLobTest();
- ct.insertClob();
- ct.getClob();
- ct.close();
- }
- private void insertClob() {
- try {
- //insert an empty clob
- stmt.executeUpdate("insert into clobtest values(empty_clob())");
- //get the empty_clob for update
- ResultSet rs = stmt
- .executeQuery("select myFile from clobtest for update");
- while (rs.next()) {
- oracle.sql.CLOB file = (oracle.sql.CLOB) rs.getClob("myFile");
- InputStream is = new FileInputStream("path");
- Writer writer = file.getCharacterOutputStream();
- //write the string to output stream
- writer.write(str.toCharArray());
- writer.flush();
- writer.close();
- }
- con.commit();
- con.close();
- } catch (SQLException e) {
- e.printStackTrace();
- } catch (IOException e) {
- e.printStackTrace();
- }
- }
- private void getClob() {
- try {
- //get the clob from db
- ResultSet rs = stmt.executeQuery("select myFile from clobtest");
- while (rs.next()) {
- oracle.sql.CLOB file = (oracle.sql.CLOB) rs.getClob("myFile");
- Reader reader = file.getCharacterStream();
- //create BufferedReader with reader
- BufferedReader br = new BufferedReader(reader);
- //create String buffer and print clob
- StringBuffer sb = new StringBuffer();
- String s = br.readLine();
- while (s != null) {
- sb.append(s);
- s = br.readLine();
- }
- System.out.println(sb.toString());
- }
- } catch (SQLException e) {
- e.printStackTrace();
- } catch (IOException e) {
- e.printStackTrace();
- }
- }
- private Connection getConnection() {
- try {
- Class.forName(driver);
- con = DriverManager.getConnection(url, username, password);
- } catch (ClassNotFoundException e) {
- e.printStackTrace();
- } catch (SQLException e) {
- e.printStackTrace();
- }
- return con;
- }
- private void close() {
- if (stmt != null) {
- try {
- stmt.close();
- } catch (SQLException e) {
- e.printStackTrace();
- } finally {
- if (con != null) {
- try {
- con.close();
- } catch (SQLException e) {
- e.printStackTrace();
- }
- }
- }
- }
- }
- }