public void deleteAllItem() {

    if (dbe.FineDBExist()) {

      try {
        Class.forName("org.sqlite.JDBC");
        c = DriverManager.getConnection("jdbc:sqlite:c:/LibMgtSys/Fine.db");
        c.setAutoCommit(false);

        System.out.println("Opened Fine database successfully from DeleteFromDB  delete allll ");

        stmt = c.createStatement();
        String sql = "DELETE FROM FineTab";

        PreparedStatement pst = c.prepareStatement(sql);

        pst.executeUpdate();
        c.commit();

        stmt.close();
        c.close();
      } catch (Exception e) {
        System.err.println(e.getClass().getName() + ": " + e.getMessage());
        System.exit(0);
      }

      System.out.println("All Data Deleted successfully");
      //   JOptionPane.showMessageDialog(null, " ALL Item Successfully Deleted.", "Delete Option: ",
      // JOptionPane.INFORMATION_MESSAGE);

    } else {
      //  JOptionPane.showMessageDialog(null, "sorry.. no database existed..", "Error..",
      // JOptionPane.ERROR_MESSAGE);
    }
  }
 public void provideAllPrivToAll(Connection dConn, Connection gConn) {
   ArrayList<SQLException> exList = new ArrayList<SQLException>();
   for (int i = 0; i < tableNames.length; i++) {
     StringBuffer sql = new StringBuffer();
     sql.append("grant all privileges on " + tableNames[i] + " to " + getAllGrantees());
     Log.getLogWriter().info("security statement is " + sql.toString());
     if (dConn != null) {
       try {
         Statement stmt = dConn.createStatement();
         stmt.execute(sql.toString()); // execute authorization
         dConn.commit();
         stmt.close();
       } catch (SQLException se) {
         SQLHelper.handleDerbySQLException(se, exList);
       }
       try {
         Statement stmt = gConn.createStatement();
         stmt.execute(sql.toString()); // execute authorization
         gConn.commit();
         stmt.close();
       } catch (SQLException se) {
         SQLHelper.handleGFGFXDException(se, exList);
       }
     } else {
       try {
         Statement stmt = gConn.createStatement();
         stmt.execute(sql.toString()); // execute authorization
         gConn.commit();
         stmt.close();
       } catch (SQLException se) {
         SQLHelper.handleSQLException(se);
       }
     }
   }
 }
