Package generalDatabase

Class SQLTypes

java.lang.Object
generalDatabase.SQLTypes
Direct Known Subclasses:
MSAccessSQLTypes, MySQLSQLTypes, PostgreSQLTypes, SqliteSQLTypes, UCanAccessSqlTypes
public class SQLTypes extends Object
Utilities for converting between java.sql.types numeric and text formats.

SQL format can be slightly different between different databases. For example, MS Access allows you to put column names in "" which then enables you to use otherwise reserved words. MySQL on the other hand, will not let you do this.

This base class contains some default behaviours, but expect them to be overridden in many instances.

Author:
Doug Gillespie
See Also:

  • Constructor Details

    • SQLTypes

      public SQLTypes()

  • Method Details

    • typeToString

      public String typeToString(PamTableItem tableItem)

    • typeToString

      public String typeToString(int sqlType, int length)
      Converts a numeric SQL type and length to a text string that can be used in SQL statements. The length parameter is generally only required by text and character types.
      Parameters:
      sqlType - SQL type as defined in java.sql.Types
      length - length of character and text fields
      Returns:
      string representation of the type

    • typeToString

      public String typeToString(int sqlType, int length, boolean counter)

    • createTableItem

      public PamTableItem createTableItem(ResultSet columnData, ResultSetMetaData cmd)
      Work out some column information from the column result set.
      Parameters:
      columnData -
      cmd -
      Returns:

    • stringToLength

      public int stringToLength(String typeName)

    • stringToType

      public int stringToType(String typeName)

    • formatColumnName

      public String formatColumnName(PamTableItem tableItem)
      Format a column name from a table item. N.B that this functin can use the 'allowblanks' variable to make column names with blanks - allow blanks can be used for reading and converting old databases, but not recommended for anything being output from PAMGuard.
      Parameters:
      tableItem -
      Returns:
      a formatted column name.

    • formatColumnName

      public String formatColumnName(String columnName)
      Format the column name. Formats may be slightly different for different DBMS's. e.g. MS Access can put quotes around names. OODB requires them to be all upper case, etc.
      Parameters:
      columnName -
      Returns:
      formatted column name.

    • formatTableName

      public String formatTableName(String tableName)
      Any bespoke formatting of table names
      Parameters:
      tableName - input table name
      Returns:
      modified table name (e.g. to lower case)

    • systemSqlType

      public int systemSqlType(int sqlType)
      Some SQL types are unavailable on some systems, so qutomatically swap them for something else.
      Parameters:
      sqlType -
      Returns:

    • getTimeStamp

      public Object getTimeStamp(Long timeMillis)
      Get a time object in the appropriate format for whichever database we're using.
      Parameters:
      timeMillis - Time in milliseconds.
      Returns:
      Appropriate object (Generally TimeStamp, but SWLite uses Long)

    • getLocalTimeStamp

      public Object getLocalTimeStamp(long millis)
      Get a local timestamp using system default time zone.
      Parameters:
      millis - time in milliseconds.
      Returns:
      local timestamp.

    • millisFromTimeStamp

      public static Long millisFromTimeStamp(Object timeValue)
      convert a time stamp read from a database into milliseconds, bu tonly rounded to the nearest second
      Parameters:
      timestamp - GMT timestamp
      Returns:
      time in milliseconds.

    • formatDBDateTimeQueryString

      public String formatDBDateTimeQueryString(long timeMilliseconds)
      Format the time for insertion into a WHERE %s BETWEEN %s clause, mostly used by the Viewer. This has been wrapped here so that we can support a different format for SQLLite
      Parameters:
      timeMilliseconds - Time in milliseconds
      Returns:
      String to insert into a where clause.

    • formatDateTimeMatchString

      public String formatDateTimeMatchString(long timeMilliseconds)
      Allow slightly different formatting when we're trying to get an exact match of a milliseconds time.
      Parameters:
      timeMilliseconds - time in milliseconds.
      Returns:
      formatted time string.

    • getLongValue

      public Long getLongValue(Object object)
      Try to read a long value - which might be an Integer or some other type depending on the database.
      Parameters:
      object - Hopefully Long, but my be something else
      Returns:
      Long or null.

    • makeFloat

      public Float makeFloat(Double value)
      Make a Float object from a Double
      Parameters:
      value - Double value
      Returns:
      Float value

    • makeFloat

      public Float makeFloat(Object value)
      Try to make a Float object out of any type of object.
      Parameters:
      value - value
      Returns:
      Float object or null.

    • makeDouble

      public Double makeDouble(Float value)
      Make a Double object from a Float
      Parameters:
      value - Float value
      Returns:
      Double value

    • makeDouble

      public Double makeDouble(Object value)
      Try to make a Double value out of any type of object.
      Parameters:
      value - value
      Returns:
      Float object or null.

    • isAllowBlanks

      public boolean isAllowBlanks()
      Returns:
      the allowBlanks

    • setAllowBlanks

      public void setAllowBlanks(boolean allowBlanks)
      Parameters:
      allowBlanks - the allowBlanks to set

    • makeInList

      public String makeInList(Object[] array)
      Make an 'IN'list, without it's column name,e.g. IN ('SPW','DO','HP')
      Parameters:
      array - of objects to include in the list.
      Returns:
      combined list.