Insert and Select images by using JDBC

[Eberman]

I am looking for some sample code which would insert and select images into Oracle Table. We are Java Shop so I am looking for any help by using JDBC.

Thanks!

[rotem_fo]

Hi,
you can store images in BLOB column in the database, and using jdbc, you can insert, update and fetch the LOB data.

In order to select LOB from a table you need to use regular statement:

Connection conn;
Statement stmt = conn.createStatement();
ResultSet rst = stmt.executeQuery(“SELECT BLOB_COLUMN FROM LOB_TABLE”);
while (rst.next()) {
     java.sql.Blob blob = rst.getObject(1);
}

In order to insert lob into the database:
Begin by using SQL statements to create the BLOB entry in the table.
Use the empty_blob() function to create the BLOB locator:

stmt.execute("INSERT INTO LOB_TABLE VALUES (empty_blob())");

Get the BLOB locator from the table:

String query = "SELECT BLOB_COLUMN FROM LOB_TABLE WHERE ...";
ResultSet rest = stmt.executeQuery(query);
BLOB blob = ((OracleResultSet)rset).getBLOB(1);

Declare a file handler, then print the length of the file. This value will be used later to ensure that the entire file is read into the BLOB.
Next, create a FileInputStream object to read the contents of the file, and an OutputStream object to retrieve the BLOB as a stream.

File binaryFile = new File("my_image_file.img");
System.out.println("my_image_file.img length = " + binaryFile.length());
FileInputStream instream = new FileInputStream(binaryFile);
OutputStream outstream = blob.getBinaryOutputStream();

Call getBufferSize() to retrieve the ideal buffer size (according to calculations by the JDBC driver) to use in writing to the BLOB, then create the buffer byte array:

int size = blob.getBufferSize();
byte[] buffer = new byte[size];
int length = -1;

Use the read() method to read the file to the byte array buffer, then use the write() method to write it to the BLOB. When you finish, close the input and output streams:

while ((length = instream.read(buffer)) != -1) {
	outstream.write(buffer, 0, length);
}
instream.close();
outstream.close();

Accessing and Manipulating BLOB and CLOB Data
Once you have your BLOB Locator in the Table you can access and manipulate the data to which it points (As shown in SELECT example above).

BLOB object featured functions:
getBinaryOutputStream(): Returns a java.io.OutputStream to write data to the BLOB as a stream.
getBinaryStream(): Returns the BLOB data for this Blob instance as a stream of bytes.
getBufferSize(): Returns the ideal buffer size, according to calculations by the JDBC driver, to use in reading and writing BLOB data. This value is a multiple of the chunk size (see getChunkSize() below) and is close to 32K.
getBytes(): Reads from the BLOB data, starting at a specified point, into a supplied buffer.
getChunkSize(): Returns the Oracle chunking size, which is specified when the LOB column is first created.
length(): Returns the length of the BLOB in bytes.
position(): Determines the byte position in the BLOB where a given pattern begins.
putBytes(): Writes BLOB data, starting at a specified point, from a supplied buf.

[pando]

Hi rotem_fo

shouldnt you use prepareStatement so the SQL stmt uses bind variables :-?

And the insert is probably better this way

begin insert into lobtest(data) values(empty_blob())
return data into ?; end;


Here is one example of how to read and write LOBs using JDBC

http://asktom.oracle.com/pls/ask/f?p...1004630777647,