Java、PostgreSQL 和 Hibernate : Select statement with nested strings

Question

我正在尝试使用 Java + Hibernate 将数据从 RedShift 卸载到 S3，但无法弄清楚如何让它接受 SQL 中的字符串中的字符串。

无论我尝试使用什么转义序列来将引号字符正确传递到 RedShift，我都会遇到一个又一个的异常。

欢迎任何想法。谢谢。

这是我尝试在 RedShift 上运行的查询(如果我直接将其输入到 RedShift 中，它就会起作用):

unload ('select raw_line from stl_load_errors where starttime > \'2013-01-01\' ')
  to 's3://myBucket/myTable/'
  credentials 'aws_access_key_id=*;aws_secret_access_key=*' gzip;

以下是我在 Java 中尝试过的各种命令以及遇到的错误:

单个反斜杠

session.createSQLQuery("unload ('select raw_line from stl_load_errors where starttime > \'2013-01-01\' ')"
  + " to 's3://myBucket/myTable/'"
  + " credentials 'aws_access_key_id=" + key + ";aws_secret_access_key=" + secret + "'  gzip")
  .executeUpdate();

结果
警告 org.hibernate.engine.jdbc.spi.SqlExceptionHelper - SQL 错误:0，SQLState:42601
错误 org.hibernate.engine.jdbc.spi.SqlExceptionHelper - 错误:“2013”​​位置或附近的语法错误:66
错误org.hibernate.exception.SQLGrammarException:无法执行语句

双反斜杠

session.createSQLQuery("unload ('select raw_line from stl_load_errors where starttime > \\'2013-01-01\\' ')"
  + " to 's3://myBucket/myTable/'"
  + " credentials 'aws_access_key_id=" + key + ";aws_secret_access_key=" + secret + "'  gzip")
  .executeUpdate();

结果
错误org.hibernate.QueryException:参数前缀':'后不允许有空格[unload('select raw_line from STL_load_errors where starttime >\'2013-01-01\'')到's3://myBucket/myTable/'凭证 'aws_access_key_id=*;aws_secret_access_key=*' gzip]
在 org.hibernate.engine.query.spi.ParameterParser.parse(ParameterParser.java:95)

三个反斜杠

session.createSQLQuery("unload ('select raw_line from stl_load_errors where starttime > \\\'2013-01-01\\\' ')"
  + " to 's3://myBucket/myTable/'"
  + " credentials 'aws_access_key_id=" + key + ";aws_secret_access_key=" + secret + "'  gzip")
  .executeUpdate();

结果
2013-06-19 01:39:59,233 错误 org.hibernate.QueryException: 参数前缀 ':' [unload ('select raw_line from STL_load_errors where starttime >\'2013-01-01\' ') 后不允许有空格's3://myBucket/myTable/' 凭证 'aws_access_key_id=*;aws_secret_access_key=*' gzip]
在 org.hibernate.engine.query.spi.ParameterParser.parse(ParameterParser.java:95)

四重反斜杠

session.createSQLQuery("unload ('select raw_line from stl_load_errors where starttime > \\\\'2013-01-01\\\\' ')"
  + " to 's3://myBucket/myTable/'"
  + " credentials 'aws_access_key_id=" + key + ";aws_secret_access_key=" + secret + "'  gzip")
  .executeUpdate();

结果
警告 org.hibernate.engine.jdbc.spi.SqlExceptionHelper - SQL 错误:0，SQLState:42601
错误 org.hibernate.engine.jdbc.spi.SqlExceptionHelper - 错误:“2013”​​位置或附近的语法错误:68
错误org.hibernate.exception.SQLGrammarException:无法执行语句

命名参数

session.createSQLQuery("unload (:query)"
  + " to 's3://myBucket/myTable/'"
  + " credentials 'aws_access_key_id=" + key + ";aws_secret_access_key=" + secret + "'  gzip")
  .setString("query", "select raw_line from stl_load_errors where starttime > '2013-01-01'")
  .executeUpdate();

结果:
警告 org.hibernate.engine.jdbc.spi.SqlExceptionHelper - SQL 错误:0，SQLState:42601
错误 org.hibernate.engine.jdbc.spi.SqlExceptionHelper - 错误:“$1”位置或附近的语法错误:9
错误org.hibernate.exception.SQLGrammarException:无法执行语句

Answer 1

您应该看一下美元引用的字符串:

http://www.postgresql.org/docs/current/interactive/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING