๋ณธ๋ฌธ ๋ฐ”๋กœ๊ฐ€๊ธฐ
Back-end ๋ฐ๋ธŒ์ฝ”์Šค/week 03 - 05 TIL (Spring)

[TIL] 221110 - SpringBoot Part2 : Embedded DB, Named Parameter Template, ํŠธ๋žœ์žญ์…˜

by young-ji 2022. 11. 11.

SpringBoot Part2 (4) 

 

1. Embedded DataBase

๊ธฐ์กด ํ…Œ์ŠคํŠธ : DB ๊ตฌ๋™์ด ๋˜์ง€์•Š์œผ๋ฉด ํ…Œ์ŠคํŠธ์— ์‹คํŒจํ•˜๋Š” ๊ตฌ์กฐ

→ ์™ธ๋ถ€ํ™˜๊ฒฝ์— ์˜ํ–ฅ์„ ๋ฐ›๋Š” ํ…Œ์ŠคํŠธ → ํ…Œ์ŠคํŠธ ์ž๋™ํ™”(jenkins๊ฐ™์€ CI ํˆด์„ ์ด์šฉํ•œ) ๋ถˆ๊ฐ€

 

Spring์—์„œ Embedded Database ์ œ๊ณต

Data Access

 

Data Access

The Data Access Object (DAO) support in Spring is aimed at making it easy to work with data access technologies (such as JDBC, Hibernate, or JPA) in a consistent way. This lets you switch between the aforementioned persistence technologies fairly easily, a

docs.spring.io

<dependency>
	<groupId>com.h2database</groupId>
	<artifactId>h2</artifactId>
	<scope>runtime</scope>
</dependency>
EmbeddedDatabase db = new EmbeddedDatabaseBuilder()
                    .generateUniqueName(true)
                    .setType(H2)
                    .setScriptEncoding("UTF-8")
                    .ignoreFailedDrops(true)
                    .addScript("schema.sql")
                    .build();

function์„ ๊ฑฐ์˜ ์ง€์›ํ•˜์ง€์•Š์•„ ์ตœ๋Œ€ํ•œ Ansi ํ‘œ์ค€์— ๋งž๊ฒŒ sql์„ ์ž‘์„ฑํ•ด์•ผํ•œ๋‹ค.

MySQL์ด๋‚˜ PostSQL๊ฐ€ํŠผ ๊ฒฝ์šฐ Embedded ์˜คํ”ˆ์†Œ์Šค๋ฅผ ์ œ๊ณตํ•˜์—ฌ ๊ทธ๊ฒƒ์„ ์ด์šฉํ•˜์—ฌ ์‹ค์ œ mySQL DB๊ฐ€ ๊ตฌ๋™๋˜๊ฒŒ ํ•ด์•ผํ•œ๋‹ค.

 

Embedded MySQL

https://github.com/wix/wix-embedded-mysql

<dependency>
	<groupId>com.wix</groupId>
	<artifactId>wix-embedded-mysql</artifactId>
	<version>4.6.1</version>
	<scope>test</scope>
</dependency>

-

import com.wix.mysql.EmbeddedMysql;
import static com.wix.mysql.EmbeddedMysql.anEmbeddedMysql;
import static com.wix.mysql.ScriptResolver.classPathScript;
import static com.wix.mysql.config.Charset.UTF8;
import static com.wix.mysql.config.MysqldConfig.aMysqldConfig;
import static com.wix.mysql.distribution.Version.v8_0_11;

@SpringJUnitConfig
@TestMethodOrder(MethodOrderer.OrderAnnotation.class)
@TestInstance(TestInstance.Lifecycle.PER_CLASS) // PER_CLASS : class ๋งˆ๋‹ค ์ธ์Šคํ„ด์Šค๊ฐ€ ํ•˜๋‚˜
class CustomerJdbcRepositoryTest {

    @Configuration
    @ComponentScan(
            basePackages = {"org.prgrms.kdt.customer"}
    )
    static class Config {

        @Bean
        public DataSource dataSource() {
            HikariDataSource dataSource = DataSourceBuilder.create()
                    .url("jdbc:mysql://localhost:2215/test-order_mgmt")
                    .username("test") 
                    .password("test1234!") // ํ•ด๋‹น ํ…Œ์ŠคํŠธ์—์„œ๋งŒ ์‚ฌ์šฉ๋  id,pw
                    .type(HikariDataSource.class)
                    .build();
            return dataSource;
        }

        @Bean
        public JdbcTemplate jdbcTemplate(DataSource dataSource) {
            return new JdbcTemplate(dataSource);
        }
    }

    @Autowired
    CustomerJdbcRepository customerJdbcRepository;
    @Autowired
    DataSource dataSource;
    EmbeddedMysql embeddedMysql;

