[자바/DB] JDBC - 정보 입력 받아 출력 및 수정

---

INSERT문

 

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Scanner;

public class Ex01_insert문 {

	public static void main(String[] args) {
		
		
		PreparedStatement psmt = null;
		Connection conn = null;
		String url = "jdbc:oracle:thin:@127.0.0.1:1521:xe";
		String db_id = "hr";
		String db_pw = "hr";
		
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection(url, db_id, db_pw);
			
				if(conn != null) {
					System.out.println("연결 성공");
				}
				else {
					System.out.println("연결 실패");
				}
				
				Scanner sc = new Scanner(System.in);
				System.out.print("ID : ");
				String id = sc.next();
				System.out.print("PW : ");
				String pw = sc.next();
				System.out.print("Name : ");
				String name = sc.next();
				System.out.print("Age : ");
				int age = sc.nextInt();
				
			String sql = "INSERT INTO MEMBER values(? , ? , ? , ?)";
			psmt = conn.prepareStatement(sql);
			
			//sql문에 들어가는 값을 세팅
			psmt.setString(1, id);
			psmt.setString(2, pw);
			psmt.setString(3, name);
			psmt.setInt(4, age);
			
			
			int cnt = psmt.executeUpdate();
			
			if(cnt>0) {
				System.out.println("insert 성공");
			}
			else {
				System.out.println("insert 실패");
			}
		}
			catch (ClassNotFoundException e) {
				System.out.println("동적 오류 발생");
				e.printStackTrace();
			}
			catch (SQLException e) {
				System.out.println("SQL문 에러 발생");
				e.printStackTrace();
			}
			
			
			finally {
				try {
					if(psmt != null) {
							psmt.close();
						}
					if(conn != null) {
						conn.close();
					} 
					}catch (SQLException e) {
						e.printStackTrace();
					}					
				}
		
		
		
		//내가 해본 코드
		/*try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
		
		String url = "jdbc:oracle:thin:@127.0.0.1:1521:xe";
		Scanner sc = new Scanner(System.in);
		//키보드로부터 id/pw/name/age 등을 입력받고
		System.out.print("id : ");
		String id = sc.next();
		System.out.print("pw : ");
		String pw = sc.next();
		System.out.print("name : ");
		String name = sc.next();
		System.out.print("age : ");
		int age = sc.nextInt();
		
		Connection conn;
	
			conn = DriverManager.getConnection(url, id, pw);
		
		
		if(conn != null) {
			System.out.println("연결 성공");
		}
		
		else {
			System.out.println("연결 실패");
		}
		
		String sql = "INSERT INTO MEMBER values('Mercury','0905',name,age)";
		
		
		
		PreparedStatement psmt = conn.prepareStatement(sql);

		int cnt = psmt.executeUpdate();
		
		if(cnt>0) {
			System.out.println("insert 성공");
		}
		else {
			System.out.println("insert 실패");
		}

		if(psmt != null) {
			psmt.close();
		}
		if(conn != null) {
			conn.close();
		}
		}catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}*/
		
	}

}

 

 

 

 

---

UPDATE문

 

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;
import java.sql.DriverManager;

public class Ex02_update문 {

	public static void main(String[] args) {
		//ID가 Mars인 사람을 비밀번호 1025로 바꿔서 SQL문을 전송하는 JDBC 구현
		PreparedStatement psmt = null;
		Connection conn = null;
		ResultSet rs = null;
		
		Scanner sc = new Scanner(System.in);
		System.out.println("===회원정보 수정===");
		System.out.print("ID입력 : ");
		String id = sc.next();
		System.out.print("PW입력 : ");
		String pw = sc.next();
		
		String sql = "UPDATE MEMBER SET pw=? where id = ?";
		
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			String url = "jdbc:oracle:thin:@127.0.0.1:1521:xe";
			String db_id="hr";
			String db_pw="hr";
			conn = DriverManager.getConnection(url, db_id, db_pw);
			
			psmt = conn.prepareStatement(sql);
			psmt.setString(1, pw);
			psmt.setString(2, id);
			
			int cnt = psmt.executeUpdate();
			
			if(cnt>0) {
				System.out.println("수정 성공");
			}
			else {
				System.out.println("수정 실패");
			}
			
			sql = "SELECT * FROM MEMBER where ID = ?";
			psmt = conn.prepareStatement(sql);
			psmt.setString(1, id);
			
			rs = psmt.executeQuery();
			
			while(rs.next()) {
				String id2 = rs.getString(1);
				String pw2 = rs.getString(2);
				String name = rs.getString(3);
				int age = rs.getInt(4);
				
				System.out.println(id2+" "+pw2+" "+name+" "+age);
		
			}
	
			}
		 catch (ClassNotFoundException e) { 

			System.out.println("동적 오류");
				e.printStackTrace();
			} catch (SQLException e) { 
			System.out.println("sql 오류");
				e.printStackTrace();
		}
		

		finally {
			try {
				if(psmt != null) {
						psmt.close();
					}
				if(conn != null) {
					conn.close();
				} 
				}catch (SQLException e) {
					e.printStackTrace();
				}					
			}
		
		/*내가 쓴 코드
		PreparedStatement psmt = null;
		Connection conn = null;
		String url = "jdbc:oracle:thin:@127.0.0.1:1521:xe";
		String id = "hr";
		String pw = "hr";
		
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			
			conn = DriverManager.getConnection(url, id, pw);
			
			if(conn != null) {
				System.out.println("연결 성공");
			}
			
			else {
				System.out.println("연결 실패");
			}
		
			String sql = "UPDATE MEMBER SET pw='1025' where id = 'Mars'";
			
			psmt = conn.prepareStatement(sql);

			int cnt = psmt.executeUpdate();
			
			if(cnt>0) {
				System.out.println("update 성공");
			}
			else {
				System.out.println("update 실패");
			}

			if(psmt != null) {
				psmt.close();
			}
			if(conn != null) {
				conn.close();
			}
			
			
			
		}
		 catch (ClassNotFoundException e) { 

			System.out.println("드라이버 오류");
				e.printStackTrace();
			} catch (SQLException e) { 
			System.out.println("sql 오류");
				e.printStackTrace();
		}
		*/
		
	
		
	}

}