H2 JDBC错误当您有一张名为”Users”的表时。

huangapple
go评论59阅读模式
英文:

H2 JDBC error when you have a table called "Users"

问题

I have translated the provided text for you:

我正在升级我的库以支持H2基线2.0，并且我有一些测试失败，因为我有一个名为Users的表，但H2现在似乎将其视为位于INFORMATION_SCHEMA而不是PUBLIC中的USERS表。

我尝试将我的URL更改为jdbc:h2:{$home}/pinf;SCHEMA=PUBLIC，但我仍然看到2个USERS的副本。我不想在INFORMATION_SCHEMA中看到这些表。

我如何让用户只看到PUBLIC模式？

在H2 1.x版本下，这个功能运行正常。

为了澄清，这是在使用con.getMetaData().getTables时发生的。

首先，我以sa用户登录，然后：

CREATE USER pinf PASSWORD 'pinf' ADMIN;
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA PUBLIC TO pinf;
REVOKE ALL ON SCHEMA INFORMATION_SCHEMA FROM pinf;

然后我以'pinf'用户登录，

通过使用getTables和null参数来迭代元数据的方式，包括来自INFORMATION_SCHEMA的表。在1.x版本中不是这样的。

英文:

I'm upgrading my library to support H2 baseline 2.0 and I have tests that fail because I have a table called Users, but H2 now seems to see that as it's USERS table in INFORMATION_SCHEMA rather than in PUBLIC.

I tried changing my URL to jdbc:h2:{$home}/pinf;SCHEMA=PUBLIC but I still see 2 copies of USERS. I don't want to see the tables in INFORMATION_SCHEMA.

How do I have a user that would only see PUBLIC schema?

This worked fine under H2 1.x.

To clarify this is when using con.getMetaData().getTables

I 1st log in as sa then:

CREATE USER pinf PASSWORD 'pinf' ADMIN;
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA PUBLIC TO pinf;
REVOKE ALL ON SCHEMA INFORMATION_SCHEMA FROM pinf;

then I log in as 'pinf'

iterating across meta data with getTables with null parameters for catalog, schema pattern and table name pattern includes the tables from INFORMATION_SCHEMA. This was not the case in 1.x version.

答案1

得分: 2

tl;dr

无法复现。

H2中的默认模式是PUBLIC。如果您在那里创建一个名为USERS的表，该表会默认被找到。

请注意您当前的模式。标准的INFORMATION_SCHEMA模式包含一个名为USERS的表。

Not reproducible

如评论中所述：

  • USERS不是H2数据库引擎中的保留字。
  • 默认情况下，在H2中没有名为USERS的表。
  • H2中的默认模式是PUBLIC。如果您在那里创建一个表，并对该表进行查询，它将默认被找到。

因此，您在问题中提出的问题无法复现。

我怀疑您已将默认模式更改为SQL标准模式INFORMATION_SCHEMA。请参阅文档中的System Tables页面。那里存在一个名为USERS的表，正如文档所述。

这是一个示例应用程序，它尝试在以下模式中查询名为USERS的表：

  • 名为INFORMATION_SCHEMA的模式。
  • 明确命名为PUBLIC的模式。
  • 默认模式（隐式为PUBLIC）。

结果分别是：

  • 成功。在INFORMATION_SCHEMA中找到表。
  • 失败。在PUBLIC中没有这样的表。
  • 失败。在默认模式PUBLIC中没有这样的表。

接下来，我们在默认模式PUBLIC中创建一个名为USERS的表，然后运行相同的三次尝试。

结果分别是：

  • 成功。在INFORMATION_SCHEMA中找到表。
  • 成功。在PUBLIC中找到表。
  • 成功。在默认模式PUBLIC中找到表。

顺便说一下，H2中默认模式的名称被定义为一个常量：Constants.SCHEMA_MAIN。该常量在示例代码中显示为PUBLIC。

示例代码

package work.basil.example.db;

