java - 如何解决 hsqldb 中的 SQLSyntaxErrorException？

Question

我尝试使用嵌入在程序中的HSQLDB。

但是，当我创建表并向数据库中插入数据时，出现错误。

以下是错误消息的一部分。

java.sql.SQLSyntaxErrorException: unexpected token: MAR required: )
    at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
    at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
    at org.hsqldb.jdbc.JDBCStatement.executeUpdate(Unknown Source)

当我在数据库中插入一些数据时会发生这种情况。

这是我的 CREATE 语句。

stmt.execute("CREATE TABLE IF NOT EXISTS readability ( id INTEGER NOT NULL IDENTITY,"
  + "LOC INTEGER DEFAULT NULL, " + "numOfComments INTEGER DEFAULT NULL,"
  + "numOfBlankLines INTEGER DEFAULT NULL," + "numOfBitOperators INTEGER DEFAULT NULL,"
  + "readability double DEFAULT NULL," + "username varchar(255) DEFAULT NULL,"
  + "storedTime datetime DEFAULT NULL," + "methodname varchar(255) DEFAULT NULL,"
  + "classname varchar(255) DEFAULT NULL," + "patternrate double DEFAULT NULL,"
  + "maxNestedControl INTEGER DEFAULT NULL," + "programVolume double DEFAULT NULL,"
  + "entropy double DEFAULT NULL,"
  + "CONSTRAINT username FOREIGN KEY (username) REFERENCES user (username) ON DELETE NO ACTION ON UPDATE NO ACTION"
  + ");");

这是我的 INSERT 语句。

stmt.executeUpdate(
  "INSERT INTO readability (LOC, numOfComments, numOfBlankLines, numOfBitOperators,"
  + " readability, username, storedTime, methodname, classname, patternRate, maxNestedControl, programVolume, entropy) VALUES("
  + readability.getLOC() + ", " + readability.getNumOfComments() + ", "
  + readability.getNumOfBlankLines() + ", " + readability.getNumOfBitOperators() + ", "
  + readability.getReadability() + ", '" + readability.getUser().getUsername() + "', "
  + readability.getStoredTime() + ", '" + readability.getMethodName() + "', '"
  + readability.getClassName() + "', " + readability.getPatternRate() + ", "
  + readability.getMaxNestedControl() + ", " + readability.getProgramVolume() + ", "
  + readability.getEntropy() + ")",
  Statement.RETURN_GENERATED_KEYS);

对象可读性具有使用的所有属性。

Answer 1

不要将值连接到查询字符串中。您的代码容易受到 SQL 注入(inject)的攻击，这也可能是导致错误的原因。 Use a prepared statement使用参数占位符并使用适当的 setter 设置值。

您的代码将变成类似的内容(为了简洁起见，省略了很多列):

try (PreparedStatement insert = connection.prepareStatement(
        "insert into readability (LOC, username) values (?, ?)", 
        Statement.RETURN_GENERATED_KEYS)) {
    insert.setInt(readability.getLOC();
    insert.setString(readibility.getUser().getUsername());

    insert.executeUpdate();

    // handle generated keys...
}

您可能还想考虑使用像 Hibernate 这样的 ORM。