    @BeforeAll
    void setup() {
        //customerJdbcRepository.deleteAll(); // embedded ์‚ฌ์šฉ์‹œ ์–ด์ฐจํ”ผ ์‹น๋‹ค ๋””๋น„๊ฐ€ ๋‚ด๋ ค๊ฐ”๋‹ค ์˜ฌ๋ผ์˜ค๊ธฐ ๋•Œ๋ฌธ์— ์‚ญ์ œํ•ด์ค„ ํ•„์š”์—†์Œ
        var mysqlConfig = aMysqldConfig(v8_0_11)
                .withCharset(UTF8)
                .withPort(2215)
                .withUser("test", "test1234!")
                .withTimeZone("Asia/Seoul")
                .build();
        embeddedMysql = anEmbeddedMysql(mysqlConfig)
                .addSchema("test-order_mgmt", classPathScript("schema.sql"))
                .start();
    }

    @AfterAll
    void cleanUp() {
        embeddedMysql.stop();
    }
}

๋ณ„๋„์˜ DB๋ฅผ ๊ตฌ๋™์‹œํ‚ค์ง€์•Š์•„๋„ ํ…Œ์ŠคํŠธ์— ํ†ต๊ณผํ•˜๋Š” ๊ฒƒ์„ ํ™•์ธ ํ•  ์ˆ˜์žˆ๋‹ค!

 

UUID ์˜ค๋ฅ˜

https://remarkablemark.org/blog/2020/05/21/mysql-uuid-bin/

 

 

2. NamedParameterJDBCTemplate

NamedParameterJDBCTemplate

: keyHolder ์ธ๋ฑ์Šค๊ธฐ๋ฐ˜์—์„œ ์ด๋ฆ„๊ธฐ๋ฐ˜์˜ ํŒŒ๋ผ๋ฏธํ„ฐ๋ฅผ ์„ค์ •ํ•  ์ˆ˜ ์žˆ๊ฒŒํ•ด์ฃผ๋Š” ํ…œํ”Œ๋ฆฟ

: JDBCTemplate์„ ํฌํ•จํ•˜๋ฉฐ ํŒŒ๋ผ๋ฏธํ„ฐ๋งŒ ๋ณ€๊ฒฝํ•ด์ฃผ๋Š” ์—ญํ• ์„ ํ•œ์ 

 

ํ…œํ”Œ๋ฆฟ ์ ์šฉ

@Repository
public class CustomerJdbcRepository implements CustomerRepository {

    private static final Logger logger = LoggerFactory.getLogger(CustomerJdbcRepository.class);

    private final NamedParameterJdbcTemplate jdbcTemplate;

    private static final RowMapper<Customer> customerRowMapper = new RowMapper<Customer>() {
        @Override
        public Customer mapRow(ResultSet rs, int rowNum) throws SQLException { //resultSet, index
            var customerName = rs.getString("name");
            var customerId = toUUID(rs.getBytes("customer_id"));
            var email = rs.getString("email");
            var createdAt = rs.getTimestamp("created_at").toLocalDateTime();
            var lastLoginAt = rs.getTimestamp("last_login_at") != null ?
                    rs.getTimestamp("last_login_at").toLocalDateTime() : null;

            return new Customer(customerId, customerName, email, lastLoginAt, createdAt);
        }
    };

    private Map<String, Object> toParamMap(Customer customer) {
        return new HashMap<>(){{
            put("customerId", customer.getCustomerId().toString().getBytes());
            put("name",customer.getName());
            put("email",customer.getEmail());
            put("lastLoginAt", customer.getLastLoginAt() != null ? Timestamp.valueOf(customer.getLastLoginAt()):null);
            put("createdAt", customer.getCreatedAt());
        }};
    }

