请教高手:
如下代码:public int findProducts(List list,
int [] categoryId,
String keyWord,
double lowNormalPrice,
double highNormalPrice,
double lowMemberPrice,
double highMemberPrice,
Date startDate,
Date endDate,
int pageNo,// 分页
int pageSize){
Connection conn = null;
ResultSet rs = null;
ResultSet rsCount = null; // 为sqlCount计算结果页数设置
int pageCount = 0;
try {
conn = DB.getConn();
String sql = "select * from product where 1=1 ";
String strId = "";
if(categoryId != null && categoryId.length > 0){
strId += "(";
for(int i=0; i<categoryId.length; i++){
if(i < categoryId.length - 1){
strId += categoryId[i] + ",";
} else {
strId += categoryId[i];
}
}
strId += ")";
sql += " and categoryid in " + strId; }
if(keyWord != null && !keyWord.trim().equals("")){
sql += " and name like '%" + keyWord + "%' or descr like '%" + keyWord + "%'";
}
if(lowNormalPrice >= 0){
sql += " and normalprice > " + lowNormalPrice; }
if(highNormalPrice > 0){
sql += " and normalprice < " + highNormalPrice;
}
if(lowMemberPrice >= 0){
sql += " and memberprice > " + lowMemberPrice; }
if(highMemberPrice > 0){
sql += " and memberprice < " + highMemberPrice;
}
if(startDate != null){
sql += " and pdate >= '" + new SimpleDateFormat("yyyy-MM-dd").format(startDate) + "'";
}
if(endDate != null){
sql += " and pdate <= '" + new SimpleDateFormat("yyyy-MM-dd").format(endDate) + "'";
}
String sqlCount = sql.replaceFirst("select \\*", "select count(*)");//放在limit前面,否则出错,"select \\*"注意特殊符号
//System.out.println(sqlCount);验证替换是否正确
sql += " limit " + (pageNo-1)*pageSize + "," + pageSize;
System.out.println(sql);// 记录SQL,一旦有异常。
rsCount = DB.executeQuery(conn, sqlCount);
rsCount.next();
pageCount = (rsCount.getInt(1) + pageSize -1) / pageSize;
//System.out.println("共" + pageCount + "页");
rs = DB.executeQuery(conn, sql);
while(rs.next()){
Product p = new Product();
p.setId(rs.getInt("id"));
p.setName(rs.getString("name"));
p.setDescr(rs.getString("descr"));
p.setNormalPrice(rs.getDouble("normalPrice"));
p.setMemberPrice(rs.getDouble("memberPrice"));
p.setPdate(rs.getTimestamp("pdate"));
p.setCategoryId(rs.getInt("categoryId"));
list.add(p);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DB.closeRs(rs);
DB.closeConn(conn);
}
return pageCount;
}
上面的代码中,连续使用多次String类型的sql变量操作,是不是性能不好? 如果换成StringBuffer该怎么操作?麻烦高手改下 感激不尽~~~
如下代码:public int findProducts(List list,
int [] categoryId,
String keyWord,
double lowNormalPrice,
double highNormalPrice,
double lowMemberPrice,
double highMemberPrice,
Date startDate,
Date endDate,
int pageNo,// 分页
int pageSize){
Connection conn = null;
ResultSet rs = null;
ResultSet rsCount = null; // 为sqlCount计算结果页数设置
int pageCount = 0;
try {
conn = DB.getConn();
String sql = "select * from product where 1=1 ";
String strId = "";
if(categoryId != null && categoryId.length > 0){
strId += "(";
for(int i=0; i<categoryId.length; i++){
if(i < categoryId.length - 1){
strId += categoryId[i] + ",";
} else {
strId += categoryId[i];
}
}
strId += ")";
sql += " and categoryid in " + strId; }
if(keyWord != null && !keyWord.trim().equals("")){
sql += " and name like '%" + keyWord + "%' or descr like '%" + keyWord + "%'";
}
if(lowNormalPrice >= 0){
sql += " and normalprice > " + lowNormalPrice; }
if(highNormalPrice > 0){
sql += " and normalprice < " + highNormalPrice;
}
if(lowMemberPrice >= 0){
sql += " and memberprice > " + lowMemberPrice; }
if(highMemberPrice > 0){
sql += " and memberprice < " + highMemberPrice;
}
if(startDate != null){
sql += " and pdate >= '" + new SimpleDateFormat("yyyy-MM-dd").format(startDate) + "'";
}
if(endDate != null){
sql += " and pdate <= '" + new SimpleDateFormat("yyyy-MM-dd").format(endDate) + "'";
}
String sqlCount = sql.replaceFirst("select \\*", "select count(*)");//放在limit前面,否则出错,"select \\*"注意特殊符号
//System.out.println(sqlCount);验证替换是否正确
sql += " limit " + (pageNo-1)*pageSize + "," + pageSize;
System.out.println(sql);// 记录SQL,一旦有异常。
rsCount = DB.executeQuery(conn, sqlCount);
rsCount.next();
pageCount = (rsCount.getInt(1) + pageSize -1) / pageSize;
//System.out.println("共" + pageCount + "页");
rs = DB.executeQuery(conn, sql);
while(rs.next()){
Product p = new Product();
p.setId(rs.getInt("id"));
p.setName(rs.getString("name"));
p.setDescr(rs.getString("descr"));
p.setNormalPrice(rs.getDouble("normalPrice"));
p.setMemberPrice(rs.getDouble("memberPrice"));
p.setPdate(rs.getTimestamp("pdate"));
p.setCategoryId(rs.getInt("categoryId"));
list.add(p);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DB.closeRs(rs);
DB.closeConn(conn);
}
return pageCount;
}
上面的代码中,连续使用多次String类型的sql变量操作,是不是性能不好? 如果换成StringBuffer该怎么操作?麻烦高手改下 感激不尽~~~
解决方案 »
- 为什么在web.xml中给jsp配置<init-param>要添加<servlet-mapping>
- java写数据库备份模块
- jsp的网页出错
- linux不能访问access
- 定时问题,怎么执行不了呢?
- 求孙卫琴的《精通Struts》一书的netstore源码。
- 10几万的考生成绩查询的问题
- 请教.struts中<bean:write>的问题?Cannot find bean TopAction in scope reques
- 谁能给在下提供一个下载IBM Websphere全套开发工具及其server的地方,不胜感激!
- 急问hibernate插入问题
- 解析xml
- 关于Mysql环境变量的配置问题?
StringBuffer sql= new StringBuffer();
sql.append("select * from product where 1=1 ");
sql.append("and categoryid in " + strId");
sql.append("and normalprice > " + lowNormalPrice");
...
String strSql=sql.toString();
情况下,StringBuilder效率要比StringBuffer要高
按顺序追加 String 变量中的字符,使此序列增加该变量的长度。如果 str 为 null,则追加 4 个字符 "null"。 假设此字符序列的长度在执行 append 方法前为 n。如果 k 小于 n,则新字符序列中索引 k 处的字符等于原序列中索引 k 处的字符;否则它等于参数 str 中索引 k-n 处的字符。
参数:
str - 一个 string。
返回:
此对象的一个引用。