Class Fn

java.lang.Object
org.litebridge.orm.expression.Fn

public final class Fn extends Object
Functions: Utility class that provides static methods for constructing query expressions.

This class is a collection of static functions to create different types of select expressions within a database query. This includes selecting DTO fields, database expressions, or counting rows in a query.

This class cannot be instantiated.

  • Method Details

    • alias

      public static <DTO> DtoAliasSpec<DTO> alias(Class<DTO> dtoClass, String alias)
      Aliases the specified entity/mapped DTO class.

      The result can be used as a target for a `FROM` or `JOIN` clause.

      Type Parameters:
      DTO - The entity or mapped DTO class to alias.
      Parameters:
      dtoClass - The DTO class to alias.
      alias - The alias to use for the DTO class.
      Returns:
      a target specification for the specified DTO class and alias.
    • aliasTable

      public static SqlFromTargetSpec aliasTable(String table, String alias)
      Aliases the specified table.

      The result can be used as a target for a `FROM` or `JOIN` clause.

      Parameters:
      table - Name of the table to alias.
      alias - The alias to use for the table.
      Returns:
      a target specification for the specified table and alias.
    • alias

      public static SqlFromTargetSpec alias(Function<SelectApi,SelectTerminal<?>> query, String alias)
      Aliases the specified subquery.

      This allows the results from the subquery to be referenced in the parent query. It can be used as a target for a `FROM` or `JOIN` clause.

      Parameters:
      query - The subquery to alias.
      alias - The alias to use for the subquery.
      Returns:
      a target specification for the specified subquery and alias.
    • values

      public static ValuesSpec values(String tableAlias, String label, @Nullable Object value)
      Creates a SQL VALUES clause for a single column of hardcoded data.

      The VALUES clause be used in SELECT or MERGE statements to construct a temporary, inline virtual table of hardcoded data without needing a physical database table.

      Parameters:
      tableAlias - the alias of the virtual table to which the values belong
      label - the value's column label
      value - the value; can be null
      Returns:
      a new ValuesSpec instance containing the specified table alias, label, and value
    • values

      public static ValuesSpec values(String tableAlias, String l1, @Nullable Object v1, String l2, @Nullable Object v2)
      Creates a SQL VALUES clause for multiple columns of hardcoded data.

      The VALUES clause be used in SELECT or MERGE statements to construct a temporary, inline virtual table of hardcoded data without needing a physical database table.

      Parameters:
      tableAlias - the alias of the virtual table to which the values belong
      l1 - the first value's column label
      v1 - the first value; can be null
      l2 - the second value's column label
      v2 - the second value; can be null
      Returns:
      a new ValuesSpec instance containing the specified table alias, label, and value
    • values

      public static ValuesSpec values(String tableAlias, String l1, @Nullable Object v1, String l2, @Nullable Object v2, String l3, @Nullable Object v3)
      Creates a SQL VALUES clause for multiple columns of hardcoded data.

      The VALUES clause be used in SELECT or MERGE statements to construct a temporary, inline virtual table of hardcoded data without needing a physical database table.

      Parameters:
      tableAlias - the alias of the virtual table to which the values belong
      l1 - the first value's column label
      v1 - the first value; can be null
      l2 - the second value's column label
      v2 - the second value; can be null
      l3 - the third value's column label
      v3 - the third value; can be null
      Returns:
      a new ValuesSpec instance containing the specified table alias, label, and value
    • values

      public static ValuesSpec values(String tableAlias, String l1, @Nullable Object v1, String l2, @Nullable Object v2, String l3, @Nullable Object v3, String l4, @Nullable Object v4)
      Creates a SQL VALUES clause for multiple columns of hardcoded data.

      The VALUES clause be used in SELECT or MERGE statements to construct a temporary, inline virtual table of hardcoded data without needing a physical database table.

      Parameters:
      tableAlias - the alias of the virtual table to which the values belong
      l1 - the first value's column label
      v1 - the first value; can be null
      l2 - the second value's column label
      v2 - the second value; can be null
      l3 - the third value's column label
      v3 - the third value; can be null
      l4 - the fourth value's column label
      v4 - the fourth value; can be null
      Returns:
      a new ValuesSpec instance containing the specified table alias, label, and value
    • values

      public static ValuesSpec values(String tableAlias, String l1, @Nullable Object v1, String l2, @Nullable Object v2, String l3, @Nullable Object v3, String l4, @Nullable Object v4, String l5, @Nullable Object v5)
      Creates a SQL VALUES clause for multiple columns of hardcoded data.

      The VALUES clause be used in SELECT or MERGE statements to construct a temporary, inline virtual table of hardcoded data without needing a physical database table.

      Parameters:
      tableAlias - the alias of the virtual table to which the values belong
      l1 - the first value's column label
      v1 - the first value; can be null
      l2 - the second value's column label
      v2 - the second value; can be null
      l3 - the third value's column label
      v3 - the third value; can be null
      l4 - the fourth value's column label
      v4 - the fourth value; can be null
      l5 - the fifth value's column label
      v5 - the fifth value; can be null
      Returns:
      a new ValuesSpec instance containing the specified table alias, label, and value
    • values

      public static ValuesSpec values(String tableAlias, String l1, @Nullable Object v1, String l2, @Nullable Object v2, String l3, @Nullable Object v3, String l4, @Nullable Object v4, String l5, @Nullable Object v5, String l6, @Nullable Object v6)
      Creates a SQL VALUES clause for multiple columns of hardcoded data.

      The VALUES clause be used in SELECT or MERGE statements to construct a temporary, inline virtual table of hardcoded data without needing a physical database table.

      Parameters:
      tableAlias - the alias of the virtual table to which the values belong
      l1 - the first value's column label
      v1 - the first value; can be null
      l2 - the second value's column label
      v2 - the second value; can be null
      l3 - the third value's column label
      v3 - the third value; can be null
      l4 - the fourth value's column label
      v4 - the fourth value; can be null
      l5 - the fifth value's column label
      v5 - the fifth value; can be null
      l6 - the sixth value's column label
      v6 - the sixth value; can be null
      Returns:
      a new ValuesSpec instance containing the specified table alias, label, and value
    • values

      public static ValuesSpec values(String tableAlias, String[] labels, @Nullable Object[] values)
      Creates a SQL VALUES clause for multiple columns of hardcoded data spanning one or more rows.

      The VALUES clause be used in SELECT or MERGE statements to construct a temporary, inline virtual table of hardcoded data without needing a physical database table.

      For a single row of VALUES data, the labels and values arrays should contain the same number of elements. For multiple rows of VALUES data, the values array size should be a multiple of the labels array size.

      Parameters:
      tableAlias - The alias of the virtual table to which the values belong.
      labels - Array of column labels.
      values - Array of nullable values. Must be the same length as labels, or a multiple of it.
      Returns:
      A new ValuesSpec instance containing the specified table alias, label, and value.
      See Also:
    • values

      public static ValuesSpec values(String tableAlias, List<String> labels, List<@Nullable Object> values)
      Creates a SQL VALUES clause for multiple columns of hardcoded data spanning one or more rows.

      The VALUES clause be used in SELECT or MERGE statements to construct a temporary, inline virtual table of hardcoded data without needing a physical database table.

      For a single row of VALUES data, the labels and values lists should contain the same number of elements. For multiple rows of VALUES data, the values list size should be a multiple of the labels list size.

      Parameters:
      tableAlias - The alias of the virtual table to which the values belong.
      labels - List of column labels
      values - List of nullable values. Must be the same length as labels, or a multiple of it.
      Returns:
      A new ValuesSpec instance containing the specified table alias, label, and value.
      See Also:
    • values

      public static ValuesSpec values(String tableAlias, Map<String,@Nullable Object> values)
      Creates a SQL VALUES clause for multiple columns of hardcoded data.

      The VALUES clause be used in SELECT or MERGE statements to construct a temporary, inline virtual table of hardcoded data without needing a physical database table.

      Parameters:
      tableAlias - the alias of the virtual table to which the values belong
      values - A map of column labels to nullable values. To preserve ordering, consider using a LinkedHashMap.
      Returns:
      a new ValuesSpec instance containing the specified table alias, label, and value
    • values

      public static ValuesSpec values(String tableAlias, List<Map<String,@Nullable Object>> rows)
      Creates a SQL VALUES clause for multiple row, each containing multiple columns of hardcoded data.

      The VALUES clause be used in SELECT or MERGE statements to construct a temporary, inline virtual table of hardcoded data without needing a physical database table.

      Parameters:
      tableAlias - the alias of the virtual table to which the values belong
      rows - List of rows, each row consisting of a map of column labels to nullable values. To preserve ordering, consider using a LinkedHashMap.
      Returns:
      a new ValuesSpec instance containing the specified table alias, label, and value
      See Also:
    • f

      public static ExpressionSpec f(String field)
      Selects a DTO field by name for the DTO class selected in the query.

      Shorthand for field(String).

      This infers the source entity/mapped DTO class from the available `FROM`/`JOIN` clauses.

      Parameters:
      field - The name of the DTO field to select.
      Returns:
      a query expression selecting the target entity/DTO field
      See Also:
    • f

      public static ExpressionSpec f(Class<?> dtoClass, String field)
      Selects a DTO field by name for the specified DTO type that is selected/joined in the query.

      Shorthand for field(Class, String).

      Parameters:
      dtoClass - The DTO class to select from.
      field - The name of the DTO field to select.
      Returns:
      a query expression selecting the target entity/DTO field
    • field

      public static ExpressionSpec field(String field)
      Selects a DTO field by name for the entity/DTO class selected in the query.

      This infers the source entity/mapped DTO class from the available `FROM`/`JOIN` clauses.

      Parameters:
      field - The name of the DTO field to select.
      Returns:
      a query expression selecting the target entity/DTO field
      See Also:
    • field

      public static ExpressionSpec field(Class<?> dtoClass, String field)
      Selects a DTO field by name for the specified DTO type that is selected/joined in the query.
      Parameters:
      dtoClass - The DTO class.
      field - The name of the DTO field to select.
      Returns:
      a query expression selecting the target entity/DTO field
      See Also:
    • alias

      public static ExpressionSpec alias(Class<?> dtoClass, String field, String alias)
      Selects a DTO field by name for the specified DTO type that is selected/joined in the query, and aliases it with the specified alias.
      Parameters:
      dtoClass - The DTO class to select from.
      field - The name of the DTO field to select.
      alias - The alias to use for the field.
      Returns:
      a query expression selecting the aliased target entity/DTO field
    • c

      public static ExpressionSpec c(String column)
      Selects a database column by name.

      This is shorthand for column(String).

      Column names can be qualified with a table name, e.g. "table.column".

      Parameters:
      column - The name of the column to select.
      Returns:
      a query expression selecting the target column.
    • column

      public static ExpressionSpec column(String column)
      Selects a database column by name.

      Column names can be qualified with a table name, e.g. "table.column".

      Parameters:
      column - The name of the column to select.
      Returns:
      a query expression selecting the target column.
      See Also:
    • c

      public static ExpressionSpec c(String table, String column)
      Selects a database column by its table and simple column name.

      Shorthand for column(String, String).

      Parameters:
      table - The table to select the column from.
      column - The name of the column to select.
      Returns:
      a query expression selecting the target column.
      See Also:
    • column

      public static ExpressionSpec column(String table, String column)
      Selects a database column by its table and simple column name.
      Parameters:
      table - The table to select the column from.
      column - The name of the column to select.
      Returns:
      a query expression selecting the target column.
      See Also:
    • c

      public static ExpressionSpec c(Table table, String column)
      Selects a database column by name.

      Shorthand for column(Table, String)

      The returned ProtoColumnExpressionSpec value has no context of the table it is selecting from yet.

      Parameters:
      table - The table to select the column from.
      column - The name of the column to select.
      Returns:
      a query expression selecting the target column.
    • column

      public static ExpressionSpec column(Table table, String column)
      Selects a database column by name.
      Parameters:
      table - The table to select the column from.
      column - The name of the column to select.
      Returns:
      a query expression selecting the target column.
    • ca

      public static ExpressionSpec ca(Table table, String column, @Nullable String columnAlias)
      Selects a database column by name and aliases it with the specified alias.

      Shorthand for alias(Table, String, String).

      Parameters:
      table - The table to select the column from.
      column - The simple name of the column to select.
      columnAlias - The alias to use for the column; may be null.
      Returns:
      a query expression selecting the target column.
    • alias

      public static ExpressionSpec alias(Table table, String column, String columnAlias)
      Selects a database column by name and aliases it with the specified alias.
      Parameters:
      table - The table to select the column from.
      column - The simple name of the column to select.
      columnAlias - The alias to use for the column; may be null.
      Returns:
      a query expression selecting the target column.
      See Also:
    • ca

      public static ExpressionSpec ca(String column, @Nullable String alias)
      Selects a database column by name and aliases it with the specified alias.

      Shorthand for alias(String, String)

      Column names can be qualified with a table name, e.g. "table.column".

      Parameters:
      column - The name of the column to select.
      alias - The alias to use for the column.
      Returns:
      a query expression selecting the target column.
    • alias

      public static ExpressionSpec alias(String column, String alias)
      Selects a database column by name and aliases it with the specified alias.

      Column names can be qualified with a table name, e.g. "table.column".

      Parameters:
      column - The name of the column to select.
      alias - The alias to use for the column.
      Returns:
      a query expression selecting the target column.
      See Also:
    • ca

      public static ExpressionSpec ca(String table, String column, @Nullable String columnAlias)
      Selects a database column by name and alias.

      Shorthand for alias(Table, String, String)

      Parameters:
      table - The table to select the column from.
      column - The simple name of the column to select.
      columnAlias - The alias to use for the column.
      Returns:
      a query expression selecting the target column.
    • alias

      public static ExpressionSpec alias(String table, String column, @Nullable String columnAlias)
      Selects a database column by name and alias.

      The returned ProtoColumnExpressionSpec value has no context of the table it is selecting from yet.

      Parameters:
      table - The table to select the column from.
      column - The name of the column to select.
      columnAlias - The alias to use for the column.
      Returns:
      a query expression selecting the target column.
    • aliasRef

      public static AliasReferenceSpec aliasRef(String alias)
      References an aliased column in the query.

      This is used to provide aliases as part of conditional statements in various SQL clauses.

      Parameters:
      alias - The alias/label of the column in the query.
      Returns:
      A reference to an aliased column in the query.
    • aliasRef

      public static AliasReferenceSpec aliasRef(String tableAlias, String columnLabel)
      References an aliased column in the query in the context of a parent table/subquery.

      This is used to provide aliases as part of conditional statements in various SQL clauses.

      Parameters:
      tableAlias - The alias/label of the parent table/subquery.
      columnLabel - The label (alias or column name) of the column to reference.
      Returns:
      A reference to an aliased column in the query.
      See Also:
    • aliasRef

      public static AliasReferenceSpec aliasRef(String tableAlias, ExpressionSpec expression)
      References an aliased column in the query in the context of a parent table/subquery.

      This is used to provide aliases as part of conditional statements in various SQL clauses.

      Parameters:
      tableAlias - The alias/label of the parent table/subquery.
      expression - Query expression targeting a column in the parent table/subquery.
      Returns:
      A reference to an aliased column in the query.
      See Also:
    • literal

      public static <T> LiteralExpressionSpec<T> literal(@Nullable T value)
      Specifies a literal value.

      This is used to specify a literal value in a query expression.

      Type Parameters:
      T - The type of the literal value.
      Parameters:
      value - The literal value to wrap.
      Returns:
      A literal expression specification.
    • literal

      public static <T> LiteralExpressionSpec<T> literal(@Nullable T value, @Nullable String alias)
      Specifies a literal value.

      This is used to specify a literal value in a query expression.

      Type Parameters:
      T - The type of the literal value.
      Parameters:
      value - The literal value to wrap.
      Returns:
      A literal expression specification.
    • convert

      public static <T> ConvertSpec<T> convert(ExpressionSpec expression, Class<T> returnType)
      Converts a database result into the specified Java type.

      This uses Litebridge's registered type converter to perform the conversion; it is not a database operation.

      It can be used to ensure that the return value of a nested expression is converted to the specified Java type on the ORM side; e.g. avg(ExpressionSpec) returns a @{Number} instance by default, with the actual return type being determined by the database. To convert the return type to a Long, convert() can be used to convert it before returning: litebridge.select(Fn.convert(Fn.avg(column), Long.class));

      Type Parameters:
      T - The type to convert the expression result to
      Parameters:
      expression - The target expression result to convert
      returnType - The type to convert the expression result to
      Returns:
      a ProtoColumnExpressionSpec expression instance to convert the return value of the nested expression
    • convert

      public static <T> ConvertIntent<T> convert(Class<T> returnType, ExpressionSpec... expressions)
      Converts the results of multiple expressions into a single Java object of the specified type.
      Type Parameters:
      T - The target type.
      Parameters:
      returnType - The target type class.
      expressions - The expressions to convert.
      Returns:
      a ConvertIntent instance.
    • row

      public static ConvertIntent<Row> row(ExpressionSpec... expressions)
      Converts the results of multiple expressions into a single database Row object.
      Parameters:
      expressions - The expressions to convert.
      Returns:
      a ConvertIntent instance for Row.
    • avg

      public static TypeOverrideExpressionSpec<Number> avg(String column)
      AVG(): Returns the average value of a column/field.
      Parameters:
      column - Name of the target column/field to calculate the average value of.
      Returns:
      a ProtoNestableTOExpr expression instance to select the average value of a column/field.
    • avg

      public static TypeOverrideExpressionSpec<Number> avg(ExpressionSpec expressionSpec)
      AVG(): Returns the average value of a column/field.
      Parameters:
      expressionSpec - Target nested expression to calculate the average value of.
      Returns:
      a ProtoNestableTOExpr expression instance to select the average value of a column/field.
    • max

      public static TypeOverrideExpressionSpec<Number> max(String column)
      MAX(): Returns the highest or largest value within a specified column/field.
      Parameters:
      column - Name of the target column/field to calculate the maximum value of.
      Returns:
      a ProtoNestableTOExpr expression instance to select the maximum value of a column/field.
    • max

      public static TypeOverrideExpressionSpec<Number> max(ExpressionSpec expressionSpec)
      MAX(): Returns the highest or largest value within a specified expression.
      Parameters:
      expressionSpec - Target nested expression to calculate the maximum value of.
      Returns:
      a ProtoNestableTOExpr expression instance to select the maximum value of a column/field.
    • min

      public static TypeOverrideExpressionSpec<Number> min(String column)
      MIN(): Returns the lowest or smallest value within a specified column or expression
      Parameters:
      column - Name of the target column/field to calculate the maximum value of.
      Returns:
      a ProtoNestableTOExpr expression instance to select the maximum value of a column/field.
    • min

      public static TypeOverrideExpressionSpec<Number> min(ExpressionSpec expressionSpec)
      MIN(): Returns the lowest or smallest value within a specified column or expression
      Parameters:
      expressionSpec - Target nested expression to calculate the maximum value of.
      Returns:
      a ProtoNestableTOExpr expression instance to select the maximum value of a column/field.
    • count

      public static TypeOverrideExpressionSpec<Long> count()
      COUNT(): Selects the count of rows matching the query.
      Returns:
      a CountSpec expression instance to select the count of rows.
    • upper

      public static ProtoNestableTOExpr<String> upper(String column)
      UPPER(): Returns the uppercase value of a column's text.
      Parameters:
      column - Target column/field name
      Returns:
      a ProtoNestableTOExpr expression instance to select a specific column.
    • upper

      public static ProtoNestableTOExpr<String> upper(ExpressionSpec expressionSpec)
      UPPER(): Returns the uppercase value of a column's text.
      Parameters:
      expressionSpec - Target nested expression
      Returns:
      a ProtoNestableTOExpr expression instance to select a specific column.
    • lower

      public static ProtoNestableTOExpr<String> lower(String column)
      LOWER(): Returns the lowercase value of a column's text.
      Parameters:
      column - Target column/field name
      Returns:
      a ProtoNestableTOExpr expression instance to select a specific column.
    • lower

      public static ProtoNestableTOExpr<String> lower(ExpressionSpec expressionSpec)
      LOWER(): Returns the lowercase value of a column's text.
      Parameters:
      expressionSpec - Target nested expression
      Returns:
      a ProtoNestableTOExpr expression instance to select a specific column.
    • substring

      public static ProtoNestableTOExpr<String> substring(String column, int start)
      SUBSTRING(): Returns the lowercase value of a column's text.

      This shorthand version omits the "length" parameter and thus extracts everything from the start position to the end of the text.

      Parameters:
      column - Target column to extract characters from.
      start - The starting position. The first character of a database string is always 1.
      Returns:
      a ProtoNestableTOExpr expression instance to select a specific column.
      See Also:
    • substring

      public static ProtoNestableTOExpr<String> substring(ExpressionSpec expressionSpec, int start)
      SUBSTRING(): Returns the lowercase value of a column's text.

      This shorthand version omits the "length" parameter and thus extracts everything from the start position to the end of the text.

      Parameters:
      expressionSpec - Target nested expression to extract characters from.
      start - The starting position. The first character of a database string is always 1.
      Returns:
      a ProtoNestableTOExpr expression instance to select a specific column.
      See Also:
    • substring

      public static ProtoNestableTOExpr<String> substring(String column, int start, int length)
      SUBSTRING(): Returns the lowercase value of a column's text.
      Parameters:
      column - Target column to extract characters from.
      start - The starting position. The first character of a database string is always 1.
      length - The number of characters to return.
      Returns:
      a ProtoNestableTOExpr expression instance to select a specific column.
      See Also:
    • substring

      public static ProtoNestableTOExpr<String> substring(ExpressionSpec expressionSpec, int start, int length)
      SUBSTRING(): Returns the lowercase value of a column's text.
      Parameters:
      expressionSpec - Target nested expression to extract characters from.
      start - The starting position. The first character of a database string is always 1.
      length - The number of characters to return.
      Returns:
      a ProtoNestableTOExpr expression instance to select a specific column.
      See Also:
    • abs

      public static ProtoNestableTOExpr<Number> abs(String column)
      ABS(): Absolute value of a number.
      Parameters:
      column - Target column/field.
      Returns:
      a ProtoNestableTOExpr expression instance to select a specific column.
    • abs

      public static ProtoNestableTOExpr<Number> abs(ExpressionSpec expressionSpec)
      ABS(): Absolute value of a number.
      Parameters:
      expressionSpec - Target nested expression.
      Returns:
      a ProtoNestableTOExpr expression instance to select a specific column.
    • currentTimestamp

      public static CurrentTimestampSpec currentTimestamp()
      CURRENT_TIMESTAMP: Returns the current date and time.
      Returns:
      a CurrentTimestampSpec instance.
    • exists

      public static ExistsExpressionSpec exists(Function<SelectApi,SelectTerminal<?>> query)
      EXISTS: Checks if the provided query returned any results
      Returns:
      An EXISTS expression.
    • exists

      public static QueryAlteringExpressionSpec<Boolean> exists()
      Execute an existence check and return a true/false response to the query.

      This should be used as the only expression in the query.
      It alters the query return type to Boolean.

      The resulting expression causes the query to be optimised for a basic existence check, e.g. a variant of: SELECT 1 FROM users WHERE user_id = ? LIMIT 1

      Returns:
      A top-level existence-checking expression.