DATABASE STEP 38 - JDBC Applications

★ JDBC 클래스

• 모든 JDBC 작업은 Auto commit이 일어난다.
• 1. Connection
  • DB 연결/해제
• 2. Statement
  • 문장 > SQL 실행
  • a. Statement
    • 기본
  • b. PreparedStatement
    • 매개변수 특화
  • c. CallableStatement
• 3. ResultSet
  • select 결과 참조 클래스

 

■ insert 실행 

• 1. DB 연결
• 2. SQL 실행
• 3. DB 종료

private static void m1() {
		
		Connection conn = null;
		Statement stat = null;
		
		try {
			
			//1. 
			conn = DBUtil.open();
			
			if(!conn.isClosed()) {
				
				System.out.println("접속 성공");
				
				//2.
				//- 자바는 SQL을 모른다. > SQL을 문자열 취급한다.(아무의미없음)
				
				String sql = "insert into tblAddress (seq, name, age, gender, tel, address, regdate) values (seqAddress.nextVal, '아무개', 22, 'm', '010-5555-5555', '서울시 강남구 대치동 100', default)";
				
				// stat > SQL 실행 > 어느 DB + 어떤 계정으로 실행할지?
				stat = conn.createStatement();
				
				//SQL 실행
				//a. 반환값이 없는 쿼리 > select를 뺀 나머지
				//- int executeUpdate()
				//b. 반환값이 있는 쿼리 > select
				//- ResultSet executeQuery()
				
				//SQL Developer > Ctrl + Enter 동일!!
				int result = stat.executeUpdate(sql);
				
				if (result == 1) {
					System.out.println("SQL 실행 성공");
				}else {
					System.out.println("접속 실패");
				}
				
			} else {
				
				System.out.println("접속 실패");
				
			}
			
			//3. 
			conn.close();
			stat.close();
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
}//m1

 

■ CREAT 문

• 1. DB 연결
• 2. SQL 실행
• 3. DB 종료

private static void m2() {
		
		Connection conn = null;
		Statement stat = null;
		
		try {
			
			conn = DBUtil.open();
			stat = conn.createStatement();
			
			//String sql = "update tblAddress set age = age + 1 where seq = 1";
			//String sql = "delete from tblAddress where seq = 1";
			//String sql = "create sequence seqTemp";
			//String sql = "drop sequence seqTemp";
			//String sql = "drop table tblAddress";
			String sql = "create table tblAddress (\r\n"
					+ "    seq number primary key,\r\n"
					+ "    name varchar2(10) not null,\r\n"
					+ "    age number(3) not null check(age between 1 and 99),\r\n"
					+ "    gender char(1) not null check(gender in ('m', 'f')),\r\n"
					+ "    tel varchar2(15) not null,\r\n"
					+ "    address varchar2(300) not null,\r\n"
					+ "    regdate date default sysdate not null\r\n"
					+ ")";
			
			int result = stat.executeUpdate(sql);
			System.out.println(result);
			
			stat.close();
			conn.close();
			
		} catch (Exception e) {
			e.printStackTrace();
		}
}

 

■ 사용자 입력 > insert

• UI + SQL
• 사용자 입력 > insert
• ex) 게시판 글쓰기

