Package com.gengoai.sql

Class SQL.F

java.lang.Object

com.gengoai.sql.SQL.F

Enclosing class:

SQL

public static final class SQL.F
extends Object

Set of commonly used SQL functions

Method Summary

Modifier and Type	Method	Description
static SQLOperable	abs(@NonNull SQLElement arg)	Generates an SQLOperable representing the absolute value of the given argument.
static SQLOperable	abs(@NonNull String arg)	Generates an SQLOperable representing the absolute value of the given column.
static SQLOperable	average(@NonNull SQLElement arg)	Generates an SQLOperable representing the average value of the given argument.
static SQLOperable	average(@NonNull String arg)	Generates an SQLOperable representing the average value of the given column.
static SQLOperable	coalesce(@NonNull SQLElement... args)	Generates an SQLOperable representing the coalesce function which returns the first non-null value.
static SQLOperable	count(@NonNull SQLElement arg)	Generates an SQLOperable representing the count value of the given argument.
static SQLOperable	count(@NonNull String arg)	Generates an SQLOperable representing the count value of the given column.
static SQLOperable	countDistinct(@NonNull SQLOperable arg)	Generates an SQLOperable representing the distinct count value of the given argument.
static SQLOperable	countDistinct(@NonNull String arg)	Generates an SQLOperable representing the distinct count value of the given column.
static SQLOperable	date(@NonNull SQLElement timeString, @NonNull SQLElement... args)	Generates an SQLOperable representing the date function, which converts the given time string into a date object.
static SQLOperable	date(@NonNull String timeString, @NonNull SQLElement... args)	Generates an SQLOperable representing the date function, which converts the given time string into a date object.
static SQLOperable	dateTime(@NonNull SQLElement timeString, @NonNull SQLElement... args)	Generates an SQLOperable representing the datetime function, which converts the given time string into a date object.
static SQLOperable	dateTime(@NonNull String timeString, @NonNull SQLElement... args)	Generates an SQLOperable representing the datetime function, which converts the given time string into a date object.
static SQLOperable	groupConcat(@NonNull SQLElement arg, @NonNull String delimiter)	Generates an SQLOperable representing the group concat function, which concatenates one more items with a given delimiter.
static SQLOperable	groupConcat(@NonNull String arg, @NonNull String delimiter)	Generates an SQLOperable representing the group concat function, which concatenates one more items with a given delimiter.
static SQLOperable	groupConcat(@NonNull Collection<? extends SQLElement> args, @NonNull String delimiter)	Generates an SQLOperable representing the group concat function, which concatenates one more items with a given delimiter.
static SQLOperable	ifNull(@NonNull SQLElement expression, @NonNull SQLElement value)	Generates an SQLOperable representing an "if null" function, which returns the specified value if the expression is null.
static SQLOperable	instr(@NonNull SQLElement string, @NonNull SQLElement substring)	Generates an SQLOperable representing an "in string" function, which searches a string for a substring.
static SQLOperable	instr(@NonNull SQLElement string, @NonNull String substring)	Generates an SQLOperable representing an "in string" function, which searches a string for a substring.
static SQLOperable	json_extract(@NonNull SQLElement json, @NonNull SQLElement jsonPath)	Generates an SQLOperable representing a function to extract a json value (object, array, primitive) from a json column for a given path in the json tree.
static SQLOperable	json_extract(@NonNull String column, @NonNull String jsonPath)	Generates an SQLOperable representing a function to extract a json value (object, array, primitive) from a json column for a given path in the json tree.
static SQLOperable	length(@NonNull SQLElement arg)	Generates an SQLOperable representing the length of the string value represented by the given argument.
static SQLOperable	length(@NonNull String arg)	Generates an SQLOperable representing the length of the string value in the given column.
static SQLOperable	lower(@NonNull SQLElement arg)	Generates an SQLOperable representing the lower function, which lower cases a string value.
static SQLOperable	lower(@NonNull String arg)	Generates an SQLOperable representing the lower function, which lower cases a string value.
static SQLOperable	ltrim(@NonNull SQLElement arg)	Generates an SQLOperable representing the ltrim function, which trims from the left side of a string.
static SQLOperable	ltrim(@NonNull String arg)	Generates an SQLOperable representing the ltrim function, which trims from the left side of a string.
static SQLOperable	max(@NonNull SQLElement arg)	Generates an SQLOperable representing the max function.
static SQLOperable	max(@NonNull String arg)	Generates an SQLOperable representing the max function.
static SQLOperable	min(@NonNull SQLElement arg)	Generates an SQLOperable representing the min function.
static SQLOperable	min(@NonNull String arg)	Generates an SQLOperable representing the min function.
static SQLOperable	nullIf(@NonNull SQLElement arg1, @NonNull SQLElement arg2)	Generates an SQLOperable representing an "null if" function, which returns null if the two arguments are equal.
static SQLOperable	random()	Generates an SQLOperable representing the random function
static SQLOperable	regexp(@NonNull SQLElement arg1, @NonNull SQLElement arg2)	Generates an SQLOperable representing a regular expression matching operating
static SQLOperable	regexp(@NonNull SQLElement arg1, @NonNull String arg2)	Generates an SQLOperable representing a regular expression matching operating
static SQLOperable	replace(@NonNull SQLElement arg, String pattern, String replacement)	Generates an SQLOperable representing an string "replace" function, which replaces a given pattern with a given replacement in string expression.
static SQLOperable	replace(@NonNull String column, String pattern, String replacement)	Generates an SQLOperable representing an string "replace" function, which replaces a given pattern with a given replacement in string expression.
static SQLOperable	round(@NonNull SQLElement arg, int precision)	Generates an SQLOperable that rounds the given argument to the given precision.
static SQLOperable	round(@NonNull String column, int precision)	Generates an SQLOperable that rounds the given argument to the given precision.
static SQLOperable	rtrim(@NonNull SQLElement arg)	Generates an SQLOperable representing the right function, which trims from the right side of a string.
static SQLOperable	rtrim(@NonNull String arg)	Generates an SQLOperable representing the rtrim function, which trims from the right side of a string.
static SQLOperable	strftime(@NonNull SQLElement format, @NonNull SQLElement timeString, @NonNull SQLElement... args)	Generates an SQLOperable representing a function to format times as strings.
static SQLOperable	substr(@NonNull SQLElement arg, int start, int length)	Generates an SQLOperable representing an "substring" function, which returns a substring from the given starting position and of the given length from an argument.
static SQLOperable	substr(@NonNull SQLElement arg, @NonNull SQLElement start, @NonNull SQLElement length)	Generates an SQLOperable representing an "substring" function, which returns a substring from the given starting position and of the given length from an argument.
static SQLOperable	substr(@NonNull String column, int start, int length)	Generates an SQLOperable representing an "substring" function, which returns a substring from the given starting position and of the given length from an argument.
static SQLOperable	substr(@NonNull String column, @NonNull SQLElement start, @NonNull SQLElement length)	Generates an SQLOperable representing an "substring" function, which returns a substring from the given starting position and of the given length from an argument.
static SQLOperable	sum(@NonNull SQLElement arg)	Generates an SQLOperable representing the sum function.
static SQLOperable	sum(@NonNull String column)	Generates an SQLOperable representing the sum function.
static SQLOperable	time(@NonNull SQLOperable timeString, @NonNull SQLElement... args)	Generates an SQLOperable representing the datetime function, which converts the given time string into a date object.
static SQLOperable	time(@NonNull String timeString, @NonNull SQLElement... args)	Generates an SQLOperable representing the time function, which converts the given time string into a date object.
static SQLOperable	trim(@NonNull SQLElement arg)	Generates an SQLOperable representing the trim function, which trims from the right and left side of a string.
static SQLOperable	trim(@NonNull String arg)	Generates an SQLOperable representing the trim function, which trims from the right and left side of a string.
static SQLOperable	upper(@NonNull SQLElement arg)	Generates an SQLOperable representing the lower function, which lower cases a string value.
static SQLOperable	upper(@NonNull String arg)	Generates an SQLOperable representing the upper function, which lower cases a string value.

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Method Detail

