javafx学习之数据库操作

上次我们学习的javafx连接数据库的简单操作,这次我们来做一个稍为复杂的操作数据库示例.IDE是:netbeans 6 beat 2,数据库用javaDB(jdb6自带有javaDB数据库,netbeans 6也带有,本例使用IDE自带的javaDB),由于水平问题中文只注解了部份代码,请见谅.(如出错,请把

上次我们学习的javafx连接数据库的简单操作,这次我们来做一个稍为复杂的操作数据库示例.IDE是:netbeans 6 beat 2,数据库用javaDB(jdb6自带有javaDB数据库,netbeans 6也带有,本例使用IDE自带的javaDB),由于水平问题中文只注解了部份代码,请见谅.(如出错,请把中文注解删除)

import javafx.ui.*;
import java.lang.Thread;
import java.lang.Exception;
import java.sql.*;
import org.apache.derby.jdbc.*;

// Connect to database

public class Database {
    public attribute driverName: String;
    public attribute jdbcUrl   : String;
    public attribute user      : String;
    public attribute password  : String;

    public attribute driver    : Driver;
    public attribute conn      : Connection;

    public operation connect();
    public operation shutdown();

    public operation tableExists(table: String);
}// Database

attribute Database.conn = null;

//-------------------------连接数据库-----------------------------------
operation Database.connect() {
    // Load driver class using context class loader
    // 加载驱动
    var thread      = Thread.currentThread();
    var classLoader = thread.getContextClassLoader();
    var driverClass = classLoader.loadClass(this.driverName);

    // Instantiate and register JDBC driver
    //实例并注册驱动
    this.driver = (Driver) driverClass.instantiate();  // JavaFX Class
    DriverManager.registerDriver(driver);

    // Connect to database
    //连接数据库
    this.conn = DriverManager.getConnection(this.jdbcUrl, this.user, this.password);
}// Database.connect

//--------------------关闭资源---------------------------
operation Database.shutdown() {
    var stmt: Statement = null;

    if(null <> this.conn) {
        try {
            stmt = this.conn.createStatement();
            stmt.close();
        } catch(e:SQLException) {           
            e.printStackTrace();
        } finally {
            if(null <> stmt) {stmt.close();}
            this.conn.close();
        }
    }// if(null <> stmt)
}// operation.Database.shutdown

operation Database.tableExists(table: String)
{
    // Check if table exists
   //检查表是否存在，注意这里并没有主动去删除
    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

 

// Single userName in the Todo list

class userName {
    attribute id  : Number;
    attribute userName: String;
}// userName

 

// Todo list

class TODO {
    属性 userNames       : userName*;
    属性 selecteduserName: Number;
    属性 newuserName     : String;

    属性 conn        ：连接；
    属性usedb       ：布尔值；
}// TODO

TODO.conn = null;
TODO.usedb = true;

//----------------------------------------数据插入---------------- -----------
将 userName 插入 TODO.userNames 时触发 {  
    // TODO: 如果发生错误，从 ListBox 中删除 userName

    if(this.usedb) {
        尝试 {
            var stmt: Statement = this.conn.createStatement();

            this.conn.setAutoCommit(false);

            // 在数据库中插入新的用户名
            //往数据库插入一条记录
            var rows = stmt.executeUpdate("INSERT INTO Uuser (userName) VALUES('{userName.userName}')");
            println("INSERT rows: {rows} for {userName.userName}");

            // 从数据库获取用户名的 userName
            // 从数据库获取用户名
            var rs = stmt.executeQuery('SELECT userName FROM Uuser');
            if(rs.next ()) {
                userName.userName = rs.getString(1);
               this.conn.commit();
            }// if(rs.next())
        } catch(e :SQLException){
            // 以对话框的形式弹出异常
            MessageDialog {
                messageType: ERROR//消息内型
                title      : "TODO - 添加标题用户名"//
                消息: "SQL: {e.getMessage()}"//消息体
                可见    : true//可见
            }// MessageDialog      
        } 最后 {
            this.conn.setAutoCommit(true ）； //自动提交
        }
    }// if(this.usedb)       
}// 插入 userName

时触发

//----------------------------------------数据删除---------------- --------------------
从 TODO.userNames 中删除 userName 时触发 {
    // TODO: 如果发生错误，请在 ListBox 中再次插入 userName

    if(this.usedb) {
        try {
            var stmt: Statement = this.conn.createStatement();
           // 从数据库删除一条记录
            var rows = stmt.executeUpdate ("DELETE FROM Uuser WHERE userName = '{userName.userName}'");
            println("删除行：{userName.userName} 的 {rows}");
        } catch(e:SQLException) {
            MessageDialog {
                messageType: ERROR
                title      : "TODO - 删除用户名"
                message    : "SQL: {e.getMessage()}"
                可见    : true
            }// MessageDialog 
        }
    }// if(this.usedb)
}// 删除时触发

 

// 数据库变量

var db  : 数据库 = null;
var stmt: 语句 = null;
var rs : ResultSet = null;

var rows：数字；

db = Database{driverName: 'org.apache.derby.jdbc.ClientDriver'//数据库驱动类
              jdbcUrl   : 'jdbc:derby://localhost:1527/sample'//数据库连接url
              user      : 'app'//用户名
              密码  : 'app'};//密码
                 

var model = TODO {
    conn: 绑定惰性 db.conn
};

//----------------------------------------创建表--------- -------------------
尝试 {
    // 连接到数据库

    db.connect();
    stmt = db.conn.createStatement();

    // 创建表
    //创建表，并插入多余记录
    if(not db.tableExists('Uuser'))
    {
        rows = stmt.executeUpdate ("CREATE TABLE Uuser(id   INT , userName VARCHAR(50))");
        println("CREATE TABLE rows: {rows}");                                           

        rows = stmt.executeUpdate("INSERT INTO Uuser VALUES(1, 'do')");
        println("INSERT rows: {rows}");

        rows = stmt.executeUpdate("INSERT INTO Uuser VALUES(2, 'did')");
        println("INSERT rows: {rows}");

       
    }// if(not db.tableExists('Uuser'))

    // 从数据库中获取用户名并将用户名添加到 model.userNames (ListBox)
   
    model.usedb = false;
    //从数据库读取记录，并插入到模型中。 userNames（其实就是显示在listBox中）
    var rs = stmt.executeQuery("SELECT * FROM Uuser ORDER BY id ASC");
   
    while(rs.next()) {
        println( "id: {rs.getInt('id')} userName: {rs.getString('userName')}");
        插入 userName{id: rs.getInt('id') userName: rs.getString( 'userName')} 到 model.userNames;
    }
   
    model.usedb = true;

//--------------------------面板-----------------------------
    Frame {
        title  : "TODO list with JFXTrigger Example"
        onClose: function() {
            return db.shutdown();//面板关闭，关闭数据库相关资源
        }

        content: BorderPanel {
            center: ListBox {
                selection: bind model.selecteduserName
                cells    : bind foreach (userName in model.userNames)
                    ListCell {
                       text: userName.userName
                    }
            }// ListBox

            bottom: FlowPanel {
                content: [
                    TextField {
                        columns: 30
                        value  : bind model.newuserName
                    }, // TextField

                   //增加按钮，点击增加一条记录
                    Button {
                        text   : 'Add'
                        enabled: bind model.newuserName.length() > 0
                        action : operation() {
                            insert userName{userName: model.newuserName} into model.userNames;
                            model.newuserName = '';
                        }
                    }, // Button
                   //删除按钮，点击删除一条记录
                    Button {
                       text   : 'Delete'
                       enabled: bind sizeof model.userNames > 0
                       action : operation() {
                           delete model.userNames[model.selecteduserName];
                       }// Button
                    }
                ]// content
            }// FlowPanel
        }// BorderPanel
       
        visible: true
    }// Frame
          
} catch(e:SQLException) {
    e.printStackTrace();
}