private static QueryRunner qr;
static {
qr = new QueryRunner(JdbcUtils.getDataSource());
}
/*
* 查找评论
*/
public List<Comment> find(String blogID) {
try {
String sql = "select child.commentID,u.userName,child.commentContent,count(child.commentID) depth,child.commentParentID,child.commentTime from comment2 parent,comment2 child,user u where child.blogID = ? and child.blogID = parent.blogID and parent.left<=child.left and parent.right>=child.right and child.userID = u.userID group by child.commentID order by child.left";
Object params[] = { blogID };
List<Comment> list = qr.query(sql, new BeanListHandler<Comment>(
Comment.class), params);
sql = "select u.userName from user u,comment c where c.commentID = ? and u.userID = c.userID";
for (int i = 0; i < list.size(); i++) {
if (list.get(i).getCommentParentID() != null) {
Object params1[] = { list.get(i).getCommentParentID() };
String userName = qr.query(sql,new ScalarHandler<String>(), params1);
Comment commentParent = qr.query(sql, new BeanHandler<Comment>(Comment.class), params1);
System.out.println(sql+ commentParent.getUserName() );
list.get(i).setCommentParent(commentParent);
}
}
return list;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}上面代码中,使用了dbutils这个工具,第一个sql语句能顺利查出结果,第二个sql语句什么都查不到,不管是用ScalarHandler还是beanHandler接收查到的数据都是空(userName和commentParent都为空),sql语句我在mysql试过了没问题能查出东西,param1[]里的东西也能正确传进去,可是就是查不出来,希望高手帮帮忙,帮我找找问题。
static {
qr = new QueryRunner(JdbcUtils.getDataSource());
}
/*
* 查找评论
*/
public List<Comment> find(String blogID) {
try {
String sql = "select child.commentID,u.userName,child.commentContent,count(child.commentID) depth,child.commentParentID,child.commentTime from comment2 parent,comment2 child,user u where child.blogID = ? and child.blogID = parent.blogID and parent.left<=child.left and parent.right>=child.right and child.userID = u.userID group by child.commentID order by child.left";
Object params[] = { blogID };
List<Comment> list = qr.query(sql, new BeanListHandler<Comment>(
Comment.class), params);
sql = "select u.userName from user u,comment c where c.commentID = ? and u.userID = c.userID";
for (int i = 0; i < list.size(); i++) {
if (list.get(i).getCommentParentID() != null) {
Object params1[] = { list.get(i).getCommentParentID() };
String userName = qr.query(sql,new ScalarHandler<String>(), params1);
Comment commentParent = qr.query(sql, new BeanHandler<Comment>(Comment.class), params1);
System.out.println(sql+ commentParent.getUserName() );
list.get(i).setCommentParent(commentParent);
}
}
return list;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}上面代码中,使用了dbutils这个工具,第一个sql语句能顺利查出结果,第二个sql语句什么都查不到,不管是用ScalarHandler还是beanHandler接收查到的数据都是空(userName和commentParent都为空),sql语句我在mysql试过了没问题能查出东西,param1[]里的东西也能正确传进去,可是就是查不出来,希望高手帮帮忙,帮我找找问题。
所以你每执行一次query都必须qr = new QueryRunner(JdbcUtils.getDataSource());
先试试看这样能不能解决问题,至于QueryRunner怎么保持长连接,你自己再查查api吧,但新问题是如何关闭数据库连接。
* 查找评论
*/
public List<Comment> find(String blogID) {
try {
String sql = "select child.commentID,u.userName,child.commentContent,count(child.commentID) depth,child.commentParentID,child.commentTime from comment2 parent,comment2 child,user u where child.blogID = ? and child.blogID = parent.blogID and parent.left<=child.left and parent.right>=child.right and child.userID = u.userID group by child.commentID order by child.left";
Object params[] = { blogID };
List<Comment> list = qr.query(sql, new BeanListHandler<Comment>(
Comment.class), params);
sql = "select u.userName from user u,comment c where c.commentID = ? and u.userID = c.userID";
for (int i = 0; i < list.size(); i++) {
if (list.get(i).getCommentParentID() != null) {
//增加的获取queryrunner
qr = new QueryRunner(JdbcUtils.getDataSource());
Object params1[] = { list.get(i).getCommentParentID() };
String userName = qr.query(sql,new ScalarHandler<String>(1), params1);
//增加的后台打印结果
System.out.println(list.get(i).getCommentParentID() +":"+ userName);
}
}
return list;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}改成了这样以后。。后台的结果和数据库的结果