230803 자바 강의

(1) 로그인 -> sql, (id, pw). 정당한 사용자를 판별해야 함.

selectOne( );

동적 쿼리를 필요로 함(런타임에서 데이터를 입력받기 때문)

---

SELECT PROD_ID, PROD_NAME, PROD_PRICE, PROD_PROPERSTOCK FROM PROD;

CREATE TABLE T_PROD AS
SELECT PROD_ID, PROD_NAME, PROD_PRICE, PROD_PROPERSTOCK
  FROM PROD; -- 서브쿼리를 통한 테이블 생성

SELECT * FROM T_PROD;

COMMIT;

---

[DBTest.java]

package dbprogramming;

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

public class DBTest {

	private String url = "jdbc:oracle:thin:@localhost:1521:xe";
	private String user = "pc_21";
	private String password = "java";
	
	private Connection conn = null;
	private Statement stmt = null; // 정적 쿼리
	private PreparedStatement pstmt = null; // 동적 쿼리
 	private ResultSet rs = null;
 	
 	private CallableStatement cs = null;// 프로시저 실행결과 반환
	
	public void selectList(String sql) {
		try {
			conn = DriverManager.getConnection(url, user, password);
			
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql); // 뷰를 보관하고 있는 객체
			
			while(rs.next()) {
				String pid = rs.getString("PROD_ID");
				String pname = rs.getString("PROD_NAME");
				StringBuilder pleng = new StringBuilder(pname);
				for(int i=pname.length(); i<pname.length()+2; i++) {
					pleng.append("\0");
				}
				String name = pleng.toString();
				int price = rs.getInt("PROD_PRICE");
				int pstock = rs.getInt("PROD_PROPERSTOCK");
				
				System.out.printf("%-12s %-20s %10d %5d\n", pid, name, price, pstock);
			}
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			if(rs != null) try {rs.close();} catch(Exception e) {}
			if(stmt != null) try {stmt.close();} catch(Exception e) {}
			if(pstmt != null) try {pstmt.close();} catch(Exception e) {}
			if(conn != null) try {conn.close();} catch(Exception e) {}
		}
		new DBTestExample().main(null);
	}

	public void selectOne(String sql, String pd) {
		try {
			conn = DriverManager.getConnection(url, user, password);
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, pd);
			rs = pstmt.executeQuery();
			
			while(rs.next()) {
				String pid = rs.getString("PROD_ID");
				String pname = rs.getString("PROD_NAME");
				StringBuilder pleng = new StringBuilder(pname);
				for(int i=pname.length(); i<pname.length()+2; i++) {
					pleng.append("\0");
				}
				String name = pleng.toString();
				int price = rs.getInt("PROD_PRICE");
				int pstock = rs.getInt("PROD_PROPERSTOCK");
				
				System.out.printf("%-12s %-20s %10d %5d\n", pid, name, price, pstock);
			}
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			if(rs != null) try {rs.close();} catch(Exception e) {}
			if(stmt != null) try {stmt.close();} catch(Exception e) {}
			if(pstmt != null) try {pstmt.close();} catch(Exception e) {}
			if(conn != null) try {conn.close();} catch(Exception e) {}
		}
		new DBTestExample().main(null);
	}
	
	/*
	 * insert into t_prod
	 *      values (?, ?, ?, ?); 오라클은 index 기준이 1
	 */
	public int update(String sql, List<Object> param) {
		
		int result = 0;
		try {
			conn = DriverManager.getConnection(url, user, password);
			
			pstmt = conn.prepareStatement(sql);
			for(int i=0; i<param.size(); i++) {
				pstmt.setObject(i+1, param.get(i)); // 오라클은 index 기준이 1. 그래서 i+1.
			}
			result = pstmt.executeUpdate();
			conn.commit();
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			if(rs != null) try {rs.close();} catch(Exception e) {}
			if(stmt != null) try {stmt.close();} catch(Exception e) {}
			if(pstmt != null) try {pstmt.close();} catch(Exception e) {}
			if(conn != null) try {conn.close();} catch(Exception e) {}
		}
		return result;
		
	}

}

[DBTestExample.java]

package dbprogramming;

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

public class DBTestExample {

