javamavendatabase-migrationderby

Running Swing Maven jar app through command line doesn't build database, but it does when running the same app from NetBeans, how to fix?

I wrote a Maven Java swing application with Apache derby embedded mode.

Used flyway-database-derby to migrate the database (to create the database).
Used maven-shade-plugin to "Clean and Build" jar with dependencies.
Used Apache NetBeans IDE 21

Case A: Run the app from the command line

If I launch the jar file using command line java -jar warehouse-box-1.0-SNAPSHOT.jar it outputs this message:

C:\Users\Saleh\Documents\NetBeansProjects\warehouse-box\target>java -jar warehouse-box-1.0-SNAPSHOT.jar
[AWT-EventQueue-0] INFO org.flywaydb.core.internal.resource.ResourceNameValidator - 1 SQL migrations were detected but not run because they did not follow the filename convention.
[AWT-EventQueue-0] INFO org.flywaydb.core.internal.resource.ResourceNameValidator - Set 'validateMigrationNaming' to true to fail fast and see a list of the invalid file names.
[AWT-EventQueue-0] INFO org.flywaydb.core.FlywayExecutor - Database: jdbc:derby:C:\Users\Saleh\warehouse-db (Apache Derby 10.17)
[AWT-EventQueue-0] WARN org.flywaydb.core.internal.database.base.Database - Flyway upgrade recommended: Derby 10.17 is newer than this version of Flyway and support has not been tested. The latest supported version of Derby is 10.15.
[AWT-EventQueue-0] INFO org.flywaydb.core.internal.database.base.Schema - Creating schema "ROOT" ...
[AWT-EventQueue-0] INFO org.flywaydb.core.internal.schemahistory.JdbcTableSchemaHistory - Creating Schema History table "ROOT"."flyway_schema_history" ...
[AWT-EventQueue-0] INFO org.flywaydb.core.internal.command.DbMigrate - Current version of schema "ROOT": null
[AWT-EventQueue-0] INFO org.flywaydb.core.internal.command.DbMigrate - Schema "ROOT" is up to date. No migration necessary.

ij> connect 'jdbc:derby:C:\Users\Saleh\warehouse-db;user=root;password=1212';
ij> show tables; # My tables were not listed, they were not created.

ij> select * from items;
ERROR 42X05: Table/View 'ITEMS' does not exist.

Case B: Run the app from NetBeans: (No problems)

No problems if I run the application from within Apache NetBeans; the app starts and the database created, and everything works smoothly. NetBeans output:

cd C:\Users\Saleh\Documents\NetBeansProjects\warehouse-box; "JAVA_HOME=C:\\Program Files\\Java\\jdk-20" cmd /c "\"C:\\Program Files\\NetBeans-21\\netbeans\\java\\maven\\bin\\mvn.cmd\" -Dexec.vmArgs= \"-Dexec.args=${exec.vmArgs} -classpath %classpath ${exec.mainClass} ${exec.appArgs}\" -Dexec.appArgs= -Dexec.mainClass=warehousebox.root.MainRun \"-Dexec.executable=C:\\Program Files\\Java\\jdk-20\\bin\\java.exe\" \"-Dmaven.ext.class.path=C:\\Program Files\\NetBeans-21\\netbeans\\java\\maven-nblib\\netbeans-eventspy.jar\" --no-transfer-progress process-classes org.codehaus.mojo:exec-maven-plugin:3.1.0:exec"
Scanning for projects...

--------------------< com.salehrezq:warehouse-box >---------------------
Building warehouse-box 1.0-SNAPSHOT
  from pom.xml
--------------------------------[ jar ]---------------------------------

--- resources:3.3.1:resources (default-resources) @ warehouse-box ---
Copying 10 resources from src\main\resources to target\classes

--- compiler:3.11.0:compile (default-compile) @ warehouse-box ---
Nothing to compile - all classes are up to date

--- exec:3.1.0:exec (default-cli) @ warehouse-box ---
[AWT-EventQueue-0] INFO org.flywaydb.core.FlywayExecutor - Database: jdbc:derby:C:\Users\Saleh\warehouse-db (Apache Derby 10.17)
[AWT-EventQueue-0] WARN org.flywaydb.core.internal.database.base.Database - Flyway upgrade recommended: Derby 10.17 is newer than this version of Flyway and support has not been tested. The latest supported version of Derby is 10.15.
[AWT-EventQueue-0] INFO org.flywaydb.core.internal.command.DbValidate - Successfully validated 2 migrations (execution time 00:02.396s)
[AWT-EventQueue-0] INFO org.flywaydb.core.internal.command.DbMigrate - Current version of schema "ROOT": null
[AWT-EventQueue-0] INFO org.flywaydb.core.internal.command.DbMigrate - Migrating schema "ROOT" to version "1 - warehouse db tables"
[AWT-EventQueue-0] INFO org.flywaydb.core.internal.command.DbMigrate - Successfully applied 1 migration to schema "ROOT", now at version v1 (execution time 00:02.471s)
------------------------------------------------------------------------
BUILD SUCCESS
------------------------------------------------------------------------
Total time:  01:16 min
Finished at: 2024-09-04T16:15:19+03:00
------------------------------------------------------------------------

