Statement

Within the current section, we will review how Spgeed can help you to integrate native PostgreSQL features.

Manage database schema creation within your application¶

Usually, when it comes to database creation, you need to create every aspect of it :

Types,
Tables,
Contraints,
Triggers,
...

You can do this incrementally inside PostgreSQL but you can also define this whole structure in a single SQL file. This file will contain the required statements to end up with the databse you want.

Once you created such a file, you can execute it directly in PostgreSQL:

psql databasename < script.sql

But you can also use Spgeed: thanks to @Script annotation, you can call the previous file directly into your application, during initialization phase for instance:

@Script(file = "script.sql");
boolean initDatabase();

Warning

The file needs to be in the classpath.

Actually, using Spgeed, you could also write each database creation step inside your application, using @Update annotation. However you would have to create one annotation for each creation statement.

Map a column name to a specific POJO attribute¶

Sometimes, in SELECT statements, you need to map a column name with another name. This is where AS keyword kicks in.

Here is an example:

Imagine you created the following POJO:

public class Journey {
    public String name;
}

And here is the table on which you want to perform a request:

CREATE TABLE IF NOT EXISTS journey (journeyname TEXT)

Now you want to retrieve a list of Journey POJO by performing a request to the journey table. And you want the name attribute of Journey POJO to be populated with the value of journeyname column from journey table.

Here is how to do that:

@Select(sql = "SELECT journeyname AS name FROM journey");
Journey[] getAll();

Perform multiple inserts¶

Spgeed allows you to create a unique statement to insert multiple rows in the database.

Here is an example:

@Update(sql = "INSERT INTO journey (name, location) VALUES (journeys | values('name', 'location')")
int save(List<Journey> journeys);

In your application, you just need to call the save method you just wrote :

List<Journey> journeys = Arrays.asList(
    new Journey("Guadeloupe", "Océan Atlantique");
    new Journey("Mayotte", "Océan Indien")
);
journeyDao.save(journeys);

Perform an upsert¶

An upsert is a database operation which consists of either inserting or updating a row into the database depending on weither the row is already present or not.

Here is an example on how you can do that with Spgeed :

@Update(sql = "INSERT INTO hotel (name, price) VALUES (${name}, ${price}) " +
               "ON CONFLICT (name) DO UPDATE SET price = EXCLUDED.price");
int saveHotel();

In the previous example, we try to insert/update hotels descriptions (name, price) into the database. Hotels are identified with a name. While inserting descriptions, if a conflict is found on a name, we just update the price. You can find more information about this on postgresql site.

Manage different fetch strategies¶

One-to-one / many-to-one fetch strategy: for a single-valued association, you can use JOIN keyword.

Here is an example :

@Select(sql = "SELECT j.*, h.* "
            + "FROM journey j JOIN hotel h ON (j.hotel = h.name) ");
Journey[] getAll();

In this case, we retrieve an array of Journey object. Each Journey object will be populated with the result of the query, trying to match each result column (from journey and hotel tables) into the POJO.

However, you might want to populate the POJO differently: usually, you don't want Journey objects to contain an attribute for each hotel property. Actually, you want it to have a single property called "hotel" of type Hotel inside the POJO. When you perform the query, you still expect the result to be an array of Journey object but you want each Journey object to contain a single Hotel object populated with the according hotel values.

Here is how you can do that : you need to use the same strategy as you would for one-to-many or many-to-many fetch strategy (see next sub-section). But instead of returning an array of objects, you should only return the first one. To do so, we use json_agg function. json_agg will aggregate all hotel properties into a json object, producing a json object for each matching hotel : this will return an array of matching hotels objects. The operator ->0 allows you to select the first item:

@Select(sql = "SELECT j.*, "
            + "(SELECT json_agg(h.*)->0"
            + "     FROM hotel h "
            + "     WHERE j.hotel = h.name "
            + ") as hotels"
            + "FROM journey j");
Journey[] getAll();

One-to-many / many-to-many fetch strategy: for many-valued association, you need to use a sub-statement and aggregate data with json_agg:

@Select(sql = "SELECT j.*, "
            + "(SELECT json_agg(h.*) "
            + "     FROM hotel h "
            + "     WHERE j.journey = j.name "
            + ") as hotels"
            + "FROM journey j");
Journey[] getAll();

Or you can do a WITH query which make it more readable:

