JavaFX Script and other musing Clarkeman's Weblog

I recently checked in a first cut of a JavaFX scripting framework for Java Database Connectivity, JDBC.  This can be found at the openjfx-compiler project. The framework is located in the javafx.sql package of the runtime jar, javafxrt.jar. To get started, use the javafx.sql.DB class.

There are two ways to create a connection to the database, one with a javax.sql.Datasource, and one with the traditional Database URL/user/password combination.  For the DataSource method, first create the Datasource or locate one via JNDI.

For MySQL, the class is either com.mysql.jdbc.jdbc2.optional.MysqlDataSource or com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource. Descriptions of these can be found in the MySQL Connector/J documentation. For other databases, check the specific JDBC documentation to see how a DataSource is supported. To create a javafx.sql.DB object using a DataSource, use an Object Literal while setting the dataSource property.

var dataSource = com.mysql.jdbc.jdbc2.optional.MysqlDataSource{};
dataSource.setServerName("localhost");
dataSource.setDatabaseName("fxtest");
dataSource.setUser("jclarke");
var db = DB {
    dataSource: dataSource
}

To use the classical approach to open a JDBC connection, provide a database URL, and optional user and password attributes. The following opens the database connection using the same properties as used in the DataSource example above.
 

var db = DB{
    user: 'jclarke'
    dbUrl:  "jdbc:mysql://localhost/fxtest";
};

If you have a problem connecting, and exception will print.

To execute an SQL statement, use the execute or executeUpdate methods. These do the exact same things, but execute returns a boolean indicating rows have been updated, while executeUpdate returns the number of rows actually updated. Using examples from the JDBC Tutorial, let's first create some tables:

db.execute("drop table if exists COFFEES");
db.execute("drop table if exists SUPPLIERS");

db.execute("create table SUPPLIERS " +
                "(SUP_ID INTEGER NOT NULL, " +
                "SUP_NAME VARCHAR(40), " +
                "STREET VARCHAR(40), " +
                "CITY VARCHAR(20), " +
                "STATE CHAR(2), " +
                "ZIP CHAR(5), " +
                "primary key(SUP_ID))");
db.execute("create table COFFEES " +
                "(COF_NAME varchar(32) NOT NULL, " +
                "SUP_ID int, " +
                "PRICE float, " +
                "SALES int, " +
                "TOTAL int, " +
                "primary key(COF_NAME), " +
                "foreign key(SUP_ID) references SUPPLIERSPK)");

db.executeUpdate("insert into SUPPLIERS " +
    "values(49, 'Superior Coffee', '1 Party Place', " +
         "'Mendocino', 'CA', '95460')");

var values = ["Kona", "00049", "15.99", "0", "0"];
db.execute("insert into COFFEES values(?,?,?,?,?)", values);

The first form is just a literal string, however, the second form uses a Parametrized SQL statement with the values taken from a JavaFX String Sequence. You could also have used the JavaFX String substitution syntax:

var coffee = "Kona";
var supId = 49;
var price = 15.99
var sales = 0;
var total = 0;
db.execute("insert into SUPPLIERS values('{coffee}', {supId), {price}, {sales}, {total}");

To fetch the data use the "query" method.

db.query("select COF_NAME, PRICE from COFFEES where COF_NAME = '{values[0]}'",
        function(rs:java.sql.ResultSet):Void {
            System.out.println("::{rs.getString("COF_NAME")}   {%5.2f rs.getFloat("PRICE")}")
        });

Using this form of the query method, the function parameter defines a function that takes a java.sql.ResultSet as an argument and is called once for each row of the result. Another form of query returns the ResultSet directly.

It is also possible to get at the basic JDBC objects. The following example show getting a PreparedStatement then using JavaFX Sequences to update rows in the COFFEES table.

var updateString = "update COFFEES " +
                      "set SALES = ? where COF_NAME like ?";
var updateSales = db.getPreparedStatement(updateString);
var salesForWeek = [175, 150, 60, 155, 90, 100];
var coffees = ["Colombian", "French_Roast", "Espresso",
                     "Colombian_Decaf", "French_Roast_Decaf", "Kona"];
for(c in coffees) {
        updateSales.setInt(1, salesForWeek[indexof c]);
        updateSales.setString(2, c);
        updateSales.executeUpdate();
}

This is just a start to the JDBC framework in JavaFX script and the entire Tutorial.fx script can be downloaded from here.
Once the JavaFX reflection API is completed, I want to add mappings from SQL directly to and from JavaFX Objects. This will provide the ability to some really interesting things with database access.