Get integer value from count in sql request-CodePudding

I have a SQL Lite database, containing specific values i need to count. I need only once that are related to a certain value. If I write the following request in database :

SELECT count(Global_Sales) FROM Data WHERE Platform =='PC';

I get what I need. However if I use the same request in my function, all of the requests I keep in a txt file, it says this:

no such column: 'count'

The code that I use:

        try(Connection conn = DriverManager.getConnection(url))
        {

            Path pathRQ = Paths.get("resources/BaseRequests.txt");
            requests = Files.readAllLines(pathRQ, StandardCharsets.UTF_8);
            Statement stm = conn.createStatement();

            for(int i =0; i < requests.size();i  )
            {
                Value.add(stm.executeQuery(requests.get(i)).getInt("count"));
            }
        }
        catch(SQLException ex )
        {
             System.out.println(ex.getMessage());
        }
        catch(IOException ex )
        {
             System.out.println(ex.getMessage());
        }

I've tried to change "count" to "Global_Sales" - the same message appears just with a bit different text. I don't know why it happens,maybe that's because all the values in data base are kept as "TEXT" or maybe that's because I've did something wrong in my code here. Question is - How do i fix this?

CodePudding user response：

Can you try to do this?

Value.add(stm.executeQuery(requests.get(i)).getInt(1));

As far as I remember, since only one value is returned at time (1 row, 1 column) you should be able to use the code snippet above

CodePudding user response：

use an alias for the column:

SELECT count(Global_Sales) as colname FROM Data WHERE Platform ='P'

and then you can use it to get the value:

Value.add(stm.executeQuery(requests.get(i)).getInt("colname"));

BTW: Equals check in sql will be done by one eqauals sign not two.

CodePudding user response：

count is not the name of the column. The name of the column is count(Global_Sales).

As others have stated, you can add a column alias, however you don't need the column name. Method executeQuery, in interface java.sql.Statement, returns a ResultSet. First, you need to call method next, after which you can call method getInt(int) and use a column index rather than a column name.

try (Connection conn = DriverManager.getConnection(url)) {
    Path pathRQ = Paths.get("resources/BaseRequests.txt");
    requests = Files.readAllLines(pathRQ, StandardCharsets.UTF_8);
    for (int i = 0; i < requests.size(); i  ) {
        try (Statement stm = conn.createStatement();
             ResultSet rs = stm.executeQuery()) {
            if (rs.next()) {
                Value.add(rs.getInt(1));
            }
        }
    }
}
catch(IOException | SQLException ex) {
    ex.printStackTrace();
}

I can't test the above code since I don't have a replica of your SQLite database and I can't mock one up since the code in your question is not a minimal, reproducible example but I think it may be better to create a new, separate Statement for each [SQL] query.

Also recommended to print the entire stack trace when your code throws an exception, rather than just the error message. Note that some exceptions do not have a message so for such exceptions, just printing the message would be the same as ignoring the exception altogether.