22-03-16(수) 033일차 [Java, Oracle] 자바로 DB 연동하여 SELECT, INSERT, UPDATE, DELETE

국비지원교육 (22.01-22.07)/강의노트

22-03-16(수) 033일차 [Java, Oracle] 자바로 DB 연동하여 SELECT, INSERT, UPDATE, DELETE

옌炎 2022. 5. 11. 11:22

728x90

수업내용

1교시 (09:30-10:20)

지난 시간 복습
PRIMARY KEY는 NOT NULL & UNIQUE / INDEX 생성

SQL

DQL
SELECT

DML
INSERT UPDATE DELETE
1. Transaction 처리를 해야 한다.
2. COMMIT, ROLLBACK 둘 중 하나는 꼭 해야한다.

1. 메모리에 적재된다.
2. 트랜잭션 처리를 해야 한다.
ROLLBACK : 메모리에 적재된 내용을 취소한다.
COMMIT : 파일에 적재된다 : 파일에 적재된 것만 다른 외부 프로그램에서 조회가 가능하다.
DDL
CREATE ALTER DROP

TCL Transaction Control Language 트랜잭션 : 두 곳의 입력/출력이 만족이 되어야 한다.
현재 진행되는 행위가 완료가 되는 것
완료: 1. 취소, 2. 끝내기
COMMIT ROLLBACK

1. 열면 닫아야 한다.
2. DML 문장 처리

DCL
GRANT REVOKE

2교시 (10:30-11:20)

OracleTest_9

package a.b.c.oracle;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Scanner;

import a.b.c.common.ChabunUtil;
import a.b.c.common.OracleConnProperty;
import a.b.c.oracle.vo.EmpVO;

public class OracleTest_9 {
	
	public int t4Insert(String t1, String t2, String t3) {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		int nCnt = 0;
		
		try {
			conn = OracleConnProperty.getConnection();
			
			String sql = "INSERT INTO TEST_T4 (T1, T2, T3, T4, T5) VALUES (?, ?, ?, 'Y', SYSDATE)";
			pstmt = conn.prepareStatement(sql);
			System.out.println("입력하기 >>> : \n" + sql);
			
			pstmt.clearParameters();
			pstmt.setString(1, t1);
			pstmt.setString(2, t2);
			pstmt.setString(3, t3);
			
			// executeQuery()'s output: ResultSet
			//		WHEN USE "SELECT"
			// executeUpdate()'s output : int
			// 		WHEN USE DML(UPDATE INSERT DELETE)
			nCnt = pstmt.executeUpdate();
			
		} catch (Exception e) {
			System.out.println("에러가 >>> : " + e.getMessage());
		}
		
		return nCnt;
	}
	
	public static void main(String[] args) throws ParseException {
		// TODO Auto-generated method stub
		
//		String t1 = "7";
		String t1 = ChabunUtil.getTestChabun("n");
		String t2 = "윤요섭";
		String t3 = "27";
		String t4 = "";
		String t5 = "";
		
		OracleTest_9 ot9 = new OracleTest_9();
		int nCnt = ot9.t4Insert(t1, t2, t3);
		
		if (nCnt == 1) {
			System.out.println(nCnt + " 건 입력 성공");
		} else {
			System.out.println("입력 실패.");
		}
	}
}

OracleTest_10

package a.b.c.oracle;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Scanner;

import a.b.c.common.OracleConnProperty;
import a.b.c.oracle.vo.EmpVO;

public class OracleTest_10 {

	public int t4Update(String t1, String t2) {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		int nCnt = 0;
		
		try {
			
			conn = OracleConnProperty.getConnection();
			
			String sql = "UPDATE TEST_T4 SET T2 = ?, T5 = SYSDATE WHERE T4 = 'Y' AND T1 = ?";
			pstmt = conn.prepareStatement(sql);
			System.out.println("입력하기 >>> : \n" + sql);
			
			pstmt.clearParameters();
			pstmt.setString(1, t2);
			pstmt.setString(2,  t1);
			
			nCnt = pstmt.executeUpdate();
			
		} catch (Exception e) {
			System.out.println("에러가 >>> : " + e.getMessage());
		}
		
		return nCnt;
	}
	
	public static void main(String[] args) {
		// TODO Auto-generated method stub

		String t1 = "7";
		String t2 = "YYS";
		String t3 = "";
		String t4 = "";
		String t5 = "";
		
		OracleTest_10 ot10 = new OracleTest_10();
		int nCnt = ot10.t4Update(t1, t2);
		
		if (nCnt == 1) {
			System.out.println(nCnt + " 건 수정 성공");
		} else {
			System.out.println("수정 실패. ");
		}
	}
}

