[RESOLVED] Convert to using a list instead of the model

Hi,

I need to know how to convert project to use a list of strings to populate the table without using the table model to populate the table with the data. This is my attempt at trying to make the project work without the table model but as you can see it has tons of errors.


Files in the DAO:

PencilDAO:

java Code:

package DAO;
 
import model.Pencil;
import model.*;
import java.sql.*;
import java.util.*;
 
/**
 * Data Access Class for the Pencil database
 */
public class PencilDAO extends PencilsApplicationDAO{
 
  /**
   * The Prepared statement used for selecting Pencils
   */
  private static PreparedStatement selectAllPreparedStatement = null;
 
 
  /**
   * Retrieves all book records  from the tblPencils
   * table in a database as a List of Pencil objects. Will create the
   * selectAllPreparedStatement object only once (the first time the method
   * is called, ie when selectAllPreparedStatement==null)
   * @return A list of Pencil objects which are all of the records from the table
   *         tblPencils.
   *         Will be null if any error occurs (eg table does not exist)
   */
  public static List<Pencil> retrieveAllColourPencils() {
    List<Pencil> results = null;
    String SQLStatementStr = 
            "SELECT colour, size "+
            "FROM tblPencils " +
            "ORDER BY colour, size";
//            "SELECT colourKey,length,colourName "+
//            "FROM tblPencil,tblColour " +
//            "WHERE tblColour.colourKey=tblColour.colourKey "+
//            "ORDER BY colourKey";
 
    ResultSet rs;
    try {
      if (selectAllPreparedStatement == null) {
        selectAllPreparedStatement = con.prepareStatement(SQLStatementStr);
      }
      rs = selectAllPreparedStatement.executeQuery();
      results = new ArrayList<Pencil>();
      while (rs.next()) {
        Pencil record;
        record = new Pencil(rs.getString("colour"), rs.getInt("size"));
        results.add(record);
      }
    } catch (SQLException ex) {
      results = null;
    }
    return results;
  }
}

PencilsApplicationDAO:

java Code:

package DAO;
 
import model.Pencil;
import java.sql.*;
import java.util.*;
import model.*;
 
/**
 * General database access routines
 */
public class PencilsApplicationDAO {
  /**
   * The Prepared statement used for selecting Pencils for
   * a particular colour
   */
  private static PreparedStatement selectColourPreparedStatement = null;
 
  /**
   * The database for the Pencil Application
   */
  public static final String DB_NAME= "5IJA_DB";
  /**
   * The connection to the database
   */
  protected static Connection con=null;
  /**
   * Connects to the database assuming a suitable driver is available
   * Will throw an exception if an error occurs. Otherwise a connection is
   * established and the con class variable is ready to be used.
   * @throws SQLException
   */
  public static void connectToDatabase() throws SQLException {
 
//    con = DriverManager.getConnection("jdbc:odbc:" + DB_NAME);
 
     con = DriverManager.getConnection("jdbc:mysql://localhost:3306/"+DB_NAME , "root", "password");
  }
 
  /**
   * Getter for connection object if needed
   * @return the Connection object
   */
  public static Connection getConnection() {
    return con;
  }
 
    /**
   * Retrieves all pencil records of a particular colour from the tblPencils
   * table in a database as a List of Pencil objects. Will create the
   * selectColourPreparedStatement object only once (the first time the method
   * is called, ie when selectColourPreparedStatement==null)
   * @param colour The colour of the pencils to retrieve
   * @return A list of Pencil objects which are all of the records from the table
   *         tblPencils with the colour as per the provided parameter.
   *         Will be null if any error occurs (eg table does not exist)
   */
  public static List<Pencil> retrievePencils(String colour) {
    List<Pencil> results = null;
    String SQLStatementStr =
            "SELECT colour, size "+
            "FROM tblPencils " +
            "WHERE colour = ? "+
            "ORDER BY colour";
 
    ResultSet rs;
    try {
      if (selectColourPreparedStatement  == null) {
        selectColourPreparedStatement = con.prepareStatement(SQLStatementStr);
      }
      selectColourPreparedStatement .setString(1, colour);
      rs = selectColourPreparedStatement.executeQuery();
      results = new ArrayList<Pencil>();
      while (rs.next()) {
        Pencil record;
        record = new Pencil(rs.getString("colour"), rs.getInt("size"));
        results.add(record);
      }
    } catch (SQLException ex) {
      results = null;
    }
    return results;
  }
}

