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

2022-02-03 | 阅读(6,910)

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

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

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

MySQL 数据库

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

create database new_db;
use new_db;

1 2	create database new_db; use new_db;

JDBC 连接串是

jdbc:mysql://host:3306/new_db

PostgreSQL 数据库

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

create schema my_schema;
set search_path=my_schema;

drop schema if exists my_schema cascade;  -- 其下的数据库对象一并被删除

create schema my_schema;

set search_path=my_schema;

drop schema if exists my_schema cascade; -- 其下的数据库对象一并被删除

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

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

set search_path=my_schema,public

1	set search_path=my_schema,public

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

select * from public.t1

1	select * from public.t1

为当前用户指定 search_path 的 SQL 是

alter user user_name set search_path to 'my_schema'

1	alter user user_name set search_path to 'my_schema'

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

import org.postgresql.ds.PGSimpleDataSource;
...
PSSimpoeDataSource dataSource = new PSSimpleDataSource();
dataSource.setCurrentSchema("my_schema");

import org.postgresql.ds.PGSimpleDataSource;

...

PSSimpoeDataSource dataSource = new PSSimpleDataSource();

dataSource.setCurrentSchema("my_schema");

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

jdbc:postgresql://localhost:5432/mydatabase?currentSchema=my_schema

1	jdbc:postgresql://localhost:5432/mydatabase?currentSchema=my_schema

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

conn.setSchema(my_schema);

1	conn.setSchema(my_schema);

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

create table public.t1(c1 int);
insert into public.t1(c1) values(1);


create schema my_schema;
create table my_schema.t1(c1 int);
insert into my_schema.t1(c1) values(2);

select * from t1  -- c1: 1

create or replace function my_schema.get_data()
  returns table (c1 int)
  language plpgsql
as $$
begin
    return query select * from t1;
end; $$

show search_path   -- public, public, "$user"
select current_schema();  -- public
select * from my_schema.get_data() -- c1: 1


set schema 'my_schema';
show search_path;   -- my_schema 
select current_schema();  -- my_schema 
select * from my_schema.get_data() -- c1: 2

set schema 'public';
show search_path;  -- public
select current_schema(); -- public
select * from my_schema.get_data() -- c1: 1

set search_path='my_schema'
show search_path        -- my_schema
select current_schema(); -- my_schema
select * from my_schema.get_data()  -- c1: 2

set search_path to public, my_schema
show search_path        -- public,my_schema
select current_schema() -- public
select * from my_schema.get_data();  -- c1: 1

set search_path to my_schema, public
show search_path        -- my_schema, public
select current_schema() -- my_schema
select * from my_schema.get_data();  -- c1: 2

create table public.t1(c1 int);

insert into public.t1(c1) values(1);

create schema my_schema;

create table my_schema.t1(c1 int);

insert into my_schema.t1(c1) values(2);

select * from t1 -- c1: 1

create or replace function my_schema.get_data()

returns table (c1 int)

language plpgsql

as $$

begin

return query select * from t1;

end; $$

show search_path -- public, public, "$user"

select current_schema(); -- public

select * from my_schema.get_data() -- c1: 1

set schema 'my_schema';

show search_path; -- my_schema

select current_schema(); -- my_schema

select * from my_schema.get_data() -- c1: 2

set schema 'public';

show search_path; -- public

select current_schema(); -- public

select * from my_schema.get_data() -- c1: 1

set search_path='my_schema'

show search_path -- my_schema

select current_schema(); -- my_schema

select * from my_schema.get_data() -- c1: 2

set search_path to public, my_schema

show search_path -- public,my_schema

select current_schema() -- public

select * from my_schema.get_data(); -- c1: 1

set search_path to my_schema, public

show search_path -- my_schema, public

select current_schema() -- my_schema

select * from my_schema.get_data(); -- c1: 2

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

SQL Server(又名 MS SQL)

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

create database sales
use sales
create login test_login with password='ksd23%sdf', default_database=sales;
create user new_user for login test_login with default_schema=my_schema;
exec sp_changedbowner 'new_user'

--exec sp_addrolemember 'db_owner', 'new_user'
--sp_addlogin 'new_user', 'ksd23%sdf', 'master'
--alter user new_user with default_schema=new_schema
--SELECT name, default_schema_name FROM sys.database_principals

create database sales

use sales

create login test_login with password='ksd23%sdf', default_database=sales;

create user new_user for login test_login with default_schema=my_schema;

exec sp_changedbowner 'new_user'

--exec sp_addrolemember 'db_owner', 'new_user'

--sp_addlogin 'new_user', 'ksd23%sdf', 'master'

--alter user new_user with default_schema=new_schema

--SELECT name, default_schema_name FROM sys.database_principals

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

select name, default_schema_name from sys.database_principals

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

CREATE FUNCTION new_schema.test_fn()  
RETURNS TABLE  
AS  
RETURN   
(  
  select * from t1
);

CREATE FUNCTION new_schema.test_fn()

RETURNS TABLE

RETURN

(

select * from t1

);

在用

select * from new_schema.test_fn()