regexp

public static SQLOperable regexp(@NonNull
                                 @NonNull SQLElement arg1,
                                 @NonNull
                                 @NonNull SQLElement arg2)

Generates an SQLOperable representing a regular expression matching operating

Parameters:

arg1 - the value to check the regular expression against.

arg2 - the regular expression

Returns:

the SQLOperable

regexp

public static SQLOperable regexp(@NonNull
                                 @NonNull SQLElement arg1,
                                 @NonNull
                                 @NonNull String arg2)

Generates an SQLOperable representing a regular expression matching operating

Parameters:

arg1 - the value to check the regular expression against.

arg2 - the regular expression

Returns:

the SQLOperable

abs

public static SQLOperable abs(@NonNull
                              @NonNull SQLElement arg)

Generates an SQLOperable representing the absolute value of the given argument.

Parameters:

arg - the function argument

Returns:

the SQLOperable

abs

public static SQLOperable abs(@NonNull
                              @NonNull String arg)

Generates an SQLOperable representing the absolute value of the given column.

Parameters:

arg - the function argument

Returns:

the SQLOperable

average

public static SQLOperable average(@NonNull
                                  @NonNull SQLElement arg)

Generates an SQLOperable representing the average value of the given argument.

Parameters:

arg - the function argument

Returns:

