기타

• java_sql 실행할때, 실행하려는 문장+우클_execute current text(ctl+s)
• String url = "jdbc:oracle:thin:@127.0.0.1:1521:xe";
  ip계속 변경되는 환경이라면 로컬호스트로 작업하기

동적 SQL

• 실행시점에 사용자가 어떤 선택을 하느냐에 따라서 변경될 수 있는 SQL문을 의미

3.SQL문을 실행하기 위한 객체생성

Connection객체의 메소드를 이용해서 SQL문을 실행할 수 있는 객체를생성

[상속구조]
Statement ---> 정적 SQL을 실행할 때 사용(보안 취약)
↑
PreparedStatement ---> 동적 SQL을 실행할 때 사용(secure코딩에 적합, 캐시사용)
↑
CallableStatement ---> 각 DBMS에 특화된 SQL로 작성된 명령문을 실행(PL/SQL)

2) PreparedStatement객체를 이용

Connection객체가 갖고있는 prepareStatement메소드를 이용해서 생성
캐시사용
[sql실행]
<1> 쿼리문장을 분석
<2> 컴파일
<3> 실행

Statement는 SQL을 실행하는 과정에서 매번 3단계의 내용을 처리하고 있지만 prepareStatement는 한번만 작업하고 캐시에서 꺼내서 사용

prepareStatement객체가 sql문을 실행하는 방식은 SQL문을 미리 파싱해놓고 외부에서 입력받아야 하는 값들은 실행할때 전달받아서 sql문이 실행될 수 있도록 처리

(1) sql문을 미리 파싱할 수 있도록 외부에서 입력받아서 처리해야 하는 부분만 ?로 대체해서 sql문을 정의

String sql = "select * from member where id=? and pass=?"
=> ?에는 필드가 올 수 없고 무조건 값만 연결될 수 있다.(컬럼명은 ?로 처리못함)
=> 순수한 값만 올 수 있고 기호는 ?에 같이 연결해서 사용할 수 없다('?'는 인식못함)

(2) prepareStatement객체를 생성할 때 sql문을 매개변수로 넘긴다.

=> Connection의 prepareStatement 메소드를 호출할때 sql을 넘겨 sql문을 미리 파싱해놓도록 작업한다.
[형식]
PrepareStatement ptmt = con.prepareStatement(sql)

(3) ?에 대한 값을 셋팅

=> ?는 외부에서 입력받는 값을 셋팅해야하므로 메소드를 이용해서 설정
=> ?를 셋팅해야 하므로 setXX메소드가 많이 있다.
=> setXXX의 메소드와 오라클 타입은 ResultSet과 동일
ptmt.setXXXX(1, "jang") => 첫번째 ?에 "jang"을 대입하기
1=> ?의 순서

4. SQL실행

2) PrepareStatement객체사용

sql문을 미리 PrepareStatement객체를 만들면서 전달했으므로 메소드를 호출할때는 sql문을 전달하지 않는다.

(1) executeUpdate : insert, update, delete 명령문을 실행

int result = ptmt.executeUpdate()

(2) executeQuery : select 명령문을 실행

ResultSet rs = ptmt.executeQuery()

[DAO패턴을 적용해서 구현하기]

• Data Access object
• 비지니스로직과 DB연동 코드를 분리하기
• 하나의 테이블을 엑세스하는 기능을 하나의 클래스로 구현하기
  테이블명 +DAO
  ex. MemberDAOImpl
• DAO를 위한 인터페이스를 정의하고 작업하기(MemberDAO)

MemberSpringDAOImpl <- MemberService <-main

실습

기본문 PreparedStatement(동적SQL)

PreparedStatement(동적SQL)를 이용해서 insert문 실행하기

