대덕인재개발원/대덕인재개발원_Java
230803 자바 강의
Unlimited00
2023. 8. 3. 20:26
(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)정보]
