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

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

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

1conn.setSchema(my_schema);

 1create table public.t1(c1 int);
 2insert into public.t1(c1) values(1);
 3
 4
 5create schema my_schema;
 6create table my_schema.t1(c1 int);
 7insert into my_schema.t1(c1) values(2);
 8
 9select * from t1  -- c1: 1
10
11create or replace function my_schema.get_data()
12  returns table (c1 int)
13  language plpgsql
14as $$
15begin
16    return query select * from t1;
17end; $$
18
19show search_path   -- public, public, "$user"
20select current_schema();  -- public
21select * from my_schema.get_data() -- c1: 1
22
23
24set schema 'my_schema';
25show search_path;   -- my_schema 
26select current_schema();  -- my_schema 
27select * from my_schema.get_data() -- c1: 2
28
29set schema 'public';
30show search_path;  -- public
31select current_schema(); -- public
32select * from my_schema.get_data() -- c1: 1
33
34set search_path='my_schema'
35show search_path        -- my_schema
36select current_schema(); -- my_schema
37select * from my_schema.get_data()  -- c1: 2
38
39set search_path to public, my_schema
40show search_path        -- public,my_schema
41select current_schema() -- public
42select * from my_schema.get_data();  -- c1: 1
43
44set search_path to my_schema, public
45show search_path        -- my_schema, public
46select current_schema() -- my_schema
47select * from my_schema.get_data();  -- c1: 2

 1create database sales
 2use sales
 3create login test_login with password='ksd23%sdf', default_database=sales;
 4create user new_user for login test_login with default_schema=my_schema;
 5exec sp_changedbowner 'new_user'
 6
 7--exec sp_addrolemember 'db_owner', 'new_user'
 8--sp_addlogin 'new_user', 'ksd23%sdf', 'master'
 9--alter user new_user with default_schema=new_schema
10--SELECT name, default_schema_name FROM sys.database_principals

1CREATE FUNCTION new_schema.test_fn()
2RETURNS TABLE  
3AS  
4RETURN   
5(  
6  select * from t1
7);  

1select * from new_schema.test_fn()

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

1create schema my_schema;
2set schema my_schema;
3
4create table t1(c1 integer); -- under my_schema
5
6-- drop all objects under my_schema, then do
7drop schema my_schema restrict;

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

 1CREATE TABLE schema1.t1(c1 integer);  -- 同时创建 Schema SCHEMA1
 2creaet SCHEMA schema2;
 3SET SCHEMA schema2;
 4VALUES CURRENT SCHEMA;   -- SCHEMA2
 5
 6CREATE OR REPLACE FUNCTION schema1.test_fn()
 7  RETURNS TABLE (c1 integer)
 8  
 9  LANGUAGE SQL
10  READS SQL DATA
11  NO EXTERNAL ACTION
12  RETURN
13    SELECT c1 FROM t1;

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

1SET SCHEMA schema2;
2SELECT * FROM TABLE(schema1.test_fn());  -- C1: 2<br/><br/>
3SET SCHEMA schema1;
4SELECT * FROM TABLE(schema1.test_fn());  -- C1: 2

 1values current_schema; -- schema1
 2
 3CREATE OR REPLACE PROCEDURE another_proc()
 4LANGUAGE SQL
 5BEGIN   
 6END
 7
 8CREATE OR REPLACE PROCEDURE test_proc()
 9LANGUAGE SQL
10BEGIN   
11    CALL another_proc();
12END

1values CURRENT_PATH;

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

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

1create user new_user identified by your_password;
2--grant create session to new_user;
3--grant unlimited tablespace to new_user;
4grant all privileges to new_user;
5create table new_user.t1(c1 integer);
6
7alter session set current_schema=new_user
8create table t1(c1 integer);   -- under new_user
9drop user new_user cascade;