OracleTest_11

package a.b.c.oracle;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Scanner;

import a.b.c.common.OracleConnProperty;
import a.b.c.oracle.vo.EmpVO;

public class OracleTest_11 {

	public int t4Delete(String t1) {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		int nCnt = 0;
		
		try {
			
			conn = OracleConnProperty.getConnection();
			
			String sql = "UPDATE TEST_T4 SET T4 = 'N', T5 = SYSDATE WHERE T4 = 'Y' AND T1 = ? ";
			pstmt = conn.prepareStatement(sql);
			System.out.println("입력하기 >>> \n" + sql);
			
			pstmt.clearParameters();
			pstmt.setNString(1, t1);
			
			nCnt = pstmt.executeUpdate();
			
		} catch (Exception e) {
			System.out.println("에러가 >>> : " + e.getMessage());
		}
		
		return nCnt;
	}
	
	public static void main(String[] args) {
		// TODO Auto-generated method stub

		String t1 = "7";
		String t2 = "";
		String t3 = "";
		String t4 = "";
		String t5 = "";
		
		OracleTest_11 ot11 = new OracleTest_11();
		int nCnt = ot11.t4Delete(t1);
		
		if (nCnt == 1) {
			System.out.println(nCnt + " 건 삭제 성공");
		} else {
			System.out.println("삭제 실패. ");
		}
	}
}

3교시 (11:30-12:20)

DateFormatUtil

package a.b.c.common;

import java.text.SimpleDateFormat;
import java.util.Date;

public abstract class DateFormatUtil {

	public static String ymdFormat() {
		return new SimpleDateFormat("yyyyMMdd").format(new Date());
	}
	
	public static String ymFormat() {
		return new SimpleDateFormat("yyyyMM").format(new Date());
	}
	
	public static String yFormat() {
		return new SimpleDateFormat("yyyy").format(new Date());
	}
	
	public static String ymdFormats(String ymdFlag) {
		
		String y = "";
		
		if("D".equals(ymdFlag.toUpperCase())) {
			y = DateFormatUtil.ymdFormat();
		}
		if ("M".equals(ymdFlag.toUpperCase())) {
			y = DateFormatUtil.ymFormat();
		}
		if ("Y".equals(ymdFlag.toUpperCase())) {
			y = DateFormatUtil.yFormat();
		}
		if ("N".equals(ymdFlag.toUpperCase())) {
			y = "";
		}
		
		return y;
	}
	
	public static void main(String[] args) {
		// TODO Auto-generated method stub

	}

}

ChabunQuery

package a.b.c.common;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public abstract class ChabunQuery {
	
	public static final short CHABUN_QUERY_TEST = 0;
	
	public static String chabun_query[] = {
		"SELECT NVL(MAX(SUBSTR(A.T1, -4)), 0) + 1 COMM_NUM FROM TEST_T4 A"	
	};
	
	public static String getTestChabunQuery() {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rsRs = null;
		String commNum = "";
		
		try {
			
			conn = OracleConnProperty.getConnection();
			
			String sql = ChabunQuery.chabun_query[CHABUN_QUERY_TEST];
			pstmt = conn.prepareStatement(sql);
			System.out.println("채번 쿼리 >>> : \n" + sql);
			
			rsRs = pstmt.executeQuery();
			
			if (rsRs != null) {
				
				while (rsRs.next()) {
					commNum = rsRs.getString("COMM_NUM");
				}
				
			} else {
				System.out.println("ChabunQuery :: 채번 검색 결과 없음 >>> : " + rsRs);
			}
			
		} catch (Exception e) {
			System.out.println("ChabunQuery :: 채번 검색 중 에러가 >>> : " + e.getMessage());
		}
		
		return commNum;
	}

	public static void main(String[] args) {
		// TODO Auto-generated method stub

	}

}

ChabunUtil

package a.b.c.common;

public abstract class ChabunUtil {
	
	public static final String BIZ_GUBUN_TEST = "T";
	
	// type : 
		// D : YYYYMMDD 
		// M : YYYYMM 
		// Y : YYYY
		// N :
	public static String numPad(String t, String c) {
		for (int i = c.length(); i < 4; i++) {
			c = "0" + c;
		}
		
		String ymd = DateFormatUtil.ymdFormats(t);
		
		return ymd.concat(c);
	}
	
