[JDBC] CRUD Exercise

🧩 TBL_CATEGORY 테이블을 대상으로 한 CRUD 기능 만들기

💁‍ Java와 Oracle SQL을 JDBC로 연동하여 TBL_CATEGORY 테이블을 조회(SELECT), 테이블에 값을 삽입(INSERT), 수정(UPDATE), 삭제(DELETE)할 수 있는 CRUD 기능을 만들어주세요!

---

🙋‍ 잠깐 ! 코드 작성 전, 기본 Setting 확인 🔥중요🔥

• config/jdbc-config.properties
• lib/ojdbc8.jar (Classpath add 포함)
• mapper/category-query.xml
• src/com/greedy/common/JDBCTemplate.java

---

👉 기반 클래스 및 파일

◼ JDBCTemplate Class

public class JDBCTemplate {
	
    /* Connection을 return 시켜주는 메소드 (DB와 연동할 때마다 호출 예정) */
	public static Connection getConnection() {
		
	Connection conn = null;
	Properties prop = new Properties();
		
	try {
		prop.load(new FileReader("config/jdbc-config.properties"));
		
        // properties 파일에서 설정 정보를 읽어오는 코드
		String driver = prop.getProperty("driver");
		String url = prop.getProperty("url");
			
		Class.forName(driver);
		
        // url과 prop만 전달하는 getConnection() 메소드 호출
		conn = DriverManager.getConnection(url, prop);
		>>> Properties안에 있는 값을 전달하기 때문에,
        >>> user와 password를 위에서 따로 입력하지 않아도 OK
			
	} catch (IOException e) {
		e.printStackTrace();
	} catch (ClassNotFoundException e) {
		e.printStackTrace();
	} catch (SQLException e) {
		e.printStackTrace();
	} 
		
	return conn;
    >>> 생성된 Connection을 값으로 반환
}
	
