Tutorial: SQLite and Processing Part II Files

--D. Thiebaut (talk) 17:27, 31 July 2013 (EDT)

---

sqlite-jdbc-3.7.2.jar

• The sqlite-jdbc-3.7.2.jar file is available here for convenience.

SQL.java

//package de.bezier.data.sql;
import processing.core.*;
//import de.bezier.data.sql.mapper.*;
import mapper.*
import java.io.*;
import java.sql.*;
import java.lang.reflect.*;
import java.util.HashMap;
import java.util.Map;
import java.util.ArrayList;

/**
 *		<h1>SQL library for Processing 2+</h1>
 *
 *		Since v 0.2.0 it has some ORM like features, see 
 *		<ul>
 *			<li><a href="#setFromRow(java.lang.Object)">setFromRow(Object)</a></li>
 *			<li><a href="#saveToDatabase(java.lang.Object)">saveToDatabase(Object)</a></li>
 *			<li><a href="#insertUpdateInDatabase(java.lang.String, java.lang.String[], java.lang.Object[])">insertUpdateIntoDatabase(String,Object[],Object[])</a></li>
 *		</ul>
 *		
 *		Links:<ul>
 *		<li>http://www.mysql.com/products/connector/j/</li>
 *		<li>http://java.sun.com/products/jdbc/</li>
 *		<li>http://www.toxi.co.uk/blog/2007/07/using-javadb-and-db4o-in-processing.htm</li>
 *		<li>http://www.tom-carden.co.uk/2007/07/30/a-quick-note-on-using-sqlite-in-processing/</li>
 *		</ul>
 *
 *		@author 		Florian Jenett - mail@florianjenett.de
 *
 *		created:		07.05.2005 - 12:46 Uhr
 *		modified:		fjenett 20121217
 */

abstract public class SQL
{
	PApplet papplet;

	public String server;
	public String database;
	public String url;
	public String user;
	protected String pass;
	public String driver = "";
	public String type = "";

	private int driverMinorVersion = -1;
	private int driverMajorVersion = -1;
	
	public java.sql.Connection connection;
	public String previousQuery;
	
	public java.sql.Statement statement;
	public java.sql.ResultSet result;
	
	private boolean DEBUG = true;
	private HashMap<ResultSet,String[]> columnNamesCache;
	private NameMapper mapper;
	private HashMap<Class,String> classToTableMap;
	protected ArrayList<String> tableNames;
	
	/**
	 *	Do not use this contructor.
	 */
	public SQL ()
	{
		System.out.println(
			"SQL(): Please use this constructor\n"+
			"\tSQL ( String _serv, String _db, String _u, String _p, PApplet _pa )"
		);
		
		//mapper = new de.bezier.data.sql.mapper.UnderScoreToCamelCaseMapper();
		mapper = new UnderScoreToCamelCaseMapper();
	}
	

	/**
	 *	You should not directly use the SQL.class instead use the classes for your database type.
	 */
	public SQL ( PApplet _pa, String _db )
	{
		this.user = "";
		this.pass = "";
		this.server = "";
		
		String f = _pa.dataPath(_db);
		File ff = new File(f);
		if ( !ff.exists() || !ff.canRead() )
		{
			f = _pa.sketchPath( _db );
			ff = new File(f);
			
			if ( !ff.exists() || !ff.canRead() )
			{
				f = _db;
				ff = new File(f);
				
				if ( !ff.exists() || !ff.canRead() )
				{
					System.err.println(
						"Sorry can't find any file named " + _db +
						" make sure it exists and the path is correct."
					);
				}
			}
		}
		
		_pa.println( "Using this database: " + f );
		
		this.database = f;
	
		this.url = "jdbc:" + type + ":" + database;
	
		this.papplet = _pa;
		papplet.registerDispose( this );
		//mapper = new de.bezier.data.sql.mapper.UnderScoreToCamelCaseMapper();
		mapper = new UnderScoreToCamelCaseMapper();
	}
	
	
	/**
	 *	You should not directly use the SQL.class instead use the classes for your database type.
	 */
	public SQL ( PApplet _pa, String _serv, String _db, String _u, String _p )
	{
		this.server = _serv;
		this.database = _db;
		
		this.url = "jdbc:" + type + "://" + server +  "/" + database;
		
		this.user = _u;
		this.pass = _p;
		
		this.papplet = _pa;
		papplet.registerDispose( this );
		//mapper = new de.bezier.data.sql.mapper.UnderScoreToCamelCaseMapper();
		mapper = new UnderScoreToCamelCaseMapper();
	}
	

	/**
	 *	Turn some debugging on/off.
	 *
	 *	@param 	yesNo	Turn it on or off
	 */
	public void setDebug ( boolean yesNo )
	{
		DEBUG = yesNo;
	}
	

	/**
	 *	Get current debugging setting
	 *
	 *	@param 	yesNo	Turn it on or off
	 */
	public boolean getDebug ()
	{
		return DEBUG;
	}
	

