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

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

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:

  1. CREATE USER pinf PASSWORD 'pinf' ADMIN;
  2. GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA PUBLIC TO pinf;
  3. 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。

示例代码

  1. package work.basil.example.db;
  3. import org.h2.jdbcx.JdbcDataSource;
  5. import javax.sql.DataSource;
  6. import java.sql.Connection;
  7. import java.sql.ResultSet;
  8. import java.sql.SQLException;
  9. import java.sql.Statement;
  11. public class UsersTableEx
  12. {
  13.     public static void main ( String[] args )
  14.     {
  15.         System.out.println ( "org.h2.engine.Constants.SCHEMA_MAIN = " + org.h2.engine.Constants.SCHEMA_MAIN + "\n---------------------" );
  16.         UsersTableEx app = new UsersTableEx ( );
  17.         DataSource dataSource = app.obtainDataSource ( );
  19.         app.queryInformationSchemaUsers ( dataSource );
  20.         app.queryPublicUsers ( dataSource );
  21.         app.queryUsers ( dataSource );
  23.         app.createUsersTable(dataSource );
  25.         app.queryInformationSchemaUsers ( dataSource );
  26.         app.queryPublicUsers ( dataSource );
  27.         app.queryUsers ( dataSource );
  29.         System.out.println ( "INFO - Demo done." );
  30.     }
  31.     
  32.     private void queryInformationSchemaUsers ( DataSource dataSource )
  33.     {
  34.         System.out.println ( "INFO - Query for INFORMATION_SCHEMA.USERS." );
  35.         String sql = 
  36.                 "SELECT *" +
  37.                 "FROM INFORMATION_SCHEMA.USERS";
  38.         try (
  39.                 Connection conn = dataSource.getConnection ( ) ;
  40.                 Statement statement = conn.createStatement ( ) ;
  41.         )
  42.         {
  43.             try (
  44.                     ResultSet resultSet = statement.executeQuery ( sql ) ;
  45.             )
  46.             {
  47.                 while ( resultSet.next ( ) )
  48.                 {
  49.                     System.out.println (
  50.                             "USER_NAME = " + resultSet.getString ( "USER_NAME" ) +
  51.                             " | IS_ADMIN = " + resultSet.getBoolean ( "IS_ADMIN" ) +
  52.                             " | REMARKS = " + resultSet.getString ( "REMARKS" ) +
  53.                             "\n------------------"
  54.                     );
  55.                 }
  56.             }
  57.         }
  58.         catch ( SQLException e ) { throw new RuntimeException ( e ); }
  59.     }
  61.     private void queryPublicUsers ( DataSource dataSource )
  62.     {
  63.         System.out.println ( "INFO - Query for PUBLIC.USERS." );
  64.         String sql = 
  65.                 "SELECT *" +
  66.                 "FROM PUBLIC.USERS";
  67.         try (
  68.                 Connection conn = dataSource.getConnection ( ) ;
  69.                 Statement statement = conn.createStatement ( ) ;
  70.         )
  71.         {
  72.             try (
  73.                     ResultSet resultSet = statement.executeQuery ( sql ) ;
  74.             )
  75.             { while ( resultSet.next ( ) ) System.out.println ( "Found row." ); }
  76.         }
  77.         catch ( org.h2.jdbc.JdbcSQLSyntaxErrorException e )
  78.         {
  79.             System.out.println ( "ERROR - " + e.getMessage ( ) + "\n--------------" );
  80.         }
  81.         catch ( SQLException e ) { throw new RuntimeException ( e ); }
  82.     }
  84.     private void queryUsers ( DataSource dataSource )
  85.     {
  86.         System.out.println ( "INFO - Query for USERS." );
  87.         String sql = 
  88.                 "SELECT *" +
  89.                 "FROM USERS";
  90.         try (
  91.                 Connection conn = dataSource.getConnection ( ) ;
  92.                 Statement statement = conn.createStatement ( ) ;
  93.         )
  94.         {
  95.             try (
  96.                     ResultSet resultSet = statement.executeQuery ( sql ) ;
  97.             )
  98.             { while ( resultSet.next ( ) ) System.out.println ( "Found row." ); }
  99.         }
  100.         catch ( org.h2.jdbc.JdbcSQLSyntaxErrorException e )
  101.         {
  102.             System.out.println ( "ERROR - " + e.getMessage ( ) + "\n--------------" );
  103.         }
  104.         catch ( SQLException e ) { throw new RuntimeException ( e ); }
  105.     }
  107.     private void createUsersTable ( DataSource dataSource )
  108.     {
  109.         System.out.println ( "INFO - Running `createUsersTable` method. \n-----------------------------" );
  110.         String sql = 
  111.                 "CREATE TABLE IF NOT EXISTS USERS" +
  112.                 "(" +
  113.                     "user_ TEXT NOT NULL ," +
  114.                     "id_ UUID NOT NULL DEFAULT RANDOM_UUID()," +
  115.                     "CONSTRAINT history_pkey_ PRIMARY KEY ( id_ )" +
  116.                 ")";
  117.         try (
  118.                 Connection conn = dataSource.getConnection ( ) ;
  119.                 Statement stmt = conn.createStatement ( ) ;
  120.         )
  121.         {
  122.             stmt.executeUpdate ( sql );
  123.         } catch ( SQLException e ) { e.printStackTrace ( ); }
  124.     }
  126.     private DataSource obtainDataSource ( )
  127.     {
  128.         org.h2.jdbcx.JdbcDataSource ds = new JdbcDataSource ( );  
  129.         ds.setURL ( "jdbc:h2:mem:users_mystery_db;DB_CLOSE_DELAY=-1" );
  130.         ds.setUser ( "scott" );
  131.         ds.setPassword ( "tiger" );
  132.         ds.setDescription ( "An example database showing a query for table named `USERS`." );
  133.         return ds;
  134.     }
  135. }