1	select * from new_schema.test_fn()

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

DB2

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

create table my_schema.t1(c1 integer);
drop table my_schema.t1 if exits;

1 2	create table my_schema.t1(c1 integer); drop table my_schema.t1 if exits;

也可以显式的创建 schema

create schema my_schema;
set schema my_schema;

create table t1(c1 integer); -- under my_schema

-- drop all objects under my_schema, then do
drop schema my_schema restrict;

create schema my_schema;

set schema my_schema;

create table t1(c1 integer); -- under my_schema

-- drop all objects under my_schema, then do

drop schema my_schema restrict;

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

jdbc:db2://localhost:50000/test_db:currentSchema=MY_SCHEMA;

1	jdbc:db2://localhost:50000/test_db:currentSchema=MY_SCHEMA;

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

CREATE TABLE schema1.t1(c1 integer);  -- 同时创建 Schema SCHEMA1 
creaet SCHEMA schema2;
SET SCHEMA schema2;
VALUES CURRENT SCHEMA;   -- SCHEMA2

CREATE OR REPLACE FUNCTION schema1.test_fn()
  RETURNS TABLE (c1 integer)
  
  LANGUAGE SQL
  READS SQL DATA
  NO EXTERNAL ACTION
  RETURN
    SELECT c1 FROM t1;

CREATE TABLE schema1.t1(c1 integer); -- 同时创建 Schema SCHEMA1

creaet SCHEMA schema2;

SET SCHEMA schema2;

VALUES CURRENT SCHEMA; -- SCHEMA2

CREATE OR REPLACE FUNCTION schema1.test_fn()

RETURNS TABLE (c1 integer)

LANGUAGE SQL

READS SQL DATA

NO EXTERNAL ACTION

RETURN

SELECT c1 FROM t1;

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

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

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

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

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

CREATE table schema2.t1(c1 integer);
INSERT INTO schema1.t1 values(1);
INSERT INTO schema2.t1 values(2);

CREATE table schema2.t1(c1 integer);

INSERT INTO schema1.t1 values(1);

INSERT INTO schema2.t1 values(2);

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

SET SCHEMA schema2;
SELECT * FROM TABLE(schema1.test_fn());  -- C1: 2

SET SCHEMA schema1;
SELECT * FROM TABLE(schema1.test_fn());  -- C1: 2

SET SCHEMA schema2;

SELECT * FROM TABLE(schema1.test_fn()); -- C1: 2

SET SCHEMA schema1;

SELECT * FROM TABLE(schema1.test_fn()); -- C1: 2

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

values current_schema; -- schema1

CREATE OR REPLACE PROCEDURE another_proc()
LANGUAGE SQL
BEGIN   
END

CREATE OR REPLACE PROCEDURE test_proc()
LANGUAGE SQL
BEGIN   
    CALL another_proc();
END

values current_schema; -- schema1

CREATE OR REPLACE PROCEDURE another_proc()

LANGUAGE SQL

BEGIN

END

CREATE OR REPLACE PROCEDURE test_proc()

LANGUAGE SQL

BEGIN

CALL another_proc();

END

在创建 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

values CURRENT_PATH;

1	values CURRENT_PATH;

输出为

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

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

SET CURRENT PATH = "SCHEMA1","SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","DB2USER"

1	SET CURRENT PATH = "SCHEMA1","SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","DB2USER"

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

jdbc:db2://localhost:50000/test_db:currentSchema=MY_SCHEMA;currentFunctionPath=MY_SCHEMA;

1	jdbc:db2://localhost:50000/test_db:currentSchema=MY_SCHEMA;currentFunctionPath=MY_SCHEMA;

Oracle

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

create user new_user identified by your_password;
--grant create session to new_user;
--grant unlimited tablespace to new_user;
grant all privileges to new_user;
create table new_user.t1(c1 integer);

alter session set current_schema=new_user
create table t1(c1 integer);   -- under new_user
drop user new_user cascade;

create user new_user identified by your_password;

--grant create session to new_user;

--grant unlimited tablespace to new_user;

grant all privileges to new_user;

create table new_user.t1(c1 integer);

alter session set current_schema=new_user

create table t1(c1 integer); -- under new_user

drop user new_user cascade;

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

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

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

总结

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

本文链接 https://yanbin.blog/jdbc-conn-string-specify-current-schema/, 来自隔叶黄莺 Yanbin Blog

best coffee on SciPy 最优化之最小化I wanted to take a moment to commend you on the outstanding quality of...
seetimee on 体验 Python FastAPI 的并发能力及线, 进程模型感谢
Yanbin on Mockito 3.4.0 开始可 Mock 静态方法有一个补救，新写了一篇 https://yanbin.blog/mockito-mock-static-method-in-multiple...
Yanbin on 升级到 Spring Boot 3 后 javax.inject.Named 不可用怎么，被抄袭了！算是被机器翻译引用的？
oleksi on 升级到 Spring Boot 3 后 javax.inject.Named 不可用https://www.springcloud.io/post/2023-02/springboot-3-javax-inject-name...