[JDBC] 오라클 java와 sql 연동법과 예제(select, Update, Delete 등)

오라클 java와 sql 연동법

-JDBC

D:\app\acorn\product\11.2.0\dbhome_1\jdbc\lib

드라이버 지정 경로(프로젝트 만들 때 마다 해줘야함)

1.프로젝트 이름에서 오른쪽 마우스

2.

3.

4.

-Apply (적용하면 된다)

+예제:

import java.lang.reflect.Field;

import java.lang.reflect.Method;

public class ClassforNameTest {

       public static void main(String[] args) throws Exception {

              Class cl = Class.forName("java.lang.Math");

              Field [] fiedls = cl.getDeclaredFields();

              for(Field f : fiedls) {

                     System.out.println("fiedls: "+ f.getName());

              }

              Method [] methods = cl.getDeclaredMethods();

              for(Method m : methods) {

                     System.out.println("methods: "+m.getName());

              }

       }

}

1.

String driver: 어떤 APi를 쓸건지, 이름

String url-> 오라클 들어갈 주소, localhost: 내가 만들어 놓은 내 컴퓨터, 1521:기본적인 오라클 포트 , orcl:오라클 sid

2.

Connetion:DB에 실제 접속을 해주게 하는 것.

위에 변수 url, id, passwd를 사용

stmt, rs:접속된 것을 통해서 sql을 실행 할 수 있게, 실제 실행해 주는 것.

stmt을 하고 pstmt를 할것.

//error:

class not roading

1.driver 주소 틀린것.

-connection은 반드시 close해 둘것.

//

            String driver = "oracle.jdbc.driver.OracleDriver";//6)j.jar파일의 api파일 로딩

            String url = "jdbc:oracle:thin:@localhost:1521:orcl";

            String userid = "scott";

            String passwd="tiger";

//

-JDBCTEST

JDBC_error:부적합한 열 이름

-컬럼 번호로 넣어도 똑같다

-JDBC_whereTEST

Sql Where절 실행

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

public class WhereTest {

       public static void main(String[] args) {

              String driver = "oracle.jdbc.driver.OracleDriver";//6)j.jar파일의 api파일 로딩

              String url = "jdbc:oracle:thin:@localhost:1521:orcl";

              String userid = "scott";

              String passwd="tiger";

              

              Connection con = null;

              Statement stmt = null;

              ResultSet rs = null;

              

              try {

                     Class.forName(driver);

                     System.out.println("드라이버 로딩 성공");

                     con = DriverManager.getConnection(url,userid,passwd);

                     System.out.println("접속 성공");

                     String sql = "select deptno , dname, loc from dept where dname='SALES'";

                     stmt = con.createStatement();

                     rs = stmt.executeQuery(sql);

                     while(rs.next()) {

                           int deptno = rs.getInt(1);

                           String dname = rs.getString(2);

                           String loc = rs.getString(3);

                           System.out.println(deptno + "\t"+ dname + "\t"+loc);

                     }

                     

              } catch (ClassNotFoundException e) {

                     e.printStackTrace();

              }catch(SQLException e) {

                     e.printStackTrace();

              }finally {

                     try {

                           if(rs != null)rs.close();

                           if(stmt != null)stmt.close();

                           if(rs != null)rs.close();

                     } catch (SQLException e) {

                           e.printStackTrace();

                     }

              }

       }

}

-Update & Delete

SQL -Update & Delete 절 이용

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

public class Statement_update {

       public static void main(String[] args) {

              String driver = "oracle.jdbc.driver.OracleDriver";//6)j.jar파일의 api파일 로딩

              String url = "jdbc:oracle:thin:@localhost:1521:orcl";

              String userid = "scott";

              String passwd="tiger";

              

              Connection con = null;

              Statement stmt = null;

              ResultSet rs = null;

              try {

                     Class.forName(driver);

                     con = DriverManager.getConnection(url,userid,passwd);

                     

                     int deptno = 16;

                     String dname = "영업";

                     String loc = "제주";

                     String sql = "Update dept set dname = '"+ dname+"', loc = '"+loc+"' where deptno ="+deptno ;

                     System.out.println(sql);

                     

                     stmt = con.createStatement();

                     int result = stmt.executeUpdate(sql);

//                   rs = stmt.executeQuery(sql);

                     System.out.println("실행된 레코드 갯수:"+result);

                     

                     String sql3 = "delete from dept where deptno ="+deptno;

                     int result2 = stmt.executeUpdate(sql3);

                     

                     String sql2 = "select * from dept";

                     rs = stmt.executeQuery(sql2);

                     System.out.println(sql2);                

                                         

                     while (rs.next()) {

                           int deptno1 = rs.getInt(1);

                           String dname1 = rs.getString(2);

                           String loc1 = rs.getString(3);

                           System.out.println(deptno1+"\t"+dname1+"\t"+loc1);                           

                                                }                                        

              } catch (ClassNotFoundException e) {

                     e.printStackTrace();

              }catch(SQLException e) {

                     e.printStackTrace();

              }finally {

                     try {

                           if(rs != null)rs.close();

                           if(stmt !=null)stmt.close();

                           if(rs != null)rs.close();

                     } catch (SQLException e) {

                           e.printStackTrace();

                     }

              }                    

       }

}

