How to create multiple schema connections at runtime?

Question

I have a a project name drop-down list, and based on the selected value a schema drop-down list is generated.

After the schema list is generated, there is a 'choose file' option where a script name is chosen which is to be run on multiple schemas.

Both the project list values and the schema list values are stored in the database. There is another table in the database where the schema credentials are stored.

I want to know that how create connections on those multiple schemas, and how to run a script in multiple schemas selected from that schema drop-down list.

I have executed the code in one schema using iBatis framework using scriptrunner method.

Table 1: ProjectName

PROJECT_PK,
PROJECT_CODE,
PROJECT_NAME

Table2: ComponentName

COMPONENT_PK,
COMPONENT_CODE,
COMPONENT_NAME,
PROJECT_PK

Table 3: SchemaName (This table contains the credentials of Other Schemas)

SCHEMA_PK,
SCHEMA_NAME,
PASSWORD,
ORACLE_SID,
HOST_NAME,
PORT_ID

Table 4: PROJECT_DETAIL

PROJECT_DETAIL_PK,
COMPONENT_PK,
SCHEMA_PK

Table5: COMPONENT_DETAILS

COMPONENT_DETAILS_PK,
PROJECT_PK,
SCHEMA_PK

I am attaching the scenario image.

Answer 1

I propose you create a 'super-schema' with the appropiate grants on every other schema, and create your JDBC connection for this 'super-schema'. This will make you need some tampering with the sql script - you will need to write it with some marker to easily substitute the schema, something like

ALTER TABLE ${SCHEMA_MARKER}.INVOICES ADD CLIENT_ADRRESS Varchar2(35);

And your java code would substitute it for the schema you are targeting. With some imagination you can extend this idea to execute bulk DDL scripts on all your schemas.

By the way, I understand by your question that you have many schemas with the same structure. I was forced once to work with such a legacy structure, and so I know there is a lot of momentum in corporate structures; nonetheless I must recommend you to redesign such system. Think for example in creating materialized views on your super-schema, replicating your schema structure but with a new field in every table primary key (this new field would fill the gap that made whomever did it separe the data in many schemas for starters).

This will surely make your query cache suffer a lot less, and will make easier the development of any new software that needs to work with that 'distributed' data.

Answer 2

In Oracle, a schema is basically a user. So to create a table FOO in the schema SX, you just login as user SX and execute create table FOO (...) (without specifying a schema). The user == schema convention in Oracle will make sure this works.

To login as user SX, get hold of your DataSource and use getConnection(String username, String password) instead of the default getConnection()

Alternative ways are to add placeholders in your script that you process to generate valid SQL first. See the answer by Jorge_B for an example.

Lastly, you can change the default schema using ALTER SESSION (see Default Schema in Oracle Connection URL). The problem with this approach is that you must restore the schema before closing the connection when using a web container with a DataSource - in this scenario, connections are never really closed, so the next piece of code asking for a connection will get one with an unexpected default schema -> hard to find errors.

Answer 3

Here is the DAO class to create multiple schema connection on the same database at run time.

We need to enter the schema names at run time through and call that in some servlet.

The schema details i.e username, password,host Id,port Id and SID are to be stored in the database tables though which the connections will be created.

The created connections are stored here in the list which can be used at a later point of time.

The credential DTO here is the object which maps to the database tables in the database and then

Connection conn=DBUtil.getConnection(constants.DB_DS_NAME);
        Statement stmt=null;
        stmt=conn.createStatement();
        ResultSet rs= null;
        Connection [] con=new Connection[schemaname.length];
        int i,j;
        String [] url=new String[schemaname.length];
        String [] username=new String[schemaname.length];
        String [] password=new String[schemaname.length];
        List<CredentialDTO> creDTOlist=new ArrayList<CredentialDTO>();
        String query1="insert into dba_project_master VALUES(9,'abc','abc','abc','abc',40)";
        String query2="CREATE TABLE EMP(EMPNO NUMBER(4,0) NOT NULL ENABLE,ENAME VARCHAR2(10 BYTE),JOB VARCHAR2(9 BYTE), SAL NUMBER(7,2),DEPTNO NUMBER(2,0))";

        try
        {
            for(i=0;i<schemaname.length;i++){
                String query=" select * from dba_schema_details where schema_name="+DBUtil.enquoteString(schemaname[i]);
                rs=stmt.executeQuery(query);

                while(rs.next()){
                    CredentialDTO creDTO=new CredentialDTO();
                    creDTO.setSCHEMA_PK(rs.getString("SCHEMA_PK"));
                    creDTO.setSCHEMA_NAME(rs.getString("SCHEMA_NAME"));
                    creDTO.setPASSWORD(rs.getString("PASSWORD"));
                    creDTO.setORACLE_SID(rs.getString("ORACLE_SID"));
                    creDTO.setHOST_NAME(rs.getString("HOST_NAME"));
                    creDTO.setPORT_ID(rs.getString("PORT_ID"));
                    creDTOlist.add(creDTO);
                }
            }
                System.out.println("creDTOlist size:"+creDTOlist.size());
                //create URL for the schema name
                int m=creDTOlist.size();
                Iterator<CredentialDTO> LItr= creDTOlist.iterator();
                String [] username1=new String[m];
                String [] password1=new String[m];
                i=0;
                while(LItr.hasNext()){

                    System.out.println("iteration "+i); 
                    CredentialDTO temp = LItr.next();
                    String URL="jdbc:oracle:thin:@"+temp.getHOST_NAME()+":"+temp.getPORT_ID()+":"+temp.getORACLE_SID();
                    System.out.println("URL:"+URL);
                    username1[i]=temp.getSCHEMA_NAME();
                    System.out.println("iteartion "+i+" username="+username1[i]);
                    password1[i]=temp.getPASSWORD();
                    System.out.println("iteartion "+i+" password="+password1[i]);
                    Class.forName("oracle.jdbc.OracleDriver");
                    con[i]=DriverManager.getConnection(URL, username1[i], password1[i]);
                    System.out.println("Connection Name:" +con[i]);
                    Statement st1=con[i].createStatement();
                    con[i].setAutoCommit(false);
                    st1.addBatch(query1);
                    st1.addBatch(query2);
                    int [] update=st1.executeBatch();
                    i++;
                }
            }
            catch(Exception ex){
                    ex.printStackTrace();
            }finally
        {
            if (conn != null) try{conn.close();} catch(SQLException ignore) {}
            if (stmt!= null) try{stmt.close();} catch(SQLException ignore) {}
            if (rs != null) try{rs.close();} catch(SQLException ignore) {}
        }
        return creDTOlist;

    }

}