Sunday, November 7, 2021

[FIXED] JDBC from Camel and Spring Boot to MS SQL Server

November 07, 2021 apache-camel, jdbc, maven, spring-boot, sql-server

Issue

I'm trying to access SQL Server from Camel with Spring Boot, but I'm getting the error "Field dataSource in com.bw.ReportRouter required a bean of type 'org.springframework.jdbc.datasource.AbstractDataSource' that could not be found."

I'm using a few tutorials to try to figure this out, mainly this one: https://www.javainuse.com/camel/camel_jdbc

If I understand this correctly, Spring Boot should configure this bean automatically.

Here is my route. I'm just trying to make a simple query to establish the connection:

package com.bw;

import org.apache.camel.builder.RouteBuilder;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.datasource.AbstractDataSource;
import org.springframework.stereotype.Component;
import javax.sql.DataSource;

@Component
public class ReportRouter extends RouteBuilder {
    
    @Autowired
    AbstractDataSource dataSource;

    public DataSource getDataSource() {
        return dataSource;
    }

    public void setDataSource(AbstractDataSource dataSource) {
        this.dataSource = dataSource;
        }

    @Override
    public void configure() {        
        from("timer:myTimer?repeatCount=1")
        .setBody(simple("select top 10 * from eai.Audit_General"))
        .log("SQL: ${body}")
        .to("jdbc:dataSource")
        .log("Out: ${body}");
    }

}

Here is application.properties

#spring.datasource.url=jdbc:sqlserver://sqlsrv\\sqlexpress;databaseName=customer
spring.datasource.url=jdbc:sqlserver://my-server.database.windows.net;databaseName=my-db
spring.datasource.username=user
spring.datasource.password=password
spring.datasource.initialization-mode=always

I think this is OK, but here is my pom.

<?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/maven-v4_0_0.xsd">

  <modelVersion>4.0.0</modelVersion>

  <groupId>com.bw</groupId>
  <artifactId>CrowdTwist-Reporting</artifactId>
  <packaging>jar</packaging>
  <version>1.0-SNAPSHOT</version>

  <name>A Camel Spring Boot Route</name>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
    <spring.boot-version>2.5.3</spring.boot-version>
    <surefire.plugin.version>3.0.0-M4</surefire.plugin.version>
    <camel-version>3.11.1</camel-version>
  </properties>

  <dependencyManagement>
    <dependencies>
      <!-- Spring Boot BOM -->
      <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-dependencies</artifactId>
        <version>${spring.boot-version}</version>
        <type>pom</type>
        <scope>import</scope>
      </dependency>
      <!-- Camel BOM -->
      <dependency>
        <groupId>org.apache.camel.springboot</groupId>
        <artifactId>camel-spring-boot-dependencies</artifactId>
        <version>${camel-version}</version>
        <type>pom</type>
        <scope>import</scope>
      </dependency>
    </dependencies>
  </dependencyManagement>

  <dependencies>

    <!-- Spring Boot -->
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-web</artifactId>
      <exclusions>
        <exclusion>
          <groupId>org.springframework.boot</groupId>
          <artifactId>spring-boot-starter-tomcat</artifactId>
        </exclusion>
      </exclusions>
    </dependency>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-undertow</artifactId>
    </dependency>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-actuator</artifactId>
    </dependency>

    <!-- Camel -->
    <dependency>
      <groupId>org.apache.camel.springboot</groupId>
      <artifactId>camel-spring-boot-starter</artifactId>
    </dependency>
    <dependency>
      <groupId>org.apache.camel.springboot</groupId>
      <artifactId>camel-stream-starter</artifactId>
    </dependency>

    <!-- Test -->
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-test</artifactId>
      <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>org.apache.camel</groupId>
      <artifactId>camel-test-spring-junit5</artifactId>
      <scope>test</scope>
    </dependency>
    
    <!-- Additions -->
    
    <!-- For receiving JMS messages from Artemis -->
    <dependency>
        <groupId>org.apache.activemq</groupId>
        <artifactId>artemis-jms-client</artifactId>
    </dependency>
    <dependency>
        <groupId>org.apache.camel.springboot</groupId>
        <artifactId>camel-jms-starter</artifactId>
    </dependency>
    <dependency>
       <groupId>org.messaginghub</groupId>
       <artifactId>pooled-jms</artifactId>
    </dependency>
    <dependency>
        <groupId>javax.json</groupId>
        <artifactId>javax.json-api</artifactId>
    </dependency>
    
    <!-- Model Object Translation -->
    <dependency>
        <groupId>org.apache.camel</groupId>
        <artifactId>camel-jackson</artifactId>
        <version>${camel-version}</version>
    </dependency>
    
    <!-- Working with JSON -->
    <dependency>
      <groupId>org.apache.camel</groupId>
      <artifactId>camel-jsonpath</artifactId>
      <version>${camel-version}</version>
    </dependency>
    
    <!-- Database Access -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>
    <dependency>
        <groupId>org.apache.camel</groupId>
        <artifactId>camel-jdbc</artifactId>
        <version>${camel-version}</version>
    </dependency>
    <dependency>
        <groupId>com.microsoft.sqlserver</groupId>
        <artifactId>mssql-jdbc</artifactId>
        <scope>runtime</scope>
    </dependency>
    
  </dependencies>

  <build>
    <plugins>
      <plugin>
        <groupId>org.apache.maven.plugins</groupId>
        <artifactId>maven-compiler-plugin</artifactId>
        <version>3.8.1</version>
        <configuration>
          <source>1.8</source>
          <target>1.8</target>
        </configuration>
      </plugin>
      <plugin>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-maven-plugin</artifactId>
        <version>${spring.boot-version}</version>
        <executions>
          <execution>
            <goals>
              <goal>repackage</goal>
            </goals>
          </execution>
        </executions>
      </plugin>
      <plugin>
        <groupId>org.apache.maven.plugins</groupId>
        <artifactId>maven-surefire-plugin</artifactId>
        <version>${surefire.plugin.version}</version>
      </plugin>
    </plugins>
  </build>

