Java type in JDBC to Postgres ltree
java, jdbc, ltree, postgresql, prepared-statement
Solution
Why not create stored procedure and call it from CallableStatement with String param to insert row with ltree via it, if preparedStatemnt.setString() isn't working?
Other solution may be `ps.setObject(2, foos.get(i).getPath(), Types.OTHER);`, but I can't check it now.
Problem
Does anyone know what Java type maps to a Postgres ltree type? I create a table like so: ``` CREATE TABLE foo (text name, path ltree); ``` A couple of inserts: ``` INSERT INTO foo (name, path) VALUES ( 'Alice', 'ROOT.first.parent'); INSERT INTO foo (name, path) VALUES ( 'Bob', 'ROOT.second.parent'); INSERT INTO foo (name, path) VALUES ( 'Ted', 'ROOT.first.parent.child'); INSERT INTO foo (name, path) VALUES ( 'Carol', 'ROOT.second.parent.child'); ``` Nothing strange there. Now I want to batch this up using a PreparedStatment: ``` public final String INSERT_SQL = "INSERT INTO foo( name, path) VALUES (?, ?)"; public void insertFoos(final List<Foo> foos) { namedParameterJdbcTemplate.getJdbcOperations().batchUpdate(INSERT_SQL, new BatchPreparedStatementSetter() { @Override public void setValues(PreparedStatement ps, int i) throws SQLException { ps.setString(1, foos.get(i).getName()); ps.setString(2, foos.get(i).getPath()); } @Override public int getBatchSize() { return foos.size(); } }); ``` } This generates the following error: ``` org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [INSERT INTO foo( name, path) VALUES (?, ?)]; nested exception is org.postgresql.util.PSQLException: ERROR: column "path" is of type ltree but expression is of type character varying Hint: You will need to rewrite or cast the expression. ``` Clearly I'm missing something. Why is it that I can insert 'something' using pure SQL but not JDBC?