Annotations

Annotations are used by Spgeed to give you an easy way to write statements. Spgeed currently supports 3 types of annotations :

• @Select, for select statements
• @Update, for CRUD statements
• @Script, to run a SQL file with multiple statements

Annotations should be used in a DAO interface or in an abstract DAO class.

Example :

public interface JourneyDao {
    @Select(sql = "SELECT * FROM journey")
    Journey[] getJourneyArray();
    // ...
}

or

public abstract class JourneyDao {
    @Select(sql = "SELECT * FROM journey")
    public abstract Journey[] getJourneyArray();
    // ...
}

Select annotation

@Select annotation can be used to write a SELECT statement.

As Spgeed is based on Jackson parser, it will support the following return types :

• Primitive type
• POJO
• Array
• List
• Set
• JSONArray
• Map (useful to send JSON Object directly)
• List
• Chunk

Chunk type

Chunk type is a specific type, defined in Spgeed, to help you deal with pagination.

Chunk type extends ArrayList, exposing some additional informations :

• long getFetch(): desired amount of elements in the chunk
• long getFirst(): index of the first element in the chunk
• long getLast(): index of the last element in the chunk + 1 (will be used as the first index in next call)
• long getTotal(): total amount of results in database
• int size(): amount of elements really loaded (inherited from ArrayList)
• static init(fetch: long) : create a Chunk object configured with the given amount of desired element (getFetch() will return that value)
• static restart(fetch: long, firstIndex: long): same as init but with the possibility to indicate a first index offset.

Examples

Returning a primitive type:

@Select(sql = "SELECT count(*) FROM journey")
int getJourneyCount();

Returning a POJO:

@Select(sql = "SELECT * FROM journey where name = ${name}")
Journey getJourneyObject(String name);

Returning an array:

@Select(sql = "SELECT * FROM journey")
Journey[] getJourneyArray();

Returning a JSON object:

@Select(sql = "SELECT * FROM journey")
JSONArray getJourneyJSON();

Using Chunk to paginate results:

@Select(sql = "SELECT * FROM journey WHERE destination = ${destination}")
Chunk<Journey> getJourneyToDestination(Chunk previousChunk, String destination);

Chunk<Journey> journeys = JourneyDao.getJourneyToDestination(Chunk.init(10), "Paris");
...
Chunk<Journey> journeys = JourneyDao.getJourneyToDestination(Chunk.restart(10, 50), "Paris");

Update annotation

@Update annotation can be used to write INSERT, UPDATE, or DELETE statements.

The update annotation can return :

• an integer if the statement is not intended to return a result set. This integer will indicate how many rows have been updated.
• the same types as a Select annotation if the statement explicitly returns something.

Example

@Update(sql = "INSERT INTO journey (name, location) VALUES (${name}, ${location})")
int save(String name, String location);

Script annotation

@Script annotation can be used to execute a file containing SQL statements. It will return a boolean value :

• true if the srcipt is executed
• false, otherwise.

Warning

The file must be added to the classloader

Example

@Script(file = "updateData.sql")
boolean updateData();

Limit

For @Select, @Update and @Script annotations, you can used it to show up a slow request or to see how long a request is.

Example

@Select(sql = "SELECT * FROM journey", limit = "1s 23ms")
Journey[] getJourneys();

@Script(file = "updateData.sql", limit = "1h 2m 3s 500ms")
boolean updateData();

@Update(sql = "INSERT INTO journey (name, location) VALUES (${name}, ${location})", limit = "3s500ms")
int save(String name, String location);

A default value can be set with SqlSession

DataSource ds = /* get your datasource */;
SqlSession session = new SqlSession(ds);
String defaultLimit = "1s";

session.setDefaultQueryLimit(defaultlimit);

You can set the defaultLimit to "0s" to see how long every request are If you send a limit and the defaultlimit is set Spgeed will take the sent one

Role

For @Select and @Update annotations, you may need to precise the role you want to use when executing the statement. Usually, this is used to manage security roles directly in your application. You can actually also deal with security directly in PostgreSQL database.

Example

@Select(sql = "SELECT * FROM journey", roles = {"manager"})
Journey[] getJourneysWithUserRole();

If you want to know more about how you can manage security concerns with Spgeed, please consult the security section.

Customs statements

If you don't want to rely on annotations and get more control on statements definitions, you can create an abstract DAO class and implement SpgeedDao.

Example

public abstract class JourneyDao implements SpgeedDao {

    public Journey[] getJourneyManual(Journey journey) throws Exception {
        Map map = new HashMap();
        map.put("journey", journey);
        return getSession().executeQuery("SELECT * FROM journey where name = ${journey.name}", map, Journey[].class);
    }
}