选择like时Mysql出错

Question

请帮助解决以下提到的错误:

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 'r.Username LIKE '%usernameanswer%' and r.Createtime > NOW() - INTERVAL 30 SECOND

代码

public List <CorrekctanswerModel> answerlist(String usernameanswer) throws Exception {
       log.info("daomimpl" + usernameanswer);
    List<CorrekctanswerModel> answerarray = new ArrayList<CorrekctanswerModel>();

    Connection c = null;
    PreparedStatement ps = null;
    ResultSet rs = null;

    String sql = " select s.NAME as Subject, d.value as variant, r.Username, r.UserAnswer, r.Correctanswer, r.Createtime from resultlog r  " +

                 " inner join test_table t on t.ID = r.QuestionId " +

                 " inner join subject s on s.ID= t.SUBJECT " +

                 " inner join dictionary d on d.ID = t.Variant" +

                 "where r.Username LIKE '%usernameanswer%' and  r.Createtime > NOW() - INTERVAL 30 SECOND " ;

    try {

        c = DbHelper.getConnection();

        if (c != null) {

            ps = c.prepareStatement(sql);

            rs = ps.executeQuery();



            while (rs.next()) {

                CorrekctanswerModel rep = new CorrekctanswerModel();
                rep.setSubject(rs.getString("Subject"));
                rep.setVariant(rs.getString("variant"));
                rep.setUsername(rs.getString("Username"));
                rep.setUseranswer(rs.getString("UserAnswer"));
                rep.setCorrekctanswer(rs.getString("Correctanswer"));
                rep.setCreatedate(rs.getDate("Createtime"));


                  answerarray.add(rep);


            }
        }

    } catch (Exception ex) {
        ex.printStackTrace();


    } finally {
        JdbcUtility.close(c, ps, rs);
    }

    return answerarray;
}

Answer 1

如果有人偶然发现这个问题，我会添加这个作为答案......

在您的代码中，使用这些行生成 SQL:

String sql = " select <fields> from resultlog r  " +
             " inner join test_table t on t.ID = r.QuestionId " +
             " inner join subject s on s.ID= t.SUBJECT " +
             " inner join dictionary d on d.ID = t.Variant" +
             "where r.Username LIKE '%usernameanswer%' and ... " ;

where 之前应该有一个空格，或者 t.Variant 之后应该有一个空格。在当前形式中，查询将执行以下操作:

... inner join dictionary d on d.ID = t.Variantwhere r.Username ...

在 where 之前添加一个空格，如下所示:

String sql = " select <fields> from resultlog r  " +
             " inner join test_table t on t.ID = r.QuestionId " +
             " inner join subject s on s.ID= t.SUBJECT " +
             " inner join dictionary d on d.ID = t.Variant" +
             " where r.Username LIKE '%usernameanswer%' and ... " ;

将导致您的查询如下所示:

... inner join dictionary d on d.ID = t.Variant where r.Username ...

另外，看起来 usernameanswer 是一个变量。如果是，where 子句将如下所示:

             " where r.Username LIKE '%" + usernameanswer + "%' and ... " ;