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 databasepublic 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);}// Databaseattribute 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.shutdownoperation 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 listclass userName { attribute id : Number; attribute userName: String;}// userName // Todo listclass TODO { attribute userNames : userName*; attribute selecteduserName: Number; attribute newuserName : String; attribute conn : Connection; attribute usedb : Boolean;}// TODOTODO.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 varsvar 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();}