我有一个查询在SQLyog中运行得很好,但在eclipse中没有。调试时显示的错误是“找不到列”。
我的问题是:
String searchQuery = "select AVG(Rating) from rating where CoursesID=? and MONTH(dtDate)=? group by dtDate";
表格:
ID | CoursesID | Rating | Comment | dtDate
11111 | SKM3207 | 5 | No | 2015-05-20
爪哇
//preparing some objects for connection
Connection currentCon = null;
ResultSet rs = null;
PreparedStatement pstmt = null;
//double totalRate = 0.0;
Vector<String> monthRating = new Vector<String>();
try{
String searchQuery = "select AVG(Rating) from rating where CoursesID=? and MONTH(dtDate)=? group by dtDate";
//connect to DB
currentCon = ConnectionManager.getConnection();
pstmt=currentCon.prepareStatement(searchQuery);
pstmt.setString(1, CoursesID);
pstmt.setString(2, fmonth);
rs = pstmt.executeQuery();
while(rs.next()){
monthRating.add(rs.getString("Rating"));
//String avg = rs.getString(1);
//totalRate = Double.parseDouble(avg);
}
}
catch (Exception ex){
System.out.println("Log In failed: An Exception has occurred! " + ex);
}
//some exception handling
finally{
if (rs != null) {
try {
rs.close();
} catch (Exception e) {}
rs = null;
}
if (pstmt != null) {
try {
pstmt.close();
} catch (Exception e) {}
pstmt = null;
}
if (currentCon != null) {
try {
currentCon.close();
} catch (Exception e) {
}
currentCon = null;
}
}
return monthRating;
是否需要在其中添加任何内容,以便在eclipse中运行它?
最佳答案
您正在使用Rating asrs.getString("Rating")
,而Rating在聚合中使用时在sql语句中没有别名。只需添加别名
SELECT AVG(Rating) AS Rating ....
关于java - sql查询在eclipse中不起作用,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/30497004/