spring boot not fetching data from external oracle database configured

Question

I want to configure oracle database with spring boot2. I have developed the dao layer code and unit tested with embeded H2 database successfully. But now when i provide the datasource configuration details in applicaiton.yaml file, it is not able to retrieve the data from the external oracle database. No error is getting displayed.

Here is my configuration

pom.xml

<!-- https://mvnrepository.com/artifact/org.eclipse.persistence/eclipselink -->
        <dependency>
            <groupId>org.eclipse.persistence</groupId>
            <artifactId>eclipselink</artifactId>
            <version>2.5.0</version>
        </dependency>

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

        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <!-- <scope>runtime</scope> -->
        </dependency>

        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc</artifactId>
            <version>11.2.0.3</version>
            <scope>system</scope>
            <systemPath>${basedir}/src/main/webapp/WEB-INF/lib/ojdbc6.jar</systemPath>
        </dependency>

Service layer:

@Autowired
SampleActivityLogRepository repository;

//inside a method
Optional<List<SampleActivityLog>> listOfActivityForToken = repository.findLogByAccNoandTokenIDOrderByDate(userId, example.getKey());

Repository Layer:

@Repository
@Transactional
public interface SampleActivityLogRepository extends JpaRepository<SampleActivityLog, Long> {

    @Query("SELECT L FROM SampleActivityLog L WHERE L.accountId = ?1 AND L.userId = ?2 ORDER BY L.createdDate desc")
    Optional<List<SampleActivityLog>> findLogByAccNoandTokenIDOrderByDate(String accNo, String userId);
}

application.yaml:

spring:
  profiles: dev

logging:
  level:
    org.springframework: ERROR
    com.example.rest.Tokennotification: DEBUG

  datasource:
    url: jdbc:oracle:thin:@(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = example.com)(PORT = 1875))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = sit)))
    username: mvcschenam
    password: support$12ab
    driverClassName: oracle.jdbc.OracleDriver
  jpa:
    database-platform: org.hibernate.dialect.Oracle10gDialect
    database: H2
    #openInView: false
    show_sql: true
    generate-ddl: false

What I have tried:

I have removed the h2 dependency from the pom.xml. but it threw error no 'url' configured for datasource
I have tried to remove autoconfiguration by using @EnableAutoConfiguration(exclude={DataSourceAutoConfiguration.class}) But it threw error that SampleActivityLogRepository is only a interface .. need to implement. But i do not see much logic here.. as the same code worked when connected to boot integrated database

I need to connect to external database but i am not sure what i am missing. I do not want to change my code as i believe it is pretty neat and working as well. Please guide me what i can do here. Thanks in advance

score 0 · Accepted Answer · answered Mar 22 '19 at 12:03

Try change your yml to:

spring:
  profiles: dev

  logging:
    level:
      org.springframework: ERROR
      com.example.rest.Tokennotification: DEBUG

  datasource:
    url: jdbc:oracle:thin:@(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = example.com)(PORT = 1875))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = sit)))
    username: mvcschenam
    password: support$12ab
    driverClassName: oracle.jdbc.OracleDriver
  jpa:
    database-platform: org.hibernate.dialect.Oracle10gDialect
    database: H2
    #openInView: false
    show_sql: true
    generate-ddl: false

It seems that your datasource and jpa was below logging node not spring

spring boot not fetching data from external oracle database configured

1 Answers1