복붙노트

[SPRING] 원인 : java.sql.SQLIntegrityConstraintViolationException : 키 'PRIMARY'에 대해 중복 항목 '10'- 스프링 배치

SPRING

원인 : java.sql.SQLIntegrityConstraintViolationException : 키 'PRIMARY'에 대해 중복 항목 '10'- 스프링 배치

나는 Spring Boot + Batch 예제를 개발 중이고 contineously 아래 오류를 받고있다.

오류:

2018-12-08 17:31:40.333 ERROR 19576 --- [           main] o.s.batch.core.step.AbstractStep         : Encountered an error executing step step1 in job job

org.springframework.dao.DuplicateKeyException: PreparedStatementCallback; SQL [INSERT INTO CUSTOMER VALUES (?, ?, ?, ?)]; Duplicate entry '10' for key 'PRIMARY'; nested exception is java.sql.BatchUpdateException: Duplicate entry '10' for key 'PRIMARY'
    at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:242) ~[spring-jdbc-5.1.3.RELEASE.jar:5.1.3.RELEASE]
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) ~[spring-jdbc-5.1.3.RELEASE.jar:5.1.3.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1444) ~[spring-jdbc-5.1.3.RELEASE.jar:5.1.3.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:632) ~[spring-jdbc-5.1.3.RELEASE.jar:5.1.3.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:646) ~[spring-jdbc-5.1.3.RELEASE.jar:5.1.3.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:936) ~[spring-jdbc-5.1.3.RELEASE.jar:5.1.3.RELEASE]
    at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.batchUpdate(NamedParameterJdbcTemplate.java:365) ~[spring-jdbc-5.1.3.RELEASE.jar:5.1.3.RELEASE]
    at org.springframework.batch.item.database.JdbcBatchItemWriter.write(JdbcBatchItemWriter.java:182) ~[spring-batch-infrastructure-4.1.0.RELEASE.jar:4.1.0.RELEASE]
    at org.springframework.batch.core.step.item.SimpleChunkProcessor.writeItems(SimpleChunkProcessor.java:188) ~[spring-batch-core-4.1.0.RELEASE.jar:4.1.0.RELEASE]
    at org.springframework.batch.core.step.item.SimpleChunkProcessor.doWrite(SimpleChunkProcessor.java:154) ~[spring-batch-core-4.1.0.RELEASE.jar:4.1.0.RELEASE]
    at org.springframework.batch.core.step.item.SimpleChunkProcessor.write(SimpleChunkProcessor.java:287) ~[spring-batch-core-4.1.0.RELEASE.jar:4.1.0.RELEASE]
    at org.springframework.batch.core.step.item.SimpleChunkProcessor.process(SimpleChunkProcessor.java:212) ~[spring-batch-core-4.1.0.RELEASE.jar:4.1.0.RELEASE]
    at org.springframework.batch.core.step.item.ChunkOrientedTasklet.execute(ChunkOrientedTasklet.java:75) ~[spring-batch-core-4.1.0.RELEASE.jar:4.1.0.RELEASE]
    at org.springframework.batch.core.step.tasklet.TaskletStep$ChunkTransactionCallback.doInTransaction(TaskletStep.java:407) ~[spring-batch-core-4.1.0.RELEASE.jar:4.1.0.RELEASE]
    at org.springframework.batch.core.step.tasklet.TaskletStep$ChunkTransactionCallback.doInTransaction(TaskletStep.java:331) ~[spring-batch-core-4.1.0.RELEASE.jar:4.1.0.RELEASE]
    at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140) ~[spring-tx-5.1.3.RELEASE.jar:5.1.3.RELEASE]
    at org.springframework.batch.core.step.tasklet.TaskletStep$2.doInChunkContext(TaskletStep.java:273) ~[spring-batch-core-4.1.0.RELEASE.jar:4.1.0.RELEASE]
    at org.springframework.batch.core.scope.context.StepContextRepeatCallback.doInIteration(StepContextRepeatCallback.java:82) ~[spring-batch-core-4.1.0.RELEASE.jar:4.1.0.RELEASE]
    at org.springframework.batch.repeat.support.RepeatTemplate.getNextResult(RepeatTemplate.java:375) ~[spring-batch-infrastructure-4.1.0.RELEASE.jar:4.1.0.RELEASE]
    at org.springframework.batch.repeat.support.RepeatTemplate.executeInternal(RepeatTemplate.java:215) ~[spring-batch-infrastructure-4.1.0.RELEASE.jar:4.1.0.RELEASE]
    at org.springframework.batch.repeat.support.RepeatTemplate.iterate(RepeatTemplate.java:145) ~[spring-batch-infrastructure-4.1.0.RELEASE.jar:4.1.0.RELEASE]
    at org.springframework.batch.core.step.tasklet.TaskletStep.doExecute(TaskletStep.java:258) ~[spring-batch-core-4.1.0.RELEASE.jar:4.1.0.RELEASE]
    at org.springframework.batch.core.step.AbstractStep.execute(AbstractStep.java:203) ~[spring-batch-core-4.1.0.RELEASE.jar:4.1.0.RELEASE]
    at org.springframework.batch.core.job.SimpleStepHandler.handleStep(SimpleStepHandler.java:148) [spring-batch-core-4.1.0.RELEASE.jar:4.1.0.RELEASE]
    at org.springframework.batch.core.job.AbstractJob.handleStep(AbstractJob.java:399) [spring-batch-core-4.1.0.RELEASE.jar:4.1.0.RELEASE]
    at org.springframework.batch.core.job.SimpleJob.doExecute(SimpleJob.java:135) [spring-batch-core-4.1.0.RELEASE.jar:4.1.0.RELEASE]
    at org.springframework.batch.core.job.AbstractJob.execute(AbstractJob.java:313) [spring-batch-core-4.1.0.RELEASE.jar:4.1.0.RELEASE]
    at org.springframework.batch.core.launch.support.SimpleJobLauncher$1.run(SimpleJobLauncher.java:144) [spring-batch-core-4.1.0.RELEASE.jar:4.1.0.RELEASE]
    at org.springframework.core.task.SyncTaskExecutor.execute(SyncTaskExecutor.java:50) [spring-core-5.1.3.RELEASE.jar:5.1.3.RELEASE]
    at org.springframework.batch.core.launch.support.SimpleJobLauncher.run(SimpleJobLauncher.java:137) [spring-batch-core-4.1.0.RELEASE.jar:4.1.0.RELEASE]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_162]
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) ~[na:1.8.0_162]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) ~[na:1.8.0_162]
    at java.lang.reflect.Method.invoke(Unknown Source) ~[na:1.8.0_162]
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:343) [spring-aop-5.1.3.RELEASE.jar:5.1.3.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198) [spring-aop-5.1.3.RELEASE.jar:5.1.3.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) [spring-aop-5.1.3.RELEASE.jar:5.1.3.RELEASE]
    at org.springframework.batch.core.configuration.annotation.SimpleBatchConfiguration$PassthruAdvice.invoke(SimpleBatchConfiguration.java:127) [spring-batch-core-4.1.0.RELEASE.jar:4.1.0.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) [spring-aop-5.1.3.RELEASE.jar:5.1.3.RELEASE]
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212) [spring-aop-5.1.3.RELEASE.jar:5.1.3.RELEASE]
    at com.sun.proxy.$Proxy40.run(Unknown Source) [na:na]
    at com.example.DatabaseOutputApplication.run(DatabaseOutputApplication.java:35) [classes/:na]
    at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:813) [spring-boot-2.1.1.RELEASE.jar:2.1.1.RELEASE]
    at org.springframework.boot.SpringApplication.callRunners(SpringApplication.java:797) [spring-boot-2.1.1.RELEASE.jar:2.1.1.RELEASE]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:324) [spring-boot-2.1.1.RELEASE.jar:2.1.1.RELEASE]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1260) [spring-boot-2.1.1.RELEASE.jar:2.1.1.RELEASE]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1248) [spring-boot-2.1.1.RELEASE.jar:2.1.1.RELEASE]
    at com.example.DatabaseOutputApplication.main(DatabaseOutputApplication.java:26) [classes/:na]