Files in the Controller:

PencilApplication:

java Code:

package controller;
 
import DAO.*;
import view.*;
import model.*;
import java.sql.*;
import java.util.*;
 
/**
 * Displays and controls a simple view of a book table
 */
public class PencilApplication {
 
  private PencilMainViewFrame mainFrame;
  private List<Pencil> data;
  private List<String> colourList;
 
  public PencilApplication() {
    // Create the view letting it know the controller
    mainFrame = new PencilMainViewFrame(this);
    mainFrame.setVisible(true);
 
    // Connect to DB. If this fails the view will need to display a
    // message and program will abort
    connectToDatabase();
 
    // Setup the model for the distinct publishers list box
    List<String> colourList;
    colourList = PencilsApplicationDAO.retrieveDistinctColours();
    if (colourList == null) {
      mainFrame.displayDatabaseUnExpectedError("Unable to Retrieve colour of pencils!");
    } else {
      mainFrame.displaynewListOfColours(colourList);
    }
    // Initial data of books is null until user makes a choice. This 'null' based
    // model will at least allow the JTable column headings to be visible.
    data = null;
    //mainFrame.displaynewListOfColours;
  }
 
  /**
   * Uses the DAO to connect to the database.
   * Will display a popup message dialogue if the connection fails and then
   * exit the application. Otherwise a connection is established and the instance
   * variable is ready to be used.
   */
  private void connectToDatabase() {
    try {
      PencilsApplicationDAO.connectToDatabase();
    } catch (SQLException ex) {
      mainFrame.displayDatabaseConnectionError();
      System.exit(-1);
    }
  }
 
  /**
   * User (through view) has requested display of Books of a particular
   * pubName. These will be retrieved from the table using the DAO methods. Then
   * the view will be asked to update itself using the new BookJTableModel or
   * display an error if something goes wrong.
   * @param pubName The pubName of the books to be displayed
   */
  public void displaycolourpencils(String colour) {
    data = PencilDAO.retrievePencils(colour);
    if (data == null) {
      mainFrame.displayDatabaseUnExpectedError("Unable to Retrieve colour of pencils " + colour);
    } else {
      this.mainFrame.displayNewPencilData(data);
    }
  }
 
  /**
   * Get everything going
   * @param args Not used
   */
  public static void main(String args[]) {
    new PencilApplication();
  }
}

Thanks,


Nightwalker

Files in the Model:

ColourComboBoxModel:

java Code:

package model;
 
import java.util.*;
import javax.swing.*;
 
/**
 * A model for the colours JList
 *
 */
public class ColourComboBoxModel extends AbstractListModel implements ComboBoxModel {
 
  /**
   * The list of colours
   */
  private List<String> data;
  private String selectedItem = "Make a Choice";
 
  /**
   * Initialise the data to the provided list
   * @param data The list of colours
   */
  public ColourComboBoxModel(List<String> data) {
    this.data = data;
  }
 
  /**
   *
   * @return The number of colours to display
   */
  public int getSize() {
    if (data != null) {
      return data.size();
    } else {
      return 0;
    }
  }
 
  /**
   *
   * @param index The position within the list
   * @return the colours to display at that position. This would be taken from
   *         the data list
   */
  public Object getElementAt(int index) {
  
    if (data == null) {
      return "";
    }
 
      String cellData;
    cellData = data.get(index);
    return cellData;
  }
  public void setSelectedItem(Object anItem) {
    this.selectedItem = (String) anItem;
  }
 
  public Object getSelectedItem() {
    return this.selectedItem;
  }
 
  public void setData(List<String> colourList) {
    this.data = colourList;
    fireContentsChanged(this, 0, 0);
  }
}

Pencil:

java Code:

package model;
 
public class Pencil {
 
  public static final String DEF_COLOUR = "RED";
  public static final int DEF_LENGTH = 0;
  private String colour;
  private int length;
 
  public Pencil() {
    this(DEF_COLOUR, DEF_LENGTH);
  }
 
  public Pencil(String colour, int length) {
    this.colour = colour.toUpperCase();
    this.length = length;
  }
 
  /**
   * Get the value of length
   *
   * @return the value of length
   */
  public int getLength() {
    return length;
  }
 
  public void setLength(int length) {
    this.length = length;
  }
 
  public String getColour() {
    return colour;
  }
 
  public void setColour(String colour) {
    this.colour = colour.toUpperCase();
  }
 
