bx

BX is a collection of frequently-used utility code. It integrates the following 3rd party packages:

• guava
• unirest-java
• jackson v3
• spring-jdbc
• HikariCP
• DuckDB

SQL Support

BX does a few things to make working with SQL databases simple.

DB Access

Setting up pooled database access is very easy. Set DB_URL, DB_USERNAME and DB_PASSWORD as environmental variables and you will have access to a pooled DataSource backed by Hikari.

export DB_URL=jdbc:postgresql://host/database
export DB_USERNAME=mydbuser
export DB_PASSWORD=mypassword

The database is then accessible

var db = Db.db(); // access a global Db instance
var dataSource = db.getDataSource();

Console Query

ConsoleQuery.withPrimaryDb().select("select * from actor");

┌───────────┬─────────────────────┐
│    id     │        name         │
│  integer  │       varchar       │
├───────────┼─────────────────────┤
│        1  │  Leonardo DiCaprio  │
│        2  │  Chase Infiniti     │
│        3  │  Benicio del Toro   │
└───────────┴─────────────────────┘

Console query has fluent integration with Spring JDBC. For instance:

    ConsoleQuery.withPrimaryDb()
        .select(c -> c.sql("Select * from actor where id=:id").param("id", 1));

will generate:

┌───────────┬─────────────────────┐
│    id     │        name         │
│  integer  │       varchar       │
├───────────┼─────────────────────┤
│        1  │  Leonardo DiCaprio  │
└───────────┴─────────────────────┘

This is equivalent if you find it cleaner:

    ConsoleQuery.withPrimaryDb()
    .select("Select * from actor where id=:id",c->c.param("id", 1));

CSV Import

This simple example will create a table and import 3 rows. Very useful for tests.

 jdbcClient.sql("create table actor(id int, name varchar(30))").update();
    
    String csv = """
            id,name
            1,Leonardo DiCaprio
            2,Chase Infiniti
            3,Benicio del Toro
            """;
    
    CsvImport.into(dataSource).fromString(csv).table("actor").importData();
    
    ConsoleQuery.with(dataSource).table("actor").show();

or from a file:

CsvImport.into(dataSource).from(new File("data.csv").table("actor").importData();

CSV Export

The fillowing will export some data to a CSV:

    CsvExport.from(dataSource)
        .to(new File("./output.csv"))
        .sql(
            c ->
                c.sql("select flight,ac_reg from adsb where flight=:flight")
                    .param("flight", "SWA3880"))
        .export();

If the data is small and you want it in a String:

    String output =
        CsvExport.from(dataSource)
            .sql(
                c ->
                    c.sql("select flight,ac_reg from adsb where flight=:flight")
                        .param("flight", "SWA3880"))
            .exportToString();

DuckDB Support

DuckDB is a fantastic embedded analytics database. In addition to analytics queries, it's very useful for CSV import/export and in-memory data transformation.

Usage

Obtaining DuckDB DataSource

// Create a DataSource to access an in-memory DuckDB instance
var ds = DuckDataSource.createInMemory();
    
// Use Spring JDBC to access the database
var client = JdbcClient.create(ds);

In the example above, the DataSource is special in that it only contains a single connection and the connection is protected from being closed. This enables it to be used in a standard JDBC pool without the databse being destroyed after each operation.

To close the databse and the DataSource, the DataSource that is returned has a close() method that can be called.

ds.close(); // closes the DataSource and the database

To obtain a file-based DuckDB DataSource:

var ds = DuckDataSource.create("jdbc:duckdb:./mydb.duckdb");

DuckTable

There is a class, DuckTable that provides many convenience methods for working with DuckDB:

var table = DuckTable.of(dataSource,"employee");

Count rows:

table.rowCount();

Rename 'employee' table to 'worker':

table.rename("employee","worker");

Rename a column:

table.rename("id","employee_id");

Drop a column:

table.dropColumn("last_name");

Drop all columns except those specified:

table.dropColumnsExcept("id","first_name","last_name");

Rename the table:

var newTable = table.renameTable("literary_work");

Create a table, insert a row, and look at the contents:

c.sql("create table book( name varchar(30), author varchar(30))").update();

		c.sql("insert into book (name,author) values (:name,:author)").param("name", "Moby Dick")
				.param("author", "Herman Melville").update();

		t.show();

┌─────────────┬───────────────────┐
│    name     │      author       │
│   varchar   │      varchar      │
├─────────────┼───────────────────┤
│  Moby Dick  │  Herman Melville  │
├─────────────┴───────────────────┤
│ 1 row                           │
└─────────────────────────────────┘

Add two more books using DuckDB's appender interface:

var appender = t.createAppender();

appender.beginRow();
appender.append("Thus Spoke Zarathustra");
appender.append("Friedrich Nietzsche");
appender.endRow();

appender.beginRow();
appender.append("As I Lay Dying");
appender.append("William Faulkner");
appender.endRow();

appender.close();
		
t.show();

Output:

┌──────────────────────────┬───────────────────────┐
│           name           │        author         │
│         varchar          │        varchar        │
├──────────────────────────┼───────────────────────┤
│  Moby Dick               │  Herman Melville      │
│  Thus Spoke Zarathustra  │  Friedrich Nietzsche  │
│  As I Lay Dying          │  William Faulkner     │
├──────────────────────────┴───────────────────────┤
│ 3 rows                                           │
└──────────────────────────────────────────────────┘