These are simple examples for using a database with the JavaFX Script compiler. The hsqldb.jar of hsqldb must be in the classpath.
SimpleDatabase.fx[]
import java.sql.*;
import java.lang.*;
// Using the memory mode of hsqldb, so that the testdb is only
// created in RAM and no data is stored on disk.
var driverClassName = 'org.hsqldb.jdbcDriver';
var jdbcUrl = 'jdbc:hsqldb:mem:testdb';
var user = 'sa';
var password = '';
// Database objects
var driver:Driver = null;
var conn :Connection = null;
var stmt :Statement = null;
var rs :ResultSet = null;
var rows :Number;
try {
// Load driver
Class.forName(driverClassName);
try {
// Connect to database
conn = DriverManager.getConnection(jdbcUrl, user, password);
stmt = conn.createStatement();
// Create table for a Todo list
rows = stmt.executeUpdate("CREATE TABLE Todos(id BIGINT NOT NULL IDENTITY,task VARCHAR(160))");
System.out.println("CREATE TABLE rows: {rows}");
// Insert three tasks
rows = stmt.executeUpdate("INSERT INTO Todos VALUES(1, 'do')");
System.out.println("INSERT rows: {rows}");
rows = stmt.executeUpdate("INSERT INTO Todos VALUES(2, 'did')");
System.out.println("INSERT rows: {rows}");
rows = stmt.executeUpdate("INSERT INTO Todos (task) VALUES('done')");
System.out.println("INSERT rows: {rows}");
// Select and print tasks
rs = stmt.executeQuery("SELECT * FROM Todos");
while(rs.next()) {
System.out.println("id: {rs.getInt('id')} task: {rs.getString('task')}");
}
} catch(e:SQLException) {
e.printStackTrace();
} finally {
if(null != rs){rs.close();}
if(null != stmt) {
try {
stmt.execute("SHUTDOWN"); // Clean up
} catch(e:SQLException) {
e.printStackTrace();
} finally {
stmt.close();
}
}// if(null != stmt)
if(null != conn){conn.close();}
}// finally
} catch(e:Exception) {
e.printStackTrace();
}
DatabaseTODO.fx[]
A simple TODO list stored in a database.
import javafx.ext.swing.*;
import javafx.scene.paint.*;
import javafx.scene.text.*;
import java.sql.*;
import java.lang.*;
/**
* Simple abstraction layer for the database.
*/
public class Database {
public var driverName: String;
public var jdbcUrl : String;
public var user : String;
public var password : String;
public var driver : Driver;
public var conn : Connection = null;
/**
* Connect to database.
*/
public function connect() {
// Load driver
Class.forName(this.driverName);
// Connect to database
this.conn = DriverManager.getConnection(this.jdbcUrl, this.user, this.password);
}// Database.connect
/**
* Close/shutdown connection to database.
*/
public function shutdown() {
var stmt: Statement = null;
if(null != this.conn) {
try {
stmt = this.conn.createStatement();
stmt.execute("SHUTDOWN"); // Clean up
} catch(e:SQLException) {
e.printStackTrace();
} finally {
if(null != stmt) {stmt.close();}
this.conn.close();
}
}// if(null != stmt)
}// public function.Database.shutdown
/**
* Check if the table exists.
* @param table Name of the table.
* @return true or false
*/
public function tableExists(table: String) {
var tableExists = false;
var dbmd = this.conn.getMetaData();
var rs = dbmd.getTables(null, null, '%', ['TABLE']);
while(rs.next()) {
if(table == rs.getString(3)) {
tableExists = true;
break;
}
}// while(rs.next())
return tableExists;
}// tableExists
}// Database
/**
* Single task in the Todo list
*/
class Task {
attribute id : Number;
attribute task: String;
}// Task
/**
* TODO list.
*/
class TODO {
attribute tasks : Task[];
attribute selectedTask: Integer;
attribute newTask : String;
attribute conn : Connection = null;
/**
* Load tasks from database.
*/
public function load() {
var stmt = this.conn.createStatement();
var rs = stmt.executeQuery("SELECT * FROM TODOS ORDER BY id ASC");
while(rs.next()) {
System.out.println("id: {rs.getInt('id')} task: {rs.getString('task')}");
insert Task{id: rs.getInt('id') task: rs.getString('task')} into this.tasks;
}
}// load
/**
* Add task to list/database.
*/
public function add() {
try {
var task = Task{task: this.newTask};
var stmt = this.conn.createStatement();
this.conn.setAutoCommit(false);
// Insert new task in database
var rows = stmt.executeUpdate("INSERT INTO TODOS (task) VALUES('{task.task}')");
System.out.println("INSERT rows: {rows} for {task.task}");
// Get id of the task from database
var rs = stmt.executeQuery('CALL IDENTITY()');
if(rs.next()) {
task.id = rs.getInt(1);
this.conn.commit();
insert task into this.tasks;
}// if(rs.next())
} catch(e:SQLException){
SwingDialog {
title : 'TODO - Add task'
background: Color.WHITE;
visible : true
content : Canvas{content: Text{content : "SQL: {e.getMessage()}"
textOrigin: TextOrigin.TOP}}
}// SwingDialog
/*MessageDialog {
messageType: ERROR
title : 'TODO - Add task'
message : "SQL: {e.getMessage()}"
visible : true
}// MessageDialog */
} finally {
this.conn.setAutoCommit(true);
}
}// add
/**
* Remove task from list/database.
*/
public function remove() {
if(sizeof this.tasks > 0) {
try {
var stmt = this.conn.createStatement();
var task = this.tasks[this.selectedTask];
var rows = stmt.executeUpdate("DELETE FROM TODOS WHERE id = {task.id}");
System.out.println("DELETE rows: {rows} for {task.task}");
delete this.tasks[this.selectedTask];
} catch(e:SQLException) {
SwingDialog {
title : 'TODO - Delete task'
background: Color.WHITE;
visible : true
content : Canvas{content: Text{content : "SQL: {e.getMessage()}"
textOrigin: TextOrigin.TOP}}
}// SwingDialog
/*MessageDialog {
//messageType: ERROR
title : 'TODO - Delete task'
message : "SQL: {e.getMessage()}"
visible : true
}// MessageDialog */
}
}// if(sizeof this.tasks > 0)
}// remove
}// TODO
// Database vars
var db : Database = null;
var stmt: Statement = null;
var rs : ResultSet = null;
var rows: Number;
db = Database{driverName: 'org.hsqldb.jdbcDriver'
jdbcUrl : 'jdbc:hsqldb:testdb/TODOOperations'
user : 'sa'
password : ''};
var model = TODO {
conn: bind db.conn
};
try {
// Connect to database
db.connect();
stmt = db.conn.createStatement();
// Create table
if(not db.tableExists('TODOS'))
{
rows = stmt.executeUpdate("CREATE TABLE TODOS(id BIGINT NOT NULL IDENTITY,
task VARCHAR(160))");
System.out.println("CREATE TABLE rows: {rows}");
rows = stmt.executeUpdate("INSERT INTO TODOS VALUES(1, 'do')");
System.out.println("INSERT rows: {rows}");
rows = stmt.executeUpdate("INSERT INTO TODOS VALUES(2, 'did')");
System.out.println("INSERT rows: {rows}");
rows = stmt.executeUpdate("INSERT INTO TODOS (task) VALUES('done')");
System.out.println("INSERT rows: {rows}");
}// if(not db.tableExists('TODOS'))
model.load();
} catch(e:SQLException) {
e.printStackTrace();
}
SwingFrame {
closeAction: function(): Void {db.shutdown(); java.lang.System.exit(0);}
title : 'Database: TODO list'
background : Color.WHITE;
visible : true
content: BorderPanel {
center: List {
selectedIndex: bind model.selectedTask with inverse
items: bind for (task in model.tasks)
ListItem {
text: task.task
}
}// List
bottom: FlowPanel {
content: [
SwingTextField {
columns: 30
text : bind model.newTask with inverse
}, // SwingTextField
SwingButton {
text : 'Add'
enabled: bind model.newTask.length() > 0
action : function() {
model.add();
model.newTask = '';
}
}, // SwingButton
SwingButton {
text : 'Delete'
enabled: bind sizeof model.tasks > 0
action : function() {
model.remove();
}// SwingButton
}
]// content
}// FlowPanel
}// BorderPanel
}// '''SwingFrame'''
Links[]
- Ricardo Rocha, "Connect to Database", 13-May-2007, openjfx dev ML
- Blaine Simpson, Fred Toussi, "Hsqldb User Guide", 2002-2005, hsqldb.org