JaVa
   

A Sample Windowed Database app

Now let's look at how we can use both inserting and retrieving data in a real windowed app. What we are going to create is a simple app 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 app that we can expand upon. Here is the basic code that we require to create an app frame:

Code Listing 16-4: The skeleton app 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();
 }
}


Java End example

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

Java ScreenShot
Screenshot-5: Our skeleton app

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 app, 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 app 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
}


Java End example

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

Java ScreenShot
Screenshot-6: Our skeleton app with GUI objects in place

If you are unsure about how to use the GUI in Java, please see , 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 app, 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 app 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:

Java Click To expand
Screenshot-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;
}


Java End example

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
}


Java End example

When we compile and execute the complete app, 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.

Java ScreenShot
Screenshot-8: The final Highscore app

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 app. 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 app 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.

JaVa
   
Comments