java.sql.SQLException - column not found

Question

I cannot find answer to my issue, though I've looked at other similar queries. My exercise was to join two tables in Mysql (tables USERS and POSTS) and when posts are added by a user, check if the actual count of users that posted at least twice changes.

The test stops when it reaches line of printing posts_number, firstname and lastname, saying that column couldn't be found. (I tried switching the order, but the same error pops up). Anyone having correction ideas?

Here is my java code:

@Test
public void testSelectUsersAndPosts () throws SQLException {

    //given
    DbManager dbManager = DbManager.getInstance();
    String countQuery = "SELECT COUNT(*) FROM POSTSBYNUMBER";
    Statement statement = dbManager.getConnection().createStatement();
    ResultSet rs = statement.executeQuery(countQuery);
    int count = 0;
    while (rs.next()) {
        count = rs.getInt("COUNT(*)");
    }

    String sql = "INSERT INTO POSTS(USER_ID, BODY) VALUES ('8', 'ha')";
    statement.executeUpdate(sql);
    sql = "INSERT INTO POSTS(USER_ID, BODY) VALUES ('8', 'yes')";
    statement.executeUpdate(sql);

    //when
    String sqlQuery = "SELECT COUNT(*) FROM POSTSBYNUMBER";
    statement = dbManager.getConnection().createStatement();
    rs = statement.executeQuery(sqlQuery);

    //then
    int counter = 0;
    while(rs.next()) {
        System.out.println(rs.getInt("POSTS_NUMBER") + ", " +   //HERE MY ERROR POPS UP 
                rs.getString("FIRSTNAME") + ", " +
                rs.getString("LASTNAME"));
        counter++;
    }


        int expected = count + 1 ;
        Assert.assertEquals(expected, counter);

        rs.close();
        statement.close();
    }

Table created using CREATE VIEW:

CREATE VIEW POSTSBYNUMBER AS
    SELECT u.FIRSTNAME, u.LASTNAME, COUNT(*) AS POSTS_NUMBER
    FROM USERS u JOIN
         POSTS p
         ON u.ID = p.USER_ID
    GROUP BY u.FIRSTNAME, u.LASTNAME
    HAVING COUNT(*) >= 2;

You are making mistake in your query and result set fetching . Check this link https://stackoverflow.com/a/23370254/12224743 . You need to change ```String countQuery = "SELECT COUNT(*) FROM POSTSBYNUMBER";``` as ```String countQuery = "SELECT COUNT(*) AS COUNT FROM POSTSBYNUMBER";``` — Umeshwaran, Mar 25 '21 at 19:38
and the fetch the result set using ```count = rs.getInt(COUNT);``` — Umeshwaran, Mar 25 '21 at 19:39

score 0 · Answer 1 · answered Mar 25 '21 at 19:44

0

Change your count query as String countQuery = "SELECT COUNT(*) AS COUNT FROM POSTSBYNUMBER";

and then fetch result set as count = rs.getInt("COUNT");.

Your code count = rs.getInt("COUNT(*)"); misinterprets and tells JDBC to look for a column named "COUNT(*)". By the definition of your view , you already have alias for count as POST_NUMBER. you may use that as well if the data is already populated in it.

answered Mar 25 '21 at 19:44

Umeshwaran

627
6
12

ok, after I introduced the changes as suggested, still I am not able to print firstname and secondname column...why? – Veronique Mar 25 '21 at 20:28
That's because you are not querying for them . If you want first name and last name , change your query like ```String sqlQuery = "SELECT COUNT(*) AS COUNT,FIRSTNAME,LASTNAME FROM POSTSBYNUMBER"; ``` – Umeshwaran Mar 25 '21 at 20:57

java.sql.SQLException - column not found

1 Answers1