오늘도 행복하다.

CallableStatement cs = null;
ResultSet rs = null;
try{
	cs = con.prepareCall("{ ? = call PR_AUTH('TEAM', ?, 'emp_no', ?) }");
	cs.registerOutParameter(1, Types.OTHER); //리턴값이 결과집합임.
	cs.setString(2, "A");
	cs.registerOutParameter(3, Types.INTEGER);
	if(cs.execute()){
		rs = (ResultSet)cs.getResultSet();
		while(rs.next()){
			out.println(rs.getString(1) + "\t" + rs.getString(2));
		}
		out.println("code:" + cs.getInt(3));
	}
}finally{
	close rs, cs, con;
}

PreparedStatement ps = null;
...
ps = con.prepareStatement("EXEC PR_AUTH 'TEAM', ?, 'emp_no', '' ");
ps.setString(1, "A"); //input인자는 가능하지만 마지막에 위치한 output인자는 지정할 수 없다(PreparedStatement에는 registerOutParameter()가 없다.)
ResultSet rs = ps.executeQuery();
while(rs.next()){
	out.println(rs.getString(1) + "===>" + rs.getString(2) + "<br />");
}

	...
	rs.close(); rs = null;
	pstmt.close(); pstmt = null;
	conn.commit(); conn = null;

}catch(Exception e){
	ROLLBACK
	THROW EXCEPTION
}finally{
	CLOSE ResultSet, PreparedStatement, Connection
}
RETURN

• jdbc:derby:testDerbyDB;create=true
• create=true 옵션은 데이터베이스가 없는 경우 생성하도록 한다.
• 데이터베이스명만 있는 경우 사용자 디렉토리에 데이터베이스가 생긴다.(이 경우 C:\Documents and Settings\USER\testDerbyDB)
• /testDerbyDB 이면 C드라이브에 생성됨, 절대경로로 지정할 수도 있음.
  
• org.apache.derby.jdbc.EmbeddedDriver
• derby.jar: contains the Derby engine and the Derby Embedded JDBC driver

• jdbc:derby://localhost:1527/testDerbyDB;create=true
• DERBY/bin/startNetworkServer 로 서버를 시작할 수 있다.
• 데이터베이스는 서버를 실행시킨 디렉토리에 생성된다. Eclipse에서 Ant로 실행시키면 프로젝트 루트에 생긴다.
  
• org.apache.derby.jdbc.ClientDriver
• 클라이언트 : derbyclient.jar
• 서버 : derbyrun.jar, derbynet.jar, derby.jar

• Oracle Database 11g Release 1 (11.1.0.7.0) JDBC Drivers
  ojdbc5.jar (1,890,499 bytes) - Classes for use with JDK 1.5.
  ojdbc6.jar (1,988,051 bytes) - Classes for use with JDK 1.6.
• Oracle Database 11g Release 1 (11.1.0.6.0) JDBC Drivers
  ojdbc5.jar (1,879,860 bytes)
  ojdbc6.jar (1,977,267 bytes)
• Oracle Database 10g Release 2 (10.2.0.4) JDBC Drivers
  classes12.jar (1,609,607 bytes) - for use with JDK 1.2 and JDK 1.3
  ojdbc14.jar (1,555,682 bytes) - classes for use with JDK 1.4 and 1.5
• Oracle Database 10g Release 2 (10.2.0.3) JDBC Drivers
  classes12.jar (1,600,090 bytes) - for use with JDK 1.2 and JDK 1.3
  ojdbc14.jar (1,545,954 bytes) - classes for use with JDK 1.4 and 1.5
• Oracle Database 10g Release 2 (10.2.0.2) JDBC Drivers
  classes12.jar (1,594,191 bytes) - for use with JDK 1.2 and JDK 1.3
  ojdbc14.jar (1,540,457 bytes) - classes for use with JDK 1.4 and 1.5
• Oracle Database 10g Release 2 (10.2.0.1.0) JDBC Drivers
  classes12.jar (1,590,491 bytes) - for use with JDK 1.2 and JDK 1.3
  ojdbc14.jar (1,536,979 bytes) - classes for use with JDK 1.4 and 1.5
• Oracle Database 10g 10.1.0.5 JDBC Drivers
  classes12.jar (1,442,469 bytes) - for use with JDK 1.2 and JDK 1.3
  ojdbc14.jar (1,378,346 bytes) - classes for use with JDK 1.4
• Oracle Database 10g 10.1.0.2 JDBC Drivers
  classes12.jar (1,417,089 bytes) - for use with JDK 1.2 and JDK 1.3
  ojdbc14.jar (1,352,918 bytes) - classes for use with JDK 1.4
