JDBC V3 Supported Syntax
The following SQL JDBC V3 statements and operations are supported.
SQL Statements and Operations
Data Definition Operations
Create Table:
CREATE TABLE table_name {( [
{ column_name data_type [ column_constraint [ ... ] ]
| table_constraint }
[PRIMARY KEY (column)]
[INDEX (column)]
[PARTITION BY]
[BROADCAST]
] )
|
AS SELECT column [, column]*
FROM table_name
[WHERE booleanExpression]
}
DropTable:
DROP TABLE [ IF EXISTS ] name
Data Change Operations
Insert
{ INSERT } INTO tablePrimary
[ '(' column [, column ]* ')' ]
[ VALUES '(' column [, column ]* ')' ]
Update:
UPDATE tablePrimary
SET assign [, assign ]*
[ WHERE booleanExpression ]
Delete:
DELETE FROM tablePrimary [ [ AS ] alias ]
[ WHERE booleanExpression ]
Data Read Operations
statement:
| explain
| describe
| query
statementList:
statement [ ';' statement ]* [ ';' ]
explain:
EXPLAIN PLAN
FOR { query }
EXPLAIN ANALYZE
FOR { query }
Describe:
DESCRIBE DATABASE databaseName
| DESCRIBE CATALOG [ databaseName . ] catalogName
| DESCRIBE SCHEMA [ [ databaseName . ] catalogName ] . schemaName
| DESCRIBE [ TABLE ] [ [ [ databaseName . ] catalogName . ] schemaName . ] tableName [ columnName ]
| DESCRIBE [ STATEMENT ] { query }
query:
values
| WITH withItem [ , withItem ]* query
| {
select
| selectWithoutFrom
}
[ ORDER BY orderItem [, orderItem ]* ]
[ LIMIT [ start, ] { count | ALL } ]
withItem:
name
[ '(' column [, column ]* ')' ]
AS '(' query ')'
orderItem:
expression [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ]
select:
SELECT [ ALL | DISTINCT ]
{ * | projectItem [, projectItem ]* }
FROM tableExpression
[ WHERE booleanExpression ]
[ GROUP BY { groupItem [, groupItem ]* } ]
[ HAVING booleanExpression ]
selectWithoutFrom: [Scalar]
SELECT [ ALL | DISTINCT ]
{ * | projectItem [, projectItem ]* }
projectItem:
expression [ [ AS ] columnAlias ]
| tableAlias . *
tableExpression:
tableReference [, tableReference ]*
| tableExpression [ NATURAL ] [ { LEFT | RIGHT } ] JOIN tableExpression [ joinCondition ]
joinCondition:
ON booleanExpression
| USING '(' column [, column ]* ')'
tableReference:
tablePrimary
[ FOR SYSTEM_TIME AS OF expression ]
[ [ AS ] alias [ '(' columnAlias [, columnAlias ]* ')' ] ]
tablePrimary:
[ [ catalogName . ] schemaName . ] tableName
'(' TABLE [ [ catalogName . ] schemaName . ] tableName ')'
| [ LATERAL ] '(' query ')'
| [ LATERAL ] TABLE '(' [ SPECIFIC ] functionName '(' expression [, expression ]* ')' ')'
columnDecl:
column type [ NOT NULL ]
optionValue:
stringLiteral
| numericLiteral
columnOrList:
column
| '(' column [, column ]* ')'
exprOrList:
expr
| '(' expr [, expr ]* ')'
values:
VALUES expression [, expression ]*
groupItem:
expression
| '(' ')'
| '(' expression [, expression ]* ')'
| GROUPING SETS '(' groupItem [, groupItem ]* ')'
Transactions
BEGIN/START and END/COMMIT are available to define the beginning and end of a Logical Unit of Work as a transaction. ROLLBACK can also be used to undo a transaction.
Example 1:
BEGIN (or START); |
Begin transaction |
INSERT …xxx…; |
|
INSERT …yyy…; |
|
SELECT …xxx…; |
OK |
SELECT …yyy…; |
OK |
END (or COMMIT); |
End transaction with commit |
Example 2:
BEGIN (or START); |
Begin transaction |
INSERT …xxx…; |
|
SELECT …xxx…; |
OK |
ROLLBACK; |
End transaction with rollback |
SELECT …xxx…; |
Not found |
INSERT …yyy…; |
|
SELECT …yyy…; |
OK |
COMMIT; |
End transaction with commit |
SELECT …yyy…; |
OK |
END (or COMMIT); |
End transaction with commit (nothing to commit) |
Aggregate Functions
COUNT(*) :: BIGINT |
Calculates the number of input rows. |
COUNT(any) :: BIGINT |
Calculates the number of input rows in which the input value is not null. |
COUNT(DISTINCT *) :: BIGINT |
Calculates the number of input rows in which the input value is unique. |
COUNT(DISTINCT any) :: BIGINT |
Calculates the number of input rows in which the input value is unique and not null. |
SUM(TINYINT | SMALLINT | INT) :: BIGINT, SUM(BIGINT | DECIMAL) :: DECIMAL, SUM(REAL) :: REAL, SUM(DOUBLE) :: DOUBLE |
Calculates the sum of the non-null input values. |
SUM(DISTINCT TINYINT | SMALLINT | INT) :: BIGINT, SUM(DISTINCT BIGINT | DECIMAL) :: DECIMAL, SUM(DISTINCT REAL) :: REAL, SUM(DISTINCT DOUBLE) :: DOUBLE |
Calculates the sum of the unique, non-null input values. |
AVG(DECIMAL) :: DECIMAL, AVG(DOUBLE) :: DOUBLE |
Calculates the mean of all the non-null input values. |
AVG(DISTINCT DECIMAL) :: DECIMAL, AVG(DISTINCT DOUBLE) :: DOUBLE |
Calculates the mean of all the unique, non-null input values. |
MIN(any) :: same type as the input |
Calculates the minimum of the non-null input values. Applicable also to OBJECT type, if the underlying value is java.lang.Comparable. |
Comparison Operators
= |
Equal to |
!= or <> |
Not equal to |
< |
Less than |
> |
Greater than |
<= |
Less than or equal to |
>= |
Greater than or equal to |
Supported Data Types and Type Conversions
The following table lists all of the supported data types, and the conversions allowed for each data type.
Convert From | Convert To | |
BOOLEAN |
VARCHAR, BOOLEAN, OBJECT |
java.lang.Boolean |
VARCHAR |
All types |
java.lang.String |
TINYINT |
VARCHAR, BOOLEAN, SMALLINT, INTEGER, BIGINT, DECIMAL, REAL, DOUBLE, OBJECT |
java.lang.Byte |
SMALLINT |
VARCHAR, SMALLINT, INTEGER, BIGINT, DECIMAL, REAL, DOUBLE, OBJECT |
java.lang.Short |
INTEGER |
VARCHAR, SMALLINT, INTEGER, BIGINT, DECIMAL, REAL, DOUBLE, OBJECT |
java.lang.Integer |
BIGINT |
VARCHAR, SMALLINT, INTEGER, BIGINT, DECIMAL, REAL, DOUBLE, OBJECT |
java.lang.Long |
DECIMAL |
VARCHAR, SMALLINT, INTEGER, BIGINT, DECIMAL, REAL, DOUBLE, OBJECT |
java.math.BigDecimal |
REAL |
VARCHAR, SMALLINT, INTEGER, BIGINT, DECIMAL, REAL, DOUBLE, OBJECT |
java.lang.Float |
DOUBLE |
VARCHAR, SMALLINT, INTEGER, BIGINT, DECIMAL, REAL, DOUBLE, OBJECT |
java.lang.Double |
DATE |
VARCHAR, DATE, TIME, TIMESTAMP, TIMESTAMP WITH TIME ZONE, OBJECT |
java.time.LocalDate |
TIME |
VARCHAR, DATE, TIME, TIMESTAMP, TIMESTAMP WITH TIME ZONE, OBJECT |
java.time.LocalTime |
TIMESTAMP |
VARCHAR, DATE, TIME, TIMESTAMP, TIMESTAMP WITH TIME ZONE, OBJECT |
java.time.LocalDateTime |
OBJECT |
All types |
Any Java class |
Date and Time Functions
EXTRACT(element, date) |
Extracts element from date. Supported elements: MILLENIUM, CENTURY, DECADE, YEAR, ISOYEAR, QUARTER, MONTH, WEEK, DAY, DOW, ISODOW, DOY, HOUR, MINUTE, SECOND, MILLISECOND, MICROSECOND, EPOCH Supported date types: DATE, TIME, TIMESTAMP, TIMESTAMP WITH TIME ZONE |
TO_TIMESTAMP_TZ(BIGINT) |
Converts BIGINT values as seconds to TIMESTAMP_WITH_TIMEZONE. |
TO_EPOCH_MILLIS(TIMESTAMP_WITH_TIMEZONE) |
Converts TIMESTAMP_WITH_TIMEZONE values to an EPOCH value in milliseconds. |
Expressions
AND/OR Operators
NOT Operator
IS Operator
IS TRUE |
IS NOT TRUE |
IS FALSE |
IS NOT FALSE |
IS NULL |
IS NOT NULL |
IN Operator
IN |
Evaluates to TRUE if the value on the left of the operator is in the list of values on the right. |
NOT IN |
Evaluates to TRUE if the value on the left of the operator is not in the list of values on the right. |
Mathematical Functions
ABS(number) |
CEIL(number) |
DEGREES(double) |
EXP(number) |
FLOOR(number) |
LN(number) |
LOG10(number) |
RADIANS(double) |
RAND |
RAND(number) |
ROUND(number) |
ROUND(number, s integer) |
TRUNCATE(number) |
TRUNCATE(number, s integer) |
Special Functions
CAST(value AS type) |
Convert the value to the given type |
NULLIF(expression1, expression2) |
Returns NULL if the expressions are equal. The data type of the NULL value returned is the same as the first expression. |
COALESCE( expression1 [, expression2 [, expression3 ] …] ) |
Evaluates each expression at a time, starting from the first. If the value of any expression is not NULL, its value is returned. If all the expressions are NULL, NULL is returned. Arguments to the right of the first non-null argument are not evaluated. |
CASE |
Returns a value when the first condition is met (like an if-then-else statement). |
BETWEEN |
Evaluates to TRUE if the value on the left of the operator is in a given range between the first value on the right and the second value on the right. (a >= b AND a <= c) |
BETWEEN SYMMETRIC |
Evaluates to TRUE if the value on the left of the operator is in a given range between the first value on the right and the second value on the right or the second value on the right and the first value on the right. (a >= b AND a <= c) OR (a >= c AND a <= b). |
NOT BETWEEN |
Evaluates to TRUE if the value on the left of the operator is not in a given range between the first value on the right and the second value on the right. |
NOT BETWEEN SYMMETRIC |
Evaluates to TRUE if the value on the left of the operator is not in a given range between the first value on the right and the second value on the right or the second value on the right and the first value on the right. |
String Functions
string || string |
Concatenate two strings |
ASCII(string) |
ASCII code of the first character of the argument |
INITCAP(string) |
Convert the first letter of each word to upper case, and the rest to lower case |
LENGTH(string) |
Length of the string |
LIKE |
Return TRUE if the value string follows the pattern |
LIKE … ESCAPE |
Return TRUE if the value string follows the pattern, escaping a special character in the pattern |
LOWER(string) |
Convert the string to lower case |
LTRIM(string) |
Equivalent to TRIM(LEADING ' ' FROM string) |
RTRIM(string) |
Equivalent to TRIM(TRAILING ' ' FROM string) |
SUBSTRING(string FROM integer) |
Extract substring starting with the given position |
SUBSTRING(string FROM integer FOR integer) |
Extract substring starting with the given position for the given length |
TRIM([LEADING|TRAILING|BOTH] [characters FROM] string) |
Remove characters (a space by default) from the start/end/both ends of the string |
TRIM(characters FROM string) |
Equivalent to TRIM(BOTH characters FROM string) |
TRIM(string) |
Equivalent to TRIM(BOTH ' ' FROM string) |
UPPER(string) |
Convert the string to upper case |
Trigonometric Functions
ACOS(double) |
ASIN(double) |
ATAN(double) |
COS(double) |
COT(double) |
SIN(double) |
TAN(double) |