Now this requires to make a wrapper to java.sql.PreparedStatement. But the system is quite large and it is better for me to make static calls only. So I coded my own static methods for achiving the same goal. The code is found at the bottom of this post and you can use it like this:
Connection con = ds.getConnection("xxx");
String sql_p = "SELECT * FROM table WHERE table.ID=:sid ORDER BY ID DESC ";
// if you don't have null values in params list then make hashmap with param values
HashMap param_map = new HashMap(); param_map.put("sid", 1);
// if you might have null values in params list then make hashmap like this
HashMap param_map = new HashMap(); param_map.put("sid", new Object[] {null, java.sql.Types.INTEGER});
// make indexed query (with ? markers)
String sql_i = SQLHelper.namedParamParseSQL(sql_p, param_map);
PreparedStatement stmt = con.prepareStatement(sql_i);
// assign statement params
stmt = SQLHelper.namedParamAssign(stmt, param_map, sql_p);
The SQLHelper class can be found here:
package util;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
import java.util.ArrayList;
import java.util.HashMap;
import ee.eschool.exceptions.DWRException;
/**
* SQL Helper functions
*
* 1. Static class for named params in JDBC SQL.
* Original code here: http://www.javaworld.com/javaworld/jw-04-2007/jw-04-jdbc.html?page=1
* Usage instructions: http://imlostinit.blogspot.com/2009/04/javasqlpreparedstatement-with-named.html
* Usage sample:
* Connection con = ds.getConnection("xxx");
* String sql_p = "SELECT * FROM table WHERE table.ID=:sid ORDER BY ID DESC ";
* // if you don't have null values in params list then make hashmap with param values
* HashMap param_map = new HashMap(); param_map.put("sid", 1);
* // if you might have null values in params list then make hashmap like this
* HashMap param_map = new HashMap(); param_map.put("sid", new Object[] {null, java.sql.Types.INTEGER});
* // make indexed query (with ? markers)
* String sql_i = SQLHelper.namedParamParseSQL(sql_p, param_map);
* PreparedStatement stmt = con.prepareStatement(sql_i);
* // assign statement params
* stmt = SQLHelper.namedParamAssign(stmt, param_map, sql_p);
*
* @author erkulas
*
*/
public class SQLHelper {
/**
* Parse SQL query with named params and return SQL query with indexes .
*
* @param sql_query SQL query string with named parameters (like :param )
* @return parsed SQL query with indexes ( ? ) in the place of params ( :param )
*/
public static String namedParamParseSQL(String sql_query) {
int length=sql_query.length();
StringBuffer parsedQuery=new StringBuffer(length);
boolean inSingleQuote=false;
boolean inDoubleQuote=false;
for(int i=0;i<length;i++) {
char c=sql_query.charAt(i);
if(inSingleQuote) {
if(c=='\'') {
inSingleQuote=false;
}
} else if(inDoubleQuote) {
if(c=='"') {
inDoubleQuote=false;
}
} else {
if(c=='\'') {
inSingleQuote=true;
} else if(c=='"') {
inDoubleQuote=true;
} else if(c==':' && i+1<length && Character.isJavaIdentifierStart(sql_query.charAt(i+1))) {
int j=i+2;
while(j<length && Character.isJavaIdentifierPart(sql_query.charAt(j))) { j++; }
String name=sql_query.substring(i+1,j);
c='?'; // replace the parameter with a question mark
i+=name.length(); // skip past the end of the parameter
}
}
parsedQuery.append(c);
}
return parsedQuery.toString();
}
/**
* Parses sql_query and returns a list of params with "param_idx" value indicating the param order in SQL.
*
* @param sql_query SQL query string with named parameters (like :param )
* @param param_map HashMap of parameter values
* @return List of HashMaps with indexes according to their order in the sql_query
*/
public static List<HashMap&rt; namedParamGetIdx(String sql_query, HashMap param_map) throws SQLException {
List<HashMap&rt; indexList = new ArrayList();
int length=sql_query.length();
StringBuffer parsedQuery=new StringBuffer(length);
boolean inSingleQuote=false;
boolean inDoubleQuote=false;
int index=1;
Object sqltype, val;
for(int i=0;i<length;i++) {
char c=sql_query.charAt(i);
if(inSingleQuote) {
if(c=='\'') {
inSingleQuote=false;
}
} else if(inDoubleQuote) {
if(c=='"') {
inDoubleQuote=false;
}
} else {
if(c=='\'') {
inSingleQuote=true;
} else if(c=='"') {
inDoubleQuote=true;
} else if(c==':' && i+1<length && Character.isJavaIdentifierStart(sql_query.charAt(i+1))) {
int j=i+2;
while(j<length && Character.isJavaIdentifierPart(sql_query.charAt(j))) { j++; }
String name=sql_query.substring(i+1,j);
c='?'; // replace the parameter with a question mark
i+=name.length(); // skip past the end of the parameter
if(param_map.get(name)!=null) {
if(param_map.get(name) instanceof Object[]) { val = ((Object[])param_map.get(name))[0]; } else { val = param_map.get(name); }
HashMap hm = new HashMap(); hm.put("param_name", name); hm.put("param_val", val); hm.put("param_idx", index);
sqltype=java.sql.Types.VARCHAR;
if((param_map.get(name) instanceof Object[]) && (((Object[])param_map.get(name))[1]!=null)) { sqltype=((Object[])param_map.get(name))[1]; }
else if(val==null) { throw new SQLException("Param null and no SQLType specified."); }
hm.put("param_sqltype", sqltype);
indexList.add(hm);
index++;
}
else { throw new SQLException("Param "+name+" not found in mapping."); }
}
}
}
return indexList;
}
/**
* Assign PreparedStatement with the values from param_map according to their order in sql_p .
*
* @param stmt The PreparedStatement instance to be used.
* @param param_map An HashMap with parameter values.
* @param sql_p SQL query with named parameters ( :param )
* @return Returns the PreparedStatement with the params assigned.
*/
public static PreparedStatement namedParamAssign(PreparedStatement stmt, HashMap param_map, String sql_p) throws SQLException {
List<HashMap&rt; param_idx = namedParamGetIdx(sql_p, param_map);
return namedParamAssign(stmt, param_idx);
}
/**
* Assign PreparedStatement with the values from param_map according to their order in sql_p .
*
* @param stmt The PreparedStatement instance to be used.
* @param statement_idx_list List of params .
* @return Returns the PreparedStatement with the params assigned.
*/
public static PreparedStatement namedParamAssign(PreparedStatement stmt, List<HashMap&rt; statement_idx_list) {
for(Integer c=0; c<statement_idx_list.size(); c=c+1) {
Object val=statement_idx_list.get(c).get("param_val");
Integer idx=(Integer)statement_idx_list.get(c).get("param_idx");
Integer sqltype=(Integer)statement_idx_list.get(c).get("param_sqltype");
try {
if(val==null) { stmt.setNull(idx, sqltype); }
else if(val instanceof String) { stmt.setString(idx, (String)val); }
else if(val instanceof Integer) { stmt.setInt(idx, (Integer)val); }
else if(val instanceof Long) { stmt.setLong(idx, (Long)val); }
else if(val instanceof Boolean) { stmt.setBoolean(idx, (Boolean)val); }
else if(val instanceof java.util.Date) { stmt.setDate(idx, new java.sql.Date( ((java.util.Date)val).getTime() ) ); }
else if(val instanceof java.sql.Date) { stmt.setDate(idx, (java.sql.Date)val); }
} catch (SQLException e) { e.printStackTrace(); }
}
return stmt;
}
}
