HSQLDB忽略案例

Question

在java中使用以下代码：

Connection c = jdbcDriver.getConnection("jdbc:hsqldb:mem:test;sql.ignore_case=true", null);
Statement stmt = c.createStatement();
stmt.executeUpdate("CREATE TABLE \"TEST\"(ID INT)");
stmt.executeQuery("SELECT id FROM \"test\";");

抛出一个org.hsqldb.HsqlException

Exception in thread "main" java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: test
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
at org.hsqldb.jdbc.JDBCStatement.executeQuery(Unknown Source)
at Test.main(Test.java:15)
Caused by: org.hsqldb.HsqlException: user lacks privilege or object not found: test
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.ParserDQL.readTableName(Unknown Source)
at org.hsqldb.ParserDQL.readTableOrSubquery(Unknown Source)
at org.hsqldb.ParserDQL.XreadTableReference(Unknown Source)
at org.hsqldb.ParserDQL.XreadFromClause(Unknown Source)
at org.hsqldb.ParserDQL.XreadTableExpression(Unknown Source)
at org.hsqldb.ParserDQL.XreadQuerySpecification(Unknown Source)
at org.hsqldb.ParserDQL.XreadSimpleTable(Unknown Source)
at org.hsqldb.ParserDQL.XreadQueryPrimary(Unknown Source)
at org.hsqldb.ParserDQL.XreadQueryTerm(Unknown Source)
at org.hsqldb.ParserDQL.XreadQueryExpressionBody(Unknown Source)
at org.hsqldb.ParserDQL.XreadQueryExpression(Unknown Source)
at org.hsqldb.ParserDQL.compileCursorSpecification(Unknown Source)
at org.hsqldb.ParserCommand.compilePart(Unknown Source)
at org.hsqldb.ParserCommand.compileStatements(Unknown Source)
at org.hsqldb.Session.executeDirectStatement(Unknown Source)
at org.hsqldb.Session.execute(Unknown Source)
... 3 more

但是为什么呢？我说ignore_case=true！

请参阅此链接：http://hsqldb.org/doc/guide/dbproperties-chapt.html#N15A0A

Answer 1

删除“(引号)”

默认情况下，如果没有引号，名称是不区分大小写的。但是你把表名放在引号之间，所以它们完全按原样注册，区分大小写。然后，这两个表名是不同的，HSQLDB将不匹配它们。

这将起作用：

import java.sql.*;

public class Main {
  public static void main(String[] args) throws Exception {
    Connection c = DriverManager.getConnection("jdbc:hsqldb:mem:test", "SA", "");
    Statement stmt = c.createStatement();
    stmt.executeUpdate("CREATE TABLE TEST(ID INT)");
    stmt.executeQuery("SELECT id FROM test;");
  }
}

那么，sql.ignore_case是做什么的？

它确保varchar比较不区分大小写.例如，abc将与ABC匹配

import java.sql.*;

public class Main {
  public static void main(String[] args) throws Exception {
    Connection c = DriverManager.getConnection("jdbc:hsqldb:mem:test;sql.ignore_case=true", "SA", "");
    Statement stmt = c.createStatement();
    stmt.executeUpdate("CREATE TABLE TeSt(NAME VARCHAR(99))");
    stmt.executeUpdate("INSERT INTO tEsT (NAME) VALUES ('ABC')");
    ResultSet rs = stmt.executeQuery("SELECT * FROM TesT WHERE NAME = 'abc'");
    rs.next();
    System.out.println(rs.getString(1)); // prints: ABC
  }
}