	/**
	 *	Open the database connection with the parameters given in the contructor.
	 */
	public boolean connect()
	{
		if ( driver == null || driver.equals("") ||
			 type == null || type.equals("") )
		{
			System.out.println( "SQL.connect(): You have to set a driver and type first." );
			return false;
		}
		
		// TODO: need to add mechanisms for different connection types and parameters, see:
		// http://jdbc.postgresql.org/documentation/83/connect.html
	
		try
		{
			Class.forName(driver);
			connection = java.sql.DriverManager.getConnection(url, user, pass);
			
		}
		catch (ClassNotFoundException e)
		{
			System.out.println( "SQL.connect(): Could not find the database driver ( "+driver+" ).\r" );
			if (DEBUG) e.printStackTrace();
			return false;
			
		}
		catch (java.sql.SQLException e)
		{
			System.out.println( "SQL.connect(): Could not connect to the database ( "+url+" ).\r" );
			if (DEBUG) e.printStackTrace();
			return false;
			
		}
		
		getTableNames();

		try {
			Driver jdbcDriver = java.sql.DriverManager.getDriver( url );
			if ( jdbcDriver != null ) {
				driverMinorVersion = jdbcDriver.getMinorVersion();
				driverMajorVersion = jdbcDriver.getMajorVersion();

				if ( DEBUG ) System.out.println( "Using driver " + getDriverVersion() );
			}
		} catch ( SQLException sqle ) {
			sqle.printStackTrace();
		}
		
		return true;
	}


	/**
	 *	Return the version of the currently active JDBC driver
	 *
	 *	@return String The version of the current driver
	 */
	public String getDriverVersion ()
	{
		if ( connection == null ) {
			System.out.println( "SQL.getDriverVersion(): you need to connect() first" );
			return null;
		} else if ( driver == null || driver.equals("") ) {
			System.out.println( "SQL.getDriverVersion(): no driver specified ... or it is null" );
			return null;
		}
		return driver + " " + driverMajorVersion + "." + driverMinorVersion;
	}
	
	private void preQueryOrExecute () 
	{
		result = null;
	}
	

	/**
	 *	Execute a SQL command on the open database connection.
	 *
	 *	@param	_sql	The SQL command to execute
	 */
	public void execute ( String _sql )
	{
		preQueryOrExecute();
		
		query( _sql, false );
	}


	/**
	 *	Execute a SQL command on the open database connection. 
	 *	Arguments are passed to String.format() first.
	 *
	 *	@param	_sql	SQL command as pattern for String.format()
	 *	@param	args	Zero or more objects to be passed to String.format()
	 *
	 *  @see <a href="http://docs.oracle.com/javase/1.5.0/docs/api/java/util/Formatter.html#syntax">Format syntax</a>
	 *	@see java.lang.String#format(java.lang.String,java.lang.Object...)
	 */
	public void execute ( String _sql, Object ... args )
	{
		preQueryOrExecute();
		
		if ( args == null || args.length == 0 ) queryOrExecute( _sql, false );
		
		Method meth = null;
		try {
			meth = String.class.getMethod( 
				"format", 
				String.class, 
				java.lang.reflect.Array.newInstance(Object.class,0).getClass() 
			);
		} catch ( Exception ex ) {
			ex.printStackTrace();
			return;
		}
		// Object[] args2 = new Object[args.length+1];
		// args2[0] = _sql;
		// System.arraycopy( args, 0, args2, 1, args.length );
		String sql2 = null;
		try {
			sql2 = (String)meth.invoke( null, _sql, args );
		} catch ( Exception ex ) {
			if (DEBUG) ex.printStackTrace();
		}
		
		queryOrExecute( sql2, false );
	}
	
	
	/**
	 *	Issue a query on the open database connection
	 *
	 *	@param	_sql	SQL command to execute for the query
	 */
	public void query ( String _sql )
	{
		preQueryOrExecute();
		
		queryOrExecute( _sql, true );
	}


	/**
	 *	Issue a query on the open database connection.
	 *	Arguments are passed to String.format() first.
	 *
	 *	@param _sql	SQL command as pattern for String.format()
	 *  @param args	Zero or more objects to be passed to String.format()
	 *
	 *  @see <a href="http://docs.oracle.com/javase/1.5.0/docs/api/java/util/Formatter.html#syntax">Format syntax</a>
	 *	@see java.lang.String#format(java.lang.String,java.lang.Object...)
	 */
	public void query ( String _sql, Object ... args )
	{
		preQueryOrExecute();
		
		if ( args == null || args.length == 0 ) queryOrExecute( _sql, true );
		
		Method meth = null;
		try {
			meth = String.class.getMethod( 
				"format", 
				String.class, 
				java.lang.reflect.Array.newInstance(Object.class,0).getClass() 
			);
		} catch ( Exception ex ) {
			ex.printStackTrace();
			return;
		}

		String sql2 = null;
		try {
			sql2 = (String)meth.invoke( null, _sql, args );
		} catch ( Exception ex ) {
			if (DEBUG) ex.printStackTrace();
		}
		
		queryOrExecute( sql2, true );
	}
	

	/**
	 *	Query implemenbtation called by execute() / query()
	 */
	private void queryOrExecute ( String _sql, boolean keep )
	{
		if ( connection == null )
		{
			System.out.println( "SQL.query(): You need to connect() first." );
			return;
		}
		
		previousQuery = _sql;
		
		try
		{
			if ( statement == null )
			{
				statement = connection.createStatement();
			}
			
			boolean hasResults = statement.execute( _sql );

			if ( keep && hasResults )
			{
				this.result = statement.getResultSet();
			}
		}
		catch ( java.sql.SQLException e )
		{
			System.out.println( "SQL.query(): java.sql.SQLException.\r" );
			if (DEBUG) {
				System.out.println( _sql );
				e.printStackTrace();
			}
		}
	}
	

	/**
	 *	Check if more results (rows) are available. This needs to be called before any results can be retrieved.
	 *
	 *	@return	boolean	true if more results are available, false otherwise
	 */
	public boolean next ()
	{	
		if ( result == null )
		{
			System.out.println( "SQL.next(): You need to query() something first." );
			return false;
		}
		
		try
		{
			return result.next();
		}
		catch ( java.sql.SQLException e )
		{
			System.out.println( "SQL.next(): java.sql.SQLException.\r" );
			if ( DEBUG ) e.printStackTrace();
		}
		return false;
	}
	

