How to read ArrayList<String> field from database with PostgreSQL JDBC

95
July 02, 2022, at 02:10 AM

I'm having an issue with getting the value of stored ArrayList<String> as VARCHAR[] in the database.

Somehow, when I'm debugging, I can see that the value returning from the database is:

I am expecting to store an ArrayList<String> by converting it to VARCHAR[] and when reading from the DB I expect to convert VARCHAR[] to ArrayList<String>

Code:

 public Message<ArrayList<String>> getPlayersInSession(Object data) throws SQLException, ClassNotFoundException {
        String sessionCode = (String) data;
        Session session = PostgreSQLJDBC.sessionJDBCInstance().getSession(sessionCode);
        //session.getPlayers() is the problem I have
        return new Message<>(Message.RequestCode.RECEIVE_PLAYERS_IN_SESSION, session.getPlayers());
(Message.RequestCode.RECEIVE_PLAYERS_IN_SESSION, players);
    }
public class Session {
    private int _id;
    private String _code;
    private ArrayList<String> _players;
    private ArrayList<Pair<String, String>> _leaderboard;
    public Session() {
        _code = "";
        _players = new ArrayList<>();
        _leaderboard = new ArrayList<>();
    }
    ...
}
public class SessionJDBC implements SessionSQL {
    private final Connection _connection;
    public SessionJDBC(String url, String user, String password) throws ClassNotFoundException, SQLException {
        Class.forName("org.postgresql.Driver");
        _connection = DriverManager.getConnection(url, user, password);
        if (!sessionTableExists()) createTable();
    }
    @Override
    public void createTable() throws SQLException {
        String sql = "CREATE TABLE session(" +
                "id SERIAL PRIMARY KEY," +
                "code VARCHAR," +
                "players VARCHAR[]," +
                "leaderboard VARCHAR[]" +
                ")";
        PreparedStatement ps = _connection.prepareStatement(sql);
        ps.executeUpdate();
    }
    @Override
    public void addSession(Session session) throws SQLException {
        String sql = "INSERT INTO session(code, players, leaderboard)"
                + "VALUES (?,?,?)";
        PreparedStatement ps = _connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        ps.setString(1, session.getCode());
        ps.setArray(2, _connection.createArrayOf("VARCHAR", session.getPlayers().toArray()));
        ps.setArray(3, _connection.createArrayOf("VARCHAR", session.getLeaderboard().toArray()));
        ps.executeUpdate();
        ResultSet generatedKeys = ps.getGeneratedKeys();
        if (generatedKeys.next()) {
            session.setId(generatedKeys.getInt(1));
        }
    }
    @Override
    public void removeSession(Session session) throws SQLException {
        String sql = "DELETE FROM session WHERE id = ?";
        PreparedStatement ps = _connection.prepareStatement(sql);
        ps.setInt(1, session.getId());
        ps.executeUpdate();
    }
    @Override
    public Session getSession(String code) throws SQLException {
        return getAllSessions().stream().filter(session -> session.getCode().equals(code)).findFirst().orElse(null);
    }
    @Override
    public ArrayList<Session> getAllSessions() throws SQLException {
        ArrayList<Session> array = new ArrayList<>();
        ResultSet result = _connection.prepareStatement("SELECT * FROM session").executeQuery();
        while (result.next()) {
            Session session = new Session();
            session.setCode(result.getString("code"));
            session.setId(result.getInt("id"));
            session.setPlayers(new ArrayList(Collections.singletonList(result.getArray("players"))));
            session.setLeaderboard(new ArrayList(Collections.singletonList(result.getArray("leaderboard"))));
            array.add(session);
        }
        result.close();
        return array;
    }
    @Override
    public boolean sessionTableExists() throws SQLException {
        DatabaseMetaData dbm = _connection.getMetaData();
        ResultSet tables = dbm.getTables(null, null, "session", null);
        return tables.next();
    }
}
Answer 1

I don't know how the code example compiles given that the ArrayList shown in the debugger is actually of type ArrayList<PgArray> rather than ArrayList<String>.

The problem is occurring in these lines:

session.setPlayers(new ArrayList(Collections.singletonList(result.getArray("players"))));
session.setLeaderboard(new ArrayList(Collections.singletonList(result.getArray("leaderboard"))));

For a start result.getArray("players") is returning a java.sql.Array object, more specifically a PgArray implementation. To get the real underlying data you need to do:

(String[])result.getArray("players").getArray();

The next problem is that you are using Collections.singletonList(). What this does is produce an ArrayList with only one element. Instead what you should use is Arrays.asList. Full solution:

session.setPlayers(new ArrayList(Arrays.asList((String[])result.getArray("players").getArray)));
session.setLeaderboard(new ArrayList(Arrays.asList((String[])result.getArray("leaderboard").getArray)));

Another thing that is interesting about this code is that you are selecting all rows from the table and then filtering in memory by streaming over the results. Why not select on the code field in an SQL query?

Rent Charter Buses Company
READ ALSO
Connection error returned from successful microservices call

Connection error returned from successful microservices call

Consider a scenario where I have 3 microservices say A, B, and C

125
Regex: Only returns message string - That&#39;s starts with messages and string between parent message curly brace

Regex: Only returns message string - That's starts with messages and string between parent message curly brace

I want to get all the message data onlySuch that it should look for message and all the data between curly braces of the parent message

122
Java 8 Mvn Project doesn&#39;t work on Java 17 [closed]

Java 8 Mvn Project doesn't work on Java 17 [closed]

Edit the question to include desired behavior, a specific problem or error, and the shortest code necessary to reproduce the problemThis will help others answer the question

67
Can&#39;t create table &quot;system_user&quot; in h2

Can't create table "system_user" in h2

i am having a problem creating a simple table in h2

65