Insert Blob into Oracle DB

[pughbri]

Hi All,

Does the spring jdbc framework have support for inserting a BLOB into oracle? I've seen some posts about reading BLOB data, but nothing about how I would go about inserting a BLOB.

Thanks,
Brian

[trisberg]

You can use the SqlUpdate and a LobHandler for this. If you are using Oracle then you should use the OracleLobHandler instead of the DefaultLobHandler. Here is a code snippet that should get you started:

DataSource dataSource;
LobHandler lobHandler;

...
 
  SqlUpdate su = new SqlUpdate(dataSource, 
      "INSERT INTO My_Poster  " + 
      "(id, poster_image) " +
      "VALUES (?, ?)" );
  su.declareParameter(new SqlParameter("id", Types.INTEGER));
  su.declareParameter(new SqlParameter("poster_image", Types.BLOB));
  su.compile();
        
  Object[] parameterValues = new Object[2];
  parameterValues[0] = new Integer(1);
  File in = new File("spring2004.jpg");
  InputStream is = null;
  try {
    is = new FileInputStream(in);
  } catch (FileNotFoundException e) {
    e.printStackTrace();
  }
  lobHandler = new DefaultLobHandler();
  parameterValues[1] = new SqlLobValue(is, (int) in.length(), lobHandler);
    
  su.update(parameterValues);

[pughbri]

Thanks for the quick reply and code snippet. Using the OracleLobHandler as you suggested worked great.

[Juergen Hoeller]

Also, have a look at the "imagedb" sample app that comes with the Spring distribution: It illustrates seamless BLOB and CLOB handling for MySQL and Oracle, working with a plain JdbcTemplate rather than JDBC operation objects (an alternative approach).

Juergen

[fseka]

Also, have a look at the "imagedb" sample app that comes with the Spring distribution: It illustrates seamless BLOB and CLOB handling for MySQL and Oracle, working with a plain JdbcTemplate rather than JDBC operation objects (an alternative approach).

Juergen

Thanks for code Juergen, but doy you have the same code working when using Hibernate ?

Because in you example you "hard code" table and column names.

Best regards
--
fSeka

[garpinc2]

I am having a problem. Please advise....

The following code is giving error. I know I have oracle drivers on classpath because it works in all other cases

14:27:15,108 ERROR ContextLoader:108 - Context initialization failed
org.springframework.beans.factory.BeanCreationExce ption: Error creating bean wit
h name 'oracleLobHandler' defined in resource [/WEB-INF/applicationContext.xml]
of ServletContext: Instantiation of bean failed; nested exception is org.springf
ramework.beans.FatalBeanException: Could not instantiate class [org.springframew
ork.jdbc.support.lob.OracleLobHandler]; constructor threw exception; nested exce
ption is org.springframework.dao.InvalidDataAccessApiUsageE xception: Couldn't in
itialize OracleLobHandler because Oracle driver classes are not available; neste
d exception is java.lang.ClassNotFoundException: oracle.jdbc.OracleConnection
org.springframework.beans.FatalBeanException: Could not instantiate class [org.s
pringframework.jdbc.support.lob.OracleLobHandler]; constructor threw exception;
nested exception is org.springframework.dao.InvalidDataAccessApiUsageE xception:
Couldn't initialize OracleLobHandler because Oracle driver classes are not avail
able; nested exception is java.lang.ClassNotFoundException: oracle.jdbc.OracleCo
nnection
org.springframework.dao.InvalidDataAccessApiUsageE xception: Couldn't initialize
OracleLobHandler because Oracle driver classes are not available; nested excepti
on is java.lang.ClassNotFoundException: oracle.jdbc.OracleConnection
java.lang.ClassNotFoundException: oracle.jdbc.OracleConnection
at java.net.URLClassLoader$1.run(URLClassLoader.java: 199)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(URLClassLoader.j ava:187)
at java.lang.ClassLoader.loadClass(ClassLoader.java:2 89)
at sun.misc.Launcher$AppClassLoader.loadClass(Launche r.java:274)
at java.lang.ClassLoader.loadClass(ClassLoader.java:2 35)
at org.mortbay.http.ContextLoader.loadClass(ContextLo ader.java:206)
at org.mortbay.http.ContextLoader.loadClass(ContextLo ader.java:170)
at org.springframework.jdbc.support.lob.OracleLobHand ler.<init>(OracleLobHandle
r.java:117)

Here is the mapping causing the problem

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
  <property name="driverClassName">
    <value>oracle.jdbc.driver.OracleDriver</value>
  </property>
  <property name="url">
    <value>jdbc&#58;oracle&#58;thin&#58;@xxx&#58;1521&#58;xx</value>
  </property>
  <property name="username">
    <value>xxxx</value>
  </property>
  <property name="password">
    <value>xxx</value>
  </property>
  <property name="defaultAutoCommit">
    <value>false</value>
  </property>
</bean>

	<!-- LobHandler for Oracle JDBC drivers -->
	<!-- &#40;refers to the NativeJdbcExtractor above to get access to native OracleConnections&#41; -->
	<bean id="oracleLobHandler" class="org.springframework.jdbc.support.lob.OracleLobHandler"
			>
	</bean>

[garpinc2]

Should be

in OracleLobHandler

private static final String CONNECTION_CLASS_NAME = "oracle.jdbc.driver.OracleConnection";

Please confirm

[trisberg]

The oracle.jdbc.OracleConnection is the interface that is implemented by oracle.jdbc.driver.OracleConnection.

Try putting this code in your class before any Spring code and see if it throws an error. You probably have to skip the loading of the ApplicationContext for this test to work.

		try {
			Class.forName("oracle.jdbc.OracleConnection");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		}

That should resolve whether it is a classloading issue or not.

[garpinc2]

Seems this implementation has problem with 8.1.6 driver.

It doesn't have this interface.

Please advise.

[trisberg]

OracleLobHandler was developed and tested on Oracle 9i. I have used it on 8.1.7.4 and 10g but not on any earlier releases.

Oracle 8.1.6 is quite old and I would say that forum support for it would be limited. (It was desupported by Oracle on 31-Oct-2001)