Beispiel #1
0
 public static void main(String[] args) {
   conn = getConnection();
   QueryRunner qr = new QueryRunner();
   try {
     conn.setAutoCommit(false);
     List<Syuser> al =
         (List)
             qr.query(
                 conn, "SELECT syuser.* FROM syuser", new BeanListHandler<Syuser>(Syuser.class));
     for (Syuser u : al) {
       List<Syrole> rl =
           qr.query(
               conn,
               "SELECT syrole.* FROM syrole JOIN syuser_syrole ON syuser_syrole.SYROLE_ID = syrole.ID WHERE syuser_syrole.SYUSER_ID = ?",
               new BeanListHandler<Syrole>(Syrole.class),
               u.getId());
       u.getSyroles().addAll(rl);
       System.out.println(u.getLoginname());
       System.out.println(JSON.toJSONString(u.getSyroles()));
     }
   } catch (SQLException e) {
     e.printStackTrace();
     try {
       DbUtils.rollback(conn);
     } catch (SQLException e1) {
       e1.printStackTrace();
     }
   } finally {
     try {
       DbUtils.commitAndClose(conn);
     } catch (SQLException e) {
       e.printStackTrace();
     }
   }
 }
  @Override
  public void execute(final Context context) throws SQLException {
    final Date now = new Date(system.now());

    Connection connection = null;
    PreparedStatement pstmt = null;
    try {
      connection = openConnection();
      pstmt = connection.prepareStatement("SELECT p.uuid as uuid FROM projects p WHERE p.kee=?");

      MassUpdate massUpdate = context.prepareMassUpdate();
      massUpdate.select(
          "SELECT f.id, f.data FROM issue_filters f WHERE f.data like '%componentRoots=%'");
      massUpdate.update("UPDATE issue_filters SET data=?, updated_at=? WHERE id=?");
      final PreparedStatement finalPstmt = pstmt;
      massUpdate.execute(
          new MassUpdate.Handler() {
            @Override
            public boolean handle(Select.Row row, SqlStatement update) throws SQLException {
              Long id = row.getNullableLong(1);
              String data = row.getNullableString(2);
              if (data == null) {
                return false;
              }
              update.setString(1, convertData(finalPstmt, data));
              update.setDate(2, now);
              update.setLong(3, id);
              return true;
            }
          });
    } finally {
      DbUtils.closeQuietly(connection);
      DbUtils.closeQuietly(pstmt);
    }
  }
  private String getContentFromDB(String itemID) throws Exception {
    StringBuilder dbContent = new StringBuilder("");
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      Connection connexion = ConnectionFactory.getConnection();
      pstmt =
          connexion.prepareStatement(
              "select SR_SEQ_NUM, SR_TEXT from SR_SCRIPTS where SR_ITEM_ID = ? order by 1");
      pstmt.setString(1, itemID);
      rs = pstmt.executeQuery();
      while (rs.next()) {
        dbContent = dbContent.append(rs.getString("SR_TEXT"));
      }

    } catch (Exception e) {
      throw new Exception(
          "\r\n***ERROR RETURNED BY THE JVM : "
              + e.getMessage()
              + "\r\n(select SR_SEQ_NUM, SR_TEXT from SR_SCRIPTS where SR_ITEM_ID = '"
              + itemID
              + "'  order by 1)");
    } finally {
      DbUtils.closeQuietly(rs);
      DbUtils.closeQuietly(pstmt);
    }
    return dbContent.toString();
  }