Exemple #3
0
  // 根据username删除一个老师
  public boolean deleteTh(String username) {
    Connection conn = null;
    PreparedStatement st = null;
    ResultSet rs = null;
    String sql01 = "delete from teacher where username=?";
    String sql02 = "delete from user where username=?";
    try {
      conn = JdbcUtil.getConnection();
      conn.setAutoCommit(false); // 不自动提交事务
      st = conn.prepareStatement(sql01);
      st.setString(1, username);
      st.executeUpdate();

      st = conn.prepareStatement(sql02);
      st.setString(1, username);
      st.executeUpdate();
      conn.commit();
    } catch (SQLException e) {
      try {
        conn.rollback();
        conn.commit();
      } catch (SQLException e1) {
        e1.printStackTrace();
      }
      return false;
    } finally {
      JdbcUtil.release(conn, st, rs);
    }
    return true;
  }
  /**
   * Create the table definition to export to, removing any prior table. By specifying
   * ColumnGenerator arguments, you can add extra columns to the table of arbitrary type.
   */
  public void createTable(ColumnGenerator... extraColumns) throws SQLException {
    Connection conn = getConnection();
    PreparedStatement statement =
        conn.prepareStatement(
            getDropTableStatement(getTableName()),
            ResultSet.TYPE_FORWARD_ONLY,
            ResultSet.CONCUR_READ_ONLY);
    try {
      statement.executeUpdate();
      conn.commit();
    } finally {
      statement.close();
    }

    StringBuilder sb = new StringBuilder();
    sb.append("CREATE TABLE ");
    sb.append(getTableName());
    sb.append(" (id INT NOT NULL PRIMARY KEY, msg VARCHAR(64)");
    int colNum = 0;
    for (ColumnGenerator gen : extraColumns) {
      sb.append(", " + forIdx(colNum++) + " " + gen.getType());
    }
    sb.append(")");

    statement =
        conn.prepareStatement(
            sb.toString(), ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
    try {
      statement.executeUpdate();
      conn.commit();
    } finally {
      statement.close();
    }
  }
Exemple #5
0
 private int[] batch(Config config, Connection conn, List<String> sqlList, int batchSize)
     throws SQLException {
   if (sqlList == null || sqlList.size() == 0)
     throw new IllegalArgumentException("The sqlList length must more than 0.");
   if (batchSize < 1) throw new IllegalArgumentException("The batchSize must more than 0.");
   int counter = 0;
   int pointer = 0;
   int size = sqlList.size();
   int[] result = new int[size];
   Statement st = conn.createStatement();
   for (int i = 0; i < size; i++) {
     st.addBatch(sqlList.get(i));
     if (++counter >= batchSize) {
       counter = 0;
       int[] r = st.executeBatch();
       conn.commit();
       for (int k = 0; k < r.length; k++) result[pointer++] = r[k];
     }
   }
   int[] r = st.executeBatch();
   conn.commit();
   for (int k = 0; k < r.length; k++) result[pointer++] = r[k];
   DbKit.closeQuietly(st);
   return result;
 }
  @Test
  public void test_selectTable2() throws Exception {
    String sql = "select idcard_prefix, address from id_address";
    JdbcUtil.dbChangeToMysql();
    Connection connection = JdbcUtil.getConnection();
    connection.setAutoCommit(false);
    PreparedStatement ps = connection.prepareStatement(sql);
    List<String> adds = tst_getname();
    int count = 0;

    for (int i = 0; i < adds.size(); i++) {
      String string = adds.get(i);
      String[] s = string.split("=");
      ps.setString(1, s[0]);
      ps.setString(2, s[1]);
      ps.execute();
      if (++count == 500) {
        connection.commit();
        count = 0;
      }
    }
    if (count > 0) {
      connection.commit();
    }
    JdbcUtil.close(null, ps, connection);
  }
 private void revokeDelegatedPrivilege(Connection dConn, Connection gConn, String tableName) {
   int num = SQLTest.random.nextInt(SQLTest.numOfWorkers) + 1;
   ArrayList<SQLException> exceptionList = new ArrayList<SQLException>();
   String grantees = getGrantees(num);
   StringBuffer sql = new StringBuffer();
   int whichPriv = SQLTest.random.nextInt(tablePriv.length);
   sql.append("revoke " + tablePriv[whichPriv] + " on " + tableName + " from " + grantees);
   Log.getLogWriter().info("security statement is " + sql.toString());
   try {
     Statement stmt = dConn.createStatement();
     stmt.execute(sql.toString()); // execute authorization
     dConn.commit();
     stmt.close();
   } catch (SQLException se) {
     SQLHelper.handleDerbySQLException(se, exceptionList);
   }
   try {
     Statement stmt = gConn.createStatement();
     stmt.execute(sql.toString()); // execute authorization
     gConn.commit();
     stmt.close();
   } catch (SQLException se) {
     SQLHelper.handleGFGFXDException(se, exceptionList);
   }
 }
 @Test
 @Parameters({"task1", "sql1", "sql2"})
 public void testCounter(String resource, String sql1, String sql2) throws Exception {
   createRandomProductsJob(sql2, 100);
   Connection connection = source.getConnectionForReading();
   ResultSet results = connection.createStatement().executeQuery(sql1);
   if (!connection.getAutoCommit()) {
     connection.commit();
   }
   int count = results.next() ? results.getInt(1) : -1;
   source.close(results);
   source.closeReading();
   assertEquals(count, 100);
   perform(resource);
   assertHits("1", 100);
   // count docs in source table, must be null
   connection = source.getConnectionForReading();
   // sql1 = select count(*)
   results = connection.createStatement().executeQuery(sql1);
   if (!connection.getAutoCommit()) {
     connection.commit();
   }
   count = results.next() ? results.getInt(1) : -1;
   results.close();
   assertEquals(count, 0);
 }
  private void doTearDown() throws Exception {
    Connection c1 = null;
    Connection c2 = null;
    Statement s1 = null;
    Statement s2 = null;
    try {
      c1 = myDS.getConnection();
      c2 = myDS2.getConnection();

      s1 = c1.createStatement();
      s2 = c2.createStatement();

      s1.execute("drop table " + TABLE1);
      c1.commit();
      s2.execute("drop table " + TABLE2);
      c2.commit();

    } catch (IllegalStateException e) {
      System.err.println("Caught expected IllegalStateException from Atomikos on doTearDown");
      doTearDown();
    } finally {
      if (s1 != null) s1.close();
      if (s2 != null) s2.close();
      if (c1 != null) c1.close();
      if (c2 != null) c2.close();
    }
  }
 // Sicherheitchecks
 // Checken ob ein User an einem Spiel teilnimmt.
 private boolean isUserInGame(int userID, int gameID) {
   Connection con = DatabaseConnector.getConnection();
   Statement stmt;
   ResultSet rs;
   try {
     stmt = con.createStatement();
     // Checken ob Spieler am Spiel teilnimmt
     rs =
         stmt.executeQuery(
             "SELECT user_id FROM stations NATURAL JOIN games WHERE user_id="
                 + userID
                 + " AND game_id="
                 + gameID);
     if (rs.next()) {
       con.commit();
       return true;
     } else {
       con.commit();
       return false;
     }
   } catch (SQLException e) {
     e.printStackTrace();
     return false;
   }
 }
 // Checken ob ein User an der Reihe ist in diesem Spiel.
 private boolean isUserNextPlayer(int userID, int gameID) {
   if (!isUserInGame(userID, gameID)) {
     return false;
   }
   Connection con = DatabaseConnector.getConnection();
   Statement stmt;
   ResultSet rs;
   try {
     stmt = con.createStatement();
     // Checken ob Spieler am Spiel teilnimmt
     int nextStationType = -1, stationType = -2;
     rs = stmt.executeQuery("SELECT next_station_type FROM games WHERE game_id=" + gameID);
     if (rs.next()) {
       nextStationType = rs.getInt("next_station_type");
       con.commit();
     }
     rs =
         stmt.executeQuery(
             "SELECT station_type FROM stations NATURAL JOIN users WHERE game_id="
                 + gameID
                 + " AND user_id="
                 + userID);
     if (rs.next()) {
       stationType = rs.getInt("station_type");
       con.commit();
     }
     if (nextStationType == stationType) return true;
     else return false;
   } catch (SQLException e) {
     e.printStackTrace();
     return false;
   }
 }
  private void doSetup() throws Exception {

    if (setupDone) return;

    Connection c1 = null;
    Connection c2 = null;
    Statement s1 = null;
    Statement s2 = null;
    try {
      c1 = myDS.getConnection();
      c2 = myDS2.getConnection();

      s1 = c1.createStatement();
      s2 = c2.createStatement();

      s1.execute("create table " + TABLE1 + " ( id INTEGER, foo INTEGER )");
      s1.executeUpdate("insert into " + TABLE1 + " (id, foo) values (1, 1)");
      c1.commit();
      s2.execute("create table " + TABLE2 + " ( id INTEGER, foo INTEGER )");
      s2.executeUpdate("insert into " + TABLE2 + " (id, foo) values (1, 1)");
      c2.commit();

      setupDone = true;
    } finally {
      if (s1 != null) s1.close();
      if (s2 != null) s2.close();
      if (c1 != null) c1.close();
      if (c2 != null) c2.close();
    }
  }
Exemple #13
0
  private void removeReserva(Long reservaId) {

    DataSource dataSource = DataSourceLocator.getDataSource(OFERTA_DATA_SOURCE);

    try (Connection connection = dataSource.getConnection()) {

      try {

        /* Prepare connection. */
        connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
        connection.setAutoCommit(false);

        /* Do work. */
        reservaDao.remove(connection, reservaId);

        /* Commit. */
        connection.commit();

      } catch (InstanceNotFoundException e) {
        connection.commit();
        throw new RuntimeException(e);
      } catch (SQLException e) {
        connection.rollback();
        throw new RuntimeException(e);
      } catch (RuntimeException | Error e) {
        connection.rollback();
        throw e;
      }

    } catch (SQLException e) {
      throw new RuntimeException(e);
    }
  }
  private int completeUpdate(
      SearchIndexBuilderWorker worker, Connection connection, List runtimeToDo) throws Exception {
    try {

      for (Iterator tditer = runtimeToDo.iterator(); worker.isRunning() && tditer.hasNext(); ) {
        SearchBuilderItem sbi = (SearchBuilderItem) tditer.next();
        if (SearchBuilderItem.STATE_COMPLETED.equals(sbi.getSearchstate())) {
          if (SearchBuilderItem.ACTION_DELETE.equals(sbi.getSearchaction())) {
            delete(connection, sbi);
            connection.commit();
          } else {
            updateOrSave(connection, sbi);
            connection.commit();
          }
        }
      }
      return runtimeToDo.size();
    } catch (Exception ex) {
      log.warn(
          "Failed to update state in database due to " //$NON-NLS-1$
              + ex.getMessage()
              + " this will be corrected on the next run of the IndexBuilder, no cause for alarm"); //$NON-NLS-1$
    }
    return 0;
  }
Exemple #15
0
 private int[] batch(Config config, Connection conn, String sql, Object[][] paras, int batchSize)
     throws SQLException {
   if (paras == null || paras.length == 0)
     throw new IllegalArgumentException("The paras array length must more than 0.");
   if (batchSize < 1) throw new IllegalArgumentException("The batchSize must more than 0.");
   int counter = 0;
   int pointer = 0;
   int[] result = new int[paras.length];
   PreparedStatement pst = conn.prepareStatement(sql);
   for (int i = 0; i < paras.length; i++) {
     for (int j = 0; j < paras[i].length; j++) {
       Object value = paras[i][j];
       if (config.dialect.isOracle() && value instanceof java.sql.Date)
         pst.setDate(j + 1, (java.sql.Date) value);
       else pst.setObject(j + 1, value);
     }
     pst.addBatch();
     if (++counter >= batchSize) {
       counter = 0;
       int[] r = pst.executeBatch();
       conn.commit();
       for (int k = 0; k < r.length; k++) result[pointer++] = r[k];
     }
   }
   int[] r = pst.executeBatch();
   conn.commit();
   for (int k = 0; k < r.length; k++) result[pointer++] = r[k];
   DbKit.closeQuietly(pst);
   return result;
 }
  public void testLoggingAutoActivate() throws Throwable {
    assertFalse(
        "Auto-logging of misuse of the wrapper should be off",
        BorrowedConnectionProxy.isCallStackTraced());

    UserTransaction txn = transactionService.getUserTransaction();
    Connection connection;
    try {
      txn.begin();
      // Dig the proxy out of ... somewhere
      ConnectionHolder conHolder =
          (ConnectionHolder) TransactionSynchronizationManager.getResource(dataSource);
      connection = conHolder.getConnection();
      txn.commit();
    } catch (Throwable e) {
      try {
        txn.rollback();
      } catch (Throwable ee) {
      }
      throw e;
    }
    // Now mess with the connection, which is protected by the Hibernate wrapper
    try {
      connection.commit();
      fail("Use case should have generated a HibernateException");
    } catch (HibernateException e) {
      // Expected
    }
    assertTrue(
        "Auto-logging of misuse of the wrapper should now be on",
        BorrowedConnectionProxy.isCallStackTraced());

    // Now start a new transaction and we should see logging
    txn = transactionService.getUserTransaction();
    try {
      txn.begin();
      // Dig the proxy out of ... somewhere
      ConnectionHolder conHolder =
          (ConnectionHolder) TransactionSynchronizationManager.getResource(dataSource);
      connection = conHolder.getConnection();
      txn.commit();
    } catch (Throwable e) {
      try {
        txn.rollback();
      } catch (Throwable ee) {
      }
      throw e;
    }
    // Now mess with the connection, which is protected by the Hibernate wrapper
    try {
      connection.commit();
      fail("Use case should have generated a HibernateException");
    } catch (HibernateException e) {
      // Expected
    }
    // Check for error logs
  }
Exemple #17
0
  public <S> void execute(InputLoader<S> inputLoader, InputConverter<S> converter) {
    long count = 0;
    Connection readConnection = null;
    Statement stmt = null;
    ResultSet rs = null;
    Connection writeConnection = null;
    PreparedStatement writeStatement = null;
    try {
      writeConnection = db.getDataSource().getConnection();
      writeConnection.setAutoCommit(false);
      writeStatement = writeConnection.prepareStatement(converter.updateSql());

      readConnection = db.getDataSource().getConnection();
      readConnection.setAutoCommit(false);

      stmt =
          readConnection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
      stmt.setFetchSize(GROUP_SIZE);
      if (db.getDialect().getId().equals(MySql.ID)) {
        stmt.setFetchSize(Integer.MIN_VALUE);
      } else {
        stmt.setFetchSize(GROUP_SIZE);
      }
      rs = stmt.executeQuery(convertSelectSql(inputLoader.selectSql(), db));

      int cursor = 0;
      while (rs.next()) {
        if (converter.convert(inputLoader.load(rs), writeStatement)) {
          writeStatement.addBatch();
          cursor++;
          count++;
        }

        if (cursor == GROUP_SIZE) {
          writeStatement.executeBatch();
          writeConnection.commit();
          cursor = 0;
        }
      }
      if (cursor > 0) {
        writeStatement.executeBatch();
        writeConnection.commit();
      }

    } catch (SQLException e) {
      SqlUtil.log(LOGGER, e);
      throw processError(e);
    } catch (Exception e) {
      throw processError(e);
    } finally {
      DbUtils.closeQuietly(writeStatement);
      DbUtils.closeQuietly(writeConnection);
      DbUtils.closeQuietly(readConnection, stmt, rs);

      LOGGER.info("{} rows have been updated", count);
    }
  }
  public void doDDLOp(Connection dConn, Connection gConn) {
    boolean success = false;
    int maxNumOfTries = 1;
    ArrayList<SQLException> exList = new ArrayList<SQLException>();
    String tableName = tableNames[SQLTest.random.nextInt(tableNames.length)];
    String routineName = (hasRoutine) ? getRoutineNames(dConn, gConn) : null;
    StringBuffer aStr = new StringBuffer();

    // grant or revoke
    String act = action[SQLTest.random.nextInt(action.length)];
    aStr.append(act);

    // rest of the authorization stmt
    if (routineName == null) aStr.append(getAuthForTables(tableName, act));
    else
      aStr.append(
          SQLTest.random.nextBoolean()
              ? getAuthForTables(tableName, act)
              : getAuthForRoutines(routineName, act));

    if (dConn != null) {
      try {
        success = applySecurityToDerby(dConn, aStr.toString(), exList); // insert to derby table
        int count = 0;
        while (!success) {
          if (count >= maxNumOfTries) {
            Log.getLogWriter()
                .info("Could not get the lock to finish grant/revoke stmt, abort this operation");
            return;
          }
          exList.clear();
          success =
              applySecurityToDerby(
                  dConn, aStr.toString(), exList); // retry insert to derby table
          count++;
        }

        applySecurityToGFE(gConn, aStr.toString(), exList); // insert to gfe table
        SQLHelper.handleMissedSQLException(exList);
        gConn.commit();
        dConn.commit();
      } catch (SQLException se) {
        SQLHelper.printSQLException(se);
        throw new TestException("execute security statement fails " + TestHelper.getStackTrace(se));
      } // for verification
    } else {
      try {
        applySecurityToGFE(gConn, aStr.toString()); // insert to gfe table
        gConn.commit();
      } catch (SQLException se) {
        SQLHelper.printSQLException(se);
        throw new TestException("execute security statement fails " + TestHelper.getStackTrace(se));
      }
    } // no verification
  }
  protected void createTiles() throws InterruptedException, MapCreationException {
    @SuppressWarnings("unused") // W #unused
    int maxMapProgress =
        2 * (mMap.getXMax() - mMap.getXMin() + 1) * (mMap.getYMax() - mMap.getYMin() + 1);
    // bundleProgress.initMapCreation(maxMapProgress);
    TileImageParameters param = mMap.getParameters();
    // if (param != null)
    // mapDlTileProvider = new ConvertedRawTileProvider(mapDlTileProvider, param.getFormat());
    try {
      conn.setAutoCommit(false);
      int batchTileCount = 0;
      int tilesWritten = 0;
      Runtime r = Runtime.getRuntime();
      long heapMaxSize = r.maxMemory();
      prepStmt = conn.prepareStatement(getTileInsertSQL());
      for (int x = mMap.getXMin(); x <= mMap.getXMax(); x++) {
        for (int y = mMap.getYMin(); y <= mMap.getYMax(); y++) {
          // checkUserAbort();
          // bundleProgress.incMapCreationProgress();
          try {
            // byte[] sourceTileData = mapDlTileProvider.getTileData(x, y);
            byte[] sourceTileData = null;
            if (sourceTileData != null) {
              writeTile(x, y, mMap.getZoom(), sourceTileData);
              tilesWritten++;
              long heapAvailable = heapMaxSize - r.totalMemory() + r.freeMemory();

              batchTileCount++;
              if ((heapAvailable < HEAP_MIN) || (batchTileCount >= MAX_BATCH_SIZE)) {
                log.trace("Executing batch containing " + batchTileCount + " tiles");
                prepStmt.executeBatch();
                prepStmt.clearBatch();
                System.gc();
                conn.commit();
                // bundleProgress.incMapCreationProgress(batchTileCount);
                batchTileCount = 0;
              }
            }
          } catch (IOException e) {
            throw new MapCreationException(mMap, e);
          }
        }
      }
      prepStmt.executeBatch();
      prepStmt.clearBatch();
      System.gc();
      if (tilesWritten > 0) updateTileMetaInfo();
      log.trace("Final commit containing " + batchTileCount + " tiles");
      conn.commit();
      // bundleProgress.setMapCreationProgress(maxMapProgress);
    } catch (SQLException e) {
      throw new MapCreationException(mMap, e);
    }
  }
Exemple #20
0
  private void failLeftTasks() throws SQLException {
    Connection connection = DatabaseManager.CreateConnection();
    connection.setAutoCommit(false);

    try {
      AccessChecksInserter dbAccessChecks = new AccessChecksInserter(connection);
      for (Service service : services) {
        int serviceId = service.getId();

        List<Task> newTasks = newTasksAll.get(serviceId);
        synchronized (newTasks) {
          for (Task task : newTasks)
            dbAccessChecks.Insert(
                task.team.getId(),
                task.serviceId,
                CheckerExitCode.CheckerError.toInt(),
                "In newTasks queue",
                "Round timeout",
                0,
                task.id);

          newTasks.clear();
          connection.commit();
        }

        HashMap<UUID, Task> processingTasks = processingTasksAll.get(serviceId);
        synchronized (processingTasks) {
          for (Task task : processingTasks.values())
            dbAccessChecks.Insert(
                task.team.getId(),
                task.serviceId,
                CheckerExitCode.Down.toInt(),
                "In processingTasks queue",
                "Round timeout",
                0,
                task.id);

          processingTasks.clear();
          connection.commit();
        }
        List<Task> doneTasks = doneTasksAll.get(serviceId);
        synchronized (doneTasks) {
          doneTasks.clear();
        }
      }
    } catch (SQLException e) {
      connection.rollback();
      throw e;
    } finally {
      connection.close();
    }
  }
Exemple #21
0
  @SuppressWarnings("unchecked")
  public DSResponse remove(DSRequest req) throws Exception {
    Map<String, Object> map = req.getOldValues();
    DSResponse responce = new DSResponse(map);
    Connection connMaps = null;
    Connection connBilling = null;
    PreparedStatement psDelete = null;

    try {
      connMaps = DBConnection.getConnection("MAP");
      connBilling = DBConnection.getConnection("Gass");
      int cusid = new Integer(map.get("buid").toString());
      psDelete = connMaps.prepareStatement("delete from district_meters where cusid=?");
      psDelete.setInt(1, cusid);
      psDelete.executeUpdate();
      removeDistrict_Meter_mapping(cusid, connMaps);
      removeDistrict_Meter_mapping(cusid, connBilling);
      connMaps.commit();
      connBilling.commit();
      return responce;
    } catch (Exception e) {
      e.printStackTrace();
      try {
        connMaps.rollback();
      } catch (Exception e2) {
        // TODO: handle exception
      }
      try {
        connBilling.rollback();
      } catch (Exception e2) {
        // TODO: handle exception
      }
      throw new Exception(e.toString());
    } finally {
      try {
        psDelete.close();
      } catch (Exception e2) {
        // TODO: handle exception
      }
      try {
        DBConnection.freeConnection(connBilling);
      } catch (Exception e2) {
        // TODO: handle exception
      }
      try {
        DBConnection.freeConnection(connMaps);
      } catch (Exception e2) {
        // TODO: handle exception
      }
    }
  }
Exemple #22
0
  @Test
  public void testInflightDeleteNotSeen() throws Exception {
    String selectSQL = "SELECT * FROM " + FULL_TABLE_NAME;
    try (Connection conn1 = DriverManager.getConnection(getUrl());
        Connection conn2 = DriverManager.getConnection(getUrl())) {
      conn1.setAutoCommit(false);
      conn2.setAutoCommit(true);
      ResultSet rs = conn1.createStatement().executeQuery(selectSQL);
      assertFalse(rs.next());

      String upsert =
          "UPSERT INTO "
              + FULL_TABLE_NAME
              + "(varchar_pk, char_pk, int_pk, long_pk, decimal_pk, date_pk) VALUES(?, ?, ?, ?, ?, ?)";
      PreparedStatement stmt = conn1.prepareStatement(upsert);
      // upsert two rows
      TestUtil.setRowKeyColumns(stmt, 1);
      stmt.execute();
      TestUtil.setRowKeyColumns(stmt, 2);
      stmt.execute();

      conn1.commit();

      rs = conn1.createStatement().executeQuery("SELECT count(*) FROM " + FULL_TABLE_NAME);
      assertTrue(rs.next());
      assertEquals(2, rs.getInt(1));

      String delete = "DELETE FROM " + FULL_TABLE_NAME + " WHERE varchar_pk = 'varchar1'";
      stmt = conn1.prepareStatement(delete);
      int count = stmt.executeUpdate();
      assertEquals(1, count);

      rs = conn1.createStatement().executeQuery("SELECT count(*) FROM " + FULL_TABLE_NAME);
      assertTrue(rs.next());
      assertEquals(1, rs.getInt(1));
      assertFalse(rs.next());

      rs = conn2.createStatement().executeQuery("SELECT count(*) FROM " + FULL_TABLE_NAME);
      assertTrue(rs.next());
      assertEquals(2, rs.getInt(1));
      assertFalse(rs.next());

      conn1.commit();

      rs = conn2.createStatement().executeQuery("SELECT count(*) FROM " + FULL_TABLE_NAME);
      assertTrue(rs.next());
      assertEquals(1, rs.getInt(1));
      assertFalse(rs.next());
    }
  }
 private void writeDB(
     final JerModel jerModel,
     final boolean isInsert,
     final boolean isUpdate,
     final boolean isDelete) {
   try {
     try {
       conn.setAutoCommit(false);
       if (isInsert) {
         try (PreparedStatement psJm = jerModel.getPreparer().createInsertStmt(conn)) {
           psJm.executeUpdate();
         }
         conn.commit();
         for (final PreparedStatement stmt :
             jerModel
                 .getPreparer()
                 .getPrepStmtsFromMultiFields(conn, getMaxIDValueForTable(jerModel))) {
           try (PreparedStatement psJm = stmt) {
             psJm.executeUpdate();
           }
         }
       } else if (isUpdate) {
         for (final PreparedStatement stmt : jerModel.getPreparer().createUpdateStmts(conn)) {
           try (PreparedStatement psJm = stmt) {
             psJm.executeUpdate();
           }
         }
       } else if (isDelete) {
         for (final PreparedStatement stmt : jerModel.getPreparer().createDelStmts(conn)) {
           try (PreparedStatement psJm = stmt) {
             psJm.executeUpdate();
           }
         }
       }
       conn.commit();
       setChanged();
       notifyObservers(new JerObserverMsg(JerMsgType.DB_UPDATE));
     } catch (SQLException | NumberFormatException nfe) {
       conn.rollback();
       conn.setAutoCommit(true);
       throw nfe;
     } finally {
       conn.setAutoCommit(true);
     }
   } catch (final SQLException e1) {
     LOGGER.severe(e1.getMessage());
     setModelAndID(jerModel, -1);
     jerInfoMsgHandler.showMsg("@" + jerModel.getIdName() + ": " + e1.getLocalizedMessage());
   }
 }
  public void testCloseGlobalContextNoRecycle() throws Exception {
    if (log.isDebugEnabled()) {
      log.debug("*** Starting testCloseGlobalContextNoRecycle");
    }
    TransactionManager tm = TransactionManagerServices.getTransactionManager();
    tm.begin();

    Connection c1 = pds.getConnection();
    Connection c2 = pds.getConnection();
    c1.createStatement();
    c1.close();
    assertTrue(c1.isClosed());

    try {
      c1.createStatement();
      fail("expected SQLException");
    } catch (SQLException ex) {
      assertEquals("connection handle already closed", ex.getMessage());
    }

    c2.createStatement();

    try {
      c2.commit();
      fail("expected SQLException");
    } catch (SQLException ex) {
      assertEquals("cannot commit a resource enlisted in a global transaction", ex.getMessage());
    }

    tm.commit();
    assertFalse(c2.isClosed());

    c2.close();
    assertTrue(c2.isClosed());

    try {
      c2.createStatement();
      fail("expected SQLException");
    } catch (SQLException ex) {
      assertEquals("connection handle already closed", ex.getMessage());
    }

    try {
      c2.commit();
      fail("expected SQLException");
    } catch (SQLException ex) {
      assertEquals("connection handle already closed", ex.getMessage());
    }
  }
Exemple #25
0
  /**
   * Cleaning.
   *
   * @throws SQLException
   */
  private static void doClean(DBCleanerTool dbCleaner) throws DBCleanException, SQLException {
    Connection jdbcConn = dbCleaner.getConnection();
    try {
      dbCleaner.clean();
      dbCleaner.commit();

      jdbcConn.commit();
    } catch (SQLException e) {
      jdbcConn.rollback();

      dbCleaner.rollback();

      jdbcConn.commit();
    }
  }
  private void loadData(long ts) throws SQLException {
    Properties props = new Properties(TEST_PROPERTIES);
    props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts));
    Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
    int groupFactor = NUM_ROWS_INSERTED / 2;
    for (int i = 0; i < NUM_ROWS_INSERTED; i++) {
      insertRow(conn, Integer.toString(i % (groupFactor)), 10);

      if ((i % 1000) == 0) {
        conn.commit();
      }
    }
    conn.commit();
    conn.close();
  }
