Query Java doesn’t execute

I’m trying to execute a query on eclipse at my Java program, but it always exit with a exception and don’t execute the query, I read a lot of posts here but no one of them could help me with this.

public static ResultSet queryAccAmount;

public static String query = " SELECT COUNT(*) s FROM (SELECT  L.ACCNUMBER AS AN, L.PROD AS PRD " 
        + " FROM LTRANS(nolock) L, AN(nolock) ANM " 
        + " WHERE DATA BETWEEN 20210101 AND 20210228 " 
        + " AND L.PRD IN (1, 2, 3) " 
        + " AND L.ACCNUMBER = ANM.ACCNUMBER "
        + " AND L.ISS = ANM.ISS " 
        + " AND L.PROD = ANM.PROD " 
        + " AND L.MESSAGECODE <> 'MESSAGE' "
        + " GROUP BY L.PROD, L.ACCNUMBER) S; ";

public static void main(String[] args) throws InterruptedException {

        Connection c = connect();

        int accAmount = 0;
        
        try {
            queryAccAmount = c.createStatement().executeQuery(query);

            accAmount = Integer.parseInt(queryAccAmount.getString(1));
            LOG.info("Mounth total: " + accAmount);
        } catch (Exception ex) {
            System.out.println(ex);
        } finally {
            TimeUnit.SECONDS.sleep(10);
            disconnect();
        }
}

The error when execute the program:

JavaError

But at the base have data:

DataBase

I don’t know what is wrong with this, who could help me I’m already appreciate!

Answer

You need to call executeQuery once and then read the results in loop for example Updating my comment with working example:-

import java.sql.*;

public class JDBCConnector {
   // JDBC driver name and database URL
   static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
   static final String DB_URL = "jdbc:mysql://localhost/EMP";

   //  Database credentials
   static final String USER = "root";
   static final String PASS = "mysql";
   
   public static void main(String[] args) {
   Connection conn = null;
   Statement stmt = null;
   try{
      //STEP 2: Register JDBC driver
      Class.forName("com.mysql.jdbc.Driver");

      //STEP 3: Open a connection
      System.out.println("Connecting to database...");
      conn = DriverManager.getConnection(DB_URL,USER,PASS);

      //STEP 4: Execute a query
      System.out.println("Creating statement...");
      stmt = conn.createStatement();
      String sql;
      sql = "SELECT id, first, last, age FROM Employees";
      ResultSet rs = stmt.executeQuery(sql);

      //STEP 5: Extract data from result set
      while(rs.next()){
         //Retrieve by column name
         int id  = rs.getInt("id");
         int age = rs.getInt("age");
         String first = rs.getString("first");
         String last = rs.getString("last");

         //Display values
         System.out.print("ID: " + id);
         System.out.print(", Age: " + age);
         System.out.print(", First: " + first);
         System.out.println(", Last: " + last);
      }
      //STEP 6: Clean-up environment
      rs.close();
      stmt.close();
      conn.close();
   }catch(SQLException se){
      //Handle errors for JDBC
      se.printStackTrace();
   }catch(Exception e){
      //Handle errors for Class.forName
      e.printStackTrace();
   }finally{
      //finally block used to close resources
      try{
         if(stmt!=null)
            stmt.close();
      }catch(SQLException se2){
      }// nothing we can do
      try{
         if(conn!=null)
            conn.close();
      }catch(SQLException se){
         se.printStackTrace();
      }//end finally try
   }//end try
   System.out.println("Goodbye!");
}//end main
}//end FirstExample