1. Home
  2. Development
  3. Access Oracle Database in groovy

Access Oracle Database in groovy

Iñigo Boyano, modified 5 Years ago.
Access Oracle Database in groovy
Junior Member Posts: 96 Join Date: 2/4/14 Recent Posts
Hi, I have a Liferay DXP version, my system have a external database apart from my local database.

Both database are using oracle databse 12c.In mi lib/ext i have the ojdbc8.jar.

Those conecctions works properly in my modules and java developments but when I try to execute a sql in groovy, the console launch the following error when I execute the getConnection() method :

"java.sql.SQLException: No suitable driver found for jdbc:oracle:thin:'user'/'password'@'url':1521:'schema'" 

The sentence is:
Class.forName("oracle.jdbc.OracleDriver");
String dbURL = "jdbc:oracle:thin:'user'/'password'@'url':1521:'schema'";
Connection conn = DriverManager.getConnection(dbURL);

Can anyone help me?

Kind regards, 
Íñigo
thumbnail
David H Nebinger, modified 5 Years ago.
RE: Access Oracle Database in groovy
Liferay Legend Posts: 14933 Join Date: 9/2/06 Recent Posts
I wouldn't be opening connections like this manually, it's really low level and it is indicating that you're going to be sharing the production database credentials at least in some way that allows for hacker interception.

Since the portal is already configured for your datasource, I would opt to leverage it directly. Something along the lines of:

com.liferay.portal.dao.jdbc.spring.DataSourceFactoryBean dsFactoryBean = 
  new com.liferay.portal.dao.jdbc.spring.DataSourceFactoryBean();
dsFactoryBean.setPropertyPrefix("jdbc.default."); // use whatever prefix you need for your external connection
javax.sql.DataSource dataSource = dsFactoryBean.createInstance();
java.sql.Connection conn = dataSource.getConnection();
Hopefully you'll be using JNDI for your DB connection properties (so they don't leak usernames/passwords), but this technique will still get you the connection regardless.

Note that things get much simpler when you are using the Liferay default connection, then you can just grab it from InfrastructureUtil.getDataSource().
Iñigo Boyano, modified 5 Years ago.
RE: Access Oracle Database in groovy
Junior Member Posts: 96 Join Date: 2/4/14 Recent Posts
Thanks David, 
I've tried your solution but i receive the following error when I create the instance "dsFactoryBean.createInstance();"
com.zaxxer.hikari.HikariDataSource cannot be found by com.liferay.server.admin.web_1.0.42
thumbnail
David H Nebinger, modified 5 Years ago.
RE: Access Oracle Database in groovy
Liferay Legend Posts: 14933 Join Date: 9/2/06 Recent Posts
Yeah, it's trying to create a data source wrapped by the hikari connection pool; since you're just creating the bean, it won't have the portal's class loader wrapping the call...

I think if you wrap the call to dsFactoryBean.createInstance() with a temporary setting of the thread context class loader to the PortalClassLoaderUtil's getClassLoader() (don't forget to restore it after creating the datasource), you will likely have the correct context to create the instance.