示例#1
0
  public void excluirLote(List<JobLote> listaExcluir) {

    Connection conn = null;
    PreparedStatement stmt = null;
    String sql = "delete from joblote where job = ? and operacao = ? and lote =? ";

    try {

      conn = GerenciaConexaoSQLServer.abreConexao();
      conn.setAutoCommit(false);

      for (int i = 0; i <= listaExcluir.size() - 1; i++) {

        JobLote job = listaExcluir.get(i);

        stmt = conn.prepareStatement(sql);
        stmt.setString(1, job.getJob().trim().replace(".", ""));
        stmt.setInt(2, job.getOperNum());
        stmt.setInt(3, job.getLote());
        stmt.executeUpdate();

        alteraJobAposExclusao(conn, job);

        gravaJobsExcluidos(
            conn, job.getJob().trim().replace(".", ""), job.getOperNum(), job.getLote());
      }

      // adiciona a op na tela inicial
      for (int i = 0; i <= listaExcluir.size() - 1; i++) {
        JobLote job = listaExcluir.get(i);
        JobProtheus jobProtheus = retornaJob(conn, job);
        adicionaOPTelaInicial(conn, jobProtheus, job);
      }

      JOptionPane.showMessageDialog(this, "Job Excluido com Sucesso!");
      conn.commit();

    } catch (SQLException e) {

      if (conn != null) {

        try {

          conn.rollback();
          conn.setAutoCommit(true);

          JOptionPane.showMessageDialog(null, "Não foi possivel excluir! Descrição: " + e);

        } catch (SQLException ex) {
          JOptionPane.showMessageDialog(null, "Erro ao fazer o rollback! Descrição: " + ex);
        }
      }

    } finally {
      GerenciaConexaoSQLServer.closeConexao(conn, stmt);
    }
  }