	/**
	 *	Get names of available tables in active database,
	 *	needs to be implemented per db adapter.
	 *
	 * @return String[] The table names
	 */
	abstract public String[] getTableNames ();
	

	/**
	 *	Returns an array with the column names of the last request.
	 *
	 *	@return String[] the column names of last result or null
	 */
	public String[] getColumnNames ()
	{
		String[] colNames = null;
		
		if ( result == null )
		{
			System.out.println( "SQL.getColumnNames(): You need to query() something first." );
			return null;
		}
		
		// if ( columnNamesCache == null )
		// 	columnNamesCache = new HashMap<ResultSet,String[]>();
		// 	
		// colNames = columnNamesCache.get( result );
		// if ( colNames != null ) return colNames;
		
		java.sql.ResultSetMetaData meta = null;
		try {
			meta = result.getMetaData();
		} catch ( SQLException sqle ) {
			if (DEBUG) sqle.printStackTrace();
			return null;
		}
		
		if ( meta != null ) 
		{
			try {
				colNames = new String[ meta.getColumnCount() ];
				for ( int i = 1, k = meta.getColumnCount(); i <= k; i++ ) 
				{
					colNames[i-1] = meta.getColumnName( i );
				}
			} catch ( SQLException sqle ) {
				if (DEBUG) sqle.printStackTrace();
				return null;
			}
		}
		
		// columnNamesCache.clear();
		// columnNamesCache.put( result, colNames );
		
		return colNames;
	}
	

	/**
	 *	Get connection. ... in case you want to do JDBC stuff directly.
	 *
	 *	@return java.sql.Connection The connection
	 */
	public java.sql.Connection getConnection ()
	{
		return connection;
	}
	

	/**
	 *	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"
	 *
	 *	@param	_field	The name of the field
	 *	@return	int	Value of the field or 0
	 */
	public int getInt ( String _field )
	{
		// TODO: 0 does not seem to be a good return value for a numeric field to indicate failure
		// same goes for other numeric fields
		
		if ( result == null )
		{
			System.out.println( "SQL.getInt(): You need to query() something first." );
			return 0;
		}
		
		try
		{
			return result.getInt( _field );
		}
		catch ( java.sql.SQLException e )
		{
			System.out.println( "SQL.getInt(): java.sql.SQLException.\r" );
			if (DEBUG) e.printStackTrace();
		}
		return 0;
	}
	
	/**
	 *	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"
	 *
	 *	@param	_column	The column index of the field to read
	 *	@return	int	Value of the field or 0
	 */
	public int getInt ( int _column )
	{
		if ( result == null )
		{
			System.out.println( "SQL.getInt(): You need to query() something first." );
			return 0;
		}
		
		try
		{
			return result.getInt( _column );
		}
		catch ( java.sql.SQLException e )
		{
			System.out.println( "SQL.getInt(): java.sql.SQLException.\r" );
			if (DEBUG) e.printStackTrace();
		}
		return 0;
	}
	

	/**
	 *	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"
	 *
	 *	@param	_field	The name of the field
	 *	@return	long	Value of the field or 0
	 */
	public long getLong ( String _field )
	{
		if ( result == null )
		{
			System.out.println( "SQL.getLong(): You need to query() something first." );
			return 0;
		}
		
		try
		{
			return result.getLong( _field );
		}
		catch ( java.sql.SQLException e )
		{
			System.out.println( "SQL.getLong(): java.sql.SQLException.\r" );
			if (DEBUG) e.printStackTrace();
		}
		return 0;
	}
	
	
	/**
	 *	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"
	 *
	 *	@param	_column	The column index of the field
	 *	@return	long	Value of the field or 0
	 */
	public long getLong ( int _column )
	{
		if ( result == null )
		{
			System.out.println( "SQL.getLong(): You need to query() something first." );
			return 0;
		}
		
		try
		{
			return result.getLong( _column );
		}
		catch ( java.sql.SQLException e )
		{
			System.out.println( "SQL.getLong(): java.sql.SQLException.\r" );
			if (DEBUG) e.printStackTrace();
		}
		return 0;
	}
	

	/**
	 *	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"
	 *
	 *	@param	_field	The name of the field
	 *	@return	float	Value of the field or 0
	 */
	public float getFloat ( String _field )
	{
		if ( result == null )
		{
			System.out.println( "SQL.getFloat(): You need to query() something first." );
			return 0.0f;
		}
		
		try
		{
			return result.getFloat( _field );
		}
		catch ( java.sql.SQLException e )
		{
			System.out.println( "SQL.getFloat(): java.sql.SQLException.\r" );
			if (DEBUG) e.printStackTrace();
		}
		return 0.0f;
	}


	/**
	 *	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"
	 *
	 *	@param	_column	The index of the column of the field
	 *	@return	float	Value of the field or 0
	 */
	public float getFloat ( int _column )
	{
		if ( result == null )
		{
			System.out.println( "SQL.getFloat(): You need to query() something first." );
			return 0.0f;
		}
		
		try
		{
			return result.getFloat( _column );
		}
		catch ( java.sql.SQLException e )
		{
			System.out.println( "SQL.getFloat(): java.sql.SQLException.\r" );
			if (DEBUG) e.printStackTrace();
		}
		return 0.0f;
	}
	
	
	/**
	 *	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"
	 *
	 *	@param	_field	The name of the field
	 *	@return	double	Value of the field or 0
	 */
	public double getDouble ( String _field )
	{
		if ( result == null )
		{
			System.out.println( "SQL.getDouble(): You need to query() something first." );
			return 0.0;
		}
		
		try
		{
			return result.getDouble( _field );
		}
		catch ( java.sql.SQLException e )
		{
			System.out.println( "SQL.getDouble(): java.sql.SQLException.\r" );
			if (DEBUG) e.printStackTrace();
		}
		return 0.0;
	}


