search

Ninja-Squad / DbSetup

An API for populating a database in unit tests
http://dbsetup.ninja-squad.com/
212 stars 34 forks source link

How to insert Postgres Jsonb value? #49

Closed totalwar closed 9 years ago

totalwar commented 9 years ago

When I try to prepare db table contains jsonb column I get exception. Version 1.6.0 Code:

insertInto('table').columns('json_col').values('{"smth":0}')

Stacktrace: Caused by: org.postgresql.util.PSQLException: ERROR: column "json_col" is of type jsonb but expression is of type character varying Hint: You will need to rewrite or cast the expression. at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2182) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1911) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:173) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:645) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:495) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:441) at com.zaxxer.hikari.proxy.PreparedStatementProxy.executeUpdate(PreparedStatementProxy.java:61) at com.ninja_squad.dbsetup.operation.Insert.execute(Insert.java:229) at com.ninja_squad.dbsetup.operation.CompositeOperation.execute(CompositeOperation.java:92) at com.ninja_squad.dbsetup.operation.CompositeOperation.execute(CompositeOperation.java:92) at com.ninja_squad.dbsetup.DbSetup.launch(DbSetup.java:107) ... 2 more

jnizet commented 9 years ago

Hi @totalwar

I have no experience with jsonb and PostgreSQL, so I don't know how you're even supposed to insert jsonb into PostgreSQL with pure JDBC. If you know how to do it with JDBC, then doing it with DbSetup is just a matter of using a specific Binder for this column:

insertInto('table').columns('json_col').values('{"smth":0}').withBinder(new Binder() {
    @Override
    public void bind(PreparedStatement statement, int param, Object value) {
        // use straight JDBC to bind the parameter to the statement
    }
});

Of course, this Binder could be a top-level class (or even a singleton) rather than an anonymous class, if you want to reuse it multiple times.

If you don't know how to implement the JDBC code, then tell me, and I'll try doing some research when I have time. But you could also ask the question on StackOverflow and have a much larger audience to help you.

Hope it helps. Sorry for the delay.

totalwar commented 9 years ago

Thanks, I've implemented custom Binder already, it works