Difference between revisions of "Tutorial: SQLite and Processing Files"

From dftwiki3
Jump to: navigation, search
(Created page with "--~~~~ ---- <bluebox> This page contains the accompanying files for SQLite and Processing tutorial. </bluebox> =SQL.java= <code><...")
 
 
Line 7: Line 7:
 
</bluebox>
 
</bluebox>
  
−
=SQL.java=
+
<br />
 +
__TOC__
 +
<br />
 +
<br />
 +
=Files for Tutorial 1=
 +
==SQL.java==
 
<code><pre>
 
<code><pre>
 
   
 
   
Line 866: Line 871:
  
  
−
=SQLite.java=
+
==SQLite.java==
 
<code><pre>
 
<code><pre>
 
import processing.core.*;
 
import processing.core.*;
Line 935: Line 940:
  
 
<br />
 
<br />
 +
=Files for Tutorial 2=
 +
 +
==example1.java==
 +
<code><pre>
 +
package SQLitePackage;
 +
 +
import processing.core.*;
 +
 +
public class example1 extends PApplet {
 +
SQLite db;
 +
 +
public example1() {
 +
}
 +
 +
public void setup() {
 +
size(100, 100);
 +
 +
db = new SQLite(this, "test.db"); // open database file
 +
 +
if (db.connect()) {
 +
// list table names
 +
db.query( "SELECT name as \"Name\" FROM SQLITE_MASTER where type=\"table\"");
 +
 +
while (db.next()) {
 +
println(db.getString("Name"));
 +
}
 +
 +
// read all in table "table_one"
 +
db.query("SELECT * FROM table_one");
 +
 +
while (db.next()) {
 +
print(db.getString("field_one"));
 +
print( " - " );
 +
println(db.getInt("field_two"));
 +
}
 +
}
 +
}
 +
}
 +
 +
</pre></code>
 +
<br />
 +
==SQL.java==
 +
<code><pre>
 +
package SQLitePackage;
 +
 +
 +
import java.io.*;
 +
import java.sql.*;
 +
import processing.core.*;
 +
/**
 +
* SQL library for Processing 1.0
 +
*
 +
* see:<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 20070801
 +
*
 +
* @since 004
 +
* @version 005 - added more general SQL, simplyfied MySQL
 +
*
 +
*/
 +
 +
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 = "";
 +
 +
public java.sql.Connection connection;
 +
public String previousQuery;
 +
 +
public java.sql.Statement statement;
 +
public java.sql.ResultSet result;
 +
 +
private boolean DEBUG = true;
 +
 +
/**
 +
* Do not use this contructor.
 +
*/
 +
 +
public SQL ()
 +
{
 +
System.out.println("SQL(): Please use this constructor\r\tSQL ( String _serv, String _db, String _u, String _p, PApplet _pa )");
 +
}
 +
 +
/**
 +
* 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 );
 +
}
 +
 +
 +
/**
 +
* 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 );
 +
}
 +
 +
/**
 +
* 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;
 +
 +
}
 +
 +
// removed finally block, thanks nao
 +
 +
return true;
 +
}
 +
 +
/**
 +
* Execute a SQL command on the open database connection.
 +
*
 +
* @param _sql The SQL command to execute
 +
*/
 +
 +
public void execute ( String _sql )
 +
{
 +
if ( connection == null )
 +
{
 +
System.out.println( "SQL.query(): You need to connect() first." );
 +
return;
 +
}
 +
 +
previousQuery = _sql;
 +
 +
try
 +
{
 +
if ( statement == null )
 +
{
 +
statement = connection.createStatement();
 +
}
 +
 +
statement.execute( _sql );
 +
}
 +
catch ( java.sql.SQLException e )
 +
{
 +
System.out.println( "SQL.query(): java.sql.SQLException.\r" );
 +
if (DEBUG) e.printStackTrace();
 +
}
 +
}
 +
 +
 +
/**
 +
* Issue a query on the open database connection
 +
*
 +
* @param _sql SQL command to execute for the query
 +
*/
 +
 +