	/**
	 *	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"
	 *
	 *	@param	_column	The column index of the field
	 *	@return	double	Value of the field or 0
	 */
	public double getDouble ( int _column )
	{
		if ( result == null )
		{
			System.out.println( "SQL.getDouble(): You need to query() something first." );
			return 0.0;
		}
		
		try
		{
			return result.getDouble( _column );
		}
		catch ( java.sql.SQLException e )
		{
			System.out.println( "SQL.getDouble(): java.sql.SQLException.\r" );
			if (DEBUG) e.printStackTrace();
		}
		return 0.0;
	}
	
	
	/**
	 *	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"
	 *
	 *	@param	_field	The name of the field
	 *	@return	java.math.BigDecimal	Value of the field or null
	 */
	public java.math.BigDecimal getBigDecimal ( String _field )
	{
		if ( result == null )
		{
			System.out.println( "SQL.getBigDecimal(): You need to query() something first." );
			return null;
		}
		
		try
		{
			return result.getBigDecimal( _field );
		}
		catch ( java.sql.SQLException e )
		{
			System.out.println( "SQL.getBigDecimal(): java.sql.SQLException.\r" );
			if (DEBUG) e.printStackTrace();
		}
		return null;
	}


	/**
	 *	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"
	 *
	 *	@param	_column	The column index of the field
	 *	@return	java.math.BigDecimal	Value of the field or null
	 */
	public java.math.BigDecimal getBigDecimal ( int _column )
	{
		if ( result == null )
		{
			System.out.println( "SQL.getBigDecimal(): You need to query() something first." );
			return null;
		}
		
		try
		{
			return result.getBigDecimal( _column );
		}
		catch ( java.sql.SQLException e )
		{
			System.out.println( "SQL.getBigDecimal(): java.sql.SQLException.\r" );
			if (DEBUG) e.printStackTrace();
		}
		return null;
	}
	
	
	/**
	 *	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"
	 *
	 *	@param	_field	The name of the field
	 *	@return	boolean	Value of the field or 0
	 */
	public boolean getBoolean ( String _field )
	{
		if ( result == null )
		{
			System.out.println( "SQL.getBoolean(): You need to query() something first." );
			return false;
		}
		
		try
		{
			return result.getBoolean( _field );
		}
		catch ( java.sql.SQLException e )
		{
			System.out.println( "SQL.getBoolean(): java.sql.SQLException.\r" );
			if (DEBUG) e.printStackTrace();
		}
		return false;
	}
	

	/**
	 *	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"
	 *
	 *	@param	_column	The column index of the field
	 *	@return	boolean	Value of the field or 0
	 */
	public boolean getBoolean ( int _column )
	{
		if ( result == null )
		{
			System.out.println( "SQL.getBoolean(): You need to query() something first." );
			return false;
		}
		
		try
		{
			return result.getBoolean( _column );
		}
		catch ( java.sql.SQLException e )
		{
			System.out.println( "SQL.getBoolean(): java.sql.SQLException.\r" );
			if (DEBUG) e.printStackTrace();
		}
		return false;
	}
	
	
	/**
	 *	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"
	 *
	 *	@param	_field	The name of the field
	 *	@return	String	Value of the field or null
	 */
	public String getString ( String _field )
	{
		if ( result == null )
		{
			System.out.println( "SQL.getString(): You need to query() something first." );
			return null;
		}
		
		try
		{
			return result.getString( _field );
		}
		catch ( java.sql.SQLException e )
		{
			System.out.println( "SQL.getString(): java.sql.SQLException.\r" );
			if (DEBUG) e.printStackTrace();
		}
		return null;
	}

	/**
	 *	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"
	 *
	 *	@param	_column	The column index of the field
	 *	@return	String	Value of the field or null
	 */
	public String getString ( int _column )
	{
		if ( result == null )
		{
			System.out.println( "SQL.getString(): You need to query() something first." );
			return null;
		}
		
		try
		{
			return result.getString( _column );
		}
		catch ( java.sql.SQLException e )
		{
			System.out.println( "SQL.getString(): java.sql.SQLException.\r" );
			if (DEBUG) e.printStackTrace();
		}
		return null;
	}
	
	/**
	 *	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"
	 *
	 *	@param	_field	The name of the field
	 *	@return	java.sql.Date	Value of the field or null
	 */
	public java.sql.Date getDate ( String _field )
	{
		if ( result == null )
		{
			System.out.println( "SQL.getDate(): You need to query() something first." );
			return null;
		}
		
		try
		{
			return result.getDate( _field );
		}
		catch ( java.sql.SQLException e )
		{
			System.out.println( "SQL.getDate(): java.sql.SQLException.\r" );
			if (DEBUG) e.printStackTrace();
		}
		return null;
	}
	

