demo1

package db.demo1;

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

public class DeleteDemo {

	public static void main(String[] args) throws Exception {
		String sql = "delete from user_contacts where user_name = ?";
		
		Class.forName("oracle.jdbc.OracleDriver");
		Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "hr", "zxcv1234");
		
		PreparedStatement pstmt = connection.prepareStatement(sql);
		pstmt.setString(1, "ㅁ마ㅁㅁ마");
		int rowCount = pstmt.executeUpdate();
		System.out.println(rowCount + "개의 행이 삭제되었습니다.");
		
		pstmt.close();
		connection.close();
	}
}

 

package db.demo1;

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

public class InsertDemo1 {

	public static void main(String[] args) throws ClassNotFoundException, SQLException {
		
		Scanner scanner = new Scanner(System.in);
		
		System.out.println("주소록 등록 프로그램");
		
		System.out.println("순번입력> ");
		int no = scanner.nextInt();
		System.out.println("이름입력> ");
		String name = scanner.next();
		System.out.println("연락처입력> ");
		String phone = scanner.next();
		
		// 1. JDBC 드라이버 메모리 로딩
		Class.forName("oracle.jdbc.OracleDriver");
		
		// 2. Database와 연결을 유지하는 Connection 객체 획득하기
		String url1 = "jdbc:oracle:thin:@localhost:1521:xe";
		String user = "hr";
		String password = "zxcv1234";
		Connection connection = DriverManager.getConnection(url1, user, password);
		
		// 3. 실행할 쿼리 정의
		String sql = "insert into user_contacts"
				+ "(user_no, user_name, user_phone, user_create_date)"
				+ "values"
				+ "(?, ?, ?, sysdate)";
		
		// 4. 쿼리를 데이터베이스로 전송하는 PreparedStatement 객체 획득하기
		PreparedStatement pstmt = connection.prepareStatement(sql);
		
		// 5. PreparedStatement의 setXXX(인덱스, 값) 메소드를 사용해서 ?와 치환될 값 설정하기
		pstmt.setInt(1, no);
		pstmt.setString(2, name);
		pstmt.setString(3, phone);
		
		// 6. 쿼리 실행
		int rowCount = pstmt.executeUpdate();
		System.out.println(rowCount + "개의 행이 추가되었습니다.");
		
		// 7. 자원 해제 (필수!, 자원 해제는 역순으로!)
		pstmt.close();
		connection.close();
		
	}
}

 

package db.demo1;

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

public class SelectDemo {

	public static void main(String[] args) throws Exception {
		String sql = "select * from user_contacts";
		
		Class.forName("oracle.jdbc.OracleDriver");
		Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "hr", "zxcv1234");
		PreparedStatement pstmt = conn.prepareStatement(sql);
		ResultSet rs= pstmt.executeQuery();

		while (rs.next()) {
			int no = rs.getInt("user_no");
			String name = rs.getString("user_name");
			String phone = rs.getString("user_phone");
			
			System.out.println(no + ", " + name + ", " + phone);
			
		}
		rs.close();
		pstmt.close();
		conn.close();
	}
}

 

package db.demo1;

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

public class UpdateDemo {

	public static void main(String[] args) throws Exception {
		String sql = "update user_contacts "
				+ "set "
				+ "user_name = ?, "
				+ "user_phone = ? "
				+ "where user_no = ? ";
		
		Scanner scanner = new Scanner(System.in);
		
		System.out.println("수정할 사용자 번호 입력> ");
		int no = scanner.nextInt();
		System.out.println("새 이름 입력> ");
		String name = scanner.next();
		System.out.println("새 연락처 입력> ");
		String phone = scanner.next();
		
		Class.forName("oracle.jdbc.OracleDriver");
		Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "hr", "zxcv1234");
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setString(1, name);
		pstmt.setString(2, phone);
		pstmt.setInt(3, no); // 3은 열 번호. 5 입력시 부적절한 인덱스라며 에러
		int rowCount = pstmt.executeUpdate();
		System.out.println(rowCount + "개의 행이 변경되었습니다.");
		
		pstmt.close();
		conn.close();
		
		
	}
}