</project>

There isn't much else to show. I generated the project from a Maven archetype: mvn archetype:generate -DarchetypeGroupId=org.apache.camel.archetypes -DarchetypeArtifactId=camel-archetype-spring-boot -DarchetypeVersion=3.11.1

Here is the only other piece, the main process:

package com.bw;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

    @SpringBootApplication
    public class MySpringBootApplication {
    
        /**
         * A main method to start this application.
         */
        public static void main(String[] args) {
            SpringApplication.run(MySpringBootApplication.class, args);
        }
    
    }

Solution

Here is a working example.

package com.bw;

import org.apache.camel.builder.RouteBuilder;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.datasource.AbstractDataSource;
import org.springframework.stereotype.Component;
import javax.sql.DataSource;

@Component
public class ReportRouter extends RouteBuilder {
    
    @Autowired
    DataSource dataSource;

    public DataSource getDataSource() {
        return dataSource;
    }

    public void setDataSource(AbstractDataSource dataSource) {
        this.dataSource = dataSource;
        }

    @Override
    public void configure() {        
        from("timer:myTimer?repeatCount=1")
        .setBody(simple("select top 10 * from eai.Audit_General"))
        .log("SQL: ${body}")
        .to("jdbc:dataSource")
        .log("Out: ${body}");
    }

}

application.properties

spring.datasource.url=jdbc:sqlserver://my-server.database.windows.net;databaseName=my-db
spring.datasource.username=user
spring.datasource.password=password
spring.datasource.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver

pom

