Using H2 Console in development with Spring Boot then NOT when deployed to Pivotal Cloud Foundry

Frequently when developing Spring based applications, I will use the H2 in memory database during your development process. H2 ships with a web based database console, which you can use while your application is under development. It is a convenient way to view the tables created by Hibernate and run queries against the in memory database. In this post I show what is required to set this up as well as what it means to then deploy your Spring Boot applications to Pivotal Cloud Foundry and rely on a database service and hence your application becomes cloud aware.

1. First ensure you have included the H2 maven dependency as shown below. I also use DevTools BUT thats not needed to enable the H2 web console

  
  <dependency>
   <groupId>com.h2database</groupId>
   <artifactId>h2</artifactId>
  </dependency>

2. Then create an specific application.yml file while in development mode only and enable the H2 web console. Using the default name "application.yml"will ensure while your in DEV mode it will use that file. Notice how I give the database a name rather then use the default and also specify a datasource you don't need to go to that effort BUT to me it's good practice to define a datasource because it is what you will do for an application itself when not in DEV mode.

application.yml

server:
  error:
    whitelabel:
      enabled: false

spring:
  h2:
    console:
      enabled: true
  jpa:
    hibernate:
      ddl-auto: create
  datasource:
    url: jdbc:h2:mem:apples;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE
    username: sa
    password:
    driver-class-name: org.h2.Driver
    platform: h2

3. Run your spring boot application

....
2016-09-22 21:30:26.771  INFO 18929 --- [  restartedMain] s.b.c.e.t.TomcatEmbeddedServletContainer : Tomcat started on port(s): 8080 (http)
2016-09-22 21:30:26.778  INFO 18929 --- [  restartedMain] gBootJpaBootstrapEmployeeDemoApplication : Started SpringBootJpaBootstrapEmployeeDemoApplication in 6.021 seconds (JVM running for 6.553)

4. Connect to the H2 we console as follows

http://localhost:8080/h2-console/

The JDBC Url now becomes what you set in the dialog above, inshort the DB name I set was "apples"

When it comes to deployment in Pivotal Cloud Foundry (PCF) you most likely will not want to use H2 and instead bind to a database service like MySQL for example. To do that we would alter our project as follows.

5. Add the following maven dependancies. I add MySQL dependency and you can leave H2 as it will use that if it doesn't finda MySQL service instance to bind to. I also add "spring-boot-starter-cloud-connectors" as it's this which automatically creates and configures a DataSource which injects the service details at Runtime for me.

  
  <dependency>
   <groupId>mysql</groupId>
   <artifactId>mysql-connector-java</artifactId>
  </dependency>
  <dependency>
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-starter-cloud-connectors</artifactId>
  </dependency>

6. Add a specific cloud application YML file named "application-cloud.yml" as follows. I have left out a datasource and Spring Boot will create that for me when bound to the database service, BUT generally I always set the datasource with the correct properties required to meet my application requirements.

application-cloud.yml

spring:
  jpa:
    hibernate:
      ddl-auto: create

server:
  error:
    whitelabel:
      enabled: false

7. When creating a manifest.yml file to deploy your application to PCF all you need to do is add a MySQL database service and specify the active profile as CLOUD as shown below which will ensure we use the "application-cloud.yml" file we created above.

manifest.yml

---
applications:
- name: springboot-bootstrap-employee
  memory: 512M
  instances: 1
  random-route: true
  timeout: 180
  path: ./target/springbootjpabootstrapemployeedemo-0.0.1-SNAPSHOT.jar
  services:
    - pas-mysql
  env:
    JAVA_OPTS: -Djava.security.egd=file:///dev/urando
    SPRING_PROFILES_ACTIVE: cloud

The project in IntelliJ is as follows

GitHub URL as follows:

https://github.com/papicella/SpringBootJPABootstrapEmployeeDemo

Creating a Pivotal GemFireXD Data Source Connection from IntelliJ IDEA 13.x

In order to create a Pivotal GemFireXD Data Source Connection from IntelliJ 13.x , follow the steps below. You will need to define a GemFireXD driver , prior to creating the Data Source itself.

1. Bring up the Databases panel.

2. Define a GemFireXD Driver as follows

