Insert 2d array into PostreSQL DB using JDBI?

Question

Can I use the SQL Object API to map Game to a row in the GAMES DB? Here is my attempt:

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

When insert is called I get this error:

org.postgresql.util.PSQLException: Can't infer the SQL type to use for an instance of [[C. Use setObject() with an explicit Types value to specify the type to use.

What is [[C? Can I make this work somehow? If not I'd really appreciate an alternative.

Manikandan · Accepted Answer · 2015-10-11 11:21:01Z

3

JDBI doesn't know how to cast array type. So we need to define ArgumentFactory for char[][] like this.

import org.skife.jdbi.v2.StatementContext;
import org.skife.jdbi.v2.tweak.Argument;
import org.skife.jdbi.v2.tweak.ArgumentFactory;

import java.sql.Array;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class CharArrayArgument implements ArgumentFactory<char[][]> {
    @Override
    public boolean accepts(Class<?> expectedType, Object value, StatementContext ctx) {
        return value != null && char[][].class.isAssignableFrom(value.getClass());
    }

    @Override
    public Argument build(Class<?> expectedType, final char[][] value, StatementContext ctx) {
        return new Argument() {
            @Override
            public void apply(int position, PreparedStatement statement, StatementContext ctx) throws SQLException {
                Array values = statement.getConnection().createArrayOf("char", value);
                statement.setArray(position, values);
            }
        };
    }
}

Register this argumentFactory to dbi. It should work.

    dbi.registerArgumentFactory(new CharArrayArgument());
    GameDao gameDao = dbi.open(GameDao.class);
    Game game = new Game(1, 2, 3, new char[][]{{'a'}, {'b'}});

    gameDao.createTableIfNotExists();
    gameDao.insert(game);

answered Oct 11, 2015 at 11:21

Manikandan

3,1752 gold badges22 silver badges29 bronze badges

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

1 Comment

JamesGold Over a year ago

Thank you. Do you think you could answer this question as well? Now that I can store char[][] in the database I am not sure how to cast back to java char[][] after I've retrieved it. Probably should have asked in the original question.

Collectives™ on Stack Overflow

Insert 2d array into PostreSQL DB using JDBI?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related