38. SimpleJDBC (Select, Insert, Delete, Update)

memo.txt

drop table test;

create table test(
	num number,
	name varchar2(10),
	addr varchar2(10)
	);

insert into test values(1, '윤아', '서울');
insert into test values(2, '아이유', '제주');
insert into test values(3, '웬디', '부산');
commit;

select * from test;	

[JDBC 프로그래밍 단계]
1. 드라이버 로드			
2. 계정 접속			
3. SQL문 작성 - 분석   
		3.1) ?자리에 값 채우기
4. SQL문 실행 		   
5. 데이터베이스 끊기

 

NUM	NAME	ADDR
1	윤아	서울
2	아이유	제주
3	웬디	부산

 

[JDBC 프로그래밍 단계]
1. 드라이버 로드
2. 계정 접속
3. SQL문 작성 - 분석
                3.1) ?자리에 값 채우기
4. SQL문 실행
5. 데이터베이스 끊기

 

 

---

1. Select문 작성

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

public class Ex07_select연습 {

	public static void main(String[] args) {
		String driver = "oracle.jdbc.driver.OracleDriver";
		String url = "jdbc:oracle:thin:@localhost:1521:orcl";
		String id = "sqlid";
		String pw = "sqlpw";

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

		// 1. 드라이브 호출
		try {
			Class.forName(driver);

			// 2. 계정연결
			conn = DriverManager.getConnection(url, id, pw);

			// 3. SQL문 작성 - 분석
			String sql  = "Select * from test";
			ps = conn.prepareStatement(sql);

			// 4. sql문 실행
			rs = ps.executeQuery(sql);

			while(rs.next()) {
				// 한줄씩 접근
				int num = rs.getInt("num");
				String name = rs.getString("name");
				String addr = rs.getString("addr");
				System.out.println(num + "," + name + "," + addr);
			}

		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {

			{
				try {
					if(conn != null) 
						conn.close();
					if(ps != null)
						ps.close();
					if(rs != null)
						rs.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
	}
}

---

2. Insert문 작성

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

public class Ex08_insert연습 {

	public static void main(String[] args) {
		String driver = "oracle.jdbc.driver.OracleDriver";
		String url = "jdbc:oracle:thin:@localhost:1521:orcl";
		String id = "sqlid" ;
		String pw = "sqlpw";

		Connection conn = null;
		PreparedStatement ps = null;
		// 1. 드라이브

		try {
			Class.forName(driver);
			// 2. 계정연결
			conn = DriverManager.getConnection(url, id, pw);
			// 3. sql 작성 - 분석
			int num = 5;
			String name = "태연";
			String addr = "경주";

			String sql = "insert into test (num, name, addr)values(?, ?, ?)";
			ps = conn.prepareStatement(sql);	
			ps.setInt(1, num);
			ps.setString(2, name);
			ps.setString(3, addr);

			int cnt = ps.executeUpdate();
			System.out.println("cnt : "+ cnt);
			System.out.println(num + " " + name + " " + addr);

		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				if(conn != null)
					conn.close();
				if(ps != null)
					ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

	}

}

 

---

3. Insert문 작성

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

public class Delete {

	public static void main(String[] args) {
		Connection conn = null;
		PreparedStatement ps = null;
		
		String driver = "oracle.jdbc.driver.OracleDriver";
		String url = "jdbc:oracle:thin:@localhost:1521:orcl";
		String id = "sqlid";
		String pw = "sqlpw";
		// 드라이브

			try {
				Class.forName(driver);
				conn = DriverManager.getConnection(url, id, pw);
				int num=30;
								
//				String sql = "delete from test where num=10";
				String sql = "delete from test where num=?";
				ps = conn.prepareStatement(sql);
				ps.setInt(1, num);
				
				int cnt = ps.executeUpdate();
				System.out.println("cnt :" + cnt);
				
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
			} catch (SQLException e) {
				e.printStackTrace();
			}finally {
				if(ps!= null) {
					try {
						ps.close();
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				}
				
				if(conn != null) 
				{
					try {
						conn.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
				
			}
		
			
			
	


	}

}

---

4. Update문 작성

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

public class Update {

	public static void main(String[] args) {
		String driver = "oracle.jdbc.driver.OracleDriver";
		String url = "jdbc:oracle:thin:@localhost:1521:orcl";
		String id = "sqlid";
		String pw = "sqlpw";
		
		int cnt = -1;
		Connection conn = null;
		PreparedStatement  ps = null;
		try {
			// 드라이버
			Class.forName(driver);
			// 계정접속
			conn = DriverManager.getConnection(url, id, pw);
			// sql 작성 분석
			int num = 30;
			String name = "현아";
			String addr = "광주";
			int conNum = 2; // 조건 : 2번을 바꿈
			
//			String sql = "update test set num=10, name='서현', addr='목포' where num=1";
//			String sql = "update test set num=" + num +
//		             ", name='" + name + "'" +
//		             ", addr='" + addr + "'" +
//		             " where num=" + conNum;
			
			String sql = "update test set num=?, name=?, addr=? where num=?";

			ps = conn.prepareStatement(sql);		
			ps.setInt(1, num);
			ps.setString(2, name);
			ps.setString(3, addr);
			ps.setInt(4, conNum);
			
			// sql문 실행
			cnt = ps.executeUpdate();
			System.out.println("cnt :" + cnt);
			// 수정성공 : 1
			// 조건 X : 0
			
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			if(conn != null)
			if(ps != null);{
				try {
					ps.close();
				} catch (SQLException e) {
					
					e.printStackTrace();
				}
			}
			if(conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		


	}

}