public void query ( String _sql )
 +
{
 +
if ( connection == null )
 +
{
 +
System.out.println( "SQL.query(): You need to connect() first." );
 +
return;
 +
}
 +
 +
previousQuery = _sql;
 +
 +
try
 +
{
 +
if ( statement == null )
 +
{
 +
statement = connection.createStatement();
 +
}
 +
 +
result = statement.executeQuery( _sql );
 +
}
 +
catch ( java.sql.SQLException e )
 +
{
 +
System.out.println( "SQL.query(): java.sql.SQLException.\r" );
 +
if (DEBUG) 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;
 +
}
 +
 +
/**
 +
* 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;
 +
}
 +
 +
 +
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;
 +
}
 +
 +
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;
 +
}
 +
 +
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;
 +
}
 +
 +
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;
 +
}
 +
 +
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;
 +
}
 +
 +
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;
 +
}
 +
 +
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;
 +
}
 +
 +
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;
 +
}
 +
 +
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;
 +
}
 +
 +
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;
 +
}
 +
 +
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()
 +
*/
 +
 +
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;
 +
}
 +
}
 +
}
 +
</pre></code>
 +
<br />
 +
==SQLite.java==
 +
<code><pre>
 +
package SQLitePackage;
 +
 +
import processing.core.*;
 +
 +
/**
 +
* SQLite wrapper for SQL library for Processing 1.0
 +
* <p>
 +
* A wrapper around some of sun's java.sql.* classes and the pure java
 +
* "org.sqlite.JDBC" driver by zentus.com (BSD).
 +
* </p>
 +
* see:
 +
* <ul>
 +
* <li>http://www.zentus.com/sqlitejdbc/</li>
 +
* <li>http://files.zentus.com/sqlitejdbc/</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 20081129
 +
*
 +
* @since 0.0.7
 +
* @version 0.0.7
 +
*
 +
*/
 +
 +
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;
 +
}
 +
}
 +
</pre></code>
 
<br />
 
<br />
 +
  
 
<br />
 
<br />

Latest revision as of 16:51, 30 June 2011

--D. Thiebaut 15:05, 30 June 2011 (EDT)


This page contains the accompanying files for SQLite and Processing tutorial.




Files for Tutorial 1

SQL.java

 

import java.io.*;
import java.sql.*;
import processing.core.*;
/**
 *		SQL library for Processing 1.0
 *		
 *		see:<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 20070801
 *
 *		@since 			004
 *		@version 		005 - added more general SQL, simplyfied MySQL
 *
 */

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 = "";
	
	public java.sql.Connection connection;
	public String previousQuery;
	
	public java.sql.Statement statement;
	public java.sql.ResultSet result;
	
	private boolean DEBUG = true;
	
	/**
	 *	Do not use this contructor.
	 */
	 
	public SQL ()
	{
		System.out.println("SQL(): Please use this constructor\r\tSQL ( String _serv, String _db, String _u, String _p, PApplet _pa )");
	}
	
	/**
	 *	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 );
	}
	
	
	/**
	 *	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 );
	}
	
	/**
	 *	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;
			
		}
		
		// removed finally block, thanks nao
		
		return true;
	}
	
	/**
	 *	Execute a SQL command on the open database connection.
	 *
	 *	@param	_sql	The SQL command to execute
	 */
	
	public void execute ( String _sql )
	{
		if ( connection == null )
		{
			System.out.println( "SQL.query(): You need to connect() first." );
			return;
		}
		
		previousQuery = _sql;
		
		try
		{
			if ( statement == null )
			{
				statement = connection.createStatement();
			}
			
			statement.execute( _sql );
		}
		catch ( java.sql.SQLException e )
		{
			System.out.println( "SQL.query(): java.sql.SQLException.\r" );
			if (DEBUG) e.printStackTrace();
		}
	}
	
	
	/**
	 *	Issue a query on the open database connection
	 *
	 *	@param	_sql	SQL command to execute for the query
	 */
		
	public void query ( String _sql )
	{
		if ( connection == null )
		{
			System.out.println( "SQL.query(): You need to connect() first." );
			return;
		}
		
		previousQuery = _sql;
		
		try
		{
			if ( statement == null )
			{
				statement = connection.createStatement();
			}
			
			result = statement.executeQuery( _sql );
		}
		catch ( java.sql.SQLException e )
		{
			System.out.println( "SQL.query(): java.sql.SQLException.\r" );
			if (DEBUG) 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;
	}
	
	/**
	 *	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;
	}
	
	
	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;
	}
	
	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;
	}
	
	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;
	}
	
	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;
	}
	
	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;
	}
	
	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;
	}
	
	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;
	}
	
	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;
	}
	
	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;
	}
	
	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;
	}
	
	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()
	 */
	 
	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;
		}
	}
}


SQLite.java

import processing.core.*;

