jdbc

Edit

참조하는 jar파일 확인

pmap [pid] | grep jar

혹은 cat /proc/<pid>/smaps

ps axuw | grep java | grep -v grep | awk '{print $2}' | while read P; do pmap $P | grep 'spring' ; done

Statement Cache

JDBC API 사용법

Statment를 안 가지면 maximum open cursor exceed ! 에러나 Limit on number of statements exceeded 에러 발생

각종 DBMS JDBC 드라이버 셋팅법 정리

에러 관련

Mysql

Fetch size

autoreconnect=true

Mysql batchupdate

BLOB image 관련

이미지 저장

테이블 생성

create table test(
  img blob
);

import sun.misc.*;
import java.sql.*;
import java.io.*;

public class test
{
    public static void main(String args[])
    {
        try {

            Class.forName("oracle.jdbc.driver.OracleDriver");
            Connection con = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "xxx", "xxx");
            con.setAutoCommit(false);

            File file = new File("Sample.jpg");
            FileInputStream fis = new FileInputStream(file);

            PreparedStatement ps = con.prepareStatement("insert into test (img) values (?)");
            ps.setBinaryStream(1, fis, (int)file.length());
            ps.executeUpdate();

            ps.close();
            con.close();
            fis.close();

        } catch(Exception e) {
            System.out.println(e.toString());
        }

    }
}

DB에서 이미지 읽어서 웹으로 보여주기 JSP

<%@ page import="java.sql.*, java.io.*"%><%
  String driver = "com.sybase.jdbc3.jdbc.SybDriver";
  String strUrl = "jdbc:sybase:Tds:11.22.1.111:1111/Dbname";
       //                   jdbc db           local      port  sid.
    Connection conn   = null;
    Statement  stmt   = null;
    PreparedStatement pstmt = null;
    ResultSet rs;
    ByteArrayOutputStream os = null;
    byte[] result = null;

       try{
            Class.forName(driver);
        }catch(Exception exception)        {
            System.out.println(exception);
        }

        try{
         Properties p=new Properties();
       p.put("user","ebaiusr");
       p.put("password","****");
          conn = DriverManager.getConnection(strUrl,p);

          // 디비에서 읽는다.
          pstmt = conn.prepareStatement("select PIC_FILE from PIC_FILE where RCN = '780728' ");
          rs = pstmt.executeQuery();
          if(rs.next()) {
            InputStream is = rs.getBinaryStream(1);
            os = new ByteArrayOutputStream();
            int i;
            while((i = is.read()) != -1) {
                os.write(i);
            }
            result = os.toByteArray();
            is.close();
            os.close();
            }
        } catch(Exception e) {
            out.println("error sql execute");
            out.println("result = " + e.toString());
        }
        finally
        {
            try {
                if(conn != null) conn.close();
            }
            catch(Exception e) {
                out.println("error conn close");
            }
        }
response.setContentType("image/jpeg");
ServletOutputStream oout = response.getOutputStream();
oout.write(result);
oout.flush();
oout.close();
%>

기타

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

Oracle Clob, Blob

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

Children