SQL Builder

SQLBuilder is one of the fantastic parts in AbacusUtil. Writing and maintaining mass of SQL statements in String used to be a big trouble in the development of a lot of backend applications. SQLBuild turns it into a pleasure to write much more readable/maintainable SQL with the concise APIs. Let's experience it by a few of examples:

String sql = E.select("id", "firstName", "lastName").from("account").where(L.eq("id", 1)).sql();
// sql: SELECT id AS "id", first_name AS "firstName", last_name AS "lastName" FROM account WHERE id = 1
sqlExecutor.query(sql).println();

// Generate the parameterized sql with '?' and improve the performance/security by PreparedStatement.
Pair pair = RE.select("id", "firstName", "lastName").from("account").where(L.eq("id", 1)).pair();
// sql: SELECT id AS "id", first_name AS "firstName", last_name AS "lastName" FROM account WHERE id = ?
sqlExecutor.query(pair.sql, pair.parameters).println();

// Generate the parameterized sql with named parameters and improve the performance/security by PreparedStatement,
// and supports entity/map as parameters.
pair = NE.select("id", "firstName", "lastName").from("account").where(L.eq("id", 1)).pair();
// sql: SELECT id AS "id", first_name AS "firstName", last_name AS "lastName" FROM account WHERE id = :id
sqlExecutor.query(pair.sql, pair.parameters).println();

(If you're confused by short name: E, RE, NE, L, N. Refer to SQLBuilder for (E, RE, NE). L is the short name for the factory of structured condition. N is a general utility class for String/Array..., introduced at: N)

We can do even better by removing the magic Strings with the constants in the auto-generated classes for property name. It will improve the maintainability.

String sql = E.select(ID, FIRST_NAME, LAST_NAME).from(Account._).where(L.eq(ID, 1)).sql();
sqlExecutor.query(sql).println();

// Actually we don't need and should not set the concrete parameters for generation of parameterized sql,
// Just set parameter value with question mark expression.
sql = RE.select(ID, FIRST_NAME, LAST_NAME).from(Account._).where(L.eq(ID, L.QME)).sql();
sqlExecutor.query(sql, 1).println();

// Parameters with format: Array/List/Map/Entity are supported by named SQL
sql = NE.select(ID, FIRST_NAME, LAST_NAME).from(Account._).where(L.eq(ID, L.QME)).sql();
sqlExecutor.query(sql, 1).println();

Or use the class directly:

String sql = NE.selectFrom(Account.class).where(eq(ID)).sql();
// sql: SELECT id AS "id", gui AS "gui", first_name AS "firstName", last_name AS "lastName", status AS "status", last_update_time AS "lastUpdateTime", create_time AS "createTime", devices AS "devices" FROM account WHERE id = :id

Here is the performance test:

Profiler.run(new Runnable() {
    @Override
    public void run() {
        String sql = E.insert("gui", "firstName", "lastName", "lastUpdateTime", "createTime").into("account").sql();
        // sql: INSERT INTO account (gui, first_name, last_name, last_update_time, create_time) VALUES (?, ?, ?, ?, ?)
        assertEquals(102, sql.length());

        sql = NE.select("gui", "firstName", "lastName", "lastUpdateTime", "createTime").from("account").where(L.eq("id", 1)).sql();
        // sql: SELECT gui AS "gui", first_name AS "firstName", last_name AS "lastName", last_update_time AS "lastUpdateTime", create_time AS "createTime" FROM account WHERE id = :id
        assertEquals(166, sql.length());
    }
}, 16, 200000, 3).printResult();