CODEWITHSUNDEEP
postgresqlstored-proceduresjdbcspring-jdbchikaricp
rog
rog

Reputation: 13

PostgresQL stored procedure called from JDBC does not work

I have a postgresql database with some table and a stored procedure which write on this table. Everything goes fine, when I call this stored procedure from psql: I see my records inserted in my table. Although, when I call this stored procedure through JDBC, I get the log of stored procedure execution, the sequence is incremented, however my table is not updated.

Here are the SQL I executed from a fresh postgresql installation (9.5.1):

Table creation:

CREATE SEQUENCE atable_id_seq;
   CREATE TABLE IF NOT EXISTS "atable" (
   "id" int NOT NULL DEFAULT NEXTVAL('atable_id_seq'),
   "name" varchar(250) DEFAULT NULL,
   PRIMARY KEY ("id")
);

The creation of stored procedure:

CREATE OR REPLACE FUNCTION awrite(IN name character varying, OUT result character varying) AS
$$
BEGIN
            INSERT INTO atable(name) VALUES (name)
                    RETURNING atable.id INTO awrite.result;
                    RAISE INFO 'Create awrite: %', name;
END
$$
LANGUAGE plpgsql;

Calling awrite stored procedure from psql actually triggers the log in postgres and updates the table.

Then I wrote a java program to call the same stored procedure:

HikariConfig hikariConfig = new HikariConfig();
hikariConfig.setDriverClassName("org.postgresql.Driver");
hikariConfig.setJdbcUrl("jdbc:postgresql://127.0.0.1:5432/toto?charSet=UNICODE");
hikariConfig.setUsername("toto");

hikariConfig.setAutoCommit(false);
hikariConfig.setMinimumIdle(0);
hikariConfig.setMaximumPoolSize(2);

JdbcTemplate jdbcTemplate =
        new JdbcTemplate(new HikariDataSource(hikariConfig));

SimpleJdbcCall call = new SimpleJdbcCall(jdbcTemplate).withProcedureName("awrite");

HashMap<String, Object> params = new HashMap<String, Object>();
params.put("name", "Foo");

call.addDeclaredParameter(new SqlOutParameter("result", Types.VARCHAR));

Map<String, Object> result = call.execute(params);
jdbcTemplate.execute("COMMIT;");

System.out.println("Result: ");
for (String s : result.keySet()) {
    System.out.println(s + ": " + result.get(s));
}

I get an ID and execution went well. However my table does not contain the record.

I don't get it, maybe someone could help me?

Upvotes: 1

Views: 1347

Answers (1)

Thomas Risberg
Thomas Risberg

Reputation: 976

I believe your problem is that you are setting the JDBC AutoCommit flag to false but you aren't using a JDBC transaction. You need to run you're call inside a JDBC transaction (easiest is to put your code in a method and annotate the method with @Transactional or start and commit a JDBC transaction programmatically using a TransactionTemplate). Calling "COMMIT" like you are doing doesn't work.

Upvotes: 1

Related Questions