java - sql查询在eclipse中不起作用

Question

我有一个在 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

Java

//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 中运行？

Answer 1

您正在使用 Rating 作为 rs.getString("Rating")，而 Rating 在聚合中使用时在 sql 语句中没有别名。只需添加一个别名

SELECT AVG(Rating) AS Rating ....