• Oracle9i 9.2.0.8 JDBC Drivers
  ojdbc14.jar - JDBC classes (1,212,964 bytes) - For use with JDK 1.4
  classes12.jar - JDBC classes (1,234,433bytes) - For use with JDK 1.2 and JDK 1.3
  classes111.jar - JDBC classes (1,063,074 bytes) - For use with JDK 1.1
• Oracle9i 9.2.0.5 JDBC Drivers
  ojdbc14.jar - JDBC classes (1,200,046 bytes) - For use with JDK 1.4
  classes12.zip - JDBC classes (1,232,604 bytes) - For use with JDK 1.2 and JDK 1.3
  classes111.zip - JDBC classes (1,063,479bytes) - For use with JDK 1.1
• Oracle9i 9.2.0.4 JDBC Drivers
  ojdbc14.jar - JDBC classes (1,187,584 bytes) - For use with JDK 1.4
  classes12.zip - JDBC classes (1,219,950 bytes) - For use with JDK 1.2 and JDK 1.3
  classes111.zip - JDBC classes (1,052,833 bytes) - For use with JDK 1.1
• Oracle9i 9.2.0.3 JDBC Drivers
  ojdbc14.jar - JDBC classes (1,181,679 bytes) - For use with JDK 1.4
  classes12.zip - JDBC classes (1,213,897 bytes) - For use with JDK 1.2 and JDK 1.3
  classes111.zip - JDBC classes (1,048,261 bytes) - For use with JDK 1.1
• Oracle9i 9.2.0.1 JDBC Drivers
  ojdbc14.jar - JDBC classes ( 1,174,976 bytes) - For use with JDK 1.4
  classes12.zip - JDBC classes ( 1,207,068 bytes) - For use with JDK 1.2 and JDK 1.3
  classes111.zip - JDBC classes ( 1,043,528 Bytes) - For use with JDK 1.1
• Oracle9i 9.0.1.4 JDBC Drivers
  classes12.zip - JDBC classes (1,143,559 bytes) - For use with JDK 1.2 and JDK 1.3
  classes111.zip - JDBC classes (988,625 bytes) - For use with JDK 1.1
• Oracle9i 9.0.1 JDBC Drivers
  classes12.zip - JDBC classes( 1,081 kb) - For use with JDK 1.2 and JDK 1.3
  classes111.zip - JDBC classes ( 936 kB) - For use with JDK 1.1
• Oracle8i 8.1.7.1JDBC Drivers
  classes12.zip - JDBC classes ( 1,892 kB) - For use with JDK 1.2
  classes111.zip - JDBC classes ( 1,741 kB)

• 2008 지원 드라이버 : http://msdn.microsoft.com/ko-kr/library/dd903047%28SQL.10%29.aspx
• sqljdbc4.jar

• http://www.microsoft.com/downloads/results.aspx?pocId=&freetext=JDBC&DisplayLang=ko
• com.microsoft.sqlserver.jdbc.SQLServerDriver
• jdbc:sqlserver://ADDRESS:1433;databaseName=DB_NAME;
• sqljdbc.jar(1.2.2828.100 - 570KB, 1.1.1501.101 - 248KB, 1.0.809.102 - 227KB)
• 참고 http://msdn.microsoft.com/ko-kr/library/bb418445(SQL.10).aspx

• 이 드라이버는 아마도 SQL Server 2000 드라이버.
  
• com.microsoft.jdbc.sqlserver.SQLServerDriver
• jdbc:microsoft:sqlserver://ADDRESS:1433
• mssqlserver.jar 와 com/microsoft/jdbc/base/BaseDriver 을 필요로 한다.

• http://jtds.sourceforge.net
• net.sourceforge.jtds.jdbc.Driver
• jdbc:jtds:sqlserver://ADDRESS:1433/DB_NAME
• jtds-1.2.2.jar
• TEXT 컬럼 처리 가능
  

=-=> ms 드라이버는 text 컬럼처리 안되는게 있을거야.

- Windows 통합인증을 사용할 때는 sqljdbc_auth.dll이 필요 ==???

• MySQL Connectors
• com.mysql.jdbc.Driver
• jdbc:mysql://ADDRESS/DB_NAME?Unicode=true&characterEncoding=EUC_KR
• mysql-connector-java-5.1.0-bin.jar(554KB)
  

CallableStatement cs = null;
try{
    cs = con.prepareCall("{ ? = call sp_test(?,?,?) }");
    int idx = 0;
    cs.registerOutParameter(++idx, Types.INTEGER);
    cs.setString(++idx, COL_1);
    cs.setString(++idx, COL_2);
    cs.setString(++idx, COL_3);
    
    cs.execute();
    int result = cs.getInt(1);
    if(result == -1){ //check error
        throw new SQLException("result is -1"); //=-=> 좀 더 정형화된 다른 예외를 만들어야...
    }
    return result;

}catch(Exception e){  
    throw new RuntimeSQLException(e, primaryKey); 
}finally{
    if(cs != null){ try{ cs.close(); }catch(Exception ignored){  } }
}

