A Sample Windowed Database Application

Now let's look at how we can use both inserting and retrieving data in a real windowed application. What we are going to create is a simple application that lists the top five high scores and allows users to input their name and the score they attained. First let's create a skeleton application that we can expand upon. Here is the basic code that we require to create an application frame:

Code Listing 16-4: The skeleton application frame

import javax.swing.*;     public class Highscore extends JFrame {     public Highscore()     {         super("Highscore Example");         setBounds(0, 0, 300, 300);         setDefaultCloseOperation(EXIT_ON_CLOSE);         setResizable(false);         getContentPane().setLayout(null);                        // We will add our GUI objects here...                 setVisible(true);     }         public static void main(String[] args)     {         Highscore mainApp = new Highscore();     } }

When we execute this code, we will see that it creates a simple application frame, which can be seen in the following figure.

Figure 16-5: Our skeleton application

Next we need to add some GUI objects to allow the user to view the top five high scores in a list and also some input boxes to allow the user to enter his or her name and score and add the data to the database.

Let's now add a GUI table to contain the high-score list, two labels, two input boxes, and a button to allow a user to add a score. The GUI code is going to be added to the constructor of our application, and the full source so far can be seen in the following code listing. The new code is marked by comments in the code.

Code Listing 16-5: The skeleton application frame with GUI objects

import java.awt.*; import javax.swing.*; import javax.swing.table.*;     public class Highscore extends JFrame {     public Highscore()     {         super("Highscore Example");         setBounds(0, 0, 300, 300);         setDefaultCloseOperation(EXIT_ON_CLOSE);         setResizable(false);         getContentPane().setLayout(null);                        // NEW ->         scoreTable = new JTable(5, 2);         scoreTable.setEnabled(false);         scoreTable.setBounds(20, 20, 250, 80);                 nameLabel = new JLabel("Enter Your Name:");         nameLabel.setLocation(10, 140);         nameLabel.setSize(nameLabel.getPreferredSize());                 scoreLabel = new JLabel("Enter Your Score:");         scoreLabel.setLocation(10, 170);         scoreLabel.setSize(scoreLabel.getPreferredSize());                 nameField = new JTextField();         nameField.setBounds(120, 140, 140, 20);                 scoreField = new JTextField();         scoreField.setBounds(120, 170, 140, 20);                 submitButton = new JButton("Submit your Score!");         submitButton.setBounds(20, 220, 250, 30);             Container content = getContentPane();             content.add(scoreTable);         content.add(nameLabel);         content.add(scoreLabel);         content.add(nameField);         content.add(scoreField);         content.add(submitButton);         // <- NEW                 setVisible(true);     }         public static void main(String[] args)     {         Highscore mainApp = new Highscore();     }            // NEW ->     JTable       scoreTable;    // List box to hold the high scores     JLabel       nameLabel;     // Label for the player's name     JLabel       scoreLabel;    // Label for the player's score     JTextField   nameField;     // Field for inputting player's name     JTextField   scoreField;    // Field for inputting player's score     JButton      submitButton;  // Button for submitting a score     // <- NEW }

When we then execute this code, we can see that our five GUI objects are now visible in the application frame.

Figure 16-6: Our skeleton application with GUI objects in place

If you are unsure about how to use the GUI in Java, please see Chapter 13, which explains the Java GUI in detail, and the bonus GUI chapter available on the companion CD-ROM.

Okay, so now that we have our basic application, let's create a database in which we can then store the players' scores. Once we have the database ready, we can then add code to the application to allow it to interact with the database.

Now open up the MySQL console client and execute the following set of statements:

mysql> CREATE DATABASE highscore;     mysql> USE highscore;     mysql> CREATE TABLE scoredata (     -> name TEXT,     -> score INT);

Execute the DESCRIBE command in the MySQL console to ensure that our table has been created as we planned. Here is the statement that we require to do this:

mysql> DESCRIBE scoredata;

Tip 

If you have an artistic slant, it may be that you are slightly lazier than your average programmer. If so, you may want to use the shortened keyword DESC to describe your tables.

The following figure is a screen shot of the expected output from the MySQL console:

Figure 16-7: Our scoredata table in our highscore database

Let's go back to Java and add a method to our Highscore class that will retrieve the top five high scores from the database and insert them into our JTable object. To allow the table to be filled with data, we need to create a TableHandler class to control how the data is added into the table. Let's look at the complete code listing for this class, and then we will break it down to see how it works.

Code Listing 16-6: The TableHandler class

import java.sql.*; import javax.swing.table.*; import java.util.*;     class TableHandler extends AbstractTableModel {     public void updateTable()     {         try         {             Connection conn;                                          conn = DriverManager.getConnection("jdbc:mysql://                 localhost/highscore?user=root&password=");                         Statement myStatement = conn.createStatement();                      ResultSet rs = myStatement.executeQuery("SELECT * FROM                 scoredata ORDER BY score DESC LIMIT "+MAX_ROWS);                           int row = 0;             while(rs.next())             {                 list[row][0] = rs.getString("name");                 list[row][1] = rs.getString("score");                 row++;             }                         conn.close();                         // Tell the table there is new data so it can update             // itself...             fireTableDataChanged();         }         catch(SQLException e)         {             System.out.println(e);           }     }         public int getColumnCount()     {         return MAX_COLUMNS;     }         public int getRowCount()     {         return MAX_ROWS;     }        public Object getValueAt(int row, int column)     {         return list[row][column];     }            String[][] list = new String[MAX_ROWS][MAX_COLUMNS];         static final int MAX_ROWS = 5;     static final int MAX_COLUMNS = 2; }

The TableHandler class extends the AbstractTableModel class that allows it to specify the data added to a table. The first method that we have implemented, updateTable, is not actually part of the AbstractTableModel class, but it does access methods from it (which are implemented below the updateTable method).

The updateTable method first creates a connection to the database and then executes the following SQL query:

SELECT * FROM scoredata ORDER BY score DESC LIMIT 5

This selects all of the data from the scoredata table in descending order using the score field to order it. Note that ordering is done after the filter (*) and where options. Note that the "LIMIT 5" on the end of the statement means that the database will only return the first five results it finds matching our query.

Next we cycle through the ResultSet and add each of the results to a two-dimensional string array used to store the row and column information for the table. Here is the code fragment that does this for us:

    int row = 0; while(rs.next()) {     list[row][0] = rs.getString("name");     list[row][1] = rs.getString("score");     row++; }

Finally, we call a method that is part of the AbstractTableModel called fireTableDataChanged so that the table knows that its data has been changed and it refreshes itself.

This leads us nicely into the getValueAt method that we have implemented in this class. This method is used by the table to read in the values for each of the rows and columns in our actual JTable. Note that all this function does is return string values from our two-dimensional string array list (which we filled with values from the database in the updateTable method). Hence, we can define our own table values in this method for the table to call internally to retrieve the table data.

The other two methods are used to tell the table the number of rows and columns it currently contains. Hence, if we stored row data in a linked list, we could return the size of the list in the getRowCount method and then handle obtaining the row data in the getValueAt method accordingly.

Now we need to integrate the TableHandler class into our main Highscore class. Here is the complete source listing for the Highscore class with the TableHandler implemented. We have also made it so that you can click the button to add your score into the database.

Code Listing 16-7: The final Highscore class

import java.awt.*; import java.awt.event.*;    // NEW import javax.swing.*; import java.sql.*;     public class Highscore extends JFrame implements ActionListener  // NEW {     public Highscore()     {         super("Highscore Example");         setBounds(0, 0, 300, 300);         setDefaultCloseOperation(EXIT_ON_CLOSE);         setResizable(false);         getContentPane().setLayout(null);                   // NEW ->         tableHandler = new TableHandler();         tableHandler.updateTable();         // <- NEW                        scoreTable = new JTable(tableHandler); // new constructor         scoreTable.setBounds(20, 20, 250, 80);          scoreTable.setEnabled(false);                 nameLabel = new JLabel("Enter Your Name:");         nameLabel.setLocation(10, 140);         nameLabel.setSize(nameLabel.getPreferredSize());                 scoreLabel = new JLabel("Enter Your Score:");         scoreLabel.setLocation(10, 170);         scoreLabel.setSize(scoreLabel.getPreferredSize());                 nameField = new JTextField();         nameField.setBounds(120, 140, 140, 20);                 scoreField = new JTextField();         scoreField.setBounds(120, 170, 140, 20);                 submitButton = new JButton("Submit your Score!");         submitButton.setBounds(20, 220, 250, 30);                 submitButton.addActionListener(this);  // NEW                 Container content = getContentPane();                 content.add(scoreTable);         content.add(nameLabel);         content.add(scoreLabel);         content.add(nameField);         content.add(scoreField);         content.add(submitButton);                 setVisible(true);     }         // NEW ->     public void actionPerformed(ActionEvent e)     {         if(e.getSource() == submitButton)         {             // Check the fields contain values             String name = nameField.getText();             String score = scoreField.getText();                             if(name.length()>0 && score.length()>0)             {                 // Insert the score into the database                 try                 {                     Connection conn;                                                                      conn = DriverManager.getConnection(                         "jdbc:mysql://localhost/highscore?                         user=root&password=");                                             Statement myStatement = conn.createStatement();                                             myStatement.executeUpdate("INSERT INTO scoredata                         VALUES ('"+name+"',"+score+")");                                             conn.close();                 }                 catch(SQLException ex)                 {                     System.out.println(ex);                 }                                       // Finally, refresh the highscore table and blank                  // the fields                 tableHandler.updateTable();             }         }     }     // <- NEW             public static void main(String[] args)     {         // NEW ->         try         {             Class.forName("org.gjt.mm.mysql.Driver");         }         catch(ClassNotFoundException e)         {             System.out.println(e);         }         // <- NEW                         Highscore mainApp = new Highscore();     }          JTable       scoreTable;    // List box to hold the high scores     JLabel       nameLabel;     // Label for the player's name     JLabel       scoreLabel;    // Label for the player's score     JTextField   nameField;     // Field for inputting player's name     JTextField   scoreField;    // Field for inputting player's score     JButton      submitButton;  // Button for submitting a score         // NEW ->     TableHandler tableHandler;     // <- NEW }

When we compile and execute the complete application, we can see that when we enter scores and submit them, the five highest scores are shown. The following figure is a screen shot with some sample scores in it.

Figure 16-8: The final Highscore application

We first added a call to the static Class.forName method, which (as we saw before) initializes the MySQL driver class that is used by the JDBC.

Let's have a look now at how we integrated the TableHandler. First we created a TableHandler object and assigned it as the "model" for the table by passing it as a parameter to the JTable constructor. This was accomplished with the following code segment:

// NEW ->         tableHandler = new TableHandler();         tableHandler.updateTable();         // <- NEW                        scoreTable = new JTable(tableHandler); // new constructor 

Notice how we also call the updateTable method of the TableHandler here. This will update our JTable object with the data from the database when we start our application. We also call this function again when the user presses the button to submit their scores.

We have attached an ActionListener to the submit button, so that any time the button is clicked it will invoke the actionPerformed method. When the button is clicked, the application checks to ensure that there is data in both the text fields, and then it inserts the score into the database using the following code:

myStatement.executeUpdate("INSERT INTO scoredata VALUES     ('"+name+"',"+score+")");

Note that after the data has been inserted, the updateTable function is called so the scores are relisted in the correct order again, taking into account the new score that has just been added into the database.