Database Operations for javafx Learning

The simple operations we learned last time to connect to the database with javafx, this time we will do a slightly complex database operation example. IDE: netbeans6beat2, the database uses javaDB (jdb6 comes with javaDB database, netbeans6 also has, this example uses javaDB of IDE). Due to horizontal issues, only some codes are annotated in Chinese, sorry. (if an error occurs

The simple operations we learned last time to connect to the database with javafx, this time we will do a slightly complex database operation example. IDE is: netbeans 6 beat 2, and the database uses javaDB (jdb6 comes with javaDB database, and netbeans 6 also comes with it. In this example, javaDB comes with IDE ), due to horizontal issues, only some codes are annotated in Chinese. Sorry. (if an error occurs

The simple operations we learned last time to connect to the database with javafx, this time we will do a slightly complex database operation example. IDE is: netbeans 6 beat 2, and the database uses javaDB (jdb6 comes with javaDB database, and netbeans 6 also comes with it. In this example, javaDB comes with IDE ), due to horizontal issues, only some codes are annotated in Chinese. Sorry. (if an error occurs, delete the Chinese annotation)

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;

// ------------------------- Connect to the database -----------------------------------
Operation Database. connect (){
// Load driver class using context class loader
// Load the driver
Var thread = Thread. currentThread ();
Var classLoader = thread. getContextClassLoader ();
Var driverClass = classLoader. loadClass (this. driverName );

// Instantiate and register JDBC driver
// Instance and register the driver
This. driver = (Driver) driverClass. instantiate (); // JavaFX Class
DriverManager. registerDriver (driver );

// Connect to database
// Connect to the database
This. conn = DriverManager. getConnection (this. jdbcUrl, this. user, this. password );
} // Database. connect

// -------------------- Close the resource ---------------------------
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
// Check whether the table exists. Note that the table is not deleted.
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;

// ----------------------------- Data insertion ---------------------------
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
// Insert a record into the database
Var rows = stmt.exe cuteUpdate ("insert into Uuser (userName) VALUES ('{userName. userName }')");
Println ("INSERT rows: {rows} for {userName. userName }");

// Get userName of the userName from database
// Obtain userName from the database
Var rs = stmt.exe cuteQuery ('select userName FROM uuser ');
If (rs. next ()){
UserName. userName = rs. getString (1 );
This. conn. commit ();
} // If (rs. next ())
} Catch (e: SQLException ){
// An exception is displayed in the dialog box.
MessageDialog {
MessageType: ERROR // Message Type
Title: "TODO-Add userName" // title
Message: "SQL: {e. getMessage ()}" // message Body
Visible: true // visible
} // MessageDialog
} Finally {
This. conn. setAutoCommit (true); // automatically submit
}
} // If (this. usedb)
} // Trigger on insert userName

// ----------------------------- Data deletion ------------------------------------
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 ();
// Delete a record from the database
Var rows = stmt.exe cuteUpdate ("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 '// Database Driver Class
JdbcUrl: 'jdbc: derby: // localhost: 1527/sample' // database connection url
User: 'app' // user Name
Password: 'app'}; // password

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

// --------------------------------- Create a table ----------------------------
Try {
// Connect to database

Db. connect ();
Stmt = db. conn. createStatement ();

// Create table
// Create a table and insert two records
If (not db. tableExists ('uuser '))
{
Rows = stmt.exe cuteUpdate ("create table Uuser (id INT, userName VARCHAR (50 ))");
Println ("create table rows: {rows }");

Rows = stmt.exe cuteUpdate ("insert into Uuser VALUES (1, 'Do ')");
Println ("INSERT rows: {rows }");

Rows = stmt.exe cuteUpdate ("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;
// Read records from the database and insert them to model. userNames (actually displayed in listBox)
Var rs = stmt.exe cuteQuery ("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;

// -------------------------- Panel -----------------------------
Frame {
Title: "TODO list with JFXTrigger Example"
OnClose: function (){
Return db. shutdown (); // closes the Panel and closes database-related resources.
}

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

// Add a record and click Add
Button {
Text: 'add'
Enabled: bind model. newuserName. length ()> 0
Action: operation (){
Insert userName {userName: model. newuserName} into model. userNames;
Model. newuserName = '';
}
}, // Button
// Click Delete to delete a record.
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 ();
}