如何在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注入的攻击，这可能也是导致错误的原因。使用参数占位符设置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。