Introduction

Here I am going to show you how to connect to Oracle data source using JDBC LDAP URL. I am going to create multiple data sources for connecting to multiple Oracle databases using Spring Boot framework. I am going to use Spring JdbcTemplate to query the database.

Why do you want to connect to multiple databases?

You may have a requirement where you need to connect to two different databases for migration purpose, or you may want to compare your dev environment’s data with prod environment’s data then you need to connect to your dev and prod databases. And best would be for making comparison between two environments’ data, you can build an application which will automate your comparison process and send a notification mail to the intended users with the status of the comparison.

Related Posts:

• Multiple Data Sources In Spring Boot
• Multiple Data Sources In Spring

Prerequisites

Java 1.8+ (11 – 16), Spring Boot 2.3.2+ (2.3.2 – 2.6.4), Gradle 5.6.1+, Maven 3.8.2, Oracle 12c

Project Setup

You can create gradle or maven based project in your favorite IDE or tool.

For maven based project you can use the following pom.xml file:

<?xml version="1.0" encoding="UTF-8"?>

<project xmlns="http://maven.apache.org/POM/4.0.0"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>

	<groupId>com.roytuts</groupId>
	<artifactId>spring-datasource-oracle-ldap-url</artifactId>
	<version>0.0.1-SNAPSHOT</version>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<maven.compiler.source>11</maven.compiler.source>
		<maven.compiler.target>11</maven.compiler.target>
	</properties>

	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.6.4</version>
	</parent>

	<repositories>
		<repository>
			<id>hand-china-repo</id>
			<name>HandChinaRepo</name>
			<url>http://nexus.saas.hand-china.com/content/repositories/rdc/</url>
		</repository>
	</repositories>

	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter</artifactId>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-jdbc</artifactId>
		</dependency>

		<dependency>
			<groupId>com.oracle</groupId>
			<artifactId>ojdbc7</artifactId>
			<version>12.1.0.2</version>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>
</project>

In the above pom.xml file I have configured repository from where the ojdbc7 jar will be downloaded. THis ojdbc7 jar is not available in the maven central and it could be found in the repository – http://nexus.saas.hand-china.com/content/repositories/rdc/.

For gradle based project you can use the following build.gradle script:

buildscript {
	ext {
		springBootVersion = '2.3.2.RELEASE'
	}
    repositories {
        mavenCentral()
    }
    dependencies {
        classpath("org.springframework.boot:spring-boot-gradle-plugin:${springBootVersion}")
    }
}

apply plugin: 'java'
apply plugin: 'org.springframework.boot'

sourceCompatibility = 1.8
targetCompatibility = 1.8

repositories {
    mavenCentral()
	maven {
		url 'http://nexus.saas.hand-china.com/content/repositories/rdc/'
	}
}

dependencies {
	compile/implementation("org.springframework.boot:spring-boot-starter-web:${springBootVersion}")
	compile/implementation("org.springframework.boot:spring-boot-starter-jdbc:${springBootVersion}")
	compile/implementation 'com.oracle:jdbc7:12.1.0.2'
}

Application Properties

The following application.properties file under src/main/resources folder has two LDAP JDBC URLs for Oracle database. The one is for the development database and the other one is for the production database.

db.dev.url=jdbc:oracle:thin:@ldap:<IP_address or URL>/db_dev,cn=OracleContext,DC=oracle,dc=support,dc=com
db.dev.usr=dev_user
db.dev.pwd=dev_pwd

db.prod.url=jdbc:oracle:thin:@ldap:<IP_address or URL>/db_prod,cn=OracleContext,DC=oracle,dc=support,dc=com
db.prod.usr=prod_user
db.prod.pwd=prod_pwd

DataSource Configurations

Here I am configuring multiple datasources, i.e., I have created two data sources – one is for dev environment and another one is for prod environment. You can also define as many data sources as you require but remember you must make one of them as a primary data source (using @Primary annotation) in your Spring Boot application otherwise your application will throw exception during start up.

I have also created two JdbcTemplate beans for two data sources.

package com.roytuts.spring.datasource.oracle.ldap.url.config;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.env.Environment;
import org.springframework.jdbc.core.JdbcTemplate;

import oracle.jdbc.pool.OracleDataSource;

@Configuration
public class DataSourceConfig {

	@Autowired
	private Environment env;

	@Primary
	@Bean(name = "devDataSource")
	public DataSource devDataSource() {
		try {
			OracleDataSource dataSource = new OracleDataSource();
			dataSource.setUser(env.getProperty("db.dev.usr"));
			dataSource.setPassword(env.getProperty("db.dev.pwd"));
			dataSource.setURL(env.getProperty("db.dev.url"));

			return dataSource;
		} catch (Exception e) {
		}

		return null;
	}

	@Bean(name = "prodDataSource")
	public DataSource prodDataSource() {
		try {
			OracleDataSource dataSource = new OracleDataSource();
			dataSource.setUser(env.getProperty("db.prod.usr"));
			dataSource.setPassword(env.getProperty("db.prod.pwd"));
			dataSource.setURL(env.getProperty("db.prod.url"));

			return dataSource;
		} catch (Exception e) {
		}

		return null;
	}

	@Bean(name = "devJdbcTemplate")
	public JdbcTemplate devJdbcTemplate() {
		return new JdbcTemplate(devDataSource());
	}

	@Bean(name = "prodJdbcTemplate")
	public JdbcTemplate prodJdbcTemplate() {
		return new JdbcTemplate(prodDataSource());
	}

}

DAO Class

Here the DAO class that fetches data from database. I have marked this class with @Repository annotation to indicate this class as a Spring repository.

package com.roytuts.spring.datasource.oracle.ldap.url.dao;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

@Repository
public class RecordDao {

	@Autowired
	@Qualifier("devJdbcTemplate")
	private JdbcTemplate devJdbcTemplate;

	@Autowired
	@Qualifier("prodJdbcTemplate")
	private JdbcTemplate prodJdbcTemplate;

	public Object devRecord() {
		Object obj = devJdbcTemplate.queryForObject("sql", Object.class);

		return obj;
	}

	public Object prodRecord() {
		Object obj = prodJdbcTemplate.queryForObject("sql", Object.class);

		return obj;
	}

}

I have auto-wired two instances of JdbcTemplate and specified @Qualifier annotation to segregate them for dev and prod environments, respectively.

Now you can simply use the appropriate JdbcTemplate instance or even you can use the appropriate data source from Config class to query your database.

You can create appropriate row mapper class to map your model class with respective columns of the database table.

Source Code

Download