示例#2
0
 void runPrepareTest() {
   setProgressMinMax(0, m_nNumRuns - 1);
   int nProgressStep = m_nNumRuns / 10 + 1;
   try {
     boolean m_bTransactionsUsed = m_bTrans && m_conn.getMetaData().supportsTransactions();
     boolean bCurrentAutoCommit = m_conn.getAutoCommit();
     m_conn.setAutoCommit(!m_bTransactionsUsed);
     int nAccNum, nBranchNum, nTellerNum;
     double dDelta, dBalance;
     log(
         "Starting SQL prepare/execute benchmark for "
             + m_nNumRuns
             + ((m_time > 0) ? " min.\n" : " runs\n"),
         0);
     java.util.Random rand = new java.util.Random(m_nMaxAccount + m_nMaxBranch + m_nMaxTeller);
     for (int nRun = 0; (m_time > 0) ? true : nRun < m_nNumRuns; nRun++) {
       if (m_time > 0) {
         java.util.Date current = new java.util.Date();
         if ((current.getTime() - m_time) > (m_nNumRuns * 60000)) break;
       }
       PreparedStatement updAccStmt = null,
           selAccStmt = null,
           updTellerStmt = null,
           updBranchStmt = null,
           insHistStmt = null;
       try {
         nAccNum = (int) (rand.nextFloat() * rand.nextFloat() * (m_nMaxAccount - 1)) + 1;
         nBranchNum = (int) (rand.nextFloat() * (m_nMaxBranch - 1)) + 1;
         nTellerNum = (int) (rand.nextFloat() * (m_nMaxTeller - 1)) + 1;
         dDelta =
             ((double)
                     ((long)
                         ((((int) (rand.nextFloat() * (m_nMaxTeller - 1)) + 1)
                                 * (rand.nextFloat() > 0.5 ? -1 : 1))
                             * 100)))
                 / 100;
         // prepare statements
         updAccStmt =
             m_conn.prepareStatement(
                 "UPDATE "
                     + m_Driver.getAccountName()
                     + " SET balance = balance + ? WHERE account = ?");
         selAccStmt =
             m_conn.prepareStatement(
                 "SELECT balance FROM " + m_Driver.getAccountName() + " WHERE account = ?");
         updTellerStmt =
             m_conn.prepareStatement(
                 "UPDATE "
                     + m_Driver.getTellerName()
                     + " SET balance = balance + ? WHERE teller = ?");
         updBranchStmt =
             m_conn.prepareStatement(
                 "UPDATE "
                     + m_Driver.getBranchName()
                     + " SET balance = balance + ? WHERE branch = ?");
         insHistStmt =
             m_conn.prepareStatement(
                 "INSERT INTO "
                     + m_Driver.getHistoryName()
                     + " (histid, account, teller, branch, amount, timeoftxn, filler) VALUES (? , ? , ? , ? , ? , "
                     + m_nowFunction
                     + " , ?)");
         // bind parameters
         updAccStmt.setDouble(1, dDelta);
         updAccStmt.setInt(2, nAccNum);
         //					System.out.println(nAccNum);
         selAccStmt.setInt(1, nAccNum);
         updTellerStmt.setDouble(1, dDelta);
         updTellerStmt.setInt(2, nTellerNum);
         updBranchStmt.setDouble(1, dDelta);
         updBranchStmt.setInt(2, nBranchNum);
         insHistStmt.setInt(1, nRun);
         insHistStmt.setInt(2, nAccNum);
         insHistStmt.setInt(3, nTellerNum);
         insHistStmt.setInt(4, nBranchNum);
         insHistStmt.setDouble(5, dDelta);
         insHistStmt.setString(6, BenchPanel.strFiller.substring(0, 21));
         java.util.Date startTime = new java.util.Date();
         // execute statements
         log(
             "UPDATE "
                 + m_Driver.getAccountName()
                 + " SET balance = balance + "
                 + dDelta
                 + " WHERE account = "
                 + nAccNum
                 + "\n",
             2);
         updAccStmt.executeUpdate();
         log(
             "SELECT balance FROM "
                 + m_Driver.getAccountName()
                 + " WHERE account = "
                 + nAccNum
                 + "\n",
             2);
         ResultSet balanceSet = selAccStmt.executeQuery();
         balanceSet.next();
         dBalance = balanceSet.getFloat(1);
         //					if (balanceSet == null)
         //						System.out.println("balanceSet is NULL");
         //					else {
         //						String strBalance = balanceSet.getString(1);
         //						System.out.println(balanceSet.wasNull() ? "SQL NULL" : strBalance);
         //					}
         balanceSet.close();
         log(
             "UPDATE "
                 + m_Driver.getTellerName()
                 + " SET balance = balance + "
                 + dDelta
                 + " WHERE teller = "
                 + nTellerNum
                 + "\n",
             2);
         updTellerStmt.executeUpdate();
         log(
             "UPDATE "
                 + m_Driver.getBranchName()
                 + " SET balance = balance + "
                 + dDelta
                 + " WHERE branch = "
                 + nBranchNum
                 + "\n",
             2);
         updBranchStmt.executeUpdate();
         log(
             "INSERT INTO "
                 + m_Driver.getHistoryName()
                 + " (histid, account, teller, branch, amount, timeoftxn, filler) VALUES ("
                 + nRun
                 + " , "
                 + nAccNum
                 + " , "
                 + nTellerNum
                 + " , "
                 + nBranchNum
                 + " , "
                 + dDelta
                 + " , "
                 + m_nowFunction
                 + " , \'"
                 + BenchPanel.strFiller.substring(0, 21)
                 + "\')\n",
             2);
         insHistStmt.executeUpdate();
         if (m_bQuery) executeQuery();
         if (m_bTransactionsUsed) m_conn.commit();
         java.util.Date endTime = new java.util.Date();
         m_nTrans += 1;
         double diff = endTime.getTime() - startTime.getTime();
         if (diff < 1000) m_nTrans1Sec += 1;
         else if (diff < 2000) m_nTrans2Sec += 1;
         m_nTimeSum += diff / 1000;
       } catch (SQLException e1) {
         // System.err.println(e1.getMessage());
         break;
       } finally {
         try {
           if (updAccStmt != null) updAccStmt.close();
           if (selAccStmt != null) selAccStmt.close();
           if (updTellerStmt != null) updTellerStmt.close();
           if (updBranchStmt != null) updBranchStmt.close();
           if (insHistStmt != null) insHistStmt.close();
         } catch (SQLException e) {
         }
       }
       if (nRun % nProgressStep == 0) setProgressValue(nRun);
       // yield();
     }
     setProgressValue(m_nNumRuns - 1);
     m_conn.setAutoCommit(bCurrentAutoCommit);
   } catch (SQLException e) {
     // JOptionPane.showMessageDialog(null, e.getMessage(), "SQL Error in Text test",
     // JOptionPane.ERROR_MESSAGE);
     log("SQLError in prepare test : " + e.getMessage(), 0);
   }
 }
