How to convert 2d array from PostgreSQL DB to java

问题:

How can I convert a 2d char array from my Postgres DB to a native Java char[][]? This is my attempt based on this answer:

import java.sql.Array;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.skife.jdbi.v2.StatementContext;
import org.skife.jdbi.v2.tweak.ResultSetMapper;

public class GameMapper implements ResultSetMapper<Game>{
    public Game map(int index, ResultSet resultSet, StatementContext statementContext) throws SQLException
    {
        Array board = resultSet.getArray("BOARD");
        return new Game(resultSet.getInt("ID"),
                        resultSet.getInt("WHOSE_TURN"),
                        resultSet.getInt("WINNER"),
                        (char[][]) board.getArray());
    }
}

Data class:

public class Game {
    protected int id; 
    protected int whoseTurn;
    protected int winner;
    protected char[][] board;

    public Game(int id, int turn, int winner, char[][] board ) {
        this.id=id;
        this.whoseTurn=turn;
        this.winner=winner;
        this.board=board;
    }

    @JsonProperty
    public int getId() {
        return id;
    }

    @JsonInclude(Include.NON_NULL)
    public int getWhoseTurn() {
        return whoseTurn;
    }

    @JsonInclude(Include.NON_NULL)
    public int getWinner() {
        return winner;
    }

    public char[][] getBoard() {
        return board;
    }   
}

DAO:

@RegisterMapper(GameMapper.class)
public interface GameDAO {

    @SqlUpdate("create table if not exists GAMES (ID integer, WHOSE_TURN varchar(10), WINNER varchar(10), BOARD char(1)[][])")
    void createTableIfNotExists();

    @SqlUpdate("insert into GAMES (ID, WHOSE_TURN, WINNER, BOARD) values (:id, :whoseTurn, :winner, :board)")
    void insert(@BindBean Game game);
}

But this results in a cast error:

java.lang.ClassCastException: [[Ljava.lang.String; cannot be cast to [[C

Do I need to use ResultSet instead?

回答1:

[[Ljava.lang.String; cannot be cast to [[C

Time to learn what Java array type names mean.

[ is array-of. So [[ is array-of array-of, i.e. both type names are 2-d arrays.

L indicates a reference type, followed by the class name.

C is the primitive type char.

So here, we see that you are attempting to cast two-dimensional array of java.lang.String to a two-dimensional array of char. That doesn't make any sense. It looks like JDBI maps the Java data type char to an SQL string type like character or character varying, (verify by checking the table in the database). If so, the JDBC driver will return that from the database as java.lang.String.

So you need to either override that mapping to use the database data type "char" - a single character - or, more simply, assert that the strings are one character long and unpack them into a character. So you'll have to copy the arrays.

回答2:

I had a look at postgres JDBC driver. If the column type is CHAR or VARCHAR it tries to convert it to String array.

    else if (type == Types.CHAR || type == Types.VARCHAR)
    {
        Object[] oa = null;
        ret = oa = (dims > 1 ? (Object[]) java.lang.reflect.Array.newInstance(String.class, dimsLength) : new String[count]);
    }

So you will get 2D String array. You need to convert it to char[][] in the mapper.