3. Once defined select it by using the following options. Your using the Driver you created at #2 above

+ -> Data Source -> com.pivotal.gemfirexd.jdbc.ClientDriver 

4. Create a Connection as shown below. You would need to having a running GemFireXD cluster at this point in order to connect.

5.  Once connected you can browse objects as shown below.

6. Finally we can run DML/DDL directly from IntelliJ as shown below.

OC4J 10.1.3.x - Accessing a Data Source remotely without oc4jadmin user

I previously showed how one would remotely access JNDI object's which include EJB's , Data Sources etc as shown in this post below.

http://theblasfrompas.blogspot.com/2008/07/enable-remote-clients-to-access-oas.html

Typically internally I use oc4jadmin who has access to everything and of course it has no issues. However in the real world 2 things are just about certain.

1. A Data Source would most likely be created at the container level for applications to share allthough it's not uncommon for them to be created at the application level.

2. The OAS administrator would almost certainly provide a separate user for JNDI access.

With such a setup the user created won't be able to access container specific JNDI objects such as data sources as most likely the OAS administrator won't make the user given part of the "oc4j-administrators" group. In this case the same principals as the blog entry apply however you won't find a container orion-application.xml.

So assuming you have a Data Source created in the HOME container you would need to alter the file below and then stop/start the instance to allow users to access container specific JNDI objects such as a Data Source. This demo assumes your user is part of the group called "users" as per the blog entry above.

$ORACLE_HOME/j2ee/home/config/application.xml

<namespace-access>
  <read-access>
    <namespace-resource root="">
      <security-role-mapping>
              <group name="oc4j-administrators" />
              <group name="ascontrol_admin" />
              <group name="users" />
      </security-role-mapping>
    </namespace-resource>
  </read-access>
  <write-access>
      <namespace-resource root="">
        <security-role-mapping>
                <group name="oc4j-administrators" />
                <group name="ascontrol_admin" />
                <group name="users" />
        </security-role-mapping>
      </namespace-resource>
  </write-access>
</namespace-access>

Accessing a Data Source Remotely in FMW 11g (11.1.1.2.0) from JDeveloper 11g (11.1.1.2.0)

I needed to access a data source remotely on FMW 11g server from JDeveloper 11g. I found that to do this I needed to follow these steps.

Note: This was done using 11.1.1.2 which is the latest version of FMW 11g and JDeveloper 11g

1. Edit setDomainEnv script to ensure you set this property to true. By default it's false.

WLS_JDBC_REMOTE_ENABLED="-Dweblogic.jdbc.remoteEnabled=true"

2. Re-start your server to pick up the change done at step #1 above.

3. In your project add the following libraries to allow remote access and the required JDBC driver library.

• Weblogic 10.3 Remote-Client
• Oracle JDBC

4. From JDeveloper access your data source remotely with code as follows making sure you use your connection details and the correct JNDI name for the data source.

package pas.au.remote.wls11g;

import java.sql.Connection;

import java.sql.SQLException;

import java.util.Date;
import java.util.Hashtable;
import java.util.logging.Level;
import java.util.logging.Logger;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;

import javax.sql.DataSource;

public class RemoteDataSourceAccess
{
 private Logger logger = Logger.getLogger(this.getClass().getSimpleName());
 public RemoteDataSourceAccess()
 {
 }

 public void run ()
 {
   logger.log(Level.INFO, "Started RemoteDataSourceAccess at " + new Date());
  
   InitialContext ctx = null;

   try
   {
     ctx = getInitialContext();
     DataSource ds = (DataSource) ctx.lookup("jdbc/scottDS");
     Connection conn = (Connection) ds.getConnection();
     logger.log(Level.INFO, "Got connection : " + conn);
    
     logger.log(Level.INFO, "Auto Commit = " + conn.getAutoCommit());
   }
   catch (NamingException e)
   {
     logger.log(Level.SEVERE, "Error occurred", e);
     System.exit(1);
   }
   catch (SQLException e)
   {
     logger.log(Level.SEVERE, "SQLException occurred", e);
     System.exit(1);
   }

   logger.log(Level.INFO, "Ended RemoteDataSourceAccess at " + new Date());
 }

