JDBC + hibernate writes BLOB data to Oracle

The Blob field in Oracle is special. It has much better performance than the long field and can be used to save binary data such as sample slices.

Writing BLOB fields is very different from writing other types of fields. Because blob itself has a cursor, you must use cursor to operate blob. Therefore, before writing blob, you must obtain the cursor to write data. How can you obtain the Blob cursor?

This requires you to insert an empty blob first, which will create a blob cursor, and then you can query the cursor of the empty blob using select. In this way, you can perform two steps, you can get the Blob cursor to write BLOB data.

Take a look at the following JDBC demo and write the binary file oraclejdbc. jar to the content field of the javatest database table (this is a blob field)

Import java. SQL .*; Import java. Io .*; Import oracle. SQL .*; Public class writeblob { Public static void main (string [] ARGs ){ Try { Drivermanager. registerdriver (New Oracle. JDBC. Driver. oracledriver ()); Connection conn = drivermanager. getconnection ("JDBC: oracle: thin: @ localhost: 1521: orcl", "fankai", "fankai "); Conn. setautocommit (false ); Blob blob = NULL; Preparedstatement pstmt = conn. preparestatement ("insert into javatest (name, content) values (?, Empty_blob ())"); Pstmt. setstring (1, "fankai "); Pstmt.exe cuteupdate (); Pstmt. Close (); Pstmt = conn. preparestatement ("select content from javatest where name =? For Update "); Pstmt. setstring (1, "fankai "); Resultset rset = pstmt.exe cutequery (); If (rset. Next () blob = (BLOB) rset. getblob (1 ); String filename = "oraclejdbc. Jar "; File F = new file (filename ); Fileinputstream fin = new fileinputstream (f ); System. Out. println ("file size =" + FIN. Available ()); Pstmt = conn. preparestatement ("Update javatest set content =? Where name =? "); Outputstream out = blob. getbinaryoutputstream (); Int COUNT =-1, total = 0; Byte [] DATA = new byte [(INT) Fin. Available ()]; Fin. Read (data ); Out. Write (data ); /* Byte [] DATA = new byte [blob. getbuffersize ()]; another method to save memory While (COUNT = fin. Read (data ))! =-1 ){ Total + = count; Out. Write (data, 0, count ); } */ Fin. Close (); Out. Close (); Pstmt. setblob (1, blob ); Pstmt. setstring (2, "fankai "); Pstmt.exe cuteupdate (); Pstmt. Close (); Conn. Commit (); Conn. Close (); } Catch (sqlexception e ){ System. Err. println (E. getmessage ()); E. printstacktrace (); } Catch (ioexception e ){ System. Err. println (E. getmessage ()); } } }

Take a look at the example in three steps:

1. Insert an empty blob.

Into javatest (name, content) values (?, Empty_blob ());

2. Obtain the Blob cursor.

Select Content from javatest where name =? For update;

Note !!! You must add for update, which will lock the row until the row is modified to avoid concurrent conflicts.

3. Update javatest set content =? Where name =

Use cursor to write data to the database

We also want to remind you that:

The jdbc2.0 specification in JDK is imperfect. Only the reading blob interface is used, but the writing blob interface is not used. jdbc3.0 in JDK is added with the writing blob interface. You can use the jdbc3.0 interface or the jdbc api of Oracle. In the preceding example, I used the JDBC API of oracle.

Note the following:

Java. SQL. blob Oracle. SQL. blob

Note that blob is case-insensitive. Do not mix up when writing programs.

Next, let's take a look at how to write data using hibernate. The principle is the same. It also involves three steps, but the code is much simpler.

This is the cat object definition.

Package com. fankai; Import java. SQL. Blob; Public class cat { Private string ID; Private string name; Private char sex; Private float weight; Private blob image; Public CAT (){} Public String GETID () {return ID ;} Public void setid (string ID) {This. ID = ID ;} Public String getname () {return name ;} Public void setname (string name) {This. Name = Name ;} Public char getsex () {return sex ;} Public void setsex (char sex) {This. Sex = sex ;} Public float getweight () {return weight ;} Public void setweight (float weight) {This. Weight = weight ;} Public blob getimage () {return image ;} Public void setimage (BLOB image) {This. Image = image ;} }

This is Cat. HBM. xml.

<? XML version = "1.0"?> <! Doctype hibernate-Mapping System "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd"> <Hibernate-mapping> <Class name = "com. fankai. Cat" table = "cat"> <! -- JCs-Cache Usage = "read-only"/--> <ID name = "ID" unsaved-value = "null"> <Generator class = "UUID. Hex"/> </ID> <Property name = "name" length = "16" not-null = "true"/> <Property name = "sex" length = "1" not-null = "true"/> <Property name = "weight"/> <Property name = "image"/> </Class> </Hibernate-mapping>

The following is a complete example of writing data into blob using hibernate. Compared with JDBC, it is much easier to write, and you do not need to write Oracle special SQL statements:

Package com. fankai; Import java. SQL. Blob; Import net. SF. hibernate .*; Import oracle. SQL .*; Import java. Io .*; Public class testcathibernate { Public static void testblob (){ Session S = NULL; Byte [] buffer = new byte [1]; Buffer [0] = 1; Try { Sessionfactory Sf = hibernatesessionfactory. getsessionfactory (); S = SF. opensession (); Transaction Tx = S. begintransaction (); Cat c = new CAT (); C. setname ("Robbin "); C. setimage (hibernate. createblob (buffer )); S. Save (C ); S. Flush (); S. Refresh (C, lockmode. Upgrade ); Blob blob = (BLOB) C. getimage (); Outputstream out = blob. getbinaryoutputstream (); String filename = "oraclejdbc. Jar "; File F = new file (filename ); Fileinputstream fin = new fileinputstream (f ); Int COUNT =-1, total = 0; Byte [] DATA = new byte [(INT) Fin. Available ()]; Fin. Read (data ); Out. Write (data ); Fin. Close (); Out. Close (); S. Flush (); TX. Commit (); } Catch (exception e ){ System. Out. println (E. getmessage ()); } Finally { If (s! = NULL) Try { S. Close (); } Catch (exception e ){} } } }