Spring Boot Logback DB Appender Properties

Question

Hi I want to use a DBAppenderin my Spring Boot application. I want to retrieve database connection properties from the application.properties file. However it doesn't seem to recognize them. Keep in mind that I'm using Spring Boot 1.2.x so I can't use logback-spring.xml yet.

The configuration I'm using is the following:

<appender name="DB" class="ch.qos.logback.classic.db.DBAppender">
        <connectionSource class="ch.qos.logback.core.db.DriverManagerConnectionSource">

            <driverClass>${spring.datasource.driver-class-name}</driverClass>
            <url>${spring.datasource.url}</url>
            <user>${spring.datasource.username}</user>
            <password>${spring.datasource.password}</password>
        </connectionSource>
    </appender>

Contour

That will never work. Logback knows nothing about spring and vice versa. Replacement of properties is only going to work for spring managed beans, the logback config isn't spring managed so you cannot do things like that. – Ejecta

Answer 1

Stumbled upon this while searching for a similar solution. Since this is still unanswered, here's a few approaches I found:

1) If you are using Spring Boot 1.3+ (which you already pointed out you're not but for future reference), I managed to use the <springProperty> tag to reuse the same values from application.properties.

application.properties (for embedded H2 DB):

spring.datasource.driverClassName=org.h2.Driver
spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.username=sa
spring.datasource.password=

logback-spring.xml:

<springProperty name="spring.datasource.driverClassName" source="spring.datasource.driverClassName"/>
<springProperty name="spring.datasource.url" source="spring.datasource.url"/>
<springProperty name="spring.datasource.username" source="spring.datasource.username"/>
<springProperty name="spring.datasource.password" source="spring.datasource.password"/>

<appender name="DB" class="ch.qos.logback.classic.db.DBAppender">
    <connectionSource class="ch.qos.logback.core.db.DriverManagerConnectionSource">
        <driverClass>${spring.datasource.driverClassName}</driverClass>
        <url>${spring.datasource.url}</url>
        <user>${spring.datasource.username}</user>
        <password>${spring.datasource.password}</password>
    </connectionSource>
</appender>

2) Import application properties as property source: Unable to use Spring Property Placeholders in logback.xml

<property resource="application.properties" />

3) Maybe you're able to register the Datasource in the container JNDI and use logback's JNDIConnectionSource instead? Check out this other post: How to create JNDI context in Spring Boot with Embedded Tomcat Container

Shirker

Answer 2

So please upvote jpt's answer. Because without the hint of "

<property resource="application.yml" />

" (or .properties in that answer), I wouldn't have gotten anything to work.

But my answer and contribution : I wanted to add this will work with application.yml as well.

I will try to list everything I did here:

Set 3 environment variables.

SPRING_DATASOURCE_URL
SPRING_DATASOURCE_USER
SPRING_DATASOURCE_PASSWORD

contents of application.yml (below)

spring:
  datasource:
    #SPRING_DATASOURCE_URL environment variable will be something like -> jdbc:sqlserver://MySqlServer\\MyInstance:1433;DatabaseName=MyDbName;
    url: ${SPRING_DATASOURCE_URL}
    username: ${SPRING_DATASOURCE_USERNAME}
    password: ${SPRING_DATASOURCE_PASSWORD}
    driverClassName: com.microsoft.sqlserver.jdbc.SQLServerDriver
logging:
    config: classpath:logback-spring.xml

Note, I am using the file "logback-spring.xml". I'm not sure if it makes a difference (as opposed to just using "logback.xml")

Contents of logback-spring.xml (below)

