Problem while making oracle Data Source connection

[bablu singh]

I am making connection with Oracle DataSource and I am able connect with DB using first approach. But getting error using second approach. I want to use second approach. I want to make data connection in impl class.

First Approach

Properties file
spring.datasource.url=jdbc:oracle:thin:@X.X.X.X:X:X
spring.datasource.username=username
spring.datasource.password=password
spring.datasource.driver-class-name=oracle.jdbc.driver.OracleDriver

AppConfig.java
package com.college.config;
@Component
@Configuration
@ConfigurationProperties(prefix = "spring.datasource")
public class AppConfig {
	private String url;
	private String username;
	private String password;
	public AppConfig() {}
	getter/setter..
	@Bean
    public DataSource dataSource() throws SQLException {
        OracleDataSource oracleDS = new OracleDataSource();
        oracleDS.setUser(username);
        oracleDS.setPassword(password);
        oracleDS.setURL(url);
        oracleDS.setImplicitCachingEnabled(true);
        oracleDS.setFastConnectionFailoverEnabled(true);
        return oracleDS;
    }
}

package com.college.DAV;
@EnableAutoConfiguration(exclude={DataSourceAutoConfiguration.class})
@SpringBootApplication(scanBasePackages={"com.college"})
public class DavApplication extends SpringBootServletInitializer {
	protected SpringApplicationBuilder configure(SpringApplicationBuilder builder) {
    	return builder.sources(DavApplication.class);
    }
}

package com.college.service;
@RestController
@RequestMapping("/")
public class ZoneService {
	@Autowired
	private AppConfig appConfig;
	@RequestMapping("/greetMe")
	public String greetMe(){
		return "Hello!! Application is running!!";
	}
	@RequestMapping("/listZones")
	public String listZones() throws SQLException {
		ZoneDAO zoneDAO = new ZoneDAOImpl();
		zoneDAO.setDataSource(appConfig.dataSource());
		return zoneDAO.listZones();
	}
}

package com.college.dao.impl;
@Repository
@ComponentScan
public class ZoneDAOImpl implements ZoneDAO {
	private JdbcTemplate jdbcTemplate;
    public ZoneDAOImpl() {} 
   	@Override
	public void setDataSource(DataSource dataSource) {
		this.jdbcTemplate = new JdbcTemplate(dataSource);
	}
	@Override
	public String listZones() throws SQLException {
		System.out.println("In listZones method");
		String sql = "SELECT ZONE,TEAM FROM LOCATION WHERE ZONE = 'DEF' AND TEAM LIKE '%LG' AND SITE LIKE 'Y%' ";
		List<Zone> zones = jdbcTemplate.query(sql, new ZoneMapper());
		for(Zone z:zones) {
			System.out.println(z);
		}	
		return "List Zones Called";
	}
}

Second Approach:

package com.college.service;
@RestController
@RequestMapping("/")
public class ZoneService {
	@RequestMapping("/greetMe")
	public String greetMe(){
		return "Hello!! Application is running!!";
	}
	@RequestMapping("/listZones")
	public String listZones() throws SQLException {
		ZoneDAO zoneDAO = new ZoneDAOImpl();
		return zoneDAO.listZones();
	}
}

package com.college.dao.impl;
@Repository
@ComponentScan
public class ZoneDAOImpl implements ZoneDAO {
	@Autowired
	private AppConfig appConfig;
	private JdbcTemplate jdbcTemplate;
    public ZoneDAOImpl() {}
	public void getJdbcTemplate() {
		try {
			this.jdbcTemplate = new JdbcTemplate(appConfig.dataSource());
		} catch(SQLException e) {
			System.out.println("Exception:"+e.getMessage());
		}
	}
	@Override
	public String listZones() throws SQLException {
		System.out.println("In listZones method");
		getJdbcTemplate();
		String sql = "SELECT ZONE,TEAM FROM LOCATION WHERE ZONE = 'OHN' AND TEAM LIKE '%LG' AND SITE LIKE 'Y%' ";
		List<Zone> zones = jdbcTemplate.query(sql, new ZoneMapper());
		for(Zone z:zones) {
			System.out.println(z);
		}
		return "List Zones Called";
	}
}

