AbacusUtil Docs | Download | Javadoc | FAQ | Community

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 running on a laptop with: CPU: Intel i7-3520M 2.9GHz, Meory: 8G, OS: Windows 7 64-bit, JDK: 1.7.0_80:

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();

Test Result:

========================================================================================================================
(unit: milliseconds)
threadNum=16; loops=200000
totalElapsedTime: 5750

maxMethodTime(run): 244
minMethodTime(run): 0
method name avg time min time max time 0.01% >= 0.1% >= 1% >= 10% >= 20% >= 50% >= 80% >= 90% >= 99% >= 99.9% >= 99.99% >=
run 0.0139 0 244 1 1 1 0 0 0 0 0 0 0 0
========================================================================================================================

It just took about 5750 milliseconds to generate 6.4 million sql scripts with 16 threads. The avarage time is 0.0139 milliseconds and maxmium time is 244 milliseconds. 99.99% is less than 1 milliseconds.

Although it's super fast to generate the short sql, it's still one of the good practices to define the most used/static/long sql scripts in String constants or sql mapper xml file.