Caused by: java.sql.BatchUpdateException: Duplicate entry '10' for key 'PRIMARY'
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.8.0_162]
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source) ~[na:1.8.0_162]
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source) ~[na:1.8.0_162]
    at java.lang.reflect.Constructor.newInstance(Unknown Source) ~[na:1.8.0_162]
    at com.mysql.cj.util.Util.handleNewInstance(Util.java:191) ~[mysql-connector-java-8.0.13.jar:8.0.13]
    at com.mysql.cj.util.Util.getInstance(Util.java:166) ~[mysql-connector-java-8.0.13.jar:8.0.13]
    at com.mysql.cj.util.Util.getInstance(Util.java:173) ~[mysql-connector-java-8.0.13.jar:8.0.13]
    at com.mysql.cj.jdbc.exceptions.SQLError.createBatchUpdateException(SQLError.java:224) ~[mysql-connector-java-8.0.13.jar:8.0.13]
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeBatchSerially(ClientPreparedStatement.java:874) ~[mysql-connector-java-8.0.13.jar:8.0.13]
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeBatchInternal(ClientPreparedStatement.java:456) ~[mysql-connector-java-8.0.13.jar:8.0.13]
    at com.mysql.cj.jdbc.StatementImpl.executeBatch(StatementImpl.java:814) ~[mysql-connector-java-8.0.13.jar:8.0.13]
    at com.zaxxer.hikari.pool.ProxyStatement.executeBatch(ProxyStatement.java:128) ~[HikariCP-3.2.0.jar:na]
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeBatch(HikariProxyPreparedStatement.java) ~[HikariCP-3.2.0.jar:na]
    at org.springframework.jdbc.core.JdbcTemplate.lambda$batchUpdate$2(JdbcTemplate.java:950) ~[spring-jdbc-5.1.3.RELEASE.jar:5.1.3.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:617) ~[spring-jdbc-5.1.3.RELEASE.jar:5.1.3.RELEASE]
    ... 44 common frames omitted