-PreparedStatement

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

public class JDBCTest {

       public static void main(String[] args) {

              String driver = "oracle.jdbc.driver.OracleDriver";//6)j.jar파일의 api파일 로딩

              String url = "jdbc:oracle:thin:@localhost:1521:orcl";

              String userid = "scott";

              String passwd="tiger";

              

              Connection con = null;

              PreparedStatement pstmt = null;

              ResultSet rs = null;

              

              try {

                     Class.forName(driver);

                     con = DriverManager.getConnection(url,userid,passwd);

                     String sql = "select deptno, dname, loc from dept";

                     pstmt = con.prepareStatement(sql);

                     rs = pstmt.executeQuery();

                     while(rs.next()) {

                           int deptno = rs.getInt(1);

                           String dname = rs.getString(2);

                           String loc = rs.getString(3);

                           System.out.println(deptno + "\t"+ dname +"\t"+ loc);

                     }

                     

              } catch (ClassNotFoundException e) {

                     e.printStackTrace();

              }catch(SQLException e) {

                     e.printStackTrace();

              }finally {

                     try {

                           if( rs != null)rs.close();

                           if(pstmt != null)pstmt.close();

                           if(con !=null)con.close();

                     } catch (SQLException e) {

                           e.printStackTrace();

                     }

              }      

       }

}

-

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

public class JDBCTest2

       public static void main(String[] args) {

              String driver = "oracle.jdbc.driver.OracleDriver";//6)j.jar파일의 api파일 로딩

              String url = "jdbc:oracle:thin:@localhost:1521:orcl";

              String userid = "scott";

              String passwd="tiger";

              

              Connection con = null;

              PreparedStatement pstmt = null;

              ResultSet rs = null;

              try {

                     Class.forName(driver);

                     con = DriverManager.getConnection(url,userid, passwd);

                     String sql = "insert into dept(deptno, dname, loc)"+"values (?,?,?)";

                     pstmt = con.prepareStatement(sql);

                     pstmt.setInt(1, 14);

                     pstmt.setString(2, "개발");

                     pstmt.setString(3, "서울");

                     int num = pstmt.executeUpdate();

                     System.out.println("실행된 레코드 갯수: "+num);

                     

                     String sql2="select * from dept where deptno=12";

                     pstmt = con.prepareStatement(sql2);

                     rs = pstmt.executeQuery(sql2);

                     System.out.println(sql2);

                     while(rs.next()) {

                           int deptno1 = rs.getInt(1);

                           String dname1 = rs.getString(2);

                           String loc1 = rs.getString(3);

                           System.out.println(deptno1+"\t"+dname1+"\t"+loc1);

                     }                    

              } catch (ClassNotFoundException e) {

                     e.printStackTrace();

              } catch(SQLException e) {

                     e.printStackTrace();

              }finally {

                     try {

                           if( rs != null)rs.close();

                           if(pstmt != null)pstmt.close();

                           if(con != null)con.close();

                           

                     } catch (SQLException e) {

                           e.printStackTrace();

                     }

              }

       }

}

-예제

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

public class JDBCUpdate {

public static void main(String[] args) {

String driver = "oracle.jdbc.driver.OracleDriver";//6)j.jar파일의 api파일 로딩

String url = "jdbc:oracle:thin:@localhost:1521:orcl";

String userid = "scott";

String passwd="tiger";

Connection con = null;

PreparedStatement pstmt = null;

ResultSet rs = null;

try {

Class.forName(driver);

con = DriverManager.getConnection(url,userid, passwd);

// String sql = "update dept set dname=?,loc=? where deptno =?";

// pstmt = con.prepareStatement(sql);

//

// pstmt.setString(1, "영업");

// pstmt.setString(2, "제주");

// pstmt.setInt(3, 90);

//

// int num = pstmt.executeUpdate();

// System.out.println("실행된 레코드 갯수: "+num);

//

// String sql2 = "select * from dept where deptno = ?";

// pstmt = con.prepareStatement(sql2);

// pstmt.setInt(1, 90);

// rs = pstmt.executeQuery();

// System.out.println(sql2);

//

//

// String sql4 = "select deptno from dept where dname in(?,?)";