[오라클 실습] DBOracle, DBConnection

DBOracle

package DB;

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

public class DBOracle {

	public static void main(String[] args) {
		Connection conn = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		
		try {
			String base_ym;
			String eidx_var_nm;
			String drv_var_yn;
			double eidx_itm_val;
			String unt;
			java.sql.Date upd_dt;
			String cemno;
			String result;
			
			int num = 0;
						
			String query = "SELECT * FROM TKG486_IEW_MCRO_EIDX_STRD WHERE BASE_YM=202203";
			
			conn = DBConnection.getConnection();
			pstm = conn.prepareStatement(query);
			rs = pstm.executeQuery();
			
			System.out.println("BASE_YM EIDX_VAR_NM DRV_VAR_YN EIDX_ITM_VAL \tUNI\tUPD_DT  CEM_NO");
			System.out.println("=========================================================================");
			
			while(rs.next()) {
				base_ym = rs.getString(1);
				eidx_var_nm = rs.getString(2);
				drv_var_yn = rs.getString(3);
				eidx_itm_val = rs.getDouble(4);
				unt = rs.getString(5);
				upd_dt = rs.getDate(6);
				cemno = rs.getString(7);
				
				result = base_ym+" \t"+eidx_var_nm+"\t"+drv_var_yn+"\t"+eidx_itm_val+"\t"+unt+"\t"+upd_dt+"\t"+cemno;
				System.out.println(result);
				
			}
		}catch(SQLException sqle) {
			System.out.println("SELECT문에서 예외 발생");
			sqle.printStackTrace();
		}finally {
			try {
				if(rs != null) {rs.close();}
				if(pstm != null) {pstm.close();}
				if(conn != null) {conn.close();}
			}catch(Exception e) {
				throw new RuntimeException(e.getMessage());
			}
		}
	}
}

DBOracle (2)

package DB;

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

public class DBOracle {

	public static void main(String[] args) {
		Connection conn = null;
		PreparedStatement pstm = null;
		PreparedStatement pstm2 = null;
		ResultSet rs = null;
		ResultSet rs2 = null;
		
		try {
			Scanner sc = new Scanner(System.in);
			conn = DBConnection.getConnection();
			String base_ym;
			String eidx_var_nm;
			String drv_var_yn;
			double eidx_itm_val;
			String unt;
			java.sql.Date upd_dt;
			String cemno;
			String result;

			System.out.print("BASE_YM 입력받기: ");
			String input = sc.next();

			String query = "UPDATE TKG486_IEW_MCRO_EIDX_STRD "
							+ "SET EIDX_ITM_VAL=EIDX_ITM_VAL*100"
							+ ", UPD_DT=TO_CHAR(SYSDATE, 'YYYYMMDD')"
							+"WHERE BASE_YM="+input+""
							+ "and DRV_VAR_YN IN ('Y','N')" ;
			
			pstm = conn.prepareStatement(query);
			rs = pstm.executeQuery( );
			
			System.out.println("\nBASE_YM\tEIDX_ITM_VAL\tUPD_DT");
			System.out.println("==============================");
			
			String query2 = "SELECT BASE_YM,EIDX_ITM_VAL,UPD_DT FROM TKG486_IEW_MCRO_EIDX_STRD ";
			pstm2 = conn.prepareStatement(query2);
			rs2 = pstm2.executeQuery( );
			
			while(rs2.next()) {
				base_ym = rs2.getString(1);
				eidx_itm_val = rs2.getDouble(2);
				upd_dt = rs2.getDate(3);
				result = base_ym+"\t"+eidx_itm_val+"\t"+upd_dt;
				System.out.println(result);
			}
			
		}catch(SQLException sqle) {
			System.out.println("SELECT문에서 예외 발생");
			sqle.printStackTrace();
		}finally {
			try {
				if(rs != null) {rs.close();}
				if(rs2 != null) {rs2.close();}
				if(pstm != null) {pstm.close();}
				if(conn != null) {conn.close();}
			}catch(Exception e) {
				throw new RuntimeException(e.getMessage());
			}
		}
	}
}

 

 

DBConnection

package DB;

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

public class DBConnection {
	public static Connection dbConn;
	public static Connection getConnection() {
		Connection conn = null;
		try {
			String user = "c##scott";
			String pw = "tiger";
			String url = "jdbc:oracle:thin:@localhost:1521:oracle";
			
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection(url, user, pw);
			
			System.out.println("Database에 연결되었습니다.\n");
		}catch(ClassNotFoundException cnfe) {
			System.out.println("DB 드라이버 로딩 실패 :"+cnfe.toString());
		}catch(SQLException sqle) {
			System.out.println("DB 접속실패 :"+sqle.toString());
		}catch(Exception e) {
			System.out.println("Unknown error");
			e.printStackTrace();
		}
		return conn;
	}

}