[SPRING] 데이터 추가, 변경, 삭제

 

 

 

의존 자동 주입 어노테이션 사용하고, spring jdbc를 이용하여 데이터에 접근하여 코드 구성

 

 

src/main/java/com.spring.woori.board/BoardService

package com.spring.woori.board;

import java.util.List;

public interface BoardService {
	void insert(BoardVO vo);
	void update(BoardVO vo);
	void delete(BoardVO vo);
	BoardVO get(BoardVO vo);
	List<BoardVO> getList(BoardVO vo);
}

 

src/main/java/com.spring.woori.board/BoardVO

package com.spring.woori.board;

import java.sql.Date;

public class BoardVO {
	private int seq;
	private String title;
	private String writer;
	private String content;
	private Date regdate;
	private int cnt;
	public int getSeq() {
		return seq;
	}
	public void setSeq(int seq) {
		this.seq = seq;
	}
	public String getTitle() {
		return title;
	}
	public void setTitle(String title) {
		this.title = title;
	}
	public String getWriter() {
		return writer;
	}
	public void setWriter(String writer) {
		this.writer = writer;
	}
	public String getContent() {
		return content;
	}
	public void setContent(String content) {
		this.content = content;
	}
	public Date getRegdate() {
		return regdate;
	}
	public void setRegdate(Date regdate) {
		this.regdate = regdate;
	}
	public int getCnt() {
		return cnt;
	}
	public void setCnt(int cnt) {
		this.cnt = cnt;
	}
	@Override
	public String toString() {
		return "BoardVO [seq=" + seq + ", title=" + title + ", writer=" + writer + ", content=" + content + ", regdate="
				+ regdate + ", cnt=" + cnt + "]";
	}
}

 

src/main/java/com.spring.woori.board.impl/BoardDAO

package com.spring.woori.board.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.springframework.stereotype.Repository;

import com.spring.woori.board.BoardVO;
import com.spring.woori.common.JDBCUtil;

@Repository("BoardDAO")
public class BoardDAO {
	private Connection conn=null;
	private PreparedStatement pstmt = null;
	private ResultSet rs = null;
	
	
	public void insert(BoardVO vo) {
		System.out.println("insert() 실행");
		String sql ="insert into board1(seq, title, writer, content, regdate) values((select nvl(max(seq),0)+1 from board1),?,?,?,sysdate)";
		try {
			conn = JDBCUtil.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, vo.getTitle());
			pstmt.setString(2, vo.getWriter());
			pstmt.setString(3, vo.getContent());
			pstmt.executeUpdate();
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.close(pstmt,conn);
		}
		
	}
	
	public void update(BoardVO vo) {
		System.out.println("update() 실행");
		String sql = "update board1 set title=?,writer=? where seq=?";
		
		try {
			conn = JDBCUtil.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, vo.getTitle());
			pstmt.setString(2, vo.getWriter());
			pstmt.setInt(3, vo.getSeq());
			pstmt.executeUpdate();
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.close(pstmt, conn);
		}
	}
	
	public void delete(BoardVO vo) {
		System.out.println("delete() 실행");
		String sql = "delete from board1 where seq=?";
		
		try {
			conn = JDBCUtil.getConnection();
			pstmt=conn.prepareStatement(sql);
			
			pstmt.setInt(1, vo.getSeq());
			pstmt.executeUpdate();
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.close(pstmt, conn);
		}
	}
	
	public BoardVO get(BoardVO vo) {
		System.out.println("get() 실행");
		String sql = "select * from board1 where seq=?";
		BoardVO vo3 = null;
		try {
			conn=JDBCUtil.getConnection();
			pstmt=conn.prepareStatement(sql);
			pstmt.setInt(1, vo.getSeq());
			rs=pstmt.executeQuery();
			
			if(rs.next()) {
				vo3 = new BoardVO();
				vo3.setCnt(rs.getInt("cnt"));
				vo3.setContent(rs.getString("content"));
				vo3.setRegdate(rs.getDate("regdate"));
				vo3.setSeq(rs.getInt("seq"));
				vo3.setTitle(rs.getString("title"));
				vo3.setWriter(rs.getString("writer"));
			}
			}catch(Exception e) {
				e.printStackTrace();
			}finally {
				JDBCUtil.close(rs,pstmt, conn);
			}
			
			
		
		
		return vo3;
		
	}
	
	public List<BoardVO> getList(BoardVO vo){
		System.out.println("getList() 실행");
		List<BoardVO> list = new ArrayList<BoardVO>();
		String sql = "select * from board1";
		
		
		try {
			conn=JDBCUtil.getConnection();
			pstmt = conn.prepareStatement(sql);
			rs=pstmt.executeQuery();
			
			while(rs.next()) {
				BoardVO bvo = new BoardVO();
				bvo.setCnt(rs.getInt("cnt"));
				bvo.setContent(rs.getString("content"));
				bvo.setRegdate(rs.getDate("regdate"));
				bvo.setSeq(rs.getInt("seq"));
				bvo.setTitle(rs.getString("title"));
				bvo.setWriter(rs.getString("writer"));
				list.add(bvo);
			}
			
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.close(rs,pstmt, conn);
		}
		
		return list;
		
	}
}

 

