JDBC codes

DB메타정보 알기

import java.io.*;
import java.util.*;
import java.sql.*;

public class Test2 {
 
  public static void main(String[] args) throws SQLException, FileNotFoundException {
  try{
  
   try{
    Class.forName ("oracle.jdbc.driver.OracleDriver");
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
    
    Connection con =  DriverManager.getConnection("");
    Statement stmt = null;
    
    DatabaseMetaData dbm= con.getMetaData();
    System.out.println("Product Name :"+dbm.getDatabaseProductName());
    System.out.println("Product Version :"+dbm.getDatabaseProductVersion());
    System.out.println("Driver Major Version :"+dbm.getDriverMajorVersion());
    System.out.println("Driver Minor Version :"+dbm.getDriverMinorVersion());
    System.out.println("Driver Name :"+dbm.getDriverName());
    System.out.println("Driver Version :"+dbm.getDriverVersion());
   
   } catch (SQLException ex) {
    System.out.println("BulletinList:execList() : SQLException") ;
    System.out.println("SQLState : " + ex.getSQLState()) ;
    System.out.println("message : " + ex.getMessage()) ;
    System.out.println("Oracle Error Code : " + ex.getErrorCode() ) ;
   }
  
  }catch (Exception ee) {
   System.out.println("haha"+ee.getMessage());
  }
  }
}

JSP에서 DataSource를 이용한 Connection 획득

import java.sql.*;
import javax.sql.*;
import javax.naming.*;

...

Context ctx = new InitialContext();
// 이 객체가 JNDI에 미리 등록되어 있어야만 한다.
DataSource ds = (DataSource)ctx.lookup("jdbc/bookSampleDB");
Connection con = ds.getConnection("id", "password");

(JSP에서)
<%@ page contentType = "text/html; charset=euc-kr" language="java" %>
<%@ page import = "java.sql.*" %>
<%@ page import = "javax.naming.*" %>
<%@ page import = "javax.sql.*" %>

<%
        Connection conn   = null;      
        Statement  stmt   = null;
        ResultSet  rs     = null;
        try       {

        Context ctx = new InitialContext();
        // 이 객체가 JNDI에 미리 등록되어 있어야만 한다.
        DataSource ds = (DataSource)ctx.lookup("sybaseTXDS");
        Connection con = ds.getConnection();
        stmt = conn.createStatement();
        rs   = stmt.executeQuery("SELECT USR_ID, NAME, RCN, EMPLNUM FROM CM_USRINFO WHERE BAI_USR_YN = 'Y'");
%>

각종 Connection 획득방법

public class DBHelper {
    private Connection connection   = null;  
    public Connection getConnection(){
        return connection;
    }
    public DBHelper(String driver,String url,String user, String password){
        this(driver,url,user, password, null);
    }
    public DBHelper(){ }
   
    public void setConnectionFromDataSource(String dataSourceName) throws Exception{
        Context ctx = new InitialContext();
        DataSource ds = (DataSource)ctx.lookup(dataSourceName);
        connection = ds.getConnection();
    }

    public DBHelper(String propertyFileName){
        try {
            Properties prop = new Properties();
            prop.load(new FileInputStream(propertyFileName));
            setConnection(prop);
        } catch(Exception e) {
            e.printStackTrace();
        }       
    }
   
    private void setConnection(Properties prop){
        String driver = prop.getProperty("driver");
        String url = prop.getProperty("url");       
        try{
            Class.forName(driver);
        } catch(Exception e){
            e.printStackTrace();
        }
        try{
            connection = DriverManager.getConnection(url,prop);
            System.out.println("Connection Success");
        } catch(Exception e) {
            e.printStackTrace();
        }       
    }
   
    public DBHelper(String driver,String url,String user, String password, String charSet){
        Properties prop = new Properties();
        prop.put("driver",driver);
        prop.put("url",url);           
        prop.put("user",user);   
        prop.put("password",password);
        if ( charSet != null) prop.put("charSet",charSet);   
        setConnection(prop);           
    }   

DB to DB insert

JDBC DataConversion(Using Metadata)

Oracle Clob, Blob

 Oracle_Clob_Blob.doc

batchUpdate

  private static void batchUpdate(String url, String driver, Properties prop, String query) {
  Connection con =  null; 
  PreparedStatement psmt  = null;
  try{
   Class.forName (driver);
   con = DriverManager.getConnection(url,prop);
   con.setAutoCommit(false);
   psmt = con.prepareStatement(query.toString());
   for(int i=1;i<=1000;i++){
    psmt.setString(1, "00");
    psmt.setString(2, String.valueOf(i));
    psmt.setString(3, "code:"+i);
    psmt.addBatch();
    System.out.println(i+"th row set");
    if(i%100==0)  {
     psmt.executeBatch();
     System.out.println("commit");
    }
   }
  }   catch (Exception ex) {
    ex.printStackTrace();
    } finally {
    DbUtils.closeQuietly(psmt);
    DbUtils.closeQuietly(con);
    }
 }