Coder Forum

Java Quellcode

package TableBrowserTest;
 
import java.awt.*;
import java.sql.*;
import javax.swing.*;
 
public class TableBrowser extends JFrame {
 
public static void main(String[] args) {
 new TableBrowser();
 new TimeTable();
}
 
/* Deklaration der Variablen Objekte und Felder */
 
final DefaultTableModel data = new DefaultTableModel();
 
final static JTextField t_user = new JTextField(20);
final static JTextField t_pw = new JTextField(20);
final static JTextField t_db = new JTextField(20);
final static JTextField t_table = new JTextField(20);
 
final JTextField t_vname = new JTextField(20);
final JTextField t_nname = new JTextField(20);
final JTextField t_try = new JTextField(30);
final JTextField t_begin = new JTextField(20);
final JTextField t_end = new JTextField(20);
final static JTextField t_date = new JTextField(20);
 
JButton b_connect = new JButton("Show");
JButton b_insert = new JButton("Insert");
 
JLabel l_user = new JLabel("User");
JLabel l_pw = new JLabel("Password");
JLabel l_db = new JLabel("Database");
JLabel l_table = new JLabel("Table");
 
JLabel l_empty = new JLabel(" ");
JLabel l_empty2 = new JLabel(" ");
JLabel l_empty3 = new JLabel(" ");
JLabel l_empty4 = new JLabel(" ");
JLabel l_empty5 = new JLabel(" ");
JLabel l_empty6 = new JLabel(" ");
 
JLabel l_vname = new JLabel("Vorname");
JLabel l_nname = new JLabel("Nachname");
JLabel l_try = new JLabel("Versuch");
JLabel l_begin = new JLabel("Beginn");
JLabel l_end = new JLabel("Ende");
JLabel l_date = new JLabel("Datum");
 
/* Methode zum überprüfen der Datensätze auf Redundanz */
public void checkData() {
 try {
  Class.forName("com.mysql.jdbc.Driver");
  java.sql.Connection con = DriverManager.getConnection(
    "jdbc:mysql://localhost/" + t_db.getText(), t_user
      .getText(), t_pw.getText());
 
  /* SQL-Query ausführen und ResultSet zurückholen */
  java.sql.Statement st = con.createStatement();
  String sql = "SELECT * FROM " + t_table.getText()
    + " WHERE Datum = '" + t_date.getText()
    + "' AND Beginn <= '" + t_begin.getText()
    + ":00' AND Ende >= '" + t_begin.getText()
    + ":00' OR Datum = '" + t_date.getText()
    + "' AND Beginn <= '" + t_end.getText()
    + ":00' AND Ende >= '" + t_end.getText()
    + ":00' OR Datum = '" + t_date.getText()
    + "' AND Beginn >= '" + t_begin.getText()
    + ":00' AND Ende <= '" + t_end.getText()+":00'";
  ResultSet rsSet = st.executeQuery(sql);
 
  rsSet.last();
  int rowCount = rsSet.getRow();
 
  if (rowCount == 0) {
   insertData();
  } else {
   throw new SQLException("Dieser Zeitraum ist bereits belegt");
  }
 
 } catch (ClassNotFoundException e) {
  System.err.println("DB-Driver not found!");
  System.exit(1);
 } catch (SQLException e) {
  System.err.println("SQL-Error:" + e.getMessage());
  JOptionPane.showMessageDialog(null,
    "Dieser Zeitraum ist bereits belegt!", "Fehler",
    JOptionPane.ERROR_MESSAGE);
 }
}
 
/* Methode zum übertragen der Daten aus der SQL Datenbank in die JTable */
public void updateData() {
 /* Verbindung zur DB herstellen, Statement initialisieren */
 
 try {
  Class.forName("com.mysql.jdbc.Driver");
  java.sql.Connection con = DriverManager.getConnection(
    "jdbc:mysql://localhost/" + t_db.getText(), t_user
      .getText(), t_pw.getText());
 
  /* SQL-Query ausführen und ResultSet zurückholen */
  java.sql.Statement st = con.createStatement();
  String sql = "SELECT Versuch, Beginn, Ende, Datum FROM "
    + t_table.getText() + " ORDER BY Datum, Beginn, Ende";
  ResultSet rs = st.executeQuery(sql);
 
  ResultSetMetaData rsmd = null;
  int clmCnt = -1;
 
  rsmd = rs.getMetaData();
  clmCnt = rsmd.getColumnCount();
 
  if (rsmd == null || clmCnt == -1) {
   throw new RuntimeException("rsmd is null");
  }
 
  try {
   rs.last();
   int rowCnt = rs.getRow();
   rs.beforeFirst();   
 
   Object[][] odata = new Object[rowCnt][clmCnt];
   Object[] clmHeaders = new Object[clmCnt];
 
   for (int i = 1; i <= clmCnt; i++) {
    clmHeaders[i - 1] = rsmd.getColumnName(i);
   }
 
   int row = 0;
   while (rs.next()) {
    for (int j = 1; j <= clmCnt; j++) {
     odata[row][j - 1] = rs.getString(j);
    }
    row++;
   }
   data.setDataVector(odata, clmHeaders);
 
  } catch (SQLException e1) {
   e1.printStackTrace();
  }
 
  data.fireTableStructureChanged();
  data.fireTableDataChanged();
 
 } catch (ClassNotFoundException e) {
  System.err.println("DB-Driver not found!");
  System.exit(1);
 } catch (SQLException e) {
  System.err.println("SQL-Error:" + e.getMessage());
  System.exit(1);
 }
}
 
/* Methode zum hinzufügen eines Datensatzes in die SQL-Datenbank */
public void insertData() {
 try {
  Class.forName("com.mysql.jdbc.Driver");
  java.sql.Connection con = DriverManager.getConnection(
    "jdbc:mysql://localhost/" + t_db.getText(), t_user
      .getText(), t_pw.getText());
 
  /* SQL-Query ausführen und ResultSet zurückholen */
  java.sql.Statement st = con.createStatement();
  String sql = "INSERT INTO "
    + t_table.getText()
    + " (`Vorname`, `Nachname`, `Versuch`, `Beginn`, `Ende`, `Datum`) "
    + "VALUES (" + "'" + t_vname.getText() + "', '"
    + t_nname.getText() + "', '" + t_try.getText() + "', '"
    + t_begin.getText() + "', '" + t_end.getText() + "', '"
    + t_date.getText() + "'" + ")";
 
  st.executeUpdate(sql);
  updateData();
 
 } catch (ClassNotFoundException e) {
  System.err.println("DB-Driver not found!");
  System.exit(1);
 } catch (SQLException e) {
  System.err.println("SQL-Error: " + e.getMessage());
  JOptionPane.showMessageDialog(null, "SQL Fehler", "Fehler",
    JOptionPane.ERROR_MESSAGE);
 }
}
 
public static String getDate(){
 return "2009-06-17";
}
 
public static String getTable(){
 return "bookings";
}
 
public static String getUser(){
 return t_user.getText();
}
 
public static String getPW(){
 return t_pw.getText();
}
 
public static String getDB(){
 return t_db.getText();
}
 
 
 
public TableBrowser() {
 JFrame frame = new JFrame("Tabelle");
 
 frame.addWindowListener(new WindowAdapter() {
  @Override
  public void windowClosing(WindowEvent e) {
   System.exit(0);
  }
 });
 
 JTable tableView = new JTable(data);
 
 JScrollPane scrollpane = new JScrollPane(tableView);
 scrollpane.setBorder(new BevelBorder(BevelBorder.LOWERED));
 scrollpane.setPreferredSize(new Dimension(500, 400));
 
 /* ********************************************************* */
 /* TableBrowserConnect */
 JPanel conpane = new JPanel(new GridLayout(4, 6));
 
 conpane.add(l_user);
 conpane.add(l_pw);
 conpane.add(l_db);
 conpane.add(l_table);
 conpane.add(l_empty);
 conpane.add(l_empty2);
 conpane.add(l_empty3);
 
 conpane.add(t_user);
 conpane.add(t_pw);
 conpane.add(t_db);
 conpane.add(t_table);
 conpane.add(b_connect);
 conpane.add(l_empty4);
 conpane.add(l_empty5);
 
 conpane.add(l_vname);
 conpane.add(l_nname);
 conpane.add(l_try);
 conpane.add(l_begin);
 conpane.add(l_end);
 conpane.add(l_date);
 conpane.add(l_empty6);
 
 conpane.add(t_vname);
 conpane.add(t_nname);
 conpane.add(t_try);
 conpane.add(t_begin);
 conpane.add(t_end);
 conpane.add(t_date);
 conpane.add(b_insert);
 
 /* Button Funktionen hinzufügen */
 
 b_connect.addActionListener(new ActionListener() {
  public void actionPerformed(final ActionEvent e) {
   updateData();
  }
 });
 
 b_insert.addActionListener(new ActionListener() {
  public void actionPerformed(final ActionEvent e) {
   checkData();
  }
 });
 
 /* Ende von TableBrowserConnect */
 /* ********************************************************* */
 
 Container content = getContentPane();
 content.setLayout(new BorderLayout());
 
 content.add(conpane, BorderLayout.NORTH);
 content.add(scrollpane, BorderLayout.SOUTH);
 
 setSize(650, 550);
 setVisible(true);
}
}

