import java.awt.Color;
import java.awt.FlowLayout;
import java.awt.Font;
import java.awt.GridBagLayout;
import java.awt.Rectangle;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.MouseAdapter;
import java.awt.event.MouseEvent;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;
import javax.swing.JButton;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.JTextField;
import javax.swing.table.DefaultTableModel;
import javax.swing.table.TableRowSorter;
import at.p4glass.db.DBConnect;
import at.p4glass.db.DBData;
import at.p4glass.design.DesignLabel;
import at.p4glass.design.GridBagHelp;
public class KommList extends JPanel{
// serialVersionUID
private static final long serialVersionUID = 3136098036201838809L;
//-------------------------------------------------------------------------
// Benoetigte Voraussetzungen für die Darstellung der Daten
private JTable table;
private DefaultTableModel tabModel;
private TableRowSorter<DefaultTableModel> sorter;
// Benoetigte Voraussetzungen für die Datenbank
Connection connection = null;
ResultSet rs = null;
PreparedStatement stmt = null;
//------------------------------------------------------------------------
// Labels anlegen
private JLabel lblAlleKommissionen; // Ueberschrift
private JLabel lblHeadline; // Ueberschrift
private JLabel lblId;
private JLabel lblDatum;
private JLabel lblKommissionsnummer;
private JLabel lblKommName;
private JLabel lblStrasse;
private JLabel lblHausnummer;
private JLabel lblPlz;
private JLabel lblGemeinde;
private JLabel lblTelnr;
private JLabel lblBemerkung1;
private JLabel lblBemerkung2;
// txtFields anlegen
private JTextField txtFldId;
private JTextField txtFldDatum;
private JTextField txtFldKommNr;
private JTextField txtFldKommName;
private JTextField txtFldStrasse;
private JTextField txtFldHausNr;
private JTextField txtFldPlz;
private JTextField txtFldGemeinde;
private JTextField txtFldTelNr;
private JTextField txtFldBem1;
private JTextField txtFldBem2;
// Buttons anlegen
private JButton btnReset;
private JButton btnEintragAendern;
private JButton btnEintragLoeschen;
private JButton btnRefresh;
private JButton btnSuchen;
//======================================================================
// Konstruktor
public KommList(ActionListener listener) {
setFont(new Font("Century Gothic", Font.PLAIN, 12));
setForeground(Color.WHITE);
this.setOpaque(false);
GridBagLayout gbl = new GridBagLayout();
// Größenänderung bei resize der einzelnen Spalten
// 0 = bleibt gleich
// 1.0 = wächst
gbl.columnWeights = new double[] { 1.0, 1.0, 1.0, 1.0, 1.0 };
// Spaltenbreite
gbl.columnWidths = new int[] { 50, 0, 0, 0, 50 };
// Größenänderung bei resize der einzelnen Zeilen
// 0 = bleibt gleich
// 1.0 = wächst
gbl.rowWeights = new double[] { 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0,
0.0, 0.0, 0.0, 0.0, 0.0 };
// Zeilenhöhe
gbl.rowHeights = new int[] { 20, 0, 20, 20, 20, 20, 20, 20, 20, 20, 20,
0, 0 };
setLayout(gbl);
// ------------------------------------------------------------------------
// Labels erstellen
lblAlleKommissionen = new JLabel(); // Ueberschrift
lblHeadline = new JLabel(); // Ueberschrift
lblId = new DesignLabel("ID", 14);
lblDatum = new DesignLabel("Datum", 14);
lblKommissionsnummer = new DesignLabel("KommNr", 14);
lblKommName = new DesignLabel("KommName", 14);
lblStrasse = new DesignLabel("Strasse", 14);
lblHausnummer = new DesignLabel("Hausnummer", 14);
lblPlz = new DesignLabel("PLZ", 14);
lblGemeinde = new DesignLabel("Gemeinde", 14);
lblTelnr = new DesignLabel("TelNr", 14);
lblBemerkung1 = new DesignLabel("Bemerkung 1", 14);
lblBemerkung2 = new DesignLabel("Bemerkung2", 14);
txtFldId = new JTextField();
txtFldDatum = new JTextField();
txtFldKommNr = new JTextField();
txtFldKommName = new JTextField();
txtFldStrasse = new JTextField();
txtFldHausNr = new JTextField();
txtFldPlz = new JTextField();
txtFldGemeinde = new JTextField();
txtFldTelNr = new JTextField();
txtFldBem1 = new JTextField();
txtFldBem2 = new JTextField();
// ----------------------------------------------------------------------
// Textfields disabled
txtFldId.setEditable(false);
txtFldDatum.setEditable(false);
// ----------------------------------------------------------------------
// Design Headline
lblAlleKommissionen.setText("Alle Kommissionen");
lblAlleKommissionen.setFont(new Font("Century Gothic", Font.BOLD, 15));
lblAlleKommissionen.setForeground(Color.WHITE);
lblHeadline
.setText("<html><center>Hier k\u00F6nnen Sie jede einzelne Kommission \u00E4ndern oder l\u00F6schen</center></html>");
lblHeadline.setFont(new Font("Century Gothic", Font.BOLD, 15));
lblHeadline.setForeground(Color.WHITE);
// ---------------------------------------------------------------------
// Buttons
// Suchmodus
btnSuchen = new JButton("Kommissionen Suchen");
btnSuchen.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
searchButton();
}
});
// Datensatz in DB aendern
btnEintragAendern = new JButton("Eintrag \u00E4ndern");
btnEintragAendern.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
datensatzAendern();
}
});
// Datensatz aus DB loeschen
btnEintragLoeschen = new JButton("Eintrag l\u00F6schen");
btnEintragLoeschen.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
datensatzLoeschen();
}
});
// Datensaetze aus DB holen
btnRefresh = new JButton("Tabelle aktualisieren");
btnRefresh.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
callData();
}
});
// Datensaetze erneut aus DB holen
btnReset = new JButton("Suche zur\u00FCcksetzen");
btnReset.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
reset();
}
});
//-------------------------------------------------------------------------------
// GBC Labels
this.add(lblAlleKommissionen,new GridBagHelp(1, 1, 1, 1).bounds(0, 0, 5, 5, 0, 0).done());
this.add(lblHeadline,new GridBagHelp(2, 1, 2, 1).bounds(0, 0, 5, 5, 0, 0).done());
this.add(lblId,new GridBagHelp(0, 4, 1, 1).anchorRight().bounds(0, 0, 5, 5, 0, 0).done());
this.add(lblDatum,new GridBagHelp(2, 4, 1, 1).anchorRight().bounds(0, 0, 5, 5, 0, 0).done());
this.add(lblKommissionsnummer, new GridBagHelp(0, 5, 1, 1).anchorRight().bounds(0, 0, 5, 5, 0, 0).done());
this.add(lblKommName,new GridBagHelp(2, 5, 1, 1).anchorRight().bounds(0, 0, 5, 5, 0, 0).done());
this.add(lblStrasse,new GridBagHelp(0, 6, 1, 1).anchorRight().bounds(0, 0, 5, 5, 0, 0).done());
this.add(lblHausnummer, new GridBagHelp(2, 6, 1, 1).anchorRight().bounds(0, 0, 5, 5, 0, 0).done());
this.add(lblPlz,new GridBagHelp(0, 7, 1, 1).anchorRight().bounds(0, 0, 5, 5, 0, 0).done());
this.add(lblGemeinde,new GridBagHelp(2, 7, 1, 1).anchorRight().bounds(0, 0, 5, 5, 0, 0).done());
this.add(lblTelnr,new GridBagHelp(0, 8, 1, 1).anchorRight().bounds(0, 0, 5, 5, 0, 0).done());
this.add(lblBemerkung1, new GridBagHelp(2, 8, 1, 1).anchorRight().bounds(0, 0, 5, 5, 0, 0).done());
this.add(lblBemerkung2, new GridBagHelp(2, 9, 1, 1).anchorRight().bounds(0, 0, 5, 5, 0, 0).done());
//--------------------------------------------------------------------------------
// GBC txtFields
this.add(txtFldId,new GridBagHelp(1, 4, 1, 1).hfill().bounds(0, 0, 5, 5, 0, 0).done());
this.add(txtFldDatum,new GridBagHelp(3, 4, 1, 1).hfill().bounds(0, 0, 5, 5, 0, 0).done());
this.add(txtFldKommNr,new GridBagHelp(1, 5, 1, 1).hfill().bounds(0, 0, 5, 5, 0, 0).done());
this.add(txtFldKommName,new GridBagHelp(3, 5, 1, 1).hfill().bounds(0, 0, 5, 5, 0, 0).done());
this.add(txtFldStrasse,new GridBagHelp(1, 6, 1, 1).hfill().bounds(0, 0, 5, 5, 0, 0).done());
this.add(txtFldHausNr,new GridBagHelp(3, 6, 1, 1).hfill().bounds(0, 0, 5, 5, 0, 0).done());
this.add(txtFldPlz,new GridBagHelp(1, 7, 1, 1).hfill().bounds(0, 0, 5, 5, 0, 0).done());
this.add(txtFldGemeinde,new GridBagHelp(3, 7, 1, 1).hfill().bounds(0, 0, 5, 5, 0, 0).done());
this.add(txtFldTelNr,new GridBagHelp(1, 8, 1, 1).hfill().bounds(0, 0, 5, 5, 0, 0).done());
this.add(txtFldBem1,new GridBagHelp(3, 8, 1, 1).hfill().bounds(0, 0, 5, 5, 0, 0).done());
this.add(txtFldBem2,new GridBagHelp(3, 9, 1, 1).hfill().bounds(0, 0, 5, 5, 0, 0).done());
// --------------------------------------------------------------------------
// GBC Buttons
this.add(btnSuchen, new GridBagHelp(1, 3, 1, 1).bounds(0, 0, 5, 5, 0, 0).done());
this.add(btnEintragAendern,new GridBagHelp(1, 10, 1, 1).bounds(0, 0, 5, 5, 0, 0).done());
this.add(btnEintragLoeschen,new GridBagHelp(3, 10, 1, 1).bounds(0, 0, 5, 5, 0, 0).done());
this.add(btnRefresh,new GridBagHelp(2, 10, 1, 1).bounds(0, 0, 5, 5, 0, 0).done());
this.add(btnReset,new GridBagHelp(3, 3, 1, 1).bounds(0, 0, 5, 5, 0, 0).done());
// --------------------------------------------------------------------------
// JTextfields setColumns
// Sichtbare Breite des Textfeldes
// hier 10, also passen 10 m's hinein
// m = Richtbuchstabe
// wenn mehr Buchstaben eingegeben werden, verbreitert sich das txtFeld
// nicht
txtFldId.setColumns(10);
txtFldDatum.setColumns(10);
txtFldKommNr.setColumns(10);
txtFldKommName.setColumns(10);
txtFldStrasse.setColumns(10);
txtFldHausNr.setColumns(10);
txtFldPlz.setColumns(10);
txtFldGemeinde.setColumns(10);
txtFldTelNr.setColumns(10);
txtFldBem1.setColumns(10);
txtFldBem2.setColumns(10);
/*********************************************************************************/
// DefaultTableModel
tabModel = new DefaultTableModel() {
// serialVersionUID
private static final long serialVersionUID = -8189518063922844923L;
// Zellen sperren
public boolean isCellEditable(int row, int column) {
return false;
}
};
// Spaltennamen hinzufuegen
tabModel.setColumnIdentifiers(COLUMN_IDENTIFIERS);
// Model zu Table hinzufuegen
table = new JTable(tabModel);
table.setFont(new Font("Century Gothic", Font.PLAIN, 12));
table.setEnabled(true);
table.setAutoCreateRowSorter(true);
table.setFillsViewportHeight(true);
table.setOpaque(false);
// Scrollpane erzeugen und table integrieren
JScrollPane scrollPane = new JScrollPane(table);
scrollPane.setOpaque(false);
// GBC ScrollPane
this.add(scrollPane,new GridBagHelp(0, 12, 5, 1).hfill().bounds(0, 0, 5, 5, 0, 0).done());
// Datensaetze aufrufen
callData();
}
// =================================================================================================
// Vector fuer die Spaltennamen
public final static Vector<?> COLUMN_IDENTIFIERS = new Vector<Object>() {
// serialVersionUID
private static final long serialVersionUID = 4136566931496300757L;
{
add("id");
add("datum");
add("kommNr");
add("kommName");
add("strasse");
add("hausnr");
add("plz");
add("gemeinde");
add("telNr");
add("bem 1");
add("bem 2");
}
};
// --------------------------------------------------------------------------------
// Vector fur die Daten aus der DB
protected Vector<Vector<String>> findAllPersons() {
DBConnect.connect();
Vector<Vector<String>> results = new Vector<Vector<String>>();
try {
Statement stmt = DBData.connection.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM KOMMISSIONEN");
while (rs.next()) {
Vector<String> row = new Vector<String>();
row.addElement(rs.getString("ID"));
row.addElement(rs.getString("DATUM"));
row.addElement(rs.getString("KOMMNR"));
row.addElement(rs.getString("KOMMNAME"));
row.addElement(rs.getString("STRASSE"));
row.addElement(rs.getString("HAUSNR"));
row.addElement(rs.getString("PLZ"));
row.addElement(rs.getString("GEMEINDE"));
row.addElement(rs.getString("TELNR"));
row.addElement(rs.getString("BEMERKUNG1"));
row.addElement(rs.getString("BEMERKUNG2"));
results.add(row);
}
} catch (SQLException e) {
e.printStackTrace();
}
return results;
}
//------------------------------------------------------------------------------------------------
public void searchButton() {
// txtFields leeren
txtFieldsLoeschen();
// NullPointerException umgehen
reset();
JPanel panel = new JPanel(new FlowLayout());
String text = JOptionPane.showInputDialog(panel);
// gesuchte Zeilen markieren
DefaultTableModel model = (DefaultTableModel) table.getModel();
for (int i = 0; i < model.getRowCount(); i++) {
for (int j = 0; j < model.getColumnCount(); j++) {
if (model
.getValueAt(table.convertRowIndexToModel(i),
table.convertColumnIndexToModel(j))
.toString().indexOf(text) != -1) {
Rectangle r = table.getCellRect(i, 0, false);
table.scrollRectToVisible(r);
table.changeSelection(i, j, true, false);
for(int k=0; k<table.getColumnCount(); k++){
System.out.println(table.getValueAt(i, k));
}
}
}
}
}
//--------------------------------------------------------------------------------------------
public void datensatzAendern(){
try {
Statement stmt = DBData.connection.createStatement();
String updateDBTable;
String where;
updateDBTable = "UPDATE KOMMISSIONEN SET ";
where = " WHERE ID = " + txtFldId.getText();
stmt.executeUpdate(updateDBTable + "kommnr = '"
+ txtFldKommNr.getText() + "'" + ", "
+ "kommname = '" + txtFldKommName.getText() + "'"
+ ", " + "strasse = '" + txtFldStrasse.getText()
+ "'" + ", " + "hausnr = '"
+ txtFldHausNr.getText() + "'" + ", " + "plz = '"
+ txtFldPlz.getText() + "'" + ", " + "gemeinde = '"
+ txtFldGemeinde.getText() + "'" + ", "
+ "telnr = '" + txtFldTelNr.getText() + "'" + ", "
+ "bemerkung1 = '" + txtFldBem1.getText() + "'"
+ ", " + "bemerkung2 = '" + txtFldBem2.getText()
+ "'" + where);
JOptionPane.showMessageDialog(null,
"\u00C4nderung gespeichert");
} catch (Exception e1) {
JOptionPane.showMessageDialog(null, "Bitte w\u00E4hlen Sie einen Datensatz");
}
// Textdfelder loeschen
txtFieldsLoeschen();
callData();
}
//--------------------------------------------------------------------------------------------
public void txtFieldsLoeschen(){
txtFldId.setText(null);
txtFldDatum.setText(null);
txtFldKommNr.setText(null);
txtFldKommName.setText(null);
txtFldStrasse.setText(null);
txtFldHausNr.setText(null);
txtFldPlz.setText(null);
txtFldGemeinde.setText(null);
txtFldTelNr.setText(null);
txtFldBem1.setText(null);
txtFldBem2.setText(null);
}
public void datensatzLoeschen(){
try {
Statement stmt = DBData.connection.createStatement();
String delDBTable;
delDBTable = "DELETE FROM KOMMISSIONEN WHERE ID = "
+ txtFldId.getText();
stmt.execute(delDBTable);
JOptionPane.showMessageDialog(null, "Datensatz gel\u00F6scht");
} catch (Exception e1) {
JOptionPane.showMessageDialog(null, "Bitte w\u00E4hlen Sie einen Datensatz");
}
// txtFields leeren
txtFieldsLoeschen();
callData();
}
//--------------------------------------------------------------------------------------------
public void callData(){
Vector<Vector<String>> results = findAllPersons();
tabModel.setDataVector(results, COLUMN_IDENTIFIERS);
tabModel.fireTableDataChanged();
table.addMouseListener(new MouseAdapter() {
@Override
public void mouseClicked(MouseEvent evt) {
int row = table.getSelectedRow();
txtFldId.setText(table.getModel().getValueAt(row, 0).toString());
txtFldDatum.setText(table.getModel().getValueAt(row, 1)
.toString());
txtFldKommNr.setText(table.getModel().getValueAt(row, 2)
.toString());
txtFldKommName.setText(table.getModel().getValueAt(row, 3)
.toString());
txtFldStrasse.setText(table.getModel().getValueAt(row, 4)
.toString());
txtFldHausNr.setText(table.getModel().getValueAt(row, 5)
.toString());
txtFldPlz.setText(table.getModel().getValueAt(row, 6)
.toString());
txtFldGemeinde.setText(table.getModel().getValueAt(row, 7)
.toString());
txtFldTelNr.setText(table.getModel().getValueAt(row, 8)
.toString());
txtFldBem1.setText(table.getModel().getValueAt(row, 9)
.toString());
txtFldBem2.setText(table.getModel().getValueAt(row, 10)
.toString());
}
});
}
//--------------------------------------------------------------------------------------------
public void reset(){
createRowSorter((DefaultTableModel) table.getModel());
}
//-------------------------------------------------------------------------------------------
private void createRowSorter(DefaultTableModel model) {
sorter = new TableRowSorter<DefaultTableModel>(model);
table.setRowSorter(sorter);
}
//-------------------------------------------------------------------------------------------
}
