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