2017-06-14 18:10:20.572 ERROR 8592 --- [bio-8085-exec-1] o.s.boot.web.support.ErrorPageFilter     : Forwarding to error page from request [/listZones] due to exception [null]
java.lang.NullPointerException: null
        at com.college.dao.impl.ZoneDAOImpl.getJdbcTemplate(ZoneDAOImpl.java:26) ~[classes/:na]
        at com.college.dao.impl.ZoneDAOImpl.listZones(ZoneDAOImpl.java:34) ~[classes/:na]
        at com.college.service.ZoneService.listZones(ZoneService.java:12) ~[classes/:na]
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_101]
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_101]
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_101]
        at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_101]
        at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205) ~[spring-web-4.3.9.RELEASE.jar:4.3.9.RELEASE]
        at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:133) ~[spring-web-4.3.9.RELEASE.jar:4.3.9.RELEASE]
        at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:97) ~[spring-webmvc-4.3.9.RELEASE.jar:4.3.9.RELEASE]
        at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827) ~[spring-webmvc-4.3.9.RELEASE.jar:4.3.9.RELEASE]
        at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738) ~[spring-webmvc-4.3.9.RELEASE.jar:4.3.9.RELEASE]
        at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85) ~[spring-webmvc-4.3.9.RELEASE.jar:4.3.9.RELEASE]
        at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:967) ~[spring-webmvc-4.3.9.RELEASE.jar:4.3.9.RELEASE]
        at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:901) ~[spring-webmvc-4.3.9.RELEASE.jar:4.3.9.RELEASE]
        at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970) ~[spring-webmvc-4.3.9.RELEASE.jar:4.3.9.RELEASE]
        at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:861) ~[spring-webmvc-4.3.9.RELEASE.jar:4.3.9.RELEASE]
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:624) ~[servlet-api.jar:na]
        at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846) ~[spring-webmvc-4.3.9.RELEASE.jar:4.3.9.RELEASE]
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:731) ~[servlet-api.jar:na]
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303) [catalina.jar:7.0.73]
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) [catalina.jar:7.0.73]
        at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) ~[tomcat7-websocket.jar:7.0.73]
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) [catalina.jar:7.0.73]
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) [catalina.jar:7.0.73]
        at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:99) ~[spring-web-4.3.9.RELEASE.jar:4.3.9.RELEASE]
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [spring-web-4.3.9.RELEASE.jar:4.3.9.RELEASE]
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) [catalina.jar:7.0.73]
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) [catalina.jar:7.0.73]
        at org.springframework.web.filter.HttpPutFormContentFilter.doFilterInternal(HttpPutFormContentFilter.java:105) ~[spring-web-4.3.9.RELEASE.jar:4.3.9.RELEASE]
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [spring-web-4.3.9.RELEASE.jar:4.3.9.RELEASE]
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) [catalina.jar:7.0.73]
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) [catalina.jar:7.0.73]
        at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:81) ~[spring-web-4.3.9.RELEASE.jar:4.3.9.RELEASE]
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [spring-web-4.3.9.RELEASE.jar:4.3.9.RELEASE]
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) [catalina.jar:7.0.73]
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) [catalina.jar:7.0.73]
        at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:197) ~[spring-web-4.3.9.RELEASE.jar:4.3.9.RELEASE]
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [spring-web-4.3.9.RELEASE.jar:4.3.9.RELEASE]
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) [catalina.jar:7.0.73]
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) [catalina.jar:7.0.73]
        at org.springframework.boot.web.support.ErrorPageFilter.doFilter(ErrorPageFilter.java:115) [spring-boot-1.5.4.RELEASE.jar:1.5.4.RELEASE]
        at org.springframework.boot.web.support.ErrorPageFilter.access$000(ErrorPageFilter.java:59) [spring-boot-1.5.4.RELEASE.jar:1.5.4.RELEASE]
        at org.springframework.boot.web.support.ErrorPageFilter$1.doFilterInternal(ErrorPageFilter.java:90) [spring-boot-1.5.4.RELEASE.jar:1.5.4.RELEASE]
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [spring-web-4.3.9.RELEASE.jar:4.3.9.RELEASE]
        at org.springframework.boot.web.support.ErrorPageFilter.doFilter(ErrorPageFilter.java:108) [spring-boot-1.5.4.RELEASE.jar:1.5.4.RELEASE]
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) [catalina.jar:7.0.73]
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) [catalina.jar:7.0.73]
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:218) [catalina.jar:7.0.73]
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122) [catalina.jar:7.0.73]
        at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:505) [catalina.jar:7.0.73]
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:169) [catalina.jar:7.0.73]
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103) [catalina.jar:7.0.73]
        at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:958) [catalina.jar:7.0.73]
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116) [catalina.jar:7.0.73]
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:452) [catalina.jar:7.0.73]
        at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1087) [tomcat-coyote.jar:7.0.73]
        at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:637) [tomcat-coyote.jar:7.0.73]
        at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:316) [tomcat-coyote.jar:7.0.73]
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [na:1.8.0_101]
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [na:1.8.0_101]
        at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) [tomcat-coyote.jar:7.0.73]
        at java.lang.Thread.run(Thread.java:745) [na:1.8.0_101]

