data > database connectivity

The prefuse.data.io.sql package contains classes for retrieving data from relational databases. First, you will need to have access to a running database system. The information you need includes:

• The host URL, the internet address of the database server (use localhost if the database is on the same machine as your application)
• The database name of the particular database to connect to
• A username and password for accessing the database
• A database driver by which Java can connect to the database. Prefuse uses the common JDBC (Java Database Connectivity) API to connect to databases. Many database vendors (e.g., Oracle, MySQL) provide JDBC drivers, in which case you need to know the class name of the driver, and the driver must be on your application's classpath. If your database vendor does not provide a JDBC dirver, you may be able to use the JDBC/ODBC bridge to connect using an ODBC driver (Open Database Connectivity, used on Microsoft Windows Systems), however, this is beyond the scope of this manual. [TODO: add link to more info]

With the information above taken care of, and with the requisite database driver on your classpath, you can now load data from a database. First, you need to get a connection to the database. This is provided by the ConnectionFactory class, using one of the getDatabaseConnection() methods. The JDBC system uses a specially formatted URL to specify the database. This URL includes information about the specific database being used, the web location of the database server, and the database name. For example, the following URL connects to a MySQL database named "data" at the location "www.my.database.com":

jdbc:mysql://www.my.database.com/data

The URL above consists of (1) the jdbc marker to indicate the URL is for a database connection, (2) the mysql marker to indicate the database being used (which string to use here is dependent upon the JDBC driver you are using), (3) www.my.database.com to indicate the database host, and (4) data to indicate the database name. This URL string, along with your username and password, are used by the ConnectionFactory to connect to the database. For some database options, the ConnectionFactory may provide convenience methods that will construct the URL for you. Check the API documentation to see the currently provided convenience methods.Some ConnectionFactory methods also allow you to specify a custom SQLDataHandler instance, which is used to map database values into data values in the Java programming language. More on this below. In any case, if all goes well, a new DatabaseDataSource object will be returned. If something goes wrong, an exception will be thrown.

You can now use the DatabaseDataSource to query the database and receive the results in prefuse Table instances. Queries are specified as String values, and are assumed to contain legal SQL queries for the target database. If you are unfamiliar with the SQL dialect for your particular database, please consult the documentation from the database vendor. Query results returned from the database are automatically processed by prefuse, data values are mapped into corresponding Java primitive types or Objects, and the results are returned in a Table object. Query results can either be returned in an automatically created Table instance (using a Schema determined by the query results), or loaded into an existing Table instance with a compatible Schema. By specifying a key data field, you can also prevent duplicate results from being repeated in a provided Table. Finally, you have a choice of whether or not to request the data synchronously (in which case your code will block until the results are returned) or asynchronously (in which case your code will continue executing while the query is processed in a separate thread). These possibilities are shown in the sample code below.

The sample code below demonstrates (1) connecting to a MySQL database using the ConnectionFactory, (2) issuing a simple, synchronous query, (3) issuing a synchronous query and storing the result in an existing table, and (4) issuing an asynchrounous query and using an optional listener to receive updates on the query's progress.

// let's note our database connection properties
// the JDBC driver we'll be using
String driver = "com.mysql.jdbc.Driver";
// the internet address of the database server
String host = "localhost";
// the name of the database to connect to
String database = "prefuse";
// the username
String user = "prefuse-user";
// the password
String password = "open-sesame!";

// now lets build the connection URL
String url = "jdbc:mysql://"+host+"/"+database;

// get a connection to the database
DatabaseDataSource datasrc = null;
try {
    datasrc = ConnectionFactory.getDatabaseConnection(
                 driver, url, user, password);
} catch (SQLException e) {
    // There was an error connecting to the database
    e.printStackTrace();
    return;
} catch (ClassNotFoundException e) {
    // The database driver class was not found
    e.printStackTrace();
    return;
}

// we are now connected so we can issue queries

// let's list some SQL queries to issue
// assume our database has a table named 'table' with
// columns named 'value' and 'id', where id is the primary key
String query1 = "select * from table where value > 5";
String query2 = "select * from table where value < 3";
String query3 = "select * from table where value < 5 and value > 3";

try {
    // issue a query asynchronously, and get the result
    Table data = datasrc.getData(query1);

    // issue another query, storing the results in the
    // existing table, and preventing duplicates by
    // providing a key field to check against
    datasrc.getData(data, query2, "id");
    
    // the next example is a little more complicated...
    // it helps to understand a bit about threads
    // and synchronization!
    
    // now let's issue a query asynchronously.
    // we will print statements in a loop until the
    // query has been processed. we will use a listener
    // interface to know when to stop
    
    // we make this final so that the listener can access it
    // we make it an array so that we can update the value
    // as well as synchronize on it
    final boolean[] done = new boolean[] {false};
    
    // a listener to update the done value when the query finishes
    DataSourceWorker.Listener lstnr =
        new DataSourceWorker.Listener() {
            public void preQuery(Entry job) {
                // callback that query is about to be issued
                System.out.println("Query about to issue...");
            }
            public void postQuery(Entry job) {
                // callback that query processing is finished
                System.out.println("Query procesing finished!");
                synchronized ( done ) {
                    done[0] = true;
                }
            }
    };
    
    // issue the query. the null parameter is for an optional lock
    // to synchronize on when updating the data table
    datasrc.loadData(data, query3, "id", null, lstnr);

    // we now loop until the query is done
    synchronized ( done ) {
        while ( !done[0] ) {
            System.out.println("waiting for query to finish...");
            try {
                done.wait(200); // sleep for 200 milliseconds
            } catch ( InterruptedException ie ) {
                // don't worry about this
            }
        }
    }
    
    // now the data has been loaded in the the data table
    
} catch ( DataIOException e ) {
    // something went wrong :{
    e.printStackTrace();
    return;
}