Access H2 connection across classes

Question

I'm trying to avoid static and Singletons while integrating a local H2 database into my JavaFX app (Java8). Five other classes (including Controllers) require access to the database so I'm trying to share the one H2 connection between them all. I've read a Connection Pool avoids unnecessary reconnects, but am confused if it's applicable here. Desktop use, single user.

The following ExtrasDB Class contains 3 methods, initializeDB, getDBValues and performQuery. Previously these methods were static and I would call them from other classes using ExtrasDB.getDBValues() and use the result accordingly, but since my application uses multiple threads I'm looking for a better approach. I'm in the process of removing all static/Singleton use from my app.

The initializeDB method creates the connection and creates a table if necessary, and this method is called only once from my main controller's initialize method. This causes the connection conn to be isolated to that instance and not accessible to other class's calls on getDBValues or performQuery which causes null result sets. How do I make the database connection accessible to all necessary classes, so that these classes can freely access the database using the methods above?

public class ExtrasDB {

    final String JDBC_DRIVER = "org.h2.Driver";
    final String DB_URL = "jdbc:h2:~/mainDB";
    final String USER = "test";
    final String PASS = "test";
    Connection conn = null;
    Statement statement = null;
    String myStatement;
    ResultSet rs;
    DatabaseMetaData meta;

    public void initializeDB() {

        try {
            Class.forName("org.h2.Driver");
            conn = DriverManager.getConnection(DB_URL, USER, PASS);
            System.out.println("Connected to database successfully...");
            statement = conn.createStatement();
            meta = conn.getMetaData();
            rs = meta.getTables(null, null, "EXTRAS", new String[]{"TABLE"});  // <--- Checks for existence of table "EXTRAS"

            if (!rs.next()) {

                System.out.println("Table doesn't exist yet... Creating...");
                sql = "CREATE TABLE EXTRAS " +
                        "(column_1 VARCHAR(255), " +
                        " column_2 VARCHAR(255))";

                statement.executeUpdate(sql);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }


    public void getDBValues() throws SQLException {
        rs = statement.executeQuery("SELECT * FROM EXTRAS");
        while (rs.next()) {
            //..... do something
        }
    }

    public void performQuery(String query) {
        try {
            statement.executeUpdate(query);
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }
}

'performQuery(...) { executeUpdate(...); }` Mmm... what? Querying a database results in an update? Maybe if `ResultSet performQuery() { return this.conn.executeQuery(...); }` you don't actually need to share the connection and still get results? — Adrian Colomitchi, Sep 23 '16 at 02:52
@trashgod, I wanted to spend time understanding *[that example](https://github.com/pkouki/recsys2015/blob/master/h2/src/test/org/h2/test/jdbcx/TestConnectionPool.java)*, and it seems the `testKeepOpen()` method demonstrates how only `man.dispose()` closes the pool, however I'm confused about: (1) Should my class `ExtrasDB` be `public static ...`and what about the 3 methods, `public` or `private`? The reason I ask is (2) My other classes either can use `ExtrasDb.getDBValues()` (static) but if I instead create a ExtrasDB object in each class, it's creating a new ConnectionPool. How to share one? — MM88, Sep 23 '16 at 14:58
@AdrianColomitchi, Yes, it should be `executeQuery`, my bad. But I'm confused by your proposed solution, are you saying to replace my current `performQuery` method with `ResultSet performQuery() { return this.conn.executeQuery(...); }` How do I call this from other classes/controllers? Is this thread-safe/practical? Does it need to be a `public` or `private` method? Thanks for any feedback — MM88, Sep 23 '16 at 15:01
How did the controllers call it without my suggestion? Was it thread safe? — Adrian Colomitchi, Sep 23 '16 at 15:22
The second paragraph of my original question says how I previously used `ExtrasDB.methodName();` since the `ExtrasDB` class was static, but the [answer here](http://stackoverflow.com/a/9431863/6867589) says this is wrong. So I tried creating a ExtrasDB object using `ExtrasDB extrasDB = new ExtrasDB` in each of the classes that requries DB access, but this creates 5 separate objects, all with different connection pools. I need to use **one** connection pool across all of my classes, without making it static or using a Singleton. — MM88, Sep 23 '16 at 15:28
@MM88: Maybe a static factory, examined [here](http://stackoverflow.com/q/13046877/230513). — trashgod, Sep 23 '16 at 15:43

score 0 · Answer 1 · answered Nov 04 '16 at 13:07

You have two options if you don't want some sort of static access:

Create a Service-Factory instance (not static) at the start up of your Program. The Service-Factory is a pojo-class containing all dependencies you want to share across other classes. Those dependencies could be created by the Service-Factory or could be set with setters. All classes which needs some dependencies you only have to offer the Factory instance.
You could use some framework which implements automatic dependency injection. With such a framework you only have to annotate your constructor or fields. See this link for further information about dependency injection and this tutorial about how to use the spring framework for dependency injection.

In my JavaFX-App im using the first option. I have a class named Root and create an instance of it within the javafx.application.Application::start function. Some dependencies are created by the Root class and some others like (e.g. the controller instances) are set using setters. Because I'm loading my controllers via fxml-loader, I cant use the constructor to offer the Root instance, but instead every controller has a init function

public void initialize(Root root);

If you need more help. Feel free to comment this answer.

Access H2 connection across classes

1 Answers1