JDBC :: 테이블 생성 및 데이터 조작 프로그램

프로그램 설명

DB에 STUDENT 테이블을 생성하고
커맨드로 입력한 데이터를 삽입, 수정, 삭제 및 조회를 하는 코드

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

public class TestAll {
	public static void main(String[] args) {
		
		final int INSERT = 1;
		final int SELECT = 2;
		final int UPDATE = 3;
		final int DELETE = 4;
		
		Scanner sc = new Scanner(System.in);
		
		Connection conn = null;		// 연결 정보 보관할 객체 선언
		Statement stmt = null;		// 쿼리 날릴 객체 선언
		ResultSet rs = null;
		
		System.out.println("테이블 생성");
		
		String sql = "";			// 쿼리 문자열 선언
		int result = 0;
		int menuNum = 0;
		int sNo = 0;
		String sName = "";
		Date sDate = null;
		
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			System.out.println("JDBC 접속 성공");
			
			// DB 연결정보
			conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "JDBC", "JDBC");
			stmt = conn.createStatement();
			
			sql = "CREATE TABLE STUDENT (" +
					"SNO INTEGER NOT NULL, " +
					"SNAME VARCHAR2(30), " +
					"SDATE DATE " +
					" )";
					
			result = stmt.executeUpdate(sql);
			if (result == 0) {
				conn.commit();
				System.out.println("테이블 생성됨");
				
				while(true) {
					
					System.out.println("메뉴 선택");
					System.out.println("1. 데이터 삽입");
					System.out.println("2. 전체조회");
					System.out.println("3. 데이터 수정");
					System.out.println("4. 데이터 삭제");
					System.out.print("메뉴 입력 : ");
					menuNum = sc.nextInt();
					sc.nextLine();
					
					switch(menuNum) {
						case INSERT :
							System.out.println("SWITCH :: INSERT");
							System.out.print("SNO 입력 : ");
							sNo = sc.nextInt();
							sc.nextLine();
							System.out.print("SNAME 입력 : ");
							sName = sc.nextLine();
							sql = "INSERT INTO STUDENT VALUES("+sNo+", '"+sName+"', SYSDATE)";
							result = stmt.executeUpdate(sql);
							if (result > 0) {
								conn.commit();
								System.out.println("INSERT :: COMMIT");
							}
							else {
								conn.rollback();
								System.out.println("INSERT :: ROLLBACK");
							}
							break;
						case SELECT :
							System.out.println("SWITCH :: SELECT");
							sql = "SELECT * FROM STUDENT";
							rs = stmt.executeQuery(sql);
							while(rs.next()) {
								sNo = rs.getInt("SNO");		// 순서 바꾸면 안됨
								sName = rs.getString("SNAME");
								sDate = rs.getDate("SDATE");
								System.out.println(sNo+", "+sName+", "+sDate);
							}
							System.out.println();
							break;
						case UPDATE :
							System.out.println("SWITCH :: UPDATE");
							System.out.print("수정할 SNO 입력 :");
							sNo = sc.nextInt();
							sc.nextLine();
							sName = sc.nextLine();
							System.out.print("수정할 SNAME 입력 :");
							
							sql = "UPDATE STUDENT SET SNAME='"+sName+
									"' WHERE SNO="+sNo;
							result = stmt.executeUpdate(sql);
							if (result > 0) {
								conn.commit();
								System.out.println("UPDATE :: COMMIT");
							}
							else {
								conn.rollback();
								System.out.println("UPDATE :: ROLLBACK");
							}
							break;
						case DELETE : 
							System.out.println("SWITCH :: DELETE");
							System.out.println("삭제할 회원 이름 : ");
							sName = sc.nextLine();
							sql = null;
							sql = "DELETE FROM STUDENT WHERE SNAME='"+sName+"'";
							result = stmt.executeUpdate(sql);
							if (result > 0) {
								conn.commit();
								System.out.println("DELETE :: COMMIT");
							}
							else {
								conn.rollback();
								System.out.println("DELETE :: ROLLBACK");
							}
							break;
					} // switch
				} // while
			} // if (result == 0)
			else {
				conn.rollback();
				System.out.println("롤백");
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
		// 7) 사용한 자원 반납 - 생성 역순
			try {
				if (rs !=null && stmt != null && conn != null)
				rs.close();
				stmt.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}