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="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.