How do I use the BLOB data type with Hibernate?

Question

I am retrieving a blob field containing JSON file from Oracle 10g database. I want to convert it in to a string in my DAO and give it to an incoming service request. My entity class is:

@Lob
@Column(name = "DTA_BLOB")
private Blob DataBlob;

/**
* @return the DataBlob
*
*/
public Blob getDataBlob(){
    return DataBlob;

}
/**
* @param DataBlob the DataBlob to set
*/
public void setDataBlob(Blob DataBlob) {
    this.DataBlob = DataBlob;
}

My DAO have the method to get the string from the blob as shown below:

 @Override
    @Transactional
    public String getMenu(Long menuDataId) throws SQLException, IOException{

                MenuData menu_data = this.entityManager.find(MenuData.class,menuDataId);
                Blob menuData =menu_data.getDataBlob();
                byte[] bdata = menuData.getBytes(1, (int)menuData.length()); 
                String dataStr = new String(bdata);    
                return dataStr;

    }

but when I tried to access the service after deploying this WAR I am getting an error (and my tomcat crashed). In the log it says:

Nov 06, 2014 3:47:34 PM org.apache.catalina.core.StandardWrapperValve invoke
SEVERE: Servlet.service() for servlet [spring] in context with path [/MyWarName] threw exception [Request processing failed; nested exception is java.sql.SQLException: Closed Connection] with root cause
java.sql.SQLException: Closed Connection
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)
    at oracle.sql.BLOB.getDBAccess(BLOB.java:955)
    at oracle.sql.BLOB.getBinaryStream(BLOB.java:229)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.hibernate.engine.jdbc.SerializableBlobProxy.invoke(SerializableBlobProxy.java:72)
    at $Proxy40.getBinaryStream(Unknown Source)
    at com.pack1.pack2.dao.MenuDataDaoImpl.getMenu(MenuDataDaoImpl.java:69)
    at com.pack1.pack2.controller.MenuController.getMenu(MenuController.java:46)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:219)
    at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132)
    at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:745)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:686)
    at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80)
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:925)
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:856)
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:936)
    at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:827)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:620)
    at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:812)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:330)
    at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:118)
    at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:84)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
    at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:113)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
    at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:103)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
    at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:113)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
    at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:54)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
    at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:45)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
    at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:183)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
    at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:105)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
    at org.springframework.security.web.session.ConcurrentSessionFilter.doFilter(ConcurrentSessionFilter.java:125)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
    at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:87)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
    at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:192)
    at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:160)
    at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:343)
    at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:260)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:503)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:170)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
    at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:421)
    at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1070)
    at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:611)
    at org.apache.tomcat.util.net.AprEndpoint$SocketProcessor.doRun(AprEndpoint.java:2462)
    at org.apache.tomcat.util.net.AprEndpoint$SocketProcessor.run(AprEndpoint.java:2451)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    at java.lang.Thread.run(Unknown Source)

My persistence.xml :

<?xml version="1.0" encoding="UTF-8"?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence" version="2.0">
    <persistence-unit name="punit" transaction-type="RESOURCE_LOCAL">
        <class>com.pack1.pack2.entity.MenuData</class>
        <provider>org.hibernate.ejb.HibernatePersistence</provider>
        <properties>
            <property name="hibernate.show_sql" value="true"/>
            <property name="hibernate.dialect"
              value="org.hibernate.dialect.Oracle10gDialect"/>
            <prop key="hibernate.jdbc.use_streams_for_binary">true</prop>
        </properties>
    </persistence-unit>
</persistence>

My spring-core.xml has the following configuration (just for making sure my database configuration is correct too):

<bean id="dataSource"   class="oracle.jdbc.pool.OracleDataSource">
    <property name="dataSourceName" value="schema"/>
    <property name="URL" value="jdbc:oracle:thin:@hostname:port:xe" /> 
    <property name="user" value="name" />
    <property name="password" value="pwd" />
</bean>

Is there any configuration details I am missing for Hibernate or database configuration? Or are there any Hibernate techniques for doing this?

Answer 1

Finally got the answer, seems pretty simple now.

I mapped oracle blob to byte array, changed my entity class to

@Lob
@Column(name = "DTA_BLOB")
private byte[] DataBlob;

/**
* @return the DataBlob
*/
public byte[] getDataBlob(){
    return DataBlob;
}

/**
* @param DataBlob the DataBlob to set
*/
public void setDataBlob(byte[] DataBlob) {
    this.DataBlob = DataBlob;
}

And the DAO will be like:

@Override
@Transactional
public byte[] getMenu(Long menuDataId) throws SQLException, IOException
{
    MenuData menu_data = this.entityManager.find(MenuData.class,menuDataId);  
    return menu_data.getDataBlob();
}

Answer 2

Oracle blob can be converted to String as shown below.

byte[] bdata = blob.getBytes(1, (int)blob.length()); 
String dataStr = new String(bdata);

Some drivers support getString() on blobs although its risky to use because of encoding. In short, blob code is never that pretty. Keep in mind, string encoding can get ugly for reading blob data.