A DESCRIPTION OF THE REQUEST :
In JDBC programming we often encounter to have big static queries with lots of values to be set during runtime. PreparedStatement has methods like setObject(int i, Object object ); The problem with big query is that, the value index /order is often tedious to tally and say if the where clause condition are shuffled to get better query performance, then tallying of order in query is required again.
  To resolve this problem can java.sql package Interface PreparedStatement/ cud be yet another interface provide methods like setObject(String paramName, Object object); and SQL wud look like SELECT * FROM SOME_TABLE WHERE COL1 = ?col1 AND COL2 = ?col2 AND COL3=?col1
setObject("col1", "value1");setObject("col2", "value2"); wud fire a query as
SELECT * FROM SOME_TABLE WHERE COL1='value1' AND COL2='value2' AND COL3='value1'

JUSTIFICATION :
  To aviod tallying order of where caluse values


CUSTOMER SUBMITTED WORKAROUND :
package shom.pattern;

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

/**
 * An utility which can provide SQL to have named parameter.
 *
 */

public class NamedParameterStatement {
public PreparedStatement pstmt = null;
private List namedParams = null;

public NamedParameterStatement(Connection conn, String sql) throws SQLException {
namedParams = new ArrayList();
String trimmedSql = trimOfName(sql);
this.pstmt = conn.prepareStatement(trimmedSql);
System.out.println(namedParams);
System.out.println(trimmedSql);
}

public void setObject(String paramName, Object x) throws SQLException {
if (namedParams != null && pstmt != null) {
int paramIndex = namedParams.indexOf(paramName) + 1;
if (paramIndex <= 0 ) {
throw new SQLException("Parameter Not Defined " + paramName);
}
pstmt.setObject(paramIndex, x);
}
}

public void setString(String paramName, String x) throws SQLException {
setObject(paramName, x);
}

private String trimOfName(String sql) {
StringTokenizer tokenizer = new StringTokenizer(sql, "?");
String trimmedSql = null;
String token = null;
boolean first = true;
while(tokenizer.hasMoreTokens()) {
token = tokenizer.nextToken();
if (first) {
trimmedSql = token + "?";
first = false;
continue;
}
token = retrieveParam(token);
if (token != null && !"".equals(token.trim())) {
trimmedSql = trimmedSql + token + "?";
}
}
return trimmedSql;
}

private String retrieveParam(String para) {
int index = para.indexOf(" ");
String param = para.substring(0, index);
namedParams.add(param);
return para.substring(index);
}

public static void main(String args[]) {
Connection connection = null;
try {
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@(description=(address=(host=hostName)(protocol=tcp)(port=1521))(connect_data=(sid=shom)))";
String userId = "nasa";
String password = "hacked";
DriverManager.registerDriver((Driver) Class.forName(driver).newInstance());
connection = (Connection) DriverManager.getConnection(url,userId,password);

String sql = "SELECT * FROM SPACE WHERE STUDIO_NO = NVL (?studio , STUDIO_NO) AND SID = NVL (?sid , SID) AND ORG = ?org ";
NamedParameterStatement np = new NamedParameterStatement(connection, sql);
np.setString("studio", "F12");
np.setString("sid", "1");
np.setString("org", "B");
ResultSet rst = np.pstmt.executeQuery();
while(rst.next()) {
System.out.println(rst.getString("MODEL"));
}
} catch (Exception excp) {
excp.printStackTrace();
}

}

}
###@###.### 2005-04-11 20:16:33 GMT