Spring JDBC

•

JDBC & Spring JDBC 의존성 

•

데이터 소스 설정 & Spring JDBC 빈 등록

•

인코딩 필터

•

테이블 생성

•

비즈니스 로직

•

트랜잭션

JDBC & Spring JDBC 의존성 설정

•

pom.xml

<!-- mysql 드라이버 -->
<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>8.1.0</version>
</dependency>


<!-- Spring JDBC -->
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>${org.springframework-version}</version>
</dependency>
XML
복사

•

pom.xml - lombok

<!-- lombok -->
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.30</version>
    <scope>provided</scope>
</dependency>
XML
복사

•

pom.xml (전체)

<?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 https://maven.apache.org/maven-v4_0_0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>com.joeun</groupId>
	<artifactId>test</artifactId>
	<name>Spring-JDBC</name>
	<packaging>war</packaging>
	<version>1.0.0-BUILD-SNAPSHOT</version>
	<properties>
		<java-version>1.8</java-version>
		<org.springframework-version>5.3.10</org.springframework-version>
		<org.aspectj-version>1.9.7</org.aspectj-version>
		<!-- 아래 버전으로 했을 때, @AfterReturning 에서 returning 이 계속 null 인 이슈 -->
<!-- 		<org.springframework-version>5.0.7.RELEASE</org.springframework-version> -->
<!-- 		<org.aspectj-version>1.5.4</org.aspectj-version> -->
		<org.slf4j-version>1.6.6</org.slf4j-version>
	</properties>
	<dependencies>
		<!-- Spring -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-context</artifactId>
			<version>${org.springframework-version}</version>
			<exclusions>
				<!-- Exclude Commons Logging in favor of SLF4j -->
				<exclusion>
					<groupId>commons-logging</groupId>
					<artifactId>commons-logging</artifactId>
				 </exclusion>
			</exclusions>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-webmvc</artifactId>
			<version>${org.springframework-version}</version>
		</dependency>
				
		<!-- AspectJ -->
		<dependency>
			<groupId>org.aspectj</groupId>
			<artifactId>aspectjrt</artifactId>
			<version>${org.aspectj-version}</version>
		</dependency>	
		
		<!-- Logging -->
		<dependency>
			<groupId>org.slf4j</groupId>
			<artifactId>slf4j-api</artifactId>
			<version>${org.slf4j-version}</version>
		</dependency>
		<dependency>
			<groupId>org.slf4j</groupId>
			<artifactId>jcl-over-slf4j</artifactId>
			<version>${org.slf4j-version}</version>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.slf4j</groupId>
			<artifactId>slf4j-log4j12</artifactId>
			<version>${org.slf4j-version}</version>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>log4j</groupId>
			<artifactId>log4j</artifactId>
			<version>1.2.15</version>
			<exclusions>
				<exclusion>
					<groupId>javax.mail</groupId>
					<artifactId>mail</artifactId>
				</exclusion>
				<exclusion>
					<groupId>javax.jms</groupId>
					<artifactId>jms</artifactId>
				</exclusion>
				<exclusion>
					<groupId>com.sun.jdmk</groupId>
					<artifactId>jmxtools</artifactId>
				</exclusion>
				<exclusion>
					<groupId>com.sun.jmx</groupId>
					<artifactId>jmxri</artifactId>
				</exclusion>
			</exclusions>
			<scope>runtime</scope>
		</dependency>

		<!-- @Inject -->
		<dependency>
			<groupId>javax.inject</groupId>
			<artifactId>javax.inject</artifactId>
			<version>1</version>
		</dependency>
				
		<!-- Servlet -->