src/main/java/com.spring.woori.board.impl/BoardServiceimpl

package com.spring.woori.board.impl;

import java.util.List;

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

import com.spring.woori.board.BoardService;
import com.spring.woori.board.BoardVO;

@Service("boardService")
public class BoardServiceimpl implements BoardService{
	@Autowired
	private BoardDAO boardDAO;

	@Override
	public void insert(BoardVO vo) {
		boardDAO.insert(vo);
	}

	@Override
	public void update(BoardVO vo) {
		boardDAO.update(vo);
		
	}

	@Override
	public void delete(BoardVO vo) {
		boardDAO.delete(vo);
		
	}

	@Override
	public BoardVO get(BoardVO vo) {
		return boardDAO.get(vo);
	}

	@Override
	public List<BoardVO> getList(BoardVO vo) {
		return boardDAO.getList(vo);
	}

}

 

src/main/java/com.spring.woori.common/JDBCUtil

package com.spring.woori.common;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class JDBCUtil {
	public static Connection getConnection() {
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			return DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","scott","TIGER");
		}catch(Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public static void close(PreparedStatement pstmt, Connection conn) {
		
			try {
				if(pstmt!=null) pstmt.close();
				if(conn!=null) conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	}
	
	public static void close(ResultSet rs, PreparedStatement pstmt, Connection conn) {
		try {
			if(rs!=null) rs.close();
			if(pstmt!=null) pstmt.close();
			if(conn!=null) conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		}
	}

 

 

src/main/resources/applicationContext.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:context="http://www.springframework.org/schema/context"
	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.3.xsd">
<context:component-scan base-package="com.spring.woori"></context:component-scan>

</beans>

 

src/test/java/com.spring.woori.board/BoardServiceClient

package com.spring.woori.board;

import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

import org.springframework.context.support.AbstractApplicationContext;
import org.springframework.context.support.GenericXmlApplicationContext;

public class BoardServiceClient {

	public static void main(String[] args) {
		
		AbstractApplicationContext factory = new GenericXmlApplicationContext("applicationContext.xml");
		BoardService bs = (BoardService) factory.getBean("boardService");
		BoardVO vo = new BoardVO();
		Scanner sc = new Scanner(System.in);
		Boolean test = true;
		List<BoardVO> list = new ArrayList<>();
		
		while(test) {
			System.out.println("1.글추가 2.전체글조회 3.특정글조회 4.글정보수정 5.글삭제 6.프로그램종료");
			System.out.println("메뉴선택 >>");
			int num = sc.nextInt();
			
			switch(num) {
				case 1 :
					System.out.println("등록할 글 정보를 입력하시오. \n글제목 : ");
					vo.setTitle(sc.next());
					System.out.println("작성자 : ");
					vo.setWriter(sc.next());
					System.out.println("글내용 : ");
					vo.setContent(sc.next());
					
					bs.insert(vo);
					
					continue;
				case 2:
					
					list = bs.getList(vo);
					for(int i = 0 ; i<list.size();i++) {
						System.out.println(list.get(i).toString());
					}
					
					
					continue;
				case 3:
					System.out.println("특정번호 입력");
					vo.setSeq(sc.nextInt());
					
					System.out.println(bs.get(vo).toString());
					
					continue;
				case 4:
					System.out.println("업데이트 원하는 번호 입력");
					vo.setSeq(sc.nextInt());
					
					System.out.println("수정 타이틀 입력");
					vo.setTitle(sc.next());
					
					System.out.println("수정 내용 입력");
					vo.setContent(sc.next());
					
					bs.update(vo);
					
					list = bs.getList(vo);
					for(int i = 0 ; i<list.size();i++) {
						System.out.println(list.get(i).toString());
					}
					
					continue;
				case 5:
					System.out.println("지우고싶은 번호 입력");
					vo.setSeq(sc.nextInt());
					
					bs.delete(vo);
					
					continue;
				case 6:
					System.out.println("프로그램 종료");
					test = false;
					factory.close();
					break;
					
			}
		}

	}

}

 

 

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.spring</groupId>
	<artifactId>woori</artifactId>
	<name>23AUG21_test_practice</name>
	<packaging>war</packaging>
	<version>1.0.0-BUILD-SNAPSHOT</version>
	<properties>
		<java-version>1.8</java-version>
		<org.springframework-version>5.0.7.RELEASE</org.springframework-version>
		<org.aspectj-version>1.6.10</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.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.12</version>
			<scope>test</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>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>2.5.1</version>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                    <compilerArgument>-Xlint:all</compilerArgument>
                    <showWarnings>true</showWarnings>
                    <showDeprecation>true</showDeprecation>
                </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>