Exemple #27
0
 public void parseUser(String fileName) {
   Connection c = null;
   Statement stmt = null;
   XMLInputFactory xmlInputFactory = XMLInputFactory.newInstance();
   try {
     XMLStreamReader xmlStreamReader =
         xmlInputFactory.createXMLStreamReader(new FileInputStream(fileName));
     Class.forName("org.sqlite.JDBC");
     c = DriverManager.getConnection("jdbc:sqlite:test.db");
     c.setAutoCommit(false);
     stmt = c.createStatement();
     int event;
     int i = 0;
     while (xmlStreamReader.hasNext()) {
       event = xmlStreamReader.next();
       i++;
       if (event == XMLStreamConstants.START_ELEMENT)
         if (xmlStreamReader.getLocalName().equals("row")) {
           try {
             // xmlStreamReader.getAttributeValue if not found,
             // will return null.
             int id = Integer.parseInt(xmlStreamReader.getAttributeValue(null, "Id"));
             String displayName = xmlStreamReader.getAttributeValue(null, "DisplayName");
             if (displayName == null) {
               throw new Exception("Empty DisplayName");
             }
             String sql =
                 "INSERT INTO USER (ID,USERNAME) " + "VALUES (" + id + ",'" + displayName + "');";
             stmt.executeUpdate(sql);
           } catch (Exception e) {
             System.out.println(
                 "invalid user entry. " + e.getClass().getName() + ": " + e.getMessage());
             continue;
           }
         }
       // Every 100 entries commit once
       if (i == 100) {
         i = 0;
         c.commit();
       }
     }
     c.commit();
     stmt.close();
     c.close();
   } catch (Exception e) {
     System.out.println("Failed to parse. " + e.getClass().getName() + ": " + e.getMessage());
   }
 }
  /** Finishes the execution context */
  public static void finish() {
    Connection conn = JForumExecutionContext.getConnection(false);

    if (conn != null) {
      if (SystemGlobals.getBoolValue(ConfigKeys.DATABASE_USE_TRANSACTIONS)) {
        if (JForumExecutionContext.shouldRollback()) {
          try {
            conn.rollback();
          } catch (Exception e) {
            logger.error("Error while rolling back a transaction", e);
          }
        } else {
          try {
            conn.commit();
          } catch (Exception e) {
            logger.error("Error while commiting a transaction", e);
          }
        }
      }

      try {
        DBConnection.getImplementation().releaseConnection(conn);
      } catch (Exception e) {
        logger.error("Error while releasing the connection : " + e, e);
      }
    }

    userData.set(null);
  }
