public static Connection getConnection() throws SQLException {
   if(conn == null || conn.isClosed()) {
      conn = DriverManager.getConnection(
         "jdbc:derby:MyDB;create=true",
         "user", "password");
   }
   return conn;
}
public void close() {
   try {
     conn.close();
     DriverManager.getConnection("jdbc:derby:;shutdown=true");
   } catch (SQLException ex) {}
}

Data Models and Data Access Models
As shown before with the creation of tables, we want to be able to choose two different classes
of data. First, we have the albums, information for which can be chosen from the albums table;
and second, we have the genres, found within the genres table. To assist us, we create a data
model (see Listing 13-3 and Listing 13-4). The data model consists of the classes Album and
Genre, which provide relevant setters and getters. Note that no persistence logic is found in
these classes. We will put this logic in a separate class.

public class Album {
   private int id = 0;
   private String title;
   private String tracks;
   private String cds;
   private String year;
   private Genre genre;
   public Album(
      int id, String title, String tracks, String cds, String year, Genre genre) {
      this.id = id;
      this.title = title;
      this.tracks = tracks;
      this.cds = cds;
      this.year = year;
      this.genre = genre;
   }
   public int getId() {
      return id;
   }
   public String getTitle() {
      return title;
   }
   ...
}

The data model for an Album in the Genre class requires overwriting both the toString()
and equals() methods. This is necessary for the correct representation and selection of a genre
in the dialog for creating a new album.

public class Genre {
   private int id = 0;
   private String genre;
   public Genre(int id, String genre) {
      this.id = id;
      this.genre = genre;
   }
   public int getId() {
      return id;
   }
   public String getGenre() {
      return genre;
   }
   public String toString() {
      return genre;
   }
   public boolean equals(Object obj) {
      if(obj instanceof Genre) {
         if(((Genre)obj).getId() == id) {
            return true;
         }
      }
      return false;
   }
}



To let the data model and business logic (which in this case is primarily the user interface
for selecting data) be loosely coupled to the persistence layer, we encapsulate access to the
database and SQL statements in a separate class named DataModel (see Listing 13-5). This class
performs desired changes and requests to the database, while also providing the data via the
Album and Genre data models.
The methods getAlbums() and getGenres(), implemented in the DataModel class, provide
vectors containing the chosen data. We also have the methods insertAlbum(), updateAlbum(),
and deleteAlbum(), with which we enter albums into the database and also use for changing
and deleting them.
Listing 13-5. The DataModel class encapsulates the access to Java DB and makes data available
via the related Album and Genre data models.



public class DataModel {
   public static Vector<Album> getAlbums() {
      Vector<Album> albums = new Vector<Album>();
      try {
         Statement stmt = Installer.getConnection().createStatement();
         ResultSet rs = stmt.executeQuery("SELECT * FROM albums"+
            " INNER JOIN genres ON albums.genre = genres.id");
         while(rs.next()) {
            albums.add(new Album(rs.getInt(1), rs.getString(2),
               rs.getString(3), rs.getString(4), rs.getString(5),
               new Genre(rs.getInt(7), rs.getString(8))));
         }
         rs.close();
         stmt.close();
      } catch(SQLException e) {
         Exceptions.printStackTrace(e);
      }
      return albums;
   }
   public static Vector<Genre> getGenres() {
      Vector<Genre> genres = new Vector<Genre>();
      try {
         Statement stmt = Installer.getConnection().createStatement();
         ResultSet rs = stmt.executeQuery("SELECT * FROM genres");
         while(rs.next()) {
            genres.add(new Genre(rs.getInt(1), rs.getString(2)));
         }
         rs.close();
         stmt.close();
      } catch(Exception e) {
         e.printStackTrace();
      }
      return genres;
   }
   public static void updateAlbum(Album a) throws SQLException {
      PreparedStatement stmt = Installer.getConnection().prepareStatement(
         "UPDATE albums SET title=?, tracks=?, cds=?, years=?, genre=? WHERE id=?");
      stmt.setString(1, a.getTitle());
      stmt.setString(2, a.getTracks());
      stmt.setString(3, a.getCDs());
      stmt.setString(4, a.getYear());
      stmt.setInt(5, a.getGenre().getId());
      stmt.setInt(6, a.getId());
      stmt.execute();
   }
   public static void insertAlbum(Album a) throws SQLException {
      PreparedStatement stmt =Installer.getConnection().prepareStatement(
         "INSERT INTO albums (title, tracks, cds, years, genre) VALUES(?,?,?,?,?)");
      stmt.setString(1, a.getTitle());
      stmt.setString(2, a.getTracks());
      stmt.setString(3, a.getCDs());
      stmt.setString(4, a.getYear());
      stmt.setInt(5, a.getGenre().getId());
      stmt.execute();
   }
   public static void deleteAlbum(Album a) throws SQLException {
      PreparedStatement stmt = Installer.getConnection().prepareStatement(
            "DELETE FROM albums WHERE id = ?");
      stmt.setInt(1, a.getId());
      stmt.execute();
   }
}