/**
 *		SQLite wrapper for SQL library for Processing 1.0
 *		<p>
 *		A wrapper around some of sun's java.sql.* classes
 *		and the pure java "org.sqlite.JDBC" driver by zentus.com (BSD).
 *		</p>
 *		see:<ul>
 *			<li>http://www.zentus.com/sqlitejdbc/</li>
 *			<li>http://files.zentus.com/sqlitejdbc/</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 20081129
 *
 *		@since 			0.0.7
 *		@version 		0.0.7
 *
 */

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;
	}
}


Files for Tutorial 2

example1.java

package SQLitePackage;

import processing.core.*;

public class example1 extends PApplet {
	SQLite db;

	public example1() {
	}
	
	public void setup() {
		size(100, 100);

		db = new SQLite(this, "test.db"); // open database file

		if (db.connect()) {
			// list table names
			db.query( "SELECT name as \"Name\" FROM SQLITE_MASTER where type=\"table\"");

			while (db.next()) {
				println(db.getString("Name"));
			}

			// read all in table "table_one"
			db.query("SELECT * FROM table_one");

			while (db.next()) {
				print(db.getString("field_one"));
				print( " - " );
				println(db.getInt("field_two"));
			}
		}
	}
}


SQL.java

package SQLitePackage;


import java.io.*;
import java.sql.*;
import processing.core.*;
/**
 *		SQL library for Processing 1.0
 *		
 *		see:<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 20070801
 *
 *		@since 			004
 *		@version 		005 - added more general SQL, simplyfied MySQL
 *
 */

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 = "";
	
	public java.sql.Connection connection;
	public String previousQuery;
	
	public java.sql.Statement statement;
	public java.sql.ResultSet result;
	
	private boolean DEBUG = true;
	
	/**
	 *	Do not use this contructor.
	 */
	 
	public SQL ()
	{
		System.out.println("SQL(): Please use this constructor\r\tSQL ( String _serv, String _db, String _u, String _p, PApplet _pa )");
	}
	
	/**
	 *	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 );
	}
	
	
	/**
	 *	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 );
	}
	
	/**
	 *	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;
			
		}
		
		// removed finally block, thanks nao
		
		return true;
	}
	
	/**
	 *	Execute a SQL command on the open database connection.
	 *
	 *	@param	_sql	The SQL command to execute
	 */
	
	public void execute ( String _sql )
	{
		if ( connection == null )
		{
			System.out.println( "SQL.query(): You need to connect() first." );
			return;
		}
		
		previousQuery = _sql;
		
		try
		{
			if ( statement == null )
			{
				statement = connection.createStatement();
			}
			
			statement.execute( _sql );
		}
		catch ( java.sql.SQLException e )
		{
			System.out.println( "SQL.query(): java.sql.SQLException.\r" );
			if (DEBUG) e.printStackTrace();
		}
	}
	
	
	/**
	 *	Issue a query on the open database connection
	 *
	 *	@param	_sql	SQL command to execute for the query
	 */
		
	public void query ( String _sql )
	{
		if ( connection == null )
		{
			System.out.println( "SQL.query(): You need to connect() first." );
			return;
		}
		
		previousQuery = _sql;
		
		try
		{
			if ( statement == null )
			{
				statement = connection.createStatement();
			}
			
			result = statement.executeQuery( _sql );
		}
		catch ( java.sql.SQLException e )
		{
			System.out.println( "SQL.query(): java.sql.SQLException.\r" );
			if (DEBUG) 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;
	}
	
	/**
	 *	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;
	}
	
	
	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;
	}
	
	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;
	}
	
	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;
	}
	
	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;
	}
	
	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;
	}
	
	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;
	}
	
	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;
	}
	
	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;
	}
	
	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;
	}
	
	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;
	}
	
	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()
	 */
	 
	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;
		}
	}
}


SQLite.java

package SQLitePackage;

import processing.core.*;

/**
 * SQLite wrapper for SQL library for Processing 1.0
 * <p>
 * A wrapper around some of sun's java.sql.* classes and the pure java
 * "org.sqlite.JDBC" driver by zentus.com (BSD).
 * </p>
 * see:
 * <ul>
 * <li>http://www.zentus.com/sqlitejdbc/</li>
 * <li>http://files.zentus.com/sqlitejdbc/</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 20081129
 * 
 * @since 0.0.7
 * @version 0.0.7
 * 
 */

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;
	}
}







Retrieved from "http://dominiquethiebaut.com/dftwiki3/index.php?title=Tutorial:_SQLite_and_Processing_Files&oldid=11845"