javafx删除mysql_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 {

attribute userNames       : userName*;

attribute selecteduserName: Number;

attribute newuserName     : String;

attribute conn        : Connection;

attribute usedb       : Boolean;

}// TODO

TODO.conn  = null;

TODO.usedb = true;

//---------------------------数据插入---------------------------

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

//往数据库插入一条记录

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

//从数据库得到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 - Add userName"//标题

message    : "SQL: {e.getMessage()}"//消息体

visible    : true//可见

}// MessageDialog

} finally {

this.conn.setAutoCommit(true);//自动提交

}

}// if(this.usedb)

}// trigger on insert userName

//---------------------------数据删除------------------------------------

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();

//从数据库删除一条记录

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'//数据库驱动类

jdbcUrl   : 'jdbc:derby://localhost:1527/sample'//数据库连接url

user      : 'app'//用户名

password  : 'app'};//密码

var model = TODO {

conn: bind lazy db.conn

};

//-------------------------------创建表----------------------------

try {

// Connect to database

db.connect();

stmt = db.conn.createStatement();

// Create table

//创建表,并插入两条记录

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;

//从数据库读取记录，并插入到model.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')}");

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();//面板关闭，关闭数据库相关资源

}

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();

}