 public static InitialContext getInitialContext() throws NamingException
 {
   String JNDI_FACTORY="weblogic.jndi.WLInitialContextFactory";
   String url = "t3://wayne-p2.au.oracle.com:7003";
   String username = "weblogic";
   String password = "welcome1";
  
   Hashtable<String,String> env = new Hashtable<String,String>();
   env.put(Context.SECURITY_PRINCIPAL, username);
   env.put(Context.SECURITY_CREDENTIALS, password);
   env.put(Context.INITIAL_CONTEXT_FACTORY, JNDI_FACTORY);
   env.put(Context.PROVIDER_URL, url);
   return new InitialContext(env);
 }
   
 public static void main(String[] args)
 {
  
   RemoteDataSourceAccess test = new RemoteDataSourceAccess();
   test.run();
 }
}

5. Output as follows.

25/02/2010 9:22:56 AM pas.au.remote.wls11g.RemoteDataSourceAccess run
INFO: Started RemoteDataSourceAccess at Thu Feb 25 09:22:56 EST 2010
25/02/2010 9:23:03 AM pas.au.remote.wls11g.RemoteDataSourceAccess run
INFO: Got connection : weblogic.jdbc.rmi.SerialConnection_weblogic_jdbc_rmi_internal_ConnectionImpl_weblogic_jdbc_wrapper_PoolConnection_oracle_jdbc_driver_T4CConnection_1032_WLStub@1
25/02/2010 9:23:03 AM pas.au.remote.wls11g.RemoteDataSourceAccess run
INFO: Auto Commit = true
25/02/2010 9:23:03 AM pas.au.remote.wls11g.RemoteDataSourceAccess run
INFO: Ended RemoteDataSourceAccess at Thu Feb 25 09:23:03 EST 2010

OAS 10.1.3.x : SSL With Oracle JDBC Thin Driver

The following how-to demonstrates, creating a self signed oracle wallet for the server and client, configuring the database to use TCPS and finally create a data source on OC4J 10.1.3.x.

Create the wallets

For an example on how to create the server and client keys/certificates see the following metalink note. The examples are created using orapki utility which creates a standard PKCS12 format to store X.509 certificates and private keys.

Note 762286.1 - End To End Examples of using SSL With Oracle's JDBC THIN Driver

Setup OC4J to use JDBC/THIN with SSL

1. Create a java class as follows, and package it up in a JAR file called "JDBCStartup.jar". This startup class file enables Oracle PKI provider dynamically.

/**
*
* add the following lines to your server.xml
<init-library path="<path_to_JDBCSSLSetup.jar>" />
<startup-classes>
<startup-class classname="JDBCSSLSetup" failure-is-fatal="false">
<execution-order>0</execution-order>
</startup-class>
</startup-classes>

*/

import javax.naming.*;
import java.util.*;
import java.security.Security;
import oracle.security.pki.OraclePKIProvider;
import oracle.j2ee.server.OC4JStartup;

/**
* JDBC over SSL setup startup class for OC4J
*/

public class JDBCSSLSetup implements OC4JStartup
{
/**
* Public, no-argument constructor: required by the Oracle startup class spec
*/
public JDBCSSLSetup()
{
}

public String preDeploy(Hashtable args, Context context) throws Exception
{
// instantiate OraclePKIProvider and put it into provider slot #3
Security.insertProviderAt(new OraclePKIProvider(), 3);
System.out.println("JDBCSSLSetup startup class: OraclePKIProvider has been successfully instantiated");

return "ok";
}

public String postDeploy(Hashtable args, Context context) throws Exception
{
return null;
}
} 

2. Place the JAR file into $ORACLE_HOME/j2ee/{container-name}/applib directory.

3. Edit $ORACLE_HOME/j2ee/{container-name}/config/server.xml as follows to add the startup class to the container.

<init-library path="../applib/JDBCStartup.jar" />
<startup-classes>
<startup-class classname="JDBCSSLSetup" failure-is-fatal="true" >
<execution-order>0</execution-order>
</startup-class>
</startup-classes>

4. Restart the container as shown below.

> opmnctl stopproc process-type=pas

> opmnctl startproc process-type=pas