Could you please guide me. Thanks in advance.

[bablu singh]

Could anyone look into it?

[Mohammed Sardar.]

Hi I'm trying to help you but unable to trace the root cause just by seeing the above code as we also need to make very sure about all other  configurations. But I wish to suggest to debug the issue so that root cause can be found.

*. Make sure all the jars are in place.
*. Make sure your oracle connection can be established without any problem. You can use SQLDeveloper or some other tool to confirm.
*. Place a break point at one place from the stack trace pointed out code lines so that we can get to a point where NULL pointer exception can be thrown. With this program, I guess it's something to do with

getJdbcTemplate() {
       try {
             ..  }
           }

Please get back here if you get any clues by debugging.

[Mohammed Sardar.]

bablu singh wrote:

First Approach

Properties file
spring.datasource.url=jdbc:oracle:thin:@X.X.X.X:X:X
spring.datasource.username=username
spring.datasource.password=password

Please confirm with your connection URL has been specified completely like in the below format.  jdbc: oracle:thin:@localhost:9080:xe

[bablu singh]

Hi Mohammed, Thanks for looking into it.
As you suggested, all jars are in place, building using maven.
I checked the database connection with SQLDeveloper, its connecting properly.
Also, tried debugging and I am getting the error at line no 53. Please see the logs.
Connection URL is also valid and its in the jdbc: oracle:thin:@localhost:9080:xe format. Also, same is working using first approach, as I mentioned earlier.

 1. package com.college.dao.impl;
22. @Repository
23. @ComponentScan
24. public class ZoneDAOImpl implements ZoneDAO {
28.		private JdbcTemplate jdbcTemplate;
30.		public ZoneDAOImpl() {}
37.		public void getJdbcTemplate() {
39.			try {
40.				System.out.println("In getJdbcTemplate");
41.				this.jdbcTemplate = new JdbcTemplate(new AppConfig().dataSource());
42.			} catch(SQLException e) {
43.				e.printStackTrace();
44.			}
45.		}
47.		@Override
48.		public String listZones() throws SQLException {
49.			System.out.println("In listZones method");
50.			getJdbcTemplate();
51.			String sql = "SELECT ZONE,TEAM FROM LOCATION WHERE ZONE = 'OHN' AND TEAM LIKE '%LG' AND SITE LIKE 'Y%' ";
53.			List<Zone> zones = jdbcTemplate.query(sql, new ZoneMapper());
54.			for(Zone z:zones) {
55.				System.out.println(z);
56.			}
58.			return "List Zones Called";
59.		}
60. }

