How to map jsonb input parameter in Postgresql stored procedure in java

Question

Function definition:

CREATE OR REPLACE FUNCTION insert_json(
    p_name character varying,
    p_content jsonb)

Following java call ends with SQL Exception about need of param cast...

PreparedStatement ps = helper.prepareStatement(conn, "SELECT * FROM insert_json(?, ?)");
            ps.setString(1, name);

            PGobject pgo = new PGobject();
            pgo.setType("jsonb");
            pgo.setValue(content);

            ps.setObject(2, pgo);

PostgreSQL 9.4

user3825510 · Accepted Answer · 2017-02-17 13:34:39Z

1

Yes you must create PGobject and insert, but with some changes:

First:

PGobject pgo = new PGobject();
pgo.setType("jsonb");
pgo.setValue(content);

Second:

jdbcTemplate.update("SELECT yourFunction(?, ?)",
        new Object[] {text, pgo},
        new int[] {Types.VARCHAR, Types.OTHER });

edited Feb 17, 2017 at 13:34

answered Feb 17, 2017 at 12:20

user3825510

711 silver badge3 bronze badges

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

Collectives™ on Stack Overflow

How to map jsonb input parameter in Postgresql stored procedure in java

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related