    public CustomerJdbcRepository(NamedParameterJdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @Override
    public Customer insert(Customer customer) {
        var update = jdbcTemplate.update("INSERT INTO customers(customer_id,name,email, created_at)" +
                "  VALUES (UUID_TO_BIN(:customerId),:name,:email,:createdAt)",toParamMap(customer));
        if(update != 1) throw new RuntimeException("Nothing was inserted!");
        return customer;
    }

    @Override
    public Customer update(Customer customer) {

        var update = jdbcTemplate.update("UPDATE customers SET name= :name, email= :email, last_login_at= :lastLoginAt  WHERE customer_id = UUID_TO_BIN(:customerId)",toParamMap(customer));
        if(update != 1) throw new RuntimeException("Nothing was updated!");
        return customer;
    }

    @Override
    public int count() {
        return jdbcTemplate.queryForObject("select count(*) from customers",Collections.EMPTY_MAP,Integer.class);
    }

    @Override
    public List<Customer> findAll() {
        return jdbcTemplate.query("select * from customers", customerRowMapper); //sql,row mapper ์ „๋‹ฌ
    }

    @Override
    public Optional<Customer> findById(UUID customerId) {
        try {
            return Optional.ofNullable(jdbcTemplate.queryForObject("select * from customers WHERE customer_id = UUID_TO_BIN(:customerId)",
                    Collections.singletonMap("customerId",customerId.toString().getBytes()), customerRowMapper));
            // queryForObject -> ํ•˜๋‚˜๋งŒ ๊บผ๋‚ด๊ธฐ ๊ฐ’์ด ์—†์œผ๋ฉด ใ…‡ใ…–์™ธ๋ฐœ์ƒ
        }catch (EmptyResultDataAccessException e){
            return Optional.empty();
        }
    }

    @Override
    public void deleteAll() {
        //jdbcTemplate.update("DELETE FROM customers",Collections.emptyMap());
        //empty map์„ ์ „๋‹ฌํ•˜๊ธฐ ๊ท€์ฐฎ์œผ๋ฉด ๊ธฐ์กด ํ…œํ”Œ๋ฆฟ์„ ๊ฐ€์ ธ์™€ ์‚ฌ์šฉํ•ด๋„๋œ๋‹ค.
			 jdbcTemplate.getJdbcTemplate().update("DELETE FROM customers");
    }

    static UUID toUUID(byte[] bytes) {
        var byteBuffer = ByteBuffer.wrap(bytes);
        return new UUID(byteBuffer.getLong(), byteBuffer.getLong());
    }
}

DB๋ฅผ ์‚ฌ์šฉํ• ๋• ๋ฐœ์ƒํ•  ์ˆ˜ ์žˆ๋Š” ์˜ˆ์™ธ๊ฐ€ ๋‹ค์–‘ํ•˜๋‹ค. → JDBC๊ฐ€ SQLException์„ ๋ฐœ์ƒ์‹œํ‚จ๋‹ค.

Spring์—์„œ ์˜ˆ์™ธ๋“ค์„ ์ถ”์ƒํ™”์‹œํ‚จ DataAccessException ์ œ๊ณต

 

 

3. ํŠธ๋žœ์žญ์…˜

ํ•˜๋‚˜์˜ ์ž‘์—…์„ ์ˆ˜ํ–‰ํ•˜๊ธฐ ์œ„ํ•ด ํ•„์š”ํ•œ ์—ฐ์‚ฐ๋“ค์„ ๋ชจ์•„๋‘”๊ฒƒ. ๋…ผ๋ฆฌ์ ์ธ ์ž‘์—…์˜ ํ•œ ๋‹จ์œ„

์ถœ์ฒ˜ - https://jerryjerryjerry.tistory.com/48

 

 

: ๊ฐ๊ฐ์˜ ํŠธ๋žœ์žญ์…˜์€ ์›์ž์„ฑ, ์ผ๊ด€์„ฑ, ๋…๋ฆฝ์„ฑ, ์˜๊ตฌ์„ฑ์„ ๋ณด์žฅํ•œ๋‹ค.

: ์›์ž์„ฑ์ด commit๊ณผ rollback์— ์˜ํ•ด ๋ณด์žฅ๋œ๋‹ค.

: ํŠธ๋žœ์žญ์…˜ ์ƒํƒœ๋Š” ํŠธ๋žœ์žญ์…˜์—๋Š” ๋‚ด๊ฐ€ ์ ์€ ์ฟผ๋ฆฌ๋ฌธ๊ณผ ๋ฐ์ดํ„ฐ๋ฅผ ์ตœ์ข…์ ์œผ๋กœ ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค์— ๋ฐ˜์˜ํ•˜๋Š” COMMIT๊ณผ ์‹คํŒจํ–ˆ์„๋•Œ COMMINT ์‹œ์ ์œผ๋กœ ๋‹ค์‹œ ๋˜๋Œ์•„๊ฐ€๋Š” ROLLBACK์ด ์žˆ๋‹ค.

 

spring์—์„œ ํŠธ๋žœ์žญ์…˜ ์ฒ˜๋ฆฌ๋ฅผํ•˜๋ ค๋ฉด AOP๋ฅผ ์•Œ์•„์•ผํ•œ๋‹ค.

 

JDBC๋ฅผ ์ด์šฉํ•œ ์ง์ ‘ ํŠธ๋žœ์žญ์…˜ ์ฒ˜๋ฆฌ

 // AutoCommit์„ ๊บผ์„œ ํ€ด๋ฆฌ ํŠธ๋žœ์žญ์…˜์„ ํ•˜๋‚˜๋กœ ๋ฌถ์€ ๋’ค ์ž‘์—…์ด ์ •์ƒ ์ข…๋ฃŒ๋˜๋ฉด ๋‹ค์‹œ ํ‚จ๋‹ค.
connection.setAutoCommit(false);
	.. excute ๋™์ž‘
connection.setAutoCommit(true);

// AutoCommit์ด ์ผœ์ง€๊ธฐ์ „์— ์‹คํŒจํ•˜๋ฉด rollbackํ›„ connection ๋Š๊ธฐ
connection.rollback();
connection.close();

 

 

 

์ถœ์ฒ˜ - ํ•ด๋ฆฌ : SpringBoot Part1

๋Œ“๊ธ€