	public static String getTestChabun(String type) {
		String commNum = ChabunQuery.getTestChabunQuery();
		return BIZ_GUBUN_TEST.concat(ChabunUtil.numPad(type, commNum));
	}
	
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		
		System.out.println(">>> : " + ChabunUtil.getTestChabun("d"));
	}
}

4교시 (12:30-13:20)

OracleTest_9_1

package a.b.c.oracle;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Scanner;

import a.b.c.common.ChabunUtil;
import a.b.c.common.OracleConnProperty;
import a.b.c.emp.search.sql.EmpSearchQueryMap;
import a.b.c.emp.search.vo.EmpSearchVO;
import a.b.c.oracle.vo.EmpVO;

public class OracleTest_9_1 {
	
	public ArrayList<HashMap<String, String>> t4Select() {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rsRs = null;
		ArrayList <HashMap<String, String>> aList = null;
		HashMap<String, String> hp = null;
		
		try {
			
			conn = OracleConnProperty.getConnection();
			String sql = "SELECT T1, T2, T3, T4, TO_CHAR(T5, 'YYYYMMDD') AS T5 FROM TEST_T4 ORDER BY 1";
			pstmt = conn.prepareStatement(sql);
			System.out.println("전체 조회 >>> :  \n" + sql);
			rsRs = pstmt.executeQuery();
			
			if (rsRs != null) {
				
				aList = new ArrayList<HashMap<String, String>>();
				
				while (rsRs.next()) {
					
					hp = new HashMap<String, String>();
					hp.put("T1", rsRs.getString(1));
					hp.put("T2", rsRs.getString(2));
					hp.put("T3", rsRs.getString(3));
					hp.put("T4", rsRs.getString(4));
					hp.put("T5", rsRs.getString(5));
					
					aList.add(hp);
				}
				
			} else {
				System.out.println("OracleTest_9_1 :: 전체 조회 결과 없음 >>> : " + rsRs);
			}
			
		} catch (Exception e) {
			System.out.println("OracleTest_9_1 :: 전체 조회 중 에러가 >>> : " + e.getMessage());
		}
		
		return aList;
	}
	
	public boolean t4Insert(String t1, String t2, String t3) {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		int nCnt = 0;
		boolean bool = false;
		
		try {
			conn = OracleConnProperty.getConnection();
			String sql = "INSERT INTO TEST_T4 (T1, T2, T3, T4, T5) VALUES (?, ?, ?, 'Y', SYSDATE)";
			pstmt = conn.prepareStatement(sql);
			System.out.println("입력하기 >>> : \n" + sql);
			
			pstmt.clearParameters();
			pstmt.setString(1, t1);
			pstmt.setString(2, t2);
			pstmt.setString(3, t3);
			
			nCnt = pstmt.executeUpdate();
//			boolean b = !conn.getAutoCommit();
//			System.out.println("b >>> : " + b);
//			if (b) conn.commit();
			if (!conn.getAutoCommit()) conn.commit();
			
			System.out.println("nCnt >>> : " + nCnt + " 건 등록되었음");
			if (nCnt > 0) { bool = true; }
			
			OracleConnProperty.conClose(conn, pstmt);
			
		} catch (Exception e) {
			System.out.println("에러가 >>> : " + e.getMessage());
		} finally {
			OracleConnProperty.conClose(conn, pstmt);
		}
		
		return bool;
	}

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		
//		String t1 = "7";
		String t1 = ChabunUtil.getTestChabun("n");
		String t2 = "윤요섭";
		String t3 = "27";
		String t4 = "";
		String t5 = "";
		
		OracleTest_9_1 ot91 = new OracleTest_9_1();
		boolean bool = ot91.t4Insert(t1, t2, t3);
		
		if (bool) {
			System.out.println("입력 성공 >>> : " + bool);
			
			OracleTest_9_1 ot91_1 = new OracleTest_9_1();
			ArrayList<HashMap<String, String>> aList = ot91_1.t4Select();
			
			if (aList != null && aList.size() > 0) {
				
				for (int i=0; i < aList.size(); i++) {
					HashMap<String, String> _hp = aList.get(i);
					
					System.out.print(_hp.get("T1") + " : ");
					System.out.print(_hp.get("T2") + " : ");
					System.out.print(_hp.get("T3") + " : ");
					System.out.print(_hp.get("T4") + " : ");
					System.out.println(_hp.get("T5"));
				}
			}
		} else {
			System.out.println("입력 실패 >>> : ");
		}
	}
}

5교시 (14:30-15:20)

OrecleConnProperty

