de.bezier.data.sql

## Class SQL

- java.lang.Object
- - de.bezier.data.sql.SQL

- Direct Known Subclasses:
    MySQL, PostgreSQL, SQLite

      public abstract class SQL
      extends java.lang.Object

  # SQL library for Processing 2+

  Since v 0.2.0 it has some ORM like features, see
  - setFromRow(Object)
  - saveToDatabase(Object)
  - insertUpdateIntoDatabase(String,Object[],Object[])
  Links:
  - http://www.mysql.com/products/connector/j/
  - http://java.sun.com/products/jdbc/
  - http://www.toxi.co.uk/blog/2007/07/using-javadb-and-db4o-in-processing.htm
  - http://www.tom-carden.co.uk/2007/07/30/a-quick-note-on-using-sqlite-in-processing/
  Author:
    Florian Jenett - mail@florianjenett.de created: 07.05.2005 - 12:46 Uhr modified: fjenett 20121217

- - ### Field Summary

    Fields
    Modifier and Type | Field and Description
    --- | ---
    `java.sql.Connection` | `connection`
    `java.lang.String` | `database`
    `java.lang.String` | `driver`
    `java.lang.String` | `previousQuery`
    `java.sql.ResultSet` | `result`
    `java.lang.String` | `server`
    `java.sql.Statement` | `statement`
    `java.lang.String` | `type`
    `java.lang.String` | `url`
    `java.lang.String` | `user`

  - ### Constructor Summary

    Constructors
    Constructor and Description
    `SQL()` Do not use this contructor.
    `SQL(processing.core.PApplet _pa, java.lang.String _db)` You should not directly use the SQL.class instead use the classes for your database type.
    `SQL(processing.core.PApplet _pa, java.lang.String _serv, java.lang.String _db, java.lang.String _u, java.lang.String _p)` You should not directly use the SQL.class instead use the classes for your database type.

  - ### Method Summary

    All Methods Instance Methods Abstract Methods Concrete Methods
    Modifier and Type | Method and Description
    --- | ---
    `void` | `close()` Close the database connection
    `boolean` | `connect()` Open the database connection with the parameters given in the contructor.
    `void` | `dispose()` Callback function for PApplet.registerDispose()
    `java.lang.String` | `escape(java.lang.Object o)` Generate an escaped String for a given Object
    `void` | `execute(java.lang.String _sql)` Execute a SQL command on the open database connection.
    `void` | `execute(java.lang.String _sql, java.lang.Object... args)` Execute a SQL command on the open database connection.
    `java.math.BigDecimal` | `getBigDecimal(int _column)` Read a java.math.BigDecimal value from the specified field.
    `java.math.BigDecimal` | `getBigDecimal(java.lang.String _field)` Read a java.math.BigDecimal value from the specified field.
    `byte[]` | `getBlob(int _column)` Read a value from the specified field to have it returned as an byte[]
    `byte[]` | `getBlob(java.lang.String _field)` Read a value from the specified field to have it returned as an byte[]
    `boolean` | `getBoolean(int _column)` Read a boolean value from the specified field.
    `boolean` | `getBoolean(java.lang.String _field)` Read a boolean value from the specified field.
    `java.lang.String[]` | `getColumnNames()` Returns an array with the column names of the last request.
    `java.sql.Connection` | `getConnection()` Get connection.
    `java.sql.Date` | `getDate(int _column)` Read a java.sql.Date value from the specified field.
    `java.sql.Date` | `getDate(java.lang.String _field)` Read a java.sql.Date value from the specified field.
    `boolean` | `getDebug()` Get current debugging setting
    `double` | `getDouble(int _column)` Read a double value from the specified field.
    `double` | `getDouble(java.lang.String _field)` Read a double value from the specified field.
    `java.lang.String` | `getDriverVersion()` Return the version of the currently active JDBC driver
    `float` | `getFloat(int _column)` Read a float value from the specified field.
    `float` | `getFloat(java.lang.String _field)` Read a float value from the specified field.
    `int` | `getInt(int _column)` Read an integer value from the specified field.
    `int` | `getInt(java.lang.String _field)` Read an integer value from the specified field.
    `long` | `getLong(int _column)` Read a long value from the specified field.
    `long` | `getLong(java.lang.String _field)` Read a long value from the specified field.
    `NameMapper` | `getNameMapper()` Get the current NameMapper
    `java.lang.Object` | `getObject(int _column)` Read a value from the specified field to have it returned as an object.
    `java.lang.Object` | `getObject(java.lang.String _field)` Read a value from the specified field to have it returned as an object.
    `java.lang.String` | `getString(int _column)` Read a String value from the specified field.
    `java.lang.String` | `getString(java.lang.String _field)` Read a String value from the specified field.
    `abstract java.lang.String[]` | `getTableNames()` Get names of available tables in active database, needs to be implemented per db adapter.
    `java.sql.Time` | `getTime(int _column)` Read a java.sql.Time value from the specified field.
    `java.sql.Time` | `getTime(java.lang.String _field)` Read a java.sql.Time value from the specified field.
    `java.sql.Timestamp` | `getTimestamp(int _column)` Read a java.sql.Timestamp value from the specified field.
    `java.sql.Timestamp` | `getTimestamp(java.lang.String _field)` Read a java.sql.Timestamp value from the specified field.
    `void` | `insertUpdateInDatabase(java.lang.String tableName, java.lang.String[] columnNames, java.lang.Object[] values)` Insert or update a bunch of values in the database.
    `java.lang.String` | `nameToGetter(java.lang.String name)` Convert a field name to a getter name: fieldName -> getFieldName().
    `java.lang.String` | `nameToSetter(java.lang.String name)` Convert a field name to a setter name: fieldName -> setFieldName().
    `boolean` | `next()` Check if more results (rows) are available.
    `void` | `query(java.lang.String _sql)` Issue a query on the open database connection
    `void` | `query(java.lang.String _sql, java.lang.Object... args)` Issue a query on the open database connection.
    `void` | `registerTableNameForClass(java.lang.String name, java.lang.Object classOrObject)` Set a table name for a class.
    `void` | `saveToDatabase(java.lang.Object object)` Take an object, try to find table name from object name (or look it up), get fields and getters from object and pass that on to insertUpdateIntoDatabase(table, columns, values).
    `void` | `saveToDatabase(java.lang.String tableName, java.lang.Object object)` Takes a table name and an object and tries to construct a set of columns names from fields and getters found in the object.
    `void` | `setDebug(boolean yesNo)` Turn some debugging on/off.
    `void` | `setFromRow(java.lang.Object object)` Highly experimental ... tries to map column names to public fields or setter methods in the given object.
    `void` | `setNameMapper(NameMapper mapper)` Set the current NameMapper

    - ### Methods inherited from class java.lang.Object

      `equals, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait`

