java - 我们如何修复“java.sql.SQLSyntaxErrorException :

Question

这个问题已经有答案了:

SQL query: Can't order by column called "order"? (7 个回答)

已关闭 4 年前。

我想通过 api 显示订单列表，但 DAO SQLSyntaxErrorException 中出现错误。

@RequestMapping("list")
    public String getAllOrders() {
        //APIResponse response=new APIResponse();
        List<OrderBeans> orderList = orderDao.selectAll();

        return new Gson().toJson(orderList);
    }

public List<OrderBeans> selectAll() {
        System.out.println("DAO => " + jdbcTemplate);
        List<OrderBeans> orders = null;
        String query = "select * from " + TABLE_ORDER +"";
        try {
            orders = jdbcTemplate.query(query, new OrderRowMapper());
        } catch (EmptyResultDataAccessException | IncorrectResultSetColumnCountException e) {

        }
        return orders;
    }

3-May-2019 15:01:15.997 SEVERE [http-nio-8084-exec-109] org.apache.catalina.core.StandardWrapperValve.invoke Servlet.service() for servlet [dispatcher] in context with path [/Grocery] threw exception [Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [select * from order]; nested exception is java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order' at line 1] with root cause
 java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order' at line 1

Answer 1

我在这里推测您的错误是由于您的 SQL 表被称为 ORDER 造成的，这当然是几乎所有 SQL 版本中的保留关键字。您应该始终避免使用保留关键字命名表和列。作为解决方法，您可以按如下方式构建查询，在表名称周围放置反引号:

String query = "select * from `" + TABLE_ORDER + "`";

但是，在您有机会修复数据模型之前，上述内容只能被视为临时解决方案。