Documentation for the "questions" JSON key.

Questions are simple SQL statements that return one, and only one boolean (true/false) value.

This is a light-weight way to get a boolean answer to a question without using select beans or counters

The key questions is a JSON Array on the table JSON object, for example:

"tables": [
	{
		"fields": [
			...
		],
		"questions": [
			...
		]
	}
]

Allowable keys for a Question JSON Object

key namekey value typeusagenotes
nameStringmandatoryThis is the name for the questions. If not present on the JSON object an H2ZeroParseException will be thrown.
selectClauseStringmandatoryFor 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 boolean result.
selectFieldsJSON Array (String)invalidSelect fields are invalid for the questions JSON object.

The select clause is automatically generated with a single select field (which is of type boolean).
insertClauseJSON ArrayinvalidThe insert clause is only valid for inserters and cannot be used for any other SQL interaction.

By default, the insert clause will be a generated with all of the fields from the <table_name>, however this may be over-ridden to include only non-nullable fields.

If you wish to over-ride this default behaviour, then this is the SQL query that will be run.
whereClauseStringoptionalUsed if the SQL query is parameterised.
whereFieldsJSON Array (String)optionalThe 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.
uniqueboolean (default false)ignoredWhether 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 boolean and is therefore un-necessary.

Example 1: Simple Question

"questions": [
	{
	"name": "doWeHaveMoreThanTwentyUsers",
	"selectClause": "select count(*) > 20 from user"
	}
]

Whilst rather contrived, this will return a true/false answer as to whether we have more than 20 users.

The code that is generated for the above question resides in the following file location.

With the following method signatures

public static boolean doWeHaveMoreThanTwentyUsers() throws H2ZeroFinderException, SQLException
public static boolean doWeHaveMoreThanTwentyUsers(Connection connection) throws H2ZeroFinderException, SQLException
public static boolean doWeHaveMoreThanTwentyUsersSilent()
public static boolean doWeHaveMoreThanTwentyUsersSilent(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 Question

"questions": [
	{
		"name": "doesUserNameExist",
		"selectClause": "select count(*) > 0 from user",
		"whereClause": "where nm_user = ?",
		"whereFields": [
			"nm_user"
		]
	}
]

In the above example, the question being asked is whether a particular username exists in the user table. If it does, the return value is true else false.

The code that is generated for the above question resides in the following file location.

With the following method signatures

public static boolean doesUserNameExist() throws H2ZeroFinderException, SQLException
public static boolean doesUserNameExist(Connection connection) throws H2ZeroFinderException, SQLException
public static boolean doesUserNameExistSilent()
public static boolean doesUserNameExistSilent(Connection connection)
Please see the method signatures page for information about usage patterns for the various method signatures that are generated.

Example 3: Parameterised Question with dual parameter query

"questions": [
	{
		"name": "doWeHaveUsersBetweenAgeExclusive",
		"selectClause": "select count(*) > 0 from user",
		"whereClause": "where num_age > ? and num_age < ?",
		"whereFields": [
			{ "name": "num_age", "alias": "numAgeFrom" },
			{ "name": "num_age", "alias": "numAgeTo" }
		]
	}
]

In the above example, the question being asked is whether we have users between two ages that are passed in as aprameters. If we do, the return value is true else false.

The code that is generated for the above question resides in the following file location.

With the following method signatures

public static boolean doWeHaveUsersBetweenAgeExclusive(Integer numAgeFrom, Integer numAgeTo) throws H2ZeroFinderException, SQLException
public static boolean doWeHaveUsersBetweenAgeExclusive(Connection connection, Integer numAgeFrom, Integer numAgeTo) throws H2ZeroFinderException, SQLException
public static boolean doWeHaveUsersBetweenAgeExclusiveSilent(Integer numAgeFrom, Integer numAgeTo)
public static boolean doWeHaveUsersBetweenAgeExclusiveSilent(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 Question with in: designator fields

"questions": [
	{
		"name": "doWeHaveUsersInAges",
		"selectClause": "select count(*) > 0 from user",
		"whereClause": "where num_age in (...)",
		"whereFields": [
			"in:num_age"
		]
	}
]

In the above example, the question being asked is whether we have users in a list of ages. If we do, the return value is true else false.

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 question resides in the following file location.

With the following method signatures

public static boolean doWeHaveUsersInAges(List<Integer> numAgeList) throws H2ZeroFinderException, SQLException
public static boolean doWeHaveUsersInAges(Connection connection, List<Integer> numAgeList) throws H2ZeroFinderException, SQLException
public static boolean doWeHaveUsersInAgesSilent(List<Integer> numAgeList)
public static boolean doWeHaveUsersInAgesSilent(Connection connection, List<Integer> numAgeList)
Please see the method signatures page for information about usage patterns for the various method signatures that are generated.