JavaFX et base de données

trois classes java pour exécuter les requettes en asynchone et parser basiquement le resultset en reponse.

un classe javafx ou tu choisi si tu attends un resultset, si tu veux updater le PreparedStatement et des fonctions pour traiter le resultat.

Voilou:
AsyncQuery.java

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
 
package database;
 
import com.sun.javafx.runtime.async.AbstractAsyncOperation;
import com.sun.javafx.runtime.async.AsyncOperationListener;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
 
/**
 *
 * @author alex
 */
 
public class AsyncQuery extends AbstractAsyncOperation {
 
    private PreparedStatement ps;
    private Connection connection;
    private String query;
 
    @SuppressWarnings("unchecked")
    public AsyncQuery(
            Connection connection,
            String query,
            AsyncOperationListener listener) {
        super(listener);
        this.connection = connection;
        this.query = query;
        try {
            ps = connection.prepareStatement(query);
        } catch (SQLException ex) {
            Logger.getLogger(AsyncQuery.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
 
    @Override
    public Object call() throws Exception {
        try {
            if(ps != null) {
                ps.execute();
                return true;
            }
            else {
                return false;
            }
        } catch (Exception e) {
            System.out.println("AsyncValueQueryError: "+e.getMessage()
                    +"\nParams: query="+query);
            return false;
        }
    }
 
    public PreparedStatement getPs() {
        return ps;
    }
 
}

AsyncValueQuery.java

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
 
/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
 
package database;
 
import com.sun.javafx.runtime.async.AbstractAsyncOperation;
import com.sun.javafx.runtime.async.AsyncOperationListener;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
 
 
/**
 *
 * @author alex
 */
public class AsyncValueQuery extends AbstractAsyncOperation {
    private PreparedStatement ps;
    private Connection connection;
    private String query;
 
    @SuppressWarnings("unchecked")
    public AsyncValueQuery(
            Connection connection,
            String query,
            AsyncOperationListener listener) {
        super(listener);
        this.connection = connection;
        this.query = query;
        try {
            ps = connection.prepareStatement(query);
        } catch (SQLException ex) {
            Logger.getLogger(AsyncValueQuery.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
 
 
 
    @Override
    public Object call() throws Exception {
        try {
            if( ps != null) {
                ResultSet rs = ps.executeQuery(query);
                QueryResult qr = new QueryResult(rs);
                return qr;
            } else {
                return null;
            }
        } catch (Exception e) {
            System.out.println("AsyncValueQueryError: "+e.getMessage()
                    +"\nParams: query="+query);
            return null;
        }
 
    }
 
    public PreparedStatement getPs() {
        return ps;
    }
 
}

QueryResult.java

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
 
/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
 
package database;
 
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
 
/**
 *
 * @author Alex
 */
public class QueryResult {
 
    private ArrayList<String> headers;
    private int nbResult = 0;
    private int nbColum = 0;
    private ResultSet resultSet;
 
    /**
     *
     * @param rs
     */
    public QueryResult( ResultSet rs ) {
        this.resultSet = rs;
        try {
            if (rs != null) {
                nbColum = rs.getMetaData().getColumnCount();
                headers = new ArrayList<String>(nbColum);
                for (int i = 0; i < nbColum; i++) {
                    headers.add(rs.getMetaData().getColumnName(i+1));
                }
                if (rs.first() ) {
                    do  {
                        nbResult ++;
                    } while (rs.next());
                }
                //System.out.println("nbcols: " + nbColum + " nb res: "+ nbResult);
            }
        } catch (SQLException ex) {
            Logger.getLogger(QueryResult.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
 
    /**
     *
     * @return
     */
    public ArrayList<String> getHeaders() {
        return headers;
    }
 
    /**
     *
     * @return
     */
    public int getNbColum() {
        return nbColum;
    }
 
    /**
     *
     * @return
     */
    public ResultSet getResultSet() {
        return resultSet;
    }
 
    /**
     *
     * @return
     */
    public int getNbResult() {
        return nbResult;
    }
 
}

et la classe javaFX Query.fx

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
 
/*
 * Query.fx
 *
 * Created on 4 mars 2009, 10:46:31
 */
 
package database;
 
import database.AsyncQuery;
import database.AsyncValueQuery;
import database.QueryResult;
import java.lang.Object;
import java.sql.Connection;
import java.sql.PreparedStatement;
import javafx.async.AbstractAsyncOperation;
 
/**
 * @author alex
 */
 
public class Query extends AbstractAsyncOperation{
 
    public-init var sqlConnection: Connection = null;
    public-init var retrieveValue: Boolean = false;
    public-init var update: Boolean = false;
    public-init var query: String = null;
    public var onResult: function(result: QueryResult);
    public var onUpdate: function(ps: PreparedStatement): Boolean;
    public var onValid: function(valid: Boolean);
    var task: com.sun.javafx.runtime.async.AbstractAsyncOperation;
 
    public override function cancel() : Void {
        if (task != null) {
            task.cancel();
        }
    }
 
    protected override function start() : Void {
        //println("execute: {query}");
        var allowRun = true;
        if( retrieveValue ) {
            task = new AsyncValueQuery(
                sqlConnection,
                query,
                this.listener);
             if( update ) {
                 var avq = task as AsyncValueQuery;
                 allowRun = onUpdate( avq.getPs() );
             }
 
        } else  {
            task = new AsyncQuery(
                sqlConnection,
                    query,
                    this.listener);
           if( update ) {
                 var aq = task as AsyncQuery;
                 allowRun = onUpdate( aq.getPs() );
             }
        }
        if (allowRun) task.start();
    }
 
    protected override function onCompletion(value : Object) : Void {
        //println("onCompletion");
        if( retrieveValue ) {
            onResult(value as QueryResult)
        } else {
            onValid(value as Boolean);
        }
    }
 
}

@+++;