JDBC 연결 실습

 

 

 

 

 

1. JDBC드라이버 로드 Class.forName(driver)  

 String driver = "oracle.jdbc.OracleDriver";

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

String userid = "mango"; String passwd = "mango";

 

2. DB연결 Connection

Connection conn = null; //db 연결

Statement stmt null; //connect를 이용해 sql명령을 실행하는 객체

ResultSet rs = null; //sql실행 후 select 결과를 저장하는 객체

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

 

 

3. SQL구문을 오라클서버로 송신

sql문을 작성한다. 스트링 안에 세미콜론을 붙이지 않는다.

select문일 경우 executeQuery()를 사용하며, DML문일 경우엔 executeUpdate()를 사용한다. 

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

 

Statement, PreparedStatement - 바인드변수(?)사용 가능 

 

 

 

4. 송신결과 수신 :

stmt = conn.createStatement();

 

 

 

5. 결과 활용 : rs.next()

ResultSet, int

Statement의 sql명령 결과를 ResultSet으로 받아서 실행한다.

 

rs = stmt.executeQuery(sql);

 

6. while 반복문을 사용해 rs로 받은 정보를 컬럼명으로 접근해서 출력한다. 여기까지 try문이 끝났다.

while(rs.next()) {

int deptno = rs.getInt("deptno");

String dname = rs.getString("daname");

String loc = rs.getString("loc");

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

}

 

while 반복문으로 출력할 때 컬럼명 대신 컬럼 순서에 맞는 번호 또는 별칭을 사용 가능

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);

}

 

7.  catch문으로 예외를 처리한다.

}catch(ClassNotFoundException e){

e.printStackTrace(); }catch(SQLException e) { e.printStackTrace();

}

 

 

 

8. DB연결해제 : close()

 

 

 

JDBC드라이버 연결

 

 

oracle에서 사용된 문자 자료형은 getString (), 숫자는 getInt();

SELECT rownum, a.*
FROM (SELECT *

FROM product;
ORDER BY prod_name) a;

package jdbc;

 

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.Scanner;

 

import com.my.sql.MyConnection;

 

public class JDBCTest {

public static void testProductList() {

Connection conn = null;

try {

conn = MyConnection.getConnection();

}catch(ClassNotFoundException | SQLException e) {

e.printStackTrace();

}

Statement stmt=null;

ResultSet rs=null;

try {

stmt = conn.createStatement();

String selectSQL = "SELECT * FROM product";

rs = stmt.executeQuery(selectSQL);

while(rs.next()) {

String no = rs.getString(1);

String name = rs.getString("prod_name");

int price = rs.getInt("prod_price");

System.out.println(no + "-" + name + "-" + price);

}

} catch (SQLException e) {

e.printStackTrace();

} finally {

MyConnection.close(rs, stmt, conn);

}

 

}

public static void testProductPageList() {

Connection conn = null;

try {

conn = MyConnection.getConnection();

} catch (ClassNotFoundException | SQLException e) {

e.printStackTrace();

return;

}

 

// Statement stmt = null;

PreparedStatement pstmt = null;

 

ResultSet rs = null;

Scanner sc = new Scanner(System.in);

System.out.println("검색할 페이지를 입력하세요:");

 

int currentPage = Integer.parseInt(sc.nextLine());

//검색할 페이지

int cntPerPage = 3; //페이지당 보여 줄 목록 수

int startRow = (currentPage - 1) * cntPerPage + 1; //페이지의 시작행

int endRow = (currentPage) * cntPerPage; //페이지의 끝행

 

try {

// stmt = conn.createStatement();

// String selectPageSQL = "SELECT *\r\n"

// + "FROM (SELECT rownum rn, a.*\r\n"

// + " FROM (SELECT *\r\n"

// + " FROM product\r\n"

// + " ORDER BY prod_name) a\r\n"

// + " )\r\n"

// + "WHERE rn BETWEEN "+ startRow +" AND " + endRow;

// rs = stmt.executeQuery(selectPageSQL);

 

 

//바인드변수 : ? 값을 대신

String selectPageSQL = "SELECT *\r\n"

+ "FROM (SELECT rownum rn, a.*\r\n"

+ " FROM (SELECT *\r\n"

+ " FROM product\r\n"

+ " ORDER BY prod_name) a\r\n"

+ " )\r\n"

+ "WHERE rn BETWEEN ? AND ?";

pstmt = conn.prepareStatement(selectPageSQL);

pstmt.setInt(1, startRow);

pstmt.setInt(2, endRow);

rs = pstmt.executeQuery();

 

while(rs.next()) {

int rowNum = rs.getInt("rn");

String prodNo = rs.getString("prod_no");

String prodName = rs.getString("prod_name");

int prodPrice = rs.getInt("prod_price");

System.out.println(rowNum + ":"

+ prodNo + "-" + prodName +"-" + prodPrice);

}

 

} catch (SQLException e) {

e.printStackTrace();

} finally {

MyConnection.close(rs, pstmt, conn);

}

 

}

 

public static void testProductUpdate() {

Connection conn = null;

 

try {

conn = MyConnection.getConnection();

} catch (ClassNotFoundException e) {

e.printStackTrace();

} catch (SQLException e) {

e.printStackTrace();

}

 

Scanner sc = new Scanner(System.in);

System.out.println("상품번호:");

String prodNo = sc.nextLine();

 

System.out.println("변경할 가격");

int prodPrice = Integer.parseInt(sc.nextLine());

// String updateSQL =

// "UPDATE product SET prod_price ="+prodPrice+ " WHERE prod_no = '"+prodNo+"'";

// Statement stmt = null;

 

String updateSQL =

"UPDATE product SET prod_price=? WHERE prod_no=?";

PreparedStatement pstmt = null;

 

 

try {

// stmt = conn.createStatement();

// int rowcnt =

// stmt.executeUpdate(updateSQL); //송신 DML(INSERT/UPDATE/DETETE-처리건수반환

// DDL(CREATE/ALTER/DROP) -0반환

 

pstmt = conn.prepareStatement(updateSQL);

pstmt.setInt(1,prodPrice);

pstmt.setString(2, prodNo);

int rowcnt = pstmt.executeUpdate();

if(rowcnt == 0) {

System.out.println("수정할 상품이 없습니다.");

}else {

System.out.println(rowcnt+"건의 상품이 수정되었습니다");

}

} catch (SQLException e) {

e.printStackTrace();

}

 

 

}

public static void main(String[] args) {

// testProductList();

// testProductPageList();

testProductUpdate();

 

}

}

 

 

 

 

 

 

 

 

package com.my.sql;

 

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

 

 

public class MyConnection {

public static Connection getConnection()

throws ClassNotFoundException, SQLException{

Class.forName("oracle.jdbc.OracleDriver");

 

Connection conn=null;

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

String user = "hr";

String password = "hr";

 

conn = DriverManager.getConnection(url, user, password);

System.out.println("DB와 연결 성공");

return conn;

}

 

gug

t

public static void close(ResultSet rs, Statement stmt, Connection conn) {

if(rs != null) {

try {

rs.close();

} catch (SQLException e) {

}

}

if(stmt != null) {

try {

stmt.close();

} catch (SQLException e) {

}

}

if(conn != null) {

try {

conn.close();

} catch (SQLException e) {

}

}

}

}