Documentation for the "counters" JSON key.
Counters are simple SQL statements that return one, and only one integer value.
This is a light-weight way to get a count of something without going through a selectClause bean finder.
The key counters is a JSON Array on the table JSON object, for example:
"tables": [
{
"fields": [
...
],
"counters": [
...
]
}
]
Allowable keys for a Counter JSON Object
| key name | key value type | usage | notes |
|---|---|---|---|
| name | String | mandatory | This is the name for the counters. If not present on the JSON object an H2ZeroParseException will be thrown. |
selectClause | String | optional | For general SQL interactions: By default, the select query will be a select * from <table_name>, If you wish to over-ride this default behaviour, then this is the SQL query that will be run.For inserters SQL interactions: This is the SQL query that is used to insert the values for the table. This may also be a cross-table select.The select clause statement MUST return one and only one integer result. |
selectFields | JSON Array (String) | invalid | Select fields are invalid for the counters JSON object. The select clause is automatically generated with a single select field (which is of type integer). |
whereClause | String | optional | Used if the SQL query is parameterised. |
whereFields | JSON Array (String) | optional | The name for each of the whereClause parameters.If there is a whereFields key on the JSON object without any whereClause then this will generate a FATAL validation message. |
unique | boolean (default false) | ignored | Whether the SQL query will return a single object or an List of objects.This always returns one, and only one result in the result set of type integer and is therefore un-necessary. |
Automatically generated methods
Every table automatically has countAll methods generated, with the following signatures:
public static int countAll() throws SQLException
public static int countAll(Connection connection) throws SQLException
public static int countAllSilent()
public static int countAllSilent(Connection connection)
Example 1: Simple Count
"counters": [
{
"name": "countNumberOfUsers",
"selectClause": "select count(*) from user"
}
]
As it would appear, this would return the number of users in the table. Without a whereClause, these methods are identical to the countAll methods and consequently aren't necessary, however this is included for illustrative purposes.
The code that is generated for the above counter resides in the following file location.
src/main/java/synapticloop/sample/h2zero/counter/UserCounter.java
[plain java | pretty-printed]
With the following method signatures
public static int countNumberOfUsers() throws H2ZeroFinderException, SQLException
public static int countNumberOfUsers(Connection connection) throws H2ZeroFinderException, SQLException
public static int countNumberOfUsersSilent()
public static int countNumberOfUsersSilent(Connection connection)
Please see the method signatures page for information about usage patterns for the various method signatures that are generated.You may also use a whereClause and whereFields if you wish to pass in additional parameters.
Example 2: Parameterised Count
"counters": [
{
"name": "countNumberOfUsersOverAge",
"selectClause": "select count(*) from user"
"whereClause": "where num_user_age > ?",
"whereFields": [
"num_user_age"
]
}
]
In the above example, the count being given is the number of users that are older than a parameterised value that is passed in.
The code that is generated for the above counter resides in the following file location.
src/main/java/synapticloop/sample/h2zero/counter/UserCounter.java
[plain java | pretty-printed]
With the following method signatures
public static int countNumberOfUsersOverAge(Integer numAge) throws H2ZeroFinderException, SQLException
public static int countNumberOfUsersOverAge(Connection connection, Integer numAge) throws H2ZeroFinderException, SQLException
public static int countNumberOfUsersOverAgeSilent(Integer numAge)
public static int countNumberOfUsersOverAgeSilent(Connection connection, Integer numAge)
Please see the method signatures page for information about usage patterns for the various method signatures that are generated.Example 3: Parameterised Count with dual value parameter
"counters": [
{
"name": "countNumberOfUsersBetweenAge",
"selectClause": "select count(*) from user",
"whereClause": "where num_age > ? and num_age < ?",
"whereFields": [
{ "name": "num_age", "alias": "numAgeFrom" },
{ "name": "num_age", "alias": "numAgeTo" }
]
}
]
The code that is generated for the above counter resides in the following file location.
src/main/java/synapticloop/sample/h2zero/counter/UserCounter.java
[plain java | pretty-printed]
With the following method signatures
public static int countNumberOfUsersBetweenAge(Integer numAgeFrom, Integer numAgeTo) throws H2ZeroFinderException, SQLException
public static int countNumberOfUsersBetweenAge(Connection connection, Integer numAgeFrom, Integer numAgeTo) throws H2ZeroFinderException, SQLException
public static int countNumberOfUsersBetweenAgeSilent(Integer numAgeFrom, Integer numAgeTo)
public static int countNumberOfUsersBetweenAgeSilent(Connection connection, Integer numAgeFrom, Integer numAgeTo)
Please see the method signatures page for information about usage patterns for the various method signatures that are generated.Example 4: Parameterised Counter with in: designator fields
"questions": [
{
"name": "countUsersInAges",
"selectClause": "select count(*) > 0 from user",
"whereClause": "where num_age in (...)",
"whereFields": [
"in:num_age"
]
}
]
In the above example, the count being given is the number of users that have an age that equals that of the passed in list
Note both the whereClause having the ellipses (...) to designate that this is an in query and the whereFields using the specialised in: designator.
The code generation uses these two parts in order to correctly specify the method parameters and the way in which a PreparedStatement is constructed.
The code that is generated for the above counter resides in the following file location.
src/main/java/synapticloop/sample/h2zero/counter/UserCounter.java
[plain java | pretty-printed]
With the following method signatures
public static int countUsersInAges(List<Integer> numAgeList) throws H2ZeroFinderException, SQLException
public static int countUsersInAges(Connection connection, List<Integer> numAgeList) throws H2ZeroFinderException, SQLException
public static int countUsersInAgesSilent(List<Integer> numAgeList)
public static int countUsersInAgesSilent(Connection connection, List<Integer> numAgeList)
Please see the method signatures page for information about usage patterns for the various method signatures that are generated.