본문 바로가기
[study]이론정리/Spring Boot

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

by yoon9i 2024. 7. 2.

3> SpringJDBC + mysql 연동

가. 의존성 설정

  <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jdbc</artifactId>
  </dependency>
  <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
  <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.33</version>
  </dependency>

나. DB 연동위한 4가지 정보 설정

  #application properties
  spring.datasource.driver-class-name=cohttp://m.mysql.cj.jdbc.Driver
  spring.datasource.url=jdbc:mysql://localhost:3306/testdb
  spring.datasource.username=root
  spring.datasource.password=1234

다. schema.sql 과 data.sql 활성화
https://docs.spring.io/spring-boot/docs/2.7.18/reference/htmlsingle/#howto.data-initialization.using-basic-sql-scripts

  #application.properties
  spring.sql.init.mode=always|never 

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.TodoMySQLService;
import com.exam.service.TodoMySQLServiceImpl;

@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);
		
		TodoMySQLServiceImpl service = 
				ctx.getBean("todoService", TodoMySQLServiceImpl.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 TodoMySQLRepository {
	
//	@Autowired 또는 생성자로 주입
//	@Autowired
	JdbcTemplate template;

	// 생성자를 통해서 주입
	public TodoMySQLRepository(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 TodoMySQLService {
	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.TodoMySQLRepository;

@Service("todoService")
public class TodoMySQLServiceImpl implements TodoMySQLService {

//	@Autowired 또는 생성자로 주입
	@Autowired
	TodoMySQLRepository 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

# 이전 jdbc.properties 에서 사용했던 4가지 정보 설정
# jdbc.driver=com.mysql.cj.jdbc.Driver
# jdbc.url=jdbc:mysql://localhost:3306/testdb
# jdbc.userid=root
# jdbc.passwd=1234

spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/testdb
spring.datasource.username=root
spring.datasource.password=1234

# schema.sql 과 data.sql 사용하겠다고 설정 필요.
spring.sql.init.mode=always
# 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/mysql/mysql-connector-java -->
  <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.33</version>
  </dependency>

<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter</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>

'[study]이론정리 > Spring Boot' 카테고리의 다른 글

Spring 기반의 DB 연동 - springframework 및 SpringBoot 트랜잭션 처리  (0) 2024.07.02
Spring 기반의 DB 연동 - mybatis + mysql: 연동1 - @Repository 클래스 + TodoMapper.xml 연동  (1) 2024.07.02
Spring 기반의 DB 연동 - SpringJDBC + h2 연동  (0) 2024.07.02
AOP(Aspect Oriented Programming: 관점 지향 프로그래밍)  (1) 2024.07.02
프로파일(Profile)  (0) 2024.07.02

관련글

티스토리툴바