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

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

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

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.
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 hav it returned as an object.
java.lang.Object	getObject(java.lang.String _field) Read a value from the specified field to hav 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

getObject

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

Read a value from the specified field to hav 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 hav 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