sql query doesn't work in eclipse

Question

I have a query which run well in SQLyog but not in eclipse. The error showed is "Column not found" when I debugged.

Here is my query:

String searchQuery = "select AVG(Rating) from rating where CoursesID=? and MONTH(dtDate)=? group by dtDate";

table:

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;

Is that anything need to add into it so that it can be run in eclipse?

• Butani Vijay: Please show your table
• Conrad Lotz: What does you connection string to the database look like?
• Kushal: Check your table create statement
• Sue: Here is my java. @ConradLotz

Answer 1

You are using Rating as rs.getString("Rating") while Rating has no Alias in the sql statement while being used in aggregation. Just add an alias

SELECT AVG(Rating) AS Rating ....

Answer 2

In your query, you're not defining a name for the column you're retrieving:

select AVG(Rating) from ...

So, when you execute this:

rs.getString("Rating")

You get an exception. Change the query to:

select AVG(Rating) AS Rating from ...

Answer 3

monthRating.add(rs.getString("Rating"));

You are fetching Rating column from resultSet but query has AVG(Rating) as column name so change it to,

monthRating.add(rs.getString("AVG(Rating)");