Database logging using log4j in Spring Boot

In our Spring Boot project, I am trying to accumulate messages in an h2 in-memory database and load them into MySQL database when the number of messages reaches 50.

Here are the dependencies on the pom.xml file:

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
            <exclusions>
                <exclusion>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-starter-logging</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-starter-log4j -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-log4j</artifactId>
            <version>1.3.8.RELEASE</version>
        </dependency>

 <!-- H2 DB -->
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <version>${h2.version}</version>
        </dependency>

      

        
        
        
      

    

log4j.properties looks like this:

log4j.rootLogger = DEBUG, sql

# Define the file appender
log4j.appender.sql=org.apache.log4j.jdbc.JDBCAppender
log4j.appender.sql.URL=jdbc:h2:mem:liteDatabase
# Set Database Driver
log4j.appender.sql.driver=org.h2.Driver
# Set database user name and password
log4j.appender.sql.user=sa
log4j.appender.sql.password=sa
# Set the SQL statement to be executed.
log4j.appender.sql.sql=INSERT INTO LOGS (LOG_DATE, LOGGER, LOG_LEVEL, MESSAGE) VALUES (now() ,'%C','%p','%m')
# Define the xml layout for file appender
log4j.appender.sql.layout=org.apache.log4j.PatternLayout

      

        
        
        
      

    

From the createTables-h2.sql file:

CREATE TABLE LOGS
(
    id INT auto_increment PRIMARY KEY,
    LOG_DATE   DATETIME NOT NULL,
    LOGGER  VARCHAR2(250)    NOT NULL,
    LOG_LEVEL   VARCHAR2(10)    NOT NULL,
    MESSAGE VARCHAR2(1000)  NOT NULL
);

      

        
        
        
      

    

datasource bean for h2:

@Bean(name = "liteDataSource")
    public BasicDataSource liteDataSource() {
        BasicDataSource dataSource = new BasicDataSource();
        dataSource.setDriverClassName(env.getProperty("h2.driver-class-name"));
        dataSource.setUrl(env.getProperty("h2.url"));
        dataSource.setUsername(env.getProperty("h2.username"));
        dataSource.setPassword(env.getProperty("h2.password"));

        Resource initData = new ClassPathResource("scripts/createTables-h2.sql");
        DatabasePopulator databasePopulator = new ResourceDatabasePopulator(initData);
        DatabasePopulatorUtils.execute(databasePopulator, dataSource);
        return dataSource;
}

      

        
        
        
      

    

The problem is log4j is trying to log into the database before it is created

Error message:

log4j:ERROR Failed to excute sql
org.h2.jdbc.JdbcSQLException: Table "LOGS" not found; SQL statement:
INSERT INTO LOGS VALUES ('', now() ,'org.springframework.core.env.MutablePropertySources','DEBUG','Adding [servletConfigInitParams] PropertySource with lowest search precedence') [42102-193]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
    at org.h2.message.DbException.get(DbException.java:179)
    at org.h2.message.DbException.get(DbException.java:155)
    at org.h2.command.Parser.readTableOrView(Parser.java:5389)
    at org.h2.command.Parser.readTableOrView(Parser.java:5366)
    at org.h2.command.Parser.parseInsert(Parser.java:1053)
    at org.h2.command.Parser.parsePrepared(Parser.java:413)
    at org.h2.command.Parser.parse(Parser.java:317)
    at org.h2.command.Parser.parse(Parser.java:289)
    at org.h2.command.Parser.prepareCommand(Parser.java:254)
    at org.h2.engine.Session.prepareLocal(Session.java:561)
    at org.h2.engine.Session.prepareCommand(Session.java:502)
    at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1203)
    at org.h2.jdbc.JdbcStatement.executeUpdateInternal(JdbcStatement.java:126)
    at org.h2.jdbc.JdbcStatement.executeUpdate(JdbcStatement.java:115)
    at org.apache.log4j.jdbc.JDBCAppender.execute(JDBCAppender.java:218)
    at org.apache.log4j.jdbc.JDBCAppender.flushBuffer(JDBCAppender.java:289)
    at org.apache.log4j.jdbc.JDBCAppender.append(JDBCAppender.java:186)
    at org.apache.log4j.AppenderSkeleton.doAppend(AppenderSkeleton.java:251)
    at org.apache.log4j.helpers.AppenderAttachableImpl.appendLoopOnAppenders(AppenderAttachableImpl.java:66)
    at org.apache.log4j.Category.callAppenders(Category.java:206)
    at org.apache.log4j.Category.forcedLog(Category.java:391)
    at org.apache.log4j.Category.log(Category.java:856)
    at org.slf4j.impl.Log4jLoggerAdapter.log(Log4jLoggerAdapter.java:581)
    at org.apache.commons.logging.impl.SLF4JLocationAwareLog.debug(SLF4JLocationAwareLog.java:131)
    at org.springframework.core.env.MutablePropertySources.addLast(MutablePropertySources.java:109)
    at org.springframework.web.context.support.StandardServletEnvironment.customizePropertySources(StandardServletEnvironment.java:84)
    at org.springframework.core.env.AbstractEnvironment.<init>(AbstractEnvironment.java:122)
    at org.springframework.core.env.StandardEnvironment.<init>(StandardEnvironment.java:54)
    at org.springframework.web.context.support.StandardServletEnvironment.<init>(StandardServletEnvironment.java:44)
    at org.springframework.boot.SpringApplication.getOrCreateEnvironment(SpringApplication.java:436)
    at org.springframework.boot.SpringApplication.prepareEnvironment(SpringApplication.java:335)
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:308)
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1186)
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1175)
    at com.example.DemoApplication.main(DemoApplication.java:42)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:483)
    at org.springframework.boot.devtools.restart.RestartLauncher.run(RestartLauncher.java:49)

      

        
        
        
      

    

Please help me if you can suggest any solution!