如何将Excel电子表格配置为javax.sql.DataSource？ [英] How can I configure an Excel spreadsheet as a javax.sql.DataSource?
本文介绍了如何将Excel电子表格配置为javax.sql.DataSource？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
是否有可以使用Excel作为数据源的驱动程序（最好在maven repo中可用）？我应该为url指定什么值？
修改
以下是尝试使用Excel电子表格的代码一个javax.sql.DataSource：
src / main / resources / application.yml
code> ---
spring：
个人资料：
活动：开发
---
spring：
个人资料：开发
数据源：
url：jdbc：odbc：Driver = {Microsoft Excel Driver（* .xls）}; DBQ = C：/dev/testproj/src/main/resources/test.xls
driver-类名：sun.jdbc.odbc.JdbcOdbcDriver
src / main / java / com / test / TestApplication.java
package com.test;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
public class TestApplication {
public static void main（String [] args）{
SpringApplication.run（TestApplication.class，args ）;
}
}
src / main / java / com / test / batch /BatchConfiguration.java
package com.test.batch;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import javax.sql.DataSource;
import org.springframework.batch.core.Job;
import org.springframework.batch.core.Step;
import org.springframework.batch.core.configuration.annotation.EnableBatchProcessing;
import org.springframework.batch.core.configuration.annotation.JobBuilderFactory;
import org.springframework.batch.core.configuration.annotation.StepBuilderFactory;
import org.springframework.batch.core.launch.support.RunIdIncrementer;
import org.springframework.batch.item.ItemProcessor;
import org.springframework.batch.item.ItemReader;
import org.springframework.batch.item.ItemWriter;
import org.springframework.batch.item.database.JdbcCursorItemReader;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.RowMapper;
@Configuration
@EnableBatchProcessing
public class BatchConfiguration {
@Bean
public ItemReader< String> reader（DataSource dataSource）{
JdbcCursorItemReader< String> itemReader = new JdbcCursorItemReader< String>（）;
itemReader.setDataSource（dataSource）;
System.out.println（dataSource）;
itemReader.setSql（从Sheet1中选择一个）;
itemReader.setRowMapper（new RowMapper< String>（）{
public String mapRow（ResultSet rs，int rowNum）throws SQLException {
String here = rs.getString（0）;
System.out.println（here）;
return here;
}
}）;
return itemReader;
}
@Bean
public ItemProcessor< String，String>处理器（）{
返回新的ItemProcessor< String，String>（）{
public String process（final String data）throws Exception {
return data;
}
};
}
@Bean
public ItemWriter< String>作者（DataSource dataSource）{
return new ItemWriter< String>（）{
public void write（List<？extends String> items）throws Exception {
}
};
}
@Bean
public步骤（StepBuilderFactory stepBuilderFactory，ItemReader< String> reader，ItemProcessor< String，String>处理器，ItemWriter< String>作者）{
return stepBuilderFactory.get（step）
。< String，String> chunk（1）
.reader（reader）
.processor（processor）
.writer（writer）
.build（）;
}
@Bean
public Job importUserJob（JobBuilderFactory jobs，Step step）{
return jobs.get（importUserJob）
.incrementer（new RunIdIncrementer（））
.flow（step）
.end（）
.build（）;
}
}
build.gradle
buildscript {
ext {
springBootVersion ='1.3.0.RELEASE'
}
存储库{
mavenCentral（）
}
依赖关系{
classpath（org.springframework.boot：spring-boot-gradle-plugin：$ {springBootVersion}）
$
应用插件：'java'
应用插件：'eclipse'
应用插件：'spring-boot'
jar {
baseName ='test-load'
version ='1.0'
}
sourceCompatibility = 1.8
targetCompatibility = 1.8
（$ org.springframework.boot：spring-boot-starter-batch）
$ b $
compile（'org.springframework.boot：spring-boot-starter-integration'）
testCompile（'org.springframework.boot：spring-boot-starter-test'）
}
eclipse {
classpath {
containers.remove（'org.eclipse.jdt.launching.JRE_CONTAINER'）
containers'org.eclipse.jdt.launching.JRE_CONTAINER / org.eclipse .jdt.internal.debug.ui.launcher.StandardVMType / JavaSE-1.8'
}
}
任务包装（类型：Wrapper）{
gradleVersion =' 2.9'
}
tasks.withType（Test）{
scanForTestClasses = false
include** / * Test.class
}
执行毕业生任务bootRun时会给出以下错误：
2015-12-28 16：01：18.530错误1356 --- [main] osbatch.core.step.AbstractStep：在作业中遇到错误执行步骤importUserJob
org.springframework.batch.item.ItemStreamException：无法初始化读者
在org.springframework.batch.item.support.AbstractItemCountingItemStreamItemReader.open（AbstractItemCountingItemStreamItemReader.java:147 ）〜[spring-batch-infrastructure-3.0.5.RELEASE.jar：3.0.5.RELEASE]
在org.springframework.batch.item.support.CompositeItemStream.open（CompositeItemStream.java:96）〜[ spring-batch-infrastructure-3.0.5.RELEASE.jar：3.0.5.RELEASE]
在org.springframework.batch.core.step.tasklet.TaskletStep.open（TaskletStep.java:310）〜[spring -batch-core-3.0.5.RELEASE.jar：3.0.5.RELEASE]
在org.springframework.batch.core.step.AbstractStep.execute（AbstractStep.java:197）〜[spring-batch- core-3.0.5.RELEASE.jar：3.0.5.RELEASE]
在org.springframework.batch.core.job.SimpleStepHandler.handleStep（SimpleStepHandler.java:148）[spring-batch-core-3.0。 5.RELEASE.jar：3.0.5.RELEASE]
在org.springframework.batch.core.job.flow.JobFlowExecutor.executeStep（JobFlowExecutor.java:64）[spring-batch-core-3.0.5。 RELEASE.jar：3.0.5.RELEASE]
在org.springframework.batch.core.job.flow.support.state.StepState.handle（StepState.java:67）[spring-batch-core-3.0。 5.RELEASE.jar：3.0.5.RELEASE]
在org.springframework.batch.core.job.flow.support.SimpleFlow.resume（SimpleFlow.java:169）[spring-batch-core-3.0.5.RELEASE.jar：3.0.5.RELEASE]
在org.springframework.batch.core.job.flow.support.SimpleFlow.start（SimpleFlow.java:144）[spring-batch-core-3.0.5.RELEASE.jar：3.0.5.RELEASE]
在org.springframework.batch.core.job.flow.FlowJob.doExecute（FlowJob.java:134）[spring-batch-core-3.0.5.RELEASE.jar：3.0.5.RELEASE]
在org .springframework.batch.core.job.AbstractJob.execute（AbstractJob.java:306）[spring-batch-core-3.0.5.RELEASE.jar：3.0.5.RELEASE]
在org.springframework.batch .core.launch.support.SimpleJobLauncher $ 1.run（SimpleJobLauncher.java:135）[spring-batch-core-3.0.5.RELEASE.jar：3.0.5.RELEASE]
在org.springframework.core。 task.SyncTaskExecutor.execute（SyncTaskExecutor.java:50）[spring-core-4.2.3.RELEASE.jar：4.2.3.RELEASE]
在org.springframework.batch.core.launch.support.SimpleJobLauncher。 run（SimpleJobLauncher.java:128）[spring-batch-co re-3.0.5.RELEASE.jar：3.0.5.RELEASE]
at sun.reflect.NativeMethodAccessorImpl.invoke0（Native Method）〜[na：1.8.0_66]
at sun.reflect.NativeMethodAccessorImpl .invoke（NativeMethodAccessorImpl.java:62）〜[na：1.8.0_66]
at sun.reflect.DelegatingMethodAccessorImpl.invoke（DelegatingMethodAccessorImpl.java:43）〜[na：1.8.0_66]
在java .lang.reflect.Method.invoke（Method.java:497）〜[na：1.8.0_66]
在org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection（AopUtils.java:302）[spring-aop - 4.2.2.RELEASE.jar ：4.2.3.RELEASE]
在org.springframework.aop.framework.ReflectiveMethodInvocation.proceed（ReflectiveMethodInvocation.java:157）[spring-aop-4.2.3.RELEASE.jar：4.2.3.RELEASE]
在org.springframework.batch.core.configuration.annotation.SimpleBatchConfiguration $ Passthru Advice.invoke（SimpleBatchConfiguration.java:127）[spring-batch-core-3.0.5.RELEASE.jar：3.0.5.RELEASE]
在org.springframework.aop.framework.ReflectiveMethodInvocation.proceed（ReflectiveMethodInvocation。 java：179）[spring-aop-4.2.3.RELEASE.jar：4.2.3.RELEASE]
在org.springframework.aop.framework.JdkDynamicAopProxy.invoke（JdkDynamicAopProxy.java:208）[spring-aop -4.2.3.RELEASE.jar：4.2.3.RELEASE]
在com.sun.proxy。$ Proxy45.run（未知来源）[na：na]
在org.springframework.boot。 autoconfigure.batch.JobLauncherCommandLineRunner.execute（JobLauncherCommandLineRunner.java:215）[spring-boot-autoconfigure-1.3.0.RELEASE.jar：1.3.0.RELEASE]
在org.springframework.boot.autoconfigure.batch。 JobLauncherCommandLineRunner.executeLocalJobs（JobLauncherCommandLineRunner.java:232）[spring-boot-autoconfigure-1.3.0.RELEASE.jar：1.3.0.RELEASE]
在org.springframework.boot.autoconfigure.batch.JobLauncherCommandLineRunner.launchJobFromProperties（ JobLauncherCommandLine Runner.java:124）[spring-boot-autoconfigure-1.3.0.RELEASE.jar：1.3.0.RELEASE]
在org.springframework.boot.autoconfigure.batch.JobLauncherCommandLineRunner.run（JobLauncherCommandLineRunner.java： 118）[spring-boot-autoconfigure-1.3.0.RELEASE.jar：1.3.0.RELEASE]
在org.springframework.boot.SpringApplication.callRunner（SpringApplication.java:792）[spring-boot-1.3 .0.RELEASE.jar：1.3.0.RELEASE]
在org.springframework.boot.SpringApplication.callRunners（SpringApplication.java:776）[spring-boot-1.3.0.RELEASE.jar：1.3.0 .RELEASE]
在org.springframework.boot.SpringApplication.afterRefresh（SpringApplication.java:763）[spring-boot-1.3.0.RELEASE.jar：1.3.0.RELEASE]
在org。 springFramework.boot.SpringApplication.doRun（SpringApplication.java:356）[spring-boot-1.3.0.RELEASE.jar：1.3.0.RELEASE]
在org.springframework.boot.SpringApplication.run（SpringApplication。 java：295）[spring-boot-1.3.0.RELEASE.jar：1.3.0.RELEASE]
在org.springframework.boot.Spring Application.run（SpringApplication.java:1112）[spring-boot-1.3.0.RELEASE.jar：1.3.0.RELEASE]
在org.springframework.boot.SpringApplication.run（SpringApplication.java:1101） [spring-boot-1.3.0.RELEASE.jar：1.3.0.RELEASE]
在com.test.TestApplication.main（TestApplication.java:10）[bin /：na]
引起：org.springframework.jdbc.BadSqlGrammarException：执行查询;不好的SQL语法[从Sheet1中选择一个];嵌套异常是java.sql.SQLSyntaxErrorException：用户没有找到特权或对象：在org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate（SQLExceptionSubclassTranslator.java:91）〜[spring-jdbc-4.2.3]中的SHEET1
.RELEASE.jar：4.2.3.RELEASE]
在org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate（AbstractFallbackSQLExceptionTranslator.java:73）〜[spring-jdbc-4.2.3.RELEASE.jar：4.2。 3.RELEASE]
在org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate（AbstractFallbackSQLExceptionTranslator.java:81）〜[spring-jdbc-4.2.3.RELEASE.jar：4.2.3.RELEASE]
在org.springframework.batch.item.database.JdbcCursorItemReader.openCursor（JdbcCursorItemReader.java:131）〜[spring-batch-infrastructure-3.0.5.RELEASE.jar：3.0.5.RELEASE]
在org .springframework.batch.item.database.AbstractCursorItemReader.doOpen（AbstractCursorItemReader.java:406）〜[spring-batch-infrastructure-3.0.5.RELEASE.jar ：3.0.5.RELEASE]
在org.springframework.batch.item.support.AbstractItemCountingItemStreamItemReader.open（AbstractItemCountingItemStreamItemReader.java:144）〜[spring-batch-infrastructure-3.0.5.RELEASE.jar：3.0。 5.RELEASE]
...省略36个共同的框架
导致：java.sql.SQLSyntaxErrorException：用户没有找到权限或对象：在org.hsqldb.jdbc.JDBCUtil中的SHEET1
。 sqlException（Unknown Source）〜[hsqldb-2.3.3.jar：2.3.3]
在org.hsqldb.jdbc.JDBCUtil.sqlException（Unknown Source）〜[hsqldb-2.3.3.jar：2.3.3 ]
在org.hsqldb.jdbc.JDBCPreparedStatement。< init>（Unknown Source）〜[hsqldb-2.3.3.jar：2.3.3]
在org.hsqldb.jdbc.JDBCConnection.prepareStatement （未知源）〜[hsqldb-2.3.3.jar：2.3.3]
在org.springframework.batch.item.database.JdbcCursorItemReader.openCursor（JdbcCursorItemReader.java:120）〜[spring-batch-infrastructure -3.0.5.RELEASE.jar：3.0.5.RELEASE]
... 38个共同的框架省略
造成的：org.hsqldb.Hs qlException：用户缺少权限或未找到对象：SHEET1
在org.hsqldb.error.Error.error（Unknown Source）〜[hsqldb-2.3.3.jar：2.3.3]
在org。 hsqldb.error.Error.error（Unknown Source）〜[hsqldb-2.3.3.jar：2.3.3]
在org.hsqldb.ParserDQL.readTableName（Unknown Source）〜[hsqldb-2.3.3.jar ：2.3.3]
在org.hsqldb.ParserDQL.readTableOrSubquery（Unknown Source）〜[hsqldb-2.3.3.jar：2.3.3]
在org.hsqldb.ParserDQL.XreadTableReference（未知来源）〜[hsqldb-2.3.3.jar：2.3.3]
在org.hsqldb.ParserDQL.XreadFromClause（Unknown Source）〜[hsqldb-2.3.3.jar：2.3.3]
at org.hsqldb.ParserDQL.XreadTableExpression（Unknown Source）〜[hsqldb-2.3.3.jar：2.3.3]
在org.hsqldb.ParserDQL.XreadQuerySpecification（Unknown Source）〜[hsqldb-2.3.3.jar ：2.3.3]
在org.hsqldb.ParserDQL.XreadSimpleTable（Unknown Source）〜[hsqldb-2.3.3.jar：2.3.3]
在org.hsqldb.ParserDQL.XreadQueryPrimary（未知来源）〜[hsqldb-2.3.3.jar：2.3.3]
在org.hsqldb .ParserDQL.XreadQueryTerm（Unknown Source）〜[hsqldb-2.3.3.jar：2.3.3]
在org.hsqldb.ParserDQL.XreadQueryExpressionBody（Unknown Source）〜[hsqldb-2.3.3.jar：2.3。 3]
在org.hsqldb.ParserDQL.XreadQueryExpression（Unknown Source）〜[hsqldb-2.3.3.jar：2.3.3]
在org.hsqldb.ParserDQL.compileCursorSpecification（Unknown Source）〜[ hsqldb-2.3.3.jar：2.3.3]
在org.hsqldb.ParserCommand.compilePart（未知源）〜[hsqldb-2.3.3.jar：2.3.3]
在org.hsqldb .ParserCommand.compileStatement（Unknown Source）〜[hsqldb-2.3.3.jar：2.3.3]
在org.hsqldb.Session.compileStatement（Unknown Source）〜[hsqldb-2.3.3.jar：2.3。 3]
在org.hsqldb.StatementManager.compile（Unknown Source）〜[hsqldb-2.3.3.jar：2.3.3]
在org.hsqldb.Session.execute（Unknown Source）〜[ hsqldb-2.3.3.jar：2.3.3]
解决方案
对于Excel文件c：/temp/test1.xlsx和'Table'abc。您必须以这种形式完全写出驱动程序名称！
对于Java8，您需要将所有sun包类从jre7复制到新的jar文件加上jdbcOdbc.dll从jre7 bin文件夹在您的项目文件夹中。
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCExcelConnection {
public static void main（String [] args）{
Connection connection = null;
语句语句= null;
ResultSet resultSet = null;
try {
Class.forName（sun.jdbc.odbc.JdbcOdbcDriver）;
connection = DriverManager
.getConnection（jdbc：odbc：Driver = {Microsoft Excel Driver（* .xls，* .xlsx，* .xlsm，* .xlsb）}; DBQ = c：\ \Temp\\test1.xlsx; readOnly的= FALSE）;
statement = connection.createStatement（）;
resultSet = statement.executeQuery（SELECT * FROM [abc $]）;
} catch（ClassNotFoundException e）{
System.err.println（ClassNotFoundException by driver load - + e）;
} catch（SQLException e）{
System.err.println（SQLException by connect - + e）;
} finally {
if（connection！= null）{
try {
connection.close（）;
} catch（Exception e）{
}
}
if（statement！= null）{
try {
statement.close（）;
} catch（Exception e）{
}
}
if（resultSet！= null）{
try {
resultSet.close（）;
} catch（Exception e）{
}
}
}
}
}
I have a Spring Boot 1.3.0 (Java 8) application that needs to use Excel as a data source. (POI and JXL do not work due to type of Excel file.) The application needs to run in an unix environment. I have the application configured to set the datasource url and driver-class-name via a yml file.
Is there a driver (preferably available in the maven repo) that can use Excel as a data source? What value should I specify for the url?
Edit
Here's the code from an attempt to use the Excel spreadsheet as a javax.sql.DataSource:
src/main/resources/application.yml
---
spring:
profiles:
active: development
---
spring:
profiles: development
datasource:
url: jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=C:/dev/testproj/src/main/resources/test.xls
driver-class-name: sun.jdbc.odbc.JdbcOdbcDriver
src/main/java/com/test/TestApplication.java
package com.test;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
public class TestApplication {
public static void main(String[] args) {
SpringApplication.run(TestApplication.class, args);
}
}
src/main/java/com/test/batch/BatchConfiguration.java
package com.test.batch;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import javax.sql.DataSource;
import org.springframework.batch.core.Job;
import org.springframework.batch.core.Step;
import org.springframework.batch.core.configuration.annotation.EnableBatchProcessing;
import org.springframework.batch.core.configuration.annotation.JobBuilderFactory;
import org.springframework.batch.core.configuration.annotation.StepBuilderFactory;
import org.springframework.batch.core.launch.support.RunIdIncrementer;
import org.springframework.batch.item.ItemProcessor;
import org.springframework.batch.item.ItemReader;
import org.springframework.batch.item.ItemWriter;
import org.springframework.batch.item.database.JdbcCursorItemReader;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.RowMapper;
@Configuration
@EnableBatchProcessing
public class BatchConfiguration {
@Bean
public ItemReader<String> reader(DataSource dataSource) {
JdbcCursorItemReader<String> itemReader = new JdbcCursorItemReader<String>();
itemReader.setDataSource(dataSource);
System.out.println(dataSource);
itemReader.setSql("SELECT a from Sheet1");
itemReader.setRowMapper(new RowMapper<String>() {
public String mapRow(ResultSet rs, int rowNum) throws SQLException {
String here= rs.getString(0);
System.out.println(here);
return here;
}
});
return itemReader;
}
@Bean
public ItemProcessor<String, String> processor() {
return new ItemProcessor<String, String>() {
public String process(final String data) throws Exception {
return data;
}
};
}
@Bean
public ItemWriter<String> writer(DataSource dataSource) {
return new ItemWriter<String>() {
public void write(List<? extends String> items) throws Exception {
}
};
}
@Bean
public Step step(StepBuilderFactory stepBuilderFactory, ItemReader<String> reader, ItemProcessor<String, String> processor, ItemWriter<String> writer) {
return stepBuilderFactory.get("step")
.<String, String> chunk(1)
.reader(reader)
.processor(processor)
.writer(writer)
.build();
}
@Bean
public Job importUserJob(JobBuilderFactory jobs, Step step) {
return jobs.get("importUserJob")
.incrementer(new RunIdIncrementer())
.flow(step)
.end()
.build();
}
}
build.gradle
buildscript {
ext {
springBootVersion = '1.3.0.RELEASE'
}
repositories {
mavenCentral()
}
dependencies {
classpath("org.springframework.boot:spring-boot-gradle-plugin:${springBootVersion}")
}
}
apply plugin: 'java'
apply plugin: 'eclipse'
apply plugin: 'spring-boot'
jar {
baseName = 'test-load'
version = '1.0'
}
sourceCompatibility = 1.8
targetCompatibility = 1.8
repositories {
mavenCentral()
}
dependencies {
compile('org.springframework.boot:spring-boot-starter-batch')
compile('org.springframework.boot:spring-boot-starter-integration')
testCompile('org.springframework.boot:spring-boot-starter-test')
}
eclipse {
classpath {
containers.remove('org.eclipse.jdt.launching.JRE_CONTAINER')
containers 'org.eclipse.jdt.launching.JRE_CONTAINER/org.eclipse.jdt.internal.debug.ui.launcher.StandardVMType/JavaSE-1.8'
}
}
task wrapper(type: Wrapper) {
gradleVersion = '2.9'
}
tasks.withType(Test) {
scanForTestClasses = false
include "**/*Test.class"
}
The following error is given when executing the gradle task bootRun:
2015-12-28 16:01:18.530 ERROR 1356 --- [ main] o.s.batch.core.step.AbstractStep : Encountered an error executing step step in job importUserJob
org.springframework.batch.item.ItemStreamException: Failed to initialize the reader
at org.springframework.batch.item.support.AbstractItemCountingItemStreamItemReader.open(AbstractItemCountingItemStreamItemReader.java:147) ~[spring-batch-infrastructure-3.0.5.RELEASE.jar:3.0.5.RELEASE]
at org.springframework.batch.item.support.CompositeItemStream.open(CompositeItemStream.java:96) ~[spring-batch-infrastructure-3.0.5.RELEASE.jar:3.0.5.RELEASE]
at org.springframework.batch.core.step.tasklet.TaskletStep.open(TaskletStep.java:310) ~[spring-batch-core-3.0.5.RELEASE.jar:3.0.5.RELEASE]
at org.springframework.batch.core.step.AbstractStep.execute(AbstractStep.java:197) ~[spring-batch-core-3.0.5.RELEASE.jar:3.0.5.RELEASE]
at org.springframework.batch.core.job.SimpleStepHandler.handleStep(SimpleStepHandler.java:148) [spring-batch-core-3.0.5.RELEASE.jar:3.0.5.RELEASE]
at org.springframework.batch.core.job.flow.JobFlowExecutor.executeStep(JobFlowExecutor.java:64) [spring-batch-core-3.0.5.RELEASE.jar:3.0.5.RELEASE]
at org.springframework.batch.core.job.flow.support.state.StepState.handle(StepState.java:67) [spring-batch-core-3.0.5.RELEASE.jar:3.0.5.RELEASE]
at org.springframework.batch.core.job.flow.support.SimpleFlow.resume(SimpleFlow.java:169) [spring-batch-core-3.0.5.RELEASE.jar:3.0.5.RELEASE]
at org.springframework.batch.core.job.flow.support.SimpleFlow.start(SimpleFlow.java:144) [spring-batch-core-3.0.5.RELEASE.jar:3.0.5.RELEASE]
at org.springframework.batch.core.job.flow.FlowJob.doExecute(FlowJob.java:134) [spring-batch-core-3.0.5.RELEASE.jar:3.0.5.RELEASE]
at org.springframework.batch.core.job.AbstractJob.execute(AbstractJob.java:306) [spring-batch-core-3.0.5.RELEASE.jar:3.0.5.RELEASE]
at org.springframework.batch.core.launch.support.SimpleJobLauncher$1.run(SimpleJobLauncher.java:135) [spring-batch-core-3.0.5.RELEASE.jar:3.0.5.RELEASE]
at org.springframework.core.task.SyncTaskExecutor.execute(SyncTaskExecutor.java:50) [spring-core-4.2.3.RELEASE.jar:4.2.3.RELEASE]
at org.springframework.batch.core.launch.support.SimpleJobLauncher.run(SimpleJobLauncher.java:128) [spring-batch-core-3.0.5.RELEASE.jar:3.0.5.RELEASE]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_66]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_66]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_66]
at java.lang.reflect.Method.invoke(Method.java:497) ~[na:1.8.0_66]
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:302) [spring-aop-4.2.3.RELEASE.jar:4.2.3.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190) [spring-aop-4.2.3.RELEASE.jar:4.2.3.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157) [spring-aop-4.2.3.RELEASE.jar:4.2.3.RELEASE]
at org.springframework.batch.core.configuration.annotation.SimpleBatchConfiguration$PassthruAdvice.invoke(SimpleBatchConfiguration.java:127) [spring-batch-core-3.0.5.RELEASE.jar:3.0.5.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) [spring-aop-4.2.3.RELEASE.jar:4.2.3.RELEASE]
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:208) [spring-aop-4.2.3.RELEASE.jar:4.2.3.RELEASE]
at com.sun.proxy.$Proxy45.run(Unknown Source) [na:na]
at org.springframework.boot.autoconfigure.batch.JobLauncherCommandLineRunner.execute(JobLauncherCommandLineRunner.java:215) [spring-boot-autoconfigure-1.3.0.RELEASE.jar:1.3.0.RELEASE]
at org.springframework.boot.autoconfigure.batch.JobLauncherCommandLineRunner.executeLocalJobs(JobLauncherCommandLineRunner.java:232) [spring-boot-autoconfigure-1.3.0.RELEASE.jar:1.3.0.RELEASE]
at org.springframework.boot.autoconfigure.batch.JobLauncherCommandLineRunner.launchJobFromProperties(JobLauncherCommandLineRunner.java:124) [spring-boot-autoconfigure-1.3.0.RELEASE.jar:1.3.0.RELEASE]
at org.springframework.boot.autoconfigure.batch.JobLauncherCommandLineRunner.run(JobLauncherCommandLineRunner.java:118) [spring-boot-autoconfigure-1.3.0.RELEASE.jar:1.3.0.RELEASE]
at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:792) [spring-boot-1.3.0.RELEASE.jar:1.3.0.RELEASE]
at org.springframework.boot.SpringApplication.callRunners(SpringApplication.java:776) [spring-boot-1.3.0.RELEASE.jar:1.3.0.RELEASE]
at org.springframework.boot.SpringApplication.afterRefresh(SpringApplication.java:763) [spring-boot-1.3.0.RELEASE.jar:1.3.0.RELEASE]
at org.springframework.boot.SpringApplication.doRun(SpringApplication.java:356) [spring-boot-1.3.0.RELEASE.jar:1.3.0.RELEASE]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:295) [spring-boot-1.3.0.RELEASE.jar:1.3.0.RELEASE]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1112) [spring-boot-1.3.0.RELEASE.jar:1.3.0.RELEASE]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1101) [spring-boot-1.3.0.RELEASE.jar:1.3.0.RELEASE]
at com.test.TestApplication.main(TestApplication.java:10) [bin/:na]
Caused by: org.springframework.jdbc.BadSqlGrammarException: Executing query; bad SQL grammar [SELECT a from Sheet1]; nested exception is java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: SHEET1
at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:91) ~[spring-jdbc-4.2.3.RELEASE.jar:4.2.3.RELEASE]
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73) ~[spring-jdbc-4.2.3.RELEASE.jar:4.2.3.RELEASE]
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-4.2.3.RELEASE.jar:4.2.3.RELEASE]
at org.springframework.batch.item.database.JdbcCursorItemReader.openCursor(JdbcCursorItemReader.java:131) ~[spring-batch-infrastructure-3.0.5.RELEASE.jar:3.0.5.RELEASE]
at org.springframework.batch.item.database.AbstractCursorItemReader.doOpen(AbstractCursorItemReader.java:406) ~[spring-batch-infrastructure-3.0.5.RELEASE.jar:3.0.5.RELEASE]
at org.springframework.batch.item.support.AbstractItemCountingItemStreamItemReader.open(AbstractItemCountingItemStreamItemReader.java:144) ~[spring-batch-infrastructure-3.0.5.RELEASE.jar:3.0.5.RELEASE]
... 36 common frames omitted
Caused by: java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: SHEET1
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
at org.hsqldb.jdbc.JDBCPreparedStatement.<init>(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
at org.hsqldb.jdbc.JDBCConnection.prepareStatement(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
at org.springframework.batch.item.database.JdbcCursorItemReader.openCursor(JdbcCursorItemReader.java:120) ~[spring-batch-infrastructure-3.0.5.RELEASE.jar:3.0.5.RELEASE]
... 38 common frames omitted
Caused by: org.hsqldb.HsqlException: user lacks privilege or object not found: SHEET1
at org.hsqldb.error.Error.error(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
at org.hsqldb.error.Error.error(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
at org.hsqldb.ParserDQL.readTableName(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
at org.hsqldb.ParserDQL.readTableOrSubquery(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
at org.hsqldb.ParserDQL.XreadTableReference(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
at org.hsqldb.ParserDQL.XreadFromClause(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
at org.hsqldb.ParserDQL.XreadTableExpression(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
at org.hsqldb.ParserDQL.XreadQuerySpecification(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
at org.hsqldb.ParserDQL.XreadSimpleTable(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
at org.hsqldb.ParserDQL.XreadQueryPrimary(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
at org.hsqldb.ParserDQL.XreadQueryTerm(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
at org.hsqldb.ParserDQL.XreadQueryExpressionBody(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
at org.hsqldb.ParserDQL.XreadQueryExpression(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
at org.hsqldb.ParserDQL.compileCursorSpecification(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
at org.hsqldb.ParserCommand.compilePart(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
at org.hsqldb.ParserCommand.compileStatement(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
at org.hsqldb.Session.compileStatement(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
at org.hsqldb.StatementManager.compile(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
at org.hsqldb.Session.execute(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
解决方案
For Excel file c:/temp/test1.xlsx and 'Table' abc. You have to write driver name exactly in this form!
For Java8 you need to copy all sun package classes from jre7 to new jar file plus JdbcOdbc.dll from jre7 bin folder in your project folder.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCExcelConnection {
public static void main(String[] args) {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
connection = DriverManager
.getConnection("jdbc:odbc:Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=c:\\Temp\\test1.xlsx;readOnly=false");
statement = connection.createStatement();
resultSet = statement.executeQuery("SELECT * FROM [abc$]");
} catch (ClassNotFoundException e) {
System.err.println("ClassNotFoundException by driver load - " + e);
} catch (SQLException e) {
System.err.println("SQLException by connect - " + e);
} finally {
if (connection != null) {
try {
connection.close();
} catch (Exception e) {
}
}
if (statement != null) {
try {
statement.close();
} catch (Exception e) {
}
}
if (resultSet != null) {
try {
resultSet.close();
} catch (Exception e) {
}
}
}
}
}
这篇关于如何将Excel电子表格配置为javax.sql.DataSource？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
