[Day29][JDBC] Select where / Delete / 수동commit / PreparedStatement

Ⅰ. select where

-- Package(jdbc.day01.sql)에 Class(JdbcTest04SelectWhere) Class(JdbcTest03Select) 복사하여 생성

 

(Eclipse)package jdbc.day01.sql;

import java.sql.*;
import java.util.Scanner;

public class JdbcTest04SelectWhere {

	public static void main(String[] args) {
		
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
	
		Scanner sc = new Scanner(System.in);
		
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
            
			System.out.print("▷ 연결할 오라클 서버의 IP주소 : ");
			String IP = sc.nextLine();
			
			System.out.print("▷ 오라클 사용자명 : ");
			String userName = sc.nextLine();
			
			System.out.print("▷ 암호 : ");
			String passwd = sc.nextLine();
			
			conn = DriverManager.getConnection("jdbc:oracle:thin:@"+IP+":1521:xe", userName, passwd);
			
            stmt = conn.createStatement();
			
		    System.out.println("== 검색대상 선택 ==\n" 
   				 + "1.글번호 2.글쓴이 3.글내용 4.전체조회\n");
		    
		    String searchStmt = "";
		    
		    System.out.print("메뉴선택 => ");
		    String strNo = sc.nextLine();
		    
		    System.out.print("검색어 => ");
		    String searchWord = sc.nextLine();
		   
		    if("1".equals(strNo)) {	// 글번호 검색
		    	searchStmt = " no = " + searchWord;
		    }
		    
		    else if("2".equals(strNo)) { // 글쓴이 검색
		    	searchStmt = " name like '%'||'"+searchWord+"'||'%' "; // 특수문자 앞에는 역슬래쉬('\')가 있어야 하지만 없어도 실행된다.
		    }
		    
		    else if("3".equals(strNo)) { // 글내용 검색
		    	searchStmt = " msg like '%'||'"+searchWord+"'||'%' ";
		    } 
		    
		    else if("4".equals(strNo)) { // 전체조회
		    	searchStmt = " 1=1";
		    }
		    
		    String sql = "select no, name, msg\n"+
		    		"      ,to_char(writeday, 'yyyy-mm-dd hh24:mi:ss') AS WRITEDAY\n"+
		    		"from jdbc_tbl_memo\n"+
		    		"where " + searchStmt + "\n" +
		    		"order by no desc";
			
			rs = stmt.executeQuery(sql);
			
			System.out.println("--------------------------------------------------------");
			System.out.println("글번호\t글쓴이\t글내용\t작성일자");

			System.out.println("--------------------------------------------------------");
			
			while(rs.next()) {
				int no = rs.getInt("no");
				String name = rs.getString("name");
				String msg = rs.getString("msg");
				String writeday = rs.getString("WRITEDAY");
				
				System.out.println(no+"\t"+name+"\t"+msg+"\t"+ writeday);
			}
		} catch (ClassNotFoundException e) {
			System.out.println(">> ojdbc6.jar 파일이 없습니다. <<");
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if(rs != null) rs.close();
				if(stmt != null) stmt.close();
				if(conn != null) conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

 

---

Ⅱ. delete

 

(Eclipse)package jdbc.day01.sql;

import java.sql.*;
import java.util.Scanner;

public class JdbcTest05Delete {
	public static void main(String[] args) {
		
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		
		Scanner sc = new Scanner(System.in);
		
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			
			conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:xe", "myorauser", "eclass");
			
		    stmt = conn.createStatement();
			
		    System.out.print("▷ 삭제할 글번호 입력 : ");
			String strNo = sc.nextLine();
		   
		    String sql = "delete from jdbc_tbl_memo\n"+
						 "where no = " + Integer.parseInt(strNo);
		    
		    String sql2 = "select no, name, msg\n"+
		    		"      ,to_char(writeday, 'yyyy-mm-dd hh24:mi:ss') AS WRITEDAY\n"+
		    		"from jdbc_tbl_memo\n"+
		    		"order by no desc";
		   
			int n = stmt.executeUpdate(sql);
			rs = stmt.executeQuery(sql2);
			
			if(n==1) {
			System.out.println("글번호" + Integer.parseInt(strNo) + "가 삭제되었습니다.");
			}
			else {
				System.out.println("글번호" + Integer.parseInt(strNo) + "번 글은 존재하지 않으므로 삭제 불가합니다.");
			}
			
			System.out.println("--------------------------------------------------------");
			System.out.println("글번호\t글쓴이\t글내용\t작성일자");

			System.out.println("--------------------------------------------------------");
			
			while(rs.next()) {
				int no = rs.getInt(1);
				String name = rs.getString(2);
				String msg = rs.getString(3);
				String writeday = rs.getString(4);
				
				System.out.println(no+"\t"+name+"\t"+msg+"\t"+ writeday);
			}
		} catch (ClassNotFoundException e) {
			System.out.println(">> ojdbc6.jar 파일이 없습니다. <<");
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if(rs != null) rs.close();
				if(stmt != null) stmt.close();
				if(conn != null) conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			sc.close();
		}

 

 

 

---

Ⅲ. 수동 commit

 

Delete 수동 commit으로 변경

 

-- 동일 Package(jdbc.day01.sql)에 Class(JdbcTest06DeleteSudongCommit)를 Class(JdbcTest05Delete) 복사하여 생성

-- Java에서 오라클의 기본은 Auto Commit이다. 수동 Commit을 하는 방법을 알아 보자.

 

(Eclipse)JdbcTest06DeleteSudongCommit

conn.setAutoCommit(false);

 

-- true일 경우 auto commit

 

(Eclipse)JdbcTest06DeleteSudongCommit

if (n == 1) {
		System.out.print("▷ 적용(Y/y) / 취소(N/n) => ");
		String confirm = sc.nextLine();

		if ("Y".equals(confirm) || "y".equals(confirm)) {
			conn.commit();
			System.out.println("글번호" + Integer.parseInt(strNo) + "번 글이 삭제되었습니다.");
		}
		else if ("N".equals(confirm) || "n".equals(confirm)) {
			conn.rollback();
			System.out.println("글번호" + Integer.parseInt(strNo) + "번 글 삭제가 취소되었습니다.");
		}
	} 
			
	else {
		System.out.println("글번호" + Integer.parseInt(strNo) + "번 글은 존재하지 않으므로 삭제 불가합니다.");
	}

 

 

 

---

 

Update 수동 commit으로 변경해 보세요.

 

(Eclipse)package jdbc.day01.sql;

import java.sql.*;
import java.util.Scanner;

public class JdbcTest07UpdateSudongCommit {

	public static void main(String[] args) {
		
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		
		Scanner sc = new Scanner(System.in);
		
		try {
			// >>> 1. OracleDriver(오라클 드라이버) 로딩 <<<
			Class.forName("oracle.jdbc.driver.OracleDriver");
			
			// >>> 2. 어떤 오라클 서버와 연결을 할래? <<<
			conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:xe", "myorauser", "eclass");
			
			// 수동 commit
			conn.setAutoCommit(false);
			
			
			// >>> 3. 연결한 오라클서버에 SQL문(편지)을 전달할 Statement 객체(우편배달부) 생성하기 <<<
		    stmt = conn.createStatement();
			
			// >>> 4. SQL문을 작성한다. <<<
		    System.out.print("▷ 변경할 글번호 입력 : ");
			String strNo = sc.nextLine();
			
			System.out.print("▷ 작성자명 : ");
			String strName = sc.nextLine();
			
			System.out.print("▷ 글내용 : ");
			String strMsg = sc.nextLine();
		   
		    String sql = "update jdbc_tbl_memo set name = '" + strName + "'\n" +
                         ",msg = '" + strMsg + "'\n" +
                         " where no = " + strNo;
		    

			// >>> 5. Statement stmt(우편배달부)가 작성된 SQL문(편지)을 오라클 서버에 보내서 실행이 되어지도록 한다. <<<
		    
			int n = stmt.executeUpdate(sql);
			
			if (n == 1) {
				System.out.print("▷ 적용(Y/y) / 취소(N/n) => ");
				String confirm = sc.nextLine();

				if ("Y".equals(confirm) || "y".equals(confirm)) {
					conn.commit();
					System.out.println(">> 글번호 1번글의 작성자명, 글내용이 변경되었습니다. <<");
				}

				else if ("N".equals(confirm) || "n".equals(confirm)) {
					conn.rollback();
					System.out.println("글번호" + Integer.parseInt(strNo) + "번 글 변경이 취소되었습니다.");
				}
			} 
			else {
				System.out.println("글번호" + Integer.parseInt(strNo) + "번 글은 존재하지 않으므로 변경이 불가합니다.");
			}
			
			String sql2 = "select no, name, msg\n"+
		    		"      ,to_char(writeday, 'yyyy-mm-dd hh24:mi:ss') AS WRITEDAY\n"+
		    		"from jdbc_tbl_memo\n"+
		    		"order by no desc";
			
			rs = stmt.executeQuery(sql2);
			
			System.out.println("--------------------------------------------------------");
			System.out.println("글번호\t글쓴이\t글내용\t작성일자");

			System.out.println("--------------------------------------------------------");
			
			while(rs.next()) {
				int no = rs.getInt(1);
				String name = rs.getString(2);
				String msg = rs.getString(3);
				String writeday = rs.getString(4);
				
				System.out.println(no+"\t"+name+"\t"+msg+"\t"+ writeday);
			}
		} catch (ClassNotFoundException e) {
			System.out.println(">> ojdbc6.jar 파일이 없습니다. <<");
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			// >>> 6. 사용하였던 자원 반납하기 <<<
			try {
				if(rs != null) rs.close();
				if(stmt != null) stmt.close();
				if(conn != null) conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			sc.close();
		}
	} 
}

 

 

 

 

---

Ⅳ. PreparedStatement

 

-- Statement는 보안상의 이유로 잘 사용되지 않는다.

 

(Eclipse)JdbcTest07UpdateSudongCommit

System.out.println(sql);

 

 

-- sql을 출력해 보면 변경하고자 하는 데이터가 다 보이는 것을 확인할 수 있다.

-- Statement 대신 PreparedStatement를 사용한다.

 

---

 

-- Package(jdbc.day02.sql) 와 Class(JdbcTest07UpdateSudongCommit)를 복사하여 Class(JdbcTest07UpdatePreparedStatement)를 생성한다.

 

(Eclipse)JdbcTest07UpdatePreparedStatement

PreparedStatement pstmt = null;

 

-- Statement로 지정했던 것을 PreparedStatement로 변경한다. (변수명은 pstmt)

 

package jdbc.day02.sql;

import java.sql.*;
import java.util.Scanner;

public class JdbcTest07UpdatePreparedStatement {

	public static void main(String[] args) {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		Scanner sc = new Scanner(System.in);
		
		try {
			// >>> 1. OracleDriver(오라클 드라이버) 로딩 <<<
			Class.forName("oracle.jdbc.driver.OracleDriver"); // "오라클 드라이버가 있는 경로 name"
			
			// >>> 2. 어떤 오라클 서버와 연결을 할래? <<<
			conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:xe", "myorauser", "eclass");
			
			// 수동 commit
			conn.setAutoCommit(false);
			
			// >>> 3. SQL문을 작성한다. (try~catch 안에 해도 괜찮고 밖에 해도 괜찮다.) <<<
		    System.out.print("▷ 변경할 글번호 입력 : ");
			String strNo = sc.nextLine();
			
			System.out.print("▷ 작성자명 : ");
			String strName = sc.nextLine();
			
			System.out.print("▷ 글내용 : ");
			String strMsg = sc.nextLine();
		   
		    String sql = "update jdbc_tbl_memo set name = ? \n" +
                         ",msg = ? \n" +
                         " where no = ? ";
		    			// ? 를 "위치홀더"라고 부른다. 
		    		
		    
		    // >>> 4. 연결한 오라클서버에 SQL문(편지)을 전달할 preparedStatement 객체(우편배달부) 생성하기 <<<
		    pstmt = conn.prepareStatement(sql);
		    
		    pstmt.setString(1, strName); // 숫자 1은 sql에서 첫 번째 물음표(위치홀더)를 말한다.
		    pstmt.setString(2, strMsg);
		    pstmt.setString(3, strNo);

			System.out.println(sql);
		    
			// >>> 5. Statement stmt(우편배달부)가 작성된 SQL문(편지)을 오라클 서버에 보내서 실행이 되어지도록 한다. <<<
		    
			int n = pstmt.executeUpdate();
			
			if (n == 1) {
				System.out.print("▷ 적용(Y/y) / 취소(N/n) => ");
				String confirm = sc.nextLine();

				if ("Y".equals(confirm) || "y".equals(confirm)) {
					conn.commit();
					System.out.println(">> 글번호 1번글의 작성자명, 글내용이 변경되었습니다. <<");
				}

				else if ("N".equals(confirm) || "n".equals(confirm)) {
					conn.rollback();
					System.out.println("글번호" + Integer.parseInt(strNo) + "번 글 변경이 취소되었습니다.");
				}

			} 
			
			else {
				System.out.println("글번호" + Integer.parseInt(strNo) + "번 글은 존재하지 않으므로 변경이 불가합니다.");
			}
			
		} catch (ClassNotFoundException e) {
			System.out.println(">> ojdbc6.jar 파일이 없습니다. <<");
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			// >>> 6. 사용하였던 자원 반납하기 <<<
			try {
				if(pstmt != null) pstmt.close();
				if(conn != null) conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			sc.close();
		}
	}
}

 

 

-- Statement 와 PreparedStatement 의 가장 큰 차이점은 캐시 사용유무이다. 

-- Statement는 매번 쿼리문장(SQL문)을 수행할때 마다 모든 단계(파싱 parsing)를 거치지만 PreparedStatement는 처음 한번만 모든 단계(파싱 parsing)를 수행한 후 캐시에 담아 재사용한다. 그러므로 동일한 쿼리문장(SQL문)을 수행시 PreparedStatement가 DB에 훨씬 적은 부하를 주므로 성능이 좋아진다.

 

---

select where PreparedStatement 사용하기

 

package jdbc.day02.sql;

import java.sql.*;
import java.util.Scanner;

public class JdbcTest04SelectWhere {

	public static void main(String[] args) {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
        ResultSet rs = null;
		
		Scanner sc = new Scanner(System.in);
		
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver"); // "오라클 드라이버가 있는 경로 name"
			
			conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:xe", "myorauser", "eclass");
									
		    System.out.println("== 검색대상 선택 ==\n" 
   				 + "1.글번호 2.글쓴이 3.글내용 4.전체조회\n");
		    
		    System.out.print("메뉴선택 => ");
		    String strNo = sc.nextLine();
		    
		    System.out.print("검색어 => ");
		    String searchWord = sc.nextLine();
		    
		    String sql = "select no, name, msg\n"+
		    		"      ,to_char(writeday, 'yyyy-mm-dd hh24:mi:ss') AS WRITEDAY\n"+
		    		"from jdbc_tbl_memo \n";
	    
		    if("1".equals(strNo)) {	
		    	sql += " where no = ? ";
		    }
		    
		    else if("2".equals(strNo)) {
		    	sql += " where name like '%'|| ? ||'%' "; // 특수문자 앞에는 역슬래쉬('\')가 있어야 하지만 없어도 실행된다.
		    }
		    
		    else if("3".equals(strNo)) {
		    	sql += " where msg like '%'|| ? ||'%' ";
		    } 
		    
		    else if("4".equals(strNo)) {
		    	sql += "";
		    }
		   
		    sql += " order by no desc";
		   
		    pstmt = conn.prepareStatement(sql);
		    
		    if(!"4".equals(strNo)) {
		    	pstmt.setString(1, searchWord);
		    }
		    
		    System.out.println("sql 확인용 => \n" +sql);
		    
			rs = pstmt.executeQuery();
			
			System.out.println("--------------------------------------------------------");
			System.out.println("글번호\t글쓴이\t글내용\t작성일자");

			System.out.println("--------------------------------------------------------");
			
			while(rs.next()) {
				
				int no = rs.getInt("no");
				String name = rs.getString("name");
				String msg = rs.getString("msg");
				String writeday = rs.getString("WRITEDAY");
				
				System.out.println(no+"\t"+name+"\t"+msg+"\t"+ writeday);
			}
		} catch (ClassNotFoundException e) {
			System.out.println(">> ojdbc6.jar 파일이 없습니다. <<");
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if(rs != null) rs.close();
				if(pstmt != null) pstmt.close();
				if(conn != null) conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

 

 

---

문제 ▷▷ 아래의 메뉴에 맞는 기능을 나타내세요.

 

 

package jdbc.day02.sql.preparedstatement;

import java.sql.*;
import java.util.Scanner;

public class QuizSelectHR {

	public static void main(String[] args) {

		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		Scanner sc = new Scanner(System.in);

		outer: try {
			Class.forName("oracle.jdbc.driver.OracleDriver");

			conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:xe", "HR", "eclass");

			String menuNo = "";

			do {
				System.out.println("== 검색대상 선택 ==\n" + "1.사원번호 2.부서번호 3.남자 4.여자 5.전체조회 6.프로그램 종료\n");

				System.out.print("▷ 메뉴 선택 : ");
				menuNo = sc.nextLine();
				String searchNo = "";

				String sql = "select employee_id\n" + "      ,first_name || ' ' || last_name AS ENAME\n"
						+ "      ,department_id\n" + "      ,salary\n"
						+ "      ,case when substr(jubun, 7, 1) in ('1', '3') then '남자' else '여자' end AS GENDER\n"
						+ "from employees";

				switch (menuNo) {
				case "1":
					System.out.print("찾고자 하는 사원번호 : ");
					searchNo = sc.nextLine();
					sql += " where employee_id = ? order by 1";
					break;
				case "2":
					System.out.print("찾고자 하는 부서번호 : ");
					searchNo = sc.nextLine();
					sql += " where department_id = ? order by 1";
					break;
				case "3":
					sql += " where substr(jubun, 7, 1) in ('1', '3') order by 1";
					break;
				case "4":
					sql += " where substr(jubun, 7, 1) in ('2', '4') order by 1";
					break;
				case "5":
					sql += " order by 1";
					break;
				case "6":
					System.out.println("프로그램을 종료합니다.");
					break outer;

				default:
					System.out.println("1~5까지의 숫자만 입력하세요.");
					continue;
				}

				pstmt = conn.prepareStatement(sql);

				if ("1".equals(menuNo) || "2".equals(menuNo)) {
					pstmt.setString(1, searchNo);
				}

				rs = pstmt.executeQuery();

				System.out.println("--------------------------------------------------------");
				System.out.println("사원번호\t사원명\t\t\t부서번호\t월급\t성별");
				System.out.println("--------------------------------------------------------");

				while (rs.next()) {
					int empId = rs.getInt("employee_id");
					String ename = rs.getString("ENAME");
					int dptId = rs.getInt("department_id");
					int salary = rs.getInt("salary");
					String gender = rs.getString("GENDER");

					System.out.println(empId + "\t" + ename + "\t\t" + dptId + "\t" + salary + "\t" + gender);
				}
			} while (true);

		} catch (ClassNotFoundException e) {
			System.out.println(">> ojdbc6.jar 파일이 없습니다. <<");
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (pstmt != null)
					pstmt.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

	}
}

 

---

PreparedStatement를 사용하여 DDL과 DML 작업을 같이 할 경우

 

-- 실행되어질 sql문이 DDL문(create, alter, drop, truncate)이라면 리턴값이 -1이 나온다.
-- 실행되어질 sql문이 DML문(insert, update, delete)이라면 리턴값이 적용된 행의 개수가 나온다.