public class PreparedInsertTest {
	public static void main(String[] args) {
		PreparedInsertTest obj = new PreparedInsertTest();
		Scanner key = new Scanner(System.in);
		System.out.print("아이디:");
		String id = key.next();
		
		System.out.print("패스워드:");
		String pass = key.next();
		
		System.out.print("성명:");
		String name = key.next();
		
		System.out.print("주소:");
		String addr = key.next();
		
		System.out.print("기타정보:");
		String info = key.next();
		
		obj.insert(id, pass, name, addr, info);
	}
	public void insert(String id, String pass, String name, String addr, String info) {
		String url="jdbc:oracle:thin:@192.168.0.4:1521:xe";
		String user="scott";
		String password="tiger";
//		String sql="insert into member values('"+id+"','"+pass+"','"+name+"','"+addr+"',sysdate,10000,'"+info+"')";
		String sql="insert into member values(?,?,?,?,sysdate,10000,?)";
		try {
//			1.드라이버로딩
			Class.forName("oracle.jdbc.driver.OracleDriver");
			System.out.println("드라이버로딩성공!!");
//			2. 연결하기
			Connection con = DriverManager.getConnection(url, user, password);
			System.out.println("연결성공!!"+con);
//			3.SQL문을 실행하기 위한 객체를 만들기
//			Statement stmt = con.createStatement();
			
//			1. sql문이 미리 파싱될 수 있도록 PreparedStatement 객체 생성
			PreparedStatement ptmt = con.prepareStatement(sql);
			
//			2. ?에 값을 셋팅
			ptmt.setString(1, id);
			ptmt.setString(2, pass);
			ptmt.setString(3, name);
			ptmt.setString(4, addr);
			ptmt.setString(5, info);
			
			System.out.println("PreparedStatement객체 생성완료!!"+ptmt);
//			3. sql 실행 - sql문을 전달하지 않는다.
			int result = ptmt.executeUpdate();
			
//			5. 결과처리
			System.out.println(result+"개 행 삽입성공!!");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch(SQLException e) {
			e.printStackTrace();
		}
	}
}

PreparedStatement(동적 SQL)를 이용해서 update문 실행하기

public class PreparedUppdateTest {
	public static void main(String[] args) {
		PreparedUppdateTest obj = new PreparedUppdateTest();
		Scanner key = new Scanner(System.in);
		
		System.out.print("기존 주소:");
		String bad = key.next();
		
		System.out.print("변경할 주소:");
		String aad = key.next();
		
		obj.update(bad, aad);
	}
	public void update(String bad, String aad) {
		String url = "jdbc:oracle:thin:@192.168.0.4:1521:xe";
		String user = "scott";
		String password = "tiger";
//		String sql = "update member "+ "set addr='"+aad+"' where addr="+"'"+bad+"'";
		String sql = "update member set addr=? where addr=?";
		
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			
			Connection con =DriverManager.getConnection(url, user, password);
			
			PreparedStatement ptmt = con.prepareStatement(sql);
			ptmt.setString(1, aad);
			ptmt.setString(2, bad);
			
			ptmt.executeUpdate();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

PreparedStatement(동적 SQL)를 이용해서 delete문 실행하기

public class PreparedDeleteTest {
	public static void main(String[] args) {
		PreparedDeleteTest obj = new PreparedDeleteTest();
		
		Scanner key = new Scanner(System.in);
		System.out.println("삭제할 아이디:");
		String did = key.next();
		
		obj.delete(did);
	}
	public void delete(String did) {
		String url = "jdbc:oracle:thin:@192.168.0.4:1521:xe";
		String user = "scott";
		String password = "tiger";
		StringBuffer sql = new StringBuffer();
		sql.append("delete member ");
//		sql.append("where id='"+did+"'");
		sql.append("where id=?");
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			
			Connection con =DriverManager.getConnection(url, user, password);
			
			PreparedStatement ptmt = con.prepareStatement(sql.toString());
			ptmt.setString(1, did);
			ptmt.executeUpdate();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

PreparedStatement로 select

public class PreparedSelectTest {
	public static void main(String[] args) {
		PreparedSelectTest obj = new PreparedSelectTest();
		obj.select();
	}
	public void select() {
		String url = "jdbc:oracle:thin:@192.168.0.4:1521:xe";
		String user = "scott";
		String password = "tiger";
		String sql = "select * from member";
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			
			Connection con = DriverManager.getConnection(url, user, password);
			
			PreparedStatement ptmt = con.prepareStatement(sql);
			
			ResultSet rs = ptmt.executeQuery();
			System.out.println(rs);
			while(rs.next()) {
				System.out.print(rs.getString("id")+"\t");
				System.out.print(rs.getString("pass")+"\t");
				System.out.print(rs.getString("name")+"\t");
				System.out.print(rs.getString(4)+"\t");
				System.out.print(rs.getDate(5)+"\t");
				System.out.print(rs.getInt("point")+"\t");
				System.out.println(rs.getString(7));
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

PreparedStatement 활용

public class PreparedLoginTest {
	public static void main(String[] args) {
		PreparedLoginTest obj = new PreparedLoginTest();
		
		Scanner key = new Scanner(System.in);
		System.out.print("아이디:");
		String id = key.nextLine();
		
		System.out.print("패스워드:");
		String pass = key.nextLine();
		
		obj.login(id, pass);
	}
	public void login(String id,String pass) {
		String sql = "select * from member where id =? and pass=?";
		String url = "jdbc:oracle:thin:@192.168.0.4:1521:xe";
		String user = "scott";
		String password = "tiger";
		
		Connection con = null;
		PreparedStatement ptmt = null;
		ResultSet rs = null;
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			con = DriverManager.getConnection(url, user, password);
			ptmt = con.prepareStatement(sql);
			ptmt.setString(1, id);
			ptmt.setString(2, pass);
			rs = ptmt.executeQuery();
			
			if(rs.next()) {
				System.out.print(rs.getString("name")+"님 로그인 성공");
			}else {
				System.out.print("님 로그인 실패");
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				if(rs!=null) rs.close();
				if(ptmt!=null) ptmt.close();
				if(con!=null) con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

전날 exam + PreparedStatement(동적SQL) 활용

EmpSearchTest

이름에 입력한 글자가 속한 사람들의 정보를 출력하세요
이름철자:___

public class EmpSearchTest {
	public static void main(String[] args) {
		EmpSearchTest obj = new EmpSearchTest();
		Scanner key = new Scanner(System.in);
		System.out.println("검색할 컬럼 : ");
		String col = key.next();
		System.out.println("이름 철자 : ");
		String name = key.next();
		obj.search(col, name);
	}
	public void search(String col,String name) {
		String url = "jdbc:oracle:thin:@192.168.0.4:1521:xe";
		
		String user = "scott";
		String password = "tiger";
		String sql = "select * from emp where "+col+" like '%"+name+"%'";
		
		Connection con = null;
		Statement stm = null;
		ResultSet rs = null;
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			con = DriverManager.getConnection(url, user, password);
			stm = con.createStatement();
			rs = stm.executeQuery(sql);
			while(rs.next()){
				System.out.print(rs.getInt(1)+"\t");
				System.out.print(rs.getString(2)+"\t");
				System.out.print(rs.getString(3)+"\t");
				System.out.print(rs.getInt(4)+"\t");
				System.out.print(rs.getDate(5)+"\t");
				System.out.print(rs.getInt(6)+"\t");
				System.out.print(rs.getInt(7)+"\t");
				System.out.println(rs.getInt(8)+"\t");
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if(rs!=null) rs.close();
				if(stm!=null) stm.close();
				if(con!=null) con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

PreparedSearchTest

public class PreparedSearchTest {
	public static void main(String[] args) {
		PreparedSearchTest obj = new PreparedSearchTest();
		Scanner key = new Scanner(System.in);
		System.out.println("컬럼 : ");
		String col = key.next();
		System.out.println("이름 철자 : ");
		String name = key.next();
		obj.search(col,name);
	}
	public void search(String col,String name) {
		String url = "jdbc:oracle:thin:@192.168.0.4:1521:xe";
		
		String user = "scott";
		String password = "tiger";
		
		String sql="";
		//동적 SQL은 실행시점에 사용자가 어떤 선택을 하느냐에 따라서 변경될 수 있는 SQL문을 의미
		if(col.equals("job")) {
			sql = "select * from emp where job like ?";
		}else if(col.equals("ename")) {
			sql = "select * from emp where ename like ?";
		}
		
		Connection con = null;
		PreparedStatement psm = null;
		ResultSet rs = null;
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			con = DriverManager.getConnection(url, user, password);
			psm = con.prepareStatement(sql);
			psm.setString(1, "%"+name+"%");
			rs = psm.executeQuery();
			while(rs.next()){
				System.out.print(rs.getInt(1)+"\t");
				System.out.print(rs.getString(2)+"\t");
				System.out.print(rs.getString(3)+"\t");
				System.out.print(rs.getInt(4)+"\t");
				System.out.print(rs.getDate(5)+"\t");
				System.out.print(rs.getInt(6)+"\t");
				System.out.print(rs.getInt(7)+"\t");
				System.out.println(rs.getInt(8)+"\t");
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if(rs!=null) rs.close();
				if(psm!=null) psm.close();
				if(con!=null) con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

EmpReadTest

조회할 아이디를 입력받아 해당 아이디의 모든 정보를 출력하세요
아아디:__
[출력형태]
사번:__
성명:__
......

public class EmpReadTest {
	public static void main(String[] args) {
		EmpReadTest obj = new EmpReadTest();
		Scanner key = new Scanner(System.in);
		System.out.println("조회할 사번:");
		int empno = key.nextInt();
		obj.read(empno);
	}
	public void read(int empno) {
		String url = "jdbc:oracle:thin:@192.168.0.4:1521:xe";
		String user = "scott";
		String password = "tiger";
		String sql = "select * from emp where empno='"+empno+"'";
		
		Connection con = null;
		Statement stm = null;
		ResultSet rs = null;
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			con = DriverManager.getConnection(url, user, password);
			stm = con.createStatement();
			rs = stm.executeQuery(sql);
			while(rs.next()){
				System.out.println("사원번호 : "+rs.getInt("empno"));
				System.out.println("성명 : "+rs.getString("ename"));
				System.out.println("직무 : "+rs.getString("job"));
				System.out.println("관리자번호 : "+rs.getInt("mgr"));
				System.out.println("채용날짜 : "+rs.getDate("hiredate"));
				System.out.println("연봉 : "+rs.getInt("sal"));
				System.out.println("수당 : "+rs.getInt("comm"));
				System.out.println("부서코드 : "+rs.getInt("deptno"));
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if(rs!=null) rs.close();
				if(stm!=null) stm.close();
				if(con!=null) con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

PreparedReadTest

public class PreparedReadTest {
	public static void main(String[] args) {
		PreparedReadTest obj = new PreparedReadTest();
		Scanner key = new Scanner(System.in);
		System.out.println("조회할 사번:");
		int empno = key.nextInt();
		obj.read(empno);
	}
	public void read(int empno) {
		String url = "jdbc:oracle:thin:@192.168.0.4:1521:xe";
		String user = "scott";
		String password = "tiger";
		String sql = "select * from emp where empno=?";
		
		Connection con = null;
		PreparedStatement psm = null;
		ResultSet rs = null;
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			con = DriverManager.getConnection(url, user, password);
			psm = con.prepareStatement(sql);
			psm.setInt(1, empno);
			rs = psm.executeQuery();
			while(rs.next()){
				System.out.println("사원번호 : "+rs.getInt("empno"));
				System.out.println("성명 : "+rs.getString("ename"));
				System.out.println("직무 : "+rs.getString("job"));
				System.out.println("관리자번호 : "+rs.getInt("mgr"));
				System.out.println("채용날짜 : "+rs.getDate("hiredate"));
				System.out.println("연봉 : "+rs.getInt("sal"));
				System.out.println("수당 : "+rs.getInt("comm"));
				System.out.println("부서코드 : "+rs.getInt("deptno"));
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if(rs!=null) rs.close();
				if(psm!=null) psm.close();
				if(con!=null) con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

step.1

MemberSpringDAOImpl <- MemberService <-main

class Main

public class Main {
	public static void main(String[] args) {
		MemberService service = new MemberService();
		MemberMybatisDAOImpl dao = new MemberMybatisDAOImpl();
		service.serviceInsert(dao);
		service.serviceSelect(dao);
	}
}

MemberService

public class MemberService {
	public void serviceInsert(MemberDAO dao) {
		dao.insert();
	}
	public void serviceSelect(MemberDAO dao) {
		dao.select();
	}
}

interface MemberDAO

public interface MemberDAO {
	void insert();
	void select();
	void update();
	void delete();
}

MemberSpringDAOImpl implements MemberDAO

public class MemberSpringDAOImpl implements MemberDAO {
	public void insert() {
		System.out.println("spring jdbc로 insert하기");
	}
	public void select() {
		System.out.println("spring jdbc로 select하기");
	}
	@Override
	public void update() {
		System.out.println("spring jdbc로 update하기");
	}
	@Override
	public void delete() {
		System.out.println("spring jdbc로 delete하기");
	}
}

MemberMybatisDAOImpl implements MemberDAO

Mybatis로 member테이블에 엑세스하는 기능 - member테이블에 CLRUD하는 기능을 수행하는 클래스

public class MemberMybatisDAOImpl implements MemberDAO {
	public void insert() {
		System.out.println("spring Mybatis로 insert하기");
	}
	public void select() {
		System.out.println("spring Mybatis로 select하기");
	}
	@Override
	public void update() {
		System.out.println("spring Mybatis로 update하기");
	}
	@Override
	public void delete() {
		System.out.println("spring Mybatis로 delete하기");
	}
}

step2

DAOTest : 서비스 실행하는 곳

public class DAOTest {
	public static void main(String[] args) {
		Scanner key = new Scanner(System.in);
		System.out.println("******인사관리시스템********");
		System.out.println("1. 사원등록");
		System.out.println("2. 사원조회");
		System.out.println("3. 사원수정");
		System.out.println("4. 사원퇴사");
		System.out.println("5. 주소로 사원 검색");
		System.out.print("원하는 작업을 선택하세요:");
		int choice  = key.nextInt();
		show(choice);
	}
	public static void show(int choice){
		MenuUI ui = new MenuUI();
		switch(choice){
			case 1:
				ui.insertMenu();
				break;
			case 2:
				ui.selectMenu();
				break;
			case 3:
				ui.updateMenu();
				break;
			case 4:
				ui.deleteMenu();
				break;
			case 5:
				ui.findByAddrMenu();
				break;
		}
	}
}

DBUtil : 공통작업 처리

DB연동을 위해서 모든 메소드에서 처리할 일들, 모든 메소드에서 공통으로 처리하는 부분을 구현

1. 드라이버로딩

• 한 번 실행
• static블럭으로 처리

2. DB서버연결

• 메소드마다 항상 서버에 먼저 연결하고 sql문을 실행해야한다.
• 메소드로 정의하기
• Connection을 리턴하는 메소드를 정의
• static메소드로 정의

3. 자원반납

• 메소드마다 작업이 끝나면 모든 자원을 반납하도록 처리
• 메소드로 정의하기
• ResultSet, Statement, Connection순으로 자원을 반납해야한다.
• 각각의 자원을 반납하도록 메소드를 따로 만들거나 하나로 만들어서 작업
• static메소드로 정의

public class DBUtil {
	//드라이버 로딩
	//=>클래스가 로딩될 때 한 번만 실행된다.
    //(여러번 로딩한다고 오류가 나진 않지만 리소스가 손실됨)
	static {
		System.out.println("스태틱블럭");
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	//DB서버 접속하기
	public static Connection getConnect() {
		Connection con = null;
		String url="jdbc:oracle:thin:@192.168.0.4:1521:xe";
		String user="scott";
		String password="tiger";
		try {
			con = DriverManager.getConnection(url, user, password);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return con;
	}
	//자원반납
	public static void close(ResultSet rs, Statement stmt, Connection con) {
		try {
			if(rs!=null) rs.close();
			if(stmt!=null) stmt.close();
			if(con!=null) con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

MemberDAOImpl implements MemberDAO

public class MemberDAOImpl implements MemberDAO {
	public void insert(String id, String pass, String name, String addr, String info) {
		String sql="insert into member values(?,?,?,?,sysdate,10000,?)";
		Connection con = null;
		PreparedStatement ptmt = null;
		try {
			con = DBUtil.getConnect();
			ptmt = con.prepareStatement(sql);
			
			ptmt.setString(1, id);
			ptmt.setString(2, pass);
			ptmt.setString(3, name);
			ptmt.setString(4, addr);
			ptmt.setString(5, info);
			
			int result = ptmt.executeUpdate();
			System.out.println(result+"개 행 삽입성공!!");
		} catch(SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(null, ptmt, con);
		}
	}
	
	public void update(String bad, String aad) {
		String sql = "update member set addr=? where addr=?";
		Connection con = null;
		PreparedStatement ptmt = null;
		try {
			con = DBUtil.getConnect();
			ptmt = con.prepareStatement(sql);
			ptmt.setString(1, aad);
			ptmt.setString(2, bad);
			
			ptmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(null, ptmt, con);
		}
	}
	
	public void delete(String did) {
		StringBuffer sql = new StringBuffer(); 
		sql.append("delete member ");
		sql.append("where id=?");
		Connection con = null;
		PreparedStatement ptmt = null;
		try {
			con = DBUtil.getConnect();
			
			ptmt = con.prepareStatement(sql.toString());
			ptmt.setString(1, did);
			ptmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(null, ptmt, con);
		}
	}
	
	public void select() {
		String sql = "select * from member";
		Connection con = null;
		PreparedStatement ptmt = null;
		ResultSet rs = null;
		try {
			con = DBUtil.getConnect();
			ptmt = con.prepareStatement(sql);
			rs = ptmt.executeQuery();
			System.out.println(rs);
			while(rs.next()) {
				System.out.print(rs.getString("id")+"\t");
				System.out.print(rs.getString("pass")+"\t");
				System.out.print(rs.getString("name")+"\t");
				System.out.print(rs.getString(4)+"\t");
				System.out.print(rs.getDate(5)+"\t");
				System.out.print(rs.getInt("point")+"\t");
				System.out.println(rs.getString(7));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs, ptmt, con);
		}
	}

	public void login(String id,String pass) {
		String sql = "select * from member where id =? and pass=?";
		Connection con = null;
		PreparedStatement ptmt = null;
		ResultSet rs = null;
		try {
			con = DBUtil.getConnect();
			ptmt = con.prepareStatement(sql);
			ptmt.setString(1, id);
			ptmt.setString(2, pass);
			rs = ptmt.executeQuery();
			
			if(rs.next()) {
				System.out.print(rs.getString("name")+"님 로그인 성공");
			}else {
				System.out.print("님 로그인 실패");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ptmt, con);
		}
	}
	
	public void search(String col,String name) {
		String sql="";
		if(col.equals("job")) {
			sql = "select * from emp where job like ?";
		}else if(col.equals("ename")) {
			sql = "select * from emp where ename like ?";
		}
		
		Connection con = null;
		PreparedStatement ptmt = null;
		ResultSet rs = null;
		try {
			con = DBUtil.getConnect();
			ptmt = con.prepareStatement(sql);
			ptmt.setString(1, "%"+name+"%");
			rs = ptmt.executeQuery();
			while(rs.next()){
				System.out.print(rs.getInt(1)+"\t");
				System.out.print(rs.getString(2)+"\t");
				System.out.print(rs.getString(3)+"\t");
				System.out.print(rs.getInt(4)+"\t");
				System.out.print(rs.getDate(5)+"\t");
				System.out.print(rs.getInt(6)+"\t");
				System.out.print(rs.getInt(7)+"\t");
				System.out.println(rs.getInt(8)+"\t");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs, ptmt, con);
		}
	}
}

MemberDAO : 상속용 인터페이스

public interface MemberDAO {
	void insert(String id, String pass, String name, String addr, String info);
	void update(String bad, String aad);
	void delete(String did);
	void select();
	void login(String id,String pass);
	void search(String col,String name);
}

MenuUI : 메소드 호출

public class MenuUI {
	Scanner key = new Scanner(System.in);
	MemberDAO dao = new MemberDAOImpl();
	public void insertMenu(){
		System.out.println("*******사원등록********");
		System.out.print("아이디:");
		String id = key.next();
		System.out.print("패스워드:");
		String pass = key.next();
		System.out.print("성명:");
		String name = key.next();
		System.out.print("주소:");
		String addr = key.next();
		System.out.print("기타:");
		String info = key.next();
		//여기에서 MemberDAO의 메소드를 호출하세요
		dao.insert(id, pass, name, addr, info);
	}
	public void updateMenu(){
		System.out.println("*******사원수정********");
		System.out.print("변경할 주소:");
		String addr1 = key.next();
		System.out.print("조건으로 검색할 주소:");
		String addr2 = key.next();
		//여기에서 MemberDAO의 메소드를 호출하세요
		dao.update(addr2, addr1);
	}
	public void deleteMenu(){
		System.out.println("*******사원삭제********");
		System.out.print("삭제할id:");
		String id = key.next();
		//여기에서 MemberDAO의 메소드를 호출하세요
		dao.delete(id);
	}
	public void findByAddrMenu(){
		System.out.println("*******사원검색********");
		System.out.print("주소:");
		String addr = key.next();
		//여기에서 MemberDAO의 메소드를 호출하세요
		dao.search("addr", addr);
	}
	
	public void selectMenu(){
		System.out.println("*******사원조회********");
		//여기에서MemberDAO의 메소드를 호출하세요 - 전체사원조회
		dao.select();
	}
}

exam.DAO

과제설명링크

DAOTest

public class DAOTest {
	public static void main(String[] args) {
		Scanner key = new Scanner(System.in);
		System.out.println("******인사관리시스템********");
		System.out.println("1. 사원등록");
		System.out.println("2. 전체사원조회");
		System.out.println("3. job별로 사원조회하기");
		System.out.println("4. SMITH부서의 전 사원의 급여를 500올려주기");
		System.out.println("5. comm이 0인 직원 삭제하기");
		System.out.println("6. 각 매니저의 정보 조회하기(매니저 아이디로 조회하기)");
		System.out.print("원하는 작업을 선택하세요:");
		int choice  = key.nextInt();
		show(choice);
	}
	public static void show(int choice){
		MenuUI ui = new MenuUI();
		switch(choice){
			case 1:
				ui.insertMenu();
				break;
			case 2:
				ui.selectMenu();
				break;
			case 3:
				ui.searchMenu();
				break;
			case 4:
				ui.updateMenu();
				break;
			case 5:
				ui.deleteMenu();
				break;
			case 6:
				ui.getInfoMenu();
				break;
		}
	}
}

DBUtil

public class DBUtil {
	static {
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	public static Connection getConection() {
		Connection con = null;
		String url = "jdbc:oracle:thin:@192.168.0.4:1521:xe";
		String user = "scott";
		String password = "tiger";
		try {
			con = DriverManager.getConnection(url, user, password);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return con;
	}
	public static void close(ResultSet rs, PreparedStatement psm, Connection con) {
		try {
			if(rs!=null) rs.close();
			if(psm!=null) psm.close();
			if(con!=null) con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

EmpDAOImpl implements EmpDAO

public class EmpDAOImpl implements EmpDAO {
	public void insert(int empno, String ename, String job, int mgr, int deptno) {
		String sql = "insert into emp values(?,?,?,?, sysdate, 3000, 0, ?)";
		Connection con = null;
		PreparedStatement psm = null;
		try {
			con = DBUtil.getConection();
			psm = con.prepareStatement(sql);
			
			psm.setInt(1, empno);
			psm.setString(2, ename);
			psm.setString(3, job);
			psm.setInt(4, mgr);
			psm.setInt(5, deptno);
			
			psm.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(null, psm, con);
		}
	}
	public void select() {
		String sql = "select * from emp";
		Connection con = null;
		PreparedStatement psm = null;
		ResultSet rs = null;
		try {
			con = DBUtil.getConection();
			psm = con.prepareStatement(sql);
			rs = psm.executeQuery();
			while(rs.next()) {
				System.out.print(rs.getInt(1)+"\t");
				System.out.print(rs.getString(2)+"\t");
				System.out.print(rs.getString(3)+"\t");
				System.out.print(rs.getInt(4)+"\t");
				System.out.print(rs.getDate(5)+"\t");
				System.out.print(rs.getInt(6)+"\t");
				System.out.print(rs.getInt(7)+"\t");
				System.out.println(rs.getInt(8)+"\t");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs, psm, con);
		}
	}
	public void search(String job) {
		String sql="select * from emp where job=?";
		
		Connection con = null;
		PreparedStatement psm = null;
		ResultSet rs = null;
		try {
			con = DBUtil.getConection();
			psm = con.prepareStatement(sql);
			psm.setString(1, job);
			rs = psm.executeQuery();
			while(rs.next()){
				System.out.print(rs.getInt(1)+"\t");
				System.out.print(rs.getString(2)+"\t");
				System.out.print(rs.getString(3)+"\t");
				System.out.print(rs.getInt(4)+"\t");
				System.out.print(rs.getDate(5)+"\t");
				System.out.print(rs.getInt(6)+"\t");
				System.out.print(rs.getInt(7)+"\t");
				System.out.println(rs.getInt(8)+"\t");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs, psm, con);
		}
	}
	public void update(int sal, String ename) {
		String sql = "update emp set sal=sal+? where deptno=(select deptno from emp where ename=?)";
		
		Connection con = null;
		PreparedStatement psm = null;
		try {
			con = DBUtil.getConection();
			psm = con.prepareStatement(sql);
			psm.setInt(1, sal);
			psm.setString(2, ename);
			int result = psm.executeUpdate();
			System.out.println(result);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(null, psm, con);
		}
	}
	public void delete(int comm) {
		String sql = "delete emp where comm=?";
		Connection con = null;
		PreparedStatement psm = null;
		try {
			con = DBUtil.getConection();
			psm = con.prepareStatement(sql);
			psm.setInt(1, comm);
			psm.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(null, psm, con);
		}
	}
	public void read(int mgr) {
		String sql = "select * from emp where mgr=?";
		
		Connection con = null;
		PreparedStatement psm = null;
		ResultSet rs = null;
		try {
			con = DBUtil.getConection();
			psm = con.prepareStatement(sql);
			psm.setInt(1, mgr);
			rs = psm.executeQuery();
			while(rs.next()){
				System.out.println("사원번호 : "+rs.getInt("empno"));
				System.out.println("성명 : "+rs.getString("ename"));
				System.out.println("직무 : "+rs.getString("job"));
				System.out.println("관리자번호 : "+rs.getInt("mgr"));
				System.out.println("채용날짜 : "+rs.getDate("hiredate"));
				System.out.println("연봉 : "+rs.getInt("sal"));
				System.out.println("수당 : "+rs.getInt("comm"));
				System.out.println("부서코드 : "+rs.getInt("deptno"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs, psm, con);
		}
	}
}

EmpDAO

public interface EmpDAO {
	void insert(int empno, String ename, String job, int mgr, int deptno);
	void select();
	void search(String job);
	void update(int sal, String ename);
	void delete(int comm);
	void read(int mgr);
}

MenuUI

public class MenuUI {
	Scanner key = new Scanner(System.in);
	EmpDAO dao = new EmpDAOImpl();
	public void insertMenu() {
		System.out.print("사원번호 : ");
		int empno = key.nextInt();
		System.out.print("이름 : ");
		String ename = key.next();
		System.out.print("직무 : ");
		String job = key.next();
		System.out.print("매니저아이디 : ");
		int mgr = key.nextInt();
		System.out.print("부서번호 : ");
		int deptno = key.nextInt();
		
		dao.insert(empno, ename, job, mgr, deptno);
	}
	public void selectMenu(){
		dao.select();
	}
	public void searchMenu(){
		System.out.println("job : ");
		String job = key.next();
		dao.search(job);
	}
	public void updateMenu(){
		System.out.print("급여 : ");
		int sal= key.nextInt();
		System.out.print("이름 : ");
		String ename= key.next();
		dao.update(sal, ename);
	}
	public void deleteMenu(){
		System.out.print("수당 : ");
		int comm= key.nextInt();
		dao.delete(comm);
	}
	public void getInfoMenu(){
		System.out.print("관리자번호 : ");
		int mgr= key.nextInt();
		dao.read(mgr);
	}
}

본 포스팅은 멀티캠퍼스의 멀티잇 백엔드 개발(Java)의 교육을 수강하고 작성되었습니다.