<!-- 		<dependency> -->
<!-- 			<groupId>javax.servlet</groupId> -->
<!-- 			<artifactId>servlet-api</artifactId> -->
<!-- 			<version>2.5</version> -->
<!-- 			<scope>provided</scope> -->
<!-- 		</dependency> -->
		<dependency>
		    <groupId>javax.servlet</groupId>
		    <artifactId>javax.servlet-api</artifactId>
		    <version>3.1.0</version>
		    <scope>provided</scope>
		</dependency>
		
		<dependency>
			<groupId>javax.servlet.jsp</groupId>
			<artifactId>jsp-api</artifactId>
			<version>2.1</version>
			<scope>provided</scope>
		</dependency>
		<dependency>
			<groupId>javax.servlet</groupId>
			<artifactId>jstl</artifactId>
			<version>1.2</version>
		</dependency>
	
		<!-- Test -->
		<dependency>
			<groupId>junit</groupId>
			<artifactId>junit</artifactId>
			<version>4.7</version>
			<scope>test</scope>
		</dependency>        
		
		<!-- mysql 드라이버 -->
		<dependency>
		    <groupId>com.mysql</groupId>
		    <artifactId>mysql-connector-j</artifactId>
		    <version>8.1.0</version>
		</dependency>
		
		
		<!-- Spring JDBC -->
		<dependency>
		    <groupId>org.springframework</groupId>
		    <artifactId>spring-jdbc</artifactId>
		    <version>${org.springframework-version}</version>
		</dependency>
		
		<!-- lombok -->
		<dependency>
		    <groupId>org.projectlombok</groupId>
		    <artifactId>lombok</artifactId>
		    <version>1.18.30</version>
		    <scope>provided</scope>
		</dependency>
		
		
	</dependencies>
    <build>
        <plugins>
            <plugin>
                <artifactId>maven-eclipse-plugin</artifactId>
                <version>2.9</version>
                <configuration>
                    <additionalProjectnatures>
                        <projectnature>org.springframework.ide.eclipse.core.springnature</projectnature>
                    </additionalProjectnatures>
                    <additionalBuildcommands>
                        <buildcommand>org.springframework.ide.eclipse.core.springbuilder</buildcommand>
                    </additionalBuildcommands>
                    <downloadSources>true</downloadSources>
                    <downloadJavadocs>true</downloadJavadocs>
                </configuration>
            </plugin>
            <!-- 자바 버전 		: 1.6 → 1.8 -->
            <!-- 플러그인 버전		: 2.5.1 → 3.8.1 -->
            <!-- parameter 인자 추가 -->
            <plugin>
			    <groupId>org.apache.maven.plugins</groupId>
			    <artifactId>maven-compiler-plugin</artifactId>
			    <version>3.8.1</version>
			    <configuration>
			        <source>1.8</source>
			        <target>1.8</target>
			        <compilerArgs>
			            <arg>-parameters</arg>
			        </compilerArgs>
			    </configuration>
			</plugin>
            <plugin>
                <groupId>org.codehaus.mojo</groupId>
                <artifactId>exec-maven-plugin</artifactId>
                <version>1.2.1</version>
                <configuration>
                    <mainClass>org.test.int1.Main</mainClass>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>
XML
복사

데이터 소스 설정, Spring JDBC 빈 등록

•

root-context.xml

<!-- 데이터 소스 등록 -->
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
	<property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
	<property name="url" value="jdbc:mysql://127.0.0.1:3306/joeun"></property>
	<property name="username" value="joeun"></property>
	<property name="password" value="123456"></property>
</bean>

<!-- JDBC Template 빈 등록 -->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
	<property name="dataSource" ref="dataSource" />
</bean>
XML
복사

인코딩 필터

•

web.xml

<!-- 인코딩 필터(한글깨짐 방지) --> 
<filter> 
    <filter-name>encodingFilter</filter-name> 
    <filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class>
    <init-param> 
        <param-name>encoding</param-name>
        <param-value>UTF-8</param-value>
    </init-param>
