JDBC 连接串中指定当前 schema(含 Oracle, DB2, PostgreSQL 和 SQLServer)

现在流行数据都有 Schema 的概念,一般作为数据库对象(表,函数,存储过程等)的命名空间。所以在数据库端往往存在 实例/数据库/Schema 这样层级划分。对于 DB2 和 Oracle 用客户端创建一个新的数据库并非易事,灵活的在数据库中较轻量的划分隔离空间的办法因数据库类型而异

  1. MySQL:  创建数据库(create database), create schema 是 create database 的别名
  2. PostgreSQL: create database 创建新的数据库,或在当前数据库下用 create schema 创建 schema
  3. SQLServer: 和 PostgreSQL 一样的自由,create database 创建新的数据库,或在当前数据库下用 create schema 创建 schema
  4. DB2: 用 create schema 创建新的 schema, 或创建数据库对象时直接加上前缀,create table abc.test1..., 没有 abc schema 则会自动创建
  5. Oracle: create schema 较麻烦,涉及到 authorization. 但可以通过 create user 创建新用户后就有了对应的新 schema

下面我们来了解下在 PostgreSQL/SQLServer 中创建新的 schema,如何在 JDBC 连接字串中指定默认 schema, 同时也涉及到 database/schema/user 的创建以及在 SQL 中如何切换。

MySQL 数据库

创建新的数据库,要连接新的数据库只要在 JDBC 连接字串中把数据库改下就行

JDBC 连接串是

jdbc:mysql://host:3306/new_db

PostgreSQL 数据库

它默认的 Schema 是 public, 创建,删除和使用新的 Schema SQL 语句是

PostgreSQL 依据 search_path 来定位数据库对象,如上面用 select * from t1 时就会从 my_schema 中找 t1 表

search_path 还能设定多个 schema, 如果执行

当执行 select * from t1 时先从 my_schema schema找是否有 t1 表,找到就用它,没找到的话再从 public schema 中找。所以两个  schema 中都有表 t1 的话,就 my_schema 的。也可指定 schema 来明确查询,如

为当前用户指定 search_path 的 SQL 是

假如用的是 PGSimpleDataSource 连接池实现, 可以针对 dataSource 调用 setCurrentSchema() 方法来指定默认的 schema

回到 JDBC 连接字符串,从 PostgreSQL 提供的 JDBC 驱动 9.4 版本开始,可以用参数 currentSchema=my_schema

java.sql.Connection 提供有一个  setSchema() 方法,它依赖于 JDBC 驱动的实现,对于 PostgreSQL 的驱动,调用

也能有效的切换到想要的 schema,但用 set schema my_schema, 能直接影响到 search_path 和 current_schema 的值。
下面是 search_path, current_schema, 引用函数,函数内部对表的引用的关系

设置 schema 时同时修改 search_path 的值为 schema。修改 search_path 后,第一个值将作为当前 schema 的值。函数或存储过程内部对其他对象引用是依据当前 schema, 与当前函数或存储过程的所属 schema 无关。

SQL Server(又名 MS SQL)

SQLServer 默认的 schema 是 dbo, 试过多种方式来指定新用户的默认 schema,但都无法工作

执行上面的语,可以用 new_user 登陆,但是看到的 select schema_name() 仍然是 dbo, 虽然用

select name, default_schema_name from sys.database_principals

看到的 sales 数据库是 my_schema
所以对于 SQL Server 只有一个办法就是使用表,存储过程的时候都加上 schema 前缀。不过 SQL Server 有一个聪明的地方就是在函数或存储过程中会访问与当前函数或存储过程所在 schema 中的资源。比如定义了函数

在用

调用时,函数中的 select * from t1 就会访问 new_schema.t1 表,这是比较安全的。 DB2 和 PostgreSQL 中存储过程/函数中访问其他资源是以当前会话的 schema 为准。
SQL Server 用 drop schema new_schema 删除时必须先把该 schema 下的资源全删除了才行。

DB2

DB2 中创建表或其他数据库对象时加上前缀就会自动创建相应的 schema, 如

 也可以显式的创建 schema

会话中可以切换 schema, 删除 schema 前也必须确保它是空的
JDBC 连接串中指定 schema

这里特别要注意,MY_SCHEMA 必须是全大写,并且最后的分号 ; 必不可少
在 DB2 的函数或存储过程中也需要小心的情况是,其中定位资源时是用当前会话中的 schema, 而与怎么引用该存储过程或函数无关。下面的例子

以上创建函数 schema1.test_fn() 无法成功,因为当前 schema 是 schema2,  而在 schema2 中不存在表 t1,错误是

SQL Error [42704]: "SCHEMA2.T1" is an undefined name.. SQLCODE=-204, SQLSTATE=42704, DRIVER=4.26.14

要想成功创建该函数,有两种方式

  1. 用 set schema schema1 切换当前 schema 为 schema1
  2. 在 schema2 中创建一个表 t1

我们采取第二种方式,保存当前 schema 为 schema2 的情况下,创建表 t1, 并插入数据以测试函数的行为

再执行以上的函数创建语句,就能建立 schema1.test_fn() 函数,测试

由以上可知,DB2 内部默认时对哪个 schema 下对象的访问是编译期绑定的,这与 PostgreSQL 的运行期由当前上下文(schema) 动态绑定是不一样的。
DB2 中除了在函数或存储过程中使用表时能根据上下文(current schema) 来定位,在函数或存储过程中调用其他函数或存储过程时的定位规则又不一样了。
比如连接 DB2 后创建 schema test, 并且设置了它为当前 schema

在创建 test_proc 时不能成功,出现如下错误

SQL Error [42884]: No authorized routine named "ANOTHER_PROC" of type "PROCEDURE" having compatible arguments was found.. SQLCODE=-440, SQLSTATE=42884, DRIVER=4.26.14

就是找不到先前创建的 another_prod, 原因是定位函数或存储过程与 current_schema 无关,而是依赖于 current_path

输出为

"SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","DB2USER"

TEST 不在其中,只要执行下面的 set current path 语句 test_proc 存储过程就能创建成功。

注意 SCHEMA1 也是要全大写的。反应到 JDBC 连接字符串的话就需要 currentFunctionPath=SCHEMA1; 属性,因此针对 DB2,使用新的 Schema 的话完整的 JDBC 连接串就是

Oracle

在 Oracle 中无论是创建新的 database 和 schema 都不是容易的事,但是我们可以创建一个新的用户,每个用户有自己的 schema。以下是创建新用户并授权的语句

Oracle 还允许级联的删除用户,连同其下的表或其他数据库对象一起删除。
有了新用户后,新的 JDBC 连接串就是

jdbc:oracle:thin:new_user/your_password@localhost:1521:ee

而且也不存在像 PostgreSQL 和 DB2 中函数或存储过程引用对象的问题。
同样,试图调用 java.sql.Connection 的 conn.setSchema(new_user) 也是不工作的,因为 Oracle 的驱动压根没实现该方法。

总结

  1. MySQL 中 database 和 schema 是一个概念,它靠 database 来隔离数据库对象
  2. Oracle 中采取用户来隔离数据库对象也还不错
  3. PostgreSQL 的函数或存储过程中对未指定 schema 的数据库对象的访问的规则是在调用时动态绑定到当前会话中的 schema
  4. DB2 的函数或存储过程中如有对未指 schema 的数据库对象的访问规则是在创建时静态绑定到当前会话的 schema
  5. 以上 #3 和 #4 中,函数或存储过程中内部默认的 schema 都与创建或调用函数或存储过程时的 schema 前缀无关

类别: Database. 标签: , . 阅读(375). 订阅评论. TrackBack.
guest
0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x