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