package a.b.c.common;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public abstract class OracleConnProperty {

	// DataSource 정보 : 데이터베이스 연결 정보
	
	//	C:\app\kosmo\product\11.2.0\dbhome_1\jdbc\lib\ojdbc6.jar
	//	C:\Program Files\Java\jdk1.8.0_202\jre\lib\ext\ojdbc6.jar
	public static final String JDBC_DRIVER = "oracle.jdbc.driver.OracleDriver";
	//	C:\app\kosmo\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora
	//	오라클 데이터베이스 프로그램 port 1521,  SID : orclKSY00
	public static final String JDBC_URL = "jdbc:oracle:thin:@localhost:1521:orclKSY00";
	public static final String JDBC_USER = "scott";
	public static final String JDBC_PASSWORD = "tiger";

	// Connection 연결하는 함수 만들기
	public static Connection getConnection() {
		
		Connection conn = null;
		
		try {
			Class.forName(JDBC_DRIVER);
			conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD);
			
		} catch (Exception e) {
			System.out.println("데이터베이스 연결에 문제가 있어요" + e.getMessage());
		}
	
		return conn;
	}
	
	public static void conClose(Connection conn, PreparedStatement pstmt, ResultSet rsRs) {
		try {
			if (rsRs != null) try { rsRs.close(); rsRs = null; } catch (Exception ex) {}
			if (pstmt != null) try { pstmt.close(); pstmt = null; } catch (Exception ex) {}
			if (conn != null) try { conn.close(); conn = null; } catch (Exception ex) {}
		} catch (Exception e2) {}
	}
	
	public static void conClose(Connection conn, PreparedStatement pstmt) {
		try {
			if (pstmt != null) try { pstmt.close(); pstmt = null; } catch (Exception ex) {}
			if (conn != null) try { conn.close(); conn = null; } catch (Exception ec) {}
		} catch (Exception e2) {}
	}
}

6교시 (15:30-16:20)

OracleTest_10_1

package a.b.c.oracle;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Scanner;

import a.b.c.common.OracleConnProperty;
import a.b.c.oracle.vo.EmpVO;

public class OracleTest_10_1 {
	
	public boolean t4Update(String t1, String t2) {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		int nCnt = 0;
		boolean bool = false;
		
		try {
			conn = OracleConnProperty.getConnection();
			String sql = "UPDATE TEST_T4 SET T2 = ?, T5 = SYSDATE WHERE T4 = 'Y' AND T1 = ?";
			pstmt = conn.prepareStatement(sql);
			System.out.println("입력하기 >>> : \n" + sql);
			
			pstmt.clearParameters();
			pstmt.setString(1, t2);
			pstmt.setString(2, t1);
			
			nCnt = pstmt.executeUpdate();
			if (!conn.getAutoCommit()) conn.commit();
			
			System.out.println("nCnt >>> : " + nCnt + " 건 수정되었음");
			if (nCnt > 0) { bool = true; }
			
//			OracleConnProperty.conClose(conn, pstmt);
			 
		} catch (Exception e) {
			System.out.println("에러가 >>> : " + e.getMessage());
		} finally {
			OracleConnProperty.conClose(conn, pstmt);
		}
		
		return bool;
	}

	public static void main(String[] args) {
		// TODO Auto-generated method stub

		String t1 = "7";
		String t2 = "YYS";
		String t3 = "";
		String t4 = "";
		String t5 = "";
		
		OracleTest_10_1 ot101 = new OracleTest_10_1();
		boolean bool = ot101.t4Update(t1, t2);
		
		if (bool) {
			System.out.println("수정 성공 >>> : " + bool);
		} else {
			System.out.println("수정 실패 >>> : " + bool);
		}
	}
}

7교시 (16:30-17:20)

OracleTest_11_1

package a.b.c.oracle;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Scanner;

import a.b.c.common.OracleConnProperty;
import a.b.c.oracle.vo.EmpVO;

public class OracleTest_11_1 {
	
	public boolean t4Delete(String t1) {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		int nCnt = 0;
		boolean bool = false;
		
		try {
			
			conn = OracleConnProperty.getConnection();
			String sql = "UPDATE TEST_T4 SET T4 = 'N', T5 = SYSDATE WHERE T4 = 'Y' AND T1 = ?";
			pstmt = conn.prepareStatement(sql);
			System.out.println("삭제하기 >>> : \n" + sql);
			
			pstmt.clearParameters();
			pstmt.setString(1, t1);
			
			nCnt = pstmt.executeUpdate();
			if (!conn.getAutoCommit()) conn.commit();
			
			System.out.println("nCnt >>> : " + nCnt + " 건 삭제되었음");
			if (nCnt > 0) { bool = true; }
			
		} catch (Exception e) {
			System.out.println("에러가 >>> : " + e.getMessage());
		} finally {
			OracleConnProperty.conClose(conn, pstmt);
		}
		return bool;
	}