the SQLOperable

average

public static SQLOperable average(@NonNull
                                  @NonNull String arg)

Generates an SQLOperable representing the average value of the given column.

Parameters:

arg - the column whose values will be averaged

Returns:

the SQLOperable

coalesce

public static SQLOperable coalesce(@NonNull
                                   @NonNull SQLElement... args)

Generates an SQLOperable representing the coalesce function which returns the first non-null value.

Parameters:

args - the function arguments

Returns:

the SQLOperable

count

public static SQLOperable count(@NonNull
                                @NonNull SQLElement arg)

Generates an SQLOperable representing the count value of the given argument.

Parameters:

arg - the function argument

Returns:

the SQLOperable

count

public static SQLOperable count(@NonNull
                                @NonNull String arg)

Generates an SQLOperable representing the count value of the given column.

Parameters:

arg - the function argument

Returns:

the SQLOperable

countDistinct

public static SQLOperable countDistinct(@NonNull
                                        @NonNull SQLOperable arg)

Generates an SQLOperable representing the distinct count value of the given argument.

Parameters:

arg - the function argument

Returns:

the SQLOperable

countDistinct

public static SQLOperable countDistinct(@NonNull
                                        @NonNull String arg)

Generates an SQLOperable representing the distinct count value of the given column.

Parameters:

arg - the function argument

Returns:

the SQLOperable

date

public static SQLOperable date(@NonNull
                               @NonNull SQLElement timeString,
                               @NonNull
                               @NonNull SQLElement... args)

Generates an SQLOperable representing the date function, which converts the given time string into a date object. Depending on the underlying RDBMS the date function may take extra arguments denoting such tings as the date format.

Parameters:

timeString - the time string value

args - extract arguments for formatting the date

Returns:

the SQLOperable

date

public static SQLOperable date(@NonNull
                               @NonNull String timeString,
                               @NonNull
                               @NonNull SQLElement... args)

Generates an SQLOperable representing the date function, which converts the given time string into a date object. Depending on the underlying RDBMS the date function may take extra arguments denoting such tings as the date format.

Parameters:

timeString - the time string literal value

args - extract arguments for formatting the date

Returns:

the SQLOperable

dateTime

public static SQLOperable dateTime(@NonNull
                                   @NonNull SQLElement timeString,
                                   @NonNull
                                   @NonNull SQLElement... args)

Generates an SQLOperable representing the datetime function, which converts the given time string into a date object. Depending on the underlying RDBMS the date function may take extra arguments denoting such tings as the date format.

Parameters:

