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?
BLOB stands for Binary Large Objects and as its name suggests, it can be used for storing binary data while TEXT is used for storing large number of strings. BLOB can be used to store binary data that means we can store pictures, videos, sounds and programs also.
The getBytes() method of the Blob Interface retrieves the contents of the current Blob object and returns as a byte array. Using the getBlob() method you can get the contents of the blob in to a byte array and create an image using the write() method of the FileOutputStream object.
The @Lob annotation specifies that the database should store the property as Large Object. The columnDefinition in the @Column annotation defines the column type for the property. Since we're going to save byte array, we're using BLOB.
Databases use the data types BLOB (binary large object) and CLOB (character large object) to store large objects, like images and very long texts. JPA and Hibernate provide two kinds of mappings for these types. You can choose if you want to: Materialize the LOB and map it to a byte[] or a String.
In this post, I am giving an example of inserting BLOB data into database using hibernate and getting this data from database, using hibernate entities. In short, inserting and fetching BLOB data such as images requires two steps: define database column type as “BLOB” and have a field in entity of type “byte array”.
Databases use the data types BLOB (binary large object) and CLOB (character large object) to store large objects, like images and very long texts. JPA and Hibernate provide two kinds of mappings for these types.
In short, inserting and fetching BLOB data such as images requires two steps: define database column type as “BLOB” and have a field in entity of type “byte array”. Lets take an example, in which, I am inserting “test.png” image from windows C drive to database (MySQL).
Byte array or Blob can be used to store large binary data such as files and images. According to Hibernate Types: A primitive byte[] is mapped to a JDBC VARBINARY. A java.sql.Blob is mapped to a JDBC BLOB.
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();
}
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With