示例#3
0
 void runTextTest() {
   Statement stmt = null;
   try {
     //			System.out.println("Thread :" + getName() + " Text test entered");
     setProgressMinMax(0, m_nNumRuns - 1);
     int nProgressStep = m_nNumRuns / 10 + 1;
     //			System.out.println("Thread :" + getName() + " before commit state");
     boolean m_bTransactionsUsed = m_bTrans && m_conn.getMetaData().supportsTransactions();
     //			System.out.println("Thread :" + getName() + " are transactions used");
     boolean bCurrentAutoCommit = m_conn.getAutoCommit();
     //			System.out.println("Thread :" + getName() + "got currentCommit");
     m_conn.setAutoCommit(!m_bTransactionsUsed);
     //			System.out.println("Thread :" + getName() + " commit state set");
     stmt = m_conn.createStatement();
     int nAccNum, nBranchNum, nTellerNum;
     double dDelta, dBalance;
     log(
         "Starting SQL text benchmark for " + m_nNumRuns + ((m_time > 0) ? " min.\n" : " runs\n"),
         0);
     java.util.Random rand = new java.util.Random(m_nMaxAccount + m_nMaxBranch + m_nMaxTeller);
     for (int nRun = 0; (m_time > 0) ? true : nRun < m_nNumRuns; nRun++) {
       if (m_time > 0) {
         java.util.Date current = new java.util.Date();
         if ((current.getTime() - m_time) > (m_nNumRuns * 60000)) break;
       }
       try {
         nAccNum = (int) (rand.nextFloat() * rand.nextFloat() * (m_nMaxAccount - 1)) + 1;
         nBranchNum = (int) (rand.nextFloat() * (m_nMaxBranch - 1)) + 1;
         nTellerNum = (int) (rand.nextFloat() * (m_nMaxTeller - 1)) + 1;
         dDelta =
             ((double)
                     ((long)
                         ((((int) (rand.nextFloat() * (m_nMaxTeller - 1)) + 1)
                                 * (rand.nextFloat() > 0.5 ? -1 : 1))
                             * 100)))
                 / 100;
         java.util.Date startTime = new java.util.Date();
         log(
             "UPDATE "
                 + m_Driver.getAccountName()
                 + " SET balance = balance + "
                 + dDelta
                 + " WHERE account = "
                 + nAccNum
                 + "\n",
             2);
         stmt.executeUpdate(
             "UPDATE "
                 + m_Driver.getAccountName()
                 + " SET balance = balance + "
                 + dDelta
                 + " WHERE account = "
                 + nAccNum);
         log(
             "SELECT balance FROM "
                 + m_Driver.getAccountName()
                 + " WHERE account = "
                 + nAccNum
                 + "\n",
             2);
         ResultSet balanceSet =
             stmt.executeQuery(
                 "SELECT balance FROM "
                     + m_Driver.getAccountName()
                     + " WHERE account = "
                     + nAccNum);
         balanceSet.next();
         dBalance = balanceSet.getDouble(1);
         balanceSet.close();
         log(
             "UPDATE "
                 + m_Driver.getTellerName()
                 + " SET balance = balance + "
                 + dDelta
                 + " WHERE teller = "
                 + nTellerNum
                 + "\n",
             2);
         stmt.executeUpdate(
             "UPDATE "
                 + m_Driver.getTellerName()
                 + " SET balance = balance + "
                 + dDelta
                 + " WHERE teller = "
                 + nTellerNum);
         log(
             "UPDATE "
                 + m_Driver.getBranchName()
                 + " SET balance = balance + "
                 + dDelta
                 + " WHERE branch = "
                 + nBranchNum
                 + "\n",
             2);
         stmt.executeUpdate(
             "UPDATE "
                 + m_Driver.getBranchName()
                 + " SET balance = balance + "
                 + dDelta
                 + " WHERE branch = "
                 + nBranchNum);
         log(
             "INSERT INTO "
                 + m_Driver.getHistoryName()
                 + " (histid, account, teller, branch, amount, timeoftxn, filler) VALUES ("
                 + nRun
                 + " , "
                 + nAccNum
                 + " , "
                 + nTellerNum
                 + " , "
                 + nBranchNum
                 + " , "
                 + dDelta
                 + " , "
                 + m_nowFunction
                 + " , \'"
                 + BenchPanel.strFiller.substring(0, 21)
                 + "\')\n",
             2);
         stmt.executeUpdate(
             "INSERT INTO "
                 + m_Driver.getHistoryName()
                 + " (histid, account, teller, branch, amount, timeoftxn, filler) VALUES ("
                 + nRun
                 + " , "
                 + nAccNum
                 + " , "
                 + nTellerNum
                 + " , "
                 + nBranchNum
                 + " , "
                 + dDelta
                 + " , "
                 + m_nowFunction
                 + " , \'"
                 + BenchPanel.strFiller.substring(0, 21)
                 + "\')");
         if (m_bQuery) executeQuery();
         //					System.out.println("Done query");
         if (m_bTransactionsUsed) m_conn.commit();
         java.util.Date endTime = new java.util.Date();
         //					System.out.println("Done");
         m_nTrans += 1;
         long diff = endTime.getTime() - startTime.getTime();
         if (diff < 1000) m_nTrans1Sec += 1;
         else if (diff < 2000) m_nTrans2Sec += 1;
         m_nTimeSum += ((double) diff) / 1000;
       } catch (SQLException e1) {
         // System.err.println(e1.getMessage());
         // e1.printStackTrace();
         break;
       }
       if (nRun % nProgressStep == 0) setProgressValue(nRun);
       // yield();
     }
     setProgressValue(m_nNumRuns - 1);
     m_conn.setAutoCommit(bCurrentAutoCommit);
   } catch (SQLException e) {
     // e.printStackTrace();
     // JOptionPane.showMessageDialog(null, e.getMessage(), "SQL Error in Text test",
     // JOptionPane.ERROR_MESSAGE);
     log("SQLError in text test : " + e.getMessage(), 0);
   } finally {
     if (stmt != null)
       try {
         stmt.close();
       } catch (SQLException e) {
       }
   }
 }
  public void actionPerformed(ActionEvent ev) {

    String s = ev.getActionCommand();

    if (s == null) {
      if (ev.getSource() instanceof JMenuItem) {
        JMenuItem i;

        s = ((JMenuItem) ev.getSource()).getText();
      }
    }

    /*
    // button replace by toolbar
            if (s.equals("Execute")) {
                execute();
            } else
    */
    if (s.equals("Exit")) {
      windowClosing(null);
    } else if (s.equals("Transfer")) {
      Transfer.work(null);
    } else if (s.equals("Dump")) {
      Transfer.work(new String[] {"-d"});
    } else if (s.equals("Restore")) {
      Transfer.work(new String[] {"-r"});
    } else if (s.equals("Logging on")) {
      javaSystem.setLogToSystem(true);
    } else if (s.equals("Logging off")) {
      javaSystem.setLogToSystem(false);
    } else if (s.equals("Refresh Tree")) {
      refreshTree();
    } else if (s.startsWith("#")) {
      int i = Integer.parseInt(s.substring(1));

      txtCommand.setText(sRecent[i]);
    } else if (s.equals("Connect...")) {
      connect(ConnectionDialogSwing.createConnection(fMain, "Connect"));
      refreshTree();
    } else if (s.equals("Results in Grid")) {
      iResult = 0;

      pResult.removeAll();
      pResult.add(gScrollPane, BorderLayout.CENTER);
      pResult.doLayout();
      gResult.fireTableChanged(null);
      pResult.repaint();
    } else if (s.equals("Open Script...")) {
      JFileChooser f = new JFileChooser(".");

      f.setDialogTitle("Open Script...");

      // (ulrivo): set default directory if set from command line
      if (defDirectory != null) {
        f.setCurrentDirectory(new File(defDirectory));
      }

      int option = f.showOpenDialog(fMain);

      if (option == JFileChooser.APPROVE_OPTION) {
        File file = f.getSelectedFile();

        if (file != null) {
          StringBuffer buf = new StringBuffer();

          ifHuge = DatabaseManagerCommon.readFile(file.getAbsolutePath());

          if (4096 <= ifHuge.length()) {
            buf.append("This huge file cannot be edited. Please execute\n");
            txtCommand.setText(buf.toString());
          } else {
            txtCommand.setText(ifHuge);
          }
        }
      }
    } else if (s.equals("Save Script...")) {
      JFileChooser f = new JFileChooser(".");

      f.setDialogTitle("Save Script");

      // (ulrivo): set default directory if set from command line
      if (defDirectory != null) {
        f.setCurrentDirectory(new File(defDirectory));
      }

      int option = f.showSaveDialog(fMain);

      if (option == JFileChooser.APPROVE_OPTION) {
        File file = f.getSelectedFile();

        if (file != null) {
          DatabaseManagerCommon.writeFile(file.getAbsolutePath(), txtCommand.getText());
        }
      }
    } else if (s.equals("Save Result...")) {
      JFileChooser f = new JFileChooser(".");

      f.setDialogTitle("Save Result...");

      // (ulrivo): set default directory if set from command line
      if (defDirectory != null) {
        f.setCurrentDirectory(new File(defDirectory));
      }

      int option = f.showSaveDialog(fMain);

      if (option == JFileChooser.APPROVE_OPTION) {
        File file = f.getSelectedFile();

        if (file != null) {
          showResultInText();
          DatabaseManagerCommon.writeFile(file.getAbsolutePath(), txtResult.getText());
        }
      }
    } else if (s.equals("Results in Text")) {
      iResult = 1;

      pResult.removeAll();
      pResult.add(txtResultScroll, BorderLayout.CENTER);
      pResult.doLayout();
      showResultInText();
      pResult.repaint();
    } else if (s.equals("AutoCommit on")) {
      try {
        cConn.setAutoCommit(true);
      } catch (SQLException e) {
      }
    } else if (s.equals("AutoCommit off")) {
      try {
        cConn.setAutoCommit(false);
      } catch (SQLException e) {
      }
    } else if (s.equals("Commit")) {
      try {
        cConn.commit();
      } catch (SQLException e) {
      }
    } else if (s.equals("Insert test data")) {
      insertTestData();
    } else if (s.equals("Rollback")) {
      try {
        cConn.rollback();
      } catch (SQLException e) {
      }
    } else if (s.equals("Disable MaxRows")) {
      try {
        sStatement.setMaxRows(0);
      } catch (SQLException e) {
      }
    } else if (s.equals("Set MaxRows to 100")) {
      try {
        sStatement.setMaxRows(100);
      } catch (SQLException e) {
      }
    } else if (s.equals("SELECT")) {
      showHelp(DatabaseManagerCommon.selectHelp);
    } else if (s.equals("INSERT")) {
      showHelp(DatabaseManagerCommon.insertHelp);
    } else if (s.equals("UPDATE")) {
      showHelp(DatabaseManagerCommon.updateHelp);
    } else if (s.equals("DELETE")) {
      showHelp(DatabaseManagerCommon.deleteHelp);
    } else if (s.equals("CREATE TABLE")) {
      showHelp(DatabaseManagerCommon.createTableHelp);
    } else if (s.equals("DROP TABLE")) {
      showHelp(DatabaseManagerCommon.dropTableHelp);
    } else if (s.equals("CREATE INDEX")) {
      showHelp(DatabaseManagerCommon.createIndexHelp);
    } else if (s.equals("DROP INDEX")) {
      showHelp(DatabaseManagerCommon.dropIndexHelp);
    } else if (s.equals("CHECKPOINT")) {
      showHelp(DatabaseManagerCommon.checkpointHelp);
    } else if (s.equals("SCRIPT")) {
      showHelp(DatabaseManagerCommon.scriptHelp);
    } else if (s.equals("SHUTDOWN")) {
      showHelp(DatabaseManagerCommon.shutdownHelp);
    } else if (s.equals("SET")) {
      showHelp(DatabaseManagerCommon.setHelp);
    } else if (s.equals("Test Script")) {
      showHelp(DatabaseManagerCommon.testHelp);
    }
  }