JNDI Connection Pool - Yash-777/SteamingServlet GitHub Wiki

Links:

Oracle Supported Database Specifiers

Connection Pooling Concepts

If you do not use connection pooling, each connection instance (java.sql.Connection or oracle.jdbc.OracleConnection instance) encapsulates its own physical database connection. When you call the close() method of the connection instance, the physical connection itself is closed. This is true whether you obtain the connection instance through the JDBC 2.0 data source facility described under "Data Sources", or through the DriverManager facility described under "Open a Connection to a Database".

To perform a lookup and open a connection to the database logically bound to the JNDI name, use the logical JNDI name.

The JDBC 2.0 Specification requires that all JDBC data sources be registered in the jdbc naming subcontext of a JNDI namespace or in a child subcontext of the jdbc subcontext.

JNDI_SUBCONTEXT = "java:comp/env/";
JNDI_SUBCONTEXT_LogicalName = "java:comp/env/jdbc/";
ctx.lookup("jdbc/sampledb");

Oracle Connection Event Listener: OracleConnectionEventListener Class

Web Application - Read the DB details form Contex.xml

public class DBConnection {
    Connection conn = null;
    
    private static final String JNDI_SUBCONTEXT = "java:comp/env/";
    private static final String JNDI_SUBCONTEXT_LogicalName = "java:comp/env/jdbc/";
    private ArrayList<DataSource> dataSourcesList = new ArrayList<DataSource>();
    public void initialContextJNDI() {
        try {
            Context ctx = new InitialContext();
            /** To Get the list of NameClassPair registered with JNDI */
            javax.naming.NamingEnumeration<NameClassPair> list = ctx.list(JNDI_SUBCONTEXT_LogicalName);
            String datasourceName = "";
            while (list.hasMore()) {
                try {
                    datasourceName = JNDI_SUBCONTEXT_LogicalName + "" + list.next().getName();
                    DataSource dataSource = (DataSource) ctx.lookup(datasourceName);
                    System.out.println("Initialized datasource " + datasourceName + " successfully.");
                    dataSourcesList.add(dataSource);
                } catch (NamingException e) {
                    System.out.println("Datasource with name " + datasourceName + " not found in jndi-tree."+e);
                    break;
                }
            }
            System.out.println("DataSourece List : "+dataSourcesList.toString());
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public Connection getDataSourceConnection(int dataSourceIndex) {
        try {
            for (int i = 0; i < dataSourcesList.size(); i++) {
                if (i == dataSourceIndex) {
                    DataSource dataSource = dataSourcesList.get(dataSourceIndex);
                    conn = dataSource.getConnection();
                    
                    /** get Meta Data for user */
                    String dbUser = conn.getMetaData().getUserName();
                    System.out.println("DataSourceConnection user name is " + dbUser);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }
    
    public Connection getDataSourceConnectionByResourceName(String environmentName) {
        Connection conn = null;
        try {
            Context ctx = new InitialContext();
            // OracleDataSource odsconn = (OracleDataSource)ctx.lookup("jdbc/sampledb");
            String datasourceName = (String) ctx.lookup(JNDI_SUBCONTEXT + "" + environmentName);
            javax.sql.DataSource dataSource = (DataSource) ctx.lookup(JNDI_SUBCONTEXT + "" + datasourceName);
            conn = dataSource.getConnection();
            
            /** get Meta Data for user */
            String dbUser = conn.getMetaData().getUserName();
            System.out.println("DataSourceConnectionByResourceName user name is " + dbUser);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }
}

Configure a resource for JNDI lookups

<?xml version="1.0" encoding="UTF-8"?>
<Context>
    <Environment name="dataSourceName" value="jdbc/JNDI_test" type="java.lang.String" override="false" />
    <Environment name="dataSourceName_2" value="jdbc/JNDI_test2" type="java.lang.String" override="false" />

    <Resource name="jdbc/JNDI_test" type="javax.sql.DataSource"
        username="scott" password="tiger" url="jdbc:oracle:thin:@127.0.0.1:1521:${dbname}"
        driverClassName="oracle.jdbc.driver.OracleDriver" maxTotal="20"
        maxIdle="10" maxWaitMillis="5000" validationQuery="select 1 from dual"
        testOnBorrow="true" auth="Container" />
        
    <Resource name="jdbc/JNDI_test2" type="javax.sql.DataSource"
        username="scott2" password="tiger2" 
        url="jdbc:oracle:thin:@(DESCRIPTION=(ENABLE=BROKEN)(ADDRESS=(PROTOCOL=tcp)(PORT=1521)(HOST=127.0.0.1))(CONNECT_DATA=(SID=${dbname})))"
        driverClassName="oracle.jdbc.driver.OracleDriver" maxTotal="20" maxIdle="10"
        maxWaitMillis="5000" minEvictableIdleTimeMillis="300000" timeBetweenEvictionRunsMillis="600000" validationInterval="600000"
        validationQuery="select 1 from dual" testOnBorrow="true" auth="Container" />
        
    <!-- accessToUnderlyingConnectionAllowed="true" -->
</Context>

SQL Commands:

-- DB_Name/SERVICE NAME/instance_name [XE]
-- jdbc:oracle:thin:@<server_host>:1521:<instance_name>
select name from V$database;  -- DB Name
select instance_name from v$instance; --
select sys_context('userenv','instance_name') from dual; -- Current Instance DB Name
select sys_context('userenv','db_name') from dual;

select ora_database_name from dual;
select * from global_name;

-- server_host
select sys_context('userenv', 'server_host') from dual;

-- User Instance
select user from dual;  -- User Name of Current User Instance

-- port
-- (ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521))
select * from v$listener_network;

--
SELECT sys_context('USERENV', 'SID') FROM DUAL;

-- 
variable i number;
variable dbname varchar2(30);
begin
    :i:=dbms_utility.get_parameter_value('db_name',:i,:dbname);
  end;
-- PL/SQL procedure successfully completed.
print dbname;
--

-- jdbc/name(any name --this will use in project(jndi)
⚠️ **GitHub.com Fallback** ⚠️