	public static void main(String[] args) {
		// TODO Auto-generated method stub

		String t1 = "7";
		String t2 = "";
		String t3 = "";
		String t4 = "";
		String t5 = "";
		
		OracleTest_11_1 ot111 = new OracleTest_11_1();
		boolean bool = ot111.t4Delete(t1);
		
		if (bool) {
			System.out.println("삭제 성공 >>> : " + bool);
		} else {
			System.out.println("삭제 실패 >>> : " + bool);
		}
	}
}

8교시 (17:30-18:30)

요구사항 (내일까지 완성할 것)

** TEST_T6 테이블 ISUD , CRUD 하기
-- 데이터
회원번호 T1 NOT NULL VARCHAR2(20) PRIMARY KEY 회원번호 규칙 : T6 + YYYYMMDD + 0001
회원이름 T2 NOT NULL VARCHAR2(20)
회원나이 T3 NUMBER(3)
회원여부 T4 NOT NULL VARCHAR2(1) Y : N
입력일 T5 DATE 최초 입력일
수정일 T6 DATE 변경 : UPDATE, DELETE

변수는 데이터베이스 컬럼명을 소문자로 만든다.
private String t1;
private String t2;
private String t3;
private String t4;
private String t5;
private String t6;

1. 테이블 정의서 만들기
2. 테이블 만들기
3. 클래스 설계서 만들기
패키지
a.b.c.t6.scr
a.b.c.t6.servie

a.b.c.t6.dao

a.b.c.t6.sql

a.b.c.t6.vo a.b.c.common

클래스
화면 : 콘솔
T6Scr.java
   public ArrayList<T6VO> t6SelectAll();
      public ArrayList<T6VO> t6SelectName();
     public ArrayList<T6VO> t6SelectDate();
      public boolean t6Insert();
      public boolean t6Update();
      public boolean t6Delete();
서비스
  T6Service.java 인터페이스
      public ArrayList<T6VO> t4SelectAll();
      public ArrayList<T6VO> t4SelectName(T6VO tvo);
      public ArrayList<T6VO> t4SelectDate(T6VO tvo);
      public boolean t6Insert(T6VO tvo); public boolean t6Update(T6VO tvo);
      public boolean t6Delete(T6VO tvo);

  T6ServiceImpl.java 클래스
다오
  T6DAO.java 인터페이스
      public ArrayList<T6VO> t6SelectAll();
  public ArrayList<T6VO> t6SelectName(T6VO tvo);
  public ArrayList<T6VO> t6SelectDate(T6VO tvo);
  public boolean t6Insert(T6VO tvo);
  public boolean t6Update(T6VO tvo);
  public boolean t6Delete(T6VO tvo);
  T6DAOImpl.java 클래스
sql
  T6QueryMap.java
  public String getT6SelectAll();
  public String getT6SelectName();
  public String getT6SelectDate();
  public String getT6Insert();
  public String getT6Update();
  public String getT6Delete();
vo
  T6VO.java
  private String t1;
  private String t2;
  private String t3;
  private String t4;
  private String t5;
  private String t6;
common
  OracleConnProperty.java
  ChabunQuery.java
  public static final short CHABUN_QUERY_T6 = 1;
  public static String chabun_query[] = {};
  public static String getT6ChabunQuery();
  ChabunUtil.java
  public static String getT6Chabun(String type);
  DateFormatUtil.java
  DateUtil.java

Notes

728x90

저작자표시

'국비지원교육 (22.01-22.07) > 강의노트' 카테고리의 다른 글

22-03-18(금) 035일차 [Java, Oracle, Network] 웹이란 (0)	2022.05.11
22-03-17(목) 034일차 [Java] RFP, 회원 ISUD 구현하기 (0)	2022.05.11
22-03-15(화) 032일차 [Java, Oracle] ISUD (DML: INSERT UPDATE DELETE), 채번 (0)	2022.05.11
22-03-14(월) 031일차 [Java, Oracle] Service 패턴, DDL(CREATE, ALTER, DROP), DML(INSERT, UPDATE, DELETE) (0)	2022.05.11
22-03-11(금) 030일차 [Java, Oracle] DAO 패턴 (0)	2022.05.10

현재글22-03-16(수) 033일차 [Java, Oracle] 자바로 DB 연동하여 SELECT, INSERT, UPDATE, DELETE

A New Life