	/**
	 *	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"
	 *
	 *	@param	_column	The column index of the field
	 *	@return	java.sql.Date	Value of the field or null
	 */
	public java.sql.Date getDate ( int _column )
	{
		if ( result == null )
		{
			System.out.println( "SQL.getDate(): You need to query() something first." );
			return null;
		}
		
		try
		{
			return result.getDate( _column );
		}
		catch ( java.sql.SQLException e )
		{
			System.out.println( "SQL.getDate(): java.sql.SQLException.\r" );
			if (DEBUG) e.printStackTrace();
		}
		return null;
	}
	
	
	/**
	 *	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"
	 *
	 *	@param	_field	The name of the field
	 *	@return	java.sql.Time	Value of the field or null
	 */
	public java.sql.Time getTime ( String _field )
	{
		if ( result == null )
		{
			System.out.println( "SQL.getTime(): You need to query() something first." );
			return null;
		}
		
		try
		{
			return result.getTime( _field );
		}
		catch ( java.sql.SQLException e )
		{
			System.out.println( "SQL.getTime(): java.sql.SQLException.\r" );
			if (DEBUG) e.printStackTrace();
		}
		return null;
	}
	

	/**
	 *	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"
	 *
	 *	@param	_column	The column index of the field
	 *	@return	java.sql.Time	Value of the field or null
	 */
	public java.sql.Time getTime ( int _column )
	{
		if ( result == null )
		{
			System.out.println( "SQL.getTime(): You need to query() something first." );
			return null;
		}
		
		try
		{
			return result.getTime( _column );
		}
		catch ( java.sql.SQLException e )
		{
			System.out.println( "SQL.getTime(): java.sql.SQLException.\r" );
			if (DEBUG) e.printStackTrace();
		}
		return null;
	}
	
	
	/**
	 *	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"
	 *
	 *	@param	_field	The name of the field
	 *	@return	java.sql.Timestamp	Value of the field or null
	 */
	public java.sql.Timestamp getTimestamp ( String _field )
	{
		if ( result == null )
		{
			System.out.println( "SQL.getTimestamp(): You need to query() something first." );
			return null;
		}
		
		try
		{
			return result.getTimestamp( _field );
		}
		catch ( java.sql.SQLException e )
		{
			System.out.println( "SQL.getTimestamp(): java.sql.SQLException.\r" );
			if (DEBUG) e.printStackTrace();
		}
		return null;
	}
	

	/**
	 *	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"
	 *
	 *	@param	_column	The column index of the field
	 *	@return	java.sql.Timestamp	Value of the field or null
	 */
	public java.sql.Timestamp getTimestamp ( int _column )
	{
		if ( result == null )
		{
			System.out.println( "SQL.getTimestamp(): You need to query() something first." );
			return null;
		}
		
		try
		{
			return result.getTimestamp( _column );
		}
		catch ( java.sql.SQLException e )
		{
			System.out.println( "SQL.getTimestamp(): java.sql.SQLException.\r" );
			if (DEBUG) e.printStackTrace();
		}
		return null;
	}
	
	
	/**
	 *	Read a value from the specified field to hav it returned as an object.
	 *
	 *	@param	_field	The name of the field
	 *	@return	Object	Value of the field or null
	 */
	public Object getObject ( String _field )
	{
		if ( result == null )
		{
			System.out.println( "SQL.getObject(): You need to query() something first." );
			return null;
		}
		
		try
		{
			return result.getObject( _field );
		}
		catch ( java.sql.SQLException e )
		{
			System.out.println( "SQL.getObject(): java.sql.SQLException.\r" );
			if (DEBUG) e.printStackTrace();
		}
		return null;
	}
	

	/**
	 *	Read a value from the specified field to hav it returned as an object.
	 *
	 *	@param	_column	The column index of the field
	 *	@return	Object	Value of the field or null
	 */
	public Object getObject ( int _column )
	{
		if ( result == null )
		{
			System.out.println( "SQL.getObject(): You need to query() something first." );
			return null;
		}
		
		try
		{
			return result.getObject( _column );
		}
		catch ( java.sql.SQLException e )
		{
			System.out.println( "SQL.getObject(): java.sql.SQLException.\r" );
			if (DEBUG) e.printStackTrace();
		}
		return null;
	}


	/**
	 *	Close the database connection
	 */
	public void close()
	{
		dispose();
	}
	
	
	/**
	 *	Callback function for PApplet.registerDispose()
	 *
	 *	@see processing.core.PApplet.registerDispose(java.lang.Object)
	 */
	public void dispose ()
	{
		if ( result != null )
		{
			try
			{
				result.close();
			}
			catch ( java.sql.SQLException e ) { ; }
			
			result = null;
		}
		
		if ( statement != null )
		{
			try
			{
				statement.close();
			}
			catch ( java.sql.SQLException e ) { ; }
			
			statement = null;
		}
		
		if ( connection != null )
		{
			try
			{
				connection.close();
			}
			catch ( java.sql.SQLException e ) { ; }
			
			connection = null;
		}
	}
	

	/**
	 *	Generate an escaped String for a given Object
	 *
	 *	@param object the Object to escape
	 *	@return String the ecaped String representation of the Object
	 */
	public String escape ( Object o )
	{
		return "\"" + o.toString().replaceAll("\"","\\\"") + "\"";
	}
	

	/**
	 *	Set the current NameMapper
	 *
	 *	@param mapper the name mapper
	 *  @see de.bezier.data.sql.mapper.NameMapper
	 */
	public void setNameMapper ( NameMapper mapper )
	{
		this.mapper = mapper;
	}
	

	/**
	 *	Get the current NameMapper
	 *
	 *  @see de.bezier.data.sql.mapper.NameMapper
	 */
	public NameMapper getNameMapper ()
	{
		return mapper;
	}
	