PreparedStatement ps = null;
try{
    ps = con.prepareStatement(QUERY);
    int idx = 0;
    ps.setString(++idx, COL_1);
    ...
    ps.setString(++idx, COL_N);
    
    return ps.executeUpdate();

}catch(Exception e){
    throw new RuntimeSQLException(e, primaryKey);
}finally{
    if(ps != null){ try{ ps.close(); }catch(Exception ignored){  }
}

Connection con = null;
PrepareStatement ps = null;

try{
	ps = con.prepareStatement(QUERY);  
	int idx = 0;  
	ps.setString(++idx, COL_1);  
	...  
	ps.setString(++idx, COL_N);  

	int affectedRow = ps.executeUpdate();
	if(affectedRow != 1){
		throw new RuntimeSQLException(" expected : 1, result : " + affectedRow
			, primaryKey); //=-=> 정형화...
	}
	con.commit();
}catch(Exception e){
	con.rollback();
	throw new RuntimeSQLException(e, primaryKey);
}finally{
    if(ps != null){ try{ ps.close(); }catch(Exception ignored){  }
    if(con != null){ try{ con.close(); }catch(Exception ignored){  }
}

try{
    //throw exception
}catch(Exception e){
    //do nothing
}

try{
    //throw exception
}catch(Exception e){
    logger.error(e);
}

try{
    //throw exception
}catch(Exception e){
    isError = true; //예외를 추적할 수 있는 e를 그냥 무시하고 있다.
    또는
    throw new XxxException(e.getMessage()); //이런 경우에도 예외를 추적할 수 없다. 필요한 경우가 아니라면 이렇게 사용하지 말고 아래와 같이 생성자에 이전 예외를 넣어준다. 
    //throw new XxxException(e); //권장
}
...
if(isError){
    message("에러입니다.");
}

int affectedRows = INSERT/UPDATE/DELETE result
if(affectedRows != 1){
    THROW "SQL EXECUTE ERROR : " + affectedRows
}

<%@page contentType="text/html; charset=UTF-8" %>
<%@page import="java.sql.*"%>
<%
String cmd = request.getParameter("cmd");
String query = empty(request.getParameter("query"));
%>
<html>
<head><title></title>
</head>
<body>
<form method="post">
<input type="hidden" name="cmd" value="execute">
<textarea name="query" rows="" cols=""><%= query %></textarea>
<input onclick="this.form.submit();" type="button">

<%
if("execute".equals(cmd)){
	
	Connection con = getConnection(); 
	PreparedStatement ps = null;
	ResultSet rs = null;
	
	try{
		ps = con.prepareStatement(query);
		boolean queryType = ps.execute();
		if(queryType){			
			
			ResultSetMetaData md = ps.getMetaData();
			int columnCount = md.getColumnCount();
			out.println("<table border="1"><tr>");
			for(int i = 1 ; i <= columnCount ; i++){
				out.println("<td>" + md.getColumnName(i) + "</td>"); //getColumnLabel()...sql server는 columnName 과 동일함.
			}
			out.println("</tr>");
						
			rs = ps.getResultSet();
			while(rs.next()){
				out.println("<tr>");
				for(int j = 1 ; j <= columnCount ; j++){
					out.println("<td>" + rs.getObject(j) + "</td>");
				}
				out.println("</tr>");
			}
			out.println("</table>");
		}else{
			//ps.getUpdateCount() //select 에도 이게 되나? sql server 는 select 인 경우 -1임.
		}		
	}catch(Exception e){ //이거 출력하는거 하나 만들어둬.
%>
		<%= e %>
<%		
	}finally{
		close(con, ps, rs);
	}
}
%>
<%!
Connection getConnection(){ //이 메서드의 구현이 바뀌겠지
	return project.DBConnectionManager.getInstance();
}
String empty(String s){
	return s == null ? "" : s;
}
void close(Connection con, Statement st, ResultSet rs){
	if(rs != null){ try{ rs.close(); }catch(Exception ignored){ } }
	if(st != null){ try{ st.close(); }catch(Exception ignored){ } }
	if(con != null){ try{ con.close(); }catch(Exception ignored){ } }
}
%>

UPDATE/DELETE/INSERT 구문은 affectedRows 를 넘겨받아서 일치하는 경우만 commit 하도록 하자.
SELECT 시 로우개수를 제한할 것.