</filter>
<filter-mapping>
    <filter-name>encodingFilter</filter-name>
    <url-pattern>/*</url-pattern>
</filter-mapping>
SQL
복사

테이블 생성

•

board.sql

CREATE TABLE `board` (
  `no` int NOT NULL AUTO_INCREMENT,
  `title` varchar(100) NOT NULL,
  `writer` varchar(100) NOT NULL,
  `content` text,
  `reg_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `upd_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `views` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`no`)
) COMMENT='게시판';
SQL
복사

트랜잭션

트랜잭션은 데이터베이스에서 한 번에 수행되어야하는 일련의 작업을 의미합니다. 이러한 작업은 모두 완료되거나 아무것도 수행되지 않아야 합니다. 트랜잭션은 데이터의 일관성과 무결성을 보장하기 위해 사용됩니다. 예를 들어, 은행에서 송금이 발생할 때 송금과 동시에 입금이 모두 성공적으로 이루어져야 하며, 하나의 작업이 실패하면 모든 작업은 롤백되어야 합니다.

트랜잭션 특징 - A.C.I.D

트랜잭션은 다음의 특징을 가지고 있습니다:

•

원자성 (Atomicity): 트랜잭션 내의 모든 작업은 일부라도 실패하면 전체 트랜잭션이 롤백되어야 합니다.

•

일관성 (Consistency): 트랜잭션이 수행되기 전과 후에 데이터베이스는 일관된 상태여야 합니다.

•

격리성 (Isolation): 동시에 실행되는 여러 트랜잭션은 서로 영향을 주지 않고 독립적으로 실행되어야 합니다.

•

지속성 (Durability): 트랜잭션이 성공적으로 완료되면 그 결과는 영구적으로 저장되어야 하며, 시스템 장애 발생 시에도 유지되어야 합니다.

•

트랜잭션 의존성 설정

◦

네임 스페이스 및 스키마 설정

◦

트랜잭션 관리자 빈 등록

◦

트랜잭션 어노테이션 기능 활성화

•

서비스 구현 클래스에 어노테이션 적용

◦

@Transactional

트랜잭션 의존성 설정

네임 스페이스

•

root-context.xml

xmlns:tx="http://www.springframework.org/schema/tx"
XML
복사

스키마 설정

•

root-context.xml

xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
		http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.1.xsd
		http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.1.xsd">
XML
복사

트랜잭션 관리자 빈 등록

•

root-context.xml

<!-- 트랜잭션 관리자 빈 등록 -->
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
	<property name="dataSource" ref="dataSource"></property>
</bean>
XML
복사

트랜잭션 어노테이션 기능 활성화

•

root-context.xml

<!-- 트랜잭션 어노테이션 기능 활성화 -->
<tx:annotation-driven />
XML
복사

서비스 구현 클래스에 어노테이션 적용

•

BoardServiceImpl.java

@Transactional
@Override
public void join(User user) throws Exception {
	userDAO.join(user);
	
	UserAuth userAuth = new UserAuth();

	userAuth .setUserNo(user.getUserNo());
	userAuth .setAuth("ROLE_USER");

	userDAO.insertAuth(userAuth );
}
Java
복사

비즈니스 로직

•

controller

◦

BoardController.java

•

dto

◦

Board.java

•

dao

◦

BoardDAO.java

•

service

◦

BoardService.java

◦

BoardServiceImpl.java

controller

•

BoardController.java

BoardController.java

package com.joeun.test.controller;

import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;

import com.joeun.test.dto.Board;
import com.joeun.test.service.BoardService;

@Controller
@RequestMapping("/board")
public class BoardController {
	
	private static final Logger logger = LoggerFactory.getLogger(BoardController.class);
	
	@Autowired
	private BoardService boardService;
	
	// 게시글 목록
	// @RequestMapping(value = "/list", method = RequestMethod.GET)
	@GetMapping("/list")		// Spring 4.3 버전 부터 사용
	public String list(Model model) throws Exception {
		List<Board> boardList = boardService.list();
		model.addAttribute(boardList);
		return "board/list";
	}
	
	// 게시글 조회
	@GetMapping("/read")		
	public String read(Model model, int no) throws Exception {
		Board board= boardService.select(no);
		model.addAttribute(board);
		return "board/read";
	}
	
	// 게시글 등록
	@GetMapping("/insert")		
	public String insert() throws Exception {
		return "board/insert";
	}
	
	// 게시글 등록 처리
	@PostMapping("/insert")		
	public String insert(Board board) throws Exception {
		int result = boardService.insert(board);
		
		return "redirect:/board/list";
	}
	
	// 게시글 수정
	@GetMapping("/update")		
	public String update(Model model, int no) throws Exception {
		Board board= boardService.select(no);
		model.addAttribute(board);
		return "board/update";
	}
	
	// 게시글 수정 처리
	@PostMapping("/update")		
	public String update(Board board) throws Exception {
		int result = boardService.update(board);
		
		return "redirect:/board/list";
	}
	
	// 게시글 삭제 처리
	@PostMapping("/delete")		
	public String delete(int no) throws Exception {
		int result = boardService.delete(no);
		
		return "redirect:/board/list";
	}

}
Java
복사

dto

•

Board.java

Board.java

package com.joeun.test.dto;

import java.util.Date;

import lombok.Data;

@Data
public class Board {

	private int no;
	private String title;
	private String writer;
	private String content;
	private Date regDate;
	private Date updDate;
	private int views;
	
	public Board() {
		
	}

	public Board(String title, String writer, String content) {
		this.title = title;
		this.writer = writer;
		this.content = content;
	}
	
}
Java
복사

dao

•

BoardDAO.java

BoardDAO.java

package com.joeun.test.dao;

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.joeun.test.dto.Board;

@Repository			// 데이터 액세스 객체로 빈 등록
public class BoardDAO {
	
	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	/**
	 * 게시글 목록 조회
	 * @return
	 */
	public List<Board> list() {
	
		String sql = " SELECT * FROM board ";
		List<Board> boardList = jdbcTemplate.query(sql, new RowMapper<Board>() {
			@Override
			public Board mapRow(ResultSet rs, int rowNum) throws SQLException {
				Board board = new Board();
				board.setno( rs.getInt("no") );
				board.setTitle( rs.getString("title") );
				board.setWriter( rs.getString("writer") );
				board.setContent( rs.getString("content") );
				board.setRegDate( rs.getTimestamp("reg_date") );
				board.setUpdDate( rs.getTimestamp("upd_date") );
				board.setViews( rs.getInt("views") );
				return board;
			}
		});
		
		return boardList;
	}
	
	
	
	public Board select(int no) {
		String sql = " SELECT * FROM board WHERE no = ? ";
		
//		List<Board> boardList = jdbcTemplate.query(sql, new RowMapper<Board>() {
//			@Override
//			public Board mapRow(ResultSet rs, int rowNum) throws SQLException {
//				Board board = new Board();
//				board.setno( rs.getInt("no") );
//				board.setTitle( rs.getString("title") );
//				board.setWriter( rs.getString("writer") );
//				board.setContent( rs.getString("content") );
//				board.setRegDate( rs.getTimestamp("reg_date") );
//				board.setUpdDate( rs.getTimestamp("upd_date") );
//				board.setViews( rs.getInt("views") );
//				return board;
//			}
//		}, no);
//		
//		Board board = null; 
//		if( boardList.isEmpty() ) 
//			board = null;
//		else 
//			board = boardList.get(0);
		
		// 2번째 방법
		Board board = jdbcTemplate.queryForObject(sql, new RowMapper<Board>() {
			@Override
			public Board mapRow(ResultSet rs, int rowNum) throws SQLException {
				Board board = new Board();
				board.setno( rs.getInt("no") );
				board.setTitle( rs.getString("title") );
				board.setWriter( rs.getString("writer") );
				board.setContent( rs.getString("content") );
				board.setRegDate( rs.getTimestamp("reg_date") );
				board.setUpdDate( rs.getTimestamp("upd_date") );
				board.setViews( rs.getInt("views") );
				return board;
			}
		}, no);
		
		// 3번째 방법
//		Object[] argList = new Object[]{no};
//		Board board = jdbcTemplate.queryForObject(sql, argList, new RowMapper<Board>() {
//			@Override
//			public Board mapRow(ResultSet rs, int rowNum) throws SQLException {
//				Board board = new Board();
//				board.setno( rs.getInt("no") );
//				board.setTitle( rs.getString("title") );
//				board.setWriter( rs.getString("writer") );
//				board.setContent( rs.getString("content") );
//				board.setRegDate( rs.getTimestamp("reg_date") );
//				board.setUpdDate( rs.getTimestamp("upd_date") );
//				board.setViews( rs.getInt("views") );
//				return board;
//			}
//		});
		
		return board;
	}
	
	public int insert(Board board) {
		String sql = " INSERT INTO board(title, writer, content) "
				   + " VALUES ( ?, ?, ? ) ";
		
		Object[] args = new Object[] {board.getTitle(), board.getWriter(), board.getContent()};
		
		int result = jdbcTemplate.update(sql, args);
		// int result = jdbcTemplate.update(sql, board.getTitle(), board.getWriter(), board.getContent());
		return result;
	}
	
	public int update(Board board) {
		String sql = " UPDATE board "
				   + "    SET title = ? "
				   + "       ,writer = ? "
				   + "       ,content = ? "
				   + "		 ,upd_date = now() "
				   + " WHERE no = ? ";
		
		Object[] args = new Object[] {board.getTitle(), board.getWriter(), board.getContent(), board.getNo()};
		int result = jdbcTemplate.update(sql, args);
		return result;
	}
	
	public int delete(int no) {
		String sql = " DELETE FROM board "
				   + " WHERE no = ? ";
		
		Object[] args = new Object[] {no};
		int result = jdbcTemplate.update(sql, args);
		return result;
	}
	
	

}
Java
복사