  public String toString() {
    return super.toString() + "[" +
            "colour=" + this.colour +
            ",length=" + this.length +
            "]";
  }
 
}

PencilJTableModel:

java Code:

package model;
 
import java.util.*;
import javax.swing.table.*;
 
/**
 * A model for the Pencil JTable
 * 
 */
public class PencilJTableModel extends AbstractTableModel {
 
  /**
   * The list of Pencil objects used for the JTable display
   */
  private String[] columnNames = new String[]{"Colour", "Length"};
  private List<Pencil> data;
 
  public PencilJTableModel() {
    this(null);
  }
 
  public PencilJTableModel(List<Pencil> data) {
    this.data = data;
  }
 
  /**
   * 
   * @return The number of rows to be displayed which would be the size of the 
   * data list.
   */
  public int getRowCount() {
    if (data != null) {
      return data.size();
    } else {
      return 0;
    }
  }
 
  /**
   *
   * @return The number of columns to display 
   */
  public int getColumnCount() {
    return columnNames.length;
  }
 
  /**
   * Returns the value for the cell at rowIndex and columnIndex.
   * Extracts the Pencil object at index rowIndex from the list data then
   * extracts the Pencil data from that object according to the names in
   * colNames
   *
   * @param rowIndex    the rowIndex whose value is to be queried
   * @param columnIndex     the columnIndex whose value is to be queried
   * @return    the value Object at the specified cell
   */
  public Object getValueAt(int rowIndex, int columnIndex) {
    Pencil record;
    Object cellData = null;
    String colName;
 
    if (data == null) {
      return "";
    }
 
    record = data.get(rowIndex);
 
 
    switch (columnIndex) {
      case 0:
        cellData = record.getColour();
        break;
      case 1:
        cellData = record.getLength();
        break;
      default:
        cellData = "unkown";
        break;
    }
 
    return cellData;
  }
 
  /**
   *  Returns the column names for the table Pencil column 0 is Colour and
   *  column 1 is Length.
   *  If column is not 0 or 1 will return a null String.
   *
   * @param column  the column being queried
   * @return a string containing the  name of column
   */
  public String getColumnName(int column) {
    return columnNames[column];
  }
 
      public void setData(List<Pencil> data) {
       this.data = data;
       fireTableDataChanged();
    }
}

In "Test" folder:

SetupPenilData.java

java Code:

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package tests;
 
import DAO.PencilsApplicationDAO;
import DAO.PencilDAO;
import java.sql.*;
 
/**
 *
 */
public class SetupPencilData {
 
  public static void main(String[] args) throws SQLException {
    String sqlSt;
 
    try {
      PencilsApplicationDAO.connectToDatabase();
    } catch (SQLException ex) {
      System.out.println("UNABLE to connect to database.");
      System.exit(-1);
    }
 
    Connection con = PencilDAO.getConnection();
    Statement s = null;
    s = con.createStatement();
 
    // Create tables
    try {
      sqlSt = "DROP TABLE tblPencils";
      s.executeUpdate(sqlSt);
    } catch (SQLException ex) {
      if (ex.getMessage().contains("Unknown table") || ex.getMessage().contains("does not exist")) {
        System.out.println("code="+ex.getErrorCode());
        System.out.println("TABLE tblPencils does not exist but will be created.");
      } else {
        ex.printStackTrace();
      }
    }
 
    System.out.println("CREATING TABLES...");
    sqlSt = "CREATE TABLE tblPencils (colour VARCHAR(50), size INT)";
    s.executeUpdate(sqlSt);
 
    ResultSet rs;
    int colourKey;
    System.out.println("INSERTING pencil records...");
    //INSERT Pencils Green
    sqlSt = "INSERT into tblPencils values ('GREEN',10), ('GREEN',10), ('BLUE',20), ('RED',15), ('GREEN',10), ('BLACK',8)";
    s.executeUpdate(sqlSt);
 
    con.close();
  }
}

Files in the View:

PencilJTableViewPanel.java

java Code:

package view;
 
import model.PencilJTableModel;
import java.awt.BorderLayout;
import javax.swing.*;
import model.Pencil;
import java.util.List;
/**
 * The JPanel for the JTable
 */
public class PencilJTableViewPanel extends JPanel {
 
  private JTable dataTable;
  private JScrollPane scrollPane;
 