<configuration debug="true" scan="true" scanPeriod="30 seconds">


    <appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender">
        <filter class="ch.qos.logback.classic.filter.ThresholdFilter">
            <level>INFO</level>
        </filter>
        <encoder>
            <pattern>%d{yyyy-MM-dd HH:mm:ss.SSS} [%thread] %-5level %logger{36} [%file:%line] %msg%n</pattern>
        </encoder>
    </appender>



    <!-- THIS IS THE MAGIC LINE that JPT figured out -->
    <property resource="application.yml" />

    <springProperty name="humptydumptyurl" source="spring.datasource.url"/>
    <springProperty name="humptydumptyusername" source="spring.datasource.username"/>
    <springProperty name="humptydumptypassword" source="spring.datasource.password"/>


    <appender name = "MyDbAppender" class="ch.qos.logback.classic.db.DBAppender">
        <connectionSource class="ch.qos.logback.core.db.DriverManagerConnectionSource">
            <driverClass>com.microsoft.sqlserver.jdbc.SQLServerDriver</driverClass>
            <url>${humptydumptyurl}</url>
            <user>${humptydumptyusername}</user>
            <password>${humptydumptypassword}</password>
        </connectionSource>
    </appender>



    <root level="INFO">
        <appender-ref ref="STDOUT"/>
        <appender-ref ref="MyDbAppender"/>
    </root>

</configuration>

Note, I purposely used "humptydumpty" to avoid ambiguity. You'll probably use a better prefix, but I wanted to show the left side of the mapping can be any name you want.

Note, my application.properties file is empty. I'm 100% application.yml.

