0

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:

enter image description here

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();
    }

}

3
  • Could you provide a code example, and a better explanation of the problem, e.g what you expect to happen and what is happening? Commented Feb 7, 2022 at 17:22
  • @Dean Updated question. Commented Feb 7, 2022 at 17:30
  • Is the problem that the ArrayList only has one element? Commented Feb 7, 2022 at 17:49

1 Answer 1

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?

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

1 Comment

Amazing! it is indeed solved my problem, now I get the ArrayList like I wanted it to. And about the selection of all rows, you are right, somehow I haven't thought of that. Thank you so much

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.