javafx學(xué)習(xí)之?dāng)?shù)據(jù)庫操作

上次我們學(xué)習(xí)的javafx連接數(shù)據(jù)庫的簡單操作,這次我們來做一個(gè)稍為復(fù)雜的操作數(shù)據(jù)庫示例.IDE是:netbeans 6 beat 2,數(shù)據(jù)庫用javaDB(jdb6自帶有javaDB數(shù)據(jù)庫,netbeans 6也帶有,本例使用IDE自帶的javaDB),由于水平問題中文只注解了部份代碼,請(qǐng)見諒.(如出錯(cuò),請(qǐng)把

上次我們學(xué)習(xí)的javafx連接數(shù)據(jù)庫的簡單操作,這次我們來做一個(gè)稍為復(fù)雜的操作數(shù)據(jù)庫示例.IDE是:netbeans 6 beat 2,數(shù)據(jù)庫用javaDB(jdb6自帶有javaDB數(shù)據(jù)庫,netbeans 6也帶有,本例使用IDE自帶的javaDB),由于水平問題中文只注解了部份代碼,請(qǐng)見諒.(如出錯(cuò),請(qǐng)把中文注解刪除)

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;

//-------------------------連接數(shù)據(jù)庫-----------------------------------
operation Database.connect() {
// Load driver class using context class loader
// 加載驅(qū)動(dòng)
var thread = Thread.currentThread();
var classLoader = thread.getContextClassLoader();
var driverClass = classLoader.loadClass(this.driverName);

// Instantiate and register JDBC driver
//實(shí)例并注冊(cè)驅(qū)動(dòng)
this.driver = (Driver) driverClass.instantiate(); // JavaFX Class
DriverManager.registerDriver(driver);

// Connect to database
//連接數(shù)據(jù)庫
this.conn = DriverManager.getConnection(this.jdbcUrl, this.user, this.password);
}// Database.connect

//--------------------關(guān)閉資源---------------------------
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
//檢查表是否存在，注意這里并沒有主動(dòng)去刪除
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 {
attribute userNames : userName*;
attribute selecteduserName: Number;
attribute newuserName : String;

attribute conn : Connection;
attribute usedb : Boolean;
}// TODO

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

//---------------------------數(shù)據(jù)插入---------------------------
trigger on insert userName into TODO.userNames {
// TODO: Remove userName from ListBox if an error occurs

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

this.conn.setAutoCommit(false);

// Insert new userName in database
//往數(shù)據(jù)庫插入一條記錄
var rows = stmt.executeUpdate("INSERT INTO Uuser (userName) VALUES('{userName.userName}')");
println("INSERT rows: {rows} for {userName.userName}");

// Get userName of the userName from database
//從數(shù)據(jù)庫得到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){
//以對(duì)話框的形式彈出異常
MessageDialog {
messageType: ERROR//消息內(nèi)型
title : "TODO - Add userName"http://標(biāo)題
message : "SQL: {e.getMessage()}"http://消息體
visible : true//可見
}// MessageDialog
} finally {
this.conn.setAutoCommit(true);//自動(dòng)提交
}
}// if(this.usedb)
}// trigger on insert userName

//---------------------------數(shù)據(jù)刪除------------------------------------
trigger on delete userName from TODO.userNames {
// TODO: Insert userName again in ListBox if an error occurs

if(this.usedb) {
try {
var stmt: Statement = this.conn.createStatement();
//從數(shù)據(jù)庫刪除一條記錄
var rows = stmt.executeUpdate("DELETE FROM Uuser WHERE userName = '{userName.userName}'");
println("DELETE rows: {rows} for {userName.userName}");
} catch(e:SQLException) {
MessageDialog {
messageType: ERROR
title : "TODO - Delete userName"
message : "SQL: {e.getMessage()}"
visible : true
}// MessageDialog
}
}// if(this.usedb)
}// trigger on delete

// Database vars

var db : Database = null;
var stmt: Statement = null;
var rs : ResultSet = null;

var rows: Number;

db = Database{driverName: 'org.apache.derby.jdbc.ClientDriver'//數(shù)據(jù)庫驅(qū)動(dòng)類
jdbcUrl : 'jdbc:derby://localhost:1527/sample'//數(shù)據(jù)庫連接url
user : 'app'//用戶名
password : 'app'};//密碼

var model = TODO {
conn: bind lazy db.conn
};

//-------------------------------創(chuàng)建表----------------------------
try {
// Connect to database

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

// Create table
//創(chuàng)建表,并插入兩條記錄
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'))

// Get userNames from database and add userNames to model.userNames (ListBox)

model.usedb = false;
//從數(shù)據(jù)庫讀取記錄，并插入到model.userNames（其實(shí)就是顯示在listBox)
var rs = stmt.executeQuery("SELECT * FROM Uuser ORDER BY id ASC");

while(rs.next()) {
println("id: {rs.getInt('id')} userName: {rs.getString('userName')}");
insert userName{id: rs.getInt('id') userName: rs.getString('userName')} into model.userNames;
}

model.usedb = true;

//--------------------------面板-----------------------------
Frame {
title : "TODO list with JFXTrigger Example"
onClose: function() {
return db.shutdown();//面板關(guān)閉，關(guān)閉數(shù)據(jù)庫相關(guān)資源
}

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

//增加按鈕，點(diǎn)擊增加一條記錄
Button {
text : 'Add'
enabled: bind model.newuserName.length() > 0
action : operation() {
insert userName{userName: model.newuserName} into model.userNames;
model.newuserName = '';
}
}, // Button
//刪除按鈕，點(diǎn)擊刪除一條記錄
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();
}

聲明：本網(wǎng)頁內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com