Clauses and Fields
selectClause
Select clauses MUST start with select. A selectClause can be used in the following circumstances:
- finders optional - this will create a select clause bean for a finder, where only a certain subset of field will be returned
- counters mandatory - this MUST always return an integer count value (i.e.
select count(*)) - questions mandatory - this MUST always return a boolean value (i.e.
select (count(*) > 0))
finders
When you only want to select a subset of the fields from a table or table. As we cannot determine what the returned fields will be - you must always include the selectFields key as well.
counters
This is a mandatory key for counters, and must always return an integer count value. You MUST NOT include the selectFields as it must always return an integer.
questions
This is a mandatory key for questions, and must always return an boolean value. You MUST NOT include the selectFields as it must always return an boolean.
selectFields
Select fields provide h2zero with the details to bind the returned values. The format is as follows, with a name and type keys.
"selectFields": [
{ "name": "id_agent", "type": "bigint" },
{ "name": "id_organisation", "type": "bigint" },
{ "name": "count", "type": "int" }
]Note: you do not need to place any other keys on the select fields, just the name and the type.
whereClause
A where clause allows you to retrieve only a subset of the data from the table, there is documentation below for the following:
- standard whereClause - As the name suggests, the standard where clause where all the fields to be filtered on exist within the current table.
- whereClause with in: fields - For denoting that you wish to pass in an array of variables to the clause
standard whereClause
For a simple where, you can just write a simple clause. All of the input parameters exist on the current table, so the whereFields entries are just straight references to the table.
"finders": [
{
"name": "findByIdUserNumAge",
"whereClause": "where id_user = ? and num_age = ?",
"whereFields": [
"id_user",
"num_age"
]
"unique": true
}
]If you require a field from a separate table, then you can use the dot (.) notation to reference that table
"finders": [
{
"name": "findByIdUserNumAge",
"whereClause": "where id_user = (select id_user from user_repository where id_user_repository = ?) and num_age = ?",
"whereFields": [
"user_repository.id_user_repository",
"num_age"
]
"unique": false
}
]whereClause with in: fields
The where can use any of the normal SQL statements that you would find, e.g. cross-selects, joins, parameters etc. However, there are some specific sytnactical features that need to be inserted for 'in clauses'.
For example:
"finders": [
{
"name": "findByNumAgeIn",
"whereClause": "where num_age in (...)",
"whereFields": [
"in:num_age"
]
"unique": false
}
]
Note the in (...) which informs h2zero that a variable list of parameters may be queried on, this is then matched to the where fields in:num_age which determines the field lookup and generation.
Consequently the following method signatures are available:
public static List findByNumAgeIn(List<Integer> numAgeList) throws H2ZeroFinderException, SQLException
public static List findByNumAgeIn(Connection connection, List<Integer> numAgeList) throws H2ZeroFinderException, SQLException
public static List findByNumAgeInSilent(List<Integer> numAgeList)
public static List findByNumAgeInSilent(Connection connection, List<Integer> numAgeList)
Please see the method signatures page for information about usage patterns for the various method signatures that are generated.whereFields
whereFields with alias fields
If a finder has multiple where fields of the same name, h2zero will de-duplicate the fields in the method signature, in some instances, you may wish to pass in the same field with different values.
For example:
"finders": [
{
"name": "findByNumAgeBetween",
"whereClause": "where num_age > ? and num_age < ?",
"whereFields": [
{ "name": "num_age", "alias": "numAgeMin" },
{ "name": "num_age", "alias": "numAgeMax" }
],
"unique": false
}
In the above example, the num_age field is used twice, with differing values. To differentiate between the passed in parameters, an alias is used to generate the following method signatures:
public static List findByNumAgeBetween(Integer numAgeMin, Integer numAgeMax) throws H2ZeroFinderException, SQLException
public static List findByNumAgeBetween(Connection connection, Integer numAgeMin, Integer numAgeMax) throws H2ZeroFinderException, SQLException
public static List findByNumAgeBetweenSilent(Integer numAgeMin, Integer numAgeMax) throws H2ZeroFinderException, SQLException
public static List findByNumAgeBetweenSilent(Connection connection, Integer numAgeMin, Integer numAgeMax) throws H2ZeroFinderException, SQLException
Please see the method signatures page for information about usage patterns for the various method signatures that are generated.