How to Set Default Schema In Oracle Using Commons DBCP

We need to specify schema name explicitly if the schema owner is different than the user by which we are logging in to database .This case is very common in production where schema is created by someone else , and you are provided separate user name and credentials to access the schema . First we create a user schema_owner , which will create a table called TEST_TAB , and now this user will create another user called app_user , and grants him read, write access .Here is the script :

// done by sysdba
-- Schema owner.
CREATE USER schema_owner IDENTIFIED BY owner
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp
  QUOTA UNLIMITED ON users;
  
GRANT CONNECT, CREATE TABLE TO schema_owner;

-- Application user.
CREATE USER app_user IDENTIFIED BY user
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp;

GRANT CONNECT, CREATE SYNONYM TO app_user;

CREATE ROLE schema_rw_role;

GRANT schema_rw_role TO app_user;

//// Done by schema_owner

CREATE TABLE test_tab (
  id          NUMBER,
  description VARCHAR2(50),
  CONSTRAINT test_tab_pk PRIMARY KEY (id)
);

GRANT SELECT, INSERT, UPDATE, DELETE ON test_tab TO schema_rw_role;

/// done by app_user
SELECT * FROM SCHEMA_OWNER.test_tab;

insert into SCHEMA_OWNER.test_tab values (1,'abhishek');
commit;

Now if you want to use SCHEMA_OWNER with user APP_USER , you have to set default schema in your Connection. For this , we can use setConnectionInitSqls , which will fire the query everytime a connection is requested from the pool . Here is the code to set Default Schema in Commons DBCP .If you do not set the default schema , you will not be able to access test_tab from app_user.

package com.datamigration.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

import org.apache.commons.dbcp.BasicDataSource;

/**
 * 
 * 
 * @author Abhishek Somani
 * 
 */
public class DataBase {
 
 private String driverClassName ;
 private String userName;
 
 private String password;
 
 private static int MAX_ACTIVE= 10;
 
 private String url;
 
 String SET_DEFAULT_SCHEMA="ALTER SESSION SET CURRENT_SCHEMA=SCHEMA_OWNER";
 private BasicDataSource ds = null;
 
 public void init() throws SQLException
 {
  ds = new BasicDataSource();
  ds.setDriverClassName("oracle.jdbc.driver.OracleDriver");
  ds.setPassword("user");
  ds.setUsername("app_user");
  ds.setUrl("jdbc:oracle:thin:@localhost:1521:ORCL");
  ds.setMaxActive(MAX_ACTIVE);
  List initSqls = new LinkedList();
  initSqls.add(SET_DEFAULT_SCHEMA);
  ds.setConnectionInitSqls(initSqls);
  //check connections
 }
 
 
 public Connection getConnection() throws SQLException
 {
  return ds.getConnection();
 }


 public String getDriverClassName() {
  return driverClassName;
 }


 public void setDriverClassName(String driverClassName) {
  this.driverClassName = driverClassName;
 }


 public String getUserName() {
  return userName;
 }


 public void setUserName(String userName) {
  this.userName = userName;
 }


 public String getPassword() {
  return password;
 }


 public void setPassword(String password) {
  this.password = password;
 }


 public String getUrl() {
  return url;
 }


 public void setUrl(String url) {
  this.url = url;
 }
 
 public static void main(String[] args) throws SQLException {
  
  DataBase db = new DataBase();
  db.init();
  Connection con = db.getConnection();
  String SELECT_BY_NAME="select * from TEST_TAB where id=?";
  PreparedStatement stmt = con.prepareStatement(SELECT_BY_NAME);
  stmt.setInt(1,1);
  ResultSet rs = stmt.executeQuery();
  if(rs.next())
  {
   System.out.println(rs.getString("description"));
  }
  
  
 }

}

In hibernate , we are given default_schema property to set default schema . Post Comments and Suggestions !! Source