Skip to main content

DataTable from mysql

4 replies [Last post]
ibme74
Offline
Joined: 2007-12-19
Points: 0

Hello, I am a beginner in java fx,
I have a doubt it is possible to connect to mysql to do a table of data?
How to do?

Thanks, and sorry for my bad english.

Reply viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.
dnivra
Offline
Joined: 2007-01-11
Points: 0

> Hello, I am a beginner in java fx,
> I have a doubt it is possible to connect to mysql to
> do a table of data?
Yes.

> How to do?
Just like how you would do it in Java.
http://dev.mysql.com/usingmysql/java/

ibme74
Offline
Joined: 2007-12-19
Points: 0

Thanks dnivra, I managed to test run this code, I do not know if it is right but seems to work, I still have to swap the results, add filters, etc.
Will know more tutorials to do this?

[code]import javafx.ui.*;
import java.lang.Thread;
import java.lang.Exception;
import java.sql.*;
// Using the memory mode of hsqldb, so that the testdb is only
// created in RAM and no data is stored on disk.

var driverClassName = "com.mysql.jdbc.Driver";
var jdbcUrl = "jdbc:mysql://localhost:3306/table";
var user = "root";
var password = "";

// 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.execute("SHUTDOWN"); // Clean up
} 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 Pais in the Todo list

class Pais {
attribute idreg : Number;
attribute codigo: String;
attribute nombre: String;
}// Pais

// Todo list

class TODO {
attribute paises : Pais*;
attribute selectedPais: Number;
attribute newPais : String;

attribute conn : Connection;

public operation load();
public operation add();
public operation remove();
}// TODO

TODO.conn = null;

operation TODO.load() {
var stmt = this.conn.createStatement();
var rs = stmt.executeQuery("SELECT * FROM referen ORDER BY codigo ASC LIMIT 0,200");

while(rs.next()) {
println("codigo: {rs.getInt('idreg')} Pais: {rs.getString('codigo')}");
insert Pais{idreg: rs.getInt('idreg') codigo: rs.getString('codigo') nombre: rs.getString('nombre')} into this.paises;
}
}// TODO.load

operation TODO.add() {
try {
var Pais = Pais{codigo: this.newPais nombre: this.newPais};
var stmt = this.conn.createStatement();

this.conn.setAutoCommit(false);

// Insert new Pais in database

var rows = stmt.executeUpdate("INSERT INTO referen (codigo,nombre) VALUES('{Pais.codigo}','{Pais.nombre}')");
println("INSERT rows: {rows} for {Pais.codigo} {Pais.nombre}");

// Get id of the Pais from database

var rs = stmt.executeQuery('CALL IDENTITY()');
if(rs.next()) {
Pais.idreg = rs.getInt(1);
this.conn.commit();

insert Pais as last into this.paises;
}// if(rs.next())
} catch(e:SQLException){
MessageDialog {
messageType: ERROR
title : 'TODO - Add Pais'
message : "SQL: {e.getMessage()}"
visible : true
}// MessageDialog
} finally {
this.conn.setAutoCommit(true);
}
}// TODO.add

operation TODO.remove() {
if(sizeof this.paises > 0) {
try {
var stmt = this.conn.createStatement();
var Pais = this.paises[this.selectedPais];

var rows = stmt.executeUpdate("DELETE FROM referen WHERE idreg = {Pais.idreg}");
println("DELETE rows: {rows} for {Pais.idreg}");

delete this.paises[this.selectedPais];
} catch(e:SQLException) {
MessageDialog {
messageType: ERROR
title : 'TODO - Delete Pais'
message : "SQL: {e.getMessage()}"
visible : true
}// MessageDialog
}
}// if(sizeof this.paises > 0)
}// TODO.remove

// Database vars

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

var rows: Number;

db = Database{driverName : "com.mysql.jdbc.Driver"
jdbcUrl : "jdbc:mysql://localhost:3306/table"
user : "root"
password : ""};

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

try {
// Connect to database

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

// Create table

if(not db.tableExists('paises'))
{
MessageDialog {
messageType: ERROR
title : 'TODO - Delete Pais'
message : "database no existe"
visible : true
}// if(not db.tableExists('TODOS'))
}
model.load();

Frame {
title : "TODO list with operations Example"
onClose: function() {
return db.shutdown();
}

content: BorderPanel {
center:Table {
columns:
[TableColumn {
text: "Idreg"
},
TableColumn {
text: "Código"
},
TableColumn {
text: "Nombre"
width: 100

}]

selection: bind model.selectedPais
cells : bind foreach (Pais in model.paises)
[TableCell {
//text: Pais.idreg
},TableCell {
text: Pais.codigo
},TableCell {
text: Pais.nombre
}]
}//table

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

Button {
text : 'Add'
enabled: bind model.newPais.length() > 0
action : operation() {
model.add();
model.newPais = '';
}
}, // Button

Button {
text : 'Delete'
enabled: bind sizeof model.paises > 0
action : operation() {
model.remove();
}// Button
}
]// content
}// FlowPanel
}// BorderPanel

visible: true
}// Frame

} catch(e:SQLException) {
e.printStackTrace();
}[/code]

henrymiller
Offline
Joined: 2008-08-13
Points: 0

Hello,
I'm a beginner on JAVAFX as well.
I've just copied the source below but I've got this error message that I cannot really understand.

JAVAFX CODE

import javafx.ui.*;
import java.lang.Thread;
import java.lang.Exception;
import java.sql.*;
// Using the memory mode of hsqldb, so that the testdb is only
// created in RAM and no data is stored on disk.

var driverClassName = "com.mysql.jdbc.Driver";
var jdbcUrl = "jdbc:mysql://localhost:3306/table";
var user = "root";
var password = "";

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

..... continue.......

ERROR

Compiling 1 source file to /Users/giovanni/NetBeansProjects/JavaFX_DB/build/classes
/Users/giovanni/NetBeansProjects/JavaFX_DB/src/javafx_db/Main.fx:33: Sorry, I was trying to understand the module contents but I got confused when I saw 'driverClass' which is an identifier.
var driver = (Driver) driverClass.instantiate(); // JavaFX Class
1 error
BUILD FAILED (total time: 0 seconds)

Can you please help me .

Thanks
HenryMiller

[b][/b][b][/b][b][/b]

indy
Offline
Joined: 2003-06-10
Points: 0

Hello,

may be this example works also with the JavaFX SDK Preview SDK:

http://jfx.wikia.com/wiki/Database

tschuess
[|8:)