2017-07-06 11:11:49.325  INFO 8796 --- [bio-8085-exec-1] o.s.web.servlet.DispatcherServlet        : FrameworkServlet 'dispatcherServlet': initialization completed in 32 ms
In listZones method
In getJdbcTemplate
2017-07-06 11:11:49.404 ERROR 8796 --- [bio-8085-exec-1] o.s.boot.web.support.ErrorPageFilter     : Forwarding to error page from request [/listZones] due to exception [Could not get JDBC Connection; nested exception is java.sql.SQLException: Invalid Oracle URL specified: OracleDataSource.makeURL]
org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is java.sql.SQLException: Invalid Oracle URL specified: OracleDataSource.makeURL
        at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:80) ~[spring-jdbc-4.3.9.RELEASE.jar:4.3.9.RELEASE]
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:394) ~[spring-jdbc-4.3.9.RELEASE.jar:4.3.9.RELEASE]
        at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:474) ~[spring-jdbc-4.3.9.RELEASE.jar:4.3.9.RELEASE]
        at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:484) ~[spring-jdbc-4.3.9.RELEASE.jar:4.3.9.RELEASE]
        at com.college.dao.impl.ZoneDAOImpl.listZones(ZoneDAOImpl.java:53) ~[classes/:na]
        at com.college.service.ZoneService.listZones(ZoneService.java:31) ~[classes/:na]
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_101]
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_101]
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_101]
        at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_101]
        at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205) ~[spring-web-4.3.9.RELEASE.jar:4.3.9.RELEASE]
        at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:133) ~[spring-web-4.3.9.RELEASE.jar:4.3.9.RELEASE]
        at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:97) ~[spring-webmvc-4.3.9.RELEASE.jar:4.3.9.RELEASE]
        at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827) ~[spring-webmvc-4.3.9.RELEASE.jar:4.3.9.RELEASE]
        at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738) ~[spring-webmvc-4.3.9.RELEASE.jar:4.3.9.RELEASE]
        at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85) ~[spring-webmvc-4.3.9.RELEASE.jar:4.3.9.RELEASE]
        at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:967) ~[spring-webmvc-4.3.9.RELEASE.jar:4.3.9.RELEASE]
        at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:901) ~[spring-webmvc-4.3.9.RELEASE.jar:4.3.9.RELEASE]
        at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970) ~[spring-webmvc-4.3.9.RELEASE.jar:4.3.9.RELEASE]
        at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:861) ~[spring-webmvc-4.3.9.RELEASE.jar:4.3.9.RELEASE]
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:624) ~[servlet-api.jar:na]
        at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846) ~[spring-webmvc-4.3.9.RELEASE.jar:4.3.9.RELEASE]
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:731) ~[servlet-api.jar:na]
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303) [catalina.jar:7.0.73]
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) [catalina.jar:7.0.73]
        at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) ~[tomcat7-websocket.jar:7.0.73]
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) [catalina.jar:7.0.73]
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [na:1.8.0_101]
        at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) [tomcat-coyote.jar:7.0.73]
        at java.lang.Thread.run(Thread.java:745) [na:1.8.0_101]
Caused by: java.sql.SQLException: Invalid Oracle URL specified: OracleDataSource.makeURL
        at oracle.jdbc.pool.OracleDataSource.makeURL(OracleDataSource.java:1536) ~[ojdbc7-12.1.0.2.jar:na]
        at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:214) ~[ojdbc7-12.1.0.2.jar:na]
        at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:184) ~[ojdbc7-12.1.0.2.jar:na]
        at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:111) ~[spring-jdbc-4.3.9.RELEASE.jar:4.3.9.RELEASE]
        at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:77) ~[spring-jdbc-4.3.9.RELEASE.jar:4.3.9.RELEASE]
        ... 65 common frames omitted

[Tim Holloway]

What is your "Datasource" class? If it's the standard J2EE one, those are not usually constructed in application code. For webapp servers, the DataSources are usually from a Database Connection Pool constructed by the appserver itself. For a stand-alone Spring application, I'd normally be constructing the DataSource connection factory in the spring configuration file. Often, in fact, the URL and credentials are injected in via something like LDAP in my apps.

[bablu singh]

