///// writeCLOB 对ascii文件的写
writeBLOB 对二进制文件的写
writeBFILE
// import the JDBC packages
import java.sql.*;
import java.io.*;
// import the Oracle JDBC extension packages
import oracle.sql.*;
import oracle.jdbc.*;public class LobExample1 { public static void main(String [] args)
throws SQLException, IOException { // register the Oracle JDBC drivers
DriverManager.registerDriver(
new oracle.jdbc.OracleDriver()
); // create a Connection object, and connect to the database
// as lob_user using the Oracle JDBC Thin driver
Connection myConnection = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:ORCL",
"lob_user",
"lob_password"
); // disable auto-commit mode
myConnection.setAutoCommit(false); // create a statement object
Statement myStatement = myConnection.createStatement(); String sourceDirectory = "C:\\sample_files\\";
writeCLOB(myStatement, sourceDirectory + "textContent.txt");
writeBLOB(myStatement, sourceDirectory + "binaryContent.doc");
addBFILE(myStatement, "SAMPLE_FILES_DIR", "textContent.txt");
addBFILE(myStatement, "SAMPLE_FILES_DIR", "binaryContent.doc"); // close the JDBC objects
myStatement.close();
myConnection.close(); } // end of main()
private static void writeCLOB(
Statement myStatement,
String fileName
) throws SQLException, IOException { // step 1: initialize the LOB column to set the LOB locator
myStatement.executeUpdate(
"INSERT INTO clob_content(file_name, clob_column) " +
"VALUES ('" + fileName + "', EMPTY_CLOB())"
); // step 2: retrieve the row containing the LOB locator
ResultSet clobResultSet = myStatement.executeQuery(
"SELECT clob_column " +
"FROM clob_content " +
"WHERE file_name = '" + fileName + "' " +
"FOR UPDATE"
);
clobResultSet.next(); // step 3: create a LOB object and read the LOB locator
CLOB myClob =
((OracleResultSet) clobResultSet).getCLOB("clob_column"); // step 4: get the chunk size of the LOB from the LOB object
int chunkSize = myClob.getChunkSize(); // step 5: create a buffer to hold a block of data from the file
char [] textBuffer = new char[chunkSize]; // step 6: create a file object
File myFile = new File(fileName); // step 7: create input stream objects to read the file contents
FileInputStream myFileInputStream = new FileInputStream(myFile);
InputStreamReader myReader =
new InputStreamReader(myFileInputStream);
BufferedReader myBufferedReader = new BufferedReader(myReader); // step 8: read the file contents and write it to the LOB
long position = 1;
int charsRead; while ((charsRead = myBufferedReader.read(textBuffer)) != -1) { // write the buffer contents to myClob using the putChars() method
myClob.putChars(position, textBuffer); // increment the end position
position += charsRead; } // end of while // step 9: perform a commit
myStatement.execute("COMMIT"); // step 10: close the objects used to read the file
myBufferedReader.close();
myReader.close();
myFileInputStream.close(); System.out.println("Wrote content from file " +
fileName + " to CLOB"); } // end of writeCLOB()
private static void writeBLOB(
Statement myStatement,
String fileName
) throws SQLException, IOException { // step 1: initialize the LOB column to set the LOB locator
myStatement.executeUpdate(
"INSERT INTO blob_content(file_name, blob_column) " +
"VALUES ('" + fileName + "', EMPTY_BLOB())"
); // step 2: retrieve the row containing the LOB locator
ResultSet blobResultSet = myStatement.executeQuery(
"SELECT blob_column " +
"FROM blob_content " +
"WHERE file_name = '" + fileName + "' " +
"FOR UPDATE"
);
blobResultSet.next(); // step 3: create a LOB object and read the LOB locator
BLOB myBlob =
((OracleResultSet) blobResultSet).getBLOB("blob_column"); // step 4: get the chunk size of the LOB from the LOB object
int chunkSize = myBlob.getChunkSize(); // step 5: create a buffer to hold a block of data from the file
byte [] byteBuffer = new byte[chunkSize]; // step 6: create a file object to open the file
File myFile = new File(fileName); // step 7: create an input stream object to read the file contents
FileInputStream myFileInputStream = new FileInputStream(myFile); // step 8: read the file contents and write it to the LOB
long position = 1;
int bytesRead; while ((bytesRead = myFileInputStream.read(byteBuffer)) != -1) { // write the buffer contents to myBlob using the putBytes() method
myBlob.putBytes(position, byteBuffer); // increment the end position
position += bytesRead; } // end of while // step 9: perform a COMMIT
myStatement.execute("COMMIT"); // step 10: close the objects used to read the file
myFileInputStream.close(); System.out.println("Wrote content from file " +
fileName + " to BLOB"); } // end of writeBLOB()
private static void addBFILE(
Statement myStatement,
String directory,
String fileName
) throws SQLException { myStatement.executeUpdate(
"INSERT INTO bfile_content(file_name, bfile_column) " +
"VALUES ('" + fileName + "', " +
"BFILENAME('" + directory + "', '" + fileName + "'))"
);
myStatement.execute("COMMIT"); System.out.println("Added pointer to file " +
fileName + " to BFILE in database directory " + directory); } // end of addBFILE()}
jave 读取BLOB
首先是Getting BLOB and CLOB Locators from a Result Set
// Select LOB locator into standard result set.
ResultSet rs =stmt.executeQuery ("SELECT blob_col, clob_col FROM lob_table");
while (rs.next())
{// Get LOB locators into Java wrapper classes.
oracle.jdbc2.Blob blob = (oracle.jdbc2.Blob)rs.getObject(1);
oracle.jdbc2.Clob clob = (oracle.jdbc2.Clob)rs.getObject(2);
[...process...]
}
然后是Read BLOB data from BLOB locator.
InputStream byte_stream = my_blob.getBinaryStream();
byte [] byte_array = new byte [10];
int bytes_read = byte_stream.read(byte_array);
和Writing BLOB Data
java.io.OutputStream outstream;
// read data into a byte array
byte[] data = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9};
// write the array of binary data to a BLOB
outstream = ((BLOB)my_blob).getBinaryOutputStream();
outstream.write(data);
还有Passing a BLOB Locator to a Prepared Statement
OraclePreparedStatement ops = (OraclePreparedStatement)conn.prepareStatement
"INSERT INTO blob_table VALUES(?)");
ops.setBLOB(1, my_blob);
ops.execute();
最后应该注意:
insert的时候一定要用empty_blob()初始化
stmt.execute ("insert into my_blob_table values ('row1', empty_blob()");
看了下面的程序就知道了import java.io.*;
import java.util.*;
import java.sql.*;
import oracle.jdbc.driver.*;
import java.text.*;
//
// @author: guo chang guo
// @time: 2003 08 17
//
// this program shows how to put a very big bytes array into oracle table(long raw)
// you can not use PreparedStatement's method setBytes, because setBytes can not
// support very big bytes array, you must use PreparedStatement's method setBinaryStream.
// if you want to use this method, you must transfer your bytes array into inputstream,
// in this example, we use ByteArrayInputStream which can be constructed by a bytes array.
//
// @copyright(R) cvicse
////
//the definition of the oracle table :CREATE TABLE bin_data (name VARCHAR(30),data long raw ).
//you can not define the table: CREATE TABLE bin_data (name VARCHAR(30),data blob )
//
writeBLOB 对二进制文件的写
writeBFILE
// import the JDBC packages
import java.sql.*;
import java.io.*;
// import the Oracle JDBC extension packages
import oracle.sql.*;
import oracle.jdbc.*;public class LobExample1 { public static void main(String [] args)
throws SQLException, IOException { // register the Oracle JDBC drivers
DriverManager.registerDriver(
new oracle.jdbc.OracleDriver()
); // create a Connection object, and connect to the database
// as lob_user using the Oracle JDBC Thin driver
Connection myConnection = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:ORCL",
"lob_user",
"lob_password"
); // disable auto-commit mode
myConnection.setAutoCommit(false); // create a statement object
Statement myStatement = myConnection.createStatement(); String sourceDirectory = "C:\\sample_files\\";
writeCLOB(myStatement, sourceDirectory + "textContent.txt");
writeBLOB(myStatement, sourceDirectory + "binaryContent.doc");
addBFILE(myStatement, "SAMPLE_FILES_DIR", "textContent.txt");
addBFILE(myStatement, "SAMPLE_FILES_DIR", "binaryContent.doc"); // close the JDBC objects
myStatement.close();
myConnection.close(); } // end of main()
private static void writeCLOB(
Statement myStatement,
String fileName
) throws SQLException, IOException { // step 1: initialize the LOB column to set the LOB locator
myStatement.executeUpdate(
"INSERT INTO clob_content(file_name, clob_column) " +
"VALUES ('" + fileName + "', EMPTY_CLOB())"
); // step 2: retrieve the row containing the LOB locator
ResultSet clobResultSet = myStatement.executeQuery(
"SELECT clob_column " +
"FROM clob_content " +
"WHERE file_name = '" + fileName + "' " +
"FOR UPDATE"
);
clobResultSet.next(); // step 3: create a LOB object and read the LOB locator
CLOB myClob =
((OracleResultSet) clobResultSet).getCLOB("clob_column"); // step 4: get the chunk size of the LOB from the LOB object
int chunkSize = myClob.getChunkSize(); // step 5: create a buffer to hold a block of data from the file
char [] textBuffer = new char[chunkSize]; // step 6: create a file object
File myFile = new File(fileName); // step 7: create input stream objects to read the file contents
FileInputStream myFileInputStream = new FileInputStream(myFile);
InputStreamReader myReader =
new InputStreamReader(myFileInputStream);
BufferedReader myBufferedReader = new BufferedReader(myReader); // step 8: read the file contents and write it to the LOB
long position = 1;
int charsRead; while ((charsRead = myBufferedReader.read(textBuffer)) != -1) { // write the buffer contents to myClob using the putChars() method
myClob.putChars(position, textBuffer); // increment the end position
position += charsRead; } // end of while // step 9: perform a commit
myStatement.execute("COMMIT"); // step 10: close the objects used to read the file
myBufferedReader.close();
myReader.close();
myFileInputStream.close(); System.out.println("Wrote content from file " +
fileName + " to CLOB"); } // end of writeCLOB()
private static void writeBLOB(
Statement myStatement,
String fileName
) throws SQLException, IOException { // step 1: initialize the LOB column to set the LOB locator
myStatement.executeUpdate(
"INSERT INTO blob_content(file_name, blob_column) " +
"VALUES ('" + fileName + "', EMPTY_BLOB())"
); // step 2: retrieve the row containing the LOB locator
ResultSet blobResultSet = myStatement.executeQuery(
"SELECT blob_column " +
"FROM blob_content " +
"WHERE file_name = '" + fileName + "' " +
"FOR UPDATE"
);
blobResultSet.next(); // step 3: create a LOB object and read the LOB locator
BLOB myBlob =
((OracleResultSet) blobResultSet).getBLOB("blob_column"); // step 4: get the chunk size of the LOB from the LOB object
int chunkSize = myBlob.getChunkSize(); // step 5: create a buffer to hold a block of data from the file
byte [] byteBuffer = new byte[chunkSize]; // step 6: create a file object to open the file
File myFile = new File(fileName); // step 7: create an input stream object to read the file contents
FileInputStream myFileInputStream = new FileInputStream(myFile); // step 8: read the file contents and write it to the LOB
long position = 1;
int bytesRead; while ((bytesRead = myFileInputStream.read(byteBuffer)) != -1) { // write the buffer contents to myBlob using the putBytes() method
myBlob.putBytes(position, byteBuffer); // increment the end position
position += bytesRead; } // end of while // step 9: perform a COMMIT
myStatement.execute("COMMIT"); // step 10: close the objects used to read the file
myFileInputStream.close(); System.out.println("Wrote content from file " +
fileName + " to BLOB"); } // end of writeBLOB()
private static void addBFILE(
Statement myStatement,
String directory,
String fileName
) throws SQLException { myStatement.executeUpdate(
"INSERT INTO bfile_content(file_name, bfile_column) " +
"VALUES ('" + fileName + "', " +
"BFILENAME('" + directory + "', '" + fileName + "'))"
);
myStatement.execute("COMMIT"); System.out.println("Added pointer to file " +
fileName + " to BFILE in database directory " + directory); } // end of addBFILE()}
jave 读取BLOB
首先是Getting BLOB and CLOB Locators from a Result Set
// Select LOB locator into standard result set.
ResultSet rs =stmt.executeQuery ("SELECT blob_col, clob_col FROM lob_table");
while (rs.next())
{// Get LOB locators into Java wrapper classes.
oracle.jdbc2.Blob blob = (oracle.jdbc2.Blob)rs.getObject(1);
oracle.jdbc2.Clob clob = (oracle.jdbc2.Clob)rs.getObject(2);
[...process...]
}
然后是Read BLOB data from BLOB locator.
InputStream byte_stream = my_blob.getBinaryStream();
byte [] byte_array = new byte [10];
int bytes_read = byte_stream.read(byte_array);
和Writing BLOB Data
java.io.OutputStream outstream;
// read data into a byte array
byte[] data = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9};
// write the array of binary data to a BLOB
outstream = ((BLOB)my_blob).getBinaryOutputStream();
outstream.write(data);
还有Passing a BLOB Locator to a Prepared Statement
OraclePreparedStatement ops = (OraclePreparedStatement)conn.prepareStatement
"INSERT INTO blob_table VALUES(?)");
ops.setBLOB(1, my_blob);
ops.execute();
最后应该注意:
insert的时候一定要用empty_blob()初始化
stmt.execute ("insert into my_blob_table values ('row1', empty_blob()");
看了下面的程序就知道了import java.io.*;
import java.util.*;
import java.sql.*;
import oracle.jdbc.driver.*;
import java.text.*;
//
// @author: guo chang guo
// @time: 2003 08 17
//
// this program shows how to put a very big bytes array into oracle table(long raw)
// you can not use PreparedStatement's method setBytes, because setBytes can not
// support very big bytes array, you must use PreparedStatement's method setBinaryStream.
// if you want to use this method, you must transfer your bytes array into inputstream,
// in this example, we use ByteArrayInputStream which can be constructed by a bytes array.
//
// @copyright(R) cvicse
////
//the definition of the oracle table :CREATE TABLE bin_data (name VARCHAR(30),data long raw ).
//you can not define the table: CREATE TABLE bin_data (name VARCHAR(30),data blob )
//
解决方案 »
- 请教:Client端利用OCI访问Oracle数据库需不需要安装Oracle客户端
- 关于Oracle 10g的Oracle HTTP Server
- Oralce连接到sqlserver数据库后,在oracle中如何写sql语句查询,添加,修改,删除数据.
- 100分求一条SQL
- sql*plus 报ora01017错 invalid username/password
- 求SQL语句,请高手帮忙!
- index table space 怎么设呢
- 存储过程??????
- 很简单的求救!?oracle designer
- 9I的dbmsstat.sql文件移植到8I时发现有个语句不能执行?????
- oracle817建库出错,在线给分,好急
- 在一个update的语句触发的trigger中如何得到是哪台机器执行的update语句?
//
//we have test this program in oracle 9.2 the file size is 31M
//public class test
{
public static void main(String args[]) throws java.io.IOException,java.sql.SQLException
{
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
}
catch (java.lang.ClassNotFoundException e)
{
System.err.print("ClassNotFoundException: " + e.getMessage());
} try
{
//open a file, put the content of file into a bytes array
File files = new File("c:\\temp\\jdk140.chm");
FileInputStream fis=new FileInputStream(files);
System.out.println(files.getName());
System.out.println((int)files.length()); byte[] fl = new byte[(int)files.length()];
System.out.println(fl.length);
fis.read(fl);
//use this bytes array to construct a InputStream
ByteArrayInputStream kk = new ByteArrayInputStream(fl) ;
//connect the oracle database
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@eaiserver01:1521:orcl", "system", "admin");
PreparedStatement ps = conn.prepareStatement("INSERT INTO bin_data (name,data)" +"VALUES (?, ?)");
//set parameters
ps.setString(1,files.getName());
ps.setBinaryStream(2, kk,(int)files.length());
//execute
ps.executeUpdate();
fis.close();
ps.close();
// read from the table
int bytesRead = 0;
int byteSum = 0;
byte[] buffer = new byte[8 * 1924];
FileOutputStream fis2 = new FileOutputStream("c:\\temp\\hi.chm");
PreparedStatement ps2 = conn.prepareStatement( "select data from bin_data");
ResultSet rs = ps2.executeQuery();
if (rs != null)
{
while(rs.next())
{
InputStream is = rs.getBinaryStream(1);
while ((bytesRead = is.read(buffer)) != -1)
{
byteSum += bytesRead;
fis2.write(buffer, 0, bytesRead);
}
fis2.close( );
}
rs.close( );
}
ps2.close( );
}
catch(Exception e)
{
System.out.println("errror :"+e.toString() );
e.printStackTrace();
}
}
}