Wikia

Planet JFX

Database

118pages on
this wiki
Talk0

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 Edit

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 Edit

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 Edit

Around Wikia's network

Random Wiki