ResultSet in while loop cannot find column name

Question

I am trying to print out a list of data from my user table in DBeaver, but whenever I run the Java program to return a list, I get an error saying "userid" column name not found. Do I need to change something in my while loop? Here is my code:

@Override public List<User> findAll() { try(Connection conn = ConnectionUtil.getConnection()) { String sql = "SELECT * FROM user;"; Statement statement = conn.createStatement(); ResultSet result = statement.executeQuery(sql); List<User> list = new ArrayList<>(); while (result.next()) { User user = new User(); user.setUserId(result.getInt("userid")); user.setUsername(result.getString("username")); user.setPassword(result.getString("password")); user.setFirstName(result.getString("firstname")); user.setLastName(result.getString("lastname")); user.setEmail(result.getString("email")); list.add(user); } return list;

Here is my DBeaver table

Are you sure about the names of your columns? Could you share the table's schema? — vmallet
– vmallet, Commented May 1, 2021 at 20:56
Unrelated: "Do I need to change something ...?" Yes, you need to use try-with-resources with both statement and result, same as you already did for conn. — Andreas
– Andreas, Commented May 1, 2021 at 21:51

Davide D'Alto · Accepted Answer · 2021-05-01 23:00:39Z

user is a special keyword in PostgreSQL. That query is returning the username of the user logged in to the database. That's why columns don't match.

You need to escape the table name for the query to work as expected:

String sql = "SELECT * FROM \"user\";";

Java 9 gained enquote… methods to make this task more graceful. See this Answer on How to quote/escape identifiers such as column names with JDBC?.

Collectives™ on Stack Overflow

ResultSet in while loop cannot find column name

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related