Fandom

Planet JFX

Database

118pages on
this wiki
Add New Page
Talk0 Share

Ad blocker interference detected!


Wikia is a free-to-use site that makes money from advertising. We have a modified experience for viewers using ad blockers

Wikia is not accessible if you’ve made further modifications. Remove the custom ad blocker rule(s) and the page will load as expected.

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

Also on Fandom

Random Wiki