// Zugriff auf eine erstellte Datenbank , mit hilfe einer // ODBC - JDBC Bridge // im zuge unseres Projektes // proudly presented by us and some other jerkheads who don't know what to do // with their time - enjoy :) /******************************************************************************/ /* so ich hab mich heut drüber gewagt das stückerl code da zu kommentieren und*/ /* ein wenig einzurücken - lesbarkeit und so - da ihn dies nicht schöner macht*/ /* und schon gar nicht funktionsfähiger war das sowieso verschissene zeit aber*/ /* was solls - lesbarkeit und dokumentation is ja das wichtigste :) */ /* da dies von 2 leuten geschrieben wurde und wir uns bis dato noch nicht auf */ /* eine einheitliche namenskonvention geeinigt haben könnts ein wenig störend */ /* sein das zu lesen und so zeux halt ... aber ich will euch nicht davon ab- */ /* halten es zu versuchen - is ja net sooooo schwer - hässlich aber nicht alzu*/ /* schwer ... falls wir zeit und motivation finden werden wir das da noch ein */ /* wenig (viel) optimieren und vielleicht ... ziemlich sicher als open-source */ /* zur verfügung stellen - bei anregungen beschwerden problemen etc. selbst */ /* schlau machen ;-) */ /* auf alle fälle sind ein paar funktionen ein wenig kommentiert - ich hoff */ /* das ihr den rest und die paar auch ohne kommentar verstehts - tipp am rande*/ /* 2l cola auf ex und ihr verstehts alles :) */ /******************************************************************************/ // Copyright (c)2002 by Benak and Schratzberger package KinoDB; import java.sql.*; import javax.swing.*; import java.awt.*; import java.awt.event.*; import java.util.*; import java.io.Serializable; import java.io.*; public class KinoDB extends JFrame implements MouseListener, MouseMotionListener { //todesvariable int i=0; // java.sql types needed for database processing private Connection connection; private Statement statement; private ResultSet resultSet; private ResultSetMetaData rsMetaData; ResultSetMetaData rsmd2; ResultSet rs2; //*************************************************************************** private JTable table; private JTable tableall; private JTextArea inputQuery; private JButton submitQuery; //*************************************************************************** JMenu menu; //*************************************************************************** JTextArea current; String s = ""; String s1 = ""; String s2 = "Select 'Successfully Connected'"; String oldstring = ""; String tableglobal = ""; boolean harr = true; //*************************************************************************** JMenuBar bar; JMenuItem suchenI; String searchString = ""; String updateString = ""; String deleteString = ""; createTable newtab; JTextField keyfield; //*************************************************************************** JFrame suchen; JFrame idyna; JFrame searchFrame; JFrame aboutframe; JFrame helpframe; JLabel sfilm; JLabel sgenre; JTextField textsfilm; JTextField textsgenre; JTextField textsadresse; JTextField searchtext; JTextField tstable2; JPanel searchPanel; JTextField updatetext; //*************************************************************************** JMenu datei; JMenu help; JMenuItem about; JMenuItem helpitem; JMenu statementmenu; JMenuItem save; JMenuItem load; JMenu bearbeiten; JMenuItem recent; JMenu einfuegen; JMenuItem createtable; JMenuItem intotable; JMenuItem film; JMenuItem genre; JMenu loeschen; JMenuItem drop; JMenu updatemenu; JMenuItem update; JMenu tablemenu; JMenu view; JMenuItem databases; JMenuItem openalltables; JMenuItem opentable; JMenuItem opentablemeta; JMenuItem menudtable; JMenu connectionmenu; JMenuItem verbneu; JMenuItem beenden; //*************************************************************************** ObjectOutputStream output; ObjectInputStream input; //*************************************************************************** JTextField tfilm = new JTextField(); JTextField tlaenge = new JTextField(); JTextField tbewertung = new JTextField(); JTextField tinhalt = new JTextField(); JTextField tgenre = new JTextField(); JTextField tlfilm = new JTextField(); JTextField tstable; JTextField idynatext; JTextField ddynatext; JTextField deleteField; JTextField uname = new JTextField(); JTextField odbc = new JTextField(); JPasswordField pword = new JPasswordField(); //*************************************************************************** JTextField textarray[]; JFrame ts; //*************************************************************************** FrameHandler fhandler = new FrameHandler(); final JPopupMenu pop = new JPopupMenu(); JMenuItem popdaten; JMenuItem popmeta; JMenuItem popeinfuegen; int mouseclick=0; int fCount = 0; String sqlbackup[] = new String[1000]; int isClosed[] = new int[1000]; //String tabletest = ""; JFrame farray[] = new JFrame[1000]; JFrame statusFrame; JTextArea status = new JTextArea(50,50); Container sf; SqlSave sqlStatement; //********************* ein konstruktor - schau schau *********************** public KinoDB(String odbc, String uname,String pword) { super( "Database Management System 1.0 Beta" ); statusFrame = new JFrame("Status"); sf = statusFrame.getContentPane(); sf.add(status); statusFrame.setSize(550,60); statusFrame.setLocation(0,500); statusFrame.show(); sqlStatement = new SqlSave(); //********* Versuch einer verbindung zu einer odbc datenquelle ********* try { String url = "jdbc:odbc:" + odbc; String username = uname; String password = pword; Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" ); connection = DriverManager.getConnection( url, username, password ); } catch ( ClassNotFoundException cnfex ) { System.err.println("Failed to load JDBC/ODBC driver." ); cnfex.printStackTrace(); System.exit( 1 ); // terminate program } catch ( SQLException sqlex ) { System.err.println( "Unable to connect" ); sqlex.printStackTrace(); System.exit( 1 ); // terminate program } //************************************************************************ inputQuery = new JTextArea( s2, 4, 30 ); // Button der die Abfrage abschickt // submitQuery = new JButton( "Submit Query" ); submitQuery.addActionListener( new ActionListener() { public void actionPerformed( ActionEvent e ) { if ((inputQuery.getText().trim().startsWith("s")) || (inputQuery.getText().trim().startsWith("S"))) { if (harr) { s = inputQuery.getText(); harr = false; current.setText(s1); status.setText(s); } else { s1 = inputQuery.getText(); harr = true; current.setText(s); status.setText(s1); } getTable(); } else { if (inputQuery.getText().trim().toLowerCase().startsWith("dropdatabase")) { try { statement.execute(inputQuery.getText()); } catch(SQLException sqlex) { sqlex.printStackTrace(); } } else { try { if (harr) { s = inputQuery.getText(); harr = false; current.setText(s1); status.setText(s); } else { s1 = inputQuery.getText(); harr = true; current.setText(s); status.setText(s1); } statement.executeUpdate(inputQuery.getText()); } catch (SQLException sqlex) { sqlex.printStackTrace(); } } } refreshFrame(); } } ); //************************************************************************ current = new JTextArea(); recent = new JMenuItem("Last Command"); //************************************************************************ //*********************** MenuItem für UNDO ****************************** //******* muss noch sehr erweitert werden zwecks dings und zeux halt ***** //************************************************************************ recent.addActionListener( new ActionListener() { public void actionPerformed( ActionEvent e ) { if (harr) { harr = false; current.setText(s1); status.setText(s); inputQuery.setText(s1); } else { harr = true; current.setText(s); status.setText(s1); inputQuery.setText(s); } getTable(); } } ); //************************************************************************ // SET-UP GUI du bloeder Wizzard-Wichser JPanel topPanel = new JPanel(); topPanel.setLayout( new BorderLayout() ); topPanel.add( new JScrollPane( inputQuery), BorderLayout.NORTH ); topPanel.add( submitQuery, BorderLayout.SOUTH ); topPanel.add( current, BorderLayout.CENTER); table = new JTable( 4, 4 ); Container c = getContentPane(); menu = new JMenu("Search"); bar = new JMenuBar(); setJMenuBar( bar ); suchenI = new JMenuItem("in Table"); datei = new JMenu("File"); connectionmenu = new JMenu("Database"); verbneu = new JMenuItem("Connect New"); beenden = new JMenuItem("Quit"); statementmenu = new JMenu("Statement"); save = new JMenuItem("Save"); load = new JMenuItem("Load"); bearbeiten = new JMenu("Edit"); einfuegen = new JMenu("Insert"); loeschen = new JMenu("Delete"); menudtable = new JMenuItem("from Table"); drop = new JMenuItem("Drop Table"); tablemenu = new JMenu("Table"); view = new JMenu("View"); databases = new JMenuItem("all Databases"); openalltables = new JMenuItem("all Tables"); opentablemeta = new JMenuItem("Table-Metadata"); opentable = new JMenuItem("Table-Data"); intotable = new JMenuItem("into Table"); createtable = new JMenuItem("Create Table"); help = new JMenu("Help"); helpitem = new JMenuItem("Help"); about = new JMenuItem("About"); updatemenu = new JMenu("Update"); update = new JMenuItem("Dataset"); popdaten = new JMenuItem("Daten"); popmeta = new JMenuItem("Metadaten"); popeinfuegen = new JMenuItem("Einfügen"); PopHandler handler = new PopHandler(); pop.add(popdaten); pop.add(popmeta); pop.add(popeinfuegen); popdaten.addActionListener(handler); popmeta.addActionListener(handler); popeinfuegen.addActionListener(handler); //************************************************************************ bar.add(datei); datei.add(connectionmenu); connectionmenu.add(verbneu); datei.add(statementmenu); statementmenu.add(save); statementmenu.add(load); datei.add(beenden); bar.add(view); view.add(databases); tablemenu.add(openalltables); tablemenu.add(opentable); tablemenu.add(opentablemeta); view.add(tablemenu); bar.add(bearbeiten); bar.add(einfuegen); bar.add(loeschen); bar.add(updatemenu); loeschen.add(menudtable); loeschen.add(drop); bearbeiten.add(recent); bar.add(menu); menu.add(suchenI); einfuegen.add(intotable); einfuegen.add(createtable); bar.add(help); help.add(helpitem); help.add(about); updatemenu.add(update); //************************************************************************ beenden.addActionListener( new ActionListener() { public void actionPerformed (ActionEvent e) { shutDown(); System.exit(0); } } ); //************************************************************************ drop.addActionListener( new ActionListener() { public void actionPerformed (ActionEvent e) { final JFrame sframe = new JFrame("Select Table"); JPanel spanel = new JPanel(new GridLayout(1,1)); final JTextField sfield = new JTextField(); Container h = sframe.getContentPane(); makeTableFrameAll(); spanel.add(new JLabel("Table: ")); spanel.add(sfield); h.add(spanel,BorderLayout.NORTH); sfield.addActionListener( new ActionListener() { public void actionPerformed (ActionEvent e) { String dropString = "Drop Table " + sfield.getText(); try{ statement = connection.createStatement(); statement.executeUpdate(dropString); refreshFrame(); sframe.hide(); } catch(SQLException sqlex){ sqlex.printStackTrace(); } } } ); refreshFrame(); sframe.setSize(200,100); sframe.show(); status.setText("Drop a table"); } } ); //************************************************************************ update.addActionListener( new ActionListener() { public void actionPerformed (ActionEvent e) { final JFrame selecttable = new JFrame("Select Table"); final JPanel selectpanel = new JPanel(new GridLayout(1,1)); updatetext = new JTextField(); Container r = selecttable.getContentPane(); selecttable.setSize(200,100); selectpanel.add(new JLabel("Table: ")); selectpanel.add(updatetext); r.add(selectpanel,BorderLayout.NORTH); status.setText("Update a table"); selecttable.show(); updatetext.addActionListener( new ActionListener() { public void actionPerformed (ActionEvent e) { try{ UpdateListener updateHandler = new UpdateListener(); JButton sbutton = new JButton("Update"); JFrame selectatrib = new JFrame("Update Table " + updatetext.getText()); keyfield = new JTextField(); String query = "Select * from "+updatetext.getText(); statement = connection.createStatement(); rs2 = statement.executeQuery( query ); rsmd2 = rs2.getMetaData(); JPanel spanel = new JPanel(new GridLayout(rsmd2.getColumnCount() + 1,1)); Container h = selectatrib.getContentPane(); selectatrib.setSize(400,200); selectatrib.setLocation(550,300); spanel.add(new JLabel("Key (of changing set): ")); spanel.add(keyfield); //keyfield.addActionListener(updateHandler); h.add(spanel,BorderLayout.NORTH); h.add(sbutton,BorderLayout.SOUTH); textarray = new JTextField[rsmd2.getColumnCount()]; for ( int j = 1; j <= rsmd2.getColumnCount(); j++ ) { // if(rsmd2.isAutoIncrement(i) == false) // { spanel.add(new JLabel(rsmd2.getColumnName(j)+" - "+rsmd2.getColumnTypeName(j))); textarray[j-1] = new JTextField(); textarray[j-1].addActionListener(updateHandler); spanel.add(textarray[j-1]); //} // JOptionPane.showMessageDialog(null,textarray[j-1].getText()); //else // start = i; } sbutton.addActionListener(updateHandler); makeTableFrame(updatetext.getText(),550,0); selecttable.hide(); selectatrib.show(); } catch(SQLException sqlex){ sqlex.printStackTrace(); } } } ); refreshFrame(); } } ); //************************************************************************ helpitem.addActionListener( new ActionListener() { public void actionPerformed (ActionEvent e) { helpframe = new JFrame("Help"); JPanel helppanel = new JPanel(new BorderLayout()); JButton helpbutton = new JButton("Ok"); Container fc = helpframe.getContentPane(); helppanel.add(new JLabel(" Database Management System 1.0 Beta"),BorderLayout.NORTH); helppanel.add(new JLabel("Wildcard in SQL is % "),BorderLayout.CENTER); helppanel.add(new JLabel("Rechnen wird nicht unterstützt vom ODBC"),BorderLayout.SOUTH); fc.add(helpbutton,BorderLayout.SOUTH); fc.add(helppanel,BorderLayout.CENTER); helpframe.setSize(362,150); helpframe.setLocation(400,350); helpframe.show(); helpbutton.addActionListener( new ActionListener() { public void actionPerformed (ActionEvent e) { helpframe.hide(); } } ); } } ); //************************************************************************ about.addActionListener( new ActionListener() { public void actionPerformed (ActionEvent e) { aboutframe = new JFrame("About"); JPanel aboutpanel = new JPanel(new BorderLayout()); JButton aboutbutton = new JButton("Ok"); Container fc = aboutframe.getContentPane(); aboutpanel.add(new JLabel(" Database Management System 1.0 Beta"),BorderLayout.NORTH); aboutpanel.add(new JLabel(" Copyright (c) 2002 by Robert Benak & Stephan Schratzberger"),BorderLayout.CENTER); fc.add(aboutbutton,BorderLayout.SOUTH); fc.add(aboutpanel,BorderLayout.CENTER); aboutframe.setSize(362,150); aboutframe.setLocation(400,350); aboutframe.show(); aboutbutton.addActionListener( new ActionListener() { public void actionPerformed (ActionEvent e) { aboutframe.hide(); } } ); } } ); //************************************************************************ opentable.addActionListener( new ActionListener() { public void actionPerformed( ActionEvent e ) { ts = new JFrame("Select Table"); ts.setSize(200,100); tstable = new JTextField(); Container f = ts.getContentPane(); JPanel tsp = new JPanel(new GridLayout(1,1)); tsp.add(new JLabel("Table:")); tsp.add(tstable); f.add(tsp,BorderLayout.NORTH); ts.show(); tstable.addActionListener( new ActionListener() { public void actionPerformed( ActionEvent e ) { JFrame showtable = new JFrame(tstable.getText()); showtable.setSize(300,300); showtable.setLocation(550,0); showtable.show(); Container j = showtable.getContentPane(); oldstring = inputQuery.getText(); String showString = ""; showString = "Select * from "+tstable.getText(); inputQuery.setText(showString); getTableFrame(j,tstable.getText()); inputQuery.setText(oldstring); tstable.setText(""); ts.hide(); } } ); } } ); //*********************************************************************** createtable.addActionListener( new ActionListener() { public void actionPerformed( ActionEvent e ) { status.setText("Create a new Table"); newtab = new createTable(); newtab.addWindowListener( new WindowAdapter() { public void windowClosed( WindowEvent e ) { if (newtab.ready == 1) { inputQuery.setText(newtab.getCreateString()); setTable(); inputQuery.setText(""); } } } ); refreshFrame(); } } ); //*********************************************************************** verbneu.addActionListener( new ActionListener() { public void actionPerformed( ActionEvent e ) { final Logon log2 = new Logon(); final KinoDB kino2 = null; log2.logb.addActionListener( new ActionListener() { public void actionPerformed( ActionEvent e ) { kino2 = new KinoDB(log2.getUrl(),log2.getUname(),log2.getPword()); log2.hide(); } } ); } } ); //*********************************************************************** openalltables.addActionListener( new ActionListener() { public void actionPerformed (ActionEvent e) { makeTableFrameAll(); } } ); //*********************************************************************** databases.addActionListener( new ActionListener() { public void actionPerformed (ActionEvent e) { makeDatabasesFrame(); } } ); //*********************************************************************** opentablemeta.addActionListener( new ActionListener() { public void actionPerformed( ActionEvent e ) { ts = new JFrame("Select Table"); ts.setSize(200,100); tstable2 = new JTextField(); Container f = ts.getContentPane(); JPanel tsp = new JPanel(new GridLayout(1,1)); tsp.add(new JLabel("Table:")); tsp.add(tstable2); f.add(tsp,BorderLayout.NORTH); ts.show(); tstable2.addActionListener( new ActionListener() { public void actionPerformed( ActionEvent e ) { makeTableFrameMeta(tstable2.getText(),550,0); } } ); } } ); //*********************************************************************** intotable.addActionListener( new ActionListener() { public void actionPerformed( ActionEvent e ) { idyna = new JFrame("Select Table"); idyna.setSize(200,100); idynatext = new JTextField(); Container f = idyna.getContentPane(); JPanel idynap = new JPanel(new GridLayout(1,1)); idynap.add(new JLabel("Table:")); idynap.add(idynatext); f.add(idynap,BorderLayout.NORTH); idyna.show(); idynatext.addActionListener( new ActionListener() { public void actionPerformed( ActionEvent e ) { try { JFrame dynatab = new JFrame("Insert into "+idynatext.getText()); JButton beinfuegen = new JButton("INSERT"); dynatab.setSize(400,200); dynatab.setLocation(550,300); Container k = dynatab.getContentPane(); makeTableFrame(idynatext.getText(),550,0); String query = "Select * from "+idynatext.getText(); statement = connection.createStatement(); rs2 = statement.executeQuery( query ); rsmd2 = rs2.getMetaData(); textarray = new JTextField[rsmd2.getColumnCount()]; JPanel dynap = new JPanel(new GridLayout(rsmd2.getColumnCount(),1)); for ( int i = 1; i <= rsmd2.getColumnCount(); ++i ) { if(rsmd2.isAutoIncrement(i) == false) { dynap.add(new JLabel(rsmd2.getColumnName(i)+" - "+rsmd2.getColumnTypeName(i))); textarray[i-1] = new JTextField(); dynap.add(textarray[i-1]); } } k.add(dynap,BorderLayout.NORTH); k.add(beinfuegen,BorderLayout.SOUTH); dynatab.show(); idyna.hide(); beinfuegen.addActionListener( new ActionListener() { public void actionPerformed( ActionEvent e ) { String insertString = ""; int i = 0; insertString = "INSERT INTO "+idynatext.getText()+" (" ; try { while ( i < rsmd2.getColumnCount() ) { if (rsmd2.isAutoIncrement (i+1) == false) { if ( i != rsmd2.getColumnCount() - 1) insertString += rsmd2.getColumnName(i+1)+","; else insertString += rsmd2.getColumnName(i+1); } i++; } insertString += ") VALUES ("; i=0; while ( i < rsmd2.getColumnCount() ) { if (rsmd2.isAutoIncrement(i+1) == false) { if (i < rsmd2.getColumnCount()-1 ) if (rsmd2.getColumnType(i+1) == Types.VARCHAR || rsmd2.getColumnType(i+1) == Types.LONGVARCHAR || rsmd2.getColumnType(i+1) == Types.DATE || rsmd2.getColumnType(i+1) == Types.DECIMAL || rsmd2.getColumnType(i+1) == Types.CHAR) insertString += "'"+textarray[i].getText()+"',"; else insertString +=textarray[i].getText()+","; else if (rsmd2.getColumnType(i+1) == Types.VARCHAR || rsmd2.getColumnType(i+1) == Types.LONGVARCHAR || rsmd2.getColumnType(i+1) == Types.DATE || rsmd2.getColumnType(i+1) == Types.DECIMAL || rsmd2.getColumnType(i+1) == Types.CHAR) insertString += "'"+textarray[i].getText()+"'"; else insertString +=textarray[i].getText(); } i++; } insertString += ")"; } catch (SQLException sqlex) { sqlex.printStackTrace(); } inputQuery.setText(insertString); setTable(); inputQuery.setText("select * from "+idynatext.getText()); getTable(); refreshFrame(); inputQuery.setText(""); } } ); } catch ( SQLException sqlex ) { sqlex.printStackTrace(); } refreshFrame(); } } ); } } ); //************************************************************************ popeinfuegen.addActionListener( new ActionListener() { public void actionPerformed( ActionEvent e ) { if (mouseclick == 1) { try { makeTableFrame(tableglobal,550,0); JFrame dynatab = new JFrame(tableglobal +" einfügen"); dynatab.setLocation(550,300); JButton beinfuegen = new JButton("INSERT"); dynatab.setSize(300,200); Container k = dynatab.getContentPane(); String query = "Select * from "+tableglobal; statement = connection.createStatement(); rs2 = statement.executeQuery( query ); rsmd2 = rs2.getMetaData(); textarray = new JTextField[rsmd2.getColumnCount()]; JPanel dynap = new JPanel(new GridLayout(rsmd2.getColumnCount(),1)); for ( int i = 1; i <= rsmd2.getColumnCount(); ++i ) { if(rsmd2.isAutoIncrement(i) == false) { dynap.add(new JLabel(rsmd2.getColumnName(i)+" ("+rsmd2.getColumnTypeName(i)+")")); textarray[i-1] = new JTextField(); dynap.add(textarray[i-1]); } } k.add(dynap,BorderLayout.NORTH); k.add(beinfuegen,BorderLayout.SOUTH); dynatab.show(); beinfuegen.addActionListener( new ActionListener() { public void actionPerformed( ActionEvent e ) { String insertString = ""; int i = 0; insertString = "INSERT INTO "+tableglobal+" (" ; try { while ( i < rsmd2.getColumnCount() ) { if (rsmd2.isAutoIncrement(i+1) == false) { if ( i != rsmd2.getColumnCount() - 1) insertString += rsmd2.getColumnName(i+1)+","; else insertString += rsmd2.getColumnName(i+1); } i++; } insertString += ") VALUES ("; i=0; while ( i < rsmd2.getColumnCount() ) { if (rsmd2.isAutoIncrement(i+1) == false) { if (i < rsmd2.getColumnCount()-1 ) if (rsmd2.getColumnType(i+1) == Types.VARCHAR || rsmd2.getColumnType(i+1) == Types.LONGVARCHAR || rsmd2.getColumnType(i+1) == Types.DECIMAL) insertString += "'"+textarray[i].getText()+"',"; else insertString +=textarray[i].getText()+","; else if (rsmd2.getColumnType(i+1) == Types.VARCHAR || rsmd2.getColumnType(i+1) == Types.LONGVARCHAR || rsmd2.getColumnType(i+1) == Types.DECIMAL) insertString += "'"+textarray[i].getText()+"'"; else insertString +=textarray[i].getText(); } i++; } insertString += ")"; //inputQuery.setText(insertString); i = 0; } catch (SQLException sqlex) { sqlex.printStackTrace(); } inputQuery.setText(insertString); setTable(); inputQuery.setText("select * from "+idynatext.getText()); getTable(); refreshFrame(); inputQuery.setText(""); } } ); //columnHeads.addElement( rsmd.getColumnName( i ) ); } catch ( SQLException sqlex ) { sqlex.printStackTrace(); } } } // } ); //************************************************************************ menudtable.addActionListener( new ActionListener() { public void actionPerformed(ActionEvent e){ JFrame ddyna = new JFrame("Select Table"); ddyna.setSize(200,100); ddynatext = new JTextField(); Container f = ddyna.getContentPane(); JPanel ddynap = new JPanel(new GridLayout(1,1)); ddynap.add(new JLabel("Table:")); ddynap.add(ddynatext); f.add(ddynap,BorderLayout.NORTH); ddyna.show(); ddynatext.addActionListener( new ActionListener() { public void actionPerformed(ActionEvent e) { try { JFrame ddynatab = new JFrame("Delete "+ddynatext.getText()); JButton bdelete = new JButton("Delete"); JPanel ddynap2 = new JPanel(new GridLayout(1,1)); deleteField = new JTextField(); ddynap2.add(new JLabel("Key: ")); ddynap2.add(deleteField); ddynatab.setSize(300,100); ddynatab.setLocation(550,300); Container k = ddynatab.getContentPane(); String query = "Select * from "+ddynatext.getText(); statement = connection.createStatement(); rs2 = statement.executeQuery( query ); rsmd2 = rs2.getMetaData(); k.add(ddynap2,BorderLayout.NORTH); k.add(bdelete,BorderLayout.SOUTH); bdelete.addActionListener( new ActionListener() { public void actionPerformed(ActionEvent e) { try { deleteString = "DELETE FROM "+ddynatext.getText()+" WHERE "+ rsmd2.getColumnName(1) +"="+deleteField.getText(); statement.executeUpdate(deleteString); } catch (SQLException sqlex){ sqlex.printStackTrace(); System.exit(0); } refreshFrame(); } } ); makeTableFrame(ddynatext.getText(),550,0); ddynatab.show(); } catch (SQLException sqlex) { sqlex.printStackTrace(); } } } ); } } ); load.addActionListener( new ActionListener() { public void actionPerformed( ActionEvent e ) { loadFile(); loadObjectUnser(); } } ); save.addActionListener( new ActionListener() { public void actionPerformed( ActionEvent e ) { saveFile(); } } ); //***************** Handler für Suchfunktionen *************************** suchenI.addActionListener( new ActionListener() { public void actionPerformed( ActionEvent e ) { suchen = new JFrame("Search"); Container d = suchen.getContentPane(); searchPanel = new JPanel(new GridLayout(1,1)); searchtext = new JTextField(); searchPanel.add(new JLabel("Table: ")); searchPanel.add(searchtext); d.add(searchPanel,BorderLayout.NORTH); searchtext.addActionListener( new ActionListener() { public void actionPerformed( ActionEvent e ) { try { searchFrame = new JFrame("Insert Attributes"); JButton searchButton = new JButton("Search"); SearchListener searchHandler = new SearchListener(); searchFrame.setSize(300,200); searchFrame.setLocation(550,300); Container k = searchFrame.getContentPane(); String query = "Select * from "+searchtext.getText(); //todesschalter=0; statement = connection.createStatement(); rs2 = statement.executeQuery( query ); rsmd2 = rs2.getMetaData(); textarray = new JTextField[rsmd2.getColumnCount()]; JPanel dynap3 = new JPanel(new GridLayout(rsmd2.getColumnCount(),1)); for ( i = 1; i <= rsmd2.getColumnCount(); ++i ) { dynap3.add(new JLabel(rsmd2.getColumnName(i))); textarray[i-1] = new JTextField(); textarray[i-1].addActionListener(searchHandler); dynap3.add(textarray[i-1]); } k.add(dynap3,BorderLayout.NORTH); k.add(searchButton,BorderLayout.SOUTH); searchFrame.show(); suchen.hide(); searchButton.addActionListener(searchHandler); } catch (SQLException sqlex) { sqlex.printStackTrace(); } } } ); suchen.setSize(200,100); suchen.show(); } } ); current.setEditable(false); c.setLayout( new BorderLayout() ); c.add( topPanel, BorderLayout.NORTH ); c.add( table, BorderLayout.CENTER ); getTable(); setSize( 550, 500 ); show(); } //*************************************************************************** private void makeTableFrame(String table,int x,int y) { //JFrame showtable = new JFrame(table); farray[fCount] = new JFrame(table); farray[fCount].setSize(400,300); farray[fCount].setLocation(x,y); farray[fCount].show(); Container j = farray[fCount].getContentPane(); oldstring = inputQuery.getText(); String showString = ""; showString = "Select * from "+table; sqlbackup[fCount] = showString; inputQuery.setText(showString); getTableFrame(j,table); inputQuery.setText(oldstring); fCount++; } //*************************************************************************** private void refreshFrame() { int i=0; Container temp; while (i < fCount) { if (isClosed[i] != 1) { temp = new Container(); farray[i].hide(); farray[i] = new JFrame(farray[i].getTitle()); farray[i].addWindowListener(fhandler); farray[i].setSize(400,300); farray[i].setLocation(550,0); temp=farray[i].getContentPane(); inputQuery.setText(sqlbackup[i]); getTableFrame(temp,""); inputQuery.setText(""); farray[i].show(); } i++; } } //*************************************************************************** private void makeTableFrameAll() { //JFrame showtable = new JFrame("All Tables"); farray[fCount] = new JFrame("All Tables"); farray[fCount].setSize(400,300); farray[fCount].setLocation(550,0); farray[fCount].show(); Container j = farray[fCount].getContentPane(); oldstring = inputQuery.getText(); String showString = ""; showString = "Show tables"; sqlbackup[fCount] = showString; inputQuery.setText(showString); getTableFrame(j,"all"); inputQuery.setText(oldstring); fCount++; } //*************************************************************************** private void makeDatabasesFrame() { //JFrame showtable = new JFrame("All Databases"); farray[fCount] = new JFrame("All Databases"); farray[fCount].setSize(400,300); farray[fCount].setLocation(550,0); farray[fCount].show(); Container j = farray[fCount].getContentPane(); oldstring = inputQuery.getText(); String showString = ""; showString = "Show Databases"; sqlbackup[fCount] = showString; inputQuery.setText(showString); getTableFrame(j,"show Databases"); inputQuery.setText(oldstring); fCount++; } //*************************************************************************** private void makeTableFrameMeta(String table,int x,int y) { //JFrame showtable = new JFrame(table); farray[fCount] = new JFrame(table); farray[fCount].setSize(400,300); farray[fCount].setLocation(x,y); farray[fCount].show(); Container j = farray[fCount].getContentPane(); oldstring = inputQuery.getText(); String showString = ""; showString = "describe "+table; sqlbackup[fCount] = showString; inputQuery.setText(showString); getTableFrame(j,table); inputQuery.setText(oldstring); fCount++; } /**************************************************************************/ private void saveFile() { JFileChooser fileChooser = new JFileChooser(); fileChooser.setFileSelectionMode( JFileChooser.FILES_ONLY ); int result = fileChooser.showSaveDialog( this ); // user clicked Cancel button on dialog if ( result == JFileChooser.CANCEL_OPTION ) return; File fileName = fileChooser.getSelectedFile(); if ( fileName == null || fileName.getName().equals( "" ) ) JOptionPane.showMessageDialog( this, "Invalid File Name", "Invalid File Name", JOptionPane.ERROR_MESSAGE ); else { // Open the file try { output = new ObjectOutputStream( new FileOutputStream( fileName ) ); sqlStatement.setString(inputQuery.getText()); output.writeObject( sqlStatement ); output.flush(); } catch ( IOException e ) { JOptionPane.showMessageDialog( this, "Error Opening File", "Error", JOptionPane.ERROR_MESSAGE ); } catch ( NumberFormatException nfe ) { JOptionPane.showMessageDialog( this, "Bad account number or balance", "Invalid Number Format", JOptionPane.ERROR_MESSAGE ); } } } //*************************************************************************** private void closeFile() { try { output.close(); System.exit( 0 ); } catch( IOException ex ) { JOptionPane.showMessageDialog( this, "Error closing file", "Error", JOptionPane.ERROR_MESSAGE ); //System.exit( 1 ); } } //*************************************************************************** private void loadFile() { JFileChooser fileChooser = new JFileChooser(); fileChooser.setFileSelectionMode( JFileChooser.FILES_ONLY ); int result = fileChooser.showOpenDialog( this ); // user clicked Cancel button on dialog if ( result == JFileChooser.CANCEL_OPTION ) return; File fileName = fileChooser.getSelectedFile(); if ( fileName == null || fileName.getName().equals( "" ) ) JOptionPane.showMessageDialog( this, "Invalid File Name", "Invalid File Name", JOptionPane.ERROR_MESSAGE ); else { // Open the file try { input = new ObjectInputStream( new FileInputStream( fileName ) ); } catch ( IOException e ) { JOptionPane.showMessageDialog( this, "Error Opening File", "Error", JOptionPane.ERROR_MESSAGE ); } } } //*************************************************************************** void loadObjectUnser() { try { sqlStatement = (SqlSave)input.readObject(); } catch ( EOFException eofex ) { JOptionPane.showMessageDialog( this, "No more records in file", "End of File", JOptionPane.ERROR_MESSAGE ); } catch ( ClassNotFoundException cnfex ) { JOptionPane.showMessageDialog( this, "Unable to create object", "Class Not Found", JOptionPane.ERROR_MESSAGE ); } catch ( IOException ioex ) { JOptionPane.showMessageDialog( this, "Error during read from file", "Read Error", JOptionPane.ERROR_MESSAGE ); } inputQuery.setText(sqlStatement.getString()); } /**************************************************************************/ private void getTable() { try { String query = inputQuery.getText(); statement = connection.createStatement(); resultSet = statement.executeQuery( query ); displayResultSet( resultSet ); } catch ( SQLException sqlex ) { sqlex.printStackTrace(); } } //*************************************************************************** private void getTableFrame(Container j,String tablename) { try { String query = inputQuery.getText(); statement = connection.createStatement(); resultSet = statement.executeQuery( query ); displayResultSetFrame( resultSet , j ,tablename); } catch ( SQLException sqlex ) { sqlex.printStackTrace(); } } //*************************************************************************** private void setTable() { try { String query = inputQuery.getText(); statement = connection.createStatement(); statement.executeUpdate(query); } catch ( SQLException sqlex ) { sqlex.printStackTrace(); } } //*************************************************************************** private void displayResultSet( ResultSet rs ) throws SQLException { // position to first record boolean moreRecords = rs.next(); // If there are no records, display a message if ( ! moreRecords ) { JOptionPane.showMessageDialog( this, "ResultSet contained no records" ); return; } Vector columnHeads = new Vector(); Vector rows = new Vector(); try { // get column heads ResultSetMetaData rsmd = rs.getMetaData(); for ( int i = 1; i <= rsmd.getColumnCount(); ++i ) columnHeads.addElement( rsmd.getColumnName( i ) ); // get row data do { rows.addElement( getNextRow( rs, rsmd ) ); } while ( rs.next() ); // display table with ResultSet contents table = new JTable( rows, columnHeads ); JScrollPane scroller = new JScrollPane( table ); Container c = getContentPane(); c.remove( 1 ); //bekackter dreck !!!! c.add( scroller, BorderLayout.CENTER ); c.validate(); } catch ( SQLException sqlex ) { sqlex.printStackTrace(); } } //*************************************************************************** private void displayResultSetFrame( ResultSet rs,Container j,String tablename ) throws SQLException { // position to first record boolean moreRecords = rs.next(); // If there are no records, display a message if ( ! moreRecords ) { JOptionPane.showMessageDialog( this, "Table contained no records" ); return; } Vector columnHeads = new Vector(); Vector rows = new Vector(); try { // get column heads ResultSetMetaData rsmd = rs.getMetaData(); for ( int i = 1; i <= rsmd.getColumnCount(); ++i ) columnHeads.addElement( rsmd.getColumnName( i ) ); // get row data do { rows.addElement( getNextRow( rs, rsmd ) ); } while ( rs.next() ); // display table with ResultSet contents //table = new JTable( rows, columnHeads ); JScrollPane scroller;// = new JScrollPane( table ); if (tablename != "all") { table = new JTable( rows, columnHeads ); //table.setName(tablename); //table.addMouseListener(this); scroller = new JScrollPane( table ); } else { tableall = new JTable( rows, columnHeads ); tableall.setName(tablename); tableall.addMouseListener(this); scroller = new JScrollPane( tableall ); } //j.remove( 1 ); //bekackter dreck !!!! (1.5 Monate später: Stephan hat recht) j.add( scroller, BorderLayout.CENTER ); j.validate(); } catch ( SQLException sqlex ) { sqlex.printStackTrace(); } } //*************************************************************************** private Vector getNextRow( ResultSet rs,ResultSetMetaData rsmd ) throws SQLException { Vector currentRow = new Vector(); for ( int i = 1; i <= rsmd.getColumnCount(); ++i ) switch( rsmd .getColumnType( i ) ){ case Types.VARCHAR: case Types.LONGVARCHAR: case Types.CHAR: case Types.DATE: currentRow.addElement( rs.getString( i ) ); break; case Types.INTEGER: currentRow.addElement( new Long( rs.getLong( i ) ) ); break; case Types.DECIMAL: currentRow.addElement( new Float( rs.getFloat( i ) ) ); break; default: System.out.println( "Type was: " + rsmd.getColumnTypeName( i ) ); } return currentRow; } //*************************************************************************** public void shutDown() { try { connection.close(); } catch ( SQLException sqlex ) { System.err.println( "Unable to disconnect" ); sqlex.printStackTrace(); } } /********************* finally the main ************************************/ public static void main( String args[] ) { final Logon log = new Logon(); final KinoDB app = null; Logon.logb.addActionListener( new ActionListener() { public void actionPerformed( ActionEvent e ) { app = new KinoDB(log.getUrl(),log.getUname(),log.getPword()); app.addWindowListener( new WindowAdapter() { public void windowClosing( WindowEvent e ) { app.shutDown(); System.exit( 0 ); } } ); log.hide(); } } ); Logon.pword.addActionListener( new ActionListener() { public void actionPerformed( ActionEvent e ) { app = new KinoDB(log.getUrl(),log.getUname(),log.getPword()); app.addWindowListener( new WindowAdapter() { public void windowClosing( WindowEvent e ) { app.shutDown(); System.exit( 0 ); } } ); log.hide(); } } ); } //*************************************************************************** public void mouseClicked( MouseEvent e ) { String s = ""; //s = e.toString(); int c = tableall.getSelectedColumn(); int r = tableall.getSelectedRow(); s = e.getSource().toString(); if (tableall.getName()=="all") { pop.show(e.getComponent(),e.getX(),e.getY()); //makeTableFrameMeta(tableall.getValueAt(r,c).toString()); tableglobal = tableall.getValueAt(r,c).toString(); } status.setText(s); } public void mousePressed( MouseEvent e ) { } public void mouseReleased( MouseEvent e ) { } public void mouseEntered( MouseEvent e ) { String s = ""; s = "Mouse im Fenster"; status.setText(s); } public void mouseExited( MouseEvent e ) { String s = ""; s = "Mouse aus dem Fenster"; status.setText(s); //pop.hide(); } // MouseMotionListener event handlers public void mouseDragged( MouseEvent e ) { } public void mouseMoved( MouseEvent e ) { } /*****************************************************/ // class zum abspeichern/laden der eingegebenen // // sql-statements ... wieder so ein nice-to-have das // // realisiert wurde ohne das es gewollt war :) // /*****************************************************/ class SqlSave implements Serializable { String statement = ""; public void SqlSave(String s) { statement = s; } public String getString() { return statement; } public void setString(String s) { statement = s; } } class PopHandler implements ActionListener { public void actionPerformed ( ActionEvent e ) { if (e.getSource() == popdaten) makeTableFrame(tableglobal,550,300); if (e.getSource() == popmeta ) makeTableFrameMeta(tableglobal,550,300); if (e.getSource() == popeinfuegen) { //ResultSet rs2; try { makeTableFrame(tableglobal,550,0); JFrame dynatab = new JFrame(tableglobal +" einfügen"); dynatab.setLocation(550,300); JButton beinfuegen = new JButton("INSERT"); dynatab.setSize(300,200); Container k = dynatab.getContentPane(); String query = "Select * from "+tableglobal; statement = connection.createStatement(); rs2 = statement.executeQuery( query ); //displayResultSet( resultSet ); rsmd2 = rs2.getMetaData(); //JOptionPane.showMessageDialog(null,rsmd2.getCatalogName(1)); textarray = new JTextField[rsmd2.getColumnCount()]; JPanel dynap = new JPanel(new GridLayout(rsmd2.getColumnCount(),1)); for ( int i = 1; i <= rsmd2.getColumnCount(); ++i ) { if(rsmd2.isAutoIncrement(i) == false) { dynap.add(new JLabel(rsmd2.getColumnName(i)+" ("+rsmd2.getColumnTypeName(i)+")")); textarray[i-1] = new JTextField(); dynap.add(textarray[i-1]); } } k.add(dynap,BorderLayout.NORTH); k.add(beinfuegen,BorderLayout.SOUTH); dynatab.show(); beinfuegen.addActionListener( new ActionListener() { public void actionPerformed( ActionEvent e ) { String insertString = ""; int i = 0; insertString = "INSERT INTO "+tableglobal+" (" ; try { while ( i < rsmd2.getColumnCount() ) { if (rsmd2.isAutoIncrement(i+1) == false) { if ( i != rsmd2.getColumnCount() - 1) insertString += rsmd2.getColumnName(i+1)+","; else insertString += rsmd2.getColumnName(i+1); } i++; } insertString += ") VALUES ("; i=0; while ( i < rsmd2.getColumnCount() ) { if (rsmd2.isAutoIncrement(i+1) == false) { if (i < rsmd2.getColumnCount()-1 ) if (rsmd2.getColumnType(i+1) == Types.VARCHAR || rsmd2.getColumnType(i+1) == Types.LONGVARCHAR || rsmd2.getColumnType(i+1) == Types.DATE || rsmd2.getColumnType(i+1) == Types.DECIMAL) insertString += "'"+textarray[i].getText()+"',"; else insertString +=textarray[i].getText()+","; else if (rsmd2.getColumnType(i+1) == Types.VARCHAR || rsmd2.getColumnType(i+1) == Types.LONGVARCHAR || rsmd2.getColumnType(i+1) == Types.DATE || rsmd2.getColumnType(i+1) == Types.DECIMAL) insertString += "'"+textarray[i].getText()+"'"; else insertString +=textarray[i].getText(); } i++; } insertString += ")"; i = 0; } catch (SQLException sqlex) { sqlex.printStackTrace(); } inputQuery.setText(insertString); setTable(); inputQuery.setText("select * from "+idynatext.getText()); getTable(); refreshFrame(); inputQuery.setText(""); //columnHeads.addElement( rsmd.getColumnName( i ) ); } } ); } catch ( SQLException sqlex ) { sqlex.printStackTrace(); } } } } //**************************************************************************** class SearchListener implements ActionListener { //**************** COLA - RAUSCH ******************************************* //**************** wildcards im sql == % *********************************** public void actionPerformed(ActionEvent e) { int schalter = 1; int schalter2 = 1; try { searchString = "Select * From " + searchtext.getText(); for (int i=1; i < rsmd2.getColumnCount() + 1; i++) { if (textarray[i-1].getText().trim().length() > 0) { if (schalter == 1){ searchString += " Where "; schalter = 0; } if ( schalter2 == 1) { if (rsmd2.getColumnType(i) == Types.VARCHAR || rsmd2.getColumnType(i) == Types.LONGVARCHAR || rsmd2.getColumnType(i) == Types.CHAR || rsmd2.getColumnType(i) == Types.DATE || rsmd2.getColumnType(i+1) == Types.DECIMAL) searchString += rsmd2.getColumnName(i) + " like '"+textarray[i-1].getText()+"'"; else searchString += rsmd2.getColumnName(i) + "=" + textarray[i-1].getText(); schalter2 = 0; } else{ if (rsmd2.getColumnType(i) == Types.VARCHAR || rsmd2.getColumnType(i) == Types.LONGVARCHAR || rsmd2.getColumnType(i) == Types.CHAR || rsmd2.getColumnType(i) == Types.DATE || rsmd2.getColumnType(i+1) == Types.DECIMAL) searchString += " and " + rsmd2.getColumnName(i) + " like '"+textarray[i-1].getText()+"'"; else searchString += " and " + rsmd2.getColumnName(i) + "=" + textarray[i-1].getText(); } } } // Debug scheiss !!! System.out.println(searchString); inputQuery.setText(searchString); getTable(); inputQuery.setText(""); searchString = ""; } catch (SQLException sqlex) { sqlex.printStackTrace(); } refreshFrame(); } } //**************************************************************************** class UpdateListener implements ActionListener { int schalter; int schalter2 = 0; String primaryKey = ""; String select=""; ResultSet pk; ResultSetMetaData pkmd; public void actionPerformed(ActionEvent e) { schalter = 0; updateString = "Update " + updatetext.getText() + " Set "; try { select = "Describe " + updatetext.getText(); statement = connection.createStatement(); pk = statement.executeQuery(select); pkmd = pk.getMetaData(); for (int i=1; i <= rsmd2.getColumnCount(); i++) { if (schalter2 == 0) { if (rsmd2.isSearchable(i)) primaryKey = rsmd2.getColumnName(i); schalter2 = 1; } if (schalter == 0) { if (textarray[i-1].getText().trim().length() > 0) { if (rsmd2.getColumnType(i) == Types.VARCHAR || rsmd2.getColumnType(i) == Types.LONGVARCHAR || rsmd2.getColumnType(i) == Types.CHAR || rsmd2.getColumnType(i) == Types.DATE || rsmd2.getColumnType(i+1) == Types.DECIMAL) updateString += rsmd2.getColumnName(i) + "='"+textarray[i-1].getText()+"'"; else updateString += rsmd2.getColumnName(i) + "=" + textarray[i-1].getText(); schalter = 1; } } else { if (textarray[i-1].getText().trim().length() > 0) { if (rsmd2.getColumnType(i) == Types.VARCHAR || rsmd2.getColumnType(i) == Types.LONGVARCHAR || rsmd2.getColumnType(i) == Types.CHAR || rsmd2.getColumnType(i) == Types.DATE || rsmd2.getColumnType(i+1) == Types.DECIMAL) updateString += ", " + rsmd2.getColumnName(i) + "='"+textarray[i-1].getText()+"'"; else updateString += ", " + rsmd2.getColumnName(i) + "=" + textarray[i-1].getText(); } } } updateString += " Where "+ primaryKey +"=" + keyfield.getText(); //mhSystem.out.println(updateString); // Debug scheiss !!! statement.executeUpdate(updateString); updateString = ""; } catch (SQLException sqlex) { sqlex.printStackTrace(); } refreshFrame(); } } //**************************************************************************** class FrameHandler implements WindowListener { public void windowClosing ( WindowEvent e ) { int i = 0; while(i