JDBC中Java类型到Postgres ltree

问题描述

有人知道Java类型映射到Postgres ltree类型吗?

Does anyone know what Java type maps to a Postgres ltree type?

我这样创建一个表:

CREATE TABLE foo (text name, path ltree);

几个插入物:

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');

没有什么奇怪的.现在，我想使用PreparedStatment对此进行批处理:

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();
  }
});

}

这会产生以下错误:

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.

很明显，我缺少了一些东西.为什么我可以使用纯SQL而不是JDBC插入某物"?

Clearly I'm missing something. Why is it that I can insert 'something' using pure SQL but not JDBC?

推荐答案

如果preparedStatemnt.setString()不起作用，为什么不创建存储过程并从带有String参数的CallableStatement中调用它来通过ltree插入行?

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?

其他解决方案可能是ps.setObject(2, foos.get(i).getPath(), Types.OTHER);，但我现在无法检查.

Other solution may be ps.setObject(2, foos.get(i).getPath(), Types.OTHER);, but I can't check it now.

这篇关于JDBC中Java类型到Postgres ltree的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！