	public static void main(String[] args) {
		
		DBTest dt = new DBTest();
		
		StringBuilder sb = new StringBuilder();
		String sql = null;
		
		List<Object> param = null;
		int flag = 0;
		
		String pid = null;
		
		Scanner sc = new Scanner(System.in);
		
		System.out.println("[[데이터베이스 관리]]");
		System.out.println(" 1. 전체검색"); // 대전에 있는 사람의 여러 명 자료 검색
		System.out.println(" 2. 단일검색"); // 로그인 정보 가져오기
		System.out.println(" 3. 신규자료입력");
		System.out.println(" 4. 자료수정");
		System.out.println(" 5. 자료삭제");
		System.out.println(" 9. 종료");
		System.out.println("------------------");
		System.out.print("작업번호 >> ");
		
		int choice = Integer.parseInt(sc.nextLine());
		
		switch(choice) {
		case 1 :
			sb.append(" SELECT PROD_ID, PROD_NAME, PROD_PRICE, PROD_PROPERSTOCK ");
			sb.append(" FROM T_PROD ");
//			sb.append(" WHERE PROD_PRICE >= 1000000 ");
			sb.append(" ORDER BY PROD_ID ASC ");
			sql = sb.toString();
			dt.selectList(sql);
			break;
		case 2 :
			sb.append(" SELECT PROD_ID, PROD_NAME, PROD_PRICE, PROD_PROPERSTOCK ");
			sb.append(" FROM T_PROD ");
			sb.append(" WHERE PROD_ID = ? ");
			sb.append(" ORDER BY PROD_ID ASC ");
			sql = sb.toString();
			System.out.print("조회할 아이디 : ");
			String pd = sc.nextLine();
			dt.selectOne(sql, pd);
			break;
		case 3 : // 신규입력(insert into TBLNM values ~)
			param = new ArrayList<Object>();
			System.out.print("상품코드 : ");
			pid = sc.nextLine();
			param.add(pid);
			System.out.print("상품명 : ");
			String pname = sc.nextLine();
			param.add(pname);
			System.out.print("가격 : ");
			int price = Integer.parseInt(sc.nextLine());
			param.add(price);
			System.out.print("적정재고 : ");
			int stock = Integer.parseInt(sc.nextLine());
			param.add(stock);
			
			sql = " INSERT INTO T_PROD VALUES(?, ?, ?, ?) ";
			flag = dt.update(sql, param);
			if(flag != 0) {
				System.out.println("자료 입력 성공");
			}else {
				System.out.println("자료 입력 실패");
			}
			main(null);
		case 4 : // 자료수정(update TBLNM set ~ where)
			param = new ArrayList<Object>();
			sql = " UPDATE T_PROD SET ";
			
			System.out.print("변경할 상품번호 : ");
			pid = sc.nextLine();
			param.add(pid);
			
			String yesOrNot = "";
			
			System.out.print("상품명 변경? (y/n) ");
			yesOrNot = sc.nextLine();
			while(yesOrNot.equalsIgnoreCase("y")) {
				System.out.print("상품명 : ");
				String p1 = sc.nextLine();
				sql += "PROD_NAME = '"+ p1 + "', ";
				break;
			}
			
			System.out.print("상품가격 변경? (y/n) ");
			yesOrNot = sc.nextLine();
			while(yesOrNot.equalsIgnoreCase("y")) {
				System.out.print("상품가격 : ");
				int p2 = Integer.parseInt(sc.nextLine());
				sql += "PROD_PRICE = "+ p2 + ", ";
				break;
			}
			
			System.out.print("적정재고 변경? (y/n) ");
			yesOrNot = sc.nextLine();
			while(yesOrNot.equalsIgnoreCase("y")) {
				System.out.print("적정재고 : ");
				int p3 = Integer.parseInt(sc.nextLine());
				sql += "PROD_PROPERSTOCK = "+ p3 + ", ";
				break;
			}
			
			int sqllen = sql.length();
			sql = sql.substring(0, sqllen-2); // (endindex-1)-1[,]
			sql += " WHERE PROD_id = ? ";
			
//			System.out.println(sql);
			
			flag = dt.update(sql, param);
			if(flag != 0) {
				System.out.println("자료 갱신 성공");
			}else {
				System.out.println("자료 갱신 실패");
			}
			
			main(null);
		case 5 : // 자료삭제(delete from ~ [where])
			param = new ArrayList<Object>();
			System.out.print("상품코드 : ");
			pid = sc.nextLine();
			param.add(pid);
			
			sql = " DELETE FROM T_PROD ";
			sql += "WHERE PROD_ID = ? ";
			
			flag = dt.update(sql, param);
			if(flag != 0) {
				System.out.println("자료 삭제 성공");
			}else {
				System.out.println("자료 삭제 실패");
			}
			main(null);
		case 9 :
			System.out.println("프로그램을 종료합니다...");
			System.exit(0);
		default : 
			System.out.println("잘못 입력하셨습니다...");
			main(null);
		}
		
	}

}

---

[데이타베이스의 메타데이터(metadata)정보]