Beispiel #4
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);
    }
  }
 // Cache en BD via JDBC une séquence de désinstallation
 // le paramètre est la liste des valeurs à insérer dans la table
 // SR_UNINSTITEMS
 public void cacheIntoDB(
     Connection connexion,
     String _package,
     int _itemOrder,
     String _pieceType,
     String _delimiter,
     Integer _keepDelimiter,
     String _dbProcName)
     throws Exception {
   setConnection(connexion);
   PreparedStatement pstmt = null;
   // insertion SR_UNINSTITEMS
   long theLong = System.currentTimeMillis();
   String itemID = String.valueOf(theLong) + '-' + increment.incrementAndGet();
   try {
     pstmt =
         connexion.prepareStatement(
             "insert into SR_UNINSTITEMS(SR_ITEM_ID, "
                 + "SR_PACKAGE, SR_ACTION_TAG, SR_ITEM_ORDER, SR_FILE_NAME, SR_FILE_TYPE, SR_DELIMITER, "
                 + "SR_KEEP_DELIMITER, SR_DBPROC_NAME) values ( ?, ?, ?, ?, ?, ?, ?, ?, ?)");
     pstmt.setString(1, itemID);
     pstmt.setString(2, _package);
     pstmt.setString(3, actionName);
     pstmt.setInt(4, _itemOrder);
     pstmt.setString(5, pieceName);
     pstmt.setString(6, _pieceType);
     pstmt.setString(7, _delimiter);
     pstmt.setInt(8, _keepDelimiter);
     pstmt.setString(9, _dbProcName);
     pstmt.executeUpdate();
   } catch (Exception ex) {
     throw new Exception("\n\t\t***ERROR RETURNED BY THE RDBMS : " + ex.getMessage() + '\n', ex);
   } finally {
     DbUtils.closeQuietly(pstmt);
   }
   try {
     // insertion SR_SCRIPTS
     final String[] subS = getSubStrings(content);
     pstmt =
         connexion.prepareStatement(
             "insert into SR_SCRIPTS(SR_ITEM_ID, SR_SEQ_NUM, SR_TEXT) " + "values (?, ?, ? )");
     for (int i = 0; i < subS.length; i++) {
       pstmt.setString(1, itemID);
       pstmt.setInt(2, i);
       pstmt.setString(3, subS[i]);
       pstmt.executeUpdate();
     }
   } catch (Exception ex) {
     throw new Exception("\n\t\t***ERROR RETURNED BY THE RDBMS : " + ex.getMessage() + '\n', ex);
   } finally {
     DbUtils.closeQuietly(pstmt);
   }
 }
 /**
  * {@inheritDoc}
  *
  * <p>此操作会清空 Cache 中 authorities 对应的 value,下次请求时装载。
  *
  * @see #getAll()
  */
 public void delete(Authority auth) throws JibuException {
   Connection conn = null;
   try {
     conn = ConnectionUtils.getConnection();
     authDAO.delete(conn, auth);
     DbUtils.commitAndClose(conn);
     Cache cache = CacheUtils.getAuthCache();
     cache.remove("authorities");
   } catch (SQLException e) {
     DbUtils.rollbackAndCloseQuietly(conn);
     throw new JibuException(e.getMessage());
   }
 }
  public String loadSchema() {
    save();

    loadSchemaResult = false;
    Connection conn = null;
    String url =
        "jdbc:mysql://"
            + dbconfig.getHost()
            + ":"
            + dbconfig.getPort()
            + "/"
            + dbconfig.getDatabase_name();
    String sql_tables =
        String.format(
            "select TABLE_SCHEMA, TABLE_NAME, TABLE_COMMENT from information_schema.tables where TABLE_SCHEMA=\"%s\";",
            dbconfig.getDatabase_name());
    String sql_columns =
        String.format(
            "select TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_TYPE, COLUMN_KEY, COLUMN_COMMENT from information_schema.columns where TABLE_SCHEMA=\"%s\";",
            dbconfig.getDatabase_name());
    String sql_keys =
        String.format(
            "select CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME from information_schema.KEY_COLUMN_USAGE where TABLE_SCHEMA=\"%s\";",
            dbconfig.getDatabase_name());

    try {
      DbUtils.loadDriver(driver);
      conn = DriverManager.getConnection(url, dbconfig.getUsername(), dbconfig.getPassword());
      QueryRunner qr = new QueryRunner();
      List<Map<String, Object>> tables = qr.query(conn, sql_tables, new MapListHandler());
      List<Map<String, Object>> columns = qr.query(conn, sql_columns, new MapListHandler());
      List<Map<String, Object>> keys = qr.query(conn, sql_keys, new MapListHandler());
      dbconfig.setTables_json(JSONUtil.serialize(tables, false));
      dbconfig.setColumns_json(JSONUtil.serialize(columns, false));
      dbconfig.setKeys_json(JSONUtil.serialize(keys, false));
      DbUtils.closeQuietly(conn);
    } catch (Exception ex) {
      return ERROR;
    }
    loadSchemaResult = true;
    SaveLog.saveLog(
        new Users((long) 1),
        "11",
        "数据库映射",
        new Date(),
        "数据库-企业数据库-模板映射—导入数据库配置方案",
        Constant.DATABASE_OPERATION,
        this.getClass().getName());
    return SUCCESS;
  }
  List<MeasureFilterRow> execute(Connection connection) throws SQLException {
    PreparedStatement statement = connection.prepareStatement(sql);
    ResultSet rs = null;
    try {
      for (int index = 0; index < dateParameters.size(); index++) {
        statement.setLong(index + 1, dateParameters.get(index));
      }
      rs = statement.executeQuery();
      return process(rs);

    } finally {
      DbUtils.closeQuietly(rs);
      DbUtils.closeQuietly(statement);
    }
  }
