Get integer value from count in sql request

Question

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?

No, I need to count all the values in Global_Sales related to a certain value in Platform column. So if I've specified PC I need only count the values in Global_Sales that are related to PC. — scribre
– scribre, Commented Jan 1, 2023 at 10:31

Jens · Accepted Answer · 2023-01-01 10:23:00Z

1

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.

answered Jan 1, 2023 at 10:23

Jens

69.6k15 gold badges107 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

w4rcT · Accepted Answer · 2023-01-01 10:45:14Z

1

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

edited Jan 1, 2023 at 10:45

answered Jan 1, 2023 at 10:29

w4rcT

3421 gold badge2 silver badges13 bronze badges

1 Comment

Tyler2P Over a year ago

Your answer could be improved by adding more information on what the code does and how it helps the OP.

Abra · Accepted Answer · 2023-01-02 04:30:03Z

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(requests.get(i))) {
            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.

Collectives™ on Stack Overflow

Get integer value from count in sql request

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related