/****
*二级代码转化为下拉框
*tableName 表名
*二级代码的key value
*过滤条件 useflag
*/
public String getOptions(String tableName, String[] keyAndvalue,String whereCondition,String defaultSelect) {
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
StringBuilder sb = new StringBuilder();
sb.append(" select "+keyAndvalue[0]+","+keyAndvalue[1]+" from "+tableName+" where 1=1 ");
sb.append(whereCondition);
StringBuffer strb = new StringBuffer("");
try {
con = DataSourceUtils.getConnection(pool);
ps = con.prepareStatement(sb.toString());
rs = ps.executeQuery();
while (rs.next()) {
strb.append(" <option value= \"");
strb.append(rs.getString(1));// 代码
strb.append("\"");
if (defaultSelect.equals(rs.getString(1)))
strb.append("selected=\"selected\" ");
strb.append(">");
strb.append(rs.getString(2));// 中文
strb.append("</option>\r\n");
}
} catch (SQLException e) {
logger.error("获得getOptions二级代码下拉框失败:"+e.getMessage(),e);
} finally {
ps.close();
rs.close();
con.close();
}
return strb.toString();
}
/****
*二级代码key转码value
*/
public String getOptionsValue(String tableName, String[] keyAndvalue, String whereCondition) {
String optionValue = "";
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
StringBuilder sb = new StringBuilder();
sb.append(" select "+keyAndvalue[0]+","+keyAndvalue[1]+" from "+tableName+" where 1=1 ");
sb.append(whereCondition);
con = DataSourceUtils.getConnection(pool);
ps = con.prepareStatement(sb.toString());
rs = ps.executeQuery();
while(rs.next()){
optionValue=rs.getString(keyAndvalue[1]);
}
} catch (SQLException e) {
logger.error("获得getOptionsValue二级代码下拉框失败:" + e.getMessage(), e);
} finally {
ps.close();
rs.close();
con.close();
}
return optionValue;
}
为提高效率可以把从数据库查询的数据系统初始化加载时放入缓存数据库