timeString - the time string value

args - extract arguments for formatting the date

Returns:

the SQLOperable

dateTime

public static SQLOperable dateTime(@NonNull
                                   @NonNull String timeString,
                                   @NonNull
                                   @NonNull SQLElement... args)

Generates an SQLOperable representing the datetime function, which converts the given time string into a date object. Depending on the underlying RDBMS the date function may take extra arguments denoting such tings as the date format.

Parameters:

timeString - the time string literal value

args - extract arguments for formatting the date

Returns:

the SQLOperable

groupConcat

public static SQLOperable groupConcat(@NonNull
                                      @NonNull SQLElement arg,
                                      @NonNull
                                      @NonNull String delimiter)

Generates an SQLOperable representing the group concat function, which concatenates one more items with a given delimiter.

Parameters:

arg - the argument to perform group concat on

delimiter - the delimiter to separate items with

Returns:

the SQLOperable

groupConcat

public static SQLOperable groupConcat(@NonNull
                                      @NonNull Collection<? extends SQLElement> args,
                                      @NonNull
                                      @NonNull String delimiter)

Generates an SQLOperable representing the group concat function, which concatenates one more items with a given delimiter.

Parameters:

args - the arguments to perform group concat on

delimiter - the delimiter to separate items with

Returns:

the SQLOperable

groupConcat

public static SQLOperable groupConcat(@NonNull
                                      @NonNull String arg,
                                      @NonNull
                                      @NonNull String delimiter)

Generates an SQLOperable representing the group concat function, which concatenates one more items with a given delimiter.

Parameters:

arg - the column to perform group concat on

delimiter - the delimiter to separate items with

Returns:

the SQLOperable

ifNull

public static SQLOperable ifNull(@NonNull
                                 @NonNull SQLElement expression,
                                 @NonNull
                                 @NonNull SQLElement value)

Generates an SQLOperable representing an "if null" function, which returns the specified value if the expression is null.

Parameters:

expression - the expression to evaluate

value - the value to return if the expression evalutes to null

Returns:

the SQLOperable

instr

public static SQLOperable instr(@NonNull
                                @NonNull SQLElement string,
                                @NonNull
                                @NonNull SQLElement substring)

Generates an SQLOperable representing an "in string" function, which searches a string for a substring.

Parameters:

string - the string to be searched

substring - the substring to search for

Returns:

the SQLOperable

instr

public static SQLOperable instr(@NonNull
                                @NonNull SQLElement string,
                                @NonNull
                                @NonNull String substring)

Generates an SQLOperable representing an "in string" function, which searches a string for a substring.

Parameters:

string - the string to be searched

substring - the literal substring to search for

Returns:

the SQLOperable

json_extract

public static SQLOperable json_extract(@NonNull
                                       @NonNull String column,
                                       @NonNull
                                       @NonNull String jsonPath)

Generates an SQLOperable representing a function to extract a json value (object, array, primitive) from a json column for a given path in the json tree.

Parameters:

column - the name of the column containing json

jsonPath - the path of the json element to extract.

Returns:

the SQLOperable

json_extract

public static SQLOperable json_extract(@NonNull
                                       @NonNull SQLElement json,
                                       @NonNull
                                       @NonNull SQLElement jsonPath)

Generates an SQLOperable representing a function to extract a json value (object, array, primitive) from a json column for a given path in the json tree.

Parameters:

json - the json

jsonPath - the path of the json element to extract.

Returns:

the SQLOperable

length

public static SQLOperable length(@NonNull
                                 @NonNull SQLElement arg)

Generates an SQLOperable representing the length of the string value represented by the given argument.

Parameters:

arg - the function argument

Returns:

the SQLOperable

length

public static SQLOperable length(@NonNull
                                 @NonNull String arg)

Generates an SQLOperable representing the length of the string value in the given column.

Parameters:

arg - the function argument

Returns:

the SQLOperable

lower

public static SQLOperable lower(@NonNull
                                @NonNull SQLElement arg)

Generates an SQLOperable representing the lower function, which lower cases a string value.