	/**
	 *	<p>Highly experimental ...<br />
	 *	tries to map column names to public fields or setter methods 
	 *	in the given object.</p>
	 *	
	 *	<p>Use like so:
	 *  <pre>
	 *	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. 
	 *  }
	 *  </pre></p>
	 *
	 *	<p>SomeObject might look like: 
	 *	<pre>
	 *	class SomeObject {
	 *		public String name;
	 *		public int id;
	 *		Date sometime;
	 *	}
	 *	</pre></p>
	 *
	 *	@param object The object to populate from the currently selected row
	 */
	public void setFromRow ( Object object )
	{
		if ( object == null ) {
			System.err.println( "SQL.rowToObject(): Handing in null won't cut it." );
			return;
		}
		
		if ( result == null ) {
			System.err.println( "SQL.rowToObject(): You need to query() something first!" );
			return;
		}
		
		String[] colNames = getColumnNames();
		if ( colNames == null )
		{
			System.err.println( 
				"SQL.rowToObject(): uh-oh something went wrong: unable to get column names." );
			return;
		}
		
		if ( colNames.length > 0 ) 
		{
			Class klass = null;
			try {
				klass = Class.forName("DeBezierDataSQL");
			} catch ( Exception e ) {
				if (DEBUG) e.printStackTrace();
			}
			if ( klass != null ) {
				Method meth = null;
				try {
					meth = klass.getMethod(
						"setFromRow",
						new Class[]{ SQL.class, Object.class }
					);
				} catch ( Exception e ) {
					if (DEBUG) e.printStackTrace();
				}
				// System.out.println( meth );
				// System.out.println( meth.getParameterTypes() );
				if ( meth != null ) {
					try {
						meth.invoke( null, this, object );
					} catch ( Exception e ) {
						if (DEBUG) e.printStackTrace();
					}
				}
			}
		}
	}
	

	/**
	 *	Convert a field name to a setter name: fieldName -> setFieldName().
	 */
	public String nameToSetter ( String name )
	{
		if ( name == null ) return name;
		if ( name.length() == 0 ) return null;
		return "set" + name.substring(0,1).toUpperCase() + name.substring(1);
	}


	/**
	 *	Convert a field name to a getter name: fieldName -> getFieldName().
	 */
	public String nameToGetter ( String name )
	{
		if ( name == null ) return name;
		if ( name.length() == 0 ) return null;
		return "get" + name.substring(0,1).toUpperCase() + name.substring(1);
	}
	

	/**
	 *	Set a table name for a class.
	 */
	public void registerTableNameForClass ( String name, Object classOrObject )
	{
		if ( name == null || name.equals("") || classOrObject == null ) return;
		
		Class klass = null;
		if ( classOrObject.getClass() != Class.class )
			klass = classOrObject.getClass();
		else
			klass = (Class)classOrObject;
			
		if ( classToTableMap == null )
			classToTableMap = new HashMap<Class,String>();
		
		classToTableMap.put( klass, name );
		if (DEBUG) System.out.println( String.format(
			"Class \"%s\" is now mapped to table \"%s\"", klass.getName(), name
		));
	}
	

