Load data from CSV to mySQL database Java+hibernate+spring

Question

I am trying to load a CSV file in to mySQL database using Java+Hibernate+Spring. I am using the following query in the DAO to help me load in to the database:

entityManager.createQuery("LOAD DATA INFILE :fileName INTO TABLE test").setParameter("fileName", "C:\\samples\\test\\abcd.csv").executeUpdate();

I got some idea to use this from http://dev.mysql.com/doc/refman/5.1/en/load-data.html and how to import a csv file into a mysql from an hibernate+spring application?

But I am getting the error:

java.lang.IllegalArgumentException: node to traverse cannot be null!

Please help! Thanks

EDIT: Here is the stacktrace

    SEVERE: Servlet.service() for servlet [Faces Servlet] in context with path [/EMS] threw exception [/pages/upload.xhtml at line 82 and column 108 action="#{uploadBean.upload}": java.lang.IllegalArgumentException: node to traverse cannot be null!] with root cause
java.lang.IllegalArgumentException: node to traverse cannot be null!
    at org.hibernate.hql.ast.util.NodeTraverser.traverseDepthFirst(NodeTraverser.java:63)
    at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:280)
    at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:182)
    at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:136)
    at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:101)
    at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:80)
    at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:124)
    at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:156)
    at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:135)
    at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1770)
    at org.hibernate.ejb.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:272)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at org.springframework.orm.jpa.SharedEntityManagerCreator$SharedEntityManagerInvocationHandler.invoke(SharedEntityManagerCreator.java:240)
    at $Proxy19.createQuery(Unknown Source)
    at com.repositories.impl.UploadDAOImpl.upload(UploadDAOImpl.java:33)
    at com.services.impl.UploadServiceImpl.upload(UploadServiceImpl.java:25)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:309)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
    at $Proxy26.upload(Unknown Source)
    at com.managedbeans.UploadBean.uploadData(UploadBean.java:40)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at org.apache.el.parser.AstValue.invoke(AstValue.java:262)
    at org.apache.el.MethodExpressionImpl.invoke(MethodExpressionImpl.java:278)
    at org.apache.myfaces.view.facelets.el.TagMethodExpression.invoke(TagMethodExpression.java:83)
    at javax.faces.component._MethodExpressionToMethodBinding.invoke(_MethodExpressionToMethodBinding.java:88)
    at org.apache.myfaces.application.ActionListenerImpl.processAction(ActionListenerImpl.java:100)
    at javax.faces.component.UICommand.broadcast(UICommand.java:120)
    at javax.faces.component.UIViewRoot._broadcastAll(UIViewRoot.java:890)
    at javax.faces.component.UIViewRoot.broadcastEvents(UIViewRoot.java:234)
    at javax.faces.component.UIViewRoot._process(UIViewRoot.java:1202)
    at javax.faces.component.UIViewRoot.processApplication(UIViewRoot.java:623)
    at org.apache.myfaces.lifecycle.InvokeApplicationExecutor.execute(InvokeApplicationExecutor.java:35)
    at org.apache.myfaces.lifecycle.LifecycleImpl.executePhase(LifecycleImpl.java:143)
    at org.apache.myfaces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:93)
    at javax.faces.webapp.FacesServlet.service(FacesServlet.java:189)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:304)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:343)
    at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:109)
    at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:83)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
    at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:97)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
    at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:100)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
    at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:78)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
    at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:54)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
    at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:35)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
    at org.springframework.security.web.authentication.www.BasicAuthenticationFilter.doFilter(BasicAuthenticationFilter.java:177)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
    at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:187)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
    at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:105)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
    at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:79)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
    at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:149)
    at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:237)
    at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:167)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
    at org.primefaces.webapp.filter.FileUploadFilter.doFilter(FileUploadFilter.java:77)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:224)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:169)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:168)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:100)
    at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:929)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:405)
    at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:964)
    at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:515)
    at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:304)
    at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
    at java.lang.Thread.run(Thread.java:662)

just glancing at this, I'm curious how the createQuery method would know that "fileName" is a parameter to be replaced (by setParameter). Are you sure this substitution is happening? (I'm not familiar with this library so just stabbing in the dark) — Yevgeny Simkin
– Yevgeny Simkin, Commented Jun 2, 2012 at 22:39
The filename in LOAD DATA INFILE <file> needs to be on the mysql server. Does mysql have access to the file? Also, please post the whole of the stacktrace for your exception. — matt b
– matt b, Commented Jun 2, 2012 at 22:49
How do you give mysql the access to the file? Please suggest — mona
– mona, Commented Jun 2, 2012 at 23:11

Bhesh Gurung · Accepted Answer · 2012-06-02 22:47:47Z

5

Since, it's a native query.

Try: entityManager.createNativeQuery.

instead of: entityManager.createQuery.

answered Jun 2, 2012 at 22:47

Bhesh Gurung

51.1k23 gold badges96 silver badges147 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

mona Over a year ago

Now I am gettingthe error: javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not execute native bulk manipulation query

Bhesh Gurung Over a year ago

Can you post the whole stacktrace?

mona Over a year ago

Thanks Bhesh it works now!! I had the data in incorrect format

Amol Dahe · Accepted Answer · 2015-08-11 06:06:03Z

1

    Query query = sessionFactory.getCurrentSession().createSQLQuery("LOAD DATA INFILE :filename INTO TABLE TestTable FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (txn_amount,card_number,terminal_id)").setString("filename", "E:/amol.csv");
    int rowCount = query.executeUpdate();

answered Aug 11, 2015 at 6:06

Amol Dahe

111 bronze badge

1 Comment

realpac Over a year ago

In case of windows, LINES TERMINATED BY '\n' should be replaced by LINES TERMINATED BY '\r\n'

Collectives™ on Stack Overflow

Load data from CSV to mySQL database Java+hibernate+spring

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related