🔍

상세 기능 구현 (JPA를 사용한 MySQL 연동)

생성일

2025/01/24 05:52

태그

spring-meta-database 생성

•

schema-mysql.sql (인텔리제이에서 검색하면 나옴)

-- Autogenerated: do not edit this file

CREATE TABLE BATCH_JOB_INSTANCE  (
	JOB_INSTANCE_ID BIGINT  NOT NULL PRIMARY KEY ,
	VERSION BIGINT ,
	JOB_NAME VARCHAR(100) NOT NULL,
	JOB_KEY VARCHAR(32) NOT NULL,
	constraint JOB_INST_UN unique (JOB_NAME, JOB_KEY)
) ENGINE=InnoDB;

CREATE TABLE BATCH_JOB_EXECUTION  (
	JOB_EXECUTION_ID BIGINT  NOT NULL PRIMARY KEY ,
	VERSION BIGINT  ,
	JOB_INSTANCE_ID BIGINT NOT NULL,
	CREATE_TIME DATETIME(6) NOT NULL,
	START_TIME DATETIME(6) DEFAULT NULL ,
	END_TIME DATETIME(6) DEFAULT NULL ,
	STATUS VARCHAR(10) ,
	EXIT_CODE VARCHAR(2500) ,
	EXIT_MESSAGE VARCHAR(2500) ,
	LAST_UPDATED DATETIME(6),
	constraint JOB_INST_EXEC_FK foreign key (JOB_INSTANCE_ID)
	references BATCH_JOB_INSTANCE(JOB_INSTANCE_ID)
) ENGINE=InnoDB;

CREATE TABLE BATCH_JOB_EXECUTION_PARAMS  (
	JOB_EXECUTION_ID BIGINT NOT NULL ,
	PARAMETER_NAME VARCHAR(100) NOT NULL ,
	PARAMETER_TYPE VARCHAR(100) NOT NULL ,
	PARAMETER_VALUE VARCHAR(2500) ,
	IDENTIFYING CHAR(1) NOT NULL ,
	constraint JOB_EXEC_PARAMS_FK foreign key (JOB_EXECUTION_ID)
	references BATCH_JOB_EXECUTION(JOB_EXECUTION_ID)
) ENGINE=InnoDB;

CREATE TABLE BATCH_STEP_EXECUTION  (
	STEP_EXECUTION_ID BIGINT  NOT NULL PRIMARY KEY ,
	VERSION BIGINT NOT NULL,
	STEP_NAME VARCHAR(100) NOT NULL,
	JOB_EXECUTION_ID BIGINT NOT NULL,
	CREATE_TIME DATETIME(6) NOT NULL,
	START_TIME DATETIME(6) DEFAULT NULL ,
	END_TIME DATETIME(6) DEFAULT NULL ,
	STATUS VARCHAR(10) ,
	COMMIT_COUNT BIGINT ,
	READ_COUNT BIGINT ,
	FILTER_COUNT BIGINT ,
	WRITE_COUNT BIGINT ,
	READ_SKIP_COUNT BIGINT ,
	WRITE_SKIP_COUNT BIGINT ,
	PROCESS_SKIP_COUNT BIGINT ,
	ROLLBACK_COUNT BIGINT ,
	EXIT_CODE VARCHAR(2500) ,
	EXIT_MESSAGE VARCHAR(2500) ,
	LAST_UPDATED DATETIME(6),
	constraint JOB_EXEC_STEP_FK foreign key (JOB_EXECUTION_ID)
	references BATCH_JOB_EXECUTION(JOB_EXECUTION_ID)
) ENGINE=InnoDB;

CREATE TABLE BATCH_STEP_EXECUTION_CONTEXT  (
	STEP_EXECUTION_ID BIGINT NOT NULL PRIMARY KEY,
	SHORT_CONTEXT VARCHAR(2500) NOT NULL,
	SERIALIZED_CONTEXT TEXT ,
	constraint STEP_EXEC_CTX_FK foreign key (STEP_EXECUTION_ID)
	references BATCH_STEP_EXECUTION(STEP_EXECUTION_ID)
) ENGINE=InnoDB;

CREATE TABLE BATCH_JOB_EXECUTION_CONTEXT  (
	JOB_EXECUTION_ID BIGINT NOT NULL PRIMARY KEY,
	SHORT_CONTEXT VARCHAR(2500) NOT NULL,
	SERIALIZED_CONTEXT TEXT ,
	constraint JOB_EXEC_CTX_FK foreign key (JOB_EXECUTION_ID)
	references BATCH_JOB_EXECUTION(JOB_EXECUTION_ID)
) ENGINE=InnoDB;