Beispiel #9
0
  @SuppressWarnings({"unchecked", "rawtypes"})
  public List<MenuItemDTO> listeGetir() {
    List<MenuItemDTO> liste = new ArrayList<MenuItemDTO>();

    Connection con = null;
    try {
      con = DAOBase.instance().getConnection();
    } catch (CreateException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }
    try {
      con.setAutoCommit(false);
    } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }
    QueryRunner runner = new QueryRunner();

    try {

      liste =
          (List<MenuItemDTO>)
              runner.query(con, Sorgular._MENULIST_.qry, new BeanListHandler(MenuItemDTO.class));

    } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    } finally {

      DbUtils.closeQuietly(con);
    }

    return liste == null ? new ArrayList<MenuItemDTO>() : liste;
  }
Beispiel #10
0
  private Queue<long[]> initGroupOfViolationIds(Database database) throws SQLException {
    Connection connection = database.getDataSource().getConnection();
    Statement stmt = null;
    ResultSet rs = null;
    try {
      connection.setAutoCommit(false);
      stmt = connection.createStatement();
      stmt.setFetchSize(10000);
      rs = stmt.executeQuery("select id from rule_failures");
      Queue<long[]> queue = new ConcurrentLinkedQueue<>();

      totalViolations = 0;
      long[] block = new long[VIOLATION_GROUP_SIZE];
      int cursor = 0;
      while (rs.next()) {
        block[cursor] = rs.getLong(1);
        cursor++;
        totalViolations++;
        if (cursor == VIOLATION_GROUP_SIZE) {
          queue.add(block);
          block = new long[VIOLATION_GROUP_SIZE];
          cursor = 0;
        }
      }
      if (cursor > 0) {
        queue.add(block);
      }
      return queue;
    } finally {
      DbUtils.closeQuietly(connection, stmt, rs);
    }
  }
 private void joinWorldAssembly(Connection conn, int nationId) throws SQLException {
   PreparedStatement endorsements =
       conn.prepareStatement("UPDATE assembly.nation SET wa_member = 1 WHERE id = ?");
   endorsements.setInt(1, nationId);
   endorsements.executeUpdate();
   DbUtils.closeQuietly(endorsements);
 }
 private void relocateNation(Connection conn, int nationId, String nation, String happening)
     throws SQLException {
   Matcher match = Utils.REGION_PATTERN.matcher(happening);
   String prevRegion = null;
   String newRegion = null;
   if (match.find()) {
     String title = happening.substring(match.start() + 2, match.end() - 2);
     prevRegion = Utils.sanitizeName(title);
   }
   if (match.find()) {
     String title = happening.substring(match.start() + 2, match.end() - 2);
     newRegion = Utils.sanitizeName(title);
   }
   Logger.info("Relocating " + nation + " from " + prevRegion + " to " + newRegion);
   if (prevRegion != null && newRegion != null) {
     // Double check they are still at their prev region before setting their new region!
     int newRegionId = getOrCreateRegion(conn, nation, newRegion);
     PreparedStatement update =
         conn.prepareStatement(
             "UPDATE assembly.nation SET region = ?, wa_member = 2 WHERE id = ? AND region = ?");
     update.setInt(1, newRegionId);
     update.setInt(2, nationId);
     update.setInt(3, getOrCreateRegion(conn, nation, prevRegion));
     update.executeUpdate();
     DbUtils.closeQuietly(update);
   }
 }
 private int getRegionOfNation(Connection conn, int nationId) throws SQLException {
   PreparedStatement select = null;
   ResultSet result = null;
   try {
     select = conn.prepareStatement("SELECT region FROM assembly.nation WHERE id = ?");
     select.setInt(1, nationId);
     result = select.executeQuery();
     if (result.next()) {
       return result.getInt(1);
     }
     return -1;
   } finally {
     DbUtils.closeQuietly(result);
     DbUtils.closeQuietly(select);
   }
 }
