JDBC API

JDBC API, DAO Pattern 목표 : 상품정보를  DB에 저장하고,  검색, 수정, 삭제 하는  프로그램 구현   1. 상품 정보를 저장할 수 있는  테이블을 구성(기존의 product 테이블에서 작업)   2. 상품관리를 할 수 있도록 클래스를 설계   DAO.java: 상품 정보 기능들의 추상메소드들을 정의하고 있는 인터페이스 Product.java: 상품 정보를 저장하는 클래스 ProductDAO.java : 상품 정보를 DB에 CRUD (상품정보 저장, 전체조회, 상세조회, 수정, 삭제기능) 하는 클래스  Test.java : 위 클래스들이 잘 작동하는지 테스트 하는 클래스

import java.util.List;
public interface DAO {
	public void insertProduct(int procode,String proname,int proprice);
	public List<Product> allViewCustomer();
	public Product findProduct(int procode);
	public void updateProduct(int procode,int proprice);
	public int deleteProduct(int procode);
}

 

public class Product {
	private int procode;
	private String proname;
	private int proprice;
	
	public Product() {
		
	}
	

	public Product(int procode, String proname, int proprice) {
		super();
		this.procode = procode;
		this.proname = proname;
		this.proprice = proprice;
	}


	public int getProcode() {
		return procode;
	}

	public void setProcode(int procode) {
		this.procode = procode;
	}

	public String getProname() {
		return proname;
	}

	public void setProname(String proname) {
		this.proname = proname;
	}

	public int getProprice() {
		return proprice;
	}

	public void setProprice(int proprice) {
		this.proprice = proprice;
	}

	@Override
	public String toString() {
		return "Product [procode=" + procode + ", proname=" + proname + ", proprice=" + proprice + "]";
	}

	
	
	
	
}

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

public class ProductDAO implements DAO {
	// 상품 정보를 DB에 CRUD (상품정보 저장, 전체조회, 상세조회, 수 정, 삭제기능) 하는 클래스

	// 1. Driver loading
	static {
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			System.out.println("class loading failure");
		}

	}

	// 2. Connection
	private Connection getConnection() throws SQLException {
		Connection con = DriverManager.getConnection(
				"jdbc:mysql://127.0.0.1:3306/scott?serverTimezone=UTC&useUniCode=yes&characterEncoding=UTF-8", "아이디",
				"비밀번호");
		return con;
	}

	// 상품 정보 저장
	public void insertProduct(int procode, String proname, int proprice) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = getConnection();

			String sql = "insert into product(procode,proname,proprice)values(?,?,?)";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, procode);
			pstmt.setString(2, proname);
			pstmt.setInt(3, proprice);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (pstmt != null)
					pstmt.close();
			} catch (Exception e) {
			}
			;
			try {
				if (conn != null)
					conn.close();
			} catch (Exception e) {
			}
			;

		}

	}

	// 전체 조회
	public List<Product> allViewCustomer() {
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		Connection conn = null;
		ArrayList<Product> list = new ArrayList();

		try {
			conn = getConnection();
			String sql = "select procode,proname,proprice from product";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				list.add(new Product(rs.getInt("procode"), rs.getString("proname"), rs.getInt("proprice")));
			}
			
		} catch (SQLException e) {

			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
			} catch (Exception e) {
			}
			;
			try {
				if (pstmt != null)
					pstmt.close();
			} catch (Exception e) {
			}
			;
			try {
				if (conn != null)
					conn.close();
			} catch (Exception e) {
			}
			;
		}
		return list;
	}

	// 상세조회
	public Product findProduct(int procode) {
		Product product = new Product();
		String sql = "select procode,proname,proprice from product where procode = ?";
		Connection con = null;
		PreparedStatement psmt = null;
		ResultSet rs = null;
		try {
			con = getConnection();
			psmt = con.prepareStatement(sql);
			psmt.setInt(1, procode);
			rs = psmt.executeQuery();
			while (rs.next()) {
				product.setProcode(rs.getInt("procode"));
				product.setProname(rs.getString("proname"));
				product.setProprice(rs.getInt("proprice"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (psmt != null)
					psmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return product;
	}

	// 수정
	public void updateProduct(int procode, int proprice) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = getConnection();

			String sql = "update product set proprice = ? where procode=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, proprice);
			pstmt.setInt(2, procode);
			pstmt.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (pstmt != null)
					pstmt.close();
			} catch (Exception e) {
			}
			;
			try {
				if (conn != null)
					conn.close();
			} catch (Exception e) {
			}
			;

		}

	}
	//삭제
	public int deleteProduct(int procode) {
		int successCnt = 0;
		String sql = "delete from product where procode = ?";
		PreparedStatement psmt = null;
		Connection con = null;
		try {
			con = getConnection();
			psmt = con.prepareStatement(sql);
			psmt.setInt(1, procode);
			successCnt = psmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if(psmt != null) psmt.close();
				if(con != null) con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return successCnt;
	}
	
}

 

import java.util.List;

public class Test {

	public static void main(String[] args) {
		ProductDAO dao = new ProductDAO();
//		상품 정보 저장
		dao.insertProduct(6, "TV4", 330000);
		dao.insertProduct(7, "노트북3", 150000);
		dao.insertProduct(8, "노트북4", 160000);
	
		
// 		전체 조회		
		List<Product> list = dao.allViewCustomer();
		for(Product product:list) {
			System.out.println(product);
		}
		
//		상세조회		
		Product p = dao.findProduct(7);
		System.out.println(p.getProcode()+" "+p.getProname()+" "+p.getProprice());

//		수정
		dao.updateProduct(2, 50000);
		
//		삭제
		dao.deleteProduct(8);
	

		


	}

}