SpringBoot/day46 / 23.11.09(목) / (핀테크) Spring 및 Ai 기반 핀테크 프로젝트 구축

허니몬·2023년 11월 9일
Springbootlegacy
0

(핀테크) Spring 및 Ai 기반 핀테크 프로젝트 구축 / Spring

목록 보기
3/14
post-thumbnail

P04_jdbc

pom_수정.txt

의존성 추가

https://mvnrepository.com/

<dependencies> 
</dependencies>

안에 넣기

Maven - Update

확인

DB TABLE 생성

-- books table
CREATE TABLE books(
code VARCHAR2(20) PRIMARY KEY, -- 도서 코드
name VARCHAR2(100),            -- 도서명
price NUMBER,                  -- 도서가격
maker VARCHAR2(50)             -- 출판사
);

com.book.bean

BooksVO.java

package com.book.bean;

public class BooksVO {
	private String code; 
	private String name; 
	private int price; 
	private String maker;
	
	public BooksVO() {
	}
	
	public BooksVO(String code, String name, int price, String maker) {
		super();
		this.code = code;
		this.name = name;
		this.price = price;
		this.maker = maker;
	}

	public String getCode() {
		return code;
	}

	public void setCode(String code) {
		this.code = code;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public int getPrice() {
		return price;
	}

	public void setPrice(int price) {
		this.price = price;
	}

	public String getMaker() {
		return maker;
	}

	public void setMaker(String maker) {
		this.maker = maker;
	}

	@Override
	public String toString() {
		return "[ 도서번호 : " + code + " - 도서명 : " + name + " - 가격 : " + price + " - 출판사 : " + maker + " ]";
	} 
	
	
}

com.book.dao

BooksDAO.java

package com.book.dao;

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

import com.book.bean.BooksVO;

public class BooksDAO {
	
	// JDBC 관련 변수
	private Connection con;
	private PreparedStatement pstmt;
	private ResultSet rs;
	
	// SQL 명령어
	private final String BOOKS_INSERT = "INSERT INTO books VALUES(?,?,?,?)";
	private final String BOOKS_SELECTALL = "SELECT * FROM books";
	private final String BOOKS_UPDATE = "UPDATE books SET NAME=?,PRICE=?,MAKER=? WHERE CODE=?";
	private final String BOOKS_DELETE = "DELETE FROM books WHERE CODE=?";
	private final String BOOKS_SELECT = "SELECT * FROM books WHERE CODE=?";
	
	// 도서 정보 등록
	public int insertBooks(BooksVO vo) {
		int su = 0;
		try {
			con = JDBCUtil.getConnection();
			pstmt = con.prepareStatement(BOOKS_INSERT);
			pstmt.setString(1, vo.getCode());
			pstmt.setString(2, vo.getName());
			pstmt.setInt(3, vo.getPrice());
			pstmt.setString(4, vo.getMaker());
			su = pstmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(rs, pstmt, con);
		}
		return su;
	}
	
