Database connect, load data loop error

Question

I currently have two different fxml pages. One is the main screen, and the other handles connections. The main Screen is currently creating the database and the tables needed on initialize. The other page is for adding and deleting values from the database. The issue becomes that I am currently running a function on the main page to populate the choiceboxes with the database data in the initialize, but when it runs it tries to create the table again and throws an error because the tables is already created. Is there an easy way to solve this?

Below is the Main pages initialize function:

public void initialize() {
        try {

            Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();
            //below gets the connection specified earlier
            conn = DriverManager.getConnection(dbURL);
            System.out.println("DB Connection Created!");

            String createString = "CREATE TABLE Connections (DETAILS VARCHAR(255) NOT NULL, NAME VARCHAR(255) UNIQUE)";
            stmt = conn.createStatement();
            stmt.execute(createString);
            System.out.println("Connections table created!");


            //below is for populating the usernames
            String sql2 = "Select NICKNAME FROM Users ORDER BY NICKNAME ASC";
            stmt = conn.createStatement();

            ResultSet rs2 = stmt.executeQuery(sql2);

            usernameList.clear();
            while(rs2.next()) {
                String name = rs2.getString("NICKNAME");
                usernameList.add(name);
                System.out.println("User initialize: " + name);
            }
            System.out.println("Objects: " + usernameList);
            sourceUsername.setItems(FXCollections.observableArrayList(usernameList));
            cbosUsername.setItems(FXCollections.observableArrayList(usernameList));
            tiUsername.setItems(FXCollections.observableArrayList(usernameList));
        }
        catch(Exception e) {
            e.printStackTrace();
        }
    }

Does anyone have any input on an easy way to solve this?

so you execute `initialize()` multiple times? Why don't you move create statement somewhere else, so you don't execute it twice? — JohnnyAW, Aug 02 '17 at 12:07
The issue is I need to populate the data initially, and if there is no table created then attempting to select data from it with throw an error as well — dgelinas21, Aug 02 '17 at 12:16
I mean, you could move the code to a section, that will be executed only once on the start up, so in further code you can assume, that the table was being created — JohnnyAW, Aug 02 '17 at 12:23
The real question is why `initialize()` is getting called more than once. That shouldn't happen. Can you post more code: read (and post) the stack trace to see what's relevant. — James_D, Aug 02 '17 at 12:24
It isn't getting called more than once when it loads. The issue is there are other pages in the application as well. So when a segue to another screen, then back to the main screen it calls initialize again. breaking it — dgelinas21, Aug 02 '17 at 12:26
Can't you just load it once? Keep references to the root node and controller and just reshow the same node instead of reloading it. — James_D, Aug 02 '17 at 12:27
https://stackoverflow.com/questions/5866154/how-to-create-table-if-it-doesnt-exist-using-derby-db — fabian, Aug 02 '17 at 15:27
Also it would be better to seperate the model from the view. (Create a different class for accessing the DB and [pass it to the fxml controller](https://stackoverflow.com/questions/14187963/passing-parameters-javafx-fxml)) — fabian, Aug 02 '17 at 15:29

JohnnyAW · Answer 1 · 2017-08-02T12:30:39.877

1

I think the best way would be to put CREATE-Statement to Application-Start-Method:

public class MyApp extends Application {
    public void start(Stage stage) {
        //perform create statement
    }
}

or simply catch all Exceptions on your CREATE-Statement:

try{
    String createString = "CREATE TABLE Connections (DETAILS VARCHAR(255) NOT NULL, NAME VARCHAR(255) UNIQUE)";
    stmt = conn.createStatement();
    stmt.execute(createString);
} catch (Exception e){
}

edited Aug 02 '17 at 12:30

answered Aug 02 '17 at 12:08

JohnnyAW

2,866
1
16
27

thought about doing this, but it is an embedded derby database, and does not support a "IF NOT EXISTS" SQL create statement – dgelinas21 Aug 02 '17 at 12:23
how about doing a `SELECT * FROM Connections` and catching exceptions? If you get the right exception, you can call CREATE-Statement – JohnnyAW Aug 02 '17 at 12:25
Don't squash the exceptions. If something else (i.e. unexpected) goes wrong, you won't know about it or be able to diagnose it. Moving the database creation to the `start(...)` method is probably the way to go here. – James_D Aug 02 '17 at 12:39
That was the reasoning as to why I was hesitant to just catch exceptions. There's no way to guarantee that this will also be the cause of them. – dgelinas21 Aug 02 '17 at 13:48
@dgelinas21 you don't need to swallow all exceptions, you can try to catch the one, that will be thrown on duplicate-creation. You can still process other exception in the usual way. I just don't know what exactly will be thrown here, so I put all exceptions as an Example. – JohnnyAW Aug 02 '17 at 14:20
@James_D you're totally right, I just don't know, what exactly will be thrown there, so I caught them all :) – JohnnyAW Aug 02 '17 at 14:29
The second approach is a bit unsatisfactory: exceptions should be for unexpected conditions. Here you're using a `try`-`catch` to handle a situation you're actually expecting (because you know you're in a situation where you will call `CREATE TABLE` multiple times). Really you should code to avoid that situation; either by moving the create statement to `start()`, as you suggest, or by ensuring the FXML is only loaded once. – James_D Aug 02 '17 at 14:32
@James_D I totally agree, but respecting the OP's question: `Does anyone have any input on an easy way to solve this?` I think this is the easiest way to solve this:) – JohnnyAW Aug 02 '17 at 14:36

Database connect, load data loop error

1 Answers1