Beispiel #14
0
  @SuppressWarnings({"unchecked", "rawtypes"})
  public List<AnaMenu> anaListeGetir() throws ReadException {
    List<AnaMenu> liste = new ArrayList<AnaMenu>();

    Connection con = null;
    try {
      con = DAOBase.instance().getConnection();

      con.setAutoCommit(false);

      QueryRunner runner = new QueryRunner();

      liste =
          (List<AnaMenu>)
              runner.query(con, Sorgular._ANAMENULIST_.qry, new BeanListHandler(AnaMenu.class));
      for (AnaMenu s : liste) {
        s.setAltMenuler(altListeGetir(con, s.getMenuitemId()));
      }

    } catch (CreateException e) {
      throw new ReadException(Messages._SQL_500_.getMesaj(), null);

    } catch (SQLException e) {
      throw new ReadException(Messages._SQL_500_.getMesaj(), null);
    } finally {

      DbUtils.closeQuietly(con);
    }

    return liste == null ? new ArrayList<AnaMenu>() : liste;
  }
Beispiel #15
0
  private List<Project> fetchAllActiveProjects(Connection connection)
      throws ProjectManagerException {
    QueryRunner runner = new QueryRunner();

    ProjectResultHandler handler = new ProjectResultHandler();
    List<Project> projects = null;
    try {
      projects = runner.query(connection, ProjectResultHandler.SELECT_ALL_ACTIVE_PROJECTS, handler);

      for (Project project : projects) {
        List<Triple<String, Boolean, Permission>> permissions =
            fetchPermissionsForProject(connection, project);

        for (Triple<String, Boolean, Permission> entry : permissions) {
          if (entry.getSecond()) {
            project.setGroupPermission(entry.getFirst(), entry.getThird());
          } else {
            project.setUserPermission(entry.getFirst(), entry.getThird());
          }
        }
      }
    } catch (SQLException e) {
      throw new ProjectManagerException("Error retrieving all projects", e);
    } finally {
      DbUtils.closeQuietly(connection);
    }

    return projects;
  }
  /**
   * Preloads the data
   *
   * @param request request from the client
   * @param response response to the client
   * @throws ServletException if any error occurs while executing the servlet
   * @throws IOException if any error occurs in the client/server communication.
   */
  private void preload(HttpServletRequest req, HttpServletResponse resp)
      throws ServletException, IOException {

    List<AreaBean> areaDomain = null;
    List<LinguaBean> languageDomain = null;
    List<UniversitaBean> universityDomain = null;
    Connection conn = null;
    Message m = null;

    try {
      conn = DS.getConnection();
      languageDomain = GetLinguaValues.getLinguaDomain(conn);
      areaDomain = GetAreaValues.getAreaDomain(conn);
      universityDomain = GetUniversitaValues.getDomain(conn);
    } catch (SQLException ex) {
      m = new Message("Error while getting the classes list.", "XXX", "Please, contact the admin.");
      req.setAttribute("message", m);
      errorForward(req, resp);
      return;
    } finally {
      DbUtils.closeQuietly(conn); // always closes the connection
    }

    // Send data to the view
    req.setAttribute("languageDomain", languageDomain);
    req.setAttribute("areaDomain", areaDomain);
    req.setAttribute("universities", universityDomain);

    /* Forward to the Search JSP page */
    getServletContext().getRequestDispatcher("/jsp/search_class.jsp").forward(req, resp);
  }