import org.h2.jdbcx.JdbcDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class UsersTableEx
{
    public static void main ( String[] args )
    {
        System.out.println ( "org.h2.engine.Constants.SCHEMA_MAIN = " + org.h2.engine.Constants.SCHEMA_MAIN + "\n---------------------" );
        UsersTableEx app = new UsersTableEx ( );
        DataSource dataSource = app.obtainDataSource ( );

        app.queryInformationSchemaUsers ( dataSource );
        app.queryPublicUsers ( dataSource );
        app.queryUsers ( dataSource );

        app.createUsersTable(dataSource );

        app.queryInformationSchemaUsers ( dataSource );
        app.queryPublicUsers ( dataSource );
        app.queryUsers ( dataSource );

        System.out.println ( "INFO - Demo done." );
    }
    
    private void queryInformationSchemaUsers ( DataSource dataSource )
    {
        System.out.println ( "INFO - Query for INFORMATION_SCHEMA.USERS." );
        String sql = 
                "SELECT *" +
                "FROM INFORMATION_SCHEMA.USERS";
        try (
                Connection conn = dataSource.getConnection ( ) ;
                Statement statement = conn.createStatement ( ) ;
        )
        {
            try (
                    ResultSet resultSet = statement.executeQuery ( sql ) ;
            )
            {
                while ( resultSet.next ( ) )
                {
                    System.out.println (
                            "USER_NAME = " + resultSet.getString ( "USER_NAME" ) +
                            " | IS_ADMIN = " + resultSet.getBoolean ( "IS_ADMIN" ) +
                            " | REMARKS = " + resultSet.getString ( "REMARKS" ) +
                            "\n------------------"
                    );
                }
            }
        }
        catch ( SQLException e ) { throw new RuntimeException ( e ); }
    }

    private void queryPublicUsers ( DataSource dataSource )
    {
        System.out.println ( "INFO - Query for PUBLIC.USERS." );
        String sql = 
                "SELECT *" +
                "FROM PUBLIC.USERS";
        try (
                Connection conn = dataSource.getConnection ( ) ;
                Statement statement = conn.createStatement ( ) ;
        )
        {
            try (
                    ResultSet resultSet = statement.executeQuery ( sql ) ;
            )
            { while ( resultSet.next ( ) ) System.out.println ( "Found row." ); }
        }
        catch ( org.h2.jdbc.JdbcSQLSyntaxErrorException e )
        {
            System.out.println ( "ERROR - " + e.getMessage ( ) + "\n--------------" );
        }
        catch ( SQLException e ) { throw new RuntimeException ( e ); }
    }

    private void queryUsers ( DataSource dataSource )
    {
        System.out.println ( "INFO - Query for USERS." );
        String sql = 
                "SELECT *" +
                "FROM USERS";
        try (
                Connection conn = dataSource.getConnection ( ) ;
                Statement statement = conn.createStatement ( ) ;
        )
        {
            try (
                    ResultSet resultSet = statement.executeQuery ( sql ) ;
            )
            { while ( resultSet.next ( ) ) System.out.println ( "Found row." ); }
        }
        catch ( org.h2.jdbc.JdbcSQLSyntaxErrorException e )
        {
            System.out.println ( "ERROR - " + e.getMessage ( ) + "\n--------------" );
        }
        catch ( SQLException e ) { throw new RuntimeException ( e ); }
    }

    private void createUsersTable ( DataSource dataSource )
    {
        System.out.println ( "INFO - Running `createUsersTable` method. \n-----------------------------" );
        String sql = 
                "CREATE TABLE IF NOT EXISTS USERS" +
                "(" +
                    "user_ TEXT NOT NULL ," +
                    "id_ UUID NOT NULL DEFAULT RANDOM_UUID()," +
                    "CONSTRAINT history_pkey_ PRIMARY KEY ( id_ )" +
                ")";
        try (
                Connection conn = dataSource.getConnection ( ) ;
                Statement stmt = conn.createStatement ( ) ;
        )
        {
            stmt.executeUpdate ( sql );
        } catch ( SQLException e ) { e.printStackTrace ( ); }
    }

    private DataSource obtainDataSource ( )
    {
        org.h2.jdbcx.JdbcDataSource ds = new JdbcDataSource ( );  
        ds.setURL ( "jdbc:h2:mem:users_mystery_db;DB_CLOSE_DELAY=-1" );
        ds.setUser ( "scott" );
        ds.setPassword ( "tiger" );
        ds.setDescription ( "An example database showing a query for table named `USERS`." );
        return ds;
    }
}

当运行时：

