logo标签列表

将CSV数据加载到mySQL数据库中 Java+hibernate+spring

javamysqlspringhibernatehql
3

我正在尝试使用Java+Hibernate+Spring将CSV文件加载到MySQL数据库中。我在DAO中使用以下查询来帮助我加载到数据库中:

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

我得到了一些灵感，可以从http://dev.mysql.com/doc/refman/5.1/en/load-data.html和如何将CSV文件从hibernate+spring应用程序导入到mysql中？中使用它。
但是我遇到了错误：
java.lang.IllegalArgumentException: node to traverse cannot be null!

请帮忙！谢谢
编辑：这是堆栈跟踪
    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)
仅仅扫了一眼，我就很好奇 createQuery 方法是怎么知道 "fileName" 是一个需要被 setParameter 替换的参数。你确定这个替换过程确实发生了吗？（我对这个库不熟悉，所以只是瞎猜） - Yevgeny Simkin
LOAD DATA INFILE <file> 中的文件名需要在 MySQL 服务器上。MySQL 是否可以访问该文件？另外，请发布异常的整个堆栈跟踪。 - matt b
你如何给MySQL访问文件的权限？请提供建议。 - mona
2个回答
5

由于这是一个本地查询。

请尝试使用：entityManager.createNativeQuery。

而不是：entityManager.createQuery。

现在我遇到了错误: javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException：无法执行本地批量操作查询 - mona
你能发布完整的堆栈跟踪吗？ - Bhesh Gurung
谢谢Bhesh，现在可以了！我之前的数据格式不正确。 - mona
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();
在Windows系统中，应将“LINES TERMINATED BY '\n'”替换为“LINES TERMINATED BY '\r\n'”。 - realpac
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接