private int getClosedResult(ActionServlet serv, Date startDate, Date endDate, String plus) {
int result=0;
Connection connection;
PreparedStatement pst;
try {
String getClosedResultHql = "select count(r.rptno) from rpthead r, product_line productline where r.portid=productline.product_line_id and r.closed_date>to_date('?','yyyy/mm/dd') and r.closed_date<=to_date('?','yyyy/mm/dd') and " + plus;
connection=this.getConnection(serv);
pst=connection.prepareStatement(getClosedResultHql);
pst.setString(1, "2011/04/13");
pst.setString(2, "2011/04/26");
ResultSet rs=pst.executeQuery(); rs.next(); //only have one element to get
result=rs.getInt(1);
System.out.println("apple:result="+result+"\n");
pst.close();
connection.close(); }catch (Exception ee) {
ee.printStackTrace();
} return result;
}
感觉这个有点问题:String getClosedResultHql = "select count(r.rptno) from rpthead r, product_line productline where r.portid=productline.product_line_id and r.closed_date>to_date('?','yyyy/mm/dd') and r.closed_date<=to_date('?','yyyy/mm/dd') and " + plus; 我把单引号去掉,又得到了这样的错误:java.sql.SQLException: Syntax error or access violation, message from server: "You have an error in your SQL syntax; chec
k the manual that corresponds to your MySQL server version for the right syntax to use near '('2011/04/13','yyyy/mm/dd') and r.close
d_date<=to_date('2011/04/26','yyyy/mm/dd'' at line 1"
int result=0;
Connection connection;
PreparedStatement pst;
try {
String getClosedResultHql = "select count(r.rptno) from rpthead r, product_line productline where r.portid=productline.product_line_id and r.closed_date>to_date('?','yyyy/mm/dd') and r.closed_date<=to_date('?','yyyy/mm/dd') and " + plus;
connection=this.getConnection(serv);
pst=connection.prepareStatement(getClosedResultHql);
pst.setString(1, "2011/04/13");
pst.setString(2, "2011/04/26");
ResultSet rs=pst.executeQuery(); rs.next(); //only have one element to get
result=rs.getInt(1);
System.out.println("apple:result="+result+"\n");
pst.close();
connection.close(); }catch (Exception ee) {
ee.printStackTrace();
} return result;
}
感觉这个有点问题:String getClosedResultHql = "select count(r.rptno) from rpthead r, product_line productline where r.portid=productline.product_line_id and r.closed_date>to_date('?','yyyy/mm/dd') and r.closed_date<=to_date('?','yyyy/mm/dd') and " + plus; 我把单引号去掉,又得到了这样的错误:java.sql.SQLException: Syntax error or access violation, message from server: "You have an error in your SQL syntax; chec
k the manual that corresponds to your MySQL server version for the right syntax to use near '('2011/04/13','yyyy/mm/dd') and r.close
d_date<=to_date('2011/04/26','yyyy/mm/dd'' at line 1"
and r.closed_date<=to_date('2011/04/26','yyyy/mm/dd'' 你没发现你这个语句不对么?
_line_id and r.closed_date>to_date('2011/04/13','yyyy/mm/dd') and r.closed_date<=to_date('2011/04/26','yyyy/mm/dd') and 1=1 and ( r.product_id=5434 or r.product_id=5435 or r.product_id=5433 or r.product_id=5440 or r.product_id=5242) and r.customer not like '%INTERNAL%';COUNT(R.RPTNO)
--------------
38
但谢谢在java语言中就不成功啊
Date endDate, String plus) {
int result=0;
Connection connection;
PreparedStatement pst;
try {
String getClosedResultHql = "select count(r.rptno) from rpthead r, product_line productline where r.portid=productline.product_line_id and r.closed_date>to_date('2011/04/13','yyyy/mm/dd') and r.closed_date<=to_date('2011/04/26','yyyy/mm/dd') and 1=1 and ( r.product_id=5434 or r.product_id=5435 or r.product_id=5433 or r.product_id=5440 or r.product_id=5242) and r.customer not like '%INTERNAL%'"; if(debug)
System.out.println("****StatisticReportHandler_bugdb.getClosedResult*****sql : "+getClosedResultHql);
connection=this.getConnection(serv);
System.out.println("apple:getClosedResultHql="+getClosedResultHql+"\n");
pst=connection.prepareStatement(getClosedResultHql); ResultSet rs=pst.executeQuery(); rs.next(); //only have one element to get
result=rs.getInt(1);
System.out.println("apple:result="+result+"\n");
pst.close();
connection.close(); }catch (Exception ee) {
ee.printStackTrace();
}
if(debug)
System.out.println("****StatisticReportHandler_bugdb.getClosedResult*****result : "+result);
return result;
}
但是,手动登陆到数据库中,运行命令:SQL> select count(r.rptno) from rpthead r, product_line productline where r.port
id=productline.product_line_id and r.closed_date>to_date('2011/04/13','yyyy/mm/d
d') and r.closed_date<=to_date('2011/04/26','yyyy/mm/dd') and 1=1 and (
2 r.product_id=5434 or r.product_id=5435 or r.product_id=5433 or r.product_id
=5440 or r.product_id=5242) and r.customer not like '%INT
3 ERNAL%';COUNT(R.RPTNO)
--------------
136就成功的
setDate(new java.sql.Date(Calender.getInstance().getTime()))
String getClosedResultHql = "select count(r.rptno) from rpthead r, product_line productline where r.portid=productline.product_line_id and r.closed_date>to_date(?,'yyyy/mm/dd') and r.closed_date<=to_date(?,'yyyy/mm/dd') and " + plus;
将getClosedResultHql完成的打印出来看看,肯定是语句的错误