SQL异常使用ORMLite准备查询 [英] SQL exception preparing query with ORMLite

问题描述

我正在使用ORM（ORMlite），我的所有调用都很顺利，直到我收到以下错误。

I am using an ORM (ORMlite) and all my calls are going well until I get the following error.

线程main中的异常org.h2.jdbc.JdbcSQLException：SQL语句中的语法错误
SELECT * FROM STORIESWHERETITLE='深切案例引导'不遵守[*]; SQL语句：
SELECT * FROM 故事 WHERE 标题 ='深切案例引导'不遵循''[ 42000-152]
在org.h2.message.DbException.getJdbcSQLException（DbException.java:327）
在org.h2.message.DbException.get（DbException.java:167）
在org.h2.message.DbException.get（DbException.java:144）
在org.h2.message.DbException.getSyntaxError（DbException.java:179）
在org.h2.command.Parser .getSyntaxError（Parser.java:480）
在org.h2.command.Parser.prepareCommand（Parser.java:229）
在org.h2.engine.Session.prepareLocal（Session.java:426 ）
在org.h2.engine.Session.prepareCommand（Session.java:374）
在org.h2.jdbc.JdbcConnection.prepareCommand（JdbcConnection.java:1093）
在org。 hb.JdbcPreparedStatement
在com.j256.ormlite.jdbc.JdbcDatabaseConnection.compileStatement（JdbcDatabaseConnection.java:83）
在com.j256.ormlite.stmt.mapped.MappedPreparedStmt.compile（MappedPreparedStmt.java:44）
at com.j256.ormlite.stmt.StatementExecutor.buildIterator（StatementExecutor.java:169）
at com.j256.ormlite.stmt.StatementExecutor.query（StatementExecutor.java:119）
at com .j256.ormlite.dao.BaseDaoImpl.query（BaseDaoImpl.java:189）

Exception in thread "main" org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement " SELECT * FROM ""STORIES"" WHERE ""TITLE"" = 'Deepcut case leads 'NOT FOLLOWED[*]'' "; SQL statement: SELECT * FROM Stories WHERE title = 'Deepcut case leads 'not followed'' [42000-152] at org.h2.message.DbException.getJdbcSQLException(DbException.java:327) at org.h2.message.DbException.get(DbException.java:167) at org.h2.message.DbException.get(DbException.java:144) at org.h2.message.DbException.getSyntaxError(DbException.java:179) at org.h2.command.Parser.getSyntaxError(Parser.java:480) at org.h2.command.Parser.prepareCommand(Parser.java:229) at org.h2.engine.Session.prepareLocal(Session.java:426) at org.h2.engine.Session.prepareCommand(Session.java:374) at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1093) at org.h2.jdbc.JdbcPreparedStatement.(JdbcPreparedStatement.java:71) at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:601) at com.j256.ormlite.jdbc.JdbcDatabaseConnection.compileStatement(JdbcDatabaseConnection.java:83) at com.j256.ormlite.stmt.mapped.MappedPreparedStmt.compile(MappedPreparedStmt.java:44) at com.j256.ormlite.stmt.StatementExecutor.buildIterator(StatementExecutor.java:169) at com.j256.ormlite.stmt.StatementExecutor.query(StatementExecutor.java:119) at com.j256.ormlite.dao.BaseDaoImpl.query(BaseDaoImpl.java:189)

我感到困惑的是出了什么问题。我从这些行调用搜索：

I'm confused as to whats going wrong. I am calling the search from these lines:

// get our query builder from the DAO
QueryBuilder<Story, Integer> queryBuilder = StoryDao.queryBuilder();
// the 'title' field must be equal to title (a variable)
queryBuilder.where().eq(Story.TITLE_FIELD_NAME, title);
// prepare our sql statement
PreparedQuery<Story> preparedQuery = queryBuilder.prepare();
// query for all stories that have that title
List<Story> accountList = StoryDao.query(preparedQuery);

推荐答案

SQL中的语法错误声明SELECT * FROMSTORIESWHERETITLE...

Syntax error in SQL statement " SELECT * FROM ""STORIES"" WHERE ""TITLE""...

@bemace是正确的，似乎在标题中引用标题，这个标题正在拧紧查询生成的字符串的转义。

@bemace is correct that there seem to be quotes in the title that is screwing up the escaping of strings generated by the query.

在ORMLite中，您应该使用 SelectArg 功能，它将生成带有SQL？参数的查询，然后直接将该字符串传递给准备的语句。​​

In ORMLite, you should use the SelectArg feature which will generate a query with SQL ? arguments and then pass the string to the prepared statement directly.

有关请参阅：

For documentation on the SelectArg, see:

http://ormlite.com/docs/select-arg

With SelectArg ，您可以执行以下操作：

With SelectArg, you'd do something like:

QueryBuilder<Story, Integer> queryBuilder = StoryDao.queryBuilder();
SelectArg titleArg = new SelectArg();
queryBuilder.where().eq(Story.TITLE_FIELD_NAME, titleArg);
PreparedQuery<Story> preparedQuery = queryBuilder.prepare();
titleArg.setValue(title);
List<Story> accountList = StoryDao.query(preparedQuery);

这篇关于SQL异常使用ORMLite准备查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！