MVC패턴, 오라클 JDBC활용한 프로그램

클라이언트 클래스

package client;

import ctrl.CTRL;

public class Test {
	public static void main(String[] args) {

		CTRL app=new CTRL();
		
		app.start();
		
	}
}

 

 

 

JDBCUtil 클래스

package model;

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

// 다양한 DAO에서
//  연결(), 해제() 를 사용할 예정
//   => 별도의 Util 클래스로 '모듈화'
public class JDBCUtil {
	
	static final String driverName="oracle.jdbc.driver.OracleDriver";
	static final String url="jdbc:oracle:thin:@localhost:1521:xe";
	static final String user="teemo";
	static final String passwd="1234";
	
	public static Connection connect() {
		Connection conn=null;
		
		try {
			// 1. driver를 메모리에 로드 == 적재
			Class.forName(driverName);
			
			// 2. conn 객체를 확보
			conn=DriverManager.getConnection(url,user,passwd);
		} catch (ClassNotFoundException | SQLException e) {
			e.printStackTrace();
		}
		
		return conn;
	}
	
	public static void disconnect(PreparedStatement pstmt, Connection conn) {
		try {
			// 4. 연결 해제
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

 

ProductDAO 클래스

package model;

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

public class ProductDAO {

	private Connection conn; // DB와의 연결을 담당
	private PreparedStatement pstmt; // CRUD 수행을 담당

	private static final String SELECTALL="SELECT * FROM PRODUCT";
	private static final String SELECTALL_NAME="SELECT * FROM PRODUCT WHERE NAME LIKE '%'||?||'%'";
	private static final String SELECTONE="SELECT * FROM PRODUCT WHERE PID=?";

	private static final String INSERT="INSERT INTO PRODUCT VALUES((SELECT NVL(MAX(PID),1000) + 1 FROM PRODUCT),?,?,?)";
	private static final String UPDATE="UPDATE PRODUCT SET CNT=CNT-1 WHERE PID=?";
	private static final String DELETE="DELETE FROM PRODUCT WHERE PID=?";

	public ArrayList<ProductDTO> selectAll(ProductDTO pDTO){
		ArrayList<ProductDTO> datas=new ArrayList<ProductDTO>();

		conn=JDBCUtil.connect();
		try {
			// 3. pstmt 로 CRUD 수행
			if(pDTO.getSearchCondition().equals("전체")) {
				pstmt=conn.prepareStatement(SELECTALL);
			}
			else { // 이름
				pstmt=conn.prepareStatement(SELECTALL_NAME);
				pstmt.setString(1, pDTO.getName());
			}
			ResultSet rs=pstmt.executeQuery();

			while(rs.next()) {
				ProductDTO data=new ProductDTO();
				data.setPid(rs.getInt("PID"));
				data.setName(rs.getString("NAME"));
				data.setPrice(rs.getInt("PRICE"));
				data.setCnt(rs.getInt("CNT"));
				datas.add(data);
			}

			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.disconnect(pstmt, conn);
		}

		return datas;
	}
	public ProductDTO selectOne(ProductDTO pDTO){
		ProductDTO data=null;

		conn=JDBCUtil.connect();
		try {
			pstmt=conn.prepareStatement(SELECTONE);
			pstmt.setInt(1, pDTO.getPid());
			ResultSet rs=pstmt.executeQuery();

			if(rs.next()) {
				data=new ProductDTO();
				data.setPid(rs.getInt("PID"));
				data.setName(rs.getString("NAME"));
				data.setPrice(rs.getInt("PRICE"));
				data.setCnt(rs.getInt("CNT"));
			}

			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.disconnect(pstmt, conn);
		}		

		return data;
	}
	public boolean insert(ProductDTO pDTO) {
		conn=JDBCUtil.connect();
		try {
			pstmt=conn.prepareStatement(INSERT);
			pstmt.setString(1, pDTO.getName());
			pstmt.setInt(2, pDTO.getPrice());
			pstmt.setInt(3, pDTO.getCnt());
			int result=pstmt.executeUpdate();
			if(result<=0) {
				return false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			JDBCUtil.disconnect(pstmt, conn);
		}		
		return true;
	}
	public boolean update(ProductDTO pDTO) {
		conn=JDBCUtil.connect();
		try {
			pstmt=conn.prepareStatement(UPDATE);
			pstmt.setInt(1, pDTO.getPid());
			int result=pstmt.executeUpdate();
			if(result<=0) {
				return false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			JDBCUtil.disconnect(pstmt, conn);
		}		
		return true;	
	}
	public boolean delete(ProductDTO pDTO) {
		conn=JDBCUtil.connect();
		try {
			pstmt=conn.prepareStatement(DELETE);
			pstmt.setInt(1, pDTO.getPid());
			int result=pstmt.executeUpdate();
			if(result<=0) {
				return false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			JDBCUtil.disconnect(pstmt, conn);
		}		
		return true;
	}

}

 

 

 

ProductDTO 클래스

package model;

public class ProductDTO {
	private int pid;
	private String name;
	private int price;
	private int cnt;
	
	private String searchCondition;
	public String getSearchCondition() {
		return searchCondition;
	}
	public void setSearchCondition(String searchCondition) {
		this.searchCondition = searchCondition;
	}
	
	public int getPid() {
		return pid;
	}
	public void setPid(int pid) {
		this.pid = pid;
	}
	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 int getCnt() {
		return cnt;
	}
	public void setCnt(int cnt) {
		this.cnt = cnt;
	}
	@Override
	public String toString() {
		return "ProductDTO [pid=" + pid + ", name=" + name + ", price=" + price + ", cnt=" + cnt + "]";
	}
}

 

 

 

View 클래스

package view;

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

import model.ProductDTO;

public class VIEW {
	private Scanner sc;
	public VIEW() {
		sc=new Scanner(System.in);
	}
	
	public void printMenu() {
		System.out.println("0. 종료");
		System.out.println("1. 추가");
		System.out.println("2. 목록");
		System.out.println("3. 번호로 검색");
		System.out.println("4. 구매");
		System.out.println("5. 삭제");
		System.out.println("6. 이름으로 검색");
	}
	public int inputAction() {
		System.out.print("번호입력 >> ");
		return sc.nextInt();
	}
	
	public void printDatas(ArrayList<ProductDTO> datas) {
		for(ProductDTO data:datas) {
			if(data.getCnt()<=0) {
				System.out.println(data.getName()+" 품절!");
				continue;
			}
			System.out.println(data);
		}
	}
	
	public int inputPid() {
		System.out.print("상품번호입력 >> ");
		return sc.nextInt();
	}
	public void printData(ProductDTO data) {
		if(data==null) {
			System.out.println("해당 상품은 존재하지않습니다...");
			return;
		}
		System.out.println(data);
	}
	public void printInfo(ProductDTO data) {
		if(data==null) {
			System.out.println("해당 상품이 존재하지않아 구매가 불가능합니다...");
		}
		else if(data.getCnt()<=0) {
			System.out.println(data.getName()+" 상품은 품절입니다!");
			System.out.println("재입고를 기다려주세용,,");
		}
	}
	
	public String inputName() {
		System.out.print("상품이름입력 >> ");
		return sc.next();
	}
	public int inputPrice() {
		System.out.print("상품가격입력 >> ");
		return sc.nextInt();
	}
	public int inputCnt() {
		System.out.print("상품재고입력 >> ");
		return sc.nextInt();
	}
	public void printTrue() {
		System.out.println("성공!");
	}
	public void printFalse() {
		System.out.println("실패...");
	}
}