CREATE TABLE BATCH_STEP_EXECUTION_SEQ (
	ID BIGINT NOT NULL,
	UNIQUE_KEY CHAR(1) NOT NULL,
	constraint UNIQUE_KEY_UN unique (UNIQUE_KEY)
) ENGINE=InnoDB;

INSERT INTO BATCH_STEP_EXECUTION_SEQ (ID, UNIQUE_KEY) select * from (select 0 as ID, '0' as UNIQUE_KEY) as tmp where not exists(select * from BATCH_STEP_EXECUTION_SEQ);

CREATE TABLE BATCH_JOB_EXECUTION_SEQ (
	ID BIGINT NOT NULL,
	UNIQUE_KEY CHAR(1) NOT NULL,
	constraint UNIQUE_KEY_UN unique (UNIQUE_KEY)
) ENGINE=InnoDB;

INSERT INTO BATCH_JOB_EXECUTION_SEQ (ID, UNIQUE_KEY) select * from (select 0 as ID, '0' as UNIQUE_KEY) as tmp where not exists(select * from BATCH_JOB_EXECUTION_SEQ);

CREATE TABLE BATCH_JOB_SEQ (
	ID BIGINT NOT NULL,
	UNIQUE_KEY CHAR(1) NOT NULL,
	constraint UNIQUE_KEY_UN unique (UNIQUE_KEY)
) ENGINE=InnoDB;

INSERT INTO BATCH_JOB_SEQ (ID, UNIQUE_KEY) select * from (select 0 as ID, '0' as UNIQUE_KEY) as tmp where not exists(select * from BATCH_JOB_SEQ);
Java
복사

TEST 진행

•

@Param 관련 에러 발생

◦

https://velog.io/@heewonim/JPQL-Error-For-queries-with-named-parameters-you-need-to-use-provide-names-for-method-parameters

◦

각각의 변수에 @Param(”id”) Long id 양식으로 명시를 해줘야 함

•

JpaConfig

package com.fastcampus.pass.config;

import org.springframework.context.annotation.Configuration;
import org.springframework.data.jpa.repository.config.EnableJpaAuditing;

@EnableJpaAuditing      // JPA auditing을 활성화 한다. entity의 생성일시와 수정일시를 자동화하는 용도로 사용 (시간을 자동으로 입력)
@Configuration
public class JpaConfig {

}
Java
복사

•

PackageRepository

package com.fastcampus.pass.repository.ptpackage;

import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.time.LocalDateTime;
import java.util.List;

@Repository
public interface PackageRepository extends JpaRepository<PackageEntity, Integer> {

    List<PackageEntity> findByCreatedAtAfter(LocalDateTime dateTime, Pageable packageSeq);

    @Transactional
    @Modifying
    @Query(value = "UPDATE PackageEntity p " +
            "      SET p.count = :count, " +
            "          p.period = :period" +
            "    WHERE p.packageSeq = :packageSeq")
    int updateCountAndPeriod(@Param("packageSeq") Integer packageSeq, @Param("count") Integer count, @Param("period") Integer period);
}
Java
복사

•

BaseEntity

package com.fastcampus.pass.repository;

import jakarta.persistence.Column;
import jakarta.persistence.EntityListeners;
import jakarta.persistence.MappedSuperclass;
import org.springframework.data.annotation.CreatedDate;
import org.springframework.data.annotation.LastModifiedDate;
import org.springframework.data.jpa.domain.support.AuditingEntityListener;

import java.time.LocalDateTime;

/*
    공통 매핑 정보를 가진 Entity
 */
@MappedSuperclass                   // 상속받은 entity에서 아래 필드들을 컬럼으로 사용할 수 있음
@EntityListeners(AuditingEntityListener.class)      // Auditing 정보를 캡처하는 Listener이다.
public abstract class BaseEntity {

    @CreatedDate        // 생성일시 생성
    @Column(updatable = false, nullable = false)        // 업데이트 하지 않도록, null이 되지 않도록 명시
    private LocalDateTime createdAt;

    @LastModifiedDate   // 마지막 수정 일시를 생성
    private LocalDateTime modifiedAt;

}
Java
복사

•

application-test.yml

spring:
  jpa:
    show-sql: true
    properties:
      hibernate:
        format_sql: true
logging:
  level:
    org.hibernate.type.descriptor.sql: trace