Note, you can also set/use a fourth environment variable (I think it will work find, I haven't tested yet)

SPRING_DATASOURCE_DRIVER-CLASS-NAME

and its two buddies

datasource:
    driverClassName: ${SPRING_DATASOURCE_DRIVER-CLASS-NAME}

and

  <springProperty name="humptydumptydriverclassname" source="spring.datasource.driver-class-name"/>

......

A few mssql-server notes below (probably not important to most users)

I used

        <dependency>
            <groupId>com.microsoft.sqlserver</groupId>
            <artifactId>mssql-jdbc</artifactId>
            <version>7.0.0.jre8</version>
        </dependency>

And had it working.

I had to tweak my DDL some, because I was getting "truncate" errors on INSERT.

-- This SQL script creates the required tables by ch.qos.logback.classic.db.DBAppender
-- 
-- The event_id column type was recently changed from INT to DECIMAL(40)
-- without testing.

DROP TABLE logging_event_property 
DROP TABLE logging_event_exception 
DROP TABLE logging_event 

CREATE TABLE logging_event 
  ( 
    timestmp         DECIMAL(20) NOT NULL,
    formatted_message  VARCHAR(max) NOT NULL,
    logger_name       VARCHAR(512) NOT NULL,
    level_string      VARCHAR(512) NOT NULL,
    thread_name       VARCHAR(512),
    reference_flag    SMALLINT,
    arg0              VARCHAR(512),
    arg1              VARCHAR(512),
    arg2              VARCHAR(512),
    arg3              VARCHAR(512),
    caller_filename   VARCHAR(512) NOT NULL,
    caller_class      VARCHAR(512) NOT NULL,
    caller_method     VARCHAR(512) NOT NULL,
    caller_line       CHAR(16) NOT NULL,
    event_id          DECIMAL(38) NOT NULL identity,
    PRIMARY KEY(event_id) 
  ) 

CREATE TABLE logging_event_property 
  ( 
    event_id          DECIMAL(38) NOT NULL, 
    mapped_key        VARCHAR(512) NOT NULL, 
    mapped_value      VARCHAR(1024), 
    PRIMARY KEY(event_id, mapped_key), 
    FOREIGN KEY (event_id) REFERENCES logging_event(event_id) 
  ) 

CREATE TABLE logging_event_exception 
  ( 
    event_id         DECIMAL(38) NOT NULL, 
    i                SMALLINT NOT NULL, 
    trace_line       VARCHAR(512) NOT NULL, 
    PRIMARY KEY(event_id, i), 
    FOREIGN KEY (event_id) REFERENCES logging_event(event_id) 
  ) 

And finally, let me give the most important debug hint of all.

HARD CODE YOUR CONNECTION STRING VALUES FIRST, get it working...THEN start plugging in the environment-variable substitutions.

The ch.qos.logback.classic.db.DBAppender has to have a good and working connection string to do a "check".....if you put in a errant connection string you will get this kind of error (below). I spent 4 hours chasing the error, which was happening because I chasing the below error, simply because my connection string was not working correctly. Again, hard code correct/working values into logback-spring.xml FIRST, get it working, then circle back and do the environment-variable spring-properties replacement voodoo.

So for for the db-appender, hard-code your url, username, password FIRST, get it working, THEN start doing the substitutions slowly...I chased the below error way too long before realizing all the substitutions were not working....

06:19:09,721 |-WARN in ch.qos.logback.classic.db.DBAppender[MyDbAppender] - Attempted to append to non started appender [MyDbAppender].
    at org.springframework.boot.context.logging.LoggingApplicationListener.onApplicationEvent(LoggingApplicationListener.java:202)
    at org.springframework.context.event.SimpleApplicationEventMulticaster.doInvokeListener(SimpleApplicationEventMulticaster.java:172)
    at org.springframework.context.event.SimpleApplicationEventMulticaster.invokeListener(SimpleApplicationEventMulticaster.java:165)
    at org.springframework.context.event.SimpleApplicationEventMulticaster.multicastEvent(SimpleApplicationEventMulticaster.java:139)
    at org.springframework.context.event.SimpleApplicationEventMulticaster.multicastEvent(SimpleApplicationEventMulticaster.java:127)
    at org.springframework.boot.context.event.EventPublishingRunListener.environmentPrepared(EventPublishingRunListener.java:75)
    at org.springframework.boot.SpringApplicationRunListeners.environmentPrepared(SpringApplicationRunListeners.java:54)
    at org.springframework.boot.SpringApplication.prepareEnvironment(SpringApplication.java:347)
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:306)
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1260)
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1248)
Caused by: java.lang.IllegalStateException: Logback configuration error detected: 
ERROR in ch.qos.logback.core.joran.spi.Interpreter@68:16 - RuntimeException in Action for tag [appender] java.lang.IllegalStateException: DBAppender cannot function if the JDBC driver does not support getGeneratedKeys method *and* without a specific SQL dialect
    at org.springframework.boot.logging.logback.LogbackLoggingSystem.loadConfiguration(LogbackLoggingSystem.java:169)
    at org.springframework.boot.logging.AbstractLoggingSystem.initializeWithSpecificConfig(AbstractLoggingSystem.java:67)
    at org.springframework.boot.logging.AbstractLoggingSystem.initialize(AbstractLoggingSystem.java:57)
    at org.springframework.boot.logging.logback.LogbackLoggingSystem.initialize(LogbackLoggingSystem.java:117)
    at org.springframework.boot.context.logging.LoggingApplicationListener.initializeSystem(LoggingApplicationListener.java:298)

And just to finish out a list of everything.

These are the logback versions I was using:

        <dependency>
            <groupId>ch.qos.logback</groupId>
            <artifactId>logback-core</artifactId>
            <version>1.2.3</version>
        </dependency>
        <dependency>
            <groupId>ch.qos.logback</groupId>
            <artifactId>logback-classic</artifactId>
            <version>1.2.3</version>
        </dependency>

Lyceum

Answer 3

I was looking for the solution jpt and specially granadaCoder descibed before, but it didn't work for me despite working with logback-spring.xml as well. Instead much simplier method helped:

Magic line

<property resource="application-local.yml" />

Then simple

<appender name="DB_Logger" class="ch.qos.logback.classic.db.DBAppender">
            <connectionSource class="ch.qos.logback.core.db.DriverManagerConnectionSource">
                <driverClass>com.mysql.cj.jdbc.Driver</driverClass>
                <url>${URL}</url>
                <user>${USERNAME}</user>
                <password>${PASSWORD}</password>
            </connectionSource>
        </appender>

Obviously in my application.yml I have spring.datasource.URL/USERNAME/PASSWORD values

Mucoid