public void tearDown() { try { Connection con = AllTests.getConnection(); Statement st = con.createStatement(); st.execute("drop table " + table); st.close(); } catch (Throwable e) { // e.printStackTrace(); } }
public void setUp() { tearDown(); try { Connection con = AllTests.getConnection(); Statement st = con.createStatement(); st.execute( "create table " + table + "(a " + testValue.dataType + ", b " + testValue.dataType + ")"); st.close(); PreparedStatement pr = con.prepareStatement("INSERT into " + table + "(a,b) Values(?,?)"); pr.setObject(1, testValue.small); pr.setObject(2, testValue.large); pr.execute(); pr.setObject(1, testValue.small); pr.setObject(2, testValue.small); pr.execute(); pr.setObject(1, testValue.large); pr.setObject(2, testValue.large); pr.execute(); pr.setObject(1, testValue.large); pr.setObject(2, testValue.small); pr.execute(); pr.setObject(1, null); pr.setObject(2, testValue.small); pr.execute(); pr.setObject(1, testValue.small); pr.setObject(2, null); pr.execute(); pr.setObject(1, null); pr.setObject(2, null); pr.execute(); pr.close(); } catch (Throwable e) { e.printStackTrace(); } }
/** * Gets the instances of <CODE>Template</CODE> that have an ID that is already in the database. * * @return An <CODE>ArrayList</CODE> containing the instances of <CODE>Template</CODE> already in * the database. * @throws java.sql.SQLException Thrown on sql exception. */ public ArrayList findTemplatesInDatabase() throws java.sql.SQLException { ArrayList templatesInDatabase = new ArrayList(); Connection oracleConnection = getDataSource().getConnection(); try { Statement query = oracleConnection.createStatement(); try { StringBuffer sql = new StringBuffer("SELECT TMPL_ID FROM "); sql.append(MPSBrowserView.SCHEMA); sql.append(".TEMPLATE WHERE TMPL_ID IN ("); ArrayList templates = getTemplates(); int templateCount = templates.size(); for (int i = 0; i < templateCount; i++) { if (i > 0) sql.append(", "); sql.append("'"); sql.append(((Template) templates.get(i)).getID()); sql.append("'"); } sql.append(")"); ResultSet result = query.executeQuery(sql.toString()); try { while (result.next()) { String templateID = result.getString("TMPL_ID"); for (int i = 0; i < templateCount; i++) { Template currentTemplate = (Template) templates.get(i); if (templateID.equals(currentTemplate.getID())) { templatesInDatabase.add(currentTemplate); currentTemplate.setInDatabase(true); } } } } finally { result.close(); } } finally { query.close(); } } finally { oracleConnection.close(); } return templatesInDatabase; }
public void runTest() throws Exception { Connection con = AllTests.getConnection(); Statement st = con.createStatement(); ResultSet rs; rs = st.executeQuery("Select * from " + table + " where 1 = 0"); assertFalse("To many rows", rs.next()); rs = st.executeQuery("Select * from " + table + " where a = b"); assertTrue("To few rows", rs.next()); assertEqualsObject("Values not equals", rs.getObject(1), rs.getObject(2), false); assertTrue("To few rows", rs.next()); assertEqualsObject("Values not equals", rs.getObject(1), rs.getObject(2), false); assertFalse("To many rows", rs.next()); rs = st.executeQuery("Select * from " + table + " where a <= b and b <= a"); assertTrue("To few rows", rs.next()); assertEqualsObject("Values not equals", rs.getObject(1), rs.getObject(2), false); assertTrue("To few rows", rs.next()); assertEqualsObject("Values not equals", rs.getObject(1), rs.getObject(2), false); assertFalse("To many rows", rs.next()); rs = st.executeQuery("Select * from " + table + " where (a > (b))"); assertTrue("To few rows", rs.next()); assertFalse("To many rows", rs.next()); rs = st.executeQuery("Select * from " + table + " where a >= b"); assertTrue("To few rows", rs.next()); assertTrue("To few rows", rs.next()); assertTrue("To few rows", rs.next()); assertFalse("To many rows", rs.next()); rs = st.executeQuery("Select * from " + table + " where not (a >= b)"); assertTrue("To few rows", rs.next()); assertTrue("To few rows", rs.next()); assertTrue("To few rows", rs.next()); assertTrue("To few rows", rs.next()); assertFalse("To many rows", rs.next()); rs = st.executeQuery("Select * from " + table + " where a < b"); assertTrue("To few rows", rs.next()); assertFalse("To many rows", rs.next()); rs = st.executeQuery("Select * from " + table + " where a < b or a>b"); assertTrue("To few rows", rs.next()); assertTrue("To few rows", rs.next()); assertFalse("To many rows", rs.next()); rs = st.executeQuery("Select * from " + table + " where a <= b"); assertTrue("To few rows", rs.next()); assertTrue("To few rows", rs.next()); assertTrue("To few rows", rs.next()); assertFalse("To many rows", rs.next()); rs = st.executeQuery("Select * from " + table + " where a <> b"); assertTrue("To few rows", rs.next()); assertTrue("To few rows", rs.next()); assertFalse("To many rows", rs.next()); PreparedStatement pr = con.prepareStatement("Select * from " + table + " where a between ? and ?"); pr.setObject(1, testValue.small); pr.setObject(2, testValue.large); rs = pr.executeQuery(); assertTrue("To few rows", rs.next()); assertTrue("To few rows", rs.next()); assertTrue("To few rows", rs.next()); assertTrue("To few rows", rs.next()); assertTrue("To few rows", rs.next()); assertFalse("To many rows", rs.next()); pr.close(); pr = con.prepareStatement("Select * from " + table + " where a not between ? and ?"); pr.setObject(1, testValue.small); pr.setObject(2, testValue.large); rs = pr.executeQuery(); assertTrue("To few rows", rs.next()); assertTrue("To few rows", rs.next()); assertFalse("To many rows", rs.next()); pr.close(); pr = con.prepareStatement("Select * from " + table + " where a in(?,?)"); pr.setObject(1, testValue.small); pr.setObject(2, testValue.large); rs = pr.executeQuery(); assertTrue("To few rows", rs.next()); assertTrue("To few rows", rs.next()); assertTrue("To few rows", rs.next()); assertTrue("To few rows", rs.next()); assertTrue("To few rows", rs.next()); assertFalse("To many rows", rs.next()); pr.close(); pr = con.prepareStatement("Select * from " + table + " where a not in(?,?)"); pr.setObject(1, testValue.small); pr.setObject(2, testValue.large); rs = pr.executeQuery(); assertTrue("To few rows", rs.next()); assertTrue("To few rows", rs.next()); assertFalse("To many rows", rs.next()); pr.close(); st.close(); }
/** * Saves the templates to the database. * * @throws java.sql.SQLException Thrown on sql error. */ public void saveToDatabase() throws java.sql.SQLException { setProgressIndeterminate(true); setMessage("Saving Templates"); ArrayList templates = getTemplates(); Connection oracleConnection = getDataSource().getConnection(); try { oracleConnection.setAutoCommit(false); Statement query = oracleConnection.createStatement(); try { int templateCount = templates.size(); // First remove any existing entries. StringBuffer sql = new StringBuffer("DELETE FROM "); sql.append(MPSBrowserView.SCHEMA); sql.append(".TMPL_SGNL_FLD"); StringBuffer whereClause = new StringBuffer(" WHERE TMPL_ID IN ("); for (int i = 0; i < templateCount; i++) { if (i > 0) whereClause.append(", "); whereClause.append("'"); whereClause.append(((Template) templates.get(i)).getID()); whereClause.append("'"); } whereClause.append(")"); sql.append(whereClause); query.execute(sql.toString()); sql = new StringBuffer("DELETE FROM "); sql.append(MPSBrowserView.SCHEMA); sql.append(".TMPL_MACRO"); sql.append(whereClause); query.execute(sql.toString()); sql = new StringBuffer("DELETE FROM "); sql.append(MPSBrowserView.SCHEMA); sql.append(".TMPL_SGNL_REC"); sql.append(whereClause); query.execute(sql.toString()); sql = new StringBuffer("DELETE FROM "); sql.append(MPSBrowserView.SCHEMA); sql.append(".TMPL_SGNL_FLD"); sql.append(whereClause); query.execute(sql.toString()); sql = new StringBuffer("DELETE FROM "); sql.append(MPSBrowserView.SCHEMA); sql.append(".TEMPLATE"); sql.append(whereClause); query.execute(sql.toString()); sql = new StringBuffer("DELETE FROM "); sql.append(MPSBrowserView.SCHEMA); sql.append(".TMPL_ARCH_REQ"); sql.append(whereClause); query.execute(sql.toString()); sql = new StringBuffer("DELETE FROM "); sql.append(MPSBrowserView.SCHEMA); sql.append(".TMPL_ARCH_REQ_GRP"); sql.append(whereClause); query.execute(sql.toString()); sql = new StringBuffer("DELETE FROM "); sql.append(MPSBrowserView.SCHEMA); sql.append(".TMPL_ARCH_REQ_GRP_ARCH_REQ"); sql.append(whereClause); query.execute(sql.toString()); sql = new StringBuffer("INSERT INTO "); sql.append(MPSBrowserView.SCHEMA); sql.append( ".TEMPLATE (TMPL_ID, TMPL_DESC, EXT_SRC_FILE_NM, EXT_SRC_FILE_MOD_DTE) VALUES (?, ?, ?, ?)"); PreparedStatement templateInsertStatement = oracleConnection.prepareStatement(sql.toString()); try { sql = new StringBuffer("INSERT INTO "); sql.append(MPSBrowserView.SCHEMA); sql.append(".TMPL_MACRO (TMPL_ID, MACRO_ID) VALUES (?, ?)"); PreparedStatement macroInsertStatement = oracleConnection.prepareStatement(sql.toString()); try { sql = new StringBuffer("INSERT INTO "); sql.append(MPSBrowserView.SCHEMA); sql.append( ".TMPL_SGNL_REC (TMPL_ID, TMPL_SGNL_ID, REC_TYPE_ID, ARCH_IND, ARCH_FREQ, ARCH_TYPE) VALUES (?, ?, ?, ?, ?, ?)"); PreparedStatement signalInsertStatement = oracleConnection.prepareStatement(sql.toString()); try { sql = new StringBuffer("INSERT INTO "); sql.append(MPSBrowserView.SCHEMA); sql.append( ".TMPL_SGNL_FLD (TMPL_ID, TMPL_SGNL_ID, FLD_ID, REC_TYPE_ID, VAL) VALUES (?, ?, ?, ?, ?)"); PreparedStatement fieldInsertStatement = oracleConnection.prepareStatement(sql.toString()); try { sql = new StringBuffer("INSERT INTO "); sql.append(MPSBrowserView.SCHEMA); sql.append(".TMPL_ARCH_REQ (TMPL_ID, ARCH_REQ_FILE_NM) VALUES (?, ?)"); PreparedStatement requestInsertStatement = oracleConnection.prepareStatement(sql.toString()); try { sql = new StringBuffer("INSERT INTO "); sql.append(MPSBrowserView.SCHEMA); sql.append(".TMPL_ARCH_REQ_GRP (TMPL_ID, ARCH_REQ_GRP_FILE_NM) VALUES (?, ?)"); PreparedStatement groupInsertStatement = oracleConnection.prepareStatement(sql.toString()); try { sql = new StringBuffer("INSERT INTO "); sql.append(MPSBrowserView.SCHEMA); sql.append( ".TMPL_ARCH_REQ_GRP_ARCH_REQ (TMPL_ID, ARCH_REQ_GRP_FILE_NM, ARCH_REQ_FILE_NM) VALUES (?, ?, ?)"); PreparedStatement requestGroupInsertStatement = oracleConnection.prepareStatement(sql.toString()); try { sql = new StringBuffer("UPDATE "); sql.append(MPSBrowserView.SCHEMA); sql.append( ".TMPL_SGNL_REC SET ARCH_IND = ?, ARCH_FREQ = ?, ARCH_TYPE = ?, ARCH_REQ_FILE = ? WHERE TMPL_ID = ? AND TMPL_SGNL_ID = ? AND REC_TYPE_ID = ?"); PreparedStatement signalUpdateStatement = oracleConnection.prepareStatement(sql.toString()); try { int progress = 0; setProgressMaximum(importedFieldCount + importedMacroCount); setProgressValue(0); setProgressIndeterminate(false); for (int templateIndex = 0; templateIndex < templateCount; templateIndex++) { Template currentTemplate = (Template) templates.get(templateIndex); String currentTemplateID = currentTemplate.getID(); templateInsertStatement.setString(1, currentTemplateID); String currentDescription = currentTemplate.getDescription(); if (currentDescription == null) templateInsertStatement.setNull(2, Types.VARCHAR); else templateInsertStatement.setString(2, currentDescription); templateInsertStatement.setString(3, currentTemplate.getFileName()); templateInsertStatement.setTimestamp( 4, currentTemplate.getFileModifiedDate()); templateInsertStatement.execute(); // Need to insert macros. int macroCount = currentTemplate.getMacroCount(); for (int macroIndex = 0; macroIndex < macroCount; macroIndex++) { macroInsertStatement.setString(1, currentTemplateID); String currentMacro = currentTemplate.getMacroAt(macroIndex); macroInsertStatement.setString(2, currentMacro); macroInsertStatement.execute(); setProgressValue(++progress); } int signalCount = currentTemplate.getSignalCount(); for (int signalIndex = 0; signalIndex < signalCount; signalIndex++) { Signal currentSignal = currentTemplate.getSignalAt(signalIndex); String currentSignalID = currentSignal.getID(); String currentRecordTypeID = currentSignal.getType().getRecordType().getID(); signalInsertStatement.setString(1, currentTemplateID); signalInsertStatement.setString(2, currentSignalID); signalInsertStatement.setString(3, currentRecordTypeID); signalInsertStatement.setString(4, currentSignal.getArchiveIndicator()); BigDecimal currentFrequency = currentSignal.getArchiveFrequency(); if (currentFrequency == null) currentFrequency = new BigDecimal("60"); // 60 default in RDB signalInsertStatement.setBigDecimal(5, currentFrequency); String currentType = currentSignal.getArchiveType(); if (currentType == null) currentType = "Monitor"; // 'Monitor' default in RDB signalInsertStatement.setString(6, currentType); signalInsertStatement.execute(); int fieldCount = currentSignal.getFieldCount(); for (int fieldIndex = 0; fieldIndex < fieldCount; fieldIndex++) { SignalField currentField = currentSignal.getFieldAt(fieldIndex); fieldInsertStatement.setString(1, currentTemplateID); fieldInsertStatement.setString(2, currentSignalID); fieldInsertStatement.setString(3, currentField.getType().getID()); fieldInsertStatement.setString(4, currentRecordTypeID); fieldInsertStatement.setString(5, currentField.getValue()); fieldInsertStatement.execute(); if (isParseCanceled()) { oracleConnection.rollback(); return; } setProgressValue(++progress); } } // Insert archive requests. int requestCount = currentTemplate.getArchiveRequestCount(); for (int requestIndex = 0; requestIndex < requestCount; requestIndex++) { ArchiveRequest currentRequest = currentTemplate.getArchiveRequestAt(requestIndex); String currentRequestFileName = currentRequest.getFileName(); requestInsertStatement.setString(1, currentTemplateID); requestInsertStatement.setString(2, currentRequestFileName); requestInsertStatement.execute(); signalCount = currentRequest.getSignalCount(); for (int signalIndex = 0; signalIndex < signalCount; signalIndex++) { Signal currentSignal = currentRequest.getSignalAt(signalIndex); String currentSignalID = currentSignal.getID(); signalUpdateStatement.setString( 1, currentSignal.getArchiveIndicator()); signalUpdateStatement.setBigDecimal( 2, currentSignal.getArchiveFrequency()); signalUpdateStatement.setString(3, currentSignal.getArchiveType()); signalUpdateStatement.setString(4, currentRequestFileName); signalUpdateStatement.setString(5, currentTemplateID); signalUpdateStatement.setString(6, currentSignalID); signalUpdateStatement.setString( 7, currentSignal.getType().getRecordType().getID()); signalUpdateStatement.execute(); int fieldCount = currentSignal.getFieldCount(); } } // Insert archive groups. int groupCount = currentTemplate.getArchiveGroupCount(); for (int groupIndex = 0; groupIndex < groupCount; groupIndex++) { ArchiveGroup currentGroup = currentTemplate.getArchiveGroupAt(groupIndex); groupInsertStatement.setString(1, currentTemplateID); String currentGroupFileName = currentGroup.getFileName(); groupInsertStatement.setString(2, currentGroupFileName); groupInsertStatement.execute(); requestCount = currentGroup.getArchiveRequestCount(); for (int requestIndex = 0; requestIndex < requestCount; requestIndex++) { ArchiveRequest currentRequest = currentGroup.getArchiveRequestAt(requestIndex); String currentRequestFileName = currentRequest.getFileName(); requestGroupInsertStatement.setString(1, currentTemplateID); requestGroupInsertStatement.setString(2, currentGroupFileName); requestGroupInsertStatement.setString( 3, currentRequest.getFileName()); } } } } finally { signalUpdateStatement.close(); } } finally { requestGroupInsertStatement.close(); } } finally { groupInsertStatement.close(); } } finally { requestInsertStatement.close(); } } finally { fieldInsertStatement.close(); } } finally { signalInsertStatement.close(); } } finally { macroInsertStatement.close(); } } finally { templateInsertStatement.close(); } } catch (java.sql.SQLException ex) { oracleConnection.rollback(); throw ex; } finally { query.close(); } if (isParseCanceled()) oracleConnection.rollback(); else oracleConnection.commit(); } finally { oracleConnection.close(); } }
public Design() throws Exception { super.setBackground(Color.BLACK); this.setTitle(""); con = getContentPane(); con.setLayout(null); dim = tk.getDefaultToolkit().getScreenSize(); this.setTitle("Customer Peer Login"); l1 = new JLabel(new ImageIcon("plain.jpg")); l1.setBounds(0, 0, 400, 400); con.add(l1); l1.setBorder(BorderFactory.createEtchedBorder(5, Color.black, Color.black)); title = new JLabel("CUSTOMER PEER LOGIN "); title.setFont(new Font("Bookman Old Style", Font.ROMAN_BASELINE, 20)); title.setForeground(Color.red); title.setBounds(80, 30, 300, 30); l1.add(title); l4 = new JLabel("CMACHINE NAME"); l4.setFont(new Font("Bookman Old Style", Font.BOLD, 16)); l4.setForeground(Color.BLUE); l4.setBounds(70, 100, 160, 20); // l4.setBorder(BorderFactory.createEtchedBorder(5,Color.green,Color.green)); l1.add(l4); jtf2 = new JTextField(); jtf2.setBounds(250, 100, 100, 20); jtf2.setBorder(BorderFactory.createEtchedBorder(5, Color.green, Color.green)); l1.add(jtf2); l2 = new JLabel("CUSER LOGIN"); l2.setFont(new Font("Bookman Old Style", Font.BOLD, 16)); l2.setForeground(Color.blue); l2.setBounds(70, 150, 120, 20); l1.add(l2); jtf1 = new JTextField(); jtf1.setBounds(250, 150, 100, 20); jtf1.setBorder(BorderFactory.createEtchedBorder(5, Color.green, Color.green)); l1.add(jtf1); l3 = new JLabel("CPASSWORD"); l3.setFont(new Font("Bookman Old Style", Font.BOLD, 16)); l3.setForeground(Color.blue); l3.setBounds(70, 200, 120, 20); l1.add(l3); jptf1 = new JPasswordField(); jptf1.setBounds(250, 200, 100, 20); jptf1.setBorder(BorderFactory.createEtchedBorder(5, Color.green, Color.green)); l1.add(jptf1); JLabel l4 = new JLabel("DAgent"); l4.setFont(new Font("Bookman Old Style", Font.BOLD, 16)); l4.setForeground(Color.blue); l4.setBounds(70, 250, 120, 20); l1.add(l4); box = new JComboBox(); box.setBounds(250, 250, 100, 20); box.setBorder(BorderFactory.createEtchedBorder(5, Color.green, Color.green)); l1.add(box); b2 = new JButton("Register"); b2.setBounds(50, 300, 100, 20); l1.add(b2); b2.setBorder(BorderFactory.createEtchedBorder(10, Color.BLUE, Color.BLUE)); b3 = new JButton("Login"); b3.setBounds(150, 300, 100, 20); b3.setBorder(BorderFactory.createEtchedBorder(10, Color.BLUE, Color.BLUE)); l1.add(b3); b1 = new JButton("Cancel"); b1.setBounds(250, 300, 100, 20); b1.setBorder(BorderFactory.createEtchedBorder(10, Color.BLUE, Color.BLUE)); l1.add(b1); b1.addActionListener( new ActionListener() { public void actionPerformed(ActionEvent we) { dispose(); } }); try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); conn = DriverManager.getConnection("jdbc:odbc:agent"); } catch (Exception exp) { } try { Statement satem = conn.createStatement(); ResultSet rsatem = satem.executeQuery("select * from Dagent"); while (rsatem.next()) { String namem = rsatem.getString("uname"); box.addItem(namem); } } catch (Exception expo1) { } b2.addActionListener( new ActionListener() { public void actionPerformed(ActionEvent we) { String username = jtf1.getText().trim(); String password = jptf1.getText().trim(); String mechine = jtf2.getText().trim(); String dname = box.getSelectedItem().toString(); int porte = 0; try { Statement sate = conn.createStatement(); ResultSet rsate = sate.executeQuery("select * from Dagent where uname='" + dname + "'"); if (rsate.next()) { servermachine = rsate.getString("umechine"); porte = rsate.getInt("ulistport"); System.out.println(servermachine); } System.out.println(servermachine); } catch (Exception exp) { exp.printStackTrace(); } try { packet p = new packet(); p.setaction("Creg"); p.setCuser(username); p.setCpass(password); p.setCmname(mechine); p.setCDpeer(dname); Socket soc = new Socket(servermachine, porte); ObjectOutputStream out = new ObjectOutputStream(soc.getOutputStream()); out.writeObject(p); ObjectInputStream in = new ObjectInputStream(soc.getInputStream()); packet rpac = (packet) in.readObject(); if (rpac.getaction().equals("ok")) { JOptionPane.showMessageDialog(null, "Sucessfully Registered"); jtf2.setText(""); jtf1.setText(""); jptf1.setText(""); } else { JOptionPane.showMessageDialog(null, "Already Registered"); jtf2.setText(""); jtf1.setText(""); jptf1.setText(""); } } catch (Exception exp) { } } }); b3.addActionListener( new ActionListener() { public void actionPerformed(ActionEvent we) { String username = jtf1.getText().trim(); String password = jptf1.getText().trim(); String mechine = jtf2.getText().trim(); String Dname = box.getSelectedItem().toString(); int porte = 0; try { Statement sate = conn.createStatement(); ResultSet rsate = sate.executeQuery("select * from Dagent where uname='" + Dname + "'"); if (rsate.next()) { servermachine = rsate.getString("umechine"); porte = rsate.getInt("ulistport"); System.out.println(servermachine); } System.out.println(servermachine); } catch (Exception exp) { } try { packet p1 = new packet(); p1.setaction("clogin"); p1.setCuser(username); p1.setCpass(password); p1.setCmname(mechine); p1.setCDpeer(Dname); Socket soc1 = new Socket(servermachine, porte); ObjectOutputStream out1 = new ObjectOutputStream(soc1.getOutputStream()); out1.writeObject(p1); ObjectInputStream in1 = new ObjectInputStream(soc1.getInputStream()); packet rpac1 = (packet) in1.readObject(); if (rpac1.getaction().equals("ok")) { int port1 = 0; try { int portm = rpac1.getCport(); System.out.println("XXXXXXX" + portm); // JOptionPane.showMessageDialog(null,"Sucessfully Started"); new Listen(portm); new process(username, portm); dispose(); } catch (Exception exp) { } } else { JOptionPane.showMessageDialog( null, "Enter valid username and password", "Server reply", 2); jtf1.setText(""); jtf2.setText(""); jptf1.setText(""); } } catch (Exception exp) { } } }); setSize(400, 400); show(); setLocation(150, 100); setResizable(false); }