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)

 

Mathematical Operators

 

 

+

Addition

-

Subtraction

*

Multiplication

/

Division

 

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)