Java Quellcode

package TableBrowserTest;
 
import java.awt.*;
import java.sql.*;
import javax.swing.*;
 
public class TimeTable extends JFrame {
 
	public TimeTable() {
		super();
		String[][] rowData = { { "8:00:00", "8:30:00" },
				{ "8:30:00", "9:00:00" }, { "9:00:00", "9:30:00" },
				{ "9:30:00", "10:00:00" }, { "10:00:00", "10:30:00" },
				{ "10:30:00", "11:00:00" }, { "11:00:00", "11:30:00" },
				{ "11:30:00", "12:00:00" }, { "12:00:00", "12:30:00" },
				{ "12:30:00", "13:00:00" }, { "13:00:00", "13:30:00" },
				{ "13:30:00", "14:00:00" }, { "14:00:00", "14:30:00" },
				{ "14:30:00", "15:00:00" }, { "15:00:00", "15:30:00" },
				{ "15:30:00", "16:00:00" }, { "16:00:00", "16:30:00" },
				{ "16:30:00", "17:00:00" } };
 
		String[] columnNames = { "Von", "Bis" };
 
		JFrame f = new JFrame("Zeiten Übersicht");
		f.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
 
		JTable table = new JTable(rowData, columnNames);
 
		try {
			Class.forName("com.mysql.jdbc.Driver");
			java.sql.Connection con2 = DriverManager.getConnection(
					"jdbc:mysql://localhost/braccess", "root", "");
 
			/* SQL-Query ausführen und ResultSet zurückholen */
			java.sql.Statement st = con2.createStatement();
			String sql = "SELECT Beginn, Ende FROM " + TableBrowser.getTable()
					+ " WHERE Datum = '" + TableBrowser.getDate() + "'";
			final ResultSet rSet = st.executeQuery(sql);
 
			ResultSetMetaData rSetmd = null;
			final int colCnt;
 
			rSetmd = rSet.getMetaData();
			colCnt = rSetmd.getColumnCount();
 
			/*** Zellen einfärben ***/
			table.setDefaultRenderer(Object.class,
					new DefaultTableCellRenderer() {
						public Component getTableCellRendererComponent(
								JTable table, Object value, boolean isSelected,
								boolean hasFocus, int row, int column) {
 
							Component c = super.getTableCellRendererComponent(
									table, value, isSelected, hasFocus, row,
									column);
 
							try {
								rSet.beforeFirst();
								while (rSet.next()) {
 
									for (int j = 1; j <= colCnt; j++) {
 
										if (value.toString().equals(
												rSet.getString(j))) {
											setBackground(Color.RED);
										} else {
											setBackground(Color.GREEN);
										}
										return this;
									}
								}
							} catch (SQLException e1) {
								e1.printStackTrace();
							}
							return c;
						}
					});
			/******************************************/
 
		} catch (ClassNotFoundException e) {
			System.err.println("DB-Driver not found!");
			System.exit(1);
		} catch (SQLException e) {
			System.err.println("SQL-Error:" + e.getMessage());
			System.exit(1);
		}
 
		f.add(new JScrollPane(table));
 
		f.setSize(100, 345);
		f.setVisible(true);
	}
}

Java Quellcode

rSet.beforeFirst();
       while (rSet.next()) {
         for (int j = 1; j <= colCnt; j++) {
 
		if (value.toString().equals(rSet.getString(j))) {
				setBackground(Color.RED);
			} else {
			        setBackground(Color.GREEN);
				}
			       return this;
				}
		}