JAVA EE7 / JDBC integration with RAC

Learning the SCAN address used in our JDBC DataSource URL

• Connect to any of your RAC nodes and execute the following instructions

Connect to any of your  RAC nodes:
[oracle@grac41 ~]$ srvctl config scan
SCAN name: grac4-scan.grid4.example.com, Network: 1/192.168.1.0/255.255.255.0/eth1
-->  grac4-scan.grid4.example.com is the host name used in our JDBC URL

Query the local listener to get Portnumber and Service Name 
[oracle@grac41 ~]$ lsnrctl status
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
..
Service "grac4" has 1 instance(s).
  Instance "grac41", status READY, has 1 handler(s) for this service...
Service "grac41" has 1 instance(s).
  Instance "grac41", status READY, has 1 handler(s) for this service...

Verify with sqlplus
[oracle@grac41 ~]$ sqlplus scott/tiger@grac4-scan.grid4.example.com:1521/grac4 @v1
SQL> select host_name,instance_name from v$instance;
HOST_NAME               INSTANCE_NAME
------------------------------ ----------------
grac41.example.com           grac41

Test your JDBC connectivity ( use the JDK in $ORACLE_HOME/jdk/bin ) 
[oracle@grac41 JDBC]$  javac version.java  ( Download  : version.java )  

[oracle@grac41 JDBC]$ java version jdbc:oracle:thin:@grac4-scan.grid4.example.com:1521/grac4 scott tiger
Locale: United States English
Driver Name             : Oracle JDBC driver
Driver Version          : 11.2.0.3.0
Driver Major Version    : 11
Driver Minor Version    : 2
Database Product Name   : Oracle
Database Product Version: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
JVM version             : 1.7.0_51
JVM vendor              : Oracle Corporation
JDK version             : 24.45-b08
CLASSPATH               :     /u01/app/oracle/product/11204/racdb/jdbc/lib/ojdbc6.jar:/home/oracle/RAC/JDBC/ucp.jar:.
LD_LIBRARY_PATH         :     /usr/java/packages/lib/amd64:/usr/lib64:/lib64:/lib:/usr/lib

 ..connected 
Hello JDBC: 03-DEC-14 from database GRAC4
[oracle@wls1 JDBC]$ ls /u01/app/oracle/product/fmw12cr3/oracle_common/modules/oracle.jdbc_12.1.0/
aqapi.jar  ojdbc6dms.jar  ojdbc6_g.jar  ojdbc6.jar  ojdbc7dms.jar  ojdbc7_g.jar  ojdbc7.jar
[oracle@wls1 JDBC]$  export CLASSPATH=.:/u01/app/oracle/product/fmw12cr3/oracle_common/modules/oracle.jdbc_12.1.0/ojdbc6.jar
[oracle@wls1 JDBC]$ javac version.java
[oracle@wls1 JDBC]$  java version jdbc:oracle:thin:@grac4-scan.grid4.example.com:1521/grac4 scotyyt tiger
Locale: United States English
Driver Name             : Oracle JDBC driver
Driver Version          : 12.1.0.2.0
Driver Major Version    : 12
Driver Minor Version    : 1
Database Product Name   : Oracle
Database Product Version: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
JVM version             : 1.7.0_71
JVM vendor              : Oracle Corporation
JDK version             : 24.71-b01
CLASSPATH               :     .:/u01/app/oracle/product/fmw12cr3/oracle_common/modules/oracle.jdbc_12.1.0/ojdbc6.jar
LD_LIBRARY_PATH         :     /usr/java/packages/lib/amd64:/usr/lib64:/lib64:/lib:/usr/lib

 ..connected 

Hello JDBC: 03-DEC-14 from database GRAC4

Setup JDBC Connection Pool/Resource in our Glassfish Server

Connect to Glassfish Admin console : http://localhost:4848
 --> JDBC --> JDBC Connect pools -> PoolName : Grac4pool -> Resource Type javx.sql.XADataSource 

Copy ojdbc6.jar to the glassfish Webserver: 
[root@wls1 Desktop]#  cp /u01/app/oracle/product/fmw12cr3/oracle_common/modules/oracle.jdbc_12.1.0/ojdbc6.jar  /usr/local/glassfish-4.1/glassfish/lib
[root@wls1 Desktop]# chmod 644  /usr/local/glassfish-4.1/glassfish/lib/ojdbc6.jar
--> restart glassfish

XA Settings: 
Resource Type       :  javax.sql.XADataSource ( allows 2PC protocol )
Datasource Classname:  oracle.jdbc.xa.client.OracleXADataSource

Add. Properties:
portNumber    : 1521
databaseName  : grac4
datasourceName: OracleXADataSource
serverName    : grac4-scan.grid4.example.com  ( SCAN name )
user          : scott
password      : tiger 
url           : jdbc:oracle:thin:@grac4-scan.grid4.example.com:1521/grac4 

Now press ping button ( should get Ping succeeded message )

--> Either modify the default DataSource create as new DataSource or init the DS with pur java code 

- Modify DEFAULT Datasource to point to our grac4pool
  Service -> JDBC -> JDBC Resources -> jdbc/__default -> RC properties -> PoolName : Grac4pool 

  Modify your JAVA code  and change the default DS with annotations : 
     @Resource(name="jdbc/__default") 
   private DataSource ds;
  or 
    @Resource(name="java.comp/DefaultDataSource")
   private DataSource ds;

- Create an own JDBC Resource named jdbc/rac
     @Resource(name="jdbc/rac")
   private DataSource ds;
or
     @Resource(name=java.comp/rac")
   private DataSource ds;

- Manually  initialize the DataSource with pure JAVA coding and without Annotations
      ds = (DataSource) InitialContext.doLookup("jdbc/__default");
  or
      ds = (DataSource) InitialContext.doLookup("jdbc/rac");

How does it work ?
Code :
  @Resource(name="jdbc/rac")
   private DataSource ds;

Key Facts 
- Object variable DataSource ds will be initalized by using the @Resource Annotation from the container  
- This feature is called  Dependency Injection which is based on Reflection API 

Reference:

• Professional entwickeln mit Java EE7  [ Verlag : Galileo Computing ]