org.h2.engine.Constants.SCHEMA_MAIN = PUBLIC
---------------------
INFO - Query for INFORMATION_SCHEMA.USERS.
USER_NAME = SCOTT | IS_ADMIN = true | REMARKS = null
------------------
INFO - Query for PUBLIC.USERS.
ERROR - Table "USERS" not found (this database is empty); SQL statement:
SELECT *
FROM PUBLIC.USERS
;
[42104-214]
--------------
INFO - Query for USERS.
ERROR -
<details>
<summary>英文:</summary>
# tl;dr
Unreproducible.
The default schema in H2 is `PUBLIC`. If you create a table there named `USERS`, that table is found by default. 
Be aware of your current schema. The standard `INFORMATION_SCHEMA` schema contains a table named `USERS`.
# Not reproducible
As noted in the Comments:
- `USERS` is not a reserved word in H2 Database Engine.
- No table named `USERS` is available by default in H2. 
- The default schema in H2 is `PUBLIC`. If you create a table there, and query on that table, it will be found by default. 
So the problem raised in your Question is not reproducible. 
I suspect you have changed the default schema to the SQL standard schema of `INFORMATION_SCHEMA`. See the [*System Tables*][1] page in the documentation. There exits a table named `USERS`, [as documented][1]. 
Here is an example app that makes 3 attempts to query a table named `USERS` in:
- The schema named `INFORMATION_SCHEMA`. 
- The schema named `PUBLIC`, explicitly.
- The default schema (which is `PUBLIC`, implicitly). 
The results are, respectively:
- Success. Table found in `INFORMATION_SCHEMA`. 
- Failure. No such table in `PUBLIC`. 
- Failure. No such table in default schema, `PUBLIC`.
Next we create a table named exactly `USERS` in the default schema, `PUBLIC`. And we run our same 3 attempts above.
The results are, respectively:
- Success. Table found in `INFORMATION_SCHEMA`. 
- Success. Table found in `PUBLIC`. 
- Success. Table found in default schema, `PUBLIC`.
By the way, the name of the default schema in H2 is defined as a constant: [`Constants.SCHEMA_MAIN`][2]. That constant holds a value of `PUBLIC` as shown in this example code.
# Example code
```java
package work.basil.example.db;
import org.h2.jdbcx.JdbcDataSource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class UsersTableEx
{
public static void main ( String[] args )
{
System.out.println ( &quot;org.h2.engine.Constants.SCHEMA_MAIN = &quot; + org.h2.engine.Constants.SCHEMA_MAIN + &quot;\n---------------------&quot; );
UsersTableEx app = new UsersTableEx ( );
DataSource dataSource = app.obtainDataSource ( );
app.queryInformationSchemaUsers ( dataSource );
app.queryPublicUsers ( dataSource );
app.queryUsers ( dataSource );
app.createUsersTable(dataSource );
app.queryInformationSchemaUsers ( dataSource );
app.queryPublicUsers ( dataSource );
app.queryUsers ( dataSource );
System.out.println ( &quot;INFO - Demo done.&quot; );
}
private void queryInformationSchemaUsers ( DataSource dataSource )
{
System.out.println ( &quot;INFO - Query for INFORMATION_SCHEMA.USERS.&quot; );
String sql = &quot;&quot;&quot;
SELECT *
FROM INFORMATION_SCHEMA.USERS
;
&quot;&quot;&quot;;
try (
Connection conn = dataSource.getConnection ( ) ;
Statement statement = conn.createStatement ( ) ;
)
{
try (
ResultSet resultSet = statement.executeQuery ( sql ) ;
)
{
while ( resultSet.next ( ) )
{
System.out.println (
&quot;USER_NAME = &quot; + resultSet.getString ( &quot;USER_NAME&quot; ) +
&quot; | IS_ADMIN = &quot; + resultSet.getBoolean ( &quot;IS_ADMIN&quot; ) +
&quot; | REMARKS = &quot; + resultSet.getString ( &quot;REMARKS&quot; ) +
&quot;\n------------------&quot;
);
}
}
}
catch ( SQLException e ) { throw new RuntimeException ( e ); }
}
private void queryPublicUsers ( DataSource dataSource )
{
System.out.println ( &quot;INFO - Query for PUBLIC.USERS.&quot; );
String sql = &quot;&quot;&quot;
SELECT *
FROM PUBLIC.USERS
;
&quot;&quot;&quot;;
try (
Connection conn = dataSource.getConnection ( ) ;
Statement statement = conn.createStatement ( ) ;
)
{
try (
ResultSet resultSet = statement.executeQuery ( sql ) ;
)
{ while ( resultSet.next ( ) ) System.out.println ( &quot;Found row.&quot; ); }
}
catch ( org.h2.jdbc.JdbcSQLSyntaxErrorException e )
{
System.out.println ( &quot;ERROR - &quot; + e.getMessage ( ) + &quot;\n--------------&quot; );
}
catch ( SQLException e ) { throw new RuntimeException ( e ); }
}
private void queryUsers ( DataSource dataSource )
{
System.out.println ( &quot;INFO - Query for USERS.&quot; );
String sql = &quot;&quot;&quot;
SELECT *
FROM USERS
;
&quot;&quot;&quot;;
try (
Connection conn = dataSource.getConnection ( ) ;
Statement statement = conn.createStatement ( ) ;
)
{
try (
ResultSet resultSet = statement.executeQuery ( sql ) ;
)
{ while ( resultSet.next ( ) ) System.out.println ( &quot;Found row.&quot; ); }
}
catch ( org.h2.jdbc.JdbcSQLSyntaxErrorException e )
{
System.out.println ( &quot;ERROR - &quot; + e.getMessage ( ) + &quot;\n--------------&quot; );
}
catch ( SQLException e ) { throw new RuntimeException ( e ); }
}
private void createUsersTable ( DataSource dataSource )
{
System.out.println ( &quot;INFO - Running `createUsersTable` method. \n-----------------------------&quot; );
String sql = &quot;&quot;&quot;
CREATE TABLE IF NOT EXISTS USERS
(
user_ TEXT NOT NULL ,
id_ UUID NOT NULL DEFAULT RANDOM_UUID() ,
CONSTRAINT history_pkey_ PRIMARY KEY ( id_ )
)
;
&quot;&quot;&quot;;
try (
Connection conn = dataSource.getConnection ( ) ;
Statement stmt = conn.createStatement ( ) ;
)
{
stmt.executeUpdate ( sql );
} catch ( SQLException e ) { e.printStackTrace ( ); }
}
private DataSource obtainDataSource ( )
{
org.h2.jdbcx.JdbcDataSource ds = new JdbcDataSource ( );  // An implementation of `javax.sql.DataSource` bundled with H2.
ds.setURL ( &quot;jdbc:h2:mem:users_mystery_db;DB_CLOSE_DELAY=-1&quot; );
ds.setUser ( &quot;scott&quot; );
ds.setPassword ( &quot;tiger&quot; );
ds.setDescription ( &quot;An example database showing a query for table named `USERS`.&quot; );
return ds;
}
}

When run:

org.h2.engine.Constants.SCHEMA_MAIN = PUBLIC
---------------------
INFO - Query for INFORMATION_SCHEMA.USERS.
USER_NAME = SCOTT | IS_ADMIN = true | REMARKS = null
------------------
INFO - Query for PUBLIC.USERS.
ERROR - Table &quot;USERS&quot; not found (this database is empty); SQL statement:
SELECT *
FROM PUBLIC.USERS
;
[42104-214]
--------------
INFO - Query for USERS.
ERROR - Table &quot;USERS&quot; not found (this database is empty); SQL statement:
SELECT *
FROM USERS
;
[42104-214]
--------------
INFO - Running `createUsersTable` method. 
-----------------------------
INFO - Query for INFORMATION_SCHEMA.USERS.
USER_NAME = SCOTT | IS_ADMIN = true | REMARKS = null
------------------
INFO - Query for PUBLIC.USERS.
INFO - Query for USERS.
INFO - Demo done.

Trailing underscore avoids collisions

Tip: Append an underscore to every name (table, column, index, etc.) to avoid collision with keywords, reserved words, system names.

The SQL standard explicitly promises to never use a trailing underscore in any of its names, keywords, reserved words, system tables, and such.

huangapple
  • 本文由 发表于 2023年5月13日 17:48:14
  • 转载请务必保留本文链接：https://go.coder-hub.com/76242077.html
  • h2
  • java
  • jdbc
  • sql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定