Spring 기반의 DB 연동 - SpringJDBC + h2 연동

13. Spring 기반의 DB 연동

1> JDBC vs SpringJDBC
- sql 이용
- JDBC 보다 자바코드가 훨신 적음. (예외처리 불필요. 이유는 모두 RuntimeException 계열임)

    try {

    } catch (SQLException e) { // Compilechecked
      // Compilechecked -> ComplieUnchecked
      throw new RuntimeException(""); // ComplieUnchecked
    }

- JdbcTemplate 빈을 이용해서 DB 처리.

2> SpringJDBC + h2 연동
가. 의존성 설정

    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-data-jdbc</artifactId>
    </dependency>
    <!-- https://mvnrepository.com/artifact/com.h2database/h2 -->
    <dependency>
        <groupId>com.h2database</groupId>
        <artifactId>h2</artifactId>
        <scope>runtime</scope>
    </dependency>

 

나. h2 관리자 콘솔 설정
- 웹페이지로 제공
- 의존성 설정

    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-web</artifactId>
    </dependency>

- h2 관리자 콘솔 활성화

    # application.properties
    # h2 관리자 콘솔 활성화
    spring.h2.console.enabled=true

- Application.java 실행

    다음과 같이 console 에 h2 관리자 정보가 출력됨.

      H2 console available at '/h2-console'. 
      Database available at 'jdbc:h2:mem:797f0fa4-06d6-40d2-ad29-8f0a92182454'

    웹 브라우저에 요청

      http://localhost:8080/h2-console

    요청 URL 은 실행시마다 매번 랜덤하게 바뀜.
    따라서 URL 고정하자.(testdb)

      # application.properties
      spring.datasource.url=jdbc:h2:mem:testdb

 

다. 매실행마다 자동으로 파일인식해서 테이블을 생성 + 데이터를 저장하는 작업처리
https://docs.spring.io/spring-boot/docs/2.7.18/reference/htmlsingle/#howto.data-initialization.using-basic-sql-scripts

  #src/main/resources
  schema.sql : create table 문
  data.sql : insert 문

  #schema.sql
  create table todo (
  id int primary key,
    name varchar(255) not null,
    author varchar(255) not null
  );

  #data.sql
  insert into todo(id,name,author) values (1, 'boot공부', '홍길동');
  insert into todo(id,name,author) values (2, 'react공부', '홍길동');
  insert into todo(id,name,author) values (3, 'CI/CD공부', '홍길동');

라. boot 와 연동
  - 전체적인 아키텍쳐
                                   TodoH2Service
                                   @Service                       @Repository
      Application.java  <--------> TodoH2ServiceImple <---------> TodoH2Repository <----------> h2
                                                                  TodoDTO(Todo)

  - sql 문 + JdbcTemplate 빈이용해서 연동
    DML(insert/delete/update): update() 메서드
    단일레코드조회: queryForObject(sql, RowMapper인터페이스, 파라미터값)
    다중레코드조회: query(sql, RowMapper인터페이스 [,파라미터값])

 

package com.exam;

import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.ApplicationContext;

import com.exam.dto.TodoDTO;
import com.exam.service.TodoH2Service;
import com.exam.service.TodoH2ServiceImpl;

@SpringBootApplication
public class Application implements CommandLineRunner{

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

    Logger logger = LoggerFactory.getLogger(getClass());

    @Autowired
	ApplicationContext ctx;

	@Override
	public void run(String... args) throws Exception {
		logger.info("logger:ApplicationContext:{}",ctx);
		
		TodoH2ServiceImpl service = 
				ctx.getBean("todoService", TodoH2ServiceImpl.class);
		
		// 저장
		service.save(new TodoDTO(4, "final프로젝트", "홍길동"));
		
		// 수정
		service.update(new TodoDTO(4, "마지막프로젝트", "홍길동2"));
		
		// 삭제
		service.delete(1);
		
		// id가 4번이 Todo 조회(단일조회)
		TodoDTO todo = service.findById(4);
		System.out.println(todo);
		
		System.out.println("######################");
		// 전체 조회
		List<TodoDTO> list = service.findAll();
		System.out.println(list);
	}

}

package com.exam.dto;

public class TodoDTO {
	int id;
	String name;
	String author;
	public TodoDTO() {
		super();
		// TODO Auto-generated constructor stub
	}
	public TodoDTO(int id, String name, String author) {
		super();
		this.id = id;
		this.name = name;
		this.author = author;
	}
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getAuthor() {
		return author;
	}
	public void setAuthor(String author) {
		this.author = author;
	}
	@Override
	public String toString() {
		return "TodoDTO [id=" + id + ", name=" + name + ", author=" + author + "]";
	}
	
	
}