Parameters:

arg - the function argument

Returns:

the SQLOperable

lower

public static SQLOperable lower(@NonNull
                                @NonNull String arg)

Generates an SQLOperable representing the lower function, which lower cases a string value.

Parameters:

arg - the column whose values will be lower cased

Returns:

the SQLOperable

ltrim

public static SQLOperable ltrim(@NonNull
                                @NonNull SQLElement arg)

Generates an SQLOperable representing the ltrim function, which trims from the left side of a string.

Parameters:

arg - the function argument

Returns:

the SQLOperable

ltrim

public static SQLOperable ltrim(@NonNull
                                @NonNull String arg)

Generates an SQLOperable representing the ltrim function, which trims from the left side of a string.

Parameters:

arg - the column whose values will be ltrimed

Returns:

the SQLOperable

max

public static SQLOperable max(@NonNull
                              @NonNull SQLElement arg)

Generates an SQLOperable representing the max function.

Parameters:

arg - the function argument

Returns:

the SQLOperable

max

public static SQLOperable max(@NonNull
                              @NonNull String arg)

Generates an SQLOperable representing the max function.

Parameters:

arg - the column to find the max over

Returns:

the SQLOperable

min

public static SQLOperable min(@NonNull
                              @NonNull SQLElement arg)

Generates an SQLOperable representing the min function.

Parameters:

arg - the function argument

Returns:

the SQLOperable

min

public static SQLOperable min(@NonNull
                              @NonNull String arg)

Generates an SQLOperable representing the min function.

Parameters:

arg - the column to find the min over

Returns:

the SQLOperable

nullIf

public static SQLOperable nullIf(@NonNull
                                 @NonNull SQLElement arg1,
                                 @NonNull
                                 @NonNull SQLElement arg2)

Generates an SQLOperable representing an "null if" function, which returns null if the two arguments are equal.

Parameters:

arg1 - the first argument

arg2 - the second argument

Returns:

the SQLOperable

random

public static SQLOperable random()

Generates an SQLOperable representing the random function

Returns:

the SQLOperable

replace

public static SQLOperable replace(@NonNull
                                  @NonNull SQLElement arg,
                                  String pattern,
                                  String replacement)

Generates an SQLOperable representing an string "replace" function, which replaces a given pattern with a given replacement in string expression.

Parameters:

arg - the string expression for which the pattern will be replace

pattern - the pattern to replace

replacement - the string to replace the pattern with

Returns:

the SQLOperable

replace

public static SQLOperable replace(@NonNull
                                  @NonNull String column,
                                  String pattern,
                                  String replacement)

Generates an SQLOperable representing an string "replace" function, which replaces a given pattern with a given replacement in string expression.

Parameters:

column - the column whose values will be replaced

pattern - the pattern to replace

replacement - the string to replace the pattern with

Returns:

the SQLOperable

round

public static SQLOperable round(@NonNull
                                @NonNull SQLElement arg,
                                int precision)

Generates an SQLOperable that rounds the given argument to the given precision.

Parameters:

arg - the arg

precision - the precision

Returns:

the SQLOperable

round

public static SQLOperable round(@NonNull
                                @NonNull String column,
                                int precision)

Generates an SQLOperable that rounds the given argument to the given precision.

Parameters:

column - the column whose values will be rounded.

precision - the precision

Returns:

the SQLOperable

rtrim

public static SQLOperable rtrim(@NonNull
                                @NonNull SQLElement arg)

Generates an SQLOperable representing the right function, which trims from the right side of a string.

Parameters:

arg - the function argument

Returns:

the SQLOperable

rtrim

public static SQLOperable rtrim(@NonNull
                                @NonNull String arg)

Generates an SQLOperable representing the rtrim function, which trims from the right side of a string.

Parameters:

arg - the column whose values will be rtrimed

Returns:

the SQLOperable

strftime

public static SQLOperable strftime(@NonNull
                                   @NonNull SQLElement format,
                                   @NonNull
                                   @NonNull SQLElement timeString,
                                   @NonNull
                                   @NonNull SQLElement... args)

