import java.awt.Color;
import java.awt.Font;
import java.awt.GridBagConstraints;
import java.awt.GridBagLayout;
import java.awt.Insets;
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.ResultSetMetaData;
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.TableModel;
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{
DefaultTableModel tabModel;
JTable table;
Connection connection = null;
ResultSet rs = null;
PreparedStatement stmt = null;
/**
*
*/
private static final long serialVersionUID = 3136098036201838809L;
// 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 txtFldSuche;
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;
/**
* Create the panel.
*/
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, 0.0, 0.0, 1.0 };
// Zeilenhöhe
gbl.rowHeights = new int[] { 20, 0, 20, 20, 20, 20, 20, 20, 20, 20, 20,
0, 20, 20, 20, 20 };
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);
// ----------------------------------------------------------------------
// Textfields erstellen
txtFldSuche = new JTextField();
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);
// ----------------------------------------------------------------------
// Buttons
JButton btnSuchen = new JButton("Suchen");
JButton btnEintragAendern = new JButton("Eintrag \u00E4ndern");
JButton btnEintragLoeschen = new JButton("Eintrag l\u00F6schen");
JButton btnRefresh = new JButton("Tabelle anzeigen/aktualisieren");
// ---------------------------------------------------------------------
// 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);
// ---------------------------------------------------------------------
// 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 Textfields
this.add(txtFldSuche,new GridBagHelp(1, 3, 2, 1).hfill().bounds(0, 0, 5, 5, 0, 0).done());
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(3, 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());
// -------------------------------------------------------------------------
// 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
txtFldSuche.setColumns(10);
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);
/*********************************************************************************/
// Verbindung mit DB
DBConnect.connect();
// --------------------------------------------------------------------------------
showDb();
// --------------------------------------------------------------------------------------------
// DB Eintraege aendern
btnEintragAendern.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
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,
"btnEintragAendern/showDb() - Eintrag gespeichert");
} catch (Exception e1) {
JOptionPane.showMessageDialog(null, e1);
}
// Nun sollte die geaenderte Tabelle "Kommissionen" die urspruengliche
// durch "showDb(); ersetzt werden
showDb();
}
});
// --------------------------------------------------------------------------------
// Tabelle aktualisieren
btnRefresh.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
try {
showDb();
JOptionPane.showMessageDialog(null, "geklickt");
} catch (Exception error) {
JOptionPane.showMessageDialog(null, error);
}
}
});
// ------------------------------------------------------------------------------------
// Eintrag loeschen
btnEintragLoeschen.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
try {
Statement stmt = DBData.connection.createStatement();
String delDBTable;
delDBTable = "DELETE FROM KOMMISSIONEN WHERE ID = "
+ txtFldId.getText();
stmt.execute(delDBTable);
if (table.getSelectedRow() != -1) {
tabModel.removeRow(table.getSelectedRow());
}
} catch (Exception e1) {
JOptionPane.showMessageDialog(null, e1);
}
}
});
}
// =================================================================================================
public void showDb() {
// DB - Tabelle Kommissionen Tabellenbezeichnung und Daten auslesen
Vector<String> columnNames = new Vector<String>();
Vector<Vector<Object>> data = new Vector<Vector<Object>>();
new Vector<Object>();
try {
String sql = "SELECT * FROM KOMMISSIONEN";
Statement stmt = DBData.connection.createStatement();
ResultSet rs = stmt.executeQuery(sql);
ResultSetMetaData rsmd = rs.getMetaData();
int columns = rsmd.getColumnCount();
for (int i = 1; i <= columns; i++) {
columnNames.addElement(rsmd.getColumnName(i));
}
while (rs.next()) {
Vector<Object> row = new Vector<Object>(columns);
for (int i = 1; i <= columns; i++) {
// row.addElement(rs.getObject(i)); // Original
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"));
}
data.addElement(row);
}
rs.close();
stmt.close();
} catch (Exception e) {
System.out.println(e);
}
// ----------------------------------------------------------------------
// Auf der Konsole zur Kontrolle
System.out.println("Methode: showDb - " + data + "\n");
// ----------------------------------------------------------------------
// DB - Ergebnisse in DefaultTableModel fuellen
tabModel = new DefaultTableModel(data, columnNames);
table = new JTable(tabModel);
JScrollPane scrollPane = new JScrollPane(table);
// GBC table
GridBagConstraints gbc_scrollPane = new GridBagConstraints();
gbc_scrollPane.insets = new Insets(0, 0, 5, 0);
gbc_scrollPane.gridheight = 1;
gbc_scrollPane.gridwidth = 5;
gbc_scrollPane.fill = GridBagConstraints.BOTH;
gbc_scrollPane.gridx = 0;
gbc_scrollPane.gridy = 12;
table.setAutoResizeMode(JTable.AUTO_RESIZE_OFF);
table.setFont(new Font("Century Gothic", Font.PLAIN, 12));
table.setEnabled(true);
table.setOpaque(false);
scrollPane.setOpaque(false);
add(scrollPane, gbc_scrollPane);
/*
* TODO zellen sollten gesperrt sein
*/
// --------------------------------------------------------------------------------------
// Zeilenumbruch der einzelnen Eintraege
// http://www.tutorials.de/swing-java2d-3d-swt-jface/228280-zeilenumbruch-im-jtable-verzweiflung.html
int str_length = 0; // variable zum setzen des grössten wertes
TableModel model = table.getModel();
for (int clmCnt = model.getColumnCount(), rowCnt = model.getRowCount(), i = 0; i < rowCnt; i++) {
for (int j = 0; j < clmCnt; j++) {
if (model.getValueAt(i, j) != null) {
String value = model.getValueAt(i, j).toString();
// Es werden ja alle spalten ausgelesen deswegen hole ich
// mir
// den den grössten wert
if (value.length() >= str_length) {
str_length = value.length();
}
// hier wird die grösste variable als zeilenhöhe gesetz bei
// der aktuellen Spalte
table.setRowHeight(i, str_length / 2 + 16);
}
}
// Nach jeder Zeile muss die Höhe wieder zurückgesetzt werden
str_length = 0;
}
// Spaltenbreite definieren
table.getColumnModel().getColumn(0).setPreferredWidth(30); // ID
table.getColumnModel().getColumn(1).setPreferredWidth(130); // Datum
table.getColumnModel().getColumn(2).setPreferredWidth(70); // KommNr
table.getColumnModel().getColumn(3).setPreferredWidth(70); // KommName
table.getColumnModel().getColumn(4).setPreferredWidth(70); // Strasse
table.getColumnModel().getColumn(5).setPreferredWidth(40); // HausNr
table.getColumnModel().getColumn(6).setPreferredWidth(40); // PLZ
table.getColumnModel().getColumn(7).setPreferredWidth(80); // Gemeinde
table.getColumnModel().getColumn(8).setPreferredWidth(100); // TelNr
table.getColumnModel().getColumn(9).setPreferredWidth(160); // Bem1
table.getColumnModel().getColumn(10).setPreferredWidth(155);// Bem2
// Klick auf Tabellenzeile
// Inhalt in die dafuer vorgesehenen txtFields ausgeben
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());
}
});
}
}