Caused by: java.sql.SQLIntegrityConstraintViolationException: Duplicate entry '10' for key 'PRIMARY'
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:117) ~[mysql-connector-java-8.0.13.jar:8.0.13]
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[mysql-connector-java-8.0.13.jar:8.0.13]
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-java-8.0.13.jar:8.0.13]
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:974) ~[mysql-connector-java-8.0.13.jar:8.0.13]
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1113) ~[mysql-connector-java-8.0.13.jar:8.0.13]
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeBatchSerially(ClientPreparedStatement.java:853) ~[mysql-connector-java-8.0.13.jar:8.0.13]
    ... 50 common frames omitted

고객 .csv

id,firstName,lastName,birthdate
1,John,Doe,10-10-1952 10:10:10
2,Amy,Eugene,05-07-1985 17:10:00
3,Laverne,Mann,11-12-1988 10:10:10
4,Janice,Preston,19-02-1960 10:10:10
5,Pauline,Rios,29-08-1977 10:10:10
6,Perry,Burnside,10-03-1981 10:10:10
7,Todd,Kinsey,14-12-1998 10:10:10
8,Jacqueline,Hyde,20-03-1983 10:10:10
9,Rico,Hale,10-10-2000 10:10:10
10,Samuel,Lamm,11-11-1999 10:10:10
11,Robert,Coster,10-10-1972 10:10:10
12,Tamara,Soler,02-01-1978 10:10:10
13,Justin,Kramer,19-11-1951 10:10:10
14,Andrea,Law,14-10-1959 10:10:10
15,Laura,Porter,12-12-2010 10:10:10
16,Michael,Cantu,11-04-1999 10:10:10
17,Andrew,Thomas,04-05-1967 10:10:10
18,Jose,Hannah,16-09-1950 10:10:10
19,Valerie,Hilbert,13-06-1966 10:10:10
20,Patrick,Durham,12-10-1978 10:10:10

Customer.java

@AllArgsConstructor
@NoArgsConstructor
@Builder
@Data
public class Customer {
    private Long id;
    private String firstName;
    private String lastName;
    private Date birthdate;
}

database.properties

spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/test
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.schema=org/springframework/batch/core/schema-mysql.sql 
spring.batch.initialize-schema=ALWAYS

스키마 - mysql.sql

