import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.*;
import javax.swing.*;
import java.lang.String;
public class GuiCardLayout implements ActionListener{
final static String CREATETABLE = "Tabelle erstellen";
final static String SELECTDATA = "Daten selektieren";
final static String EXITPROGRAM = "Programm beenden";
JTextField tablenameField;
JFormattedTextField vonField;
JTextField numberField;
JPanel cards;
public void createLayout(Container main){
//create card 1
JLabel tablenameLabel = new JLabel("Bitte geben Sie den Namen der Tabelle an:");
tablenameField = new JTextField();
JPanel textPanel = new JPanel();
textPanel.setLayout(new BoxLayout(textPanel, BoxLayout.PAGE_AXIS));
textPanel.add(tablenameLabel, BorderLayout.LINE_START);
textPanel.add(Box.createRigidArea(new Dimension(0,10)));
textPanel.add(tablenameField, BorderLayout.LINE_START);
JButton cancelButton = new JButton ("Cancel");
//Listen to events from the Cancel button.
cancelButton.addActionListener(this);
JButton okButton = new JButton (CREATETABLE);
//Listen to events from the Ok button.
okButton.addActionListener(this);
JPanel buttonPanel = new JPanel();
buttonPanel.setLayout(new BoxLayout(buttonPanel, BoxLayout.LINE_AXIS));
//Add the widgets to the container.
buttonPanel.add(cancelButton);
buttonPanel.add(Box.createRigidArea(new Dimension(5,0)));
buttonPanel.add(okButton);
JPanel card1Panel = new JPanel();
card1Panel.setLayout(new BoxLayout(card1Panel, BoxLayout.PAGE_AXIS));
card1Panel.add(textPanel);
card1Panel.add(Box.createRigidArea(new Dimension(0,10)));
card1Panel.add(buttonPanel);
//create card 2
JLabel dateLabel = new JLabel("<html>Geben Sie hier bitte den Datumsbereich ein, aus dem Sie die Datensätze auswählen wollen: (Format JJJJ-MM-TT)</html>");
JLabel vonLabel = new JLabel("Von:");
DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
vonField = new JFormattedTextField(formatter);
JPanel vonPanel = new JPanel();
vonPanel.setLayout(new BoxLayout(vonPanel, BoxLayout.LINE_AXIS));
vonPanel.add(vonLabel, BorderLayout.LINE_START);
vonPanel.add(Box.createRigidArea(new Dimension(5,0)));
vonPanel.add(vonField);
JPanel datePanel = new JPanel();
datePanel.setLayout(new BoxLayout(datePanel, BoxLayout.PAGE_AXIS));
datePanel.add(dateLabel, BorderLayout.PAGE_START);
datePanel.add(Box.createRigidArea(new Dimension(0,5)));
datePanel.add(vonPanel);
JLabel numberLabel = new JLabel("Geben Sie hier die gewünschte Anzahl Datensätze an: ");
numberField = new JTextField();
JPanel numberPanel = new JPanel();
numberPanel.setLayout(new BoxLayout(numberPanel, BoxLayout.LINE_AXIS));
numberPanel.add(numberLabel, BorderLayout.LINE_START);
numberPanel.add(numberField);
JButton cancelButton2 = new JButton ("Cancel");
cancelButton2.addActionListener(this);
JButton okButton2 = new JButton (SELECTDATA);
okButton2.addActionListener(this);
JPanel buttonPanel2 = new JPanel();
buttonPanel2.setLayout(new BoxLayout(buttonPanel2, BoxLayout.LINE_AXIS));
buttonPanel2.add(cancelButton2);
buttonPanel2.add(Box.createRigidArea(new Dimension(5,0)));
buttonPanel2.add(okButton2);
JPanel card2Panel = new JPanel();
card2Panel.setLayout(new BoxLayout(card2Panel, BoxLayout.PAGE_AXIS));
card2Panel.add(datePanel);
card2Panel.add(Box.createRigidArea(new Dimension(0,10)));
card2Panel.add(numberPanel);
card2Panel.add(Box.createRigidArea(new Dimension(0,10)));
card2Panel.add(buttonPanel2);
//create card 3
JButton exitButton = new JButton(EXITPROGRAM);
exitButton.addActionListener(this);
JPanel card3Panel = new JPanel();
card3Panel.add(new JLabel("Die Tabelle wurde erfolgreich erstellt und mit Daten befüllt!"));
card3Panel.add(exitButton);
//add all cardPanels
cards = new JPanel(new CardLayout());
cards.add(card1Panel, CREATETABLE);
cards.add(card2Panel, SELECTDATA);
cards.add(card3Panel, EXITPROGRAM);
main.add(cards);
}//end function createLayout
public void selectData( int anz, String tablename){
try{
Class.forName("com.mysql.jdbc.Driver");
}
catch (ClassNotFoundException e)
{
System.out.println("Fehler beim Zugriff auf Klasse aufgetreten:" +e);
return;
}
Connection con;
Statement stmt;
Statement stmt2;
Statement stmt3;
Statement stmt4;
ResultSet rSet_sessions;
ResultSet rSet_pages;
try
{
String url = "jdbc:mysql://localhost/mylocal";
con = DriverManager.getConnection( url, "myname", "mypwd" );
stmt = con.createStatement();
stmt2 = con.createStatement();
stmt3 = con.createStatement();
stmt4 = con.createStatement();
String sqlselect_sessions = "select DISTINCT (session_id) from tracking_log_stefanmay where timestamp > '"+ vonField.getText() +"' LIMIT 0,"+ anz +";";
rSet_sessions = stmt.executeQuery( sqlselect_sessions );
int i = 0;
while (rSet_sessions.next()){
//System.out.println("Ich bin in der while-Schleife!!");
System.out.println( (i+1) + ". session_id : " + rSet_sessions.getString(1) + "\n");
String sqlinsert = "insert into "+ tablename +" (session_id) values ('"+ rSet_sessions.getString(1) +"');";
stmt2.executeUpdate(sqlinsert);
String sqlinsertNull = "update "+ tablename +" SET home=0, archive_login=0, archive_photo=0, bm_calendars_2006_2=0, bm_calendars_2006_3=0 where session_id = '"+ rSet_sessions.getString(1) +"';";
stmt4.executeUpdate(sqlinsertNull);
String sqlselect_pages = "select distinct(ressource_id) from tracking_log_stefanmay where session_id='"+ rSet_sessions.getString(1) +"';";
rSet_pages = stmt3.executeQuery(sqlselect_pages);
while (rSet_pages.next()){
if (rSet_pages.getString(1).equals("home")){
String sqlupdate = "update "+ tablename +" SET home=1 where session_id = '"+ rSet_sessions.getString(1) +"';";
stmt2.executeUpdate(sqlupdate);
}
if (rSet_pages.getString(1).equals("archive_login")){
String sqlupdate = "update "+ tablename +" SET archive_login=1 where session_id = '"+ rSet_sessions.getString(1) +"';";
stmt2.executeUpdate(sqlupdate);
}
// ... usw.
if (rSet_pages.getString(1).equals("bm_calendars_2006_3")){
String sqlupdate = "update "+ tablename +" SET bm_calendars_nyc2006_3=1 where session_id = '"+ rSet_sessions.getString(1) +"';";
stmt2.executeUpdate(sqlupdate);
}
}//end while rSet_pages
i++;
}//end while rSet_sessions
stmt.close();
stmt2.close();
stmt3.close();
stmt4.close();
con.close();
}//end try
catch ( SQLException e )
{
System.out.println( "Fehler bei Tabellenabfrage" + e );
return;
}
}//end selectData
public void actionPerformed (ActionEvent event) {
CardLayout card = (CardLayout)cards.getLayout();
if ("Cancel".equals(event.getActionCommand()) | EXITPROGRAM.equals(event.getActionCommand()) ){
System.exit(0);
}
else{
if (CREATETABLE.equals(event.getActionCommand())){
System.out.println("Sie haben den Knopf von Tabelle erstellen gedrückt");
String tableName = tablenameField.getText();
try
{
Class.forName("com.mysql.jdbc.Driver");
}
catch (ClassNotFoundException e)
{
System.out.println("Fehler beim Zugriff auf Klasse aufgetreten:" +e);
return;
}
Connection con;
Statement stmt;
try
{
String url = "jdbc:mysql://localhost/mylocal";
con = DriverManager.getConnection( url, "myname", "mypwd" );
stmt = con.createStatement();
String sqlcreate = "create table "+ tableName +" (session_id varchar(100) PRIMARY KEY, " +
"home binary(1)," +
"newsletter_form binary(1)," +
"bm_books_nyc binary(1)," +
"bm_calendars_2005_1 binary(1)," +
// und so weiter...
"bm_calendars_2006_2 binary(1)," +
"bm_calendars_2006_3 binary(1)" +
");";
stmt.executeUpdate(sqlcreate);
stmt.close();
con.close();
}//end try
catch ( SQLException e )
{
System.out.println( "Fehler bei Tabellenabfrage" + e );
return;
}//end catch
card.show(cards, SELECTDATA);
}//end if
else {
System.out.println("Sie haben den Knopf von select data gedrückt");
int num = Integer.parseInt(numberField.getText());
System.out.println("Anzahl: "+num );
String table = tablenameField.getText();
System.out.println("Tabellenname: "+table );
selectData(num, table);
card.show(cards, EXITPROGRAM);
}
}
}//end function actionPerformed
public static void main (String[] args) {
JFrame f = new JFrame("Create and Fill Table");
f.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
f.setBounds(100, 100, 400, 200);
GuiCardLayout gui = new GuiCardLayout();
gui.createLayout(f.getContentPane());
f.setVisible(true);
}
}
