JDBC API 연습문제

• Mysql 과 연동하여 상품정보를 DB에 저장하고 검색, 수정, 삭제 하는 프로그램을 구현해보자.

---

- DAO.java

import java.util.List;

public interface DAO {
	public void insertProduct(String prCode, String name, int price);
	public void deleteProduct(String prCode);
	public void updateProduct();
	public List<Product> allViewProduct();
	public Product findProduct(String prCode);
}

---

- Product.java

public class Product {
	private String code;
	private String name;
	private int price;
	
	public Product() {}

	@Override
	public String toString() {
		return "Product [code=" + code + ", name=" + name + ", price=" + price + "]";
	}

	public Product(String code, String name, int price) {
		super();
		this.code = code;
		this.name = name;
		this.price = price;
	}

	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;
	}
	
	
}

---

- ProductDAO.java

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 {

	static {
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			System.out.println("class loading failure");
		}
	}
	
	private Connection getConnection() throws SQLException {
		Connection con = DriverManager.getConnection
			      ("jdbc:mysql://127.0.0.1:3306/scott?serverTimezone=UTC&useUniCode=yes&characterEncoding=UTF-8&useSSL=false","ssafy","ssafy");
		return con;
	}
	
	@Override
	public void insertProduct(String code, String name, int price) {
		// TODO Auto-generated method stub
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			conn = getConnection();
			String sql = "insert into product(productCode, productName, price) values(?,?,?)";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, code);
			pstmt.setString(2, name);
			pstmt.setInt(3, price);
			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) {};
		}
		
	}

	@Override
	public void deleteProduct(String code) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			conn = getConnection();
			String sql = "delete from product where productCode = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, code);
			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) {};
		}
		
	}

	@Override
	public void updateProduct() {
		
		
	}

	@Override
	public List<Product> allViewProduct() {
		// TODO Auto-generated method stub
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		Connection conn = null;
		ArrayList<Product> list = new ArrayList();
		
		try {
			conn = getConnection();
			String sql = "select productCode, productName, price from product";
			pstmt = conn.prepareStatement(sql);
			pstmt.executeQuery();
			rs = pstmt.executeQuery();
			while(rs.next()) {
				list.add(new Product(rs.getString("productCode"), rs.getString("productName"), rs.getInt("price")));
			}
		} 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;
	}

	@Override
	public Product findProduct(String code) {
		ResultSet rs = null;
		PreparedStatement pstmt=null;
		Connection conn = null;
		Product p = null;
		
		try {
			conn = getConnection();
			String sql = "select productCode, productName, price from product where productCode = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, code);
			pstmt.executeQuery();
			rs = pstmt.executeQuery();
		
			while(rs.next()) {
				p = new Product(rs.getString("productCode"), rs.getString("productName"), rs.getInt("price"));
			}
			
		} 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 p;
	}

}

---

- Test.java

import java.util.List;

public class Test {

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		DAO dao = new ProductDAO();
		
		// 상품 정보 저장
		dao.insertProduct("A012", "TV12", 300000);
		dao.insertProduct("B022", "냉장고22", 120000);
		
		// 전체 조회 
		System.out.println("*********전체 조회*********");
		List<Product> list = dao.allViewProduct();
		for(Product p : list) System.out.println(p);
		
		// 삭제 기능
		dao.deleteProduct("A012");
		
		// 상세 조회
		System.out.println(dao.findProduct("B003"));
	}

}