Java: How to insert JSON object to CLOB into oracle database

Question

I have a org.json.simple.JSONObject and I want to insert it into a clob type column in Oracle Database using PreparedStatement. How can I do that?

When I do preparedStatement.setObject(1, json_obj); I get the error : Can''t infer the SQL type in setObject for org.json.simple.JSONObject

Why not convert the JSONObject or a JSON String and insert the text? — MadProgrammer
– MadProgrammer, Commented Jun 20, 2018 at 0:42
The column type in database cannot be changed from clob to anyother type. When I try preparedStatement.setString(1, json_obj.toString()); I get SQL error: 1036 ORA-01036: illegal variable name/number — Ankit Jain
– Ankit Jain, Commented Jun 20, 2018 at 0:49
Well, you should be using toJSONString instead of toString so you get the actual JSON structure — MadProgrammer
– MadProgrammer, Commented Jun 20, 2018 at 0:52
toJSONString gives same SQL error: 1036 ORA-01036: illegal variable name/number. As suggested in above link tried oracle.sql.CLOB.createTemporary(conn, false, oracle.sql.CLOB.DURATION_SESSION); which gives org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper cannot be cast to oracle.jdbc.OracleConnection. I believe there should be an easier way. — Ankit Jain
– Ankit Jain, Commented Jun 20, 2018 at 1:15

Hernan Felipe Nieto · Accepted Answer · 2019-12-05 13:26:45Z

0

I put the json as a String and created this method, it worked for me:

public long  insertClob(String  id, String json ) {
    KeyHolder holder = new GeneratedKeyHolder();
    jdbcTemplate.update(new PreparedStatementCreator() {

    public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
        PreparedStatement ps = connection.prepareStatement("UPDATE tableWithClobColumn SET clobColumn=? WHERE id=?",
                Statement.RETURN_GENERATED_KEYS);
        Reader reader = new StringReader(json);
        ps.setClob(1, reader);
        ps.setString(2, "1911030604");

        return ps;
    }
}, holder);
return Long.parseLong("0");
}

answered Dec 5, 2019 at 13:26

Hernan Felipe Nieto

11 bronze badge

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

Collectives™ on Stack Overflow

Java: How to insert JSON object to CLOB into oracle database

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related