  /**
   * Cretae the Panel for the JTable. Placing the JTable in a JScrollPane
   */
  public PencilJTableViewPanel() {
 
    dataTable = new JTable();
    dataTable.setModel(new PencilJTableModel());
    scrollPane = new JScrollPane(dataTable);
    setLayout(new BorderLayout());
    add(scrollPane, BorderLayout.CENTER);
 
  }
    /**
   *
   * @return the model currently being used by the JTable
   */
  public PencilJTableModel getModel() {
    return (PencilJTableModel) (dataTable.getModel());
  }
 
  /**
   * Change the data used by the JTable
   * @param model The new data for the JTable
   */
  public void setModel(PencilJTableModel model) {
    dataTable.setModel(model);
  }
 
    /**
   * Change the data used by the JTable
   * @param model The new data for the JTable
   */
  public void setData(List<Pencil> data) {
    this.getModel().setData(data);
  }
 
}

PencilMainViewFrame.java:

java Code:

package view;
 
import controller.*;
import java.awt.*;
import java.awt.event.*;
import javax.swing.*;
import model.*;
 
/**
 * Displays a simple GUI allowing user to view a table of pencils of a
 * chosen colour
 */
public class PencilMainViewFrame extends JFrame {
 
  private JLabel lblColours;
  private JComboBox lstColours;
  private JPanel panButtons;
  private PencilJTableViewPanel panData;
  private PencilApplication theController;
 
  /**
   * Create the view Frame for this application
   * @param theController Logic will be handled by this controller
   * @param lstModel The data for for the JList
   * @param tabModel The data for the JTable
   */
  public PencilMainViewFrame(PencilApplication theController) {
    super("COLOUR PENCILS");
 
    this.theController = theController;
 
    this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
 
    lstColours = new JComboBox();
    lstColours.setModel(new ColourComboBoxModel(null));
    lblColours = new JLabel("Choose a colour: ");
 
    //Setup Button panel
    panButtons = new JPanel(new FlowLayout(FlowLayout.CENTER));
    panButtons.add(lblColours);
    panButtons.add(lstColours);
    panButtons.setBorder(BorderFactory.createLineBorder(Color.BLACK));
 
    // Setup books data panel
    panData = new PencilJTableViewPanel();
 
    //Add panels to frame
    add(panButtons, BorderLayout.SOUTH);
    add(panData, BorderLayout.CENTER);
 
    // Register event handlers
    lstColours.addActionListener(new ComboPublisherEventHandler());
 
    //Finish off
    setSize(600, 250);
    setVisible(true);
  }
 
  /**
   * When the controller chooses to this will display new Book data in the JTable
   * @param bookJTableModel The new data for the JTable
   */
  public void displayNewPencilData(java.util.List<Pencil> data) {
    panData.setData(data);
  }
 
  /**
   * When the controller chooses to this will display an error connecting to database message
   * @param bookJTableModel The new data for the JTable
   */
  public void displayDatabaseConnectionError() {
    JOptionPane.showMessageDialog(this, "UNABLE TO CONNECT TO DATABASE");
  }
 
  /**
   * When the controller chooses to this will display an error connecting to database message
   * @param bookJTableModel The new data for the JTable
   */
  public void displayDatabaseUnExpectedError(String message) {
    JOptionPane.showMessageDialog(this, "UNEXPECTED DATABASE ERROR HAS OCCURED\n" + message);
  }
 
  /**
   * Change the data used by the JComboBox
   * @param model The new data for the JComboBox
   */
  public void displaynewListOfColours(java.util.List<String> colours) {
    ((ColourComboBoxModel)lstColours.getModel()).setData(colours);
  }
 
  /**
   * Inner class eventHandler for the JComboBox of Colours
   */
  class ComboPublisherEventHandler implements ActionListener {
 
    /**
     * Responds to a new publisher being chosen from the JList
     * @param e Details of the event
     */
    public void actionPerformed(ActionEvent e) {
      String chosenColour;
 
      chosenColour = (String) (lstColours.getModel().getSelectedItem());
      theController.displaycolourpencils(chosenColour);
    }
  }
}

Use my attempt at the above exercise I completed it by changing the following:

1. In the retrieveDistinctColours() method, since there is no question mark in the sql statement you don't need (can't use) the setString() method. This will crash it.

2. In the displaycolourpencils() method in PencilApplication.java, you're calling retrieveAllColourPencils() - you should be calling the retrievePencils method instead.

3. In the DAO using the variable selectColourPreparedStatement in two methods retrievePencils and retrieveDistinctColours. You need to have two Prepared Statements (one for each method)

4. In my database I had colour and length.