Reverse the order of case A and B

If I first run the app using NetBeans (case B) and then after that; close it and run the app using the command line (case A), now the app launches using the command line and can work smoothly without problems, and it interacts with the created database that was created earlier in case B. So, case A works fine only if I first create the database by running the app from NetBeans. The output of the command line after the reverse:

C:\Users\Saleh\Documents\NetBeansProjects\warehouse-box\target>java -jar warehouse-box-1.0-SNAPSHOT.jar
[AWT-EventQueue-0] INFO org.flywaydb.core.internal.resource.ResourceNameValidator - 1 SQL migrations were detected but not run because they did not follow the filename convention.
[AWT-EventQueue-0] INFO org.flywaydb.core.internal.resource.ResourceNameValidator - Set 'validateMigrationNaming' to true to fail fast and see a list of the invalid file names.
[AWT-EventQueue-0] INFO org.flywaydb.core.FlywayExecutor - Database: jdbc:derby:C:\Users\Saleh\warehouse-db (Apache Derby 10.17)
[AWT-EventQueue-0] WARN org.flywaydb.core.internal.database.base.Database - Flyway upgrade recommended: Derby 10.17 is newer than this version of Flyway and support has not been tested. The latest supported version of Derby is 10.15.
[AWT-EventQueue-0] INFO org.flywaydb.core.internal.command.DbValidate - Successfully validated 2 migrations (execution time 00:00.270s)
[AWT-EventQueue-0] INFO org.flywaydb.core.internal.command.DbMigrate - Current version of schema "ROOT": 1
[AWT-EventQueue-0] INFO org.flywaydb.core.internal.command.DbMigrate - Schema "ROOT" is up to date. No migration necessary.

pom.xml

(For brevity I omitted dependencies that are not related to the topic)

<?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>
    <groupId>com.salehrezq</groupId>
    <artifactId>warehouse-box</artifactId>
    <version>1.0-SNAPSHOT</version>
    <packaging>jar</packaging>
    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <maven.compiler.source>20</maven.compiler.source>
        <maven.compiler.target>20</maven.compiler.target>
        <exec.mainClass>warehousebox.root.MainRun</exec.mainClass>
    </properties>
    <dependencies>
        <!-- https://mvnrepository.com/artifact/org.apache.derby/derby -->
        <dependency>
            <groupId>org.apache.derby</groupId>
            <artifactId>derby</artifactId>
            <version>10.17.1.0</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.derby/derbytools -->
        <dependency>
            <groupId>org.apache.derby</groupId>
            <artifactId>derbytools</artifactId>
            <version>10.17.1.0</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.flywaydb/flyway-database-derby -->
        <dependency>
            <groupId>org.flywaydb</groupId>
            <artifactId>flyway-database-derby</artifactId>
            <version>10.17.3</version>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-shade-plugin</artifactId>
                <version>3.6.0</version>
                <executions>
                    <execution>
                        <phase>package</phase>
                        <goals>
                            <goal>shade</goal>
                        </goals>
                        <configuration>
                            <transformers>
                                <transformer implementation="org.apache.maven.plugins.shade.resource.ManifestResourceTransformer">
                                    <mainClass>warehousebox.root.MainRun</mainClass>
                                </transformer>
                            </transformers>
                        </configuration>
                    </execution>
                </executions>
            </plugin>
            <!-- https://mvnrepository.com/artifact/org.apache.maven.plugins/maven-source-plugin -->
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-source-plugin</artifactId>
                <version>3.3.1</version>
            </plugin>
            <!-- https://mvnrepository.com/artifact/org.apache.maven.plugins/maven-javadoc-plugin -->
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-javadoc-plugin</artifactId>
                <version>3.10.0</version>
            </plugin>
            <!-- https://mvnrepository.com/artifact/org.apache.maven.plugins/maven-deploy-plugin -->
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-deploy-plugin</artifactId>
                <version>3.1.3</version>
            </plugin>
        </plugins>
    </build>
