CODE [day_03] DB / 04. JDBC - SELECT, COUNT 함수, INSERT, ORDER BY, SELECT에 조건주기 (StringBulider 활용)

 

디렉토리 경로

Test (main class)

1. Primary Key인 product id로 상품정보를 검색하는 예제

/TestTestProductDAO1.java

package test;

import model.ProductDAO;
import model.ProductDTO;

public class TestProductDAO1 {
	public static void main(String[] args) {
		try {
			ProductDAO dao = new ProductDAO();
			String id = "1"; // dto ok
			id = "a"; // dto = null
			ProductDTO dto = dao.findProductById(id);
			if (dto == null)
				System.out.println(id + " 아이디에 해당하는 상품이 없습니다.");
			else
				System.out.println("검색결과--> " + dto);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}

 

2. 총 상품 수를 반환받는 예제 : ResultSet의 getInt() 이용

/TestTestProductDAO2.java

package test;

import model.ProductDAO;

public class TestProductDAO2 {
	public static void main(String[] args) {
		try {
			ProductDAO dao = new ProductDAO();
			int totalCount = dao.getTotalCount();
			System.out.println("총 상품 수: "+totalCount);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}

 

3. id에 대한 상품이 존재하는지 유무를 반환 - COUNT함수 + primary key를 이용!

/TestTestProductDAO3.java

package test;

import model.ProductDAO;

public class TestProductDAO3 {
	public static void main(String[] args) {
		try {
			ProductDAO dao = new ProductDAO();
			String id = "1"; // true
			id = "a"; //false
			
			boolean flag = dao.isExistProduct(id); 
			
			if (flag) //flag == true : product가 있다
				System.out.println(id + " 아이디에 해당하는 상품 있음");
			else //flag == false : product가 있다
				System.out.println(id + " 아이디에 해당하는 상품 없음");
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}

 

4. product table에 상품정보를 등록하는 예제 - INSERT

/TestTestProductDAO4.java

package test;

import model.ProductDAO;
import model.ProductDTO;

//product table에 상품정보를 등록하는 예제
public class TestProductDAO4 {
	public static void main(String[] args) {
		try 
		{
			ProductDAO dao = new ProductDAO();
			
			//등록할 상품정보 객체 (dto)
			ProductDTO dto = new ProductDTO("4", "참이슬", "진로", 3000);
			
			// dto에 담긴 정보를 registerProduct 함수로 넘겨 INSERT
			// PK(primary key)인 id가 중복될 경우 에러뜨게 하기
			if (dao.isExistProduct(dto.getId())) //기존 테이블에 있으면 등록X
				System.out.println(dto.getId()
						+" 아이디에 대한 상품이 존재하므로 등록 불가");
			else { //중복이 아니면 registerProduct함수로 INSERT하기
				dao.registerProduct(dto);
				System.out.println("INSERT OK !" 
									+ dao.findProductById(dto.getId()));
			}
		}
		
		catch(Exception e) {
			e.printStackTrace();
		}
	}
}

 

5. product table에 상품정보를 정렬하여 조회하는 예제 - SELECT / WHERE / ORDER BY

/TestTestProductDAO5.java

package test;

import java.util.ArrayList;

import model.ProductDAO;
import model.ProductDTO;

public class TestProductDAO5 {
	public static void main(String[] args) {
		try 
		{
			ProductDAO dao = new ProductDAO();
			int lowPrice = 2000; //2000 이상
			int highPrice = 3000; //3000 이하
			
			// 상품 price가 2000이상 3000이하 상품명 name과 가격 price의 리스트 조회
			// 상품 price는 내림차순
			ArrayList<ProductDTO> list = 
					dao.getProductListByPriceOrderByDesc(lowPrice, highPrice);
			for (int  i = 0;  i < list.size(); i++) {
				System.out.println(list.get(i));
			}
		}
		catch(Exception e) {
			e.printStackTrace();
		}
	}
}

 

6. product table에 price를 할인가를 주고 정렬하여 조회하는 예제 - StringBulider 활용

*StringBulider는 String과 달리 적재되기 때문에, 메모리 부담을 줄여줄 수 있다.

/TestTestProductDAO6.java

package test;

import java.util.ArrayList;

import model.ProductDAO;
import model.ProductDTO;

public class TestProductDAO6 {
	public static void main(String[] args) {
		try {
			ProductDAO dao = new ProductDAO();
			
			int percent = 10; //10% 할인된 가격으로 조회하되, 가격오름차순으로
			ArrayList<ProductDTO> list = dao.productListDiscountPrice(percent);
			
			for (int i = 0; i < list.size(); i++) {
				System.out.println(list.get(i).getName()+ " "+ list.get(i).getPrice());
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}

 

---

DAO ( Data Access Object )

: 데이베이스 연동 로직을 정의한 객체

 

/ProductDAO.java

package model;

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


public class ProductDAO {
	private String driver = "oracle.jdbc.OracleDriver";
	private String dbUrl = "jdbc:oracle:thin:@127.0.0.1:1521:xe";
	private String user = "scott";
	private String password = "tiger";
	
	//1. 생성자에서 드라이버 로딩
	 public ProductDAO() throws ClassNotFoundException {
		 Class.forName(driver);
	 }
	 
	 //closeAll
	 public void closeAll(PreparedStatement pstmt, Connection con) throws SQLException {
		 if (pstmt != null)
			 pstmt.close();
		 if (con != null)
			 con.close();
	 }
	 
	 //closeAll OverLoading
	 public void closeAll(ResultSet rs, PreparedStatement pstmt, Connection con) throws SQLException {
		 if (rs != null)
			 rs.close();
		 this.closeAll(pstmt, con); //같은 클래스 내 closeAll 메서드 호출
	 }
	
	 //findProductById method -- id로 상품 정보 찾기
	 public ProductDTO findProductById(String id) throws SQLException {
		 Connection con = null;
		 PreparedStatement pstmt = null;
		 ResultSet rs = null;
		 ProductDTO dto = null;
		 try 
		 {
			 //2. 연결
			 con = DriverManager.getConnection(dbUrl, user, password);
			 //3. sql 문 정의
			 String sql = "SELECT name, maker, price"
			 		+ " FROM product WHERE id = ?";
			 //4. PreparedStaement
			 pstmt = con.prepareStatement(sql);
			 //5. sql 문 완성 (set)
			 pstmt.setString(1, id);
			 //6. sql 실행
			 rs = pstmt.executeQuery();
			 //7, ResultSet
			 if (rs.next()) {
				 //* ProductDTO Constructor 참고
				 //: ProductDTO(String id, String name, String maker, int price)
				 dto = new ProductDTO(id, rs.getString(2),
						 rs.getString(3), rs.getInt(4));
			 }
		 } finally {
			 //8. close
			 closeAll(rs, pstmt, con);
		 }
		 return dto;
	 }//findProductById
	 
	 //getTotalCount -- 총 상품 수 반환하기
	 public int getTotalCount() throws SQLException {
		 Connection con = null;
		 PreparedStatement pstmt = null;
		 ResultSet rs = null;
		 int count = 0;
		 
		 try {
		 //2. connection
		 con = DriverManager.getConnection(dbUrl, user, password);
		 
		 //3. sql문 정의
		 String sql = "SELECT COUNT(*) FROM product";
				 
		 //4. PreparedStatement
		 pstmt = con.prepareStatement(sql);
		 
		 //5. sql문 실행(ResultSet) <- setString대상이 없으므로 바로 실행
		 rs = pstmt.executeQuery(sql);
		 
		 //6. sql 결과 값 가져오기
		 if (rs.next())
			 count = rs.getInt(1);
		 
		 } finally {
			 //7. close
			 closeAll(rs, pstmt, con);
		 }
		 return count;
		 
	 } //getTotalCount 
	 
	 //isExistProduct -- 해당 id에 맞는 상품이 존재하는지 유무 확인
	 public boolean isExistProduct(String id) throws SQLException {
		 Connection con = null;
		 PreparedStatement pstmt = null;
		 ResultSet rs = null;
		 boolean flag = false; //boolean의 default는 false
		 
		 try {
			 //2. connection
			 con = DriverManager.getConnection(dbUrl, user, password);
			 //3. sql문 정의
			 String sql = "SELECT COUNT(*) FROM product WHERE id = ?";
			 //4. PreparedStatement
			 pstmt = con.prepareStatement(sql);
			 //5. sql문 완성(재정의 - setString)
			 pstmt.setString(1, id);
			 //6. sql문 실행하여 ResultSet에 할당
			 rs = pstmt.executeQuery();
			 //7. sql 결과값 반환 
			//COUNT함수를 무조건 한 row행을 반환하므로, next()를 실행
			rs.next();
//			int result = rs.getInt(1);
//			//8. result가 있으면 flag를 true로 변환
//			if (result == 1)
//				flag = true;
			
			// 위 코드를 삼항 연산자로 변경해본다.
			// (조건식 ? 조건식의 true결과 : 조건식의 false결과)
			flag = (rs.getInt(1) == 1 ? true : false);
			
		 } finally {
			 //9. close
			 closeAll(rs, pstmt, con);
		 }
		 
		 return flag;
	 } //isExistProduct
	
	 //registerProduct -- table에 새로운 데이터 추가(PK 중복 시 불가 조건있음)
	 public void registerProduct(ProductDTO dto) throws SQLException {
		 Connection con = null;
		 PreparedStatement pstmt = null;
		 
		 try {
			 //2. Connection
			 con = DriverManager.getConnection(dbUrl, user, password);
			 
			 //3. sql 정의
			 String sql = "INSERT INTO product(id, name, maker, price)"+
					 "VALUES (?, ?, ?, ?)";
			 
			 //4. preparedStatement 정의
			 pstmt = con.prepareStatement(sql);
			 
			 //5. sql 완성 (? 조건 채워주기)- setString()/SetInt()
			 pstmt.setString(1, dto.getId());
			 pstmt.setString(2, dto.getName());
			 pstmt.setString(3, dto.getMaker());
			 pstmt.setInt(4, dto.getPrice());
			 
			 //6. sql 실행 -- executeUpdate()
			pstmt.executeUpdate();
		
		 } finally {
				 //7. close
				 closeAll(pstmt, con);
			 }
	 }//registerProduct

	 //getProductListByPriceOrderByDesc -- 상품 정보 조회
	 public ArrayList<ProductDTO> getProductListByPriceOrderByDesc(int lowPrice,
			 int highPrice) throws SQLException {
		 Connection con = null;
		 PreparedStatement pstmt = null;
		 ResultSet rs = null;
		 ArrayList<ProductDTO> list = new ArrayList<ProductDTO>();
		 
		 try {
			 con = DriverManager.getConnection(dbUrl, user, password);
			 
			 String sql = "SELECT name, price FROM product" +
					 	" WHERE price BETWEEN ? AND ?" +
					 	" ORDER BY price DESC"; 
			 		 
			 pstmt = con.prepareStatement(sql);
			 
			 pstmt.setInt(1, lowPrice);
			 pstmt.setInt(2, highPrice);
			 
			 rs = pstmt.executeQuery();
			 
			 while (rs.next()) {
				 ProductDTO dto = new ProductDTO();
				 dto.setName(rs.getString(1));
				 dto.setPrice(rs.getInt(2));
				 list.add(dto);
				 //list.add(new ProductDTO(rs.getString("name"), rs.getInt("price")));
			 }
		 } finally {
			 closeAll(rs, pstmt, con);
		 }
		 
		 return list;
	 }

	
	 //productListDiscountPrice -- 상품 할인가로 조회
	 public ArrayList<ProductDTO> productListDiscountPrice(int percent) throws SQLException {
		 Connection con = null;
		 PreparedStatement pstmt = null;
		 ResultSet rs = null;
		 ArrayList<ProductDTO> list = new ArrayList<ProductDTO>();
		 
		 try {
			 //SQL문이 길기 때문에 StringBulider를 활용해보자
			 StringBuilder sb = new StringBuilder();
			 sb.append("SELECT name, price-(price * ? /100) AS 할인가 ");
			 sb.append("FROM product ");
			 sb.append("ORDER BY price ASC");
			 		
			 con = DriverManager.getConnection(dbUrl, user, password);
			
			 pstmt = con.prepareStatement(sb.toString());
			 
			 pstmt.setInt(1, percent);
			 
			 rs = pstmt.executeQuery();
			 
			 while (rs.next()) {
				 ProductDTO dto = new ProductDTO();
				 dto.setName(rs.getString("name")); //rs.getString(1)
				 dto.setPrice(rs.getInt("할인가")); //rs.getInt(2)
				 list.add(dto);
			 }
		 } finally {
			 closeAll(rs, pstmt, con);
		 }
		 
		return list;
	} //productListDiscountPrice
}

---

DTO ( Data Transfer Object ) /  VO ( Value Object )

: 정보를 저장하고 (계층간/ 원격으로)전송하기 위한 class

 

/ProductDTO.java

package model;

public class ProductDTO {
	//instance variable
	private String id;
	private String name;
	private String maker;
	private int price;
	

	//Constructor 1
	public ProductDTO(String id, String name, String maker, int price) {
		this.id = id;
		this.name = name;
		this.maker = maker;
		this.price = price;
	}
	
	//Constructor 2
	public ProductDTO(String name, int price) {
		super();
		this.name = name;
		this.price = price;
	}

	//Constructor 3
	public ProductDTO() {
		super();
	}
	
	//toString
	@Override
	public String toString() {
		return "ProductDTO [id=" + id + ", name=" +
				name + ", maker=" + maker + ", price=" + price + "]";
	}
	
	public String getId() {
		return id;
	}
	public void setId(String id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getMaker() {
		return maker;
	}
	public void setMaker(String maker) {
		this.maker = maker;
	}
	public int getPrice() {
		return price;
	}
	public void setPrice(int price) {
		this.price = price;
	}
}