Why did I implement my own Java-SQL framework

Many libraries focus on Object Relational Mapping (ORM) and some on Data Definition Language (DDL). Each library supports a subset of SQL: 

1. ORM: select, insert, update, delete
2. DDL: create table, create schema, create database

But if we need a combination of commands like in:
    create table t2 as
    select *
    from t1
we are doomed because only a small subset of libraries is expressive enough to cover this scenario.

To make things worse, majority of database frameworks focus on Online Transaction Processing (OLTP) but I have to work with Data Warehouse (DW) databases:

1. OLTP: Oracle, MSSQL, PostgreSQL, MySQL, ...
2. DW: Netezza, Teradata, Hive, GreenPlum, ...

Hence the list of complying libraries gets even smaller. In the end I ended up with following candidates:

1. SwissSQL
2. General SQL Parser
3. JSQLParser

SwissSQL is a nice library - you write the commands in Oracle dialect and they get transparently translated into the dialect you just need. Unfortunately, the library is not developed anymore.

General SQL Parser allows you to construct queries from the scratch. However, the verbosity has put me away from this product.

Finally JSQLParser comes to save the day. The developer is responsive and is willing to implement new features. However, it still takes a month until the new feature is implemented and propagated into the release. Hence there is a month, during which I have survive on my own code.

PS: Neither LINQ from Microsoft have support for DDL. And it lacks support for many DW databases like Netezza.

How to write database agnostic java application

There are several best practices:

1. Use some Java Persistent API, if you can (like Hibernate, JOOQ or JSQLParser).
2. Use jdbc functions whenever possible. Do you want to retrieve a list of tables? Do it with getMetaData(). If you do it with, for example, information_schema, then it's not going to work on Oracle.
3. If you have to directly write SQL, limit yourself to subset of commands supported by MySQL - only a few relational databases support a smaller subset of SQL commands (the only one I can think of is SQLite). Hence, if your application works on MySQL, it's likely to be more or less portable to any other database.
4. Build good unit tests. You are going to need them.