</project>

Database migration and connectivity

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.flywaydb.core.Flyway;

public class Connect {

    // JDBC driver name and database URL
    private static final String JDBC_DRIVER = "org.apache.derby.jdbc.EmbeddedDriver";
    private static final String DB_URL = "jdbc:derby:"
            + System.getProperty("user.home")
            + File.separator
            + "warehouse-db;create=true;collation=TERRITORY_BASED:PRIMARY";

    //  Database credentials
    private static final String USER = "root";
    private static final String PASS = "1212";

    // For atomic statements
    private static Connection conn;

    public static void buildDatabaseIfNotExist() {
        Properties p = new Properties();
        p.setProperty("validateMigrationNaming", "true");
        Flyway flyway = Flyway.configure().configuration(p).dataSource(DB_URL, USER, PASS).load();
        flyway.migrate();
    }

    public static Connection getConnection() {
        try {
            Class.forName(JDBC_DRIVER);
            conn = DriverManager.getConnection(DB_URL, USER, PASS);
        } catch (SQLException | ClassNotFoundException ex) {
            Logger.getLogger(Connect.class.getName()).log(Level.SEVERE, null, ex);
        }
        return conn;
    }
}

Migration file for flyway

Location and name: NetBeansProjects\warehouse-box\src\main\resources\db\migration\V1__warehouse_db_tables.sql

NetBeans structure:

.
└── Project/
    └── Other Sources/
        └── src/main/resources/
            └── db.migration/
                └── V1__warehouse_db_tables.sql

If anyone is interested in reproducing the whole experience; this is the app on github: https://github.com/salehrezq/warehouse-box

Solution

  • Finally, found the issue. The issue is with the way how you were creating the shade jar.

    You need to add org.apache.maven.plugins.shade.resource.ServicesResourceTransformer in the maven-shade-plugin.

    Update the maven-shade-plugin in the pom.xml:

    <plugin>
    <groupId>org.apache.maven.plugins</groupId>
    <artifactId>maven-shade-plugin</artifactId>
    <version>3.6.0</version>
    <executions>
        <execution>
            <phase>package</phase>
            <goals>
                <goal>shade</goal>
            </goals>
            <configuration>
                <transformers>
                    <transformer implementation="org.apache.maven.plugins.shade.resource.ManifestResourceTransformer">
                        <mainClass>warehousebox.root.MainRun</mainClass>
                    </transformer>
                    <transformer implementation="org.apache.maven.plugins.shade.resource.ServicesResourceTransformer"/>
                </transformers>
            </configuration>
        </execution>
    </executions>
</plugin>

    Why you need to add ServicesResourceTransformer?

    From Docs:

    JAR files providing implementations of some interfaces often ship with a META-INF/services/ directory that maps interfaces to their implementation classes for lookup by the service locator. To relocate the class names of these implementation classes, and to merge multiple implementations of the same interface into one service entry.

    Output on my local (Success):

    ➜  ~ git:(master) ✗ java -jar warehouse-box-1.0-SNAPSHOT.jar
2024-09-07 15:29:01.002 java[15668:560994] WARNING: Secure coding is not enabled for restorable state! Enable secure coding by implementing NSApplicationDelegate.applicationSupportsSecureRestorableState: and returning YES.
[AWT-EventQueue-0] INFO org.flywaydb.core.FlywayExecutor - Database: jdbc:derby:/Users/anish/warehouse-db-1 (Apache Derby 10.17)
[AWT-EventQueue-0] WARN org.flywaydb.core.internal.database.base.Database - Flyway upgrade recommended: Derby 10.17 is newer than this version of Flyway and support has not been tested. The latest supported version of Derby is 10.15.
[AWT-EventQueue-0] INFO org.flywaydb.core.internal.database.base.Schema - Creating schema "ROOT" ...
[AWT-EventQueue-0] INFO org.flywaydb.core.internal.schemahistory.JdbcTableSchemaHistory - Creating Schema History table "ROOT"."flyway_schema_history" ...
[AWT-EventQueue-0] INFO org.flywaydb.core.internal.command.DbMigrate - Current version of schema "ROOT": null
[AWT-EventQueue-0] INFO org.flywaydb.core.internal.command.DbMigrate - Migrating schema "ROOT" to version "1 - warehouse db tables"
[AWT-EventQueue-0] INFO org.flywaydb.core.internal.command.DbMigrate - Successfully applied 1 migration to schema "ROOT", now at version v1 (execution time 00:00.152s)