	/**
	 *	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).
	 *
	 *	@param object Object The object to save to db
	 *
	 *  @see #insertUpdateInDatabase(java.lang.String, java.lang.String[], java.lang.Object[])
	 */
	public void saveToDatabase ( Object object )
	{
		if ( object == null ) return;
		
		// Find the table name
		
		String tableName = null;
		
		if ( classToTableMap == null )
			classToTableMap = new HashMap<Class,String>();
		
		tableName = classToTableMap.get(object.getClass()); 
		
		if ( tableName != null )
			saveToDatabase( tableName, object );
		else
		{
			Class klass = object.getClass();
			tableName = klass.getName();
			
			for ( char c : new char[]{'$','.'} )
			{
				int indx = tableName.lastIndexOf(c);
				if ( indx >= 0 ) {
					tableName = tableName.substring(indx+1);
				}
			}
			
			if ( mapper != null ) {
				tableName = mapper.backward(tableName);
			}
			
			registerTableNameForClass( tableName, klass );
			
			saveToDatabase( tableName, 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().
	 *
	 * @param tableName String The name of the table
	 * @param object Object The object to look at
	 *
	 * @see #insertUpdateInDatabase(java.lang.String, java.lang.String[], java.lang.Object[])
	 */
	public void saveToDatabase ( String tableName, Object object )
	{
		if ( object == null ) return;
		
		String[] tableNames = getTableNames();
		if ( !java.util.Arrays.asList(tableNames).contains(tableName) ) {
			System.err.println(String.format(
				"saveToDatabase(): table '%s' not found in database '%s'", tableName, database
			));
			return;
		}
		
		String[] colNames = getColumnNames();
		String[] fieldNames = new String[colNames.length];

		if ( mapper != null ) 
		{
			for ( int i = 0; i < colNames.length; i++ ) 
			{
				//System.out.println(colNames[i]);
				fieldNames[i] = mapper.forward(colNames[i]);
				//System.out.println(fieldNames[i]);
			}
		}
		
		Class klass = object.getClass();
		Field[] fields = new Field[colNames.length];
		Method[] getters = new Method[colNames.length];
		
		for ( int i = 0; i < colNames.length; i++ )
		{
			String fieldName = fieldNames[i];
			String colName = colNames[i];
			
			Field f = null;
			try {
				f = klass.getField(fieldName);
				if ( f == null ) {
					f = klass.getField(colName);
				}
			} catch ( Exception e ) {
				if (DEBUG) e.printStackTrace();
			}
			if ( f != null ) {
				// try {
				// 	values[i] = f.get(object);
				// } catch ( Exception e ) {
				// 	if (DEBUG) e.printStackTrace();
				// }
				fields[i] = f;
			}
			else
			{
				if (DEBUG) System.out.println( "Field not found, trying setter method" );
				
				String getterName = nameToGetter(fieldName);
				Method getter = null;
				try {
					getter = klass.getMethod(
						getterName, new Class[0]
					);
				} catch ( Exception e ) {
					if (DEBUG) e.printStackTrace();
				}
				// try {
				// 	values[i] = getter.invoke(object);
				// } catch ( Exception e ) {
				// 	if (DEBUG) e.printStackTrace();
				// }
				getters[i] = getter;
			}
			if ( fields[i] == null && getters[i] == null ) {
				System.err.println(String.format(
					"Unable to get a field or getter for column '%s'", colName
				));
				return;
			}
		}
		
		Object[] values = null;
		
		Class clazz = null;
		try {
			clazz = Class.forName("DeBezierDataSQL");
		} catch ( Exception e ) {
			if (DEBUG) e.printStackTrace();
		}
		
		if ( klass != null ) {
			Method meth = null;
			try {
				meth = clazz.getMethod(
					"getValuesFromObject",
					new Class[]{ SQL.class, Field[].class, Method[].class, Object.class }
				);
			} catch ( Exception e ) {
				if (DEBUG) e.printStackTrace();
			}
			// System.out.println( meth );
			// System.out.println( meth.getParameterTypes() );
			if ( meth != null ) {
				try {
					values = (Object[])meth.invoke( null, this, fields, getters, object );
				} catch ( Exception e ) {
					if (DEBUG) e.printStackTrace();
				}
			}
		}
		
		if ( values != null ) 
		{
			insertUpdateInDatabase( tableName, colNames, values );
		}
		else
		{
			System.err.println("saveToDatabase() : trouble, trouble!!");
		}
	}
	

	/**
	 *	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.
	 *
	 *	@param tableName String The name of the table
	 *	@param columnNames String[] The names of the columns to fill or update
	 *	@param values Object[] The values to instert or update
	 */
	public void insertUpdateInDatabase ( String tableName, String[] columnNames, Object[] values )
	{
		HashMap<Object, Object> valuesKeys = new HashMap<Object,Object>();
		for ( int i = 0; i < values.length; i++ )
		{
			valuesKeys.put(columnNames[i], values[i]);
		}
		
		HashMap<String, Object> primaryKeys = null;
		try {
			DatabaseMetaData meta = connection.getMetaData();
			ResultSet rs = meta.getPrimaryKeys(null, null, tableName);
			
		    while ( rs.next() )
			{
				if ( primaryKeys == null )
			    	primaryKeys = new HashMap<String, Object>();
		    	
				String columnName = rs.getString("COLUMN_NAME");
		    	primaryKeys.put(columnName, valuesKeys.get(columnName));
				valuesKeys.remove(columnName);
		    }
			
		} catch ( SQLException sqle ) {
			sqle.printStackTrace();
		}
		
		//System.out.println(valuesKeys);
		//System.out.println(primaryKeys);
		
		String cols = "";
		String patt = "";
		HashMap<Object, Integer> valueIndices = new HashMap<Object, Integer>();
		int i = 1;
		for ( Map.Entry e : valuesKeys.entrySet() ) 
		{
			cols += ( i > 1 ? " , " : "" ) + e.getKey();
			patt += ( i > 1 ? " , " : "" ) + "?";
			valueIndices.put( e.getKey(), i );
			i++;
		}
		
		String sql = null, opts = null;
		HashMap<Object, Integer> primaryIndices = null;
		if ( primaryKeys == null || primaryKeys.size() == 0 ) {
			sql = "INSERT INTO " + tableName + " ( " + cols + " ) VALUES ( " + patt + " )";
		} else {
			primaryIndices = new HashMap<Object, Integer>();
			opts = " WHERE ";
			int p = 1;
			for ( Map.Entry e : primaryKeys.entrySet() ) {
				opts += (p > 1 ? " , " : "") + e.getKey() + " = ? ";
				primaryIndices.put( e.getKey(), p );
				p++;
			}
			//System.out.println( opts );
			String sqlFind = "SELECT * FROM "+tableName+" "+opts;
			//System.out.println( sqlFind );
			try {
				PreparedStatement psFind = connection.prepareStatement( sqlFind );
				for ( Map.Entry e : primaryKeys.entrySet() ) {
					psFind.setString( primaryIndices.get(e.getKey()), e.getValue().toString() );
				}
				result = psFind.executeQuery();
				boolean found = next();
				psFind.close();
				if ( !found ) {
					if (DEBUG) System.out.println(String.format(
						"No entry with %s found in table '%s'", primaryKeys.toString(), tableName
					));
					int k = 1, m = valueIndices.size();
					for ( Map.Entry e : primaryKeys.entrySet() ) {
						cols += ( m > 0 ? " , " : "" ) + e.getKey();
						patt += ( m > 0 ? " , " : "" ) + "?";
						valuesKeys.put( e.getKey(), e.getValue() );
						valueIndices.put( e.getKey(), m+k );
						k++;
					}
					sql = "INSERT INTO " + tableName + " ( " + cols + " ) VALUES ( " + patt + " )";
					primaryKeys = null;
				}
				else
				{
					cols = "";
					for ( Map.Entry e : valuesKeys.entrySet() )
					{
						cols += ( cols.equals("") ? "" : " , " ) + e.getKey() + " = " + "?";
					}
					sql = "UPDATE "+tableName+" SET " + cols + " " + opts;
				}
			} catch ( java.sql.SQLException sqle ) {
				sqle.printStackTrace();
			}
		}
		
		if (DEBUG) System.out.println( sql );
		
		try {
			PreparedStatement ps = connection.prepareStatement( sql );
			
			for ( Map.Entry e : valuesKeys.entrySet() )
			{
				ps.setString( valueIndices.get(e.getKey()), e.getValue()+"" );
			}
			
			if ( primaryKeys != null ) 
			{
				for ( Map.Entry e : primaryKeys.entrySet() )
				{
					ps.setString( valueIndices.size()+primaryIndices.get(e.getKey()), e.getValue()+"" );
				}
			}
			
			ps.executeUpdate();
			ps.close();
		} catch ( java.sql.SQLException sqle ) {
			sqle.printStackTrace();
		}
	}
}

SQLite.java

//package de.bezier.data.sql;

import processing.core.*;
import java.util.ArrayList;

/**
 *		SQLite wrapper for SQL library for Processing 2+
 *		<p>
 *		A wrapper around some of sun's java.sql.* classes
 *		and the pure java "org.sqlite.JDBC" driver of the Xerial project (Apache 2 license).
 *		</p>
 *		see:<ul>
 *			<li>http://www.xerial.org/trac/Xerial/wiki/SQLiteJDBC</li>
 *			<li>http://www.xerial.org/maven/repository/site/xerial-project/sqlite-jdbc/apidocs/index.html?index-all.html</li>
 *			<li>http://java.sun.com/products/jdbc/</li>
 *		</ul>
 *
 *
 *		@author 		Florian Jenett - mail@florianjenett.de
 *
 *		created:		2008-11-29 12:15:15 - fjenett
 *		modified:		fjenett 20121217
 *
 */

//public class SQLite extends de.bezier.data.sql.SQL
public class SQLite extends SQL
{	
	/**
	 *	Creates a new SQLite connection.
	 *
	 *	@param	_papplet	Your sketch, pass "this" in here
	 *	@param	_database	Path to the database file, if this is just a name the data and sketch folders are searched for the file
	 */
	 
	public SQLite ( PApplet _papplet, String _database  )
	{
		super( _papplet, _database );
		init();
	}
	
	/**
	 *	Creates a new SQLite connection, same as SQLite( PApplet, String )
	 *
	 *	@param	_papplet	Your sketch, pass "this" in here
	 *	@param	_server		Ignored
	 *	@param	_database	Path to the database file, if this is just a name the data and sketch folders are searched for the file
	 *	@param	_user		Ignored
	 *	@param	_pass		Ignored
	 */
	
	public SQLite (  PApplet _papplet, String _server, String _database, String _user, String _pass )
	{
		this( _papplet, _database );
	}
	
	
	private void init ()
	{
		this.driver = "org.sqlite.JDBC";
		this.type = "sqlite";
		
		this.url = "jdbc:" + type + ":" + database;
	}

	public String[] getTableNames ()
	{
		if ( tableNames == null )
		{
			tableNames = new ArrayList<String>();
	        query( "SELECT name AS 'table_name' FROM SQLITE_MASTER WHERE type=\"table\"" );
			while ( next() ) {
				tableNames.add( getObject("table_name").toString() );
			}
		}
		return tableNames.toArray(new String[0]);
	}
}

UnderScoreToCamelCaseMapper.java

//package de.bezier.data.sql.mapper;

/** 
 *	UnderScoreToCamelCaseMapper, does as it says.
 */ 

public class UnderScoreToCamelCaseMapper implements NameMapper {
	public String backward ( String name ) 	{
		String newName = "";
		for ( int i = 0, k = name.length(); i < k; i++ )		{
			String c = name.charAt(i) + "";
			if ( c.toUpperCase().equals(c) && !c.toLowerCase().equals(c) ) {
				if ( i > 0 && i < k-1 )
					c = "_" + c.toLowerCase();
				else
					c = c.toLowerCase();
			}
			newName += c;
		}
		return newName;
	}
	
	public String forward ( String name ) 	{
		String[] pieces = name.split("_");
		String newName = pieces[0];
		for ( int i = 1; i < pieces.length; i++ ) {
			if ( pieces[i] != null && pieces[1].length() > 0 ) {
				newName += pieces[i].substring(0,1).toUpperCase() + 
						   pieces[i].substring(1);
			}
		}
		return newName;
	}
	
	public static void main ( String ... args ) {
		String[] test = new String[]{
			"created_at",
			"created_at_and_else",
			"rank_1",
			"_rank_abc",
			"rank_abc_",
			"camelCase_and_more"
		};
		UnderScoreToCamelCaseMapper mapper = new UnderScoreToCamelCaseMapper();
		for ( String t : test ) {
			String fwd = mapper.forward(t);
			System.out.println( t + " -> " + fwd );
			String back = mapper.backward(fwd);
			System.out.println( fwd + " -> " + back );
			System.out.println( t.equals(back) );
			System.out.println( "" );
		}
	}
}

NameMapper.java

//package de.bezier.data.sql.mapper;

/**
 *	NameMapper is used to map database names to instance names
 *	When setting objects from objects with SQL.setFromRow(). *
 *	This is just an interface and only one implementation is
 *      provided in form of the default UnderScoreToCamelCaseMapper
 *	which does: field_name -> fieldName and vv.
 */

public interface NameMapper {
	/**
	 *	Maps a database name to an object name, typically
	 *	this might look like: field_name -> fieldName. 
	 */
	public String forward ( String name );
	
	/**
	 *	Reverse of forward, maps object names to database
	 *	names like: fieldName -> field_name.
	 */
	public String backward ( String name );
}