SQL에 parameter를 삽입하여 실행하는 기능
SQL에 parameter를 삽입하여 실행하는 방법이 있습니다.
String sSql = "select * from mytable where name = ? and address = ?";
Connection conn = get database connection;
PreparedStatement prestmt = null;
Statement stmt = null;
prestmt = conn.prepareStatement(sSql);
prestmt.setString(1,"Mike");
prestmt.setString(2,"LA");
rs = prestmt.executeQuery();
while (rs.next())
{
xxx = rs.getString(1);
yyy = rs.getString(2);
}
위와 같은 parameter name,address를 삽입하여 SQL을 실행할 수 있습니다.
실행되는 SQL은 다음과 같습니다.
select * from mytable where name = 'Mike' and address = 'LA'
위와 같은 방식으로 많은 개발자들이 web application을 개발합니다.
위와 같은 방식은 개발 생산성 측면에서 몇가지 문제가 있었습니다.
가장 큰 문제가 실제 실행되는 SQL을 개발자가 알 수 없습니다.
물론 parameter를 일일이 대입해서 볼수는 있지만 실제 값이 대입된 SQL을 개발자가 볼 수
없다는 것입니다.
문제가 되는 경우를 보겠습니다.
update mytalbe set name=? where id=?
prestmt.setString(1,"123456789012345678901234567890");
prestmt.setString(2,"2334");
그런데 mytable.name field는 varchar(16) 으로 선언이 되어 있다고 하겠습니다.
즉 입력한 값이 16 byte만 설정이 될 수 있습니다.
이와 같은 경우 개발자는 parameter가 설정된 실행되는 SQL을 trace log에서 볼 수 없기때문에
오류를 쉽게 파악하기 힘들 수 있습니다.
그래서 필자는 자체적으로 parameter가 설정된 SQL을 build하는 program을 개발해 보았습니다.
WCStmt.makeQuery method가 이것입니다.
parameter는 WCProperties로 전달하도록 하였습니다.
String sSql = "insert into table_name (name,address) values ('$name','$address')";
WCProperties pParam = new WCProperties();
pParam.setValue("name","jane");
pParam.setValue("address","L'A");
WCStmt oStmt = new WCStmt();
sSql = oStmt.makeQuery(sSql,pParam);
sSql 값 "insert into table_name (name,address) values ('jane','L''A')" 이와 같이 됩니다.
개발자는 sSql의 값을 확인하여 정확한 SQL이 생성되었는지를 점검할 수 있습니다.
그러나 개발을 하고 보니 이 방법도 몇가지 문제가 있었습니다.
parameter value값이 SQL에 삽입될때 몇가지 변환이 되어야 하는데 이 변환이
database의 type에 따라 약간씩 다르다는 것입니다.
L'\A 이와 같은 값이 있다고 하겠습니다.
- oracle,mssql : L''\A
update table_name set field_name = 'L''\A' where id = 37
- mysql : L''\\A
update table_name set field_name = 'L''\\A' where id = 37
이와 같이 변환이 되어야 합니다. 그래서 위의 makeQuery 함수를 다음과 같이 변경해야 했습니다.
String sDsn = "WDLDatabasePool";
sSql = oStmt.makeQuery(sSql,pParam,sDsn);
위에서 처럼 makeQuery에 database connection pool 이름을 전달하여 database type을 얻어와서
해당 database type에 대한 변환을 하도록 하였습니다.
-- JSP sample code 시작
<%@ page language="java" import="wdl.*,java.util.*,java.sql.*,java.lang.*,
java.io.*,java.io.File " contentType="text/html; charset=EUC-KR"%><%
insertRecord();
%>
<%!
public void insertRecord()
{
WCProperties rSqlParam = new WCProperties();
rSqlParam.setValue("parentid",100);
rSqlParam.setValue("text","hello");
rSqlParam.setValue("url","-");
rSqlParam.setValue("guid","g");
rSqlParam.setValue("idx",0);
rSqlParam.setValue("depth",0);
rSqlParam.setValue("child",0);
String sSql = "insert into mytable (\n";
sSql += "parentid\n";
sSql += ",text\n";
sSql += ",url\n";
sSql += ",guid\n";
sSql += ",idx\n";
sSql += ",depth\n";
sSql += ",child\n";
sSql += ")\n";
sSql += "values(\n";
sSql += "'$parentid' -- parentid\n";
sSql += ",'$text' -- text\n";
sSql += ",'$url' -- url\n";
sSql += ",'$guid' -- guid\n";
sSql += ",'$idx' -- idx\n";
sSql += ",'$depth' -- depth\n";
sSql += ",'$child' -- child\n";
sSql += ")";
WCStmt oStmt = new WCStmt();
String sSql2 = oStmt.makeQuery(sSql,rSqlParam,"WDLDatabasePool");
oStmt.executeUpdate(sSql2, null,"WDLDatabasePool");
}
%>
-- JSP sample code 끝
아래의 소스코드는 WCStmt.java의 일부분 코드입니다. makeQuery와 toSqlValue 입니다.
-- WCStmt.java에서 makeQuery 부분 추출 시작
public String makeQuery(String sSql,WCProperties rParam,String sDsn)
{
if (WCString.isEmpty(sSql) || WCString.isEmpty(sDsn))
return null;
String sTail = "";
String sHead = "";
String sMid = "";
String sRet = new String();
String sSrc = sSql;
do
{
int nIndex = sSrc.indexOf("$");
int nSqlLen = sSrc.length();
if (nIndex < 0)
break;
sMid = sSrc.substring(nIndex+1,nSqlLen);
String sTok = getTok(sMid); // getToken
int nTok = sTok.length();
sTail = sSrc.substring(nIndex + nTok+1,nSqlLen);
sHead = sSrc.substring(0,nIndex);
// sSrc = sHead;
String sVal = rParam.GetStrValue(sTok);
if (sVal == null)
{
sVal = "";
if (nIndex > 0)
{
char c = sSrc.charAt(nIndex-1);
if (c == '\'' || c == '\"')
sVal = "";
}
}
else
{
if (sVal.length() <= 0)
{
sVal = "";
if (nIndex > 0)
{
char c = sSrc.charAt(nIndex-1);
if (c == '\'' || c == '\"')
sVal = "";
}
}
}
sVal = WCString.nullCheck(WCStmt.toSqlValue(sVal,sDsn));
sRet += sHead;
sRet += sVal;
sSrc = sTail;
} while (true);
if (WCString.isEmpty(sRet))
{
sRet = sSql;
}
else
{
sRet += sTail;
}
return sRet;
}
public static String toSqlValue(String sSrc,String sDsn)
{
if (WCString.isEmpty(sSrc) || WCString.isEmpty(sDsn))
{
return null;
}
String sRet = new String();
int nLen = sSrc.length();
String sDatabaseType = WCDBConnectionManager.getDatabaseType(sDsn);
if (WCString.compareNoCase(sDatabaseType,"mysql") == 0)
{
for (int i=0;i<nLen;i++)
{
char cChar = sSrc.charAt(i);
if (cChar == '\'')
{
sRet += "'";
}
else if (cChar == '\\')
{
sRet += "\\";
}
sRet += cChar;
}
}
else if (WCString.compareNoCase(sDatabaseType,"mssql") == 0
|| WCString.compareNoCase(sDatabaseType,"oracle") == 0)
{
for (int i=0;i<nLen;i++)
{
char cChar = sSrc.charAt(i);
if (cChar == '\'')
sRet += "'";
sRet += cChar;
}
}
return sRet;
}
-- WCStmt.java에서 makeQuery 부분 추출 끝
아래의 소스가 완벽히 실행하기 위해서는 WCProperties,WCVector,WCLog,WCSystem 등의 source코드가 필요합니다.
이러한 java source는 Web Development Library(www.webdevlib.net)에서 download 받아 사용하실 수 있습니다.
물론 www.webdevlib.net에서 소스코드를 받지 않고, 아래의 소스 코드를 수정하여 사용하실 수도 있습니다.
의도적으로 소스코드의 일부만 글로 쓰는 것은 아닙니다. 모든 소스에 대한 설명은 차차 하겠습니다.
첨부파일 : sql_makeQuery.zip
sql_makeQuery.jsp
WCDBConnection.java
WCDBConnectionPool.java
WCDBConnectionManager.java
WCStmt.java
WCProperties.java
출처 : 고급 웹 UI 개발 라이브러리 Web Development Library 소스공개 : http://www.webdevlib.net