YAML
복사

•

application.yml

spring:
  datasource:
    url: jdbc:mysql://127.0.0.1:3307/pass_local?zeroDateTimeBehavior=convertToNull&characterEncoding=UTF-8&serverTimezone=Asia/Seoul
    username: pass_local_user
    password: passlocal123
    driver-class-name: com.mysql.cj.jdbc.Driver
    hikari:
      maximum-pool-size: 20     # pool에 보관 가능한 최대 connection 수

  batch:
    jdbc:
      # Batch에 사용되는 SQL 초기화 스크립트를 실행. 임베디드인 경우에는 기본적으로 실행하는데, always 설정을 하면 임베디드 타입처럼 항상 활성화시킬 수 있다.
      # 초기 수행 이후에는 off 해도 된다.
      initialize-schema: always
YAML
복사

•

PackageBatchApplicationTest

package com.fastcampus.pass;

import com.fastcampus.pass.repository.ptpackage.PackageEntity;
import com.fastcampus.pass.repository.ptpackage.PackageRepository;
import lombok.extern.slf4j.Slf4j;
import org.junit.jupiter.api.DisplayName;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;
import org.springframework.test.context.ActiveProfiles;

import java.time.LocalDateTime;
import java.util.List;

import static org.junit.jupiter.api.Assertions.*;

@SpringBootTest
@Slf4j
@ActiveProfiles("test")
class PassBatchApplicationTests {

	@Autowired
	private PackageRepository packageRepository;

	@DisplayName("test_save")
	@Test
	public void test_save() {
	    // given (사전 준비)
		PackageEntity packageEntity = new PackageEntity();
		packageEntity.setPackageName("바디 챌린지 PT 12주");
		packageEntity.setPeriod(84);

	    // when (테스트 진행할 행위)
		packageRepository.save(packageEntity);

	    // then (행위에 대한 결과 검증)
		assertNotNull(packageEntity.getPackageSeq());

	}

	@DisplayName("test_findByCreatedAtAfter")
	@Test
	public void test_findByCreatedAtAfter() {
	    // given (사전 준비)
		LocalDateTime dateTime = LocalDateTime.now().minusMinutes(1);

		PackageEntity packageEntity0 = new PackageEntity();
		packageEntity0.setPackageName("학생 전용 3개월");
		packageEntity0.setPeriod(90);
		packageRepository.save(packageEntity0);

		PackageEntity packageEntity1 = new PackageEntity();
		packageEntity1.setPackageName("학생 전용 6개월");
		packageEntity1.setPeriod(180);
		packageRepository.save(packageEntity1);

	    // when (테스트 진행할 행위)
		final List<PackageEntity> packageEntities = packageRepository.findByCreatedAtAfter(dateTime, PageRequest.of(
				0, 1, Sort.by("packageSeq").descending()));

	    // then (행위에 대한 결과 검증)
		assertEquals(1, packageEntities.size());
		assertEquals(packageEntity1.getPackageSeq(), packageEntities.get(0).getPackageSeq());
	}

	@DisplayName("test_updateCountAndPeriod")
	@Test
	public void test_updateCountAndPeriod() {
	    // given (사전 준비)
		PackageEntity packageEntity = new PackageEntity();
		packageEntity.setPackageName("바디프로필 이벤트 4개월");
		packageEntity.setPeriod(90);
		packageRepository.save(packageEntity);

	    // when (테스트 진행할 행위)
		int updatedCount = packageRepository.updateCountAndPeriod(packageEntity.getPackageSeq(), 30, 120);
		final PackageEntity updatedPackageEntity = packageRepository.findById(packageEntity.getPackageSeq()).get();

	    // then (행위에 대한 결과 검증)
		assertEquals(1, updatedCount);
		assertEquals(30, updatedPackageEntity.getCount());
		assertEquals(120, updatedPackageEntity.getPeriod());
	}

	@DisplayName("test_delete")
	@Test
	public void testMethod() {
	    // given (사전 준비)
		PackageEntity packageEntity = new PackageEntity();
		packageEntity.setPackageName("제거할 이용권");
		packageEntity.setCount(1);
		PackageEntity newPackageEntity = packageRepository.save(packageEntity);

	    // when (테스트 진행할 행위)
		packageRepository.deleteById(newPackageEntity.getPackageSeq());

	    // then (행위에 대한 결과 검증)
		assertTrue(packageRepository.findById(newPackageEntity.getPackageSeq()).isEmpty());

	}

}
Java
복사