sobota 28. února 2015

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.

Žádné komentáře: