Documentation for the "finders" JSON key.

Finders will most likely be the most often used elements of h2zero and can be configured in a variety of ways.

As the name implies, 'finders' provide methods to retrieve specific rows from the database. Finders by default return the entire row as a POJO. Finders can also be configured to return a subset of fields through a selectClause bean.

finders is a JSON Array of JSON Objects within the table object.

"tables": [
	{
		"name": "table_name",
		"fields": [
			{ ... }
		],
		"finders": [
			{ ... }
		]
	},
	...
]

Finder Types

The types of finders are as follows:

  • default finder
    • When you want to select a subset of the rows from the database
    • This maps to a sql query select * from table where condition = something;
    • This will return either a single POJO or a List of POJOs
  • select clause finder
    • When you want to select a subset of fields for a subset of rows from the database
    • This maps to a sql query select field_1, field_2 from table where condition = something;
    • This will return either single selectClauseBean or a List of selectClauseBeans containing only the values for field_1 and field_2
  • field finders (see fieldFinders)
    • This is a shortcut method to generate finders based on a field or fields
    • When This maps to a sql query select * from table where field_1 = something and field_2 = something; and will return a complete row.
    • This will return either a single POJO or a List of POJOs

Alternates to the select clause finder

Rather than configuring selectClause bean (more on this below) type queries, the following shortcut finder generators are also available:

counters
For simple counts on objects that return one and only one Integer value per query.
questions
For simple questions on objects that return one and only one Boolean value per query.

Allowable keys for a Finder JSON Object

key namekey value typeusagenotes
nameStringmandatoryThis is the name for the finders. If not present on the JSON object an H2ZeroParseException will be thrown.
selectClauseStringoptionalFor 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.

This will generate a selectClauseBean and is useful when you only wish to return a subset of the columns in a table.
selectFieldsJSON Array (String)optional / mandatorySelect fields are optional / mandatory for the finders JSON object.

If a selectClause is included, then there MUST exist a value for each of the Fields in the select clause.
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)optionalWhether the SQL query will return a single object or an List of objects.

If this interaction is expected to return a unique result, or an ArrayList of results. By default this will return multiple results.

Default Finders

By default Finders are designed to return either a unique object or an List of objects, which contain the complete row record or records respectively. 

"finders": [
	{

		"name": "findByTxtAddressEmailTxtPassword",
		"whereClause": "where txt_address_email = ? and txt_password = ?",
		"whereFields": [
			"txt_address_email",
			"txt_password"
		],
		"unique": true
	}
]

This will return a unique row (if it exists) on a lookup by txt_address_email and txt_password. Depending on the method signature, if the row cannot be found an exception will be thrown. if the *Silent method signature is used, then a null result will be returned.

With the following method signatures

public static User findByTxtAddressEmailTxtPassword(Connection connection, String txtAddressEmail, String txtPassword, Integer limit, Integer offset) throws H2ZeroFinderException, SQLException 
public static User findByTxtAddressEmailTxtPassword(Connection connection, String txtAddressEmail, String txtPassword) throws H2ZeroFinderException, SQLException 
public static User findByTxtAddressEmailTxtPassword(String txtAddressEmail, String txtPassword, Integer limit, Integer offset) throws H2ZeroFinderException, SQLException 
public static User findByTxtAddressEmailTxtPassword(String txtAddressEmail, String txtPassword) throws H2ZeroFinderException, SQLException 
public static User findByTxtAddressEmailTxtPasswordSilent(Connection connection, String txtAddressEmail, String txtPassword, Integer limit, Integer offset) 
public static User findByTxtAddressEmailTxtPasswordSilent(Connection connection, String txtAddressEmail, String txtPassword) 
public static User findByTxtAddressEmailTxtPasswordSilent(String txtAddressEmail, String txtPassword, Integer limit, Integer offset) 
public static User findByTxtAddressEmailTxtPasswordSilent(String txtAddressEmail, String txtPassword)
Please see the method signatures page for information about usage patterns for the various method signatures that are generated.