CREATE TABLE `test`.`customer` (
  `id` MEDIUMINT(8) unsigned NOT NULL,
  `firstName` VARCHAR(255) default NULL,
  `lastName` VARCHAR(255) default NULL,
  `birthdate` DATE NULL,
  PRIMARY KEY (`id`)
  ) AUTO_INCREMENT=1;

DatabaseOutputApplication.java

@SpringBootApplication
@EnableBatchProcessing
public class DatabaseOutputApplication implements CommandLineRunner{
    @Autowired
    private JobLauncher jobLauncher;

    @Autowired
    private Job job;

    public static void main(String[] args) {
        SpringApplication.run(DatabaseOutputApplication.class, args);
    }

    @Override
    public void run(String... args) throws Exception {
        JobParameters jobParameters = new JobParametersBuilder()
                .addDate("date", new Date())
                .addLong("time",System.currentTimeMillis()).toJobParameters();

        JobExecution execution = jobLauncher.run(job, jobParameters);
        System.out.println("STATUS :: "+execution.getStatus());
    }
}

JobConfig.java

@Configuration
public class JobConfig {
    @Autowired
    private StepBuilderFactory stepBuilderFactory;

    @Autowired
    private JobBuilderFactory jobBuilderFactory;

    @Autowired
    private DataSource dataSource;

    @Bean
    public FlatFileItemReader<Customer> customerItemReader(){
        FlatFileItemReader<Customer> reader = new FlatFileItemReader<>();
        reader.setLinesToSkip(1);
        reader.setResource(new ClassPathResource("/data/customer.csv"));

        DelimitedLineTokenizer tokenizer = new DelimitedLineTokenizer();
        tokenizer.setNames(new String[] {"id", "firstName", "lastName", "birthdate"});

        DefaultLineMapper<Customer> customerLineMapper = new DefaultLineMapper<>();
        customerLineMapper.setLineTokenizer(tokenizer);
        customerLineMapper.setFieldSetMapper(new CustomerFieldSetMapper());
        customerLineMapper.afterPropertiesSet();

        reader.setLineMapper(customerLineMapper);

        return reader;
    }

    @Bean
    public JdbcBatchItemWriter<Customer> customerItemWriter(){
        JdbcBatchItemWriter<Customer> writer = new JdbcBatchItemWriter<>();
        writer.setDataSource(this.dataSource);
        writer.setSql("INSERT INTO CUSTOMER VALUES (:id, :firstName, :lastName, :birthdate)");
        writer.setItemSqlParameterSourceProvider(new BeanPropertyItemSqlParameterSourceProvider<>());
        writer.afterPropertiesSet();

        return writer;
    }

    @Bean
    public Step step1() {
        return stepBuilderFactory.get("step1")
                .<Customer, Customer> chunk(10)
                .reader(customerItemReader())
                .writer(customerItemWriter())
                .build();
    }

    @Bean
    public Job job() {
        return jobBuilderFactory.get("job")
                .start(step1())
                .build();
    }   
}

해결법

  1. ==============================

    1.AUTO_INCREMENT = 1을 제거하십시오; 파일에서 기본 키를 읽으므로 작성 스크립트에서.

    AUTO_INCREMENT = 1을 제거하십시오; 파일에서 기본 키를 읽으므로 작성 스크립트에서.

    2. from https://stackoverflow.com/questions/53682367/caused-by-java-sql-sqlintegrityconstraintviolationexception-duplicate-entry-1 by cc-by-sa and MIT license

'SPRING' 카테고리의 다른 글

[SPRING] 여분의 로그인 파라미터를 가진 봄 기억 나  (0) 2019.05.05
[SPRING] 이미지를 BLOB로 저장할 때 JSP로 텍스트를 표시하는 방법은 무엇입니까?  (0) 2019.05.05
[SPRING] 커스텀 userdetailsservice를위한 bean 정의하기  (0) 2019.05.05
[SPRING] 스프링 보안 : 신뢰할 수있는 서브넷을 제외한 모든 요청을 인증합니다.  (0) 2019.05.05
[SPRING] java.lang.NoClassDefFoundError : com / google / gson / gson  (0) 2019.05.05
,

티스토리툴바