oracle insert xml in xmltype column using jdbctemplate batchupdate results in ORA-01461: can bind a LONG value only for insert into a LONG column

Question

I have a spring boot app that has a list of entity that I'm passing to the below batch insert but results in ORA-01461: can bind a LONG value only for insert into a LONG column. Not very sure why this is happening:

(...)
batchInsert(books, 100);
(...)

public int[][] batchInsert(List<Book> books, int batchSize) {
    return jdbcTemplate.batchUpdate(
            "insert into books (bookId, name, price, xml) values(?,?,?,?)",
            books,
            batchSize,
            new ParameterizedPreparedStatementSetter<Book>() {
                public void setValues(PreparedStatement ps, Book argument) 
                    throws SQLException {
                    ps.setString(1, argument.getBookId());
                    ps.setString(2, argument.getName());
                    ps.setBigDecimal(3, argument.getPrice());
                    ps.setString(4, argument.getXml());
                }
            });
}

oracle datatype for the xml column in the table is: XML SYS.XMLTYPE

and the model:

@Type(type="com.sample.util.HibernateXMLType")
@Column(name = "XML", columnDefinition="XDB.XMLTYPE")
private String xml;

The error is only happening for some specific XMLs, not all of them. Probably because of the size of those specific XMLs, not very sure.

I have tried below approaches but still getting the same error:

1.
SQLXML xmlObject = jdbcTemplate.getDataSource().getConnection().createSQLXML();
xmlObject.setString(argument.getXml());
ps.setSQLXML(4, xmlObject);

2.
ps.setClob(4, new StringReader(argument.getXml()));

Now, I've also used JPA like below, which is working with the same data type, only thing is that it is very slow compared to jdbctemplate, perhaps by the fact that it has to do a select before the insert to check whether the entity is present or no:

bookRepo.saveAll(books);

Answer 1

You should use ps.setSQLXML for xmltype: https://docs.oracle.com/javase/tutorial/jdbc/basics/sqlxml.html

But don't forget to initialize it properly:

 SQLXML sqlxml = ps.getConnection().createSQLXML();
 try {
  sqlxml.setString(argument.getXml());
  ps.setSQLXML(4, sqlxml);
 } finally {
  sqlxml.free();
 }
}