Displaying and Working with the Data
We now come to components that will display the data, allowing the user to create and edit
music albums. We’ll list the albums in a table within a TopComponent (see Figure 13-3). We begin
by creating the AlbumsTopComponent class, containing a JTable. To enable the table to display
the DataModel of our album, we need a model for the table.
Since the DataModel is only available to this class, we implement it as a private inner class
named AlbumTableModel (see Listing 13-6). The data is obtained from a vector of the type Album.
Since we later need access to the model, we create it as a private data element. We connect the
DataModel with the table via the setModel() method. Typically, table entries can be edited or
viewed via a double-click of the mouse. To create this functionality, we register a MouseListener
or a MouseAdapter with the JTable, which calls the editAlbumActionPerformed() method on
double-click. This will be discussed next.
Figure 13-3. Displaying the database entries in a table
Listing 13-6. TopComponent implementation with AlbumTableModel



final class AlbumsTopComponent extends TopComponent {
   private JTable albums;
   private AlbumTableModel model = new AlbumTableModel();
   private AlbumsTopComponent() {
      initComponents();
      albums.setModel(model);
      albums.addMouseListener(new MouseAdapter() {
         public void mouseClicked(MouseEvent event) {
            if(event.getClickCount() == 2) {
               editAlbumActionPerformed(null);
            }
         }
      });
   }
   private static final class AlbumTableModel
      extends AbstractTableModel {
      private String[] columns = {"Title", "Tracks", "CDs", "Year"};
      private Vector<Album> data = new Vector<Album>();
      public Album getRow(int row) {
         return data.get(row);
      }
      public int getRowCount() {
         return data.size();
      }
      public int getColumnCount() {
         return columns.length;
      }
      public String getColumnName(int col) {
         return columns[col];
      }
      public Object getValueAt(int row, int col) {
         Album album = data.get(row);
         switch(col) {
            case 0: return album.getTitle();
            case 1: return album.getTracks();
            case 2: return album.getCDs();
            case 3: return album.getYear();
         }
         return "";
      }
      public Vector<Album> getData() {
         return data;
      }
}




As the TopComponent opens, we need to load and display the current entries from the database. For this reason, we override the method componentOpened(), where we use our data
access model DataModel, which abstracts access to the database to obtain all entries in the
database, via the getAlbums() method. We add these to the DataModel in the table and inform
the view, which is the JTable, via the fireTableDataChanged() method, that the data has
changed.
Finally, we implement three action methods that enable the user to add, edit, and delete
entries. For the creation of new albums, we have the newAlbumActionPerformed() method. We
use it to call a static method that opens a dialog where the user can enter the required data. We
create this dialog in the final step. If the method returns an Album instance, the dialog is immediately closed and the data is added to the database. If that code can be run without an
exception being thrown, we add the album to the table.




   public void componentOpened() {
      model.getData().addAll(DataModel.getAlbums());
      model.fireTableDataChanged();
   }
   private void newAlbumActionPerformed(ActionEvent evt) {
      Album album = AlbumEditDialog.newAlbum();
      if(album != null) {
         try {
            DataModel.insertAlbum(album);
            model.getData().add(album);
            model.fireTableDataChanged();
         } catch(SQLException e) {
            Exceptions.printStackTrace(e);
         }
      }
   }





The method editAlbumActionPerformed() is invoked by means of the Edit button or by a
double-click. Similar to the way new entries are created, we again call up a dialog. However, we
need editAlbum() for that purpose, to which we pass an Album instance, allowing data to be
edited in the dialog. The currently selected row in the table invokes the getSelectedRow()
method, with the returned value allowing related data to be found in the JTable’s data model.
The user can now change the data. If the OK button is clicked, the editAlbum() method is
called, which returns the changed Album instance (see Listing 13-7). The changes are saved in
the database with the updateAlbum() method.
Finally, we need to address situations where the user deletes an entry from the database.
That will be handled by the deleteAlbumActionPerformed() method. To prevent unintended
deletion, the user is asked to confirm that the entry should be deleted. The dialog that is
required for this functionality is created in a very simple way, via the NetBeans Dialogs API (see
Chapter 8). We use the NotifyDescriptor.Confirmation instance. We show the dialog via the
notify() method. Once the user has confirmed the deletion request, the entry is removed from
the database via the deleteAlbum() method. Only when the operation can be completed
successfully do we delete the album from the JTable and update with its current entries.
Listing 13-7. TopComponent for displaying and working with the albums in the database