	// 도서 목록 조회
	public List<BooksVO> getBookslist(BooksVO vo){
		List<BooksVO> list=null; 
		try {
			list=new ArrayList<BooksVO>();
			con = JDBCUtil.getConnection();
			pstmt = con.prepareStatement(BOOKS_SELECTALL);
			rs = pstmt.executeQuery();
			
			while (rs.next()) {
				vo = new BooksVO();
				vo.setCode(rs.getString("code"));
				vo.setName(rs.getString("name"));
				vo.setPrice(rs.getInt("price"));
				vo.setMaker(rs.getString("maker"));
				list.add(vo);				
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(rs, pstmt, con);
		}
		return list;
	}
	
	// 도서 정보 수정 : CODE 로 조회
	public int updateBooks(BooksVO vo) {
		int su = 0;
		try {
			con = JDBCUtil.getConnection();
			pstmt = con.prepareStatement(BOOKS_UPDATE);
			pstmt.setString(1, vo.getName());
			pstmt.setInt(2, vo.getPrice());
			pstmt.setString(3, vo.getMaker());
			pstmt.setString(4, vo.getCode());
			su = pstmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(rs, pstmt, con);
		}
		return su;
	}
	
	// 도서 정보 삭제 : CODE 로 조회
	public int deleteBooks(BooksVO vo) {
		int su = 0;
		try {
			con = JDBCUtil.getConnection();
			pstmt = con.prepareStatement(BOOKS_DELETE);
			pstmt.setString(1, vo.getCode());
			su = pstmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(rs, pstmt, con);
		}
		return su;
	}
	
	// 도서 정보 확인 : CODE 로 조회
	public BooksVO getBooks(BooksVO vo) {
		try {
			con = JDBCUtil.getConnection();
			pstmt = con.prepareStatement(BOOKS_SELECT);
			pstmt.setString(1, vo.getCode());
			rs = pstmt.executeQuery();
			if(rs.next()) {
				vo = new BooksVO();
				vo.setCode(rs.getString("code"));
				vo.setName(rs.getString("name"));
				vo.setPrice(rs.getInt("price"));
				vo.setMaker(rs.getString("maker"));
			} else vo=null;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(rs, pstmt, con);
		}
		return vo;
	}
	
}

JDBCUtil.java

package com.book.dao;

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

public class JDBCUtil {
	private static final String driver = "oracle.jdbc.OracleDriver";
	private static final String url = "jdbc:oracle:thin:@localhost:1521:XE";
	private static final String id = "dbtest";
	private static final String pwd = "a1234";
	