当运行时：

  1. org.h2.engine.Constants.SCHEMA_MAIN = PUBLIC
  2. ---------------------
  3. INFO - Query for INFORMATION_SCHEMA.USERS.
  4. USER_NAME = SCOTT | IS_ADMIN = true | REMARKS = null
  5. ------------------
  6. INFO - Query for PUBLIC.USERS.
  7. ERROR - Table "USERS" not found (this database is empty); SQL statement:
  8. SELECT *
  9. FROM PUBLIC.USERS
  10. ;
  11. [42104-214]
  12. --------------
  13. INFO - Query for USERS.
  14. ERROR -
  15. <details>
  16. <summary>英文:</summary>
  17. # tl;dr
  18. Unreproducible.
  19. The default schema in H2 is `PUBLIC`. If you create a table there named `USERS`, that table is found by default. 
  20. Be aware of your current schema. The standard `INFORMATION_SCHEMA` schema contains a table named `USERS`.
  21. # Not reproducible
  22. As noted in the Comments:
  23. - `USERS` is not a reserved word in H2 Database Engine.
  24. - No table named `USERS` is available by default in H2. 
  25. - The default schema in H2 is `PUBLIC`. If you create a table there, and query on that table, it will be found by default. 
  26. So the problem raised in your Question is not reproducible. 
  27. 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]. 
  28. Here is an example app that makes 3 attempts to query a table named `USERS` in:
  29. - The schema named `INFORMATION_SCHEMA`. 
  30. - The schema named `PUBLIC`, explicitly.
  31. - The default schema (which is `PUBLIC`, implicitly). 
  32. The results are, respectively:
  33. - Success. Table found in `INFORMATION_SCHEMA`. 
  34. - Failure. No such table in `PUBLIC`. 
  35. - Failure. No such table in default schema, `PUBLIC`.
  36. Next we create a table named exactly `USERS` in the default schema, `PUBLIC`. And we run our same 3 attempts above.
  37. The results are, respectively:
  38. - Success. Table found in `INFORMATION_SCHEMA`. 
  39. - Success. Table found in `PUBLIC`. 
  40. - Success. Table found in default schema, `PUBLIC`.
  41. 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.
  42. # Example code
  43. ```java
  44. package work.basil.example.db;
  45. import org.h2.jdbcx.JdbcDataSource;
  46. import javax.sql.DataSource;
  47. import java.sql.Connection;
  48. import java.sql.ResultSet;
  49. import java.sql.SQLException;
  50. import java.sql.Statement;
  51. public class UsersTableEx
  52. {
  53. public static void main ( String[] args )
  54. {
  55. System.out.println ( &quot;org.h2.engine.Constants.SCHEMA_MAIN = &quot; + org.h2.engine.Constants.SCHEMA_MAIN + &quot;\n---------------------&quot; );
  56. UsersTableEx app = new UsersTableEx ( );
  57. DataSource dataSource = app.obtainDataSource ( );
  58. app.queryInformationSchemaUsers ( dataSource );
  59. app.queryPublicUsers ( dataSource );
  60. app.queryUsers ( dataSource );
  61. app.createUsersTable(dataSource );
  62. app.queryInformationSchemaUsers ( dataSource );
  63. app.queryPublicUsers ( dataSource );
  64. app.queryUsers ( dataSource );
  65. System.out.println ( &quot;INFO - Demo done.&quot; );
  66. }
  67. private void queryInformationSchemaUsers ( DataSource dataSource )
  68. {
  69. System.out.println ( &quot;INFO - Query for INFORMATION_SCHEMA.USERS.&quot; );
  70. String sql = &quot;&quot;&quot;
  71. SELECT *
  72. FROM INFORMATION_SCHEMA.USERS
  73. ;
  74. &quot;&quot;&quot;;
  75. try (
  76. Connection conn = dataSource.getConnection ( ) ;
  77. Statement statement = conn.createStatement ( ) ;
  78. )
  79. {
  80. try (
  81. ResultSet resultSet = statement.executeQuery ( sql ) ;
  82. )
  83. {
  84. while ( resultSet.next ( ) )
  85. {
  86. System.out.println (
  87. &quot;USER_NAME = &quot; + resultSet.getString ( &quot;USER_NAME&quot; ) +
  88. &quot; | IS_ADMIN = &quot; + resultSet.getBoolean ( &quot;IS_ADMIN&quot; ) +
  89. &quot; | REMARKS = &quot; + resultSet.getString ( &quot;REMARKS&quot; ) +
  90. &quot;\n------------------&quot;
  91. );
  92. }
  93. }
  94. }
  95. catch ( SQLException e ) { throw new RuntimeException ( e ); }
  96. }
  97. private void queryPublicUsers ( DataSource dataSource )
  98. {
  99. System.out.println ( &quot;INFO - Query for PUBLIC.USERS.&quot; );
  100. String sql = &quot;&quot;&quot;
  101. SELECT *
  102. FROM PUBLIC.USERS
  103. ;
  104. &quot;&quot;&quot;;
  105. try (
  106. Connection conn = dataSource.getConnection ( ) ;
  107. Statement statement = conn.createStatement ( ) ;
  108. )
  109. {
  110. try (
  111. ResultSet resultSet = statement.executeQuery ( sql ) ;
  112. )
  113. { while ( resultSet.next ( ) ) System.out.println ( &quot;Found row.&quot; ); }
  114. }
  115. catch ( org.h2.jdbc.JdbcSQLSyntaxErrorException e )
  116. {
  117. System.out.println ( &quot;ERROR - &quot; + e.getMessage ( ) + &quot;\n--------------&quot; );
  118. }
  119. catch ( SQLException e ) { throw new RuntimeException ( e ); }
  120. }
  121. private void queryUsers ( DataSource dataSource )
  122. {
  123. System.out.println ( &quot;INFO - Query for USERS.&quot; );
  124. String sql = &quot;&quot;&quot;
  125. SELECT *
  126. FROM USERS
  127. ;
  128. &quot;&quot;&quot;;
  129. try (
  130. Connection conn = dataSource.getConnection ( ) ;
  131. Statement statement = conn.createStatement ( ) ;
  132. )
  133. {
  134. try (
  135. ResultSet resultSet = statement.executeQuery ( sql ) ;
  136. )
  137. { while ( resultSet.next ( ) ) System.out.println ( &quot;Found row.&quot; ); }
  138. }
  139. catch ( org.h2.jdbc.JdbcSQLSyntaxErrorException e )
  140. {
  141. System.out.println ( &quot;ERROR - &quot; + e.getMessage ( ) + &quot;\n--------------&quot; );
  142. }
  143. catch ( SQLException e ) { throw new RuntimeException ( e ); }
  144. }
  145. private void createUsersTable ( DataSource dataSource )
  146. {
  147. System.out.println ( &quot;INFO - Running `createUsersTable` method. \n-----------------------------&quot; );
  148. String sql = &quot;&quot;&quot;
  149. CREATE TABLE IF NOT EXISTS USERS
  150. (
  151. user_ TEXT NOT NULL ,
  152. id_ UUID NOT NULL DEFAULT RANDOM_UUID() ,
  153. CONSTRAINT history_pkey_ PRIMARY KEY ( id_ )
  154. )
  155. ;
  156. &quot;&quot;&quot;;
  157. try (
  158. Connection conn = dataSource.getConnection ( ) ;
  159. Statement stmt = conn.createStatement ( ) ;
  160. )
  161. {
  162. stmt.executeUpdate ( sql );
  163. } catch ( SQLException e ) { e.printStackTrace ( ); }
  164. }
  165. private DataSource obtainDataSource ( )
  166. {
  167. org.h2.jdbcx.JdbcDataSource ds = new JdbcDataSource ( );  // An implementation of `javax.sql.DataSource` bundled with H2.
  168. ds.setURL ( &quot;jdbc:h2:mem:users_mystery_db;DB_CLOSE_DELAY=-1&quot; );
  169. ds.setUser ( &quot;scott&quot; );
  170. ds.setPassword ( &quot;tiger&quot; );
  171. ds.setDescription ( &quot;An example database showing a query for table named `USERS`.&quot; );
  172. return ds;
  173. }
  174. }

When run:

  1. org.h2.engine.Constants.SCHEMA_MAIN = PUBLIC
  2. ---------------------
  3. INFO - Query for INFORMATION_SCHEMA.USERS.
  4. USER_NAME = SCOTT | IS_ADMIN = true | REMARKS = null
  5. ------------------
  6. INFO - Query for PUBLIC.USERS.
  7. ERROR - Table &quot;USERS&quot; not found (this database is empty); SQL statement:
  8. SELECT *
  9. FROM PUBLIC.USERS
  10. ;
  11. [42104-214]
  12. --------------
  13. INFO - Query for USERS.
  14. ERROR - Table &quot;USERS&quot; not found (this database is empty); SQL statement:
  15. SELECT *
  16. FROM USERS
  17. ;
  18. [42104-214]
  19. --------------
  20. INFO - Running `createUsersTable` method. 
  21. -----------------------------
  22. INFO - Query for INFORMATION_SCHEMA.USERS.
  23. USER_NAME = SCOTT | IS_ADMIN = true | REMARKS = null
  24. ------------------
  25. INFO - Query for PUBLIC.USERS.
  26. INFO - Query for USERS.
  27. 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:

确定