java - 当查询在 phpmyadmin 中运行时，JDBC 抛出语法错误

Question

我正在使用 jdbc for mysql 开发 java 网站。这是我从 sql 中获取数据的简单函数，

@Override
    public List fetchMusic(String _uname, String _chnl_name) throws SQLException {

        String sql = "SELECT * FROM `channel_songs` WHERE `uid` = " +
                     " (SELECT `id` FROM `user` WHERE BINARY `uname` = ?)"+
                     " AND `chnl_id` = (SELECT `id` FROM `channel` WHERE BINARY `chnl_name` = ?);";

        Connection conn = dataSource.getConnection();

        PreparedStatement ps = conn.prepareStatement(sql);

        ps.setString(1, _uname);
        ps.setString(2, _chnl_name);

        List<Map<String, Object>> musicListsByChnl = jdbcTemplate.queryForList(String.valueOf(ps));


        return musicListsByChnl;

    }

因此，当我运行此程序时，我收到一条错误消息:

org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [com.mysql.jdbc.JDBC4PreparedStatement@3a4eb50a: SELECT * FROM `channel_songs`]; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'com.mysql.jdbc.JDBC4PreparedStatement@3a4eb50a: SELECT * FROM `channel_songs`' at line 1

所以，我不知道出了什么问题，也不知道问题出在哪里。当我在 phpmyadmin 上尝试此查询时，它工作正常并返回数据。那么有人可以帮助我吗？

提前致谢。 ;)

Answer 1

您错误地使用了准备好的语句...PreparedStatement 不仅仅是构建完全转义 SQL 字符串的“方便”设备。 String.valueOf(ps) 的结果实际上根本没有被定义为有效的 SQL 命令。您将得到 ps.toString()，其值仅用于调试。从您给出的异常(exception)情况来看:

check the manual that corresponds to your MySQL server version for the right syntax to use near 'com.mysql.jdbc.JDBC4PreparedStatement@3a4eb50a: SELECT * FROM channel_songs' at line 1

你应该做这样的事情:

@Override
public List fetchMusic(String _uname, String _chnl_name) throws SQLException {

    String sql = "SELECT * FROM `channel_songs` WHERE `uid` = " +
                 " (SELECT `id` FROM `user` WHERE BINARY `uname` = ?)"+
                 " AND `chnl_id` = (SELECT `id` FROM `channel` WHERE BINARY `chnl_name` = ?);";

    Connection conn = dataSource.getConnection();

    PreparedStatement ps = conn.prepareStatement(sql);

    ps.setString(1, _uname);
    ps.setString(2, _chnl_name);

    // This is, where your original code falls apart....
    // List<Map<String, Object>> musicListsByChnl = jdbcTemplate.queryForList(String.valueOf(ps));


    List<Map<String,Object>> answer = new ArrayList<>();

    try (ResultSet rs = ps.executeQuery()) {

        while (rs.next()) {

            // read a single row of the result set using the
            // getters of the `rs` instance (rs.getString(...),
            // rs.getInt(...), ...)
        }
    }



    return musicListsByChnl;

}

如果您不想编写样板内容(并且您手头似乎已经有一个jdbcTemplate):spring的JdbcTemplate有更多方便的方法，它处理分配查询参数等。只需找到最接近您需求的即可。