If the "taglibs" option is enabled, this will also generate a tag library (and its associated entry in the TLD) file.

  • src/main/java/synapticloop/sample/h2zero/taglib/user/FindByTxtAddressEmailTxtPasswordTag.java
    [plain java | pretty-printed]

selectClause Finders - subset of fields

In some instances it is desirable to only return specific fields from the database, or to use in-built MySQL language functions that do not map directly to a specific field in the table. In this case the selectClause finder is useful.

"finders": [
	{
		"name": "findNmUserDtmSignup",
		"selectClause": "select nm_user, dtm_signup from user",
		"selectFields": [
			{ "name": "nm_user", "type": "varchar" },
			{ "name": "dtm_signup", "type": "datetime" }
		],
		"unique": false
	}
]

This will return multiple rows (if they exists) on a lookup by nm_user and dtm_signup. Depending on the method signature, if the rows cannot be found an exception will be thrown. if the *Silent method signature is used, then a null result will be returned.

Due to the fact that the entire table row is not being selected (only two of the fields are being selected) a bean is also generated for the returned results, which will contain getters and setters for all of the selected fields. The bean name is <finderName>Bean. In the above example the bean name will be findNmUserDtmSignupBean

With the following method signatures

public static List<FindNmUserDtmSignupBean> findNmUserDtmSignup(Connection connection, Integer limit, Integer offset) throws H2ZeroFinderException, SQLException 
public static List<FindNmUserDtmSignupBean> findNmUserDtmSignup(Connection connection) throws H2ZeroFinderException, SQLException 
public static List<FindNmUserDtmSignupBean> findNmUserDtmSignup(Integer limit, Integer offset) throws H2ZeroFinderException, SQLException 
public static List<FindNmUserDtmSignupBean> findNmUserDtmSignup() throws H2ZeroFinderException, SQLException 
public static List<FindNmUserDtmSignupBean> findNmUserDtmSignupSilent(Connection connection, Integer limit, Integer offset) 
public static List<FindNmUserDtmSignupBean> findNmUserDtmSignupSilent(Connection connection) 
public static List<FindNmUserDtmSignupBean> findNmUserDtmSignupSilent(Integer limit, Integer offset) 
public static List<FindNmUserDtmSignupBean> findNmUserDtmSignupSilent()
Please see the method signatures page for information about usage patterns for the various method signatures that are generated.

If the "taglibs" option is enabled, this will also generate a tag library (and its associated entry in the TLD) file.

selectClause Finders - MySQL in-built functions

In some instances it is desirable to only return specific fields from the database, or to use in-built MySQL language functions that do not map directly to a specific field in the table. In this case the selectClause finder is useful.

"finders": [
	{
		"name": "findGroupNumAge",
		"selectClause": "select count(*) as num_count, num_age from user group by num_count",
		"selectFields": [
			{ "name": "num_count", "type": "int" },
			{ "name": "num_age", "type": "int" }
		],
		"unique": false
	}
]

This will return multiple rows (if they exists) on a lookup by nm_user and dtm_signup. Depending on the method signature, if the rows cannot be found an exception will be thrown. if the *Silent method signature is used, then a null result will be returned.

Due to the fact that the entire table row is not being selected (only two of the fields are being selected) a bean is also generated for the returned results, which will contain getters and setters for all of the selected fields. The bean name is <finderName>Bean. In the above example the bean name will be findNmUserDtmSignupBean

With the following method signatures

public static List<FindNmGroupNumAgeBean> findGroupNumAge() throws H2ZeroFinderException, SQLException 
public static List<FindNmGroupNumAgeBean> findGroupNumAge(Connection connection) throws H2ZeroFinderException, SQLException 
public static List<FindNmGroupNumAgeBean> findGroupNumAgeSilent() 
public static List<FindNmGroupNumAgeBean> findGroupNumAgeSilent(Connection connection)
Please see the method signatures page for information about usage patterns for the various method signatures that are generated.

If the "taglibs" option is enabled, this will also generate a tag library (and its associated entry in the TLD) file.