Beispiel #17
0
  private static List<FirmaDTO> getUpdateFirmalar() throws ReadException {
    QueryRunner runner = new QueryRunner();
    Connection con = null;

    List<FirmaDTO> list = new ArrayList<FirmaDTO>();
    try {
      con = dao.getConnection();
      con.setAutoCommit(false);
      list =
          (List<FirmaDTO>)
              runner.query(
                  con,
                  "select  kod, unvan, adsoyad, gsm_telefon, il, ilce, adres, durumu, tescil_no,"
                      + "uygunluk_belgesi, gecerlilik_suresi, servis_sozlesme, sozlesme_tarihi,"
                      + "tse_belge_no, eposta,monte_eden as monteEden,"
                      + "yetkili_servis as yetkiliServis,"
                      + "tse_belgesi as tseBelgesi,"
                      + "telefon_no as telefonNo,"
                      + "telefon_no_dahili as dahili,"
                      + "ce_belge_tipi as ceBelgeTipi FROM "
                      + "akm.bakimci_firma WHERE durumu='1'",
                  new BeanListHandler<FirmaDTO>(FirmaDTO.class));

    } catch (Exception e) {
      throw new ReadException(e.getMessage(), null);
    } finally {
      DbUtils.closeQuietly(con);
    }
    return list;
  }
  /**
   * Forwards the pre-loaded data to the insert form
   *
   * @param request request from the client
   * @param response response to the client
   * @throws ServletException if any error occurs while executing the servlet
   * @throws IOException if any error occurs in the client/server communication.
   */
  protected void doGet(HttpServletRequest req, HttpServletResponse resp)
      throws ServletException, IOException {

    List<LinguaBean> languageDomain = null;
    List<AreaBean> areaDomain = null;
    Connection conn = null;
    Message m = null;

    try {
      conn = DS.getConnection();
      languageDomain = GetLinguaValues.getLinguaDomain(conn);
      areaDomain = GetAreaValues.getAreaDomain(conn);
    } catch (SQLException ex) {
      m = new Message("Error while getting the class.", "XXX", "Please, contact the admin.");
    } finally {
      DbUtils.closeQuietly(conn); // always closes the connection
    }

    if (m == null) {
      // forward to the insert FORM
      req.setAttribute("languageDomain", languageDomain);
      req.setAttribute("areaDomain", areaDomain);
      getServletContext().getRequestDispatcher("/jsp/insert_class.jsp").forward(req, resp);
    } else {
      req.setAttribute("message", m);
      getServletContext().getRequestDispatcher("/jsp/error.jsp").forward(req, resp);
    }
  }
 public void executeSingleUpdate(String currentInstruction) throws Exception {
   if (traceMode) {
     String printableInstruction = StringUtil.sReplace("\r\n", " ", currentInstruction);
     printableInstruction = StringUtil.sReplace("\t", " ", printableInstruction);
     if (printableInstruction.length() > 147) {
       printableInstruction = printableInstruction.substring(0, 146) + "...";
     }
     console.printMessage("\t\t>" + printableInstruction);
   }
   Statement stmt = connection.createStatement();
   try {
     stmt.executeUpdate(currentInstruction);
   } catch (Exception e) {
     throw new Exception(
         "\r\n***ERROR RETURNED BY THE RDBMS : "
             + e.getMessage()
             + "\r\n***STATEMENT ON ERROR IS : "
             + currentInstruction
             + " "
             + pieceName,
         e);
   } finally {
     DbUtils.closeQuietly(stmt);
   }
 }
 public static void updateRegionHappenings(
     Connection conn,
     DatabaseAccess access,
     int nationId,
     int happeningId,
     String happening,
     HappeningType type)
     throws SQLException, ExecutionException {
   String region1Happening = type.transformToRegion1Happening(happening);
   String region2Happening = type.transformToRegion2Happening(happening);
   List<Integer> regionIds = new ArrayList<Integer>(2);
   Matcher regions = Utils.REGION_PATTERN.matcher(happening);
   while (regions.find()) {
     regionIds.add(
         access
             .getRegionIdCache()
             .get(happening.substring(regions.start() + 2, regions.end() - 2)));
   }
   if (regionIds.size() == 0 && nationId > -1) {
     PreparedStatement select =
         conn.prepareStatement("SELECT region FROM assembly.nation WHERE id = ?");
     select.setInt(1, nationId);
     ResultSet result = select.executeQuery();
     if (result.next()) {
       regionIds.add(result.getInt(1));
     }
     DbUtils.closeQuietly(result);
     DbUtils.closeQuietly(select);
   }
   PreparedStatement insert =
       conn.prepareStatement(
           "INSERT INTO assembly.regional_happenings (global_id, region, happening) VALUES (?, ?, ?)");
   if (region1Happening != null && regionIds.size() > 0) {
     insert.setInt(1, happeningId);
     insert.setInt(2, regionIds.get(0));
     insert.setString(3, region1Happening);
     insert.executeUpdate();
   }
   if (region2Happening != null && regionIds.size() > 1) {
     insert.setInt(1, happeningId);
     insert.setInt(2, regionIds.get(1));
     insert.setString(3, region2Happening);
     insert.executeUpdate();
   }
   DbUtils.closeQuietly(insert);
 }
 private void addEndorsement(Connection conn, int endorsed, int endorser) throws SQLException {
   PreparedStatement selectDuplicates =
       conn.prepareStatement(
           "SELECT endorsed FROM assembly.endorsements WHERE endorsed = ? AND endorser = ?");
   selectDuplicates.setInt(1, endorsed);
   selectDuplicates.setInt(2, endorser);
   if (!selectDuplicates.executeQuery().next()) {
     PreparedStatement endorsements =
         conn.prepareStatement(
             "INSERT INTO assembly.endorsements (endorser, endorsed) VALUES (?, ?)");
     endorsements.setInt(1, endorser);
     endorsements.setInt(2, endorsed);
     endorsements.executeUpdate();
     DbUtils.closeQuietly(endorsements);
   }
   DbUtils.closeQuietly(selectDuplicates);
 }
  @Override
  public List<Schedule> loadSchedules() throws ScheduleManagerException {
    logger.info("Loading all schedules from db.");
    Connection connection = getConnection();

    QueryRunner runner = new QueryRunner();
    ResultSetHandler<List<Schedule>> handler = new ScheduleResultHandler();

    List<Schedule> schedules;

    try {
      schedules = runner.query(connection, SELECT_ALL_SCHEDULES, handler);
    } catch (SQLException e) {
      logger.error(SELECT_ALL_SCHEDULES + " failed.");

      DbUtils.closeQuietly(connection);
      throw new ScheduleManagerException("Loading schedules from db failed. ", e);
    }

    // filter the schedules
    for (Schedule sched : schedules) {
      if (!sched.updateTime()) {
        logger.info(
            "Schedule "
                + sched.getScheduleName()
                + " was scheduled before azkaban start, skipping it.");
        schedules.remove(sched);
        removeSchedule(sched);
      } else {
        try {
          updateNextExecTime(sched);
        } catch (Exception e) {
          DbUtils.closeQuietly(connection);
          throw new ScheduleManagerException("Update next execution time failed.", e);
        }
        logger.info("Schedule " + sched.getScheduleName() + " loaded and updated.");
      }
    }

    DbUtils.closeQuietly(connection);

    logger.info("Loaded " + schedules.size() + " schedules.");

    return schedules;
  }
 public static Connection getConnection() { // 用于获得数据库连接的工具方法
   try {
     DbUtils.loadDriver(DRIVER); // 加载驱动
     conn = DriverManager.getConnection(URL, USERNAME, PASSWORD); // 建立连接
   } catch (SQLException e) {
     e.printStackTrace();
   }
   return conn;
 }
 private void removeEndorsement(Connection conn, int endorsed, int endorser) throws SQLException {
   PreparedStatement endorsements =
       conn.prepareStatement(
           "DELETE FROM assembly.endorsements WHERE endorsed = ? AND endorser = ?");
   endorsements.setInt(1, endorsed);
   endorsements.setInt(2, endorser);
   endorsements.executeUpdate();
   DbUtils.closeQuietly(endorsements);
 }
 private void resignFromWorldAssembly(Connection conn, int nationId, boolean banned)
     throws SQLException {
   PreparedStatement endorsements =
       conn.prepareStatement(
           "UPDATE assembly.nation SET wa_member = " + (banned ? "0" : "2") + " WHERE id = ?");
   endorsements.setInt(1, nationId);
   endorsements.executeUpdate();
   DbUtils.closeQuietly(endorsements);
 }
 private synchronized void setRegionUpdateTime(Connection conn, int nationId, long timestamp)
     throws SQLException {
   final int region = getRegionOfNation(conn, nationId);
   if (region != -1) {
     PreparedStatement select = null, update = null, insert = null;
     ResultSet result = null;
     try {
       select =
           conn.prepareStatement(
               "SELECT id, start, end FROM assembly.region_updates WHERE region = ? AND start BETWEEN ? AND ?");
       select.setInt(1, region);
       select.setLong(2, timestamp - Duration.standardHours(1).getMillis());
       select.setLong(3, timestamp + Duration.standardHours(1).getMillis());
       result = select.executeQuery();
       if (result.next()) {
         final int id = result.getInt(1);
         final long start = result.getLong(2);
         final long end = result.getLong(3);
         update =
             conn.prepareStatement(
                 "UPDATE assembly.region_updates SET start = ?, end = ? WHERE id = ?");
         update.setLong(1, Math.min(start, timestamp));
         update.setLong(2, Math.max(end, timestamp));
         update.setInt(3, id);
         update.executeUpdate();
       } else {
         insert =
             conn.prepareStatement(
                 "INSERT INTO assembly.region_updates (region, start, end) VALUES (?, ?, ?)");
         insert.setLong(1, region);
         insert.setLong(2, timestamp);
         insert.setLong(3, timestamp);
         insert.executeUpdate();
       }
     } finally {
       DbUtils.closeQuietly(result);
       DbUtils.closeQuietly(insert);
       DbUtils.closeQuietly(update);
       DbUtils.closeQuietly(select);
     }
   } else {
     Logger.info("Can not set region update time for nation [" + nationId + "], unknown region!");
   }
 }