Exemple #29
0
  /**
   * <br>
   * [機 能] ラベルの削除を行う <br>
   * [解 説] <br>
   * [備 考]
   *
   * @param con コネクション
   * @param paramMdl パラメータ情報
   * @throws SQLException SQL実行時例外
   */
  public void deleteLabel(Connection con, Sml310ParamModel paramMdl) throws SQLException {

    boolean commitFlg = false;

    try {
      // ラベルを削除する
      SmlLabelDao slDao = new SmlLabelDao(con);
      slDao.delete(paramMdl.getSmlEditLabelId());

      // メール - ラベルを削除する
      SmlJmeisLabelDao jDao = new SmlJmeisLabelDao(con);
      jDao.delete(paramMdl.getSmlEditLabelId());
      SmlSmeisLabelDao sDao = new SmlSmeisLabelDao(con);
      sDao.delete(paramMdl.getSmlEditLabelId());
      SmlWmeisLabelDao wDao = new SmlWmeisLabelDao(con);
      wDao.delete(paramMdl.getSmlEditLabelId());

      // メール情報表示順を削除する
      SmlLabelSortDao mailSortDao = new SmlLabelSortDao(con);
      mailSortDao.deleteMailSortOfLabel(paramMdl.getSmlEditLabelId());

      // コミット実行
      con.commit();
      commitFlg = true;
    } catch (SQLException e) {
      log__.error("SQLException", e);
      throw e;
    } finally {
      if (!commitFlg) {
        JDBCUtil.rollback(con);
      }
    }
  }
  @Override
  protected ResultSet doInBackground(String... strings) {

    String ls_usuario = "pavel";
    String ls_clave = "pavelito";
    String ls_sql = "insert into punto (color,cantidad) values('" + strings[1] + "',1)";

    try {
      Class.forName("com.mysql.jdbc.Driver");
      conexionMysql = DriverManager.getConnection(strings[0], ls_usuario, ls_clave);
      Statement estado = conexionMysql.createStatement();
      estado.execute(ls_sql);
      System.out.println("Conexion realizada");
      conexionMysql.commit();
      conexionMysql.close();

    } catch (ClassNotFoundException e) {
      /*Toast.makeText(getApplicationContext(), "ERROR:" + e.getMessage(), Toast.LENGTH_SHORT).show();*/
      e.printStackTrace();
    } catch (SQLException e) {
      /*Toast.makeText(getApplicationContext(), "ERROR: " + e.getMessage(), Toast.LENGTH_SHORT).show();*/
      e.printStackTrace();
    }

    return null;
  }