- - ### Field Detail

    - #### server

          public java.lang.String server

    - #### database

          public java.lang.String database

    - #### url

          public java.lang.String url

    - #### user

          public java.lang.String user

    - #### driver

          public java.lang.String driver

    - #### type

          public java.lang.String type

    - #### connection

          public java.sql.Connection connection

    - #### previousQuery

          public java.lang.String previousQuery

    - #### statement

          public java.sql.Statement statement

    - #### result

          public java.sql.ResultSet result

  - ### Constructor Detail

    - #### SQL

          public SQL()

      Do not use this contructor.

    - #### SQL

          public SQL(processing.core.PApplet _pa,
                     java.lang.String _db)

      You should not directly use the SQL.class instead use the classes for your database type.

    - #### SQL

          public SQL(processing.core.PApplet _pa,
                     java.lang.String _serv,
                     java.lang.String _db,
                     java.lang.String _u,
                     java.lang.String _p)

      You should not directly use the SQL.class instead use the classes for your database type.

  - ### Method Detail

    - #### setDebug

          public void setDebug(boolean yesNo)

      Turn some debugging on/off.
      Parameters:
        `yesNo` - Turn it on or off

    - #### getDebug

          public boolean getDebug()

      Get current debugging setting
      Parameters:
        `yesNo` - Turn it on or off

    - #### connect

          public boolean connect()

      Open the database connection with the parameters given in the contructor.

    - #### getDriverVersion

          public java.lang.String getDriverVersion()

      Return the version of the currently active JDBC driver
      Returns:
        String The version of the current driver

    - #### execute

          public void execute(java.lang.String _sql)

      Execute a SQL command on the open database connection.
      Parameters:
        `_sql` - The SQL command to execute

    - #### execute

          public void execute(java.lang.String _sql,
                              java.lang.Object... args)

      Execute a SQL command on the open database connection. Arguments are passed to String.format() first.
      Parameters:
        `_sql` - SQL command as pattern for String.format()
        `args` - Zero or more objects to be passed to String.format()
      See Also:
        Format syntax, `String.format(java.lang.String,java.lang.Object...)`

    - #### query

          public void query(java.lang.String _sql)

      Issue a query on the open database connection
      Parameters:
        `_sql` - SQL command to execute for the query

    - #### query

          public void query(java.lang.String _sql,
                            java.lang.Object... args)

      Issue a query on the open database connection. Arguments are passed to String.format() first.
      Parameters:
        `_sql` - SQL command as pattern for String.format()
        `args` - Zero or more objects to be passed to String.format()
      See Also:
        Format syntax, `String.format(java.lang.String,java.lang.Object...)`

    - #### next

          public boolean next()

      Check if more results (rows) are available. This needs to be called before any results can be retrieved.
      Returns:
        boolean true if more results are available, false otherwise

    - #### getTableNames

          public abstract java.lang.String[] getTableNames()

      Get names of available tables in active database, needs to be implemented per db adapter.
      Returns:
        String[] The table names

    - #### getColumnNames

          public java.lang.String[] getColumnNames()

      Returns an array with the column names of the last request.
      Returns:
        String[] the column names of last result or null

    - #### getConnection

          public java.sql.Connection getConnection()

      Get connection. ... in case you want to do JDBC stuff directly.
      Returns:
        java.sql.Connection The connection

    - #### getInt

          public int getInt(java.lang.String _field)

      Read an integer value from the specified field. Represents an INT / INTEGER type: http://java.sun.com/j2se/1.3/docs/guide/jdbc/getstart/mapping.html "8.9.6 Conversions by ResultSet.getXXX Methods"
      Parameters:
        `_field` - The name of the field
      Returns:
        int Value of the field or 0

    - #### getInt

          public int getInt(int _column)

      Read an integer value from the specified field. Represents an INT / INTEGER type: http://java.sun.com/j2se/1.3/docs/guide/jdbc/getstart/mapping.html "8.9.6 Conversions by ResultSet.getXXX Methods"
      Parameters:
        `_column` - The column index of the field to read
      Returns:
        int Value of the field or 0

    - #### getLong

          public long getLong(java.lang.String _field)

      Read a long value from the specified field. Represents a BIGINT type: http://java.sun.com/j2se/1.3/docs/guide/jdbc/getstart/mapping.html "8.9.6 Conversions by ResultSet.getXXX Methods"
      Parameters:
        `_field` - The name of the field
      Returns:
        long Value of the field or 0

    - #### getLong

          public long getLong(int _column)

      Read a long value from the specified field. Represents a BIGINT type: http://java.sun.com/j2se/1.3/docs/guide/jdbc/getstart/mapping.html "8.9.6 Conversions by ResultSet.getXXX Methods"
      Parameters:
        `_column` - The column index of the field
      Returns:
        long Value of the field or 0

    - #### getFloat

          public float getFloat(java.lang.String _field)

      Read a float value from the specified field. Represents a REAL type: http://java.sun.com/j2se/1.3/docs/guide/jdbc/getstart/mapping.html "8.9.6 Conversions by ResultSet.getXXX Methods"
      Parameters:
        `_field` - The name of the field
      Returns:
        float Value of the field or 0

    - #### getFloat

          public float getFloat(int _column)

      Read a float value from the specified field. Represents a REAL type: http://java.sun.com/j2se/1.3/docs/guide/jdbc/getstart/mapping.html "8.9.6 Conversions by ResultSet.getXXX Methods"
      Parameters:
        `_column` - The index of the column of the field
      Returns:
        float Value of the field or 0

    - #### getDouble

          public double getDouble(java.lang.String _field)

      Read a double value from the specified field. Represents FLOAT and DOUBLE types: http://java.sun.com/j2se/1.3/docs/guide/jdbc/getstart/mapping.html "8.9.6 Conversions by ResultSet.getXXX Methods"
      Parameters:
        `_field` - The name of the field
      Returns:
        double Value of the field or 0

    - #### getDouble

          public double getDouble(int _column)

      Read a double value from the specified field. Represents FLOAT and DOUBLE types: http://java.sun.com/j2se/1.3/docs/guide/jdbc/getstart/mapping.html "8.9.6 Conversions by ResultSet.getXXX Methods"
      Parameters:
        `_column` - The column index of the field
      Returns:
        double Value of the field or 0

    - #### getBigDecimal

          public java.math.BigDecimal getBigDecimal(java.lang.String _field)

      Read a java.math.BigDecimal value from the specified field. Represents DECIMAL and NUMERIC types: http://java.sun.com/j2se/1.3/docs/guide/jdbc/getstart/mapping.html "8.9.6 Conversions by ResultSet.getXXX Methods"
      Parameters:
        `_field` - The name of the field
      Returns:
        java.math.BigDecimal Value of the field or null

    - #### getBigDecimal

          public java.math.BigDecimal getBigDecimal(int _column)

      Read a java.math.BigDecimal value from the specified field. Represents DECIMAL and NUMERIC types: http://java.sun.com/j2se/1.3/docs/guide/jdbc/getstart/mapping.html "8.9.6 Conversions by ResultSet.getXXX Methods"
      Parameters:
        `_column` - The column index of the field
      Returns:
        java.math.BigDecimal Value of the field or null

    - #### getBoolean

          public boolean getBoolean(java.lang.String _field)

      Read a boolean value from the specified field. Represents BIT type: http://java.sun.com/j2se/1.3/docs/guide/jdbc/getstart/mapping.html "8.9.6 Conversions by ResultSet.getXXX Methods"
      Parameters:
        `_field` - The name of the field
      Returns:
        boolean Value of the field or 0

    - #### getBoolean

          public boolean getBoolean(int _column)

      Read a boolean value from the specified field. Represents BIT type: http://java.sun.com/j2se/1.3/docs/guide/jdbc/getstart/mapping.html "8.9.6 Conversions by ResultSet.getXXX Methods"
      Parameters:
        `_column` - The column index of the field
      Returns:
        boolean Value of the field or 0

    - #### getString

          public java.lang.String getString(java.lang.String _field)

      Read a String value from the specified field. Represents VARCHAR and CHAR types: http://java.sun.com/j2se/1.3/docs/guide/jdbc/getstart/mapping.html "8.9.6 Conversions by ResultSet.getXXX Methods"
      Parameters:
        `_field` - The name of the field
      Returns:
        String Value of the field or null

    - #### getString

          public java.lang.String getString(int _column)

      Read a String value from the specified field. Represents VARCHAR and CHAR types: http://java.sun.com/j2se/1.3/docs/guide/jdbc/getstart/mapping.html "8.9.6 Conversions by ResultSet.getXXX Methods"
      Parameters:
        `_column` - The column index of the field
      Returns:
        String Value of the field or null

    - #### getDate

          public java.sql.Date getDate(java.lang.String _field)

      Read a java.sql.Date value from the specified field. Represents DATE type: http://java.sun.com/j2se/1.3/docs/guide/jdbc/getstart/mapping.html "8.9.6 Conversions by ResultSet.getXXX Methods"
      Parameters:
        `_field` - The name of the field
      Returns:
        java.sql.Date Value of the field or null

    - #### getDate

          public java.sql.Date getDate(int _column)

      Read a java.sql.Date value from the specified field. Represents DATE type: http://java.sun.com/j2se/1.3/docs/guide/jdbc/getstart/mapping.html "8.9.6 Conversions by ResultSet.getXXX Methods"
      Parameters:
        `_column` - The column index of the field
      Returns:
        java.sql.Date Value of the field or null

    - #### getTime

          public java.sql.Time getTime(java.lang.String _field)

      Read a java.sql.Time value from the specified field. Represents TIME type: http://java.sun.com/j2se/1.3/docs/guide/jdbc/getstart/mapping.html "8.9.6 Conversions by ResultSet.getXXX Methods"
      Parameters:
        `_field` - The name of the field
      Returns:
        java.sql.Time Value of the field or null

    - #### getTime

          public java.sql.Time getTime(int _column)

      Read a java.sql.Time value from the specified field. Represents TIME type: http://java.sun.com/j2se/1.3/docs/guide/jdbc/getstart/mapping.html "8.9.6 Conversions by ResultSet.getXXX Methods"
      Parameters:
        `_column` - The column index of the field
      Returns:
        java.sql.Time Value of the field or null

    - #### getTimestamp

          public java.sql.Timestamp getTimestamp(java.lang.String _field)

      Read a java.sql.Timestamp value from the specified field. Represents TIMESTAMP type: http://java.sun.com/j2se/1.3/docs/guide/jdbc/getstart/mapping.html "8.9.6 Conversions by ResultSet.getXXX Methods"
      Parameters:
        `_field` - The name of the field
      Returns:
        java.sql.Timestamp Value of the field or null

    - #### getTimestamp

          public java.sql.Timestamp getTimestamp(int _column)

      Read a java.sql.Timestamp value from the specified field. Represents TIMESTAMP type: http://java.sun.com/j2se/1.3/docs/guide/jdbc/getstart/mapping.html "8.9.6 Conversions by ResultSet.getXXX Methods"
      Parameters:
        `_column` - The column index of the field
      Returns:
        java.sql.Timestamp Value of the field or null

    - #### getBlob

          public byte[] getBlob(java.lang.String _field)

      Read a value from the specified field to have it returned as an byte[]
      Parameters:
        `_field` - The name of the field
      Returns:
        byte[] byte array representing a blob

    - #### getBlob

          public byte[] getBlob(int _column)

      Read a value from the specified field to have it returned as an byte[]
      Parameters:
        `_column` - The column index of the field
      Returns:
        byte[] byte array representing a blob

    - #### getObject

          public java.lang.Object getObject(java.lang.String _field)

      Read a value from the specified field to have it returned as an object.
      Parameters:
        `_field` - The name of the field
      Returns:
        Object Value of the field or null

    - #### getObject

          public java.lang.Object getObject(int _column)

      Read a value from the specified field to have it returned as an object.
      Parameters:
        `_column` - The column index of the field
      Returns:
        Object Value of the field or null

    - #### close

          public void close()

      Close the database connection

    - #### dispose

          public void dispose()

      Callback function for PApplet.registerDispose()
      See Also:
        `processing.core.PApplet.registerDispose(java.lang.Object)`

    - #### escape

          public java.lang.String escape(java.lang.Object o)

      Generate an escaped String for a given Object
      Parameters:
        `object` - the Object to escape
      Returns:
        String the ecaped String representation of the Object

    - #### setNameMapper

          public void setNameMapper(NameMapper mapper)

      Set the current NameMapper
      Parameters:
        `mapper` - the name mapper
      See Also:
        `NameMapper`

    - #### getNameMapper

          public NameMapper getNameMapper()

      Get the current NameMapper
      See Also:
        `NameMapper`

    - #### setFromRow

          public void setFromRow(java.lang.Object object)

      Highly experimental ...
      tries to map column names to public fields or setter methods in the given object.

      Use like so:

                  db.query("SELECT name, id, sometime FROM table");

                  while ( db.next() ) {
                          SomeObject obj = new SomeObject();
                          db.setFromRow(obj);
                          // obj.name is now same as db.getString("name"), etc.
            }

      SomeObject might look like:

                  class SomeObject {
                          public String name;
                          public int id;
                          Date sometime;
                  }

      Parameters:
        `object` - The object to populate from the currently selected row

    - #### nameToSetter

          public java.lang.String nameToSetter(java.lang.String name)

      Convert a field name to a setter name: fieldName -> setFieldName().

    - #### nameToGetter

          public java.lang.String nameToGetter(java.lang.String name)

      Convert a field name to a getter name: fieldName -> getFieldName().

    - #### registerTableNameForClass

          public void registerTableNameForClass(java.lang.String name,
                                                java.lang.Object classOrObject)

      Set a table name for a class.

    - #### saveToDatabase

          public void saveToDatabase(java.lang.Object object)

      Take an object, try to find table name from object name (or look it up), get fields and getters from object and pass that on to insertUpdateIntoDatabase(table, columns, values).
      Parameters:
        `object` - Object The object to save to db
      See Also:
        `insertUpdateInDatabase(java.lang.String, java.lang.String[], java.lang.Object[])`

    - #### saveToDatabase

          public void saveToDatabase(java.lang.String tableName,
                                     java.lang.Object object)

      Takes a table name and an object and tries to construct a set of columns names from fields and getters found in the object. After the values are fetched from the object all is passed to insertUpdateIntoDatabase().
      Parameters:
        `tableName` - String The name of the table
        `object` - Object The object to look at
      See Also:
        `insertUpdateInDatabase(java.lang.String, java.lang.String[], java.lang.Object[])`

    - #### insertUpdateInDatabase

          public void insertUpdateInDatabase(java.lang.String tableName,
                                             java.lang.String[] columnNames,
                                             java.lang.Object[] values)

      Insert or update a bunch of values in the database. If the given table has a primary key the entry will be updated if it already existed.
      Parameters:
        `tableName` - String The name of the table
        `columnNames` - String[] The names of the columns to fill or update
        `values` - Object[] The values to instert or update

Processing library BezierSQLib (formerly SQLibrary) by Florian Jenett. (c) 2005 - 2023