Beispiel #27
0
  /** 부분 커밋하면서 입력한다. ex) batchInsert(dataSource,"QQ", params, 1000); */
  public static void batchInsert(
      DataSource dataSource, String tableName, List<Object[]> params, int commitInterval) {
    Connection conn = null;
    try {
      conn = dataSource.getConnection();
      conn.setAutoCommit(false);

      QueryRunner runner = new QueryRunner();

      StringAppender appender = new StringAppender();
      appender.appendLine(
          "SELECT a.TABLE_NAME,a.COLUMN_NAME,COMMENTS,DATA_TYPE,DATA_LENGTH,DATA_PRECISION,DATA_SCALE");
      appender.appendLine("FROM user_tab_columns a JOIN USER_COL_COMMENTS b");
      appender.appendLine("ON a.COLUMN_NAME = b.COLUMN_NAME AND a.TABLE_NAME = b.TABLE_NAME");
      appender.appendLine("WHERE a.TABLE_NAME = ? ");
      appender.appendLine("ORDER BY a.TABLE_NAME, COLUMN_ID ");

      List<String> columnNames = Lists.newArrayList();
      // 걍 한번 쓸거라 일케 함
      List<Map<String, Object>> result =
          runner.query(conn, appender.toString(), LIST_MAP_HANDLER, tableName);
      for (Map<String, Object> value : result) {
        columnNames.add(value.get("COLUMN_NAME").toString());
      }

      String sql =
          "INSERT INTO "
              + tableName
              + " ("
              + StringUtil.join(columnNames, ",")
              + ") values ("
              + StringUtil.iterateStr("?", ",", columnNames.size())
              + ")";

      List<List<Object[]>> splited = CollectionUtil.splitBySize(params, commitInterval);
      for (List<Object[]> inputList : splited) {

        Object[][] convertedList = new Object[inputList.size()][];

        for (int i = 0; i < convertedList.length; i++) {
          Object[] input = inputList.get(i);
          Object[] converted = new Object[input.length];
          for (int j = 0; j < input.length; j++) {
            converted[j] = CONVERTER.convert(input[j]);
          }
          convertedList[i] = converted;
        }
        runner.batch(conn, sql, convertedList);
        conn.commit();
      }
    } catch (SQLException e) {
      throw new SQLRuntimeException(e);
    } finally {
      DbUtils.closeQuietly(conn);
    }
  }
