Package com.ultikits.ultitools.interfaces.impl.data.sqlite

Class SQLiteDataOperator<T extends BaseDataEntity<String>>

java.lang.Object
com.ultikits.ultitools.interfaces.impl.data.AbstractRelationalDataOperator<T>
com.ultikits.ultitools.interfaces.impl.data.sqlite.SQLiteDataOperator<T>
Type Parameters:
T - the entity type
All Implemented Interfaces:
DataOperator<T>, RowCountingDelete
public class SQLiteDataOperator<T extends BaseDataEntity<String>> extends AbstractRelationalDataOperator<T>
SQLite implementation of the data operator.

Extends the abstract relational operator, providing SQLite-specific table creation without engine specification.

Since:
6.0.0
Author:
wisdomme

  • Constructor Details

    • SQLiteDataOperator

      public SQLiteDataOperator(DataSource dataSource, Class<T> type)
      Creates a new SQLite data operator.
      Parameters:
      dataSource - the SQLite data source
      type - the entity class

  • Method Details

    • orderedComparisonOperand

      @Internal protected String orderedComparisonOperand(String column, Class<?> fieldType)
      Compares GREATER and LESS as numbers of the field's Java type: a byte, short, int or long field (or its wrapper) as a 64-bit integer through CAST(column AS BIGINT), a double field (or its wrapper) as a 64-bit floating-point number through CAST(column AS DOUBLE), and a float field (or its wrapper) through CAST(CAST(CAST(column AS REAL) AS VARCHAR) AS DOUBLE), which orderedComparisonPlaceholder(Class, Object) applies to the value as well. Every other type is compared as stored.

      A field left on the default @Column type is a VARCHAR(255) column, which SQLite gives TEXT affinity, so without the cast it compares a number as text and 2 > 10 is true (#675). The cast follows the field's Java type, not the column's declared type, so tables that already exist need no change. A cast target whose name contains INT has INTEGER affinity (64-bit) in SQLite and one containing DOUB has REAL affinity (64-bit); BIGINT and DOUBLE are also 64-bit types on the H2 stand-in, whose INTEGER is 32-bit and REAL single precision. Integral and floating fields get separate casts because a single REAL cast would lose whole numbers beyond 2^53.

      A float value is bound as the double it widens to (0.1f as 0.10000000149011612), and a default column keeps SQLite's 15-significant-digit text of that double (0.100000001490116). The text reads back as exactly 0.1f, but compared with the bound value it is smaller, so LESS 0.1f would select the row holding 0.1f. Passing both sides through the same conversion to text compares them the way Java compares the values as they read back: two different finite floats differ well before the 15th significant digit, so the conversion keeps their order and never makes them equal. The conversion is applied to the value only when it is a number whose double value is finite (see orderedComparisonPlaceholder(Class, Object)): SQLite writes an infinite value as the text Inf, which converts back to 0, and converts text that is not a number to 0. Every other value (an infinity, NaN, a BigDecimal or BigInteger beyond the range of a double, or any text) is bound as it is and compared with the converted column, as before the conversion existed. A finite number of another type is converted too, so a whole number or an exact decimal equal to a stored float, such as 2^60 as a Long, compares as equal to it. One case stays approximate: a number (not only a double) that agrees with a stored float to 15 significant digits but differs after them, such as a whole number of 10^15 or more like (1L << 53) + 1 or a decimal with 16 or more significant digits, compares as equal to it, in a default column and also in a column declared REAL, where the stored value is exact but the conversion still rounds it to 15 digits. The inner REAL cast changes nothing on SQLite, whose REAL is 64-bit; on the H2 stand-in, where REAL is single precision, it first rounds the stored or bound value to the float it holds. A double field keeps the plain bound value: the value it reads back is the stored text read as a double, which is what CAST(column AS DOUBLE) compares.

      Overrides:
      orderedComparisonOperand in class AbstractRelationalDataOperator<T extends BaseDataEntity<String>>
      Parameters:
      column - an allow-listed @Column name of the entity
      fieldType - the Java type of the field mapped to column
      Returns:
      the SQL operand to compare, e.g. column or a cast of it
      Since:
      6.3.0

    • orderedComparisonPlaceholder

      @Internal protected String orderedComparisonPlaceholder(Class<?> fieldType, Object value)
      Passes the value of a GREATER or LESS condition on a float field (or its wrapper) through the same conversion as the column, CAST(CAST(CAST(? AS REAL) AS VARCHAR) AS DOUBLE), so both sides keep the same 15 significant digits (see orderedComparisonOperand(String, Class)), when the value is a Number whose double value is finite. Any other value on a float field, and every value on a field of another type, is bound unchanged: the conversion would turn an infinity, a number beyond the range of a double, or text that is not a number into 0.
      Overrides:
      orderedComparisonPlaceholder in class AbstractRelationalDataOperator<T extends BaseDataEntity<String>>
      Parameters:
      fieldType - the Java type of the field the condition compares
      value - the condition's value, which is bound to the placeholder; may be null
      Returns:
      the SQL operand holding exactly one ?, e.g. ? or a cast of it
      Since:
      6.3.0

    • isFiniteNumber

      private static boolean isFiniteNumber(Object value)
      Whether value is a number whose double value is finite (not infinite, not NaN).

    • throughStoredFloatText

      private static String throughStoredFloatText(String operand)
      The float conversion both sides share; operand is a column name or ?.

    • isIntegral

      private static boolean isIntegral(Class<?> type)

    • isFloat

      private static boolean isFloat(Class<?> type)

    • isDouble

      private static boolean isDouble(Class<?> type)

    • createTableSqlFromClazz

      protected String createTableSqlFromClazz(Class<T> type)
      Description copied from class: AbstractRelationalDataOperator
      Creates the SQL statement to create the table.

      Override this method to customize table creation for different databases.

      Specified by:
      createTableSqlFromClazz in class AbstractRelationalDataOperator<T extends BaseDataEntity<String>>
      Parameters:
      type - the entity class
      Returns:
      the CREATE TABLE SQL statement