Interview Questions

Spring Handling BLOB/CLOB

In this article let's discuss about handling BLOB data in Spring JDBC.

Consider the following class which is used to insert/retrieve blob from database.

TestBlobJdbc.java

/**
 * THIS SOFTWARE IS PROVIDED BY THE AUTHOR AND CONTRIBUTORS ``AS IS''
 * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED
 * TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
 * PURPOSES ARE DISCLAIMED. IN NO EVENT SHALL THE AUTHOR OR CONTRIBUTORS
 * BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
 * CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
 * SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
 * INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
 * CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
 * ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
 * POSSIBILITY OF SUCH DAMAGE.
 */

package in.techdive.spring.jdbc;

import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport;
import org.springframework.jdbc.support.lob.OracleLobHandler;

public class TestBlobJdbc extends SimpleJdbcDaoSupport
{

        private OracleLobHandler        oracleLobHandler;

        /**
         * @return the oracleLobHandler
         */

        public OracleLobHandler getOracleLobHandler()
        {
                return oracleLobHandler;
        }

        public void setOracleLobHandler(OracleLobHandler oracleLobHandler)
        {
                this.oracleLobHandler = oracleLobHandler;
        }

        public TestBlobJdbc()
        {
        }

        public void insertDataAsBlob()
        {
                try
                {
                        String sql = " Insert INTO TEST_BLOB " + " (ID, BLOB_FILE ) " + " Values   (? , ? ) ";

                        final InputStream fileAsStream = new FileInputStream("C:\\new.xml");

                        getJdbcTemplate().update(sql, new PreparedStatementSetter() {
                                public void setValues(PreparedStatement ps) throws SQLException
                                {
                                        ps.setInt(1, 1);
                                        oracleLobHandler.getLobCreator().setBlobAsBinaryStream(ps, 2, fileAsStream,
                                        fileAsStream.toString().getBytes().length);
                                }
                        });
                }
                catch (Exception e)
                {
                        e.printStackTrace();
                }
        }

        public InputStream getBlobData()
        {
                String sql = " SELECT * FROM TEST_BLOB WHERE ID = 1 ";
                return getSimpleJdbcTemplate().queryForObject(sql, new FileMapper());
        }

        class FileMapper implements ParameterizedRowMapper<InputStream>
        {
                public InputStream mapRow(ResultSet rs, int rowNum) throws SQLException
                {

                        return rs.getBinaryStream(2);
                }
        }
}

Have a look at the Spring configuration file.

springJdbc-lob.xml

<bean id="nativeJdbcExtractor" class="org.springframework.jdbc.support.nativejdbc.CommonsDbcpNativeJdbcExtractor"/>

<bean id="oracleLobHandler" class="org.springframework.jdbc.support.lob.OracleLobHandler">
  <property name="nativeJdbcExtractor">
  <ref local="nativeJdbcExtractor"/>
  </property>
</bean>
       
<bean id="TestBlobJdbc " class="in.techdive.spring.TestBlobJdbc ">
  <property name="jdbcTemplate"><ref bean="jdbcTemplate"/></property>
  <property name="dataSource"><ref bean="dataSource"/></property>
  <property name="queryFailureIncrementor" ref="queryFailureIncrementor"/>
  <property name="oracleLobHandler" ref="oracleLobHandler"/>
</bean>

In this way we can handle LOB objects of Oracle such as BLOB/CLOB in Spring.