Extending BroadSQL with custom commands

BroadSQL can load extra commands from JAR files without recompiling the application. This page covers where extensions live, the objects a command actually has to work with (writing to the console, running SQL directly, running another BroadSQL command), a worked example that uses all three, and the rules BroadSQL enforces on your keywords and JAR at load time.

Where extensions live

Drop a JAR containing your command classes into the extensions/ folder (configurable via CustomExtensionsFolder in conf/BroadSQL.ini, default extensions). BroadSQL scans it for classes at startup.

This means a restart, not a hot reload. The launcher scripts (BroadSQL.bat/broadsql.sh) start the JVM with -cp lib/*;drivers/*;extensions/*: every JAR present in extensions/ at that moment becomes part of the classpath for that run. Dropping a new JAR in while BroadSQL is already running does nothing until you exit and start it again.

Writing a command

Each command is a class extending Command:

public class CommandMyThing extends Command {

    public CommandMyThing() {
        super("MYTHING", "MT"); // first keyword is primary, the rest are synonyms
    }

    @Override
    public void execute(String query) throws BroadSQLException {
        String[] args = parseArgs(query);
        console.writeln("Hello from MYTHING");
    }

    @Override
    public String getDescription() {
        return "One-line description shown in HELP";
    }

    @Override
    public String getArguments() {
        return "Describe expected arguments here, or an empty string if none";
    }

    @Override
    public String getExamples() {
        return "MYTHING;\n\tMYTHING somearg;";
    }
}

getKeywords() (fed by the constructor's super(...) call above), getDescription(), getArguments(), and getExamples() feed both the in-app HELP command and the generated command reference: keep them accurate. parseArgs(query) splits the typed command line into arguments after the recognized keyword, the same way every core command does.

What a command has to work with

Once BroadSQL loads your command, it sets a handful of fields on it before calling execute(query): these are protected, so they're available directly by name inside your class:

Three more things are reachable through public getter methods rather than a field directly (useful from a command in any package, including your own extension's):

• getConsoleCommandInterpreter(): lets you run another BroadSQL command from inside yours. See "Running another BroadSQL command" below.
• getSession(): the current login session.
• getDatabaseConnectionsVault(): the CDF's connections vault, e.g. to check getDatabaseConnectionsVault().contains("SOMEID") before referencing a connection by name.

Writing to the console

console offers several output methods: the distinction that actually matters is whether the current prompt (e.g. MYDB> ) gets prepended:

Running SQL directly

sqlDatabase runs SQL against whichever connection is currently open, exactly like typing it at the prompt would:

• sqlDatabase.executeSelectQuery(String sql): runs a SELECT and prints the results the same way BroadSQL prints any query's results (respecting SET LIST, the current export mode, and so on).
• sqlDatabase.executeUpdateQuery(String sql): runs an INSERT/UPDATE/DELETE/DDL statement and prints a summary line ("N row(s) updated." etc.).
• sqlDatabase.getInt(String sql): runs a query and returns the first column of its first row as an Integer, for a quick scalar (a count, a max, an id) without handling a ResultSet yourself.

For anything more involved (iterating a ResultSet yourself, binding parameters, batching), sqlDatabase.getJdbcTemplate() returns the underlying Spring JdbcTemplate, and sqlDatabase.getDirectConnection() returns the raw java.sql.Connection.

Running another BroadSQL command

getConsoleCommandInterpreter() returns the CommandInterpreter currently driving the session: set its query and ask it to execute, exactly the way BroadSQL's own CONNECT command runs SHOW DBINFO and SHOW AUTOCOMMIT automatically after opening a connection:

getConsoleCommandInterpreter().setQuery("SHOW DBINFO");
getConsoleCommandInterpreter().executeCommand();

The query string is exactly what you'd type at the prompt, without the trailing ;: the interpreter strips it before this point when you type at the prompt, so calling it programmatically skips straight to the part after that.

Worked example: a command using all three

TABLEINFO <tableName> below prints a row count (direct SQL, via sqlDatabase.getInt) followed by the table's structure (a real BroadSQL command, DESCR, run through the interpreter): a small, realistic combination of everything above:

package com.example.broadsql.extensions;

import com.projectsontracks.controller.errors.BroadSQLException;
import com.projectsontracks.controller.shell.commands.Command;
import com.projectsontracks.controller.shell.commands.CommandUtils;

public class CommandTableInfo extends Command {

    public CommandTableInfo() {
        super("TABLEINFO", "TI");
    }

    @Override
    public void execute(String query) throws BroadSQLException {
        String[] args = parseArgs(query);
        if (!CommandUtils.isValidArgs(args)) {
            console.error("You must specify a table name");
            return;
        }
        String tableName = args[0].trim();

        // 1. Write plain output: no prompt prefix, since this continues the command's own output.
        console.writeln("Summary for " + tableName + ":");

        // 2. Run SQL directly against the current connection.
        int rowCount = sqlDatabase.getInt("SELECT COUNT(*) FROM " + tableName);
        console.writeln(rowCount + " row(s).");
        console.writeln("");

        // 3. Run a real BroadSQL command, the same way CONNECT runs SHOW DBINFO internally.
        getConsoleCommandInterpreter().setQuery("DESCR " + tableName);
        getConsoleCommandInterpreter().executeCommand();
    }

    @Override
    public String getDescription() {
        return "Displays a table's row count and structure in one call";
    }

    @Override
    public String getArguments() {
        return "<tableName> (mandatory)";
    }

    @Override
    public String getExamples() {
        return "TABLEINFO CUSTOMER;";
    }
}

MYDB> TABLEINFO CUSTOMER;
Summary for CUSTOMER:
128460 row(s).

Column      Type          Nullable
----------  ------------  --------
ID          INTEGER       NO
NAME        VARCHAR(200)  YES
COUNTRY     VARCHAR(2)    YES

(DESCR's actual output format follows its own command reference; shown here only to illustrate that TABLEINFO really did trigger it.)

Keyword rules

Keywords are validated when BroadSQL starts:

• At least one keyword, and none of them blank.
• Only letters, digits, and spaces ([a-zA-Z1-9 ]*).
• Must not collide with a keyword already registered by a core command or another extension; the conflicting class is skipped (logged, not fatal) and the rest of BroadSQL still starts normally.

Packaging

Build your command classes into a plain JAR (with any of their own dependencies, since custom extension JARs are loaded independently) and copy it into extensions/. A JAR that fails to load, or a single incompatible class inside it, is logged and skipped; it will not prevent BroadSQL from starting or block other extensions from loading.

This is exactly the mechanism BATCHLOAD, EDIT, and LOAD (see the "Extension commands" section of the command reference) already use: they ship inside BroadSQL's own JAR rather than a JAR you drop in yourself, but they're loaded through the same class-scanning path as a custom command in extensions/, not a separate, more privileged mechanism.

BroadSQL Extension Kit

Everything above shows the mechanism and one worked example. The BroadSQL Extension Kit is a ready-to-build starting point on GitHub: a small Maven project with five working example commands, unit tests, and a build already set up to compile correctly against a real BroadSQL installation, so you can copy a working class instead of assembling one from scratch.

Prerequisites: JDK 21, Maven, and a working BroadSQL 5.0.x installation. The kit compiles against your own installed lib/broadsql.jar (installed into your local Maven repository with one documented command, since BroadSQL is closed source and not published to a public artifact repository): nothing else to configure. Each example class's own comments explain the specific thing it demonstrates: start with CommandHello, then copy whichever example is closest to what you are building. Full setup steps are in the repository's own README.

The kit's example code and build setup are MIT licensed, so you're free to copy and adapt them; BroadSQL itself remains separate, closed source, commercially licensed software, and a valid installation is still required to build or run anything from the kit.

Get the BroadSQL Extension Kit on GitHub