Jak efektivně pracovat s JDBC ve skriptech Java 17

Rád bych vám představil Java třídu s necelými 170 řádky kódu pro usnadnění práce s SQL dotazy volané přes JDBC API. Čím je tohle řešení zajímavé? Třídu lze vložit do skriptu v jazyce Java verze 17.

Výhodou skriptu Java je snadná přenositelnost v textovém formátu a možnost spuštění bez předchozí kompilace, přitom máme za běhu k dispozici značné prostředky ze standardní knihovny toho jazyka. Využití skriptů se nabízí u nejrůznějších prototypů, ve kterých lze (po připojení k databázi) řešit i komplikovanější datové exporty nebo datové konverze. Skripty jsou užitečné všude tam, kde implementaci nechceme (nebo nemůžeme) vkládat do standardního Java projektu.

Použití skriptu má však i nějaká omezení, například kód je nutné zapsat do jediného souboru. Při spuštění skriptu můžeme připojit všechny potřebné knihovny, ale ty budou mít zřejmě další závislosti a pouhé uvedení jejich výčtu na příkazové řádce může být frustrující. Komplikace spojené s distribucí takového skriptu asi není třeba zdůrazňovat. Z uvedených důvodů se domnívám, že externím knihovnám ve skriptech je lepší se vyhnout. Pokud chceme jít i nadále cestou skriptu, volba padne na čisté JDBC. Pro psaní SQL dotazů lze s výhodou využít víceřádkové textové literály, jednoduššímu zápisu přispívá také automatické uzavírání objektů typu PreparedStatement (implementující rozhraní AutoCloseable). V čem je tedy problém?

Z bezpečnostních důvodů je vhodné mapovat hodnoty SQL parametrů na značky otazníků. Za hlavní handicap JDBC považuji mapování parametrů pomocí pořadového čísla otazníku (začínajícího jedničkou). První verze mapování parametrů na SQL skript často dopadne dobře, riziko chyby se však zvyšuje s přibývajícím počtem parametrů a dodatečných úpravách SQL. Připomínám, že vložením nového parametru na první pozici je třeba následující řadu přečíslovat. Další komplikací je použití operátoru IN, protože pro každou hodnotu výčtu je v SQL šabloně třeba napsat otazník, který se musí mapovat na samostatný parametr. Pokud je seznam parametrů dynamický, musí být dynamický i výčet otazníků v SQL šabloně. Odladění většího počtu složitějších SQL nám může začít brát významný čas.

Na vkládání SQL parametrů pomocí String Templates si budeme muset ještě chvíli počkat. Vkládání SQL parametrů by však mohl usnadnit jednoduchý wrapper nad rozhraním PreparedStatement, který by (před voláním SQL příkazu) připojil parametry pomocí pojmenovaných značek ve stylu JPA (alfanumerický text začínající dvojtečkou). Wrapper by mohl také zjednodušit čtení dat z databáze (příkazem SELECT), pokud by umožnil řetězit potřebné metody do jediného příkazu. Nejlépe s návratovým typem Stream<ResultSet> Pro ladění či logování SQL dotazu by se občas hodila vizualizace SQL příkazu s připojenými parametry. Představuji vám třídu SqlParamBuilder. Prioritou implementace bylo pokrýt uvedené požadavky jedinou Java třídou s minimalistickým kódem. Programové rozhraní bylo inspirované knihovnou JDBI. Ukázky používají databázi H2 v režimu in-memory. Připojení databázového driveru však bude nezbytné.

void mainStart(Connection dbConnection) throws Exception {
    try (var builder = new SqlParamBuilder(dbConnection)) {
        System.out.println("# CREATE TABLE");
        builder.sql("""
                        CREATE TABLE employee
                        ( id INTEGER PRIMARY KEY
                        , name VARCHAR(256) DEFAULT 'test'
                        , code VARCHAR(1)
                        , created DATE NOT NULL )
                        """)
                .execute();

        System.out.println("# SINGLE INSERT");
        builder.sql("""
                        INSERT INTO employee
                        ( id, code, created ) VALUES
                        ( :id, :code, :created )
                        """)
                .bind("id", 1)
                .bind("code", "T")
                .bind("created", someDate)
                .execute();

        System.out.println("# MULTI INSERT");
        builder.sql("""
                        INSERT INTO employee
                        (id,code,created) VALUES
                        (:id1,:code,:created),
                        (:id2,:code,:created)
                        """)
                .bind("id1", 2)
                .bind("id2", 3)
                .bind("code", "T")
                .bind("created", someDate.plusDays(7))
                .execute();
        builder.bind("id1", 11)
                .bind("id2", 12)
                .bind("code", "V")
                .execute();

        System.out.println("# SELECT");
        List<Employee> employees = builder.sql("""
                        SELECT t.id, t.name, t.created
                        FROM employee t
                        WHERE t.id < :id
                          AND t.code IN (:code)
                        ORDER BY t.id
                        """)
                .bind("id", 10)
                .bind("code", "T", "V")
                .streamMap(rs -> new Employee(
                        rs.getInt("id"),
                        rs.getString("name"),
                        rs.getObject("created", LocalDate.class)))
                .toList();

        System.out.println("# PRINT RESULT OF: " + builder.toStringLine());
        employees.forEach(employee -> System.out.println(employee));
        assertEquals(3, employees.size());
        assertEquals(1, employees.get(0).id);
        assertEquals("test", employees.get(0).name);
        assertEquals(someDate, employees.get(0).created);
    }
}

record Employee (int id, String name, LocalDate created) {}
static class SqlParamBuilder {…}

Poznámky k ukázce použití:

• Instanci typu SqlParamBuilder lze recyklovat pro více SQL příkazů.  Po provolání příkazu lze parametry změnit a příkaz pustit znovu. Parametry se přiřadí do naposledy použitého objektu PreparedStatement.
• Metoda sql() automaticky uzavře interní objekt PrepradedStatement (pokud byl předtím nějaký otevřený).
• Pokud měníme skupinu parametrů (typicky pro operátor IN), je třeba poslat stejný počet pro stejný PreparedStatement. Jinak bude třeba znovu použít metodu sql().
• Po posledním vykonání příkazu je třeba objekt SqlParamBuilder explicitně uzavřít. Protože však implementujeme rozhraní AutoCloseable, stačí celý blok uzavřít do bloku try. Uzavírání nemá vliv na obsaženou databázovou connection.
• V Bash shellu lze ukázku spustit skriptem SqlExecutor.sh, který si umí stáhnout potřebný JDBC driver (tady pro databázi H2).
• Pokud máme raději Kotlin, můžeme zkusit Bash skript SqlExecutorKt.sh, který připravený kód Kotlinu migruje na skript a spustí ho.
• Nenechme se zmást tím, že třída je uložena v projektu typu Maven. Jedním z důvodů je snadné spouštění jUnit testů.
• Třída má licenci Apache License, Version 2.0 .

Zřejmě nejrychlejší způsob, jak vytvořit vlastní implementaci, je stáhnout si ukázkový skript, přepracovat metodu mainRun() a upravit parametry připojení k vlastní databázi. Pro spuštění použijte vlastní JDBC driver. Domovská stránka třídy je tady.