[JAVA & JDBC] java 이클립스와 SQL연동하는 방법
Devel/JDBC2020. 8. 16. 18:06
반응형
-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";
//
-error
-컬럼 번호로 넣어도 똑같다
-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
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(?,?)";
// pstmt = con.prepareStatement(sql4);
// pstmt.setString(1, "영업");
// pstmt.setString(2, "개발");
// rs = pstmt.executeQuery();
// System.out.println(sql4);
// String sql5 = "select * from dept order by deptno";
// pstmt = con.prepareStatement(sql5);
// rs = pstmt.executeQuery();
// System.out.println(sql5);
// String sql7 = "select loc from dept where deptno >= 20";
// pstmt = con.prepareStatement(sql7);
// rs = pstmt.executeQuery();
// System.out.println(sql7);
// String sql8 = "select dname, loc from dept where dname like 'A%'";
// pstmt = con.prepareStatement(sql8);
// rs = pstmt.executeQuery();
// System.out.println(sql8);
// String sql9 = "select dname from dept where deptno = (select max(deptno) from dept)";
// pstmt = con.prepareStatement(sql9);
// rs = pstmt.executeQuery();
// System.out.println(sql9);
// String sql10 ="insert into dept (deptno, dname, loc)" + "values(?,?,?)";
// pstmt = con.prepareStatement(sql10);
// pstmt.setInt(1, 99);
// pstmt.setString(2, "개발");
// pstmt.setString(3, "서울");
// rs = pstmt.executeQuery();
// System.out.println(sql10);
// String sql11 = "select deptno, loc from dept where deptno =99";
// pstmt = con.prepareStatement(sql11);
// rs = pstmt.executeQuery();
// System.out.println(sql11);
while(rs.next()) {
// int deptno1 = rs.getInt(1);
String dname1 = rs.getString(1);
// String loc1 = rs.getString(2);
System.out.println(dname1);
//+"\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();
}
}
}
}
'Devel > JDBC' 카테고리의 다른 글
[JDBC] 2 table Join, group by 사용하는 방법 및 적용예제 (0) | 2020.08.19 |
---|---|
[JDBC] 클래스 생성부터 분리까지 모든 예제 (0) | 2020.08.19 |
[JDBC]SQL연동 클래스 분리 예제2(DAO, DTO, exception, service) (0) | 2020.08.19 |
[JDBC] 클래스생성, 분리와 확장, 상속 적용예제(DAO, DTO) (0) | 2020.08.19 |
[JDBC] 오라클 java와 sql 연동법과 예제(select, Update, Delete 등) (0) | 2020.08.19 |
댓글()