@Select(sql = "WITH hotels as ( "
            + "     SELECT h.journey, json_agg(h.*) as hotels "
            + "     FROM hotel h "
            + "     GROUP BY h.journey "
            + ") "
            + "SELECT j.*, hs.hotels "
            + "FROM journey j "
            + "JOIN hotels hs ON hs.journey = j.name"
Journey[] getAll();

Finally, you could also use a simple JOIN but you will need to declare all your fields in the GROUP BY clause:

@Select(sql = "SELECT j.name, json_agg(h.*) as hotels "
            + "FROM journey j "
            + "JOIN hotel h ON h.journey = j.name "
            + "GROUP BY j.name;"
Journey[] getAll();

As a result for each example, we will end up with an array of Journey object. Each Journey object will contain an array of Hotel object.

Ordering one-to-many / many-to-many fetch strategy: for ordering many-valued association, we recommend to create a specific column in the first table. This column will contain an ordered list (ie, an array) of references to the other table.

Let's see an example :

CREATE TABLE IF NOT EXISTS journey (name TEXT, hotels UUID[])

We add an array of hotels UUIDs in the journey table. This array will define and keep track of an ordered hotels list.

Now, if we want to retreive all journeys and their associated ordered hotels list, we will rely on that new column :

@Select(sql = "SELECT j.*, "
            + "(SELECT json_agg(hotelFrom.*) FROM"
            + "     ( "
            + "         SELECT h.* FROM hotel h"
            + "         WHERE h.name = ANY (j.hotels)"
            + "         ORDER BY array_position(j.hotels, h.name)"
            + "     ) as hotelFrom"
            + ") as hotels"
            + "FROM journey j");
Journey[] getAll();

The sub-statement retrieve all rows in the array (= ANY) and order them (ORDER BY array_position)

As an alternative, you would also end up with the same result with the following statement:

@Select(sql = "SELECT j.*, "
            + "(SELECT json_agg(hotelFrom.*) FROM"
            + "     ( "
            + "         SELECT h.* FROM hotel h"
            + "         JOIN UNNEST(j.hotels)"
            + "         WITH ORDINALITY o(name, idx) USING (name)"
            + "         ORDER BY o.idx"
            + "     ) as hotelFrom"
            + ") as hotels"
            + "FROM journey j");
Journey[] getAll();

Use `WITH` keyword for complex and/or recursive statements¶

PostgreSQL introduced WITH Queries which is a way to declare a temporary table that will only exist for the given query.

Here is how you can use this with Spgeed:

@Select(sql = "WITH journeys as (SELECT * FROM journey WHERE name LIKE 'a%'), "
            + "hotels as (SELECT * FROM hotel WHERE name IN (SELECT hotel FROM journeys))"
            + "SELECT * FROM hotels");
Hotels[] getAllFromJourneyBeginByA();

And here is a recursive example:

@Select(sql = "WITH RECURSIVE t(n) AS (" +
            "VALUES (1) " +
            "UNION ALL " +
            "SELECT n+1 FROM t WHERE n < 100 " +
            ") " +
            "SELECT sum(n) FROM t"
        )
int recursive();

You can find more information on this here : PostgreSQL.

Perform simple search with `LIKE` operator¶

The LIKE operator allows you to perform basic search. You can use it with Spgeed the way you would use it in a traditional SQL query.

Example:

@Select(sql = "SELECT * FROM journey WHERE name LIKE ${query}");
Journey[] search(String query);

From within your application, you can then search for all trips where the trip's name starts with 'a':

journeyDao.search("a%");

Return a value on insert or update statement¶

Sometimes, it can be useful to return a value when performing an insert or update statement : for instance, you might want to retrieve the sequence number of the newly created data or the value of a computed column. Spgeed allows you to retrieve that value.

Here is an example demonstrating how you can retrieve the generated sequence value while performing an update:

@Update(sql = "UPDATE journey SET billnumber = nextval('bill_seq') WHERE name = ${name} RETURNING billnumber")
int generateBillnumber(String name);

Import data using CSV format¶

PostgreSQL allows you to import data into the database directly from a CSV file. To do so, you have to use the COPY keyword:

COPY zipcode FROM 'file.csv' DELIMITER ';' CSV

However, if you want to import data into an existing table, we do recommend to follow these steps:

Create a temporary table
Import your data into that temporary table
Update the existing table with the temporary table

You can find information about this here: PostgreSQL site.

How to factorize a statement¶

Sometimes, you can end up with similar pieces of statements duplicated many times into your code. It can be convenient to factorize them. When it happens, you should introduce a static string into your DAO interface or DAO class. Then, just you use that string by concatenating it within your sql annotations.

Here is an example:

public static String EXPENSIVE = " WHERE price >= 100 ";

@Select(sql = "SELECT * FROM journey" + EXPENSIVE)
Journey[] getExpensiveJourneys();

@Select(sql = "SELECT * FROM hotel" + EXPENSIVE)
Hotel[] getExpensiveHotels();

Statement

Manage database schema creation within your application¶

Map a column name to a specific POJO attribute¶

Perform multiple inserts¶

Perform an upsert¶

Manage different fetch strategies¶

Use WITH keyword for complex and/or recursive statements¶

Perform simple search with LIKE operator¶

Return a value on insert or update statement¶

Import data using CSV format¶

How to factorize a statement¶

Use `WITH` keyword for complex and/or recursive statements¶

Perform simple search with `LIKE` operator¶