Tutorial: SQLite and Processing Files
--D. Thiebaut 15:05, 30 June 2011 (EDT)
This page contains the accompanying files for SQLite and Processing tutorial.
Contents
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;
}
}
