时间戳

首页 > 解决方案 > 在springbatch中连接到数据库时表不存在

mysql - 在springbatch中连接到数据库时表不存在

问题描述

我有一个 user.csv 文件。我想将 csv 文件的数据存储到 sql 数据库中。我在 yml 文件中提供了正确的 sql 连接，但它会引发运行时异常

Caused by: java.sql.SQLSyntaxErrorException: Table 'login.batch_job_instance' doesn't exist
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) ~[mysql-connector-java-8.0.26.jar:8.0.26]
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-java-8.0.26.jar:8.0.26]
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953) ~[mysql-connector-java-8.0.26.jar:8.0.26]
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:1003) ~[mysql-connector-java-8.0.26.jar:8.0.26]
    at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52) ~[HikariCP-4.0.3.jar:na]
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java) ~[HikariCP-4.0.3.jar:na]
    at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:722) ~[spring-jdbc-5.3.10.jar:5.3.10]
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:651) ~[spring-jdbc-5.3.10.jar:5.3.10]
    ... 42 common frames omitted

2021-09-27 16:31:27.913  INFO 3800 --- [           main] j.LocalContainerEntityManagerFactoryBean : Closing JPA EntityManagerFactory for persistence unit 'default'
2021-09-27 16:31:27.915  INFO 3800 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown initiated...
2021-09-27 16:31:27.922  INFO 3800 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown completed.

Process finished with exit code 1

批处理配置

package com.nilmani.literalmission.config

import com.nilmani.literalmission.model.User
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.file.FlatFileItemReader
import org.springframework.batch.item.file.LineMapper
import org.springframework.batch.item.file.mapping.BeanWrapperFieldSetMapper
import org.springframework.batch.item.file.mapping.DefaultLineMapper
import org.springframework.batch.item.file.transform.DelimitedLineTokenizer
import org.springframework.context.annotation.Bean
import org.springframework.context.annotation.Configuration
import org.springframework.core.io.FileSystemResource


@Configuration
@EnableBatchProcessing
class BatchConfig {
    @Bean
    fun job(jobBuilderFactory: JobBuilderFactory,
            stepBuilderFactory: StepBuilderFactory,
            itemReader: ItemReader<User>,
            itemProcessor: ItemProcessor<User,User>,
            itemWriter: ItemWriter<User>
            ):Job{
        val step: Step = stepBuilderFactory["ETL-file-load"]
            .chunk<User, User>(100)
            .reader(itemReader)
            .processor(itemProcessor)
            .writer(itemWriter)
            .build()
        return jobBuilderFactory["ETL-Load"]
            .incrementer(RunIdIncrementer())
            .start(step)
            .build()

    }
    @Bean
    fun itemReader(): FlatFileItemReader<User>? {
        val flatFileItemReader: FlatFileItemReader<User> = FlatFileItemReader<User>()
        flatFileItemReader.setResource(FileSystemResource("src/main/resources/users.csv"))
        flatFileItemReader.setName("CSV-Reader")
        flatFileItemReader.setLinesToSkip(1)
        flatFileItemReader.setLineMapper(lineMapper()!!)
        return flatFileItemReader
    }

    @Bean
    fun lineMapper(): LineMapper<User>? {
        val defaultLineMapper: DefaultLineMapper<User> = DefaultLineMapper<User>()
        val lineTokenizer = DelimitedLineTokenizer()
        lineTokenizer.setDelimiter(",")
        lineTokenizer.setStrict(false)
        lineTokenizer.setNames("id", "name", "dept", "salary")
        val fieldSetMapper: BeanWrapperFieldSetMapper<User> = BeanWrapperFieldSetMapper<User>()
        fieldSetMapper.setTargetType(User::class.java)
        defaultLineMapper.setLineTokenizer(lineTokenizer)
        defaultLineMapper.setFieldSetMapper(fieldSetMapper)
        return defaultLineMapper
    }
}

DBWriter.kt

package com.nilmani.literalmission.batch