   private void editAlbumActionPerformed(ActionEvent evt) {
      Album album = AlbumEditDialog.editAlbum(
              model.getRow(albums.getSelectedRow()));
      if(album != null) {
         try {
            DataModel.updateAlbum(album);
            model.fireTableDataChanged();
         } catch(SQLException e) {
            Exceptions.printStackTrace(e);
         }
      }
   }
   private void deleteAlbumActionPerformed(ActionEvent evt) {
      Album album = model.getRow(albums.getSelectedRow());
      NotifyDescriptor d = new NotifyDescriptor.Confirmation(
         "Are you sure you want delete the album " + album.getTitle(),
         "Confirm Album Deletion");
      if(DialogDisplayer.getDefault().notify(d) == NotifyDescriptor.YES_OPTION) {
         try {
            DataModel.deleteAlbum(album);
            model.getData().remove(album);
            model.fireTableDataChanged();
         } catch(SQLException e) {
            Exceptions.printStackTrace(e);
         }
      }
   }
}




Our last task is the creation of a dialog with which the data can be created and edited.
Again, we need to use the classes of the Dialogs API; so we need not create a complete dialog of
our own, but simply the panel with required fields (see Figure 13-4). We therefore create a
simple JPanel class, via File ➤ New File ➤ Java GUI Forms ➤ JPanel Form.
Figure 13-4. Dialog for working with entries
In the constructor of the panel, we load all the genres from the database and add them to
the combo box. Additionally, we require the methods newAlbum() and editAlbum(), which you
were introduced to in the previous section. To simplify things, implement these as static
methods (see Listing 13-8). These methods are therefore factories that are concerned with the
creation of the dialog. First, create an instance of the AlbumEditDialog class. Create a dialog
with the help of a DialogDescriptor, pass the recently created panel, and that’s everything
needed for creating a dialog. As per usual, we show the dialog via the notify() method.
As soon as the user clicks the OK button, we use the data to create an Album object and pass
it back to the user; otherwise, we simply return null and indicate an error. In the case of the
editAlbum() method, we take the same approach with creating the dialog. Simply fill the fields
with the values of the selected album. However, when the dialog is completed, don’t create a
new Album object; simply update the data via the relevant setters and pass the updated instance
back to the user.
Listing 13-8. Dialog for editing and creating new music albums







public class AlbumEditDialog extends Jpanel {
   private AlbumEditDialog() {
      initComponents();
      for(Genre g : DataModel.getGenres()) {
         genre.addItem(g);
      }
   }
   public static Album newAlbum() {
      AlbumEditDialog d = new AlbumEditDialog();
      DialogDescriptor desc = new DialogDescriptor(d, "New...");
      if(DialogDisplayer.getDefault().notify(desc) == DialogDescriptor.OK_OPTION) {
         Album album = new Album(0,
            d.title.getText(),
            d.tracks.getText(),
            d.cds.getText(),
            d.year.getText(),
            (Genre)d.genre.getModel().getSelectedItem());
         return album;
      } else {
         return null;
      }
   }
   public static Album editAlbum(Album album) {
      AlbumEditDialog d = new AlbumEditDialog();
      d.title.setText(album.getTitle());
      d.tracks.setText(album.getTracks());
      d.cds.setText(album.getCDs());
      d.year.setText(album.getYear());
      d.genre.getModel().setSelectedItem(album.getGenre());
      DialogDescriptor desc = new DialogDescriptor(d, "Edit...");
      if(DialogDisplayer.getDefault().notify(desc) == DialogDescriptor.OK_OPTION) {
         album.setTitle(d.title.getText());
         album.setTracks(d.tracks.getText());
         album.setCDs(d.cds.getText());
         album.setYear(d.year.getText());
         album.setGenre((Genre)d.genre.getModel().getSelectedItem());
         return album;
      } else {
         return null;
      }
   }
}



At this point, we have explained everything relating to data access and display of data
found within Java DB databases. We also looked at an example application, showing how to set
up and use Java DB within the NetBeans Platform.