how to handle a database connection

[Scott Hibbard]

I'm in need of some design advise regarding handling a database connection for a small GUI.

When/how often should you open and close the database file? It seems terribly inefficient to open and close the database every time you need to read or write to it. So far I'm trying to open it once and close it once on exit. Does that sound like the way to go? Are there any downsides to that approach?

Assuming that's the way to go, how do you go about doing this? Right now I have the following code in main:

        ObjectContainer db = Db4oEmbedded.openFile(Db4oEmbedded.newConfiguration(), "F:\\snowflake\\database\\snowflakeDB.yap");
        try{
            PersonList list = new PersonList(db);
            PersonListController controller = new PersonListController(list);
            PersonListView view = new PersonListView(list, controller);
        }finally{
            System.out.println("Closing DB");
            db.close();
        }

Here's PersonListView

package com.scotthibbard.snowflake;

import java.util.*;
import org.joda.time.*;
import javax.swing.*;
import java.awt.*;
import javax.swing.event.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;

class PersonListView implements PersonListObserver{

    boolean currentPersonChanged = false;
    PersonListInterface personList;
    PersonListControllerInterface controller;
    JFrame frame;
    JPanel personViewPanel;
    JPanel navPanelTop;
    JPanel navPanelBottom;
    JTextField first = new JTextField("");
    JTextField middle = new JTextField("");
    JTextField last = new JTextField("");
    JTextField ssn = new JTextField("");
    JTextField dob = new JTextField("");
    JLabel firstLabel = new JLabel("First Name");
    JLabel middleLabel = new JLabel("Middle Name");
    JLabel lastLabel = new JLabel("Last Name");
    JLabel ssnLabel = new JLabel("SSN");
    JLabel dobLabel = new JLabel("DOB");
//    ImageIcon prevImg = new ImageIcon("F:\\snowflake\\images\\prev.gif");
    JButton prevButton = new JButton("<<");

//    ImageIcon nextImg = new ImageIcon("F:\\snowflake\\images\\next.gif");
    JButton nextButton = new JButton(">>");

    JLabel listPositionLabel = new JLabel("0 of 0", SwingConstants.CENTER);

    public PersonListView (PersonList l, PersonListController cont){

        personList = l;
        controller = cont;

        personList.registerObserver(this);

        prevButton.addActionListener(new ActionListener(){
            public void actionPerformed(ActionEvent e){
                System.out.println(currentPersonChanged);
                if(currentPersonChanged){
                    savePerson();
                }
                controller.previousPerson();
            }
        });

        nextButton.addActionListener(new ActionListener(){
            public void actionPerformed(ActionEvent e){
                System.out.println(currentPersonChanged);
                if(currentPersonChanged){
                    savePerson();
                }
                controller.nextPerson();
            }
        });

        frame = new JFrame();
        frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);

        first.getDocument().addDocumentListener(new ChangeListener());
        middle.getDocument().addDocumentListener(new ChangeListener());
        last.getDocument().addDocumentListener(new ChangeListener());
        ssn.getDocument().addDocumentListener(new ChangeListener());
        dob.getDocument().addDocumentListener(new ChangeListener());

        firstLabel.setHorizontalAlignment(SwingConstants.RIGHT);
        middleLabel.setHorizontalAlignment(SwingConstants.RIGHT);
        lastLabel.setHorizontalAlignment(SwingConstants.RIGHT);
        ssnLabel.setHorizontalAlignment(SwingConstants.RIGHT);
        dobLabel.setHorizontalAlignment(SwingConstants.RIGHT);

        personViewPanel = new JPanel();
        personViewPanel.setLayout(new GridBagLayout());
        GridBagConstraints labelc = new GridBagConstraints();
        labelc.gridx = 0;  // column 0
        labelc.gridy = 0;  // row 0
        labelc.ipadx = 5;
//        labelc.ipady = 10;
        labelc.anchor = GridBagConstraints.NORTHEAST;

        GridBagConstraints c = new GridBagConstraints();
        c.gridx = 1;  // column 0
        c.gridy = 0;  // row 0
        c.ipadx = 5;
//        c.ipady = 10;
        c.weightx = 1;
        c.fill = GridBagConstraints.HORIZONTAL;
        c.anchor = GridBagConstraints.NORTHWEST;

