java - PreparedStatement 没有正确执行

Question

query = "SELECT * FROM POST_COMMENT WHERE Post_date_time= ? AND Post_User= !;";
     query = query.replace("?", "'"+post.getDatetime()+"'");
     query = query.replace("!", Integer.toString(post.getPublisher().getID()));

    PreparedStatement pstm = con.prepareStatement(query);
    ResultSet rs = pstm.executeQuery();

结果查询如下所示

SELECT * FROM POST_COMMENT WHERE Post_date_time= '2013-04-12 07:20:34.0' AND Post_User= 378;

它在 MySQL 提示行中工作正常，但是，当从准备好的语句启动时抛出此错误:

Exception in thread "main" 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 '07:20:34.0 AND Post_User=378' at line 1

但是

pstm.setString(1, post.getDatetime());
pstm.setString(2, Integer.toString(post.getPublisher().getID()));

也不行。

表定义

CREATE TABLE Post_Comment (
  Comment_ID INTEGER(7) NOT NULL,
  Post_date_time DATETIME NOT NULL,
  Post_User INTEGER(7) NOT NULL,
  PRIMARY KEY(Comment_ID, Post_date_time, Post_User),
  CONSTRAINT Post_Comment_Post
    FOREIGN KEY (Post_User, Post_date_time)
    REFERENCES Post (User, date_time)
    ON DELETE   CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT Post_Comment_Comment
    FOREIGN KEY (Comment_ID)
    REFERENCES Commentary (ID)
    ON DELETE   CASCADE
    ON UPDATE CASCADE
)
;

请帮忙。

Answer 1

看看the Java tutorials .您不应该使用字符串函数 replace() 字段。

您应该调用 "set" methods of the PreparedStatement .

---

编辑:

不，看看可用的方法。以及数据库列期望的类型。如果你想要一个日期，使用 setDate()，如果你想要一个整数，那么使用 setInt()。示例:

query = "SELECT * FROM POST_COMMENT WHERE Post_date_time = ? AND Post_User = ?;";

PreparedStatement pstm = con.prepareStatement(query);
pstm.setDate(1, post.getDatetime());
pstm.setInt(2, post.getPublisher().getID());
ResultSet rs = pstm.executeQuery();

另请注意，查询使用 ? 作为两个 占位符，而不是 !。