qQQEh0zQ

1import java.sql.*;
2import java.util.logging.*;
3import javafx.application.Application;
4import javafx.collections.*;
5import javafx.event.ActionEvent;
6import javafx.event.EventHandler;
7import javafx.scene.Scene;
8import javafx.scene.control.*;
9import javafx.scene.layout.*;
10import javafx.stage.Stage;
11
12public class H2app extends Application {
13  private static final Logger logger = Logger.getLogger(H2app.class.getName());
14  private static final String[] SAMPLE_NAME_DATA = { "John", "Jill", "Jack", "Jerry" };
15
16  public static void main(String[] args) { launch(args); }
17
18  @Override public void start(Stage stage) {
19    final ListView<String> nameView = new ListView();
20
21    final Button fetchNames = new Button("Fetch names from the database");
22    fetchNames.setOnAction(new EventHandler<ActionEvent>() {
23      @Override public void handle(ActionEvent event) {
24        fetchNamesFromDatabaseToListView(nameView);
25      }
26    });
27
28    final Button clearNameList = new Button("Clear the name list");
29    clearNameList.setOnAction(new EventHandler<ActionEvent>() {
30      @Override public void handle(ActionEvent event) {
31        nameView.getItems().clear();
32      }
33    });
34
35    VBox layout = new VBox(10);
36    layout.setStyle("-fx-background-color: cornsilk; -fx-padding: 15;");
37    layout.getChildren().setAll(
38      HBoxBuilder.create().spacing(10).children(
39        fetchNames, 
40        clearNameList    
41      ).build(),      
42      nameView
43    );
44    layout.setPrefHeight(200);
45
46    stage.setScene(new Scene(layout));
47    stage.show();
48  }
49
50  private void fetchNamesFromDatabaseToListView(ListView listView) {
51    try (Connection con = getConnection()) {
52      if (!schemaExists(con)) {
53        createSchema(con);
54        populateDatabase(con);
55      }
56      listView.setItems(fetchNames(con));
57    } catch (SQLException | ClassNotFoundException ex) {
58      logger.log(Level.SEVERE, null, ex);
59    }
60  }
61
62  private Connection getConnection() throws ClassNotFoundException, SQLException {
63    logger.info("Getting a database connection");
64    Class.forName("org.h2.Driver");
65    return DriverManager.getConnection("jdbc:h2:~/test", "sa", "");
66  }
67
68  private void createSchema(Connection con) throws SQLException {
69    logger.info("Creating schema");
70    Statement st = con.createStatement();
71    String table = "create table employee(id integer, name varchar(64))";
72    st.executeUpdate(table);
73    logger.info("Created schema");
74  }
75
76  private void populateDatabase(Connection con) throws SQLException {
77    logger.info("Populating database");      
78    Statement st = con.createStatement();      
79    int i = 1;
80    for (String name: SAMPLE_NAME_DATA) {
81      st.executeUpdate("insert into employee values(i,'" + name + "')");
82      i++;
83    }
84    logger.info("Populated database");
85  }
86
87  private boolean schemaExists(Connection con) {
88    logger.info("Checking for Schema existence");      
89    try {
90      Statement st = con.createStatement();      
91      st.executeQuery("select count(*) from employee");
92      logger.info("Schema exists");      
93    } catch (SQLException ex) {
94      logger.info("Existing DB not found will create a new one");
95      return false;
96    }
97
98    return true;
99  }
100
101  private ObservableList<String> fetchNames(Connection con) throws SQLException {
102    logger.info("Fetching names from database");
103    ObservableList<String> names = FXCollections.observableArrayList();
104
105    Statement st = con.createStatement();      
106    ResultSet rs = st.executeQuery("select name from employee");
107    while (rs.next()) {
108      names.add(rs.getString("name"));
109    }
110
111    logger.info("Found " + names.size() + " names");
112
113    return names;
114  }
115}