Datatype

In this section, we will review the PostgreSQL datatypes you can use with Spgeed.

Array type

PostgreSQL allows you to use an array type for column definition. Spgeed can help you deal with this data type thanks to the array pipe function, as we saw previously within the Expressions section.

Example: Let's create a table called "hotel". An hotel will be defined by a name and a list (array) of services.

CREATE TABLE IF NOT EXISTS hotel (name TEXT, services TEXT[])

Within your application, the according POJO would be:

public class Hotel {
    public String name;
    public String[] services;
}

You can then perform a select query to the hotel table like this:

@Select(sql = "SELECT * FROM hotel");
Hotel[] getAll();

And here is how you can insert or update hotel data:

@Update(sql = "INSERT INTO hotel (name, services) VALUES (${hotel.name}, ${hotel.services | array()})");
int createHotel(Hotel hotel);

Row type

PostgreSQL allows you to use composite types. Spgeed can help you deal with this data type thanks to the row pipe function, as we saw previously within the Expressions section.

Example: let's play with hotels locations. To do so, we create a composite type called location which is defined by a latitude and an longitude.

CREATE TYPE location AS (latitude FLOAT, longitude FLOAT);

CREATE TABLE IF NOT EXISTS hotel (name TEXT, address location)

Inside your application, you can define the according POJOs:

public class Hotel {
    public String name;
    public Location address;
}

public class Location {
    public float latitude;
    public float longitude;
}

Here is how you can perform a select query:

@Select(sql = "SELECT * FROM hotel");
Hotel getAll();

When you want to perform an insert or update, you need to use the row pipe function:

@Update(sql = "INSERT INTO hotel (name, address) VALUES (${hotel.name}, ${hotel.address | row('latitude', 'longitude')})");
int createHotel(Hotel hotel);

JSON type

PostgreSQL allows you to use JSON type as a column type. Spgeed can help you deal with this data type thanks to the json pipe function, as we saw previously within the Expressions section.

Example: let's play with hotels rooms. Imagine we want to keep track of the price of each room.

CREATE TABLE IF NOT EXISTS hotel (name TEXT, rooms JSONB)

The according POJO might look like this:

public class Hotel {
    public String name;
    public Map<String, Float> rooms;
}

The rooms attribute is a map : room names will be the key and room prices will be the value.

Here is how you can retrieve the hotels list:

@Select(sql = "SELECT * FROM hotel");
Hotel getAll();

When performing an insert or update, you should to use the json pipe function. (However, by defaut, maps attributes are always implicitly converted to a json parameter):

@Update(sql = "INSERT INTO hotel (name, rooms) VALUES (${hotel.name}, ${hotel.rooms | json()})");
int createHotel(Hotel hotel);

File type

PostgreSQL allows you to use a binary type as a column type. We recommend to store all your files in a special table.

Example :

CREATE TABLE IF NOT EXISTS file (name TEXT, content BYTEA)

public class File {
    public String name;
    public byte[] content;
}

Here is how you can retrieve all the files stored into the file table:

@Select(sql = "SELECT * FROM file");
File getFile();

And here is how you update or insert files into the file table:

@Update(sql = "INSERT INTO file (name, content) VALUES (${file.name}, ${file.content})");
int createFile(File file);

Sequence

Spgeed allows you to manipulate the sequences of your database within statements. To do so, you just need to use the PostgreSQL syntax.

Example: let's play with bill numbers.

CREATE SEQUENCE IF NOT EXISTS bill_seq;

CREATE TABLE IF NOT EXISTS journey (
    name            TEXT,
    billnumber      INTEGER   DEFAULT nextval('bill_seq')
);

ALTER SEQUENCE bill_seq OWNED BY journey.billnumber;

From within you application, you can play with the sequence like this :

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

UUID type

PostgreSQL allows you to use UUID types. UUID can be very useful to create technical identifiers or unique tokens. In this sub section, we will see how we can rely on Spgeed to generate a UUID within our application and store it into the database.

You can generate a correct UUID with JAVA with the following command:

UUID id = UUID.randomUUID();

Example: let's store hotel identifiers.

CREATE TABLE IF NOT EXISTS hotel (id UUID)

@Update(sql = "INSERT INTO hotel (id) VALUES (${id})");
int createHotel(UUID id);

Then, to store a new hotel identifier:

hotelDao.createHotel(UUID.randomUUID());