5. OAS 10..1.3.x comes with JDBC driver 10.1.0.5 , you will need to use the latest 10g JDBC driver or 11g JDBC driver, to switch to that driver follow the following metalink how to document. You should add the required JAR files below to the shared library along with the JDBC JAR file.

Note 420303.1 - How to Use The Latest Thin JDBC Driver Across All Applications For a 10.1.3.x OAS Container

If you are using 11g database you will also need these 2 JAR files added to the shared library.

• $ORACLE_HOME/jlib/osdt_cert.jar
• $ORACLE_HOME/jlib/osdt_core.jar

If you are using 10g database you will also need this JAR file added to the shared library.

• $ORACLE_HOME/jlib/ojpse.jar

6. You should be using the latest JDBC driver as per your $ORACLE_HOME/j2ee/{container-name}/config/system-application.xml. In this example we are using the 11.1.0.7 JDBC driver.

<import-shared-library name="oracle.jdbc" min-version="11.1.0.7"/>

7. Now re-start the container to ensure it picks up the latest JDBC driver which should have been done in the note above.

> opmnctl stopproc process-type=pas

> opmnctl startproc process-type=pas

8. Now you can go ahead and create a data source which is using TCPS either through asconsole or manually editing $ORACLE_HOME/j2ee/{container-name}/config/data-sources.xml

Here we will manually edit data-sources.xml and add a managed data source and connection pool to support our SSL setup. We must use the factory class "oracle.jdbc.OracleDriver" as we need to pass properties to the driver.

<managed-data-source
connection-pool-name="TCPSConnectionPool"
jndi-name="jdbc/jdbcSSLDS"
name="jdbc/jdbcSSLDS"/>

<connection-pool name="TCPSConnectionPool">
<connection-factory
factory-class="oracle.jdbc.OracleDriver"
user="scott"
password="tiger"
url="jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)
(HOST=beast.au.oracle.com)(PORT=2484))(CONNECT_DATA=(SERVICE_NAME=linux11g)))"
 commit-record-table-name="">
<property
 name="javax.net.ssl.trustStore" 
 value="/home/u01/app/oracle/product/1013AS_red/network/admin/wallets/client/ewallet.p12"/>
<property 
 name="javax.net.ssl.trustStoreType" 
 value="PKCS12"/>
<property 
 name="javax.net.ssl.trustStorePassword" 
 value="myclient123"/>
</connection-factory>
</connection-pool>

In the example above we connect to the database through TCPS for SSL with Encryption and Authentication.

Note: The connect string should include TCPS as shown above.

9. Now in this example we manually edited data-sources.xml so we must re-start the container once again to pick up those changes.

> opmnctl stopproc process-type=pas

> opmnctl startproc process-type=pas

10. Finally we can test our data source using asconsole Data Sources page as shown below.




More Information

For more information regarding SSL With Oracle JDBC Thin Driver see the following white paper

http://www.oracle.com/technology/tech/java/sqlj_jdbc/pdf/wp-oracle-jdbc_thin_ssl.pdf

Associate an IDE DB Connection to a Workspace for Web projects in JDeveloper 11g

In JDeveloper 10g you could easily deploy all database connections when running a web project in the embedded OC4J server thus allowing each connection to be represented as a data source at runtime. Turns out the same can be done in JDeveloper 11g and Weblogic Integrated server however the steps are a little different.

1. View -> Database Navigator
2. Drag the IDE connection you wish to use in your workspace. The end result is as follows for the Workspace "WebLogicStuff"





3. Now whenever you run any web project from WebLogicStuff workspace then the database connection "scott" will automatically be deployed as an application level data source.

4. In order to access the connection "scott" we should use a JNDI location as follows within our code.

java:comp/env/jdbc/scottDS

The format would be as follows, depending on the connection name.

java:comp/env/jdbc/{jdevide-connection-name}DS

Here is some java code we can then use to perform a JNDI lookup of the data source via a simple method which requires the location above to be passed to the method.

private Connection getConnection (String dataSourceLocation)
throws NamingException, SQLException
{
Connection conn = null;

// Get a context for the JNDI look up
Context ctx = new InitialContext();

// Look up a data source
javax.sql.DataSource ds
= (javax.sql.DataSource) ctx.lookup (dataSourceLocation);

// Create a connection object
conn = ds.getConnection();

return conn;
}

