Expressions

As we saw in the previous section, the annotation mechanism gives you the posibility to write statements very easily.

However, you are probably wondering how you can use this annotation mechanism to write complex statements, where you would need to prepare and inject data into it. Well, you will use expressions !

Expressions are here to help you with those operations. The next paragraphs will show you :

  • what is an expression
  • how you can use it
  • how far you can go with it

Operators

Internally, Spgeed holds a parser. When writing a statement within an annotation, the parser analyses what has been written in order to detect expressions and evaluate them.

To be identified as an expression, a substring needs to be encapsulated with a well defined operator (${}). Inside an expression, a few operators may also be used to manipulate data.

The supported operators are :

  • ${}, to create an expression
  • ., to navigate in an object
  • [], to navigate in an array
  • |, to call a pipe function

Example :

@Select(sql = "SELECT * FROM Journey WHERE destinationId = ${journey.destination.id})
Journey getJourneyWithSameDestination(Journey journey);

'.' operator and null values

${journey.destination.id} - if journey or destination is null, the parser will just inject null.

Pipe functions

Pipe functions are helpers.

They can be used in expressions to prepare and transform the data you want to inject in a statement. To use a pipe function, you need to create an expression and apply the pipe operator |.

${myParameter | myPipeFunction()} means that myPipeFunction will be applied to myParameter. The result of myPipeFunction will be injected in the statement.

Spgeed comes with a set of predefined pipe functions. We will describe each of them within the next paragraphs. However, you should notice that you can write your own pipe functions and that you can combine pipe functions all together to get what you need.

toParam

This is a default pipe function, which is actually implicitly called for every expression.

toParam asks JDBC to inject the value of a parameter in a SQL statement.

Example:

@Select(sql = "SELECT * FROM Journey WHERE name = ${name | toParam()})
Journey getJourneyByName(String name);

Same

${name | toParam}is the same as ${name}.

You can now call getJourneyByName within your application :

journeyDao.getJourneyByName("Guadeloupe");

This will generate the following SQL call :

SELECT * FROM Journey WHERE name = ?

Where ? will be replaced with Guadeloupe thanks to JDBC

toString

toString can be used in an expression to transform a parameter into a string.

Warning

Be careful, this function can lead to SQL injection vulnerability.

Example:

@Select(sql = "SELECT * FROM Journey WHERE name = '${name | toString()}')
Journey getJourneyByName(String name);

If we get back to the previous getJourneyByName example :

journeyDao.getJourneyByName("Guadeloupe");

This will now result in the following SQL call :

SELECT * FROM Journey WHERE name = 'Guadeloupe'

As you can notice, "Guadeloupe" value has been directly injected into the statement, which is the reason for the previous warning on SQL injection.

values

This pipe function is dedicated to insert statements.

values helps you to inject an array of parameters in an insert statement.

Example:

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

Now, let's try to save a few trips :

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

This will generate the following SQL call:

INSERT INTO journey (name, location) VALUES (?, ?), (?, ?)

Where ? attributes will be replaced thanks to JDBC with the following values: (Guadeloupe, Océan Atlantique), (Mayotte, Océan Indien).

Empty param

Sometimes you want to insert an array of objects into the database, and use those objects as is (ie. you don't want to extract any particular property from each object). To do so, just use the empty string as a parameter to the values function.

@Update(sql = "INSERT INTO persons (name) VALUES (names | values('')")
int save(List<String> names);

json

Postgresql supports JSON type as a column type. The json pipe function makes it easy to deal with this specific column type.

json injects a parameter in a JSON format into a statement.

Example:

@Update(sql = "UPDATE journey SET hotels = ${hotels | json()} WHERE name = 'Guadeloupe'")
int updateHotelsForGuadeloupe(List<String> hotels);

Let's update hotels :

List<Journey> hotels = Arrays.asList("First class", "Second class");
journeyDao.updateHotelsForGuadeloupe(hotels);

This will generate the following SQL call :

UPDATE journey SET hotels = ?

Where ? will be replaced thanks to JDBC with ["First class", "Second class"], which is the JSON format for the hotels list object.

array

Postgresql also supports ARRAY type as a column type. The array pipe function makes it easy to deal with this specific column type.

array injects parameters values within a statement using the array syntax.

Example:

@Update(sql = "UPDATE journey SET hotels = ${hotels | array()} WHERE name = 'Guadeloupe'")
int updateHotelsForGuadeloupe(List<String> hotels);

Let's update hotels :

List<Journey> hotels = Arrays.asList("First class", "Second class");
journeyDao.updateHotelsForGuadeloupe(hotels);

This will generate the following SQL call :

UPDATE journey SET hotels = ARRAY[?, ?]

Where ? will be replaced thanks to JDBC with "First class", "Second class"

row

Postgresql also supports complex type as a column type. As you have probably already figure it out, the row pipe function is here to help you deal with this feature.

row injects parameters values using the row syntax.

Example:

@Update(sql = "UPDATE hotel SET info = ${info | row('name', 'price', 'available')} WHERE id = '007'")
int updateHotelInfo(HotelInfo info);

Let's update an hotel description:

HotelInfo info = new Hotel("First class', 100, true);
journeyDao.updateHotelInfo(info);

This will generate the following SQL call:

UPDATE hotel SET info = ROW(?, ?, ?)

Where ? will be replaced thanks to JDBC with First class, 100 and true.

map

map extracts the desired attribute value of an object or a list of objects.

Same

${anObject.attribute}is the same as ${anObject | map('attribute')}.

Example:

@Update(sql = "UPDATE journey SET hotels = ${hotels | map('name') | array()} WHERE name = 'Guadeloupe'")
int updateHotelNamesForGuadeloupe(List<Hotel> hotels);

Let's call the previous JAVA function :

List<Journey> hotels = Arrays.asList(
    new Hotel("First class");
    new Hotel("Second class")
);
journeyDao.updateHotelNamesForGuadeloupe(hotels);

This will generate the following SQL call:

UPDATE journey SET hotels = ARRAY[?, ?]

Where ? parameters will be replaced thanks to JDBC with First class and Second class.

Create your own pipe functions

As we just saw, Spgeed offers you a set of built-in pipe functions which should help you deal with standard cases. However, if you have a special need, you can still create your own pipe function to address it.

sqlSession.addFunction("toStringAsParameter", (Query query, Object in, Object... args) -> {
    String result = query.addSqlParameter(in.toString());
    return result;
});