Beispiel #28
0
 /**
  * 批处理
  *
  * @throws Exception
  */
 @Test
 public void testBatch() throws Exception {
   String sql = "insert into admin (userName,pwd) values(?,?)";
   conn = DbUtil.getConnection();
   QueryRunner qr = new QueryRunner();
   // 批量插入
   qr.batch(conn, sql, new Object[][] {{"jack1", "888"}, {"jack2", "999"}});
   // 关闭
   DbUtils.close(conn);
 }
Beispiel #29
0
  public static void main(String[] args) {

    Connection conn = null;
    try {

      String schema = args[0];
      String table = args[1];
      String fullClassName = args[2];

      conn = DbConnection.getConnection();
      DtoCreater fks = new DtoCreater(conn, schema, table);
      fks.execute(schema, table, fullClassName);

    } catch (Exception e) {
      DbUtils.rollbackAndCloseQuietly(conn);
      return;
    } finally {
      DbUtils.closeQuietly(conn);
    }
  }
  private Connection getConnection() throws ScheduleManagerException {
    Connection connection = null;
    try {
      connection = dataSource.getConnection();
    } catch (Exception e) {
      DbUtils.closeQuietly(connection);
      throw new ScheduleManagerException("Error getting DB connection.", e);
    }

    return connection;
  }