I am using oracle jdbc pool datasource - oracle.jdbc.pool.OracleDataSource (ojdbc7-12.1.0.2.jar) and it's working properly using first approach. This Spring based application is not having any configuration XML file, Its purely a java based configuration. URL and credentials are configured in properties file. It's not using LDAP for authentication.

import java.sql.SQLException;
import javax.sql.DataSource;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import oracle.jdbc.pool.OracleDataSource;
@Component
@Configuration
@ConfigurationProperties(prefix = "spring.datasource")
public class AppConfig {
    private String url;
    private String username;
    private String password;
    public AppConfig() {}
    getter/setter..
    @Bean
    public DataSource dataSource() throws SQLException {
        OracleDataSource oracleDS = new OracleDataSource();
        oracleDS.setUser(username);
        oracleDS.setPassword(password);
        oracleDS.setURL(url);
        oracleDS.setImplicitCachingEnabled(true);
        oracleDS.setFastConnectionFailoverEnabled(true);
        return oracleDS;
    }
}

[Tim Holloway]

You should display the JDBC URL actually being used just to make sure that the propery was read OK.

But your model URL appears to be missing the database ID. That is, you have:

jdbc:oracle:thin:@X.X.X.X:X:X

And you need something like:

jdbc:oracle:thin:@//dbhostname:1521/dbname

And if memory serves, "dbname" would be an Oracle TNS name, assuming that they are still using that concept. It was supposed to become something new back around Oracle 9, but last time anyone paid me to do Oracle, TNS names still worked.

Note that the "//" is NOT OPTIONAL. Slashes have very important meanings in URLs and URIs and the number of slashes denotes specific meaning as well.

[bablu singh]

You were right, its not pickin up the property file. URL value is coming as null, not only URL but username and password also. Then I tried:

public String listZones() throws SQLException {
	System.out.println("In listZones method");
	//getJdbcTemplate();
	
	AppConfig appConfig = new AppConfig();
	DataSource ds = appConfig.dataSource();
	System.out.println("DS Detail:"+ds.getConnection());
	
	/*getJdbcTemplate();
	String sql = "SELECT ORG_ZONE,ORG_TEAM FROM NG_REPORTING_01.LOCATION_HIERARCHY  WHERE ORG_ZONE = 'OHN' AND ORG_TEAM LIKE '%LG' AND ORG_SITE LIKE 'Y%' ";
	List<Zone> zones = jdbcTemplate.query(sql, new ZoneMapper());
	for(Zone z:zones) {
		System.out.println(z);
	}*/
	return "List Zones Called";
}

Its again giving me java.sql.SQLException: Invalid Oracle URL specified: OracleDataSource.makeURL error.

public class ZoneDAOImpl implements ZoneDAO {
	@Autowired
	private AppConfig appConfig;

	public String listZones() throws SQLException {
		System.out.println("In listZones method");
		//getJdbcTemplate();
		
		DataSource ds = appConfig.dataSource();
		System.out.println("DS Detail:"+ds.getConnection());
		
		/*getJdbcTemplate();
		String sql = "SELECT ORG_ZONE,ORG_TEAM FROM NG_REPORTING_01.LOCATION_HIERARCHY WHERE ORG_ZONE = 'OHN' AND ORG_TEAM LIKE '%LG' AND ORG_SITE LIKE 'Y%' ";
		List<Zone> zones = jdbcTemplate.query(sql, new ZoneMapper());
		for(Zone z:zones) {
			System.out.println(z);
		}*/
		return "List Zones Called";
	}
}

Its giving me null pointer exception. Its not initializing appConfig by Autowiring. Could you please point me out where I am wrong.

[Tim Holloway]

This seems more like a Spring problem than an Oracle problem. I haven't used the strategy you're attempting, since most of my Spring apps were standard webapps, where LDAP allowed me to keep properties external to the WAR and thereby be able to use the same WAR for both development and production without rebuilding it. The properties approach is more appropriate to things like Spring Boot.

At this point, all I can do is recommend that you review how this feature works. Also, by default I see that the properties file should be named "application.properties" and it looks like it should be located in the root of the application's classes directory.