Desktop persistence for a fantasy football application

[mlavwilson2]

I am writing an application and am starting to doubt my approach for persistence. I am currently persisting everything to a property file. However the more complex the app gets the uglier the data layer is getting. I have cached objects... cloning them to protect the cached objects... its getting messy. I am use to using JDBC in the web environment, but this is my first desktop app. What is the best practice for this environment?

[Rod Johnson]

Have you considered a lightweight database such as HSQL? I would definitely recommend a SQL/JDBC approach.

[mlavwilson2]

Have you considered a lightweight database such as HSQL? I would definitely recommend a SQL/JDBC approach.

That is what I am leaning towards. At first I though that a relation database would be overkill for a simple desktop app. However, the state management of persisting to a object to a properties file is killing me.

[lstreepy]

If all you need is a persistence model, and not any other features of SQL (such as SELECT processing), there are a several other models you can look at:

1. You can use the JDK's new long term persistence structures: XMLEncode and XMLDecode.

2. You can use XML forms of your objects: look at castor, jixb, or similar.

In essence, you create a very simple database using files to contain the persistent form of the objects your UI is managing. Obviously this model only works for limited cases, but it might be sufficient for what you need.

However, simply because it's a GUI doesn't mean that the persistence models you used for your web environment are not applicable. As always, you should pick the solution that meets your business requirements.

Anyway, it's some food for thought.


HTH,
Larry.

[mlavwilson2]

I ended up using a hybrid opproach of hsql and castor. hsql for the archival data, and castor for the user data.

One thing I thought would be worth sharing is a simple view for querying the embeded hsql database, this is great for development mode. Note, it tis not meant to be include in a finished product (sql in the view, yuck)

See attached screen-shot:

public class SqlNavigatorView extends AbstractViewImpl implements ApplicationListener
{
   private DataSource dataSource;
   private JTextArea sql = new JTextArea();
   private JTextArea sqlUpdate = new JTextArea();
   private JTable table;

   /**
    * @see org.springframework.richclient.factory.AbstractControlFactory#createControl()
    */
   protected JComponent _createControl()
   {
      JTabbedPane tabs = new JTabbedPane();

      GridBagLayoutBuilder builder = new GridBagLayoutBuilder();
      builder.append(new JScrollPane(sql), 1, 2, 1.0, 1.0);
      builder.append(execute.createButton(), 1, 1, 0.0, 0.0);
      builder.nextLine();
      builder.append(Box.createVerticalGlue(), 1, 1, 0.0, 1.0);
      tabs.add("Select", builder.getPanel());

      builder = new GridBagLayoutBuilder();
      builder.append(new JScrollPane(sqlUpdate), 1, 2, 1.0, 1.0);
      builder.append(executeUpdate.createButton(), 1, 1, 0.0, 0.0);
      builder.nextLine();
      builder.append(Box.createVerticalGlue(), 1, 1, 0.0, 1.0);
      tabs.add("Update", builder.getPanel());

      JSplitPane split = new JSplitPane(JSplitPane.VERTICAL_SPLIT);

      split.setTopComponent(tabs);

      split.setBottomComponent(new JScrollPane(table = new JTable()));

      return split;
   }

   protected ActionCommand execute = new ActionCommand("runSql", "Run")
   {
      protected void doExecuteCommand()
      {
         final StatusBar bar = (StatusBar) getActiveWindow().getStatusBar().getControl();
         bar.setCancelEnabled(false);
         bar.taskStarted("Executing Statement...", StatusBar.UNKNOWN);

         final SwingWorker worker = new SwingWorker()
         {
            public Object construct()
            {
               getActiveWindow().getControl().setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));

               Connection connection = null;
               PreparedStatement statement = null;
               ResultSet result = null;

               try
               {
                  connection = dataSource.getConnection();
                  statement = connection.prepareStatement(sql.getText());
                  result = statement.executeQuery();

                  SqlTableModel model = new SqlTableModel(result.getMetaData());
                  table.setModel(model);
                  model.fireTableStructureChanged();

                  while (result.next())
                  {
                     Object[] values = new Object[model.getColumnCount()];
                     for (int i = 0; i < model.getColumnCount(); i++)
                     {
                        values[i] = result.getObject(i + 1);
                     }
                     model.getData().add(values);
                  }

                  model.fireTableDataChanged();
               }
               catch (Exception e)
               {
                  e.printStackTrace();
               }
               finally
               {
                  try
                  {
                     result.close();
                     statement.close();
                     connection.close();
                  }
                  catch (SQLException ignore)
                  {}
               }

               return null;
            }

            public void finished()
            {
               bar.done();
               getActiveWindow().getControl().setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
            }
         };
         worker.start();

      }
   };

   protected ActionCommand executeUpdate = new ActionCommand("runSql", "Run")
   {
      protected void doExecuteCommand()
      {
         final StatusBar bar = (StatusBar) getActiveWindow().getStatusBar().getControl();

         final SwingWorker worker = new SwingWorker()
         {
            public Object construct()
            {
               getActiveWindow().getControl().setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));

               Connection connection = null;
               Statement statement = null;
               ResultSet result = null;

               try
               {
                  connection = dataSource.getConnection();
                  statement = connection.createStatement();
                  int count = statement.executeUpdate(sqlUpdate.getText());
                  bar.setMessage("Updated " + count + " rows.");
               }
               catch (Exception e)
               {
                  e.printStackTrace();
               }
               finally
               {
                  try
                  {
                     statement.close();
                     connection.close();
                  }
                  catch (SQLException ignore)
                  {}
               }

               return null;
            }

            public void finished()
            {
               getActiveWindow().getControl().setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
            }
         };
         worker.start();

      }
   };

   /**
    * @param dataSource The dataSource to set.
    */
   public void setDataSource(DataSource dataSource)
   {
      this.dataSource = dataSource;
   }

   private class SqlTableModel extends AbstractTableModel
   {
      private int columnCount;

      private String[] columnNames;

      private List data = new ArrayList();

      public SqlTableModel(ResultSetMetaData metaData) throws SQLException
      {
         columnCount = metaData.getColumnCount();
         columnNames = new String[columnCount];
         for (int i = 0; i < columnCount; i++)
         {
            columnNames[i] = metaData.getColumnLabel(i + 1);
         }
      }

      public String getColumnName(int column)
      {
         return columnNames[column];
      }

      public int getColumnCount()
      {
         return columnCount;
      }

      public int getRowCount()
      {
         return data.size();
      }

      public Object getValueAt(int row, int column)
      {
         return ((Object[]) data.get(row))[column];
      }

      public List getData()
      {
         return data;
      }
   }
}

[ge0ffrey]

I haven't decided yet, but as I have relatively few data, I 'll probably go with xstream.codehaus.org to a file in the user dir. If it becomes more, I 'll quickly switch to hibernate-annotations/EJB3 with HSQLDB to a file in my user dir (like I am now doing on my server part with mysql).

[siberian]

Just my 2 cents.
I will use hibernate annotations / HSQL. Springframework supports them well. You only need few codes. And HSQL won't eat much disk space and memory. Yes, it may be overkill. But overkill doesn't necessary mean bad.

[ge0ffrey]

Only problem I see with that approuch is when version 1.1 comes out:
It's possible to write and deploy migrate scripts on your server for 1.0 to 1.1, but to migratie you user settings from 1.0 to 1.1 in your user-home's database might be a problem.