	public static Connection getConnection() {
		Connection con = null;
		try {
			Class.forName(driver);
			con = DriverManager.getConnection(url,id,pwd);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return con;
	} // getConnection() end
	
	public static void close(ResultSet rs, PreparedStatement pstmt, Connection con) {
		try {
			if(rs!=null) rs.close();
			if(pstmt!=null) pstmt.close();
			if(con!=null) con.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	} // close() end
	
}

com.book.service

BookService.inter

package com.book.service;

import java.util.List;

import com.book.bean.BooksVO;

public interface BookService {
	// 도서관련 CRUD 기능 메서드
	
	// 도서 정보 등록
	int insertBooks(BooksVO vo);
	// 도서 목록 조회
	List<BooksVO> getBookslist(BooksVO vo);
	// 도서 정보 수정
	int updateBooks(BooksVO vo);
	// 도서 정보 확인
	BooksVO getBooks(BooksVO vo);
	// 도서 정보 삭제
	int deleteBooks(BooksVO vo);
	
}

BookServiceImpl.java

package com.book.service;

import java.util.List;

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

import com.book.bean.BooksVO;
import com.book.dao.BooksDAO;


//	@ Service
//	- 비즈니스 로직을 수행하는 class 라는 것을 나타냄

@Service("bookService")
public class BookServiceImpl implements BookService{
	@Autowired
	private BooksDAO booksDAO;
	
	// 도서 정보 등록
	@Override
	public int insertBooks(BooksVO vo) {
		// TODO Auto-generated method stub
		return booksDAO.insertBooks(vo);
	}
	// 도서 목록 조회
	@Override
	public List<BooksVO> getBookslist(BooksVO vo) {
		// TODO Auto-generated method stub
		return booksDAO.getBookslist(vo);
	}
	// 도서 정보 수정
	@Override
	public int updateBooks(BooksVO vo) {
		// TODO Auto-generated method stub
		return booksDAO.updateBooks(vo);
	}
	// 도서 정보 확인
	@Override
	public BooksVO getBooks(BooksVO vo) {
		// TODO Auto-generated method stub
		return booksDAO.getBooks(vo);
	}
	// 도서 정보 삭제
	@Override
	public int deleteBooks(BooksVO vo) {
		// TODO Auto-generated method stub
		return booksDAO.deleteBooks(vo);
	}
	
}

com.book.jdbc

JDBCTest.java

package com.book.jdbc;

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

import org.springframework.context.support.GenericXmlApplicationContext;

import com.book.bean.BooksVO;
import com.book.service.BookService;

public class JDBCTest {
	public static void main(String[] args) {
		GenericXmlApplicationContext context = new GenericXmlApplicationContext("applicationContext.xml");
		
		BookService bookService = context.getBean("bookService", BookService.class);
		BooksVO vo = new BooksVO();
		int su = 0; // 등록, 수정, 삭제 결과 확인을 위한 int 변수
		List<BooksVO> list =null;
		Scanner sc = new Scanner(System.in);
		
//		--- 도서 등록 ---
		
		System.out.println("--- 도서 정보 등록 ---");
		System.out.print("도서 번호 >> ");
		vo.setCode(sc.next());
		System.out.print("도서명 >> ");
		sc.nextLine();
		vo.setName(sc.nextLine());
		System.out.print("도서 가격 >> ");
		vo.setPrice(sc.nextInt());
		System.out.print("출판사 >> ");
		vo.setMaker(sc.next());
		
		su = bookService.insertBooks(vo);
		if(su>0) {
			System.out.println("도서 등록 성공");
		} else {
			System.out.println("도서 등록 실패");
		}
		
//		--- 도서 목록 ---		
		System.out.println("--- 도서 목록 조회 ---");
		list = bookService.getBookslist(null);
		for(BooksVO book : list) {
			System.out.println(book);
		}
		
//		--- 도서 수정 ---		
		System.out.println("--- 도서 정보 수정 ---");
		System.out.print("수정할 도서 번호 >> ");
		vo.setCode(sc.next());
		System.out.print("도서명 >> ");
		sc.nextLine();
		vo.setName(sc.nextLine());
		System.out.print("도서 가격 >> ");
		vo.setPrice(sc.nextInt());
		System.out.print("출판사 >> ");
		vo.setMaker(sc.next());
		su = bookService.updateBooks(vo);
		if(su>0) {
			System.out.println("도서 수정 성공");
		} else {
			System.out.println("도서 수정 실패");
		}
		
		
//		--- 도서 삭제 ---		
		System.out.println("--- 도서 정보 삭제 ---");
		System.out.print("삭제할 도서 번호 >> ");
		vo.setCode(sc.next());
		su = bookService.deleteBooks(vo);
		if(su>0) {
			System.out.println("도서 삭제 성공");
		} else {
			System.out.println("도서 삭제 실패");
		}
		
//		--- 도서 확인 ---		
		System.out.println("--- 도서 정보 확인 ---");
		System.out.print("조회할 도서 번호 >> ");
		vo.setCode(sc.next());
		vo = bookService.getBooks(vo);
		if(vo!=null)
				System.out.println(vo);
		else System.out.println("없음");
		
	}
}

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 https://www.springframework.org/schema/context/spring-context-4.3.xsd">
	<context:component-scan base-package="com.book.service"/>
	<bean id="booksDAO" class="com.book.dao.BooksDAO"/>
</beans>

P05_jdbc_quiz

CREATE TABLE score(
no VARCHAR2(10) PRIMARY KEY, -- 학번
name VARCHAR2(30),           -- 이름
kor NUMBER,                  -- 과목별 점수
eng NUMBER,
mat NUMBER,
tot NUMBER,                  -- 총점
avg NUMBER(10, 2),           -- 평균
logtime date                 -- 등록일자
);

desc score;

select * from score;

com.score.bean

ScoreVO.java

package com.score.bean;

/*
CREATE TABLE score(
no VARCHAR2(10) PRIMARY KEY, -- 학번
name VARCHAR2(30),           -- 이름
kor NUMBER,                  -- 과목별 점수
eng NUMBER,
mat NUMBER,
tot NUMBER,                  -- 총점
avg NUMBER(10, 2),           -- 평균
logtime date                 -- 등록일자
);
 */

public class ScoreVO {

	private String no;
	private String name;
	private int kor;
	private int eng;
	private int mat;
	private int tot;
	private double avg;
	private String logtime;
	
	public ScoreVO() {}

	public ScoreVO(String no, String name, int kor, int eng, int mat) {
		super();
		this.no = no;
		this.name = name;
		this.kor = kor;
		this.eng = eng;
		this.mat = mat;
	}

	public String getNo() {
		return no;
	}

	public void setNo(String no) {
		this.no = no;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public int getKor() {
		return kor;
	}

	public void setKor(int kor) {
		this.kor = kor;
	}

	public int getEng() {
		return eng;
	}

	public void setEng(int eng) {
		this.eng = eng;
	}

	public int getMat() {
		return mat;
	}

	public void setMat(int mat) {
		this.mat = mat;
	}

	public int getTot() {
		return tot;
	}

	public void setTot(int tot) {
		this.tot = tot;
	}

	public double getAvg() {
		return avg;
	}

	public void setAvg(double avg) {
		this.avg = avg;
	}

	public String getLogtime() {
		return logtime;
	}

	public void setLogtime(String logtime) {
		this.logtime = logtime;
	}

	@Override
	public String toString() {
		return "성적 [ no : " + no + ", name : " + name + ", kor : " + kor + ", eng : " + eng + ", mat : " + mat + ", tot : "
				+ tot + ", avg : " + avg + " ]";
	}
	
	
	
}

com.score.dao

ScoreDAO.java

package com.score.dao;

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.score.bean.ScoreVO;

// @Repository
// - DAO class 에 사용됨
// - DB 에 접근하는 메서드를 가지고있는 class에 사용됨
@Repository
public class ScoreDAO {

	// JDBC 관련 변수
	private Connection con;
	private PreparedStatement pstmt;
	private ResultSet rs;
	
	// SQL
	private final String INSERT = "INSERT INTO score VALUES(?,?,?,?,?,?,?,SYSDATE)";
	private final String LIST = "SELECT * FROM score ORDER BY avg DESC";
		
	// 학생 한명의 성적 입력
	public int insertScore(ScoreVO vo) {
		int su = 0;
		int tot = vo.getKor() + vo.getEng() + vo.getMat();
		double avg = tot/3.0;
		vo.setTot(tot);
		vo.setAvg(avg);
		try {
			con = JDBCUtil.getConnection();
			pstmt = con.prepareStatement(INSERT);
			pstmt.setString(1, vo.getNo());
			pstmt.setString(2, vo.getName());
			pstmt.setInt(3, vo.getKor());
			pstmt.setInt(4, vo.getEng());
			pstmt.setInt(5, vo.getMat());
			pstmt.setInt(6, vo.getTot());
			pstmt.setDouble(7, vo.getAvg());
			su = pstmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(rs, pstmt, con);
		}
		return su;
	}
	
	// 학생 성적 목록
	public List<ScoreVO> getScoreList(ScoreVO vo){
		List<ScoreVO> list = new ArrayList<ScoreVO>();
		try {
			con = JDBCUtil.getConnection();
			pstmt = con.prepareStatement(LIST);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				vo = new ScoreVO();
				vo.setNo(rs.getString("no"));
				vo.setName(rs.getString("name"));
				vo.setKor(rs.getInt("kor"));
				vo.setEng(rs.getInt("eng"));
				vo.setMat(rs.getInt("mat"));
				vo.setTot(rs.getInt("tot"));
				vo.setAvg(rs.getDouble("avg"));
				list.add(vo);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(rs, pstmt, con);
		}
		return list;
	}
}

JDBCUtil.java

package com.score.dao;

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

public class JDBCUtil {

	private static final String driver = "oracle.jdbc.OracleDriver";
	private static final String url = "jdbc:oracle:thin:@localhost:1521:xe";
	private static final String id = "dbtest";
	private static final String pwd = "a1234";
	
	public static Connection getConnection() {
		
		Connection con = null;
		try {
			Class.forName(driver);
			con = DriverManager.getConnection(url, id, pwd);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return con;
		
	}
	
	public static void close(ResultSet rs, PreparedStatement pstmt, Connection con) {
		try {
 			if(rs != null) rs.close();
 			if(pstmt != null) pstmt.close();
 			if(con != null) con.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
}

com.score.service

ScoreService.inter





package com.score.service;

import java.util.List;

import com.score.bean.ScoreVO;

public interface ScoreService {

	// 학생 한명의 성적 입력
	int insertScore(ScoreVO vo);
	// 학생 성적 목록
	List<ScoreVO> getScoreList(ScoreVO vo);
}

ScoreServiceImpl.java

package com.score.service;

import java.util.List;

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

import com.score.bean.ScoreVO;
import com.score.dao.ScoreDAO;
@Service("scoreService")
public class ScoreServiceImpl implements ScoreService{
	
	@Autowired
	private ScoreDAO scoreDAO;
	
	@Override
	public int insertScore(ScoreVO vo) {
		// TODO Auto-generated method stub
		return scoreDAO.insertScore(vo);
	}

	@Override
	public List<ScoreVO> getScoreList(ScoreVO vo) {
		// TODO Auto-generated method stub
		return scoreDAO.getScoreList(vo);
	}
	
}

com.score.main

ScoreMain.java





package com.score.main;

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

import org.springframework.context.support.GenericXmlApplicationContext;

import com.score.bean.ScoreVO;
import com.score.service.ScoreService;


public class ScoreMain {
	public static void main(String[] args) {
		GenericXmlApplicationContext context = new GenericXmlApplicationContext("applicationContext.xml");
		
		ScoreService scoreService = context.getBean("scoreService", ScoreService.class);
		ScoreVO vo = new ScoreVO();
		int su = 0; 
		List<ScoreVO> list =null;
		Scanner sc = new Scanner(System.in);
		
//		--- 학생 정보 ---
		
		while(true) {
			System.out.println("1. 학생 정보 등록"
					+ "2. 리스트"
					+ ">>");
			String i=sc.next();
			switch (i) {
			case "1":
				System.out.println("--- 학생 정보 등록 ---"); 
				System.out.print("학번 >> ");
				vo.setNo(sc.next());
				System.out.print("이름 >> ");
				vo.setName(sc.next());
				System.out.print("국어 >> ");
				vo.setKor(sc.nextInt());
				System.out.print("영어 >> ");
				vo.setEng(sc.nextInt());
				System.out.print("수학 >> ");
				vo.setMat(sc.nextInt());
				
				su = scoreService.insertScore(vo);
				if(su>0) {
					System.out.println("학생 정보 등록 성공");
				} else {
					System.out.println("학생 정보 등록 실패");
				}
				break;
			case "2":
//				--- 학생 정보 목록 ---		
				System.out.println("--- 도서 목록 조회 ---");
				list = scoreService.getScoreList(null);
				for(ScoreVO stu : list) {
					System.out.println(stu);
				}
				break;
			case "0":
				System.out.print("종료");
				System.exit(0);
			default:
				System.out.println("다시");
				break;
			}
		}
	}
}

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.score.service"/>
	<bean id="scoreDAO" class="com.score.dao.ScoreDAO"/>
</beans>
profile
허니몬
Fintech
이전 포스트

SpringBoot/day45 / 23.11.08(수) / (핀테크) Spring 및 Ai 기반 핀테크 프로젝트 구축

다음 포스트

SpringBoot/day47 / 23.11.10(금) / (핀테크) Spring 및 Ai 기반 핀테크 프로젝트 구축

0개의 댓글