Wie kann ich eine Excel-Tabelle als javax.sql.DataSource konfigurieren?
Ich habe eine Spring Boot 1.3.0 (Java 8) -Anwendung, die Excel als Datenquelle verwenden muss. (POI und JXL funktionieren aufgrund des Excel-Dateityps nicht.) Die Anwendung muss in einer Unix-Umgebung ausgeführt werden. Ich habe die Anwendung so konfiguriert, dass die URL der Datenquelle und der Name der Treiberklasse über eine yml-Datei festgelegt werden.
Gibt es einen Treiber (vorzugsweise im Maven Repo verfügbar), der Excel als Datenquelle verwenden kann? Welchen Wert soll ich für die URL angeben?
Bearbeite
Hier ist der Code aus einem Versuch, die Excel-Tabelle als javax.sql.DataSource zu verwenden:
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"
}
Der folgende Fehler wird beim Ausführen der Gradle-Task bootRun ausgegeben:
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]