<?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/maven-v4_0_0.xsd">

  <modelVersion>4.0.0</modelVersion>

  <groupId>com.bw</groupId>
  <artifactId>CrowdTwist-Reporting</artifactId>
  <packaging>jar</packaging>
  <version>1.0-SNAPSHOT</version>

  <name>A Camel Spring Boot Route</name>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
    <spring.boot-version>2.5.3</spring.boot-version>
    <surefire.plugin.version>3.0.0-M4</surefire.plugin.version>
    <camel-version>3.11.1</camel-version>
  </properties>

  <dependencyManagement>
    <dependencies>
      <!-- Spring Boot BOM -->
      <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-dependencies</artifactId>
        <version>${spring.boot-version}</version>
        <type>pom</type>
        <scope>import</scope>
      </dependency>
      <!-- Camel BOM -->
      <dependency>
        <groupId>org.apache.camel.springboot</groupId>
        <artifactId>camel-spring-boot-dependencies</artifactId>
        <version>${camel-version}</version>
        <type>pom</type>
        <scope>import</scope>
      </dependency>
    </dependencies>
  </dependencyManagement>

  <dependencies>

    <!-- Spring Boot -->
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-web</artifactId>
      <exclusions>
        <exclusion>
          <groupId>org.springframework.boot</groupId>
          <artifactId>spring-boot-starter-tomcat</artifactId>
        </exclusion>
      </exclusions>
    </dependency>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-undertow</artifactId>
    </dependency>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-actuator</artifactId>
    </dependency>

    <!-- Camel -->
    <dependency>
      <groupId>org.apache.camel.springboot</groupId>
      <artifactId>camel-spring-boot-starter</artifactId>
    </dependency>
    <dependency>
      <groupId>org.apache.camel.springboot</groupId>
      <artifactId>camel-stream-starter</artifactId>
    </dependency>

    <!-- Test -->
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-test</artifactId>
      <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>org.apache.camel</groupId>
      <artifactId>camel-test-spring-junit5</artifactId>
      <scope>test</scope>
    </dependency>
    
    <!-- Additions -->
    
    <!-- For receiving JMS messages from Artemis -->
    <dependency>
        <groupId>org.apache.activemq</groupId>
        <artifactId>artemis-jms-client</artifactId>
    </dependency>
    <dependency>
        <groupId>org.apache.camel.springboot</groupId>
        <artifactId>camel-jms-starter</artifactId>
    </dependency>
    <dependency>
       <groupId>org.messaginghub</groupId>
       <artifactId>pooled-jms</artifactId>
    </dependency>
    <dependency>
        <groupId>javax.json</groupId>
        <artifactId>javax.json-api</artifactId>
    </dependency>
    
    <!-- Model Object Translation -->
    <dependency>
        <groupId>org.apache.camel</groupId>
        <artifactId>camel-jackson</artifactId>
        <version>${camel-version}</version>
    </dependency>
    
    <!-- Working with JSON -->
    <dependency>
      <groupId>org.apache.camel</groupId>
      <artifactId>camel-jsonpath</artifactId>
      <version>${camel-version}</version>
    </dependency>
    
    <!-- Database Access -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>
    <dependency>
        <groupId>org.apache.camel</groupId>
        <artifactId>camel-jdbc</artifactId>
        <version>${camel-version}</version>
    </dependency>
    <dependency>
        <groupId>org.apache.camel.springboot</groupId>
        <artifactId>camel-jdbc-starter</artifactId>
    </dependency>
    <dependency>
        <groupId>com.microsoft.sqlserver</groupId>
        <artifactId>mssql-jdbc</artifactId>
        <version>8.2.2.jre11</version>
    </dependency>
    
  </dependencies>

  <build>
    <plugins>
      <plugin>
        <groupId>org.apache.maven.plugins</groupId>
        <artifactId>maven-compiler-plugin</artifactId>
        <version>3.8.1</version>
        <configuration>
          <source>1.8</source>
          <target>1.8</target>
        </configuration>
      </plugin>
      <plugin>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-maven-plugin</artifactId>
        <version>${spring.boot-version}</version>
        <executions>
          <execution>
            <goals>
              <goal>repackage</goal>
            </goals>
          </execution>
        </executions>
      </plugin>
      <plugin>
        <groupId>org.apache.maven.plugins</groupId>
        <artifactId>maven-surefire-plugin</artifactId>
        <version>${surefire.plugin.version}</version>
      </plugin>
    </plugins>
  </build>

</project>

Answered By - BenW

This Answer collected from stackoverflow and tested by JavaFixing community admins, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Sunday, November 7, 2021

[FIXED] JDBC from Camel and Spring Boot to MS SQL Server

Issue

Solution

Popular Posts

Labels