CODE [day_02] DB / 02. JDBC - INSERT, DELETE

INSERT SQL을 이용해 MEMBER TABLE에 회원정보 저장

 

SQL query

INSERT INTO MEMBER(ID, PASSWORD, NAME, ADDRESS)
VALUES('ebs','abcd','펭수','양재');

 

java code

/TestJDBC3.java

package step3;

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

/*
 * INSERT SQL을 이용해 MEMBER TABLE에 회원정보 저장
 */
public class TestJDBC3 {
	public static void main(String[] args) {
		String driver = "oracle.jdbc.OracleDriver";
		String dbUrl = "jdbc:oracle:thin:@127.0.0.1:1521:xe";
		
		
		try {
			// 1. DB 연동을 위한 Driver loading
			Class.forName(driver);
			
			// 2. Connection
			Connection con = DriverManager.getConnection(dbUrl, "scott", "tiger");
			System.out.println(con);
			
			// 3. SQL 정의 - INSERT
			// PreparedStatement 단계에서 ?를 처리해준다
			String sql = "INSERT INTO MEMBER(ID, PASSWORD, NAME, ADDRESS) VALUES(?,?,?,?)";
			
			//4. PreparedStatement를 이용해 SQL 실행
			PreparedStatement pstmt = con.prepareStatement(sql);
			
			// 5. SQL 완성을 위해 setString
			//setString(n, value) : n - n번째 물음표 / value - 할당할 값
			pstmt.setString(1, "ajax"); //id ? 에 할당
			pstmt.setString(2, "1111");
			pstmt.setString(3, "손흥민");
			pstmt.setString(4, "토트넘");
			
			//6. executeUpdate() : int -> 영향을 준 row수
			// executeUpdate() : INSERT, DELETE, UPDATE 시 사용
			// executeQuery() : SELECT시 ReseultSet 결과를 반환받음
			int result = pstmt.executeUpdate();
			System.out.println("insert OK row "+result);
			
			//7. close()
			pstmt.close();
			con.close();
			
			
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

 

---

DELETE  address가 워싱턴인 회원정보를 모두 삭제

 

SQL query

DELETE FROM MEMBER 
WHERE ADDRESS = 'LA';

 

java code

/TestJDBC4.java

package step3;

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

/*
 * DML- DELETE , address가 워싱턴인 회원정보를 모두 삭제
 */
public class TestJDBC4 {
	public static void main(String[] args) {
		String driver = "oracle.jdbc.OracleDriver";
		String dbUrl = "jdbc:oracle:thin:@127.0.0.1:1521:xe";
		
		try {
			//1. driver loading
			Class.forName(driver);
			
			//2. connection
			Connection con = DriverManager.getConnection(dbUrl, "scott", "tiger");
		
			//3. SQL 정의
			String address = "LA";
			String sql = "DELETE FROM MEMBER WHERE ADDRESS = ?";
					
			//4. preparedStatement
			PreparedStatement pstmt = con.prepareStatement(sql);
			
			//5. setString
			pstmt.setString(1, address);
			
			//6. SQL 실행
			int result = pstmt.executeUpdate(); //result = 결과에 영향을 준 row 수를 반환
			System.out.println("DELETE OK ! 삭제한 회원수 "+ result);
		} 
		
		catch (ClassNotFoundException e) {
			e.printStackTrace();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

 

콘솔 결과