package com.exam.repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.exam.dto.TodoDTO;

@Repository("todoRepository")
public class TodoH2Repository {
	
//	@Autowired 또는 생성자로 주입
//	@Autowired
	JdbcTemplate template;

	// 생성자를 통해서 주입
	public TodoH2Repository(JdbcTemplate template) {
		super();
		this.template = template;
	}
	
	// Todo 저장
	public void save(TodoDTO todo) {
		String sql = "insert into todo (id,name,author) values (?,?,?)";
		template.update(sql, todo.getId(), todo.getName(), todo.getAuthor());
	}
	
	// Todo 삭제
	public void delete(int id) {
		String sql = "delete from todo where id = ?";
		template.update(sql, id);
	}
	
	// Todo 수정
	public void update(TodoDTO todo) {
		String sql = "update todo set name=?, author=? where id=?";
		template.update(sql, todo.getName(), todo.getAuthor(), todo.getId());
	}
	
	// Todo 단일조회
	public TodoDTO findById(int id) {
		String sql = "select id,name,author from todo where id = ?";
		TodoDTO todo = template.queryForObject(sql, new RowMapper<TodoDTO>() {

			@Override
			public TodoDTO mapRow(ResultSet rs, int rowNum) throws SQLException {
				// rs 에 값을 얻어서 TodoDTO 에 저장후 반환.
				TodoDTO dto = new TodoDTO();
				dto.setId(rs.getInt("id"));
				dto.setName(rs.getString("name"));
				dto.setAuthor(rs.getString("author"));
				
				return dto;
			}
			
		}, id);
		
		return todo;
	}
	
	// Todo 전체조회
	public List<TodoDTO> findAll() {
		String sql = "select id,name,author from todo ";
		List<TodoDTO> list = template.query(sql, new RowMapper<TodoDTO>() {

			@Override
			public TodoDTO mapRow(ResultSet rs, int rowNum) throws SQLException {
				// rs 에 값을 얻어서 TodoDTO 에 저장후 반환.
				TodoDTO dto = new TodoDTO();
				dto.setId(rs.getInt("id"));
				dto.setName(rs.getString("name"));
				dto.setAuthor(rs.getString("author"));
				
				return dto;
			}
			
		});
		
		return list;
	}
	
}

package com.exam.service;

import java.util.List;

import org.springframework.stereotype.Service;

import com.exam.dto.TodoDTO;


public interface TodoH2Service {
	public void save(TodoDTO todo); // 저장
	public void delete(int id); // 삭제
	public void update(TodoDTO todo); // 수정
	public TodoDTO findById(int id); // 단일 조회
	public List<TodoDTO> findAll(); // 전체 조회
}

package com.exam.service;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.exam.dto.TodoDTO;
import com.exam.repository.TodoH2Repository;

@Service("todoService")
public class TodoH2ServiceImpl implements TodoH2Service {

//	@Autowired 또는 생성자로 주입
	@Autowired
	TodoH2Repository todoH2Repository;
	
	// 저장
	@Override
	public void save(TodoDTO todo) {
		todoH2Repository.save(todo);
	}

	// 삭제
	@Override
	public void delete(int id) {
		todoH2Repository.delete(id);
	}

	// 수정
	@Override
	public void update(TodoDTO todo) {
		todoH2Repository.update(todo);
	}

	// 단일조회
	@Override
	public TodoDTO findById(int id) {
		return todoH2Repository.findById(id);
	}

	// 다중조회
	@Override
	public List<TodoDTO> findAll() {
		return todoH2Repository.findAll();
	}

}

# application.properties
logging.level.org.springframework=info

spring.h2.console.enabled=true

# application.properties
spring.datasource.url=jdbc:h2:mem:testdb

# data.sql
  insert into todo(id,name,author) values (1, 'boot공부', '홍길동');
  insert into todo(id,name,author) values (2, 'react공부', '홍길동');
  insert into todo(id,name,author) values (3, 'CI/CD공부', '홍길동');

# schema.sql
  create table todo (
	  id int primary key,
    name varchar(255) not null,
    author varchar(255) not null
  );

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 
	http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.7.18</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.exam</groupId>
	<artifactId>demo</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>demo</name>
	<description>Demo project for Spring Boot</description>
	<properties>
		<java.version>11</java.version>
	</properties>
	<dependencies>
		
<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-data-jdbc</artifactId>
</dependency>
<!-- https://mvnrepository.com/artifact/com.h2database/h2 -->
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <scope>runtime</scope>
</dependency>
<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-web</artifactId>
</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>

</project>