ClassCastException casting to OracleConnection from a Data Source Connection in OAS 10.1.3.x

I have previously blogged how to access a data source remotely using OAS 10.1.3.x as shown below.

http://theblasfrompas.blogspot.com/2007/07/access-data-source-from-oc4j-remotely.html

Normally I cast back to a java.sql.Connection but you can also cast to an OracleConnection, as shown below.

OracleConnection oraConnection = null;
InitialContext ic = new InitialContext(env);
DataSource ds = (DataSource) ic.lookup("jdbc/scottDS");
oraConnection = (OracleConnection) ds.getConnection();

When you do this you may get a runtime exception as follows:

Exception in thread "main" java.lang.ClassCastException: oracle_jdbc_driver_LogicalConnection_Proxy

The problem here is you must import the correct OracleConnection. The one you want to import is "oracle.jdbc.OracleConnection". You end up with the error above if you use "oracle.jdbc.driver.OracleConnection".

How to populate v$session.program from mid tier data source connections

I am constantly asked how to distinguish the different JDBC connections that come from the middle tier. His an example on how to do this OAS 10.1.3.x.

1. Create a data source as follows which is using the JDBC driver as the factory class (oracle.jdbc.driver.OracleDriver)

<managed-data-source
 connection-pool-name="ScottConnectionPool"
 jndi-name="jdbc/scottDS"
 name="jdbc/scottDS"/>
<connection-pool
  name="ScottConnectionPool"
  initial-limit="5"
  min-connections="5">
  <connection-factory
    factory-class="oracle.jdbc.driver.OracleDriver"
    user="scott"
    password="tiger"
    url="jdbc:oracle:thin:@//papicell-au2.au.oracle.com:1521/lnx102">
     <property name="v$session.program"
        value="OAS10132-apple-scottpool"/>
  </connection-factory>
</connection-pool>

2. In order to populate the PROGRAM column you need to set the property as shown below.

<property name="v$session.program" 
             value="OAS10132-apple-scottpool">

3. When the data source is started it will create 5 connections so we can then run the following SQL to see if it worked once the connection pool has been started.

set head on feedback on

set pages 999
set linesize 120

prompt
prompt SCOTT sessions

col machine format a25
col username format a15
col username format a8
col program format a25

select
username,
program,
status,
last_call_et seconds_since_active,
to_char(logon_time, 'dd-MON-yyyy HH24:MI:SS') "Logon"
from v$session
where username = 'SCOTT'
/


The result is as follows which clearly shows that the PROGRAM field has been populated by our mid tier data source:

Access a Data Source from OC4J remotely from a java client

I needed to access an data source in an OPMN managed OC4J instance remotely from a java client. My data source was in the 10.1.3.2 release of Oracle Application Server and my client was JDeveloper 10.1.3.2. Here is how I got this to work.

1. New Empty project in JDeveloper
2. Add the following libraries, which will ensure all the required jars needed are added to the classpath:

• Oracle JDBC
• JSP Runtime
• Apache Ant

3. Create a client as follows, the connection is made with a provider URL using opmn:ormi format as shown below:

package pas.test;

import java.sql.Connection;

import java.util.Hashtable;

import javax.naming.Context;
import javax.naming.InitialContext;

import javax.sql.DataSource;

public class AccessDataSource10132
{

 public static void main(String[] args)
   throws Exception
 {
   Connection conn = null;
   Hashtable env = new Hashtable();
   env.put(Context.INITIAL_CONTEXT_FACTORY,
       "oracle.j2ee.rmi.RMIInitialContextFactory");
   env.put(Context.SECURITY_PRINCIPAL, "oc4jadmin");
   env.put(Context.SECURITY_CREDENTIALS, "welcome1");
   env.put(Context.PROVIDER_URL,
     "opmn:ormi://sracanov-au2.au.oracle.com:6005:OC4J_FCF/default");

   InitialContext ic = new InitialContext(env);

   DataSource ds = (DataSource) ic.lookup("jdbc/scottDS");
   conn = ds.getConnection();
   System.out.println("Auto commit is -> " + conn.getAutoCommit());

   System.out.println("all done..");
 }
}

The output is as follows:

Auto commit is -> true
all done..