import com.nilmani.literalmission.model.User
import com.nilmani.literalmission.repository.UserRepository
import org.springframework.batch.item.ItemWriter
import org.springframework.beans.factory.annotation.Autowired
import org.springframework.stereotype.Component


@Component
class DBWriter :ItemWriter<User> {
    @Autowired
    private lateinit var userRepository: UserRepository

    @Autowired
    fun DBWriter(userRepository: UserRepository){
        this.userRepository = userRepository
    }
    @Throws(Exception::class)
    override fun write(users: MutableList<out User>) {
        println("Data Saved for Users: $users")
        userRepository.saveAll(users)
    }
}

项目处理器.kt

package com.nilmani.literalmission.batch

import com.nilmani.literalmission.model.User
import org.springframework.batch.item.ItemProcessor
import org.springframework.stereotype.Component
import java.util.*
import kotlin.collections.HashMap


@Component
class Processor : ItemProcessor<User,User> {
    private val DEPT_NAMES: MutableMap<String, String> = HashMap()
    fun Processor() {
        DEPT_NAMES["100"] = "Technology"
        DEPT_NAMES["102"] = "Operations"
        DEPT_NAMES["103"] = "Accounts"
        DEPT_NAMES["104"] = "Devloper"
    }

    @Throws(Exception::class)
    override fun process(user: User): User? {
        val deptCode:String=user.dept
        val dept=DEPT_NAMES[deptCode]
        if (dept != null) {
            user.dept = dept
        }
        user.time= Date()
        println(String.format("Converted from [%s] to [%s]", deptCode, dept))
        return user
    }
}

负载控制器.kt

package com.nilmani.literalmission.controller

import org.springframework.batch.core.*
import org.springframework.batch.core.launch.JobLauncher
import org.springframework.batch.core.repository.JobExecutionAlreadyRunningException
import org.springframework.batch.core.repository.JobInstanceAlreadyCompleteException
import org.springframework.batch.core.repository.JobRestartException
import org.springframework.beans.factory.annotation.Autowired
import org.springframework.web.bind.annotation.GetMapping
import org.springframework.web.bind.annotation.RequestMapping
import org.springframework.web.bind.annotation.RestController


@RestController
@RequestMapping("/load")
class LoadController {
    @Autowired
    private  lateinit var jobLauncher: JobLauncher
    @Autowired
    private lateinit var job:Job

    @GetMapping
    @Throws(
        JobParametersInvalidException::class,
        JobExecutionAlreadyRunningException::class,
        JobRestartException::class,
        JobInstanceAlreadyCompleteException::class
    )
    fun load(): BatchStatus {
        val maps: MutableMap<String, JobParameter> = HashMap()
        maps["time"] = JobParameter(System.currentTimeMillis())
        val parameters = JobParameters(maps)
        val jobExecution: JobExecution = jobLauncher.run(job, parameters)
        System.out.println("JobExecution: " + jobExecution.status)
        println("Batch is Running...")
        while (jobExecution.isRunning) {
            println("...")
        }
        return jobExecution.status
    }

}

用户.kt

package com.nilmani.literalmission.model

import java.util.*
import javax.persistence.Entity
import javax.persistence.Id

@Entity
data class User(
    @Id
    val id:Long=0,
    val name:String="",
    var dept:String="",
    val salary:String="",
    var time:Date
)

在此处输入图像描述

我正确地添加了每件事我是如何得到这类问题的

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/login
    driverClassName: com.mysql.cj.jdbc.Driver
    username: root
    password:
  jpa:
    database-platform: org.hibernate.dialect.MySQL5Dialect
    show-sql: true
    hibernate:
      ddl-auto: update

标签： mysqlcsvkotlinspring-batch

解决方案

根据错误Table 'login.batch_job_instance' doesn't exist，您需要在运行作业之前在数据库中创建 Spring Batch 表。

您可以通过执行 MySQL 的DDL 脚本手动执行此操作，或者如果您使用 Spring Boot，请设置 property spring.batch.initialize-schema=always。

推荐阅读