java - 使用 ORMLite 准备查询的 SQL 异常

Question

我正在使用 ORM (ORMlite)，并且我的所有调用都进展顺利，直到出现以下错误。

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)

我很困惑到底出了什么问题。我从这些行中调用搜索:

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

Answer 1

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

@bemace 是正确的，标题中似乎有引号搞砸了查询生成的字符串的转义。

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

请参阅documentation on the SelectArg 。有了它，您可以执行以下操作:

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