Generates an SQLOperable representing a function to format times as strings.

Parameters:

format - the time format

timeString - the time string to convert

args - the args used in formation

Returns:

the SQLOperable

substr

public static SQLOperable substr(@NonNull
                                 @NonNull SQLElement arg,
                                 int start,
                                 int length)

Generates an SQLOperable representing an "substring" function, which returns a substring from the given starting position and of the given length from an argument.

Parameters:

arg - the string expression to generate the substring from

start - the starting index

length - the length of substring

Returns:

the SQLOperable

substr

public static SQLOperable substr(@NonNull
                                 @NonNull SQLElement arg,
                                 @NonNull
                                 @NonNull SQLElement start,
                                 @NonNull
                                 @NonNull SQLElement length)

Generates an SQLOperable representing an "substring" function, which returns a substring from the given starting position and of the given length from an argument.

Parameters:

arg - the string expression to generate the substring from

start - the starting index

length - the length of substring

Returns:

the SQLOperable

substr

public static SQLOperable substr(@NonNull
                                 @NonNull String column,
                                 int start,
                                 int length)

Generates an SQLOperable representing an "substring" function, which returns a substring from the given starting position and of the given length from an argument.

Parameters:

column - the column whose values will be passed to the substring

start - the starting index

length - the length of substring

Returns:

the SQLOperable

substr

public static SQLOperable substr(@NonNull
                                 @NonNull String column,
                                 @NonNull
                                 @NonNull SQLElement start,
                                 @NonNull
                                 @NonNull SQLElement length)

Generates an SQLOperable representing an "substring" function, which returns a substring from the given starting position and of the given length from an argument.

Parameters:

column - the column whose values will be passed to the substring

start - the starting index

length - the length of substring

Returns:

the SQLOperable

sum

public static SQLOperable sum(@NonNull
                              @NonNull SQLElement arg)

Generates an SQLOperable representing the sum function.

Parameters:

arg - the function argument

Returns:

the SQLOperable

sum

public static SQLOperable sum(@NonNull
                              @NonNull String column)

Generates an SQLOperable representing the sum function.

Parameters:

column - the column whose values will be summed

Returns:

the SQLOperable

time

public static SQLOperable time(@NonNull
                               @NonNull SQLOperable timeString,
                               @NonNull
                               @NonNull SQLElement... args)

Generates an SQLOperable representing the datetime function, which converts the given time string into a date object. Depending on the underlying RDBMS the date function may take extra arguments denoting such tings as the date format.

Parameters:

timeString - the time string value

args - extract arguments for formatting the date

Returns:

the SQLOperable

time

public static SQLOperable time(@NonNull
                               @NonNull String timeString,
                               @NonNull
                               @NonNull SQLElement... args)

Generates an SQLOperable representing the time function, which converts the given time string into a date object. Depending on the underlying RDBMS the date function may take extra arguments denoting such tings as the date format.

Parameters:

timeString - the time string literal value

args - extract arguments for formatting the date

Returns:

the SQLOperable

trim

public static SQLOperable trim(@NonNull
                               @NonNull SQLElement arg)

Generates an SQLOperable representing the trim function, which trims from the right and left side of a string.

Parameters:

arg - the function argument

Returns:

the SQLOperable

trim

public static SQLOperable trim(@NonNull
                               @NonNull String arg)

Generates an SQLOperable representing the trim function, which trims from the right and left side of a string.

Parameters:

arg - the column whose values will be trimmed

Returns:

the SQLOperable

upper

public static SQLOperable upper(@NonNull
                                @NonNull SQLElement arg)

Generates an SQLOperable representing the lower function, which lower cases a string value.

Parameters:

arg - the function argument

Returns:

the SQLOperable

upper

public static SQLOperable upper(@NonNull
                                @NonNull String arg)

Generates an SQLOperable representing the upper function, which lower cases a string value.

Parameters:

arg - the column whose values will be upper cased

Returns:

the SQLOperable