TableModel.java

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package de.mpg.molgen.buczek.portifix;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.table.AbstractTableModel;

/**
 *
 * @author buczek
 */
public class TableModel extends AbstractTableModel {

    Connection con;

    public final static int COLUMN_NACHNAME = 0;
    public final static int COLUMN_VORNAME = 1;
    public final static int COLUMN_ABTEILUNG = 2;
    public final static int COLUMN_SCHLUESSELNUMMER = 3;
    public final static int COLUMN_EXEMPLARNUMMER = 4;
    public final static int COLUMN_RUECKGABEDATUM = 5;

    public final static int COLUMN_VISIBLE_COUNT = 6;

    public final static int COLUMN_AUSAGBE_ID = 6;
    public final static int COLUMN_EXEMPLAR_ID = 7;
    public final static int COLUMN_PERSONAL_ID = 8;

    public final static int COLUMN_COUNT = 10;

    private final String COLUMN_NAMES[] = {
        "Nachname",
        "Vorname",
        "Abteilung",
        "Schlüsselnummer",
        "Exemplarnummer",
        "Rückgabedatum",
        "Ausgabe ID",
        "Exemplar ID",
        "Personal ID"
    };

    private final Class COLUMN_CLASS[] = {
        String.class,
        String.class,
        String.class,
        String.class,
        String.class,
        MyDate.class,
        String.class,
        String.class,
        String.class,};

    final private static String SQL
            = "select NAME_1,NAME_2,ABTEILUNG,RUECKGABETERMINDATUM,AUSGABE.SCHLUESSELNUMMER,"
            + "EXEMPLARNUMMER,AUSGABE_ID,AUSGABE.EXEMPLAR_ID,AUSGABE.PERSONAL_ID"
            + " from AUSGABE,PERSONAL,EXEMPLAR"
            + " where AUSGABE.PERSONAL_ID=PERSONAL.PERSONAL_ID"
            + " and AUSGABE.EXEMPLAR_ID=EXEMPLAR.EXEMPLAR_ID"
            + " AND rueckgabetermindatum is not null"
            + " order by rueckgabetermindatum";

    final private static String SQL_ALL
            = "select NAME_1,NAME_2,ABTEILUNG,RUECKGABETERMINDATUM,AUSGABE.SCHLUESSELNUMMER,"
            + "EXEMPLARNUMMER,AUSGABE_ID,AUSGABE.EXEMPLAR_ID,AUSGABE.PERSONAL_ID"
            + " from AUSGABE,PERSONAL,EXEMPLAR"
            + " where AUSGABE.PERSONAL_ID=PERSONAL.PERSONAL_ID"
            + " and AUSGABE.EXEMPLAR_ID=EXEMPLAR.EXEMPLAR_ID"
            + " order by NAME_1";

    private  ArrayList<Object[]> row = new ArrayList<Object[]>(100);

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

    @Override
    public int getColumnCount() {
        return COLUMN_VISIBLE_COUNT;
        // return CO:LUMN_COUNT;
    }

    @Override
    public Object getValueAt(int rowIndex, int columnIndex) {
        return row.get(rowIndex)[columnIndex];
    }

    @Override
    public String getColumnName(int column) {
        return COLUMN_NAMES[column];
    }

    @Override
    public Class getColumnClass(int column) {
        return COLUMN_CLASS[column];
    }

    private static final boolean SHOW_ALL = true;

    private MyDate upgradeOptionalDate(Date d) {
        return d == null ? null : new MyDate(d);
    }

    void populate() throws SQLException {
        row.clear();
        Statement statement = con.createStatement();
        ResultSet resultSet = statement.executeQuery(SHOW_ALL ? SQL_ALL : SQL);
        while (resultSet.next()) {
            Object row[] = new Object[10];
            row[COLUMN_NACHNAME] = resultSet.getString(1);                // name_1 vorname
            row[COLUMN_VORNAME] = resultSet.getString(2);                // name_2 nachname
            row[COLUMN_ABTEILUNG] = resultSet.getString(3);                // abteilung
            row[COLUMN_RUECKGABEDATUM] = upgradeOptionalDate(resultSet.getDate(4));    // rueckgabetermin (ausgabe)
            row[COLUMN_SCHLUESSELNUMMER] = resultSet.getString(5);                // schluesselnummer
            row[COLUMN_EXEMPLARNUMMER] = resultSet.getString(6);                // exemplarnummer
            row[COLUMN_AUSAGBE_ID] = resultSet.getString(7);                // ausgabeid
            row[COLUMN_EXEMPLAR_ID] = resultSet.getString(8);                // exemplarid
            row[COLUMN_PERSONAL_ID] = resultSet.getString(9);               // personalid
            this.row.add(row);
        }
        this.fireTableDataChanged();
    }

    public boolean isCellEditable(int rowIndex, int columnIndex) {
        return false;
    }

    private static final String UPDATE_STATEMENT
            = "UPDATE AUSGABE "
            + " SET rueckgabetermindatum=?"
            + " WHERE EXEMPLAR_ID=?"
            + " AND PERSONAL_ID=?";

    void setNewDateThis(int rowIndex, Date newDate) {
        PreparedStatement statement;
        try {
            statement = con.prepareStatement(UPDATE_STATEMENT);
            statement.setDate(1, new java.sql.Date(newDate.getTime()));
            statement.setString(2, (String) getValueAt(rowIndex, COLUMN_EXEMPLAR_ID));
            statement.setString(3, (String) getValueAt(rowIndex, COLUMN_PERSONAL_ID));
            int i = statement.executeUpdate();
            populate();
        } catch (SQLException ex) {
            Logger.getLogger(TableModel.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void setConnection(Connection c) {
        this.con = c;
    }
}