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?

Solution 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();
}

Solution 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.

Comments

[Olivier Masseau]

I suggest you use new String(bdata, encoding) where encoding is your string encoding (ex: "UTF-8"). It is a good practive to always specifiy the encoding when converting bytes to a String.

[RBz]

I did change the code like that and tried deploying the war but when i try to access this service its giving me this error (from tomcat log) : 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(DatabaseE‌​rror.java:112) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseE‌​rror.java:146) at oracle.jdbc.driver.DatabaseError.throwSqlExcepti

[Codo]

When you finally try to convert it, the database connection has already been closed. A BLOB instance is just a pointer to the binary data. To retrieve it, you still need an open connection. So try to do the conversion earlier in the code when the connection is still open.

[RBz]

Can you please say what you mean by "earlier in the connection". Does that mean do the conversion within the entity class getter itself?? Because it too does'nt work. It also leads to closed connection. Please forgive my ignorance and give a little explanation on the idea you mentioned. @Codo

[Codo]

When you retrieve data from the database, you need a working database connection. The moment you retrieved the table row with the BLOB, you obviously had a connection. Yet when you try to access the contents of the BLOB, the database connection has been closed in the mean time. So you obviously don't have a database connection during the entire time of your web request. You need to understand during which parts of your request the connection is available and move the code to access the BLOB contents to that part.

[RBz]

will that require me to write a seperate class for it: I saw something here sourceforge.net/p/dbunit/feature-requests/86 I cant understand where to move my code to...as the the maximum i could move is till entity class and still its not working!!! :(

[RBz]

I still have doubt if this is the best way to do it or not. As byte[] may persist the entire data. And as the size of the file increases it may occupy more space. Any suggestions to improve this are welcome.

[Marcus Wolschon]

Java Strings are limited in size to 64KB.