81,094
社区成员
发帖
与我相关
我的任务
分享
/**
* 通用的查询方法
* @param exprList
* @param pc
* @return
* @throws SQLException
*/
private PageBean<Book> findByCriteria(List<Expression> exprList, int pc) throws SQLException {
/*
* 1. 得到ps
* 2. 得到tr
* 3. 得到beanList
* 4. 创建PageBean,返回
*/
/*
* 1. 得到ps
*/
int ps = PageConstants.BOOK_PAGE_SIZE;//每页记录数
/*
* 2. 通过exprList来生成where子句
*/
StringBuilder whereSql = new StringBuilder(" where 1=1");
List<Object> params = new ArrayList<Object>();//SQL中有问号,它是对应问号的值
for(Expression expr : exprList) {
/*
* 添加一个条件上,
* 1) 以and开头
* 2) 条件的名称
* 3) 条件的运算符,可以是=、!=、>、< ... is null,is null没有值
* 4) 如果条件不是is null,再追加问号,然后再向params中添加一与问号对应的值
*/
whereSql.append(" and ").append(expr.getName())
.append(" ").append(expr.getOperator()).append(" ");
// where 1=1 and bid = ?
if(!expr.getOperator().equals("is null")) {
whereSql.append("?");
params.add(expr.getValue());
}
}
/*
* 3. 总记录数
*/
String sql = "select count(*) from t_book" + whereSql;
Number number = (Number)qr.query(sql, new ScalarHandler(), params.toArray());
int tr = number.intValue();//得到了总记录数
System.out.println(sql);
System.out.println(tr);
/*
* 4. 得到beanList,即当前页记录
*/
sql = "select * from t_book" + whereSql + " order by orderBy limit ?,?";
/*limit是MySQL独有的,我就改成了"select * from (select *,ROW_NUMBER() OVER (ORDER BY orderBy) as rank from t_book "+whereSql+") as t where t.rank between ? and ?";*/
params.add((pc-1) * ps);//当前页首行记录的下标
params.add(ps);//一共查询几行,就是每页记录数
List<Book> beanList = qr.query(sql, new BeanListHandler<Book>(Book.class),
params.toArray());
System.out.println(sql);
System.out.println(beanList);
return null;
}
public static void main(String[] args){
BookDao bookDao = new BookDao();
List<Expression> exprList = new ArrayList<Expression>();
exprList.add(new Expression("bid", "=", "1"));//第一个测试条件
exprList.add(new Expression("bname", "like", "%java%"));//第二个测试条件
exprList.add(new Expression("edition", "is null", null));//第个测试条件
bookDao.findByCriteria(exprList, 10);
}
log4j:WARN No appenders could be found for logger (com.mchange.v2.log.MLog).
log4j:WARN Please initialize the log4j system properly.
select count(*) from t_book where 1=1 and bname like ?
78
java.sql.SQLException: com.microsoft.sqlserver.jdbc.SQLServerException: ',' 附近有语法错误。 Query: select * from t_book where 1=1 and bname like ? order by orderBy limit ?,? Parameters: [%java%, 108, 12]
at org.apache.commons.dbutils.AbstractQueryRunner.rethrow(AbstractQueryRunner.java:320)
at org.apache.commons.dbutils.QueryRunner.query(QueryRunner.java:349)
at org.apache.commons.dbutils.QueryRunner.query(QueryRunner.java:210)
at cn.itcast.jdbc.TxQueryRunner.query(TxQueryRunner.java:23)
at cn.itcast.goods.book.dao.BookDao.findByCriteria(BookDao.java:165)
at cn.itcast.goods.book.dao.BookDao.main(BookDao.java:180)
select * from (select *,ROW_NUMBER() OVER (ORDER BY orderBy) as rank from t_book and "+whereSql+") as t where t.rank between ? and ?
我用下面这个方法为什么只是替换掉了whereSql这里面的一个问号,后面两个怎么没替换掉啊?
StringBuilder whereSql = new StringBuilder(" where 1=1");
List<Object> params = new ArrayList<Object>();//SQL中有问号,它是对应问号的值
for(Expression expr : exprList) {
/*
* 添加一个条件上,
* 1) 以and开头
* 2) 条件的名称
* 3) 条件的运算符,可以是=、!=、>、< ... is null,is null没有值
* 4) 如果条件不是is null,再追加问号,然后再向params中添加一与问号对应的值
*/
whereSql.append(" and ").append(expr.getName())
.append(" ").append(expr.getOperator()).append(" ");
// where 1=1 and bid = ?
if(!expr.getOperator().equals("is null")) {
whereSql.append("?");
params.add(expr.getValue());
}
}
/*
* 3. 总记录数
*/
String sql = "select count(*) from t_book" + whereSql;
Number number = (Number)qr.query(sql, new ScalarHandler(), params.toArray());
int tr = number.intValue();//得到了总记录数
System.out.println(tr);
/*
* 4. 得到beanList,即当前页记录
*/
sql = "select * from (select *,ROW_NUMBER() OVER (ORDER BY orderBy) as rank from t_book "+whereSql+") as t where t.rank between ? and ? ";
params.add(ps);//一共查询几行,就是每页记录数
params.add((pc-1) * ps);//当前页首行记录的下标
List<Book> beanList = qr.query(sql, new BeanListHandler<Book>(Book.class),
params.toArray());