What is the proper relationship between an AbstractTableModel and the actual SQL statements?

Question

What is the proper relationship, in code, between a table model and the actual database queries?

Inside the addRow() method in the table model, should I place a further call to my database class, which in turn inserts the row into the database? I've illustrated this in the below code snippets.

public class MainPanel extends JPanel
{
    ...

    public MainPanel()
    {

        personTableModel = new PersonTableModel();

        personTable = new JTable(personTableModel);
        ...

        insertButton = new JButton("Insert");
        insertButton.addActionListener(new ActionListener() {

            public void actionPerformed(ActionEvent e)
            {
                String name = nameTextBox.getText();
                String address = addressTextBox.getText();

                Object[] row = { name, address };

                personTableModel.addRow(row);  // <--- Add row to model
            }
        });    
    }
}

public class PersonTableModel extends AbstractTableModel
{
    private List<Object[]> data;
    private List<String> columnNames;

    PersonDB personDB = new PersonDB();
    ...

    public void addRow(Object[] row)
    {
        // insert row into 'data'

        personDB.addPerson(row);  // <---- Call the personDB database class
    }

    ...
}

public class PersonDB
{

    public PersonDB()
    {
        // establish database connection
    }

    public addPerson(Object[] row)
    {
        // code that creates a SQL statement based on row data
        // and inserts new row into database.
    }
    ...
}

Answer 1

Whether or not you should directly make an insert call depends on some aspects:

• Do you want other processes to access the data immediately?
• Do you fear that your program crashes and you lose important information?
• Can you ensure that any data persisted during addRow is meaningful (the program could terminate directly after the insert)?

Than of course it may be a good idea to directly insert the data into the backing Database.

You should however watch out, that there are two variants of addRow and two variants of insertRow. DefaultTableModel directs calls internally through insertRow(int, Vector), which would probably be the only function to overwrite, if you want to immediately persist data.

If you like the proposed idea of DTOs the examples below may help you.

The Idea is to represent "Entities" or table rows as classes in Java. A DTO is the simplest representation and normally only contains fields with respective getter and setter.

Entities can generically be persisted and loaded through ORM libraries like EclipseLink or Hibernate. Additionally for this table-application the use of DTOs provide a way of storing data not shown to the user in a clean and typed way.

DTO:

public class PersonDto {
    private Long id;
    private String name;
    private String street;

    public PersonDto() {
    }

    public PersonDto(Long id, String name, String street) {
        this.id = id;
        this.name = name;
        this.street = street;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getStreet() {
        return street;
    }

    public void setStreet(String street) {
        this.street = street;
    }

    public static class NameColumn extends DtoTableModel.ColumnProvider<PersonDto, String> {

        public NameColumn() {
            super("Name", String.class);
        }

        @Override
        public String getValue(PersonDto dto) {
            return dto.getName();
        }

        @Override
        public void setValue(PersonDto dto, Object value) {
            dto.setName((String) value);
        }
    }

    public static class StreetColumn extends DtoTableModel.ColumnProvider<PersonDto, String> {

        public StreetColumn() {
            super("Street", String.class);
        }

        @Override
        public String getValue(PersonDto dto) {
            return dto.getStreet();
        }

        @Override
        public void setValue(PersonDto dto, Object value) {
            dto.setStreet((String) value);
        }
    }
}

DTO based TableModel:

import javax.swing.table.AbstractTableModel;
import java.util.ArrayList;

public class DtoTableModel<T> extends AbstractTableModel {

    private final ArrayList<T>                    rows;
    private final ArrayList<ColumnProvider<T, ?>> columns;

    protected DtoTableModel() {
        rows = new ArrayList<T>();
        columns = new ArrayList<ColumnProvider<T, ?>>();
    }

    @Override
    public int getRowCount() {
        return rows.size();
    }

    @Override
    public int getColumnCount() {
        return columns.size();
    }

    @Override
    public Object getValueAt(int rowIndex, int columnIndex) {
        return columns.get(columnIndex).getValue(rows.get(rowIndex));
    }

    @Override
    public boolean isCellEditable(int rowIndex, int columnIndex) {
        return true;
    }

    @Override
    public void setValueAt(Object aValue, int rowIndex, int columnIndex) {
        final ColumnProvider<T, ?> column = columns.get(columnIndex);
        column.setValue(rows.get(rowIndex), aValue);
        this.fireTableCellUpdated(rowIndex, columnIndex);
    }

    @Override
    public String getColumnName(int column) {
        return columns.get(column).getTitle();
    }

    public void addColumn(ColumnProvider<T, ?> column) {
        this.columns.add(column);
        this.fireTableStructureChanged();
    }

    public void addRow(T row) {
        this.rows.add(row);
        this.fireTableRowsInserted(this.rows.size() - 1, this.rows.size() - 1);
    }

    @Override
    public Class<?> getColumnClass(int columnIndex) {
        return this.columns.get(columnIndex).getValueClass();
    }

    public static abstract class ColumnProvider<T, V> {
        private       String   title;
        private final Class<V> valueClass;

        protected ColumnProvider(String title, Class<V> valueClass) {
            this.title = title;
            this.valueClass = valueClass;
        }

        public String getTitle() {
            return title;
        }

        public void setTitle(String title) {
            this.title = title;
        }

        public Class<V> getValueClass() {
            return valueClass;
        }

        public abstract V getValue(T dto);

        public abstract void setValue(T dto, Object value);
    }
}

Example-"Application":

import javax.swing.*;
import java.awt.*;

public class JTableTest extends JFrame {

    private final JTable jTable;

    public JTableTest() throws HeadlessException {
        super("JFrame test");
        this.setDefaultCloseOperation(DISPOSE_ON_CLOSE);

        final GridBagLayout layout = new GridBagLayout();
        final Container contentPane = this.getContentPane();
        contentPane.setLayout(layout);

        final GridBagConstraints gridBagConstraints = new GridBagConstraints();
        gridBagConstraints.fill = GridBagConstraints.BOTH;
        gridBagConstraints.weightx = 1.0;
        gridBagConstraints.weighty = 1.0;

        final DtoTableModel<PersonDto> dm = new DtoTableModel<PersonDto>();
        jTable = new JTable(dm);
        dm.addColumn(new PersonDto.NameColumn());
        dm.addColumn(new PersonDto.StreetColumn());
        dm.addRow(new PersonDto(1L, "Paul", "Mayfairy Street"));
        dm.addRow(new PersonDto(2L, "Peter", "Ferdinand Street"));
        JScrollPane scrollpane = new JScrollPane(jTable);
        contentPane.add(scrollpane, gridBagConstraints);

        this.pack();
    }

    public static void main(String[] args) {
        final JTableTest jTableTest = new JTableTest();
        jTableTest.setVisible(true);
    }

}