private static void m3() {
		
		//UI + SQL
		//- 사용자 입력 > insert
		//- ex) 게시판 글쓰기
		
		Connection conn = null;
		Statement stat = null;
		
		Scanner scan = new Scanner(System.in);
		
		try {
			
			System.out.print("이름 : ");
			String name = scan.nextLine(); //varchar2
			
			System.out.print("나이 : ");
			String age = scan.nextLine();	//number
			
			System.out.print("성별(m,f): ");
			String gender = scan.nextLine();	//char
			
			System.out.print("전화번호 : ");
			String tel = scan.nextLine();		//varchar2
			
			System.out.print("주소: ");
			String address = scan.nextLine();	//varchar2
			
			conn = DBUtil.open();
			stat = conn.createStatement();
			
			String sql = String.format("insert into tblAddress (seq, name, age, gender, tel, address, regdate) values (seqAddress.nextVal, '%s', %s, '%s', '%s', '%s', default)", name, age, gender, tel, address);
			
			int result = stat.executeUpdate(sql);
			System.out.println(result);
			
			stat.close();
			conn.close();
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
}

 

■ 단일값 반환 (1행 1열)

private static void m1() {
		
		//단일값 반환
		//- 결과셋 : 1행 1열

		Connection conn = null;
		Statement stat = null;
		ResultSet rs = null;
		
		try {
			
			conn = DBUtil.open();
			stat = conn.createStatement();
			
			//String sql = "select count(*) as cnt from tblInsa";
			String sql = "select name from tblInsa where num = 1010";
			
			rs = stat.executeQuery(sql);
			
			//RsultSet == 오라클 커서(Cursor)
			
			rs.next(); // 커서를 한줄 전진(이동)
			
			//현재 커서가 가르키고 있는 레코드 접근 > 특정 컬럼값을 읽기
			//-rs.getXXX();
			//int count = rs.getInt(1);
			//int count = rs.getInt("cnt");		//number > int
			//String count2 = rs.getString("cnt"); //number > String
			String name = rs.getString("name");
			
			//System.out.println("카운트 : " + count);
			//System.out.println("카운트 : " + count2);
			System.out.println(name);
			
			
			rs.close();
			stat.close();
			conn.close();
			
		} catch (Exception e) {
			e.printStackTrace();
		}
}

 

■ 1행 N열

private static void m2() {
		
		// 결과셋 : 1행 N열
		Connection conn = null;
		Statement stat = null;
		ResultSet rs = null;
		
		try {
			
			conn = DBUtil.open();
			stat = conn.createStatement();
			
			String sql = "select name, age, address from tblAddress where seq = 3";
			
			rs = stat.executeQuery(sql);
			
			if (rs.next()) {
				
				System.out.println("이름 : " + rs.getString("name"));
				System.out.println("나이 : " + rs.getString("age"));
				System.out.println("주소 : " + rs.getString("address"));
				
			} else {
				System.out.println("데이터가 없음");
			}
			
			rs.close();
			stat.close();
			conn.close();
			
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
}

 

■ N행 1열

private static void m3() {
		
		//결과셋 : N행 1열
		
		Connection conn = null;
		Statement stat = null;
		ResultSet rs = null;
		
		try {
			
			conn = DBUtil.open();
			stat = conn.createStatement();
			
			String sql = "select name from tblInsa order by name";
			
			//60명 레코드 
			rs = stat.executeQuery(sql);
			
			/*
			 * rs.next(); System.out.println(rs.getString("name"));
			 * 
			 * rs.next(); System.out.println(rs.getString("name"));
         	 * 		
         	 * rs.next(); System.out.println(rs.getString("name"));
			 */
			
			while (rs.next()) {
				System.out.println(rs.getString("name"));
			}
			//Resultset > 커서가 어디있는지??
			
			
			rs.close();
			stat.close();
			conn.close();
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
}

■ N행 N열

private static void m4() {
		
		//결과셋 : N행 N열
		

		Connection conn = null;
		Statement stat = null;
		ResultSet rs = null;
		
		try {
			
			conn = DBUtil.open();
			stat = conn.createStatement();
			
			//사용자에게 부서 입력 > 부서 명단 출력
			Scanner scan = new Scanner(System.in);
			
			System.out.print("부서명: ");
			String buseo = scan.nextLine();
			
			String sql = String.format("select * from tblInsa where buseo = '%s'", buseo);
			
			rs = stat.executeQuery(sql);
			
			System.out.println("==============================================");
			System.out.println("                     " + buseo);
			System.out.println("===============================================");
			System.out.println("[번호]\t[이름]\t[직위]\t     [급여]\t[수당]\t");
			
			while(rs.next()) {
				
				System.out.printf("%s\t%s\t%s\t%,12d\t%,d\n"
									, rs.getString("num")
									, rs.getString("name")
									, rs.getString("jikwi")
									, rs.getInt("basicpay")
									, rs.getInt("sudang"));
				
			}
			
			rs.close();
			stat.close();
			conn.close();
			
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
}