	/* Connection을 close할 메소드 */
    public static void close(Connection conn) {
		try {
			if(conn != null && !conn.isClosed()) { // : conn이 null이 아니고 닫히지 않았을 경우,
				conn.close(); // : 닫는다
				
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/* Statement를 close할 메소드 */
	public static void close(Statement stmt) {
		try {
			if(stmt != null && !stmt.isClosed()) {
				stmt.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/* ResultSet을 close할 메소드 */
	public static void close(ResultSet rset) {
		try {
			if(rset != null && !rset.isClosed()) {
				rset.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

◼ CategoryDTO Class

public class CategoryDTO {

	>>> 해당 테이블의 컬럼명과 데이터타입을 기반으로 하여 작성
	/* 필드 */
	private int categoryCode;
	private String categoryName;
	private int refCategoryCode;
	
	/* 생성자 */
	public CategoryDTO() {}
	
	public CategoryDTO(int categoryCode, String categoryName, int refCategoryCode) {
		super();
		this.categoryCode = categoryCode;
		this.categoryName = categoryName;
		this.refCategoryCode = refCategoryCode;
	}

	/* getter & setter */
	public int getCategoryCode() {
		return categoryCode;
	}

	public void setCategoryCode(int categoryCode) {
		this.categoryCode = categoryCode;
	}

	public String getCategoryName() {
		return categoryName;
	}

	public void setCategoryName(String categoryName) {
		this.categoryName = categoryName;
	}

	public int getRefCategoryCode() {
		return refCategoryCode;
	}

	public void setRefCategoryCode(int refCategoryCode) {
		this.refCategoryCode = refCategoryCode;
	}

	/* toString */
	@Override
	public String toString() {
		return "CategoryDTO [categoryCode=" + categoryCode + ", categoryName=" + categoryName + ", refCategoryCode="
				+ refCategoryCode + "]";
	}
}

◼ category-query.xml File

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
	<!-- TBL_CATEGORY 테이블을 모두 조회하는 쿼리 -->
	<entry key="selectCategory">
		SELECT
				TC.*
			FROM TBL_CATEGORY TC
	</entry>

	<!-- 입력받은 카테고리 코드의 행의 모두 조회하는 쿼리 -->
	<entry key="selectPickedCategory">
		SELECT
				TC.*
			FROM TBL_CATEGORY TC
			WHERE CATEGORY_CODE = ?
	
	</entry>


	<!--  TBL_CATEGORY 테이블에 카테고리 1개를 삽입하는 쿼리 -->
	<entry key="insertCategory">
		INSERT
			INTO TBL_CATEGORY
		(
			CATEGORY_CODE
		,	CATEGORY_NAME
		, 	REF_CATEGORY_CODE
		)
		VALUES
		(
			SEQ_CATEGORY_CODE.NEXTVAL <!-- 시퀀스로 CATEGORY_CODE 컬럼 값 계속 생성 -->
		,	?
		,	?	
		)
	</entry>
	
	<!-- 카테고리 코드를 입력 받아 카테고리명, 상위 카테고리 코드 변경하는 쿼리 -->
	<entry key="updateCategory">
		UPDATE
				TBL_CATEGORY
			SET CATEGORY_NAME = ?
			,	REF_CATEGORY_CODE = ?
			WHERE CATEGORY_CODE = ? <!-- 어떤 카테고리 코드를 가지는 것에 대해서만 CATEGORY_NAME과 REF_CATEGORY_CODE를 업뎃하겠다. -->	
	</entry>
	
	<!-- 카테고리 코드를 입력 받아 해당 행 삭제하는 쿼리 -->
	<entry key="deleteCategory">
		DELETE
			FROM TBL_CATEGORY
			WHERE CATEGORY_CODE = ?
	</entry>
</properties>

🙋‍ 잠깐 ! category-query.xml 파일을 만들기 위한 코드 작성법

• 먼저 프로젝트 하위에 mapper 소스폴더 생성
• 아래의 코드 실행 후, mapper 소스폴더 Refresh하면 파일 생성 완료

Properties prop = new Properties();
	
		prop.setProperty("keyString", "valueString");
		
		try {
			prop.storeToXML(new FileOutputStream("mapper/category-query.xml"), "");
		} catch (IOException e) {
			e.printStackTrace();
		}

👉 [SELECT] TBL_CATEGORY 테이블 전체 데이터 조회

List<CategoryDTO> 타입으로 처리

Connection conn = getConnection();
PreparedStatement pstmt = null;
ResultSet rset = null;
		
CategoryDTO category = null;
List<CategoryDTO> cateList = null;
		
try {
	Properties prop = new Properties();
	prop.loadFromXML(new FileInputStream("mapper/category-query.xml"));
			
	String query = prop.getProperty("selectCategory");
			
	pstmt = conn.prepareStatement(query);
			
	rset = pstmt.executeQuery();
			
	cateList = new ArrayList<>();
			
	while(rset.next()) {
		category = new CategoryDTO();
				
		category.setCategoryCode(rset.getInt("CATEGORY_CODE"));
		category.setCategoryName(rset.getString("CATEGORY_NAME"));
		category.setRefCategoryCode(rset.getInt("REF_CATEGORY_CODE"));
				
		cateList.add(category);
	}
			
} catch (IOException e) {
	e.printStackTrace();
} catch (SQLException e) {
	e.printStackTrace();
} finally {
	close(rset);
	close(pstmt);
	close(conn);
}
		
for(CategoryDTO cate : cateList) {
	System.out.println(cate);
}

💻 Mini Console

CategoryDTO [categoryCode=1, categoryName=식사, refCategoryCode=0]
CategoryDTO [categoryCode=2, categoryName=음료, refCategoryCode=0]
CategoryDTO [categoryCode=3, categoryName=디저트, refCategoryCode=0]
CategoryDTO [categoryCode=4, categoryName=한식, refCategoryCode=1]
CategoryDTO [categoryCode=5, categoryName=중식, refCategoryCode=1]
CategoryDTO [categoryCode=6, categoryName=일식, refCategoryCode=1]
CategoryDTO [categoryCode=7, categoryName=퓨전, refCategoryCode=1]
CategoryDTO [categoryCode=8, categoryName=커피, refCategoryCode=2]
CategoryDTO [categoryCode=9, categoryName=쥬스, refCategoryCode=2]
CategoryDTO [categoryCode=10, categoryName=기타, refCategoryCode=2]
CategoryDTO [categoryCode=11, categoryName=동양, refCategoryCode=3]
CategoryDTO [categoryCode=12, categoryName=서양, refCategoryCode=3]

---

👉 [SELECT] Scanner로 입력 받은 Category code에 해당하는 행 데이터 조회

CategoryDTO 타입으로 처리

Scanner sc = new Scanner(System.in);
System.out.println("---------- 카테고리 조회 ----------");
System.out.print("조회할 행의 카테고리 코드를 입력해주세요 :) \n => ");
String code = sc.nextLine();
		
Connection conn = getConnection();
PreparedStatement pstmt = null;
ResultSet rset = null;
		
CategoryDTO category = null;
		
try {
	Properties prop = new Properties();
	prop.loadFromXML(new FileInputStream("mapper/category-query.xml"));
			
	String query = prop.getProperty("selectPickedCategory");
			
	pstmt = conn.prepareStatement(query);
	pstmt.setString(1, code);
			
	rset = pstmt.executeQuery();
			
	while(rset.next()) {
		category = new CategoryDTO();
				
		category.setCategoryCode(rset.getInt("CATEGORY_CODE"));
		category.setCategoryName(rset.getString("CATEGORY_NAME"));
		category.setRefCategoryCode(rset.getInt("REF_CATEGORY_CODE"));
	}

} catch (IOException e) {
	e.printStackTrace();
} catch (SQLException e) {
	e.printStackTrace();
} finally {
	close(rset);
	close(pstmt);
	close(conn);
}
		
System.out.println(category);	

💻 Mini Console

---------- 카테고리 조회 ----------
조회할 행의 카테고리 코드를 입력해주세요 :) 
 => 3
CategoryDTO [categoryCode=3, categoryName=디저트, refCategoryCode=0]

---

👉 [INSERT] 사용자 입력 값으로 Category 추가

Scanner sc = new Scanner(System.in);
System.out.println("---------- 카테고리 추가 ----------");
System.out.print("카테고리 이름을 입력해주세요 :) \n => ");
String cateName = sc.nextLine();
System.out.print("상위 카테고리 코드를 입력해주세요 :) \n => ");
int refCateCode = sc.nextInt();
		
CategoryDTO newCategory = new CategoryDTO();
newCategory.setCategoryName(cateName);
newCategory.setRefCategoryCode(refCateCode);
		
Connection conn = getConnection();
PreparedStatement pstmt = null;
		
int result = 0;
		
try {
	Properties prop = new Properties();
	prop.loadFromXML(new FileInputStream("mapper/category-query.xml"));
	String query = prop.getProperty("insertCategory");
					
	pstmt = conn.prepareStatement(query);
	pstmt.setString(1, newCategory.getCategoryName());
	pstmt.setInt(2, newCategory.getRefCategoryCode());
			
	result = pstmt.executeUpdate();
		
} catch (IOException e) {
	e.printStackTrace();
} catch (SQLException e) {
	e.printStackTrace();
} finally {
	close(pstmt);
	close(conn);
}
		
if(result > 0) {
	System.out.println("메뉴 등록 완료 ! :)");			
} else {
	System.out.println("메뉴 등록 실패 :(");
}

💻 Mini Console

---------- 카테고리 추가 ----------
카테고리 이름을 입력해주세요 :) 
 => 비건
상위 카테고리 코드를 입력해주세요 :) 
 => 1
메뉴 등록 완료 ! :)

💻 TBL_CATEGORY TABLE

---

👉 [UPDATE] 사용자 입력 값으로 Category 수정

Scanner sc = new Scanner(System.in);
System.out.println("---------- 카테고리 수정 ----------");
System.out.print("변경할 카테고리 번호를 입력해주세요 :) \n => ");
int cateCode = sc.nextInt();
System.out.print("새로 등록할 카테고리 이름을 입력해주세요 :) \n => ");
sc.nextLine();
String cateName = sc.nextLine();
System.out.print("새로 등록할 상위 카테고리 번호를 입력해주세요 :) \n => ");
int refCateCode = sc.nextInt();
		
CategoryDTO newCategory = new CategoryDTO();
newCategory.setCategoryCode(cateCode);
newCategory.setCategoryName(cateName);
newCategory.setRefCategoryCode(refCateCode);
		
Connection conn = getConnection();
PreparedStatement pstmt = null;
		
int result = 0;
		
		
try {
	Properties prop = new Properties();
	prop.loadFromXML(new FileInputStream("mapper/category-query.xml"));
			
	String query = prop.getProperty("updateCategory");
			
	pstmt = conn.prepareStatement(query);
	pstmt.setString(1, newCategory.getCategoryName());
	pstmt.setInt(2, newCategory.getRefCategoryCode());
	pstmt.setInt(3, newCategory.getCategoryCode());
			
	result = pstmt.executeUpdate();
			
} catch (IOException e) {
	e.printStackTrace();
} catch (SQLException e) {
	e.printStackTrace();
} finally {
	close(pstmt);
	close(conn);
}
		
if(result > 0) {
	System.out.println("카테고리 수정 완료 ! :)");			
} else {
	System.out.println("카테고리 수정 실패 :(");
}

💻 Mini Console

---------- 카테고리 수정 ----------
변경할 카테고리 번호를 입력해주세요 :) 
 => 13
새로 등록할 카테고리 이름을 입력해주세요 :) 
 => 베지테리안
새로 등록할 상위 카테고리 번호를 입력해주세요 :) 
 => 10
카테고리 수정 완료 ! :)

💻 TBL_CATEGORY TABLE

---

👉 [DELETE] 사용자 입력 값으로 Category 삭제

Scanner sc = new Scanner(System.in);
System.out.println("---------- 카테고리 삭제 ----------");
System.out.print("삭제할 카테고리 번호를 입력해주세요 :) \n => ");
int cateCode = sc.nextInt();
		
Connection conn = getConnection();
PreparedStatement pstmt = null;
		
int result = 0;
		
Properties prop = new Properties();
try {
	prop.loadFromXML(new FileInputStream("mapper/category-query.xml"));
			
	String query = prop.getProperty("deleteCategory");
			
	pstmt = conn.prepareStatement(query);
	pstmt.setInt(1, cateCode);
			
	result = pstmt.executeUpdate();
			
} catch (IOException e) {
	e.printStackTrace();
} catch (SQLException e) {
	e.printStackTrace();
} finally {
	close(pstmt);
	close(conn);
}
		
if(result > 0) {
	System.out.println("카테고리 삭제 완료 ! :)");
} else {
	System.out.println("카테고리 삭제 실패 :(");
}

💻 Mini Console

---------- 카테고리 삭제 ----------
삭제할 카테고리 번호를 입력해주세요 :) 
 => 13
카테고리 삭제 완료 ! :)

💻 TBL_CATEGORY TABLE

---