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
  • JSON (JSONArray or JSONObject)

But, as an example, it won't support :

  • List types
  • Map types

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

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

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);
        Query query = new Query(getSession(), "SELECT * FROM journey where name = ${journey.name}", map, Journey[].class);
        return (Journey[])query.executeQuery();
    }
}