service

•

BoardService.java

•

BoardServiceImpl.java

BoardService.java

package com.aloha.spring.service;


import java.util.List;

import com.aloha.spring.dto.Board;

public interface BoardService {

	// 게시글 목록
	public List<Board> list() throws Exception;
	
	// 게시글 조회
	public Board select(int no);
	
	// 게시글 등록
	public Integer insert(Board board) throws Exception;
	
	// 게시글 수정
	public Integer update(Board board) throws Exception;
	
	// 게시글 삭제
	public Integer delete(int no) throws Exception;
}
Java
복사

BoardServiceImpl.java

package com.aloha.spring.service;

import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.aloha.spring.dao.BoardDAO;
import com.aloha.spring.dto.Board;

@Service			// 비즈니스 로직을 처리하는 서비스 클래스로 빈 등록
public class BoardServiceImpl implements BoardService {
	
	private static final Logger logger = LoggerFactory.getLogger(BoardServiceImpl.class);
	
	@Autowired
	private BoardDAO boardDAO;

	@Override
	public List<Board> list() throws Exception {
		List<Board> boardList = boardDAO.list();
		return boardList;
	}

	@Override
	public Board select(int no)  {
		Board board = boardDAO.select(no);
		return board;
	}

	@Override
	public Integer insert(Board board) throws Exception {
		int result = boardDAO.insert(board);
		return result;
	}

	@Override
	public Integer update(Board board) throws Exception {
		int result = boardDAO.update(board);
		return result;
	}

	@Override
	public Integer delete(int no) throws Exception {
		int result = boardDAO.delete(no);
		return result;
	}

}
Java
복사