H2 database functions and data types

H2 database functions and data types

I. Common H2 Database Data Types

INT type: corresponding to java. lang. Integer

REAL Type: corresponding to java. lang. Float

DOUBLE Type: corresponding to java. lang. Double

DECIMAL type: corresponding to java. math. BigDecimal, such as DECIMAL (20, 2)

CHAR type: corresponding to java. lang. String, such as CHAR (10)

VARCHAR type: corresponding to java. lang. String

VARCHAR_IGNORECASE type: corresponding to java. lang. String, case insensitive

BOOLEAN Type: corresponding to java. lang. Boolean

TIME type: corresponding to java. SQL. Time. When converted to java. SQL. Date, the Date is set

DATE type: corresponding to java. SQL. Date, in the format of yyyy-MM-dd. The default time is 00:00:00.

TIMESTAMP type: corresponding to java. SQL. Timestamp. The format is yyyy-MM-dd hh: mm: ss [. nnnnnnnnn]. java. util. Date is also supported.

TINYINT type: corresponding to java. lang. Byte,-128 to 127

SMARTINT type: corresponding to java. lang. Short,-32768 to 32767

BIGINT type: corresponding to java. lang. Long

IDENTITY type: auto-increment type, corresponding to java. lang. Long, value range:-9223372036854775808 to 9223372036854775807

BINARY type: BINARY byte storage, which cannot exceed 2 GB and is fully stored in the memory, such as BINARY (1000)

BLOB type: corresponding to java. SQL. blob, similar to BINARY, but not fully stored in memory for large data (such as files or images), uses PreparedStatement. setBinaryStream to save data.

CLOB type: similar to VARCHAR, but suitable for storing large amounts of data, and the data is not completely stored in the memory. It is used for XML or HTML documents and text files of any size. Use PreparedStatement. setCharacterStream to save data.

OTHER type: corresponding to java. lang. object, used to store serialized Java objects. It uses byte arrays. The client can only perform serialization or deserialization, use getObject deserialization, and use PreparedStatement. setObject stores data.

UUID type: java. util. UUID, 128-bit value. You can use PreparedStatement. setBytes, setString or setObject (uuid) to save the data and use ResultSet. getObject to retrieve the data.
ARRAY type: corresponding to java. lang. Object []

Ii. Common H2 Functions

CURRENT_DATE: returns the current date.

CURRENT_TIME: returns the current time.

CURRENT_TIMESTAMP: returns the current date and time.

LOWER: lowercase string

UPPER: uppercase string

CONCAT: String Link

CHAR: ASCII value to character

ASCII: Convert character to ASCII Value

ENCRYPT: encryption function, supporting AES algorithm. The Block size is 16 bytes,

For example, call encrypt ('aes ', '00', STRINGTOUTF8 ('test '))

DECRYPT: decryption function, supporting AES algorithm. The Block size is 16 bytes,

For example, call trim (CHAR (0) FROM UTF8TOSTRING (

DECRYPT ('aes ', '00', '3fabb4de8f1ee2e97d7793bab2db1116 ')))

HASH: a HASH function that only supports SHA256 algorithms, such as call hash ('sha256 ', STRINGTOUTF8 ('Password'), 1000)

MAX: Maximize

MIN: Minimum

SUM: SUM

CURRENT_USER: returns the current user

H2VERSION: returns the version of the H2 database.

DISK_SPACE_USED: return the disk space used by the table, for example, CALL DISK_SPACE_USED ('My _ table ');

DATABASE_PATH: return the path and name of the database file, such as CALL DATABASE_PATH ();

This article permanently updates the link address: