SQL data express (SDE)

What is SQL data express (SDE)?

SDE allows you to embed native sql query statements either in your code or in a property file.

SDE is a simple SQL Mapping tool which does not use XML files to manage SQL queries and database meta data. SDE is the foundation of Scooter Framework's ActiveRecord module. SDE can be used standalone.

There are many advantages of using Scooter Framework's SDE:

1. No xml mapping files

Developers do not need to maintain database table meta data in XML files. All database table meta data are detected automatically at run time.

2. Legacy database friendly

You not only can use SDE to execute sql statements, but also can call database views, functions and stored procedures.

3. Multiple databases support

SDE can connect to multiple databases at the same time.

4. Named queries

You do not need to keep your sql statements in your Java file. You can put them in a properties file and reference the property keys in your Java code.

Using SQL data express

The easist way to use SDE is through the SqlServiceClient class.

Execute SQL statement

Use named sql queries in your model class:

    //
    // retrieve records based on named sql: sqlKey maps to a sql statement in sql.properties file.
    //

    //in model class Pet.java:
    public static TableData getAllPets() {
        //maps to "getAllPets=select * from pets"
        return SqlServiceClient.retrieveTableDataBySQLKey("getAllPets");
    }

    public static TableData getWholeWorld() {
        //maps to "getWorldKey=select a.*, b.*, c.*, d.* from a, b, c, d where ..."
        return SqlServiceClient.retrieveTableDataBySQLKey("getWorldKey");
    }

Select Examples:

    //retrieve records based on embedded sql
    String sql = "SELECT * FROM pets WHERE name = ?name ORDER BY birth_date DESC";
    Map inputs = new HashMap();
    inputs.put("name", "Max");
    TableData td = SqlServiceClient.retrieveTableDataBySQL(sql, inputs);

    //retrieve rows based on embedded sql
    String sql = "SELECT * FROM pets WHERE name = ?name ORDER BY birth_date DESC";
    Map inputs = new HashMap();
    inputs.put("name", "Max");
    List rows = SqlServiceClient.retrieveRowsBySQL(sql, inputs);

    //retrieve the first row only
    RowData row = SqlServiceClient.retrieveOneRowBySQL(sql);

    //retrieve the object for the first row's first column
    String sql = "SELECT name FROM pets WHERE id = ?1";
    Map inputs = new HashMap();
    inputs.put("1", "12");
    Object petName = SqlServiceClient.retrieveObjectBySQL(sql, inputs);

Insert Examples:

    //insert a new record
    String sql = "INSERT INTO pets (name, type_id, owner_id) VALUES ('Pingping', 1, 10)";
    int insertCount = SqlServiceClient.executeSQL(sql);

    //insert a new record
    String sql = "INSERT INTO pets (name, type_id, owner_id) VALUES (?name, 1, 10)";
    Map inputs = new HashMap();
    inputs.put("name", "Pingping");
    int insertCount = SqlServiceClient.executeSQL(sql, inputs);

Update Examples:

    //update a new record based on a named sql: sqlKey maps to a sql statement
    //in sql.properties file.
    String sqlKey = "updatePetName";//maps to "UPDATE pets SET name = ?1 WHERE id = ?2)";
    Map inputs = new HashMap();
    inputs.put("1", "Wonda");
    inputs.put("2", "10");
    int updateCount = SqlServiceClient.executeSQLKey(sqlKey, inputs);

Delete Examples:

    String sql = "DELETE FROM pets WHERE name = ?name";
    Map inputs = new HashMap();
    inputs.put("name", "Pingping");
    int deleteCount = SqlServiceClient.executeSQL(sql, inputs);