Unable to insert BLOB data into mysql database

[laxmiraghu]

Hi,

I'm using spring framework for developing portal and mysql as database.

I've a requirement wherein user uploads photo and the same will be displayed back to him when he requests.

I've a problem in uploading the photo into mysql DB.

Here is my program.

JdbcTemplate jt1 = new JdbcTemplate(dataSource);

Blob blob = jt1.getDataSource().getConnection().createBlob();
            blob.setBytes(1, file.getFile());
            
            String sqlStr = "insert into photos (email, tag, photo, description) values ('abc@yahoo.com', 'tag', " + blob + ", 'photo description')";
            
            
            jt1.execute(sqlStr);

But i'm getting following exception when i try to insert blob data.

java.lang.ClassNotFoundException: org.springframework.dao.TransientDataAccessResourceException
        at org.apache.catalina.loader.WebappClassLoader.loadClass(WebappClassLoader.java:1359)
        at org.apache.catalina.loader.WebappClassLoader.loadClass(WebappClassLoader.java:1205)
        at java.lang.ClassLoader.loadClassInternal(ClassLoader.java:320)
        at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.<init>(SQLErrorCodeSQLExceptionTranslator.java:96)
        at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.<init>(SQLErrorCodeSQLExceptionTranslator.java:109)
        at org.springframework.jdbc.support.JdbcAccessor.getExceptionTranslator(JdbcAccessor.java:99)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:407)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:429)
        at com.mosonex.portal.services.RegistrationServiceImpl.uploadMedia(RegistrationServiceImpl.java:308)
        at com.mosonex.portal.controller.UploadPhotoController.onSubmitAction(UploadPhotoController.java:72)
        at org.springframework.web.portlet.mvc.SimpleFormController.processFormSubmission(SimpleFormController.java:322)
        at org.springframework.web.portlet.mvc.AbstractFormController.handleActionRequestInternal(AbstractFormController.java:423)
        at org.springframework.web.portlet.mvc.AbstractController.handleActionRequest(AbstractController.java:224)
        at org.springframework.web.portlet.mvc.SimpleControllerHandlerAdapter.handleAction(SimpleControllerHandlerAdapter.java:52)
        at org.springframework.web.portlet.DispatcherPortlet.doActionService(DispatcherPortlet.java:604)
        at org.springframework.web.portlet.FrameworkPortlet.processRequest(FrameworkPortlet.java:417)
        at org.springframework.web.portlet.FrameworkPortlet.processAction(FrameworkPortlet.java:400)
        at org.apache.jetspeed.factory.JetspeedPortletInstance.processAction(JetspeedPortletInstance.java:97)
        at org.apache.jetspeed.container.JetspeedContainerServlet.doGet(JetspeedContainerServlet.java:258)
        at org.apache.jetspeed.container.JetspeedContainerServlet.doPost(JetspeedContainerServlet.java:396)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:710)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:269)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
        at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:691)
        at org.apache.catalina.core.ApplicationDispatcher.doInclude(ApplicationDispatcher.java:594)
        at org.apache.catalina.core.ApplicationDispatcher.include(ApplicationDispatcher.java:505)
        at org.apache.jetspeed.container.invoker.ServletPortletInvoker.invoke(ServletPortletInvoker.java:273)
        at org.apache.jetspeed.container.invoker.ServletPortletInvoker.action(ServletPortletInvoker.java:148)

Please help. Any help is highly appreciated.

Thank you.

[noon]

Which spring version are you using?

The error message refers to a missing class "org.springframework.dao.TransientDataAccessResour ceException" which can be found from spring.jar (spring version 2.5.x). According to API this class has existed from version 2.5 not in previous versions.

So check your JAR and Spring versions.

[laxmiraghu]

Yes. I had two versions of spring jars in my classpath which caused the problem.

Now i'm facing a different exception of which stack trace is given below:

org.springframework.jdbc.InvalidResultSetAccessException: getByte Failed on value ( [B@1f7a434 ) in column 1; nested exception is java.sql.SQLException: getByte Failed on value ( [B@1f7a434 ) in column 1
        at org.springframework.jdbc.support.rowset.ResultSetWrappingSqlRowSet.getByte(ResultSetWrappingSqlRowSet.java:173)
        at com.mosonex.portal.services.RegistrationServiceImpl.getMedia(RegistrationServiceImpl.java:324)
        at com.mosonex.portal.servlet.ConfirmRegistration.getPhoto(ConfirmRegistration.java:130)
        at com.mosonex.portal.servlet.ConfirmRegistration.processRequest(ConfirmRegistration.java:55)
        at com.mosonex.portal.servlet.ConfirmRegistration.doGet(ConfirmRegistration.java:77)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:690)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:269)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:210)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:174)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:117)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:108)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:151)
        at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:870)
        at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:665)
        at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:528)
        at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:81)
        at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:685)
        at java.lang.Thread.run(Thread.java:619)
Caused by: java.sql.SQLException: getByte Failed on value ( [B@1f7a434 ) in column 1
        at com.sun.rowset.CachedRowSetImpl.getByte(CachedRowSetImpl.java:1794)
        at org.springframework.jdbc.support.rowset.ResultSetWrappingSqlRowSet.getByte(ResultSetWrappingSqlRowSet.java:170)
        ... 20 more

Can anyone help on this please?

I'm trying to read BLOB data as bytes. Here is the code snippet.

JdbcTemplate jt1 = new JdbcTemplate(dataSource);
            
            String sql = "select photo from photos where email='" + email + "'";
               
            System.out.println("Sql String is -----> " + sql);
            
            SqlRowSet	myRows1 =  jt1. queryForRowSet(sql);
            myRows1.first();
            
            Upload upload = new Upload();
            
            byte[] byteArr = new byte[1];
            byteArr[1] = myRows1.getByte(1);
            upload.setFile(byteArr);

[laxmiraghu]

HI All,

I found a crud way of uploading / downloading images from mysql. Below is the code for uploading the image into mysql.

            JdbcTemplate jt1 = new JdbcTemplate(dataSource);            
            
            Blob blob = jt1.getDataSource().getConnection().createBlob();
            blob.setBytes(1, file.getFile());
            
            ResultSet rs = null;
            PreparedStatement pstmt = null;
            
            try {
                pstmt = jt1.getDataSource().getConnection().prepareStatement("insert into photos (email, tag, photo, description) values (?, ?, ?, ?)");
                
                pstmt.setString(1, "abc@yahoo.com");
                pstmt.setString(2, "tag");
                pstmt.setBlob(3, blob);
                pstmt.setString(4, "desc");
                
                int i = pstmt.executeUpdate();
                
                if(i>0) {
                  System.out.println("Uploaded successfully !");
                 }
                else {
                System.out.println("unsucessfull to upload image.");
                  }

            } catch (Exception e) {
                throw e;
            }

and for downloading the image, here is the code:

            JdbcTemplate jt1 = new JdbcTemplate(dataSource);
            
            String sql = "select photo from photos where email='" + email + "'"
            

            Upload upload = new Upload();
            try {
                       Statement s = jt1.getDataSource().getConnection().createStatement();
                       ResultSet rst = s.executeQuery(sql);
                       rst.first();
                       byte [] barray = rst.getBytes("photo");

                       upload.setFile(barray);
                       rst.close();
                       s.close();
            } catch(SQLException sqle) {
                throw sqle;
            }

Please do let me know if there is a better way of doing it. Thanks for any help in this regard.