SQL응용 JDBC (SELECT, INSERT, UPDATE)

오라클 JDBC 연동하여 

 

**execute() : DDL 실행

**executeQuery() : SELECT

**executeUpdate() : INSERT , UPDATE, DELETE

 

** SELECT 문 .

public class Example {

	public void select(Connection con,Statement st,ResultSet rs) {
		String sql = "SELECT * FROM EMP_TBL";
		try {
			st = con.createStatement();
			rs = st.executeQuery(sql);
			while(rs.next()==true) {
				String value = rs.getString("ENAME");
				System.out.println("ENAME: "+value);
			}
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				if(rs!=null) rs.close();
				if(st!=null) st.close();
			}catch(SQLException e) {
				e.printStackTrace();
			}
		}
	}

**INSERT 문 ( boolean 사용 )

	public boolean insert(Connection con,Statement st) {
		boolean result = false;
		String sql="INSERT INTO EMP_TBL(EMPNO,ENAME) VALUES (10000,'김자바')";
		try {
			st =con.createStatement();
			int row = st.executeUpdate(sql);
			if(row>=1) {
				System.out.println("INSERT완료");
				return true;
			}else {
				System.out.println("INSERT실패");
			}
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				if(st!=null) st.close();
			}catch(SQLException e) {
				e.printStackTrace();
			}
		}
		return false;
	}

**INSERT ( String )  , DELETE 문자열을 넣어주면 DELETE.

--매개변수로 문자열을 가지고오기때문에
--메서드 안에서 jdbc 연결을 해주어야한다.
    public void insertString(String all) {
		Connection con = null;
		Statement st = null;
		try { 		
			Class.forName("oracle.jdbc.driver.OracleDriver");
			con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "user2", "1234");
			st = con.createStatement();
			int row= st.executeUpdate(all);
			if(row>=1) {
				System.out.println("INSERT완료");
			}else {
				System.out.println("INSERT실패");
			}
		}catch(ClassNotFoundException e) {
			e.printStackTrace();
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				if(st!=null) st.close();
				if(con!=null) con.close();
			}catch(SQLException e) {
				e.printStackTrace();
			}
		}
	}

 

**UPDATE ( boolean 사용 )

 

	public boolean update(Connection con,Statement st) {
		boolean result = false;
		String sql="UPDATE EMP_TBL SET ENAME='김JAVA' WHERE EMPNO=10000";
		try {
			st = con.createStatement();
			int row = st.executeUpdate(sql);
			if(row==1) {
				System.out.println("UPDATE완료");
			}else {
				System.out.println("UPDATE실패");
			}
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				if(st != null) st.close();
			}catch(SQLException e) {
				e.printStackTrace();
			}
		}
		return result;
	}

 

 

 

 

*******메인메서드. 실행부

	public static void main(String[] args) {
		
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		Example ex = new Example();
		
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			System.out.println("드라이버연결성공");
			connection=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "user2", "1234");
			System.out.println("오라클DB연결성공");
			
			//ex.select(connection, statement, resultSet);
			//ex.insert(connection, statement);
			//ex.insertString("INSERT INTO EMP_TBL(EMPNO,ENAME) VALUES (10001 ,'김JDBC')");
			ex.update(connection, statement);
						
		}catch(ClassNotFoundException e) {
			e.printStackTrace();
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				if(connection !=null) connection.close();		
			}catch(SQLException e) {
				e.printStackTrace();
			}
		}	
	}
}