Archive for the ‘ JDBC ’ Category

Tomcat & Oracle DataSource 설정

1. conf/server.xml파일을 열고 <GlobalNamingResources>엘리먼트 안에 다음 내용 추가.


<Resource name="jdbc/myoracle" auth="Container"
 type="javax.sql.DataSource" driverClassName="oracle.jdbc.OracleDriver"
 url="jdbc:oracle:thin:@localhost:1521:데이터베이스이름"
 username="유저네임" password="패스워드" />

2. conf/context.xml에 추가


<ResourceLink global="jdbc/myoracle" name="jdbc/myoracle" type="javax.sql.DataSource"/>

3. 코드 안에서는


InitialContext ic = new InitialContext();
DataSource ds = (DataSource)ic.lookup("java:comp/env/jdbc/myoracle");
Connection con = ds.getConnection();
2010
07/11
POSTED BY
CATEGORY
JDBC
JSP&Servlet
Oracle
Comments Off

select 쿼리

select 쿼리는 executeQuery() 메소드를 이용한다.
executeQuery() 메소드는 ResultSet으로 리턴되며
ResultSet의 next() 등을 이용하여 결과를 보여주게된다.


package step3;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import config.DbConfig;

public class TestJdbc3 {
 public static void main(String[] args) {
 Connection con = null;
 Statement stmt = null;
 ResultSet rs = null;    // select 조회 결과를 열어볼 수 있도록 추상메서드를 제공..

 try {
 Class.forName(DbConfig.driver);
 System.out.println("1. Driver Loading...");

 con = DriverManager.getConnection(DbConfig.url, DbConfig.user, DbConfig.pass);
 System.out.println("2. Connection...");

 stmt = con.createStatement();
 System.out.println("3. Statement...");

 String sql = "select id, password, name, address from member";
 // Select 문은 executeQuery() 메소드를 이용한다
 rs = stmt.executeQuery(sql);    // ResultSet 으로 리턴되어짐

 // next() : 다음 행이 존재하면 true 아니면 false
 while(rs.next()){
 String id = rs.getString(1);
 String password = rs.getString(2);
 String name = rs.getString(3);
 String address = rs.getString("address");    // 컬러명으로도 가능하다.
 System.out.println("id : " + id + ", password : " + password +
 ", name : " + name + ", address : " + address);
 }// whiles
 System.out.println("4. Query 실행...");

 } catch (ClassNotFoundException e) {
 e.printStackTrace();
 } catch (SQLException e) {
 e.printStackTrace();
 } finally {
 if(rs != null){
 try {
 rs.close();
 System.out.println("rs.close()");
 } catch (SQLException e) {
 e.printStackTrace();
 }
 }// if
 if(stmt != null){
 try {
 stmt.close();
 System.out.println("stmt.close()");
 } catch (SQLException e) {
 e.printStackTrace();
 }
 }// if
 if(con != null){
 try {
 con.close();
 System.out.println("con.close()");
 } catch (SQLException e) {
 e.printStackTrace();
 }
 }// if
 System.out.println("5. Close...");
 }// finally

 }// main
}// class
2010
06/16
POSTED BY
CATEGORY
JDBC
Comments Off

JDBC step

* JDBC step

1. Driver Loading
2. Connection
3. Statement
4. Query 정의 및 실행
  1. executeUpdate() : insert, delete, update
  2. executeQuery() : select : ResultSet 으로 리턴
5. Close()
package step1;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class TestJdbc1 {
    public static void main(String[] args) {
        String driver = "oracle.jdbc.driver.OracleDriver";        // 1
        String url = "jdbc:oracle:thin:@127.0.0.1:1521:xe";        // 2
        String user = "pm";
        String pass = "oracle";
        try {
            // 1.
            Class.forName(driver);
            System.out.println("1. driver loading");
            // 2.
            Connection con = DriverManager.getConnection(url, user, pass);
            System.out.println("2. Connection");
            Statement stmt = con.createStatement();
            System.out.println("3. Statement");
            String sql = "insert into member(id, password, name, address) " +
            "values('lee', 'bbb', '이영표', '종로')";
            int result = stmt.executeUpdate(sql);
            // int result 리턴값은 영향을 준 row 의 수
            System.out.println("4. Query 실행--> " + result);
            stmt.close();
            con.close();
            System.out.println("5. Close...");
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }
}
2010
06/14
POSTED BY
CATEGORY
JDBC
Comments Off