        personViewPanel.add(firstLabel, labelc);
        personViewPanel.add(first, c);
        labelc.gridy = 1;
        c.gridy = 1;
        personViewPanel.add(middleLabel, labelc);
        personViewPanel.add(middle, c);
        labelc.gridy = 2;
        c.gridy = 2;
        personViewPanel.add(lastLabel, labelc);
        personViewPanel.add(last, c);
        labelc.gridy = 3;
        c.gridy = 3;
        personViewPanel.add(ssnLabel, labelc);
        personViewPanel.add(ssn, c);
        labelc.gridy = 4;
        c.gridy = 4;
        personViewPanel.add(dobLabel, labelc);
        personViewPanel.add(dob, c);

        frame.getContentPane().add(BorderLayout.CENTER, personViewPanel);

        navPanelBottom = new JPanel();
        navPanelBottom.setLayout(new BorderLayout());

        navPanelBottom.add(BorderLayout.WEST, prevButton);
        navPanelBottom.add(BorderLayout.CENTER, listPositionLabel);
        navPanelBottom.add(BorderLayout.EAST, nextButton);

        frame.getContentPane().add(BorderLayout.SOUTH, navPanelBottom);


        frame.setSize(300,300);
        frame.setVisible(true);

        updatePersonList();
    }


    public void updatePersonList(){
        listPositionLabel.setText(personList.getPosition() + " of " + personList.getSize());
        Person p = personList.getPerson();
        first.setText(p.getFirstName());
        middle.setText(p.getMiddleName());
        last.setText(p.getLastName());
        ssn.setText("");
        dob.setText(p.getDateOfBirthString());
        currentPersonChanged = false;
    }

    public void savePerson(){
        Person p = new Person(first.getText(), middle.getText(), last.getText());
        //p.setDateOfBirth(dob.getText());
        controller.savePerson(p);
    }

    class ChangeListener implements DocumentListener {

        public void insertUpdate(DocumentEvent e) {
            System.out.println("insertupdate");
            currentPersonChanged = true;
        }
        public void removeUpdate(DocumentEvent e) {
            System.out.println("removeupdate");
            currentPersonChanged = true;
        }
        public void changedUpdate(DocumentEvent e) {
            System.out.println("changedupdate");
            currentPersonChanged = true;
        }
    }

}

I'm using a very simple MVC pattern. PersonListView handles the swing components including a JFrame to hold it all together. I'd like the database to be available until the window is closed which it's not.

Can someone explain why db.close() is reached before the window is closed? I'm assuming some new threads are kicked off somewhere in there but I'm a little fuzzy on the details.

The big question I'm looking for is this: How do I gracefully handle resources like a database in a locally run swing app - specifically on close? Should I create a helper class to handle the connection? Where do I put code to handle the cleanup when the window is closed?

Thanks in advance for any and all help!

[Scott Hibbard]

nevermind. I figured it out. :-)

I changed the default close operation to do nothing and added a Window Listener to listen for a windowClosing event. Using the listener, I was able to close the database, dispose of my frame and exit the program.

    
class Snowflake {

DatabaseHandler dbh = new DatabaseHandler();
PersonListInterface list = new PersonList(dbh);
PersonListControllerInterface controller = new PersonListController(list);
PersonListView view = new PersonListView(list, controller);

JFrame frame = new JFrame();


    public Snowflake () {

        // window closing handler
        WindowListener wl = new WindowAdapter(){
            public void windowClosing(WindowEvent e){
                close();
            }
        };

        // create window & add components
        frame.setDefaultCloseOperation(JFrame.DO_NOTHING_ON_CLOSE);
        frame.addWindowListener(wl);
        frame.getContentPane().add(view.getPanel());

        frame.setSize(300,300);
        frame.setVisible(true);

    }

    void close(){
        view.close();
        frame.dispose();
        dbh.close();
        System.exit(0);
    }

    public static void main ( String[] arg ){
        Snowflake mySnowflake = new Snowflake();
    }

}