Kosta 클라우드 네이티브 애플리케이션 개발 (CNA) 교육 20일차

JAVA

mydb.properties

maria.DB_URL = jdbc:mariadb://localhost:3306/mydb
maria.DB_ID = it
maria.DB_PW = 0000

oracle.DB_URL = jdbc:oracle:thin:@localhost:1521:XE
#oracle.DB_URL = jdbc:oracle:thin:@192.168.0.40:1521:XE
oracle.DB_ID = it
oracle.DB_PW = 0000

 

DBManager

package com.myjdbc;

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

public interface DBManager {
	public Connection connect();
	public void close(Connection conn, PreparedStatement pstmt, ResultSet rs);
	public void close(Connection conn, PreparedStatement pstmt);
	
}

 

MariaDBManager

package com.myjdbc;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

public class MariaDBManager implements DBManager {
	
	private String DB_URL;
	private String DB_ID;
	
	private String DB_PW;
	
	private static final Properties props = new Properties();
	private static MariaDBManager instance;
	
	 // private 생성자
    private MariaDBManager() {
        try {
            props.load(MariaDBManager.class.getClassLoader().getResourceAsStream("mydb.properties"));
            DB_URL = props.getProperty("maria.DB_URL");
            DB_ID = props.getProperty("maria.DB_ID");
            DB_PW = props.getProperty("maria.DB_PW");
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    
    // 싱글톤 인스턴스 반환 메소드
    public static MariaDBManager getInstance() {
        if (instance == null) {
            synchronized (MariaDBManager.class) {
                if (instance == null) {
                    instance = new MariaDBManager();
                }
            }
        }
        return instance;
    }
	
	@Override
	public Connection connect() {
		Connection conn = null;
		try {
			Properties props = new Properties();
			props.load(OracleDBManager.class.getClassLoader().getResourceAsStream("mydb.properties"));
					
			Class.forName("org.mariadb.jdbc.Driver");
			conn = DriverManager.getConnection(DB_URL, DB_ID, DB_PW);
			
			if(conn != null)
				System.out.println("마리아 연결성공");
			else
				System.out.println("마리아 연결실패");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return conn;
	}
	
	@Override
	public void close(Connection conn, PreparedStatement pstmt, ResultSet rs) {
		try {
			if(rs != null) 		rs.close();
			if(pstmt != null)   pstmt.close();
			if(conn != null)    conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	@Override
	public void close(Connection conn, PreparedStatement pstmt) {
		try {
			if(pstmt != null)   pstmt.close();
			if(conn != null)    conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	

}

 

OracleDBManager

package com.myjdbc;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

public class OracleDBManager implements DBManager {
	
    private String DB_URL;
    private String DB_ID;
    private String DB_PW;
    
    private static final Properties props = new Properties();
    private static OracleDBManager instance;
    
    // private 생성자
    private OracleDBManager() {
        try {
            props.load(OracleDBManager.class.getClassLoader().getResourceAsStream("mydb.properties"));
            DB_URL = props.getProperty("oracle.DB_URL");
            DB_ID = props.getProperty("oracle.DB_ID");
            DB_PW = props.getProperty("oracle.DB_PW");
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    
    // 싱글톤 인스턴스 반환 메소드
    public static OracleDBManager getInstance() {
        if (instance == null) {
            synchronized (OracleDBManager.class) {
                if (instance == null) {
                    instance = new OracleDBManager();
                }
            }
        }
        return instance;
    }
    
    @Override
    public Connection connect() {
        Connection conn = null;
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            conn = DriverManager.getConnection(DB_URL, DB_ID, DB_PW);
            
            if(conn != null)
                System.out.println("오라클 연결성공");
            else
                System.out.println("오라클 연결실패");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }
    
    @Override
    public void close(Connection conn, PreparedStatement pstmt, ResultSet rs) {
        try {
            if(rs != null)      rs.close();
            if(pstmt != null)   pstmt.close();
            if(conn != null)    conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    
    @Override
    public void close(Connection conn, PreparedStatement pstmt) {
        try {
            if(pstmt != null)   pstmt.close();
            if(conn != null)    conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

 

EmpVO

package com.myjdbc;

// VO(Value Object) : 값을 넣고  빼는 역할을 하는 클래스

public class EmpVO {  
	private int empno;
	private String ename;
	private int sal;
	private int deptno;
	
	
	public EmpVO() {  }
	
	public EmpVO(int empno, String ename, int sal, int deptno) {
		//super();
		this.empno = empno;
		this.ename = ename;
		this.sal = sal;
		this.deptno = deptno;
		
	}

	public int getEmpno() {
		return empno;
	}

	public void setEmpno(int empno) {
		this.empno = empno;
	}

	public String getEname() {
		return ename;
	}

	public void setEname(String ename) {
		this.ename = ename;
	}

	public int getSal() {
		return sal;
	}

	public void setSal(int sal) {
		this.sal = sal;
	}

	public int getDeptno() {
		return deptno;
	}

	public void setDeptno(int deptno) {
		this.deptno = deptno;
	}

	@Override
	public String toString() {
		return "EmpVO [empno=" + empno + ", ename=" + ename + ", sal=" + sal + ", deptno=" + deptno + "]";
	}
	
}

 

 

EmpDAO

package com.myjdbc;

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

public class EmpDAO {
	
	
	//--------------------------------------------------------------------------------
	public ArrayList<EmpVO> select() {
	
		ArrayList<EmpVO> alist = new ArrayList<EmpVO>();
		
		DBManager dbm = MariaDBManager.getInstance();  		//new MariaDBManager();
		//DBManager dbm = OracleDBManager.getInstance();  	//new OracleDBManager();
		Connection conn = dbm.connect();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			
			String sql = "select * from emp";
			pstmt =  conn.prepareStatement(sql);
			rs = pstmt.executeQuery();  
			while(rs.next()) {
				EmpVO vo = new EmpVO();
				vo.setEmpno(  rs.getInt("empno")     );
				vo.setEname(  rs.getString("ename")  );
				alist.add(vo);
			}	
		} catch (SQLException e) {
			e.printStackTrace();
		}	finally {
				dbm.close(conn, pstmt, rs);
		}
		return alist;
	}
	//--------------------------------------------------------------------------------
	
	
	public EmpVO select(int empnoParam) {
		EmpVO vo = new EmpVO();
		
		//DBManager dbm = OracleDBManager.getInstance();    //new OracleDBManager();
		DBManager dbm = MariaDBManager.getInstance();  		//new MariaDBManager();
		Connection conn = dbm.connect();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			String sql = "select * from emp where empno=?";
			pstmt =  conn.prepareStatement(sql);
			pstmt.setInt(1, empnoParam);	//------파라미터를 1번째?에 바인딩
			
			rs = pstmt.executeQuery();  
			rs.next();
			vo.setEmpno(  rs.getInt("empno")     );
			vo.setEname(  rs.getString("ename")  );
		} catch (SQLException e) {
			e.printStackTrace();
		}	finally {
			dbm.close(conn, pstmt, rs);
		}
		return vo;
	}
	
	public int insert(int empnoParam, String enameParam, int deptnoParam) {
		DBManager dbm  = OracleDBManager.getInstance();  //new OracleDBManager();
		Connection conn = dbm.connect();
		PreparedStatement pstmt = null;
		int rows = 0;
		try {
			String sql = "insert into emp(empno, ename, deptno) values(?,?,?)";
			pstmt =  conn.prepareStatement(sql);
			pstmt.setInt(1, empnoParam);	//------파라미터를 1번째?에 바인딩
			pstmt.setString(2, enameParam);
			pstmt.setInt(3, deptnoParam);
			rows = pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}	finally {
			dbm.close(conn, pstmt);
		}
		return rows;
	}
	
	
	public int update(int empnoParam, int salParam) {
		DBManager dbm  = OracleDBManager.getInstance();  //new OracleDBManager();
		Connection conn = dbm.connect();
		PreparedStatement pstmt = null;
		int rows = 0;
		try {
			String sql = "update emp set sal=? where empno=?";
			pstmt =  conn.prepareStatement(sql);
			pstmt.setInt(1, salParam);	//------파라미터를 1번째?에 바인딩
			pstmt.setInt(2, empnoParam);
			rows = pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}	finally {
			dbm.close(conn, pstmt);
		}
		return rows;
	}
	
	public int delete(int empnoParam) {
		DBManager dbm  = OracleDBManager.getInstance();  //new OracleDBManager();
		Connection conn = dbm.connect();
		PreparedStatement pstmt = null;
		int rows = 0;
		try {
			String sql = "delete from emp where empno=?";
			pstmt =  conn.prepareStatement(sql);
			pstmt.setInt(1, empnoParam);	//------파라미터를 1번째?에 바인딩
			rows = pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}	finally {
			dbm.close(conn, pstmt);
		}
		return rows;
	}	
	
	// --------- 호출 ---------
	public static void main(String[] args) throws InterruptedException  {
		
		EmpDAO o = new EmpDAO();
		
		ArrayList<EmpVO> res2 = o.select();
		for(EmpVO evo : res2)
			System.out.println(evo.toString());
		
		
		System.out.println("----------");
		
		
		
		EmpVO vo = o.select(7876);
		System.out.println("호출결과1:" + vo.toString() + "\n");
		
		
		
//	
//		
//		int res3 = o.insert(7733, "JAVA", 10);
//		System.out.println("호출결과3:" + res3 + "\n");
//	
//		
//		int res4 = o.update(7733, 9999);
//		System.out.println("호출결과4:" + res4 + "\n");
//		
//		
//		int res5 = o.delete(7733);
//		System.out.println("호출결과5:" + res5 + "\n");
//		
	}
	
	
		
	
}