Announcement Announcement Module
Collapse
No announcement yet.
Insert Blob into Oracle DB Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Insert Blob into Oracle DB

    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

  • #2
    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:
    Code:
    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);

    Comment


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

      Comment


      • #4
        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

        Comment


        • #5
          Originally posted by 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
          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

          Comment


          • #6
            Ditto... Please show example.

            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
            Code:
            <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>

            Comment


            • #7
              Looks like a bug

              Should be

              in OracleLobHandler

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

              Please confirm

              Comment


              • #8
                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.

                Code:
                		try &#123;
                			Class.forName&#40;"oracle.jdbc.OracleConnection"&#41;;
                		&#125; catch &#40;ClassNotFoundException e&#41; &#123;
                			e.printStackTrace&#40;&#41;;
                			throw new RuntimeException&#40;e&#41;;
                		&#125;
                That should resolve whether it is a classloading issue or not.

                Comment


                • #9
                  Seems this implementation has problem with 8.1.6 driver

                  Seems this implementation has problem with 8.1.6 driver.

                  It doesn't have this interface.

                  Please advise.

                  Comment


                  • #10
                    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)

                    Comment

                    Working...
                    X