[FIXED] Spring Boot App - Tomcat can't access SQLite database file

Issue

I have been searching about this solution for days now and I can't figure out a solution about it.

I have a Java Spring Boot application supposed to interact with a database, for ease to use and store I chose a SQLite database file. My application is hosted on a Tomcat 8.5 on a Windows Server 2019.

On launch my applications is supposed to read the SQLite database but I'm getting the error : [SQLITE_ERROR] SQL error or missing database (no such table: users_moved_group)

The error shows that the application cannot find the database but it does exist and I have double check a thousand times the path (I also tried absolute and relative paths), here it is :

spring.datasource.url = jdbc:sqlite:C:/Tomcat 8.5/db/workday.sqlite

As you can see, everything checks out. So my guess is that the Tomcat Service cannot read the file , but it is located inside the Tomcat folder especially to avoid this kind of error (on clean install of a Windows Server and Tomcat it works).

The issue only happens on one of 3 Virtual Machine, not mine but where I still have almost all admin rights.

I am totally lost here, nothing I do makes the Tomcat see the file to allow my application to reads it.

Here is a sample code reading the database :

public void clearTable() {

Connection connection;
String usersMovedGroupTable = "users_moved_group";
String sqlExpression = "SELECT * FROM " + usersMovedGroupTable;

List<UsersMovedGroup> usersMovedGroupList = new ArrayList<>();

try {
    connection = DriverManager.getConnection(applicationProperties.getDatasourceUrl());
    Statement statement = connection.createStatement();

    ResultSet rs = statement.executeQuery(sqlExpression);
    while(rs.next())
    {
        UsersMovedGroup user = new UsersMovedGroup(rs.getString("ump_date"), rs.getString("ump_user_id"), rs.getString("ump_user_login"), rs.getString("ump_group_id"), rs.getString("ump_group_name"));
        usersMovedGroupList.add(user);
    }
} catch (SQLException e) {
    logger.error("Error connecting to SQLite database : " + e.getMessage());
}

At first I was using jdbcTemplate to make SQL request but I changed it to Statement etc, but same result.

If you want here is my pom.xml :

<?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/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.4.4</version>
    <relativePath/>
  </parent>

  <groupId>com.sqlite</groupId>
  <artifactId>user-moved-ad-group-event-hook</artifactId>
  <version>1.0-SNAPSHOT</version>
  <packaging>war</packaging>
  <name>user-moved-ad-group-event-hook</name>
  <description>Detect user changed of AD group</description>

  <properties>
    <java.version>1.8</java.version>
  </properties>

  <dependencies>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-test</artifactId>
      <scope>test</scope>
    </dependency>
    
    <dependency>
      <groupId>org.apache.commons</groupId>
      <artifactId>commons-lang3</artifactId>
      <version>3.5</version>
    </dependency>

    <!-- SQLite -->
    <dependency>
      <groupId>org.xerial</groupId>
      <artifactId>sqlite-jdbc</artifactId>
      <version>3.34.0</version>
    </dependency>

    <dependency>
      <groupId>log4j</groupId>
      <artifactId>log4j</artifactId>
      <version>1.2.17</version>
      <scope>compile</scope>
    </dependency>

    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-jdbc</artifactId>
      <version>RELEASE</version>
    </dependency>

    <dependency>
      <groupId>org.thymeleaf</groupId>
      <artifactId>thymeleaf-spring5</artifactId>
    </dependency>
  </dependencies>

  <build>
    <finalName>${project.artifactId}</finalName>
    <plugins>
      <plugin>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-maven-plugin</artifactId>
        <version>2.4.4</version>
      </plugin>
    </plugins>
  </build>

  <profiles>
    <profile>
      <id>dev</id>
      <activation>
        <activeByDefault>true</activeByDefault>
      </activation>
      <properties>
        <activatedProperties>dev</activatedProperties>
      </properties>
    </profile>
    <profile>
      <id>lyvoc</id>
      <properties>
        <activatedProperties>lyvoc</activatedProperties>
      </properties>
    </profile>
    <profile>
      <id>prod</id>
      <properties>
        <activatedProperties>prod</activatedProperties>
      </properties>
    </profile>
  </profiles>

</project>

Solution

Comming back with some feedback. After trying everything I tried a fresh install of a Tomcat 9.0 (instead of common used 8.5) and everything works now without any change from the code.

Answered By - Jérémie Poisson