/**
   * Method to apply any restrictions to the created ResultSet.
   *
   * @param ps The PreparedStatement
   * @param query The query
   * @param applyTimeout Whether to apply the query timeout (if any) direct to the PreparedStatement
   * @throws SQLException Thrown when an error occurs applying the constraints
   */
  public static void prepareStatementForExecution(
      PreparedStatement ps, Query query, boolean applyTimeout) throws SQLException {
    NucleusContext nucleusCtx = query.getExecutionContext().getNucleusContext();
    RDBMSStoreManager storeMgr = (RDBMSStoreManager) query.getStoreManager();
    PersistenceConfiguration conf = nucleusCtx.getPersistenceConfiguration();

    if (applyTimeout) {
      Integer timeout = query.getDatastoreReadTimeoutMillis();
      if (timeout != null && timeout > 0) {
        ps.setQueryTimeout(timeout / 1000);
      }
    }

    // Apply any fetch size
    int fetchSize = 0;
    if (query.getFetchPlan().getFetchSize() > 0) {
      // FetchPlan has a size set so use that
      fetchSize = query.getFetchPlan().getFetchSize();
    }
    if (storeMgr.getDatastoreAdapter().supportsQueryFetchSize(fetchSize)) {
      ps.setFetchSize(fetchSize);
    }

    // Apply any fetch direction
    String fetchDir =
        conf.getStringProperty(RDBMSPropertyNames.PROPERTY_RDBMS_QUERY_FETCH_DIRECTION);
    Object fetchDirExt =
        query.getExtension(RDBMSPropertyNames.PROPERTY_RDBMS_QUERY_FETCH_DIRECTION);
    if (fetchDirExt != null) {
      fetchDir = (String) fetchDirExt;
      if (!fetchDir.equals("forward")
          && !fetchDir.equals("reverse")
          && !fetchDir.equals("unknown")) {
        throw new NucleusUserException(LOCALISER.msg("052512"));
      }
    }

    if (fetchDir.equals("reverse")) {
      ps.setFetchDirection(ResultSet.FETCH_REVERSE);
    } else if (fetchDir.equals("unknown")) {
      ps.setFetchDirection(ResultSet.FETCH_UNKNOWN);
    }

    // Add a limit on the number of rows to include the maximum we may need
    long toExclNo = query.getRangeToExcl();
    if (toExclNo != 0 && toExclNo != Long.MAX_VALUE) {
      if (toExclNo > Integer.MAX_VALUE) {
        // setMaxRows takes an int as input so limit to the correct range
        ps.setMaxRows(Integer.MAX_VALUE);
      } else {
        ps.setMaxRows((int) toExclNo);
      }
    }
  }
  /**
   * Load the cache entry for the given URI from the database.
   *
   * @param uri the URI of the cached resource for which to return the cache entry
   * @return an instance of KiWiCacheEntry representing the caching metadata for the given resource,
   *     or null in case there is no entry for this resource
   * @throws SQLException
   */
  public KiWiCacheEntry getCacheEntry(String uri) throws SQLException {

    KiWiCacheEntry cached = entryResourceCache.get(uri);

    // lookup element in cache first, so we can avoid reconstructing it if it is already there
    if (cached != null) {
      return cached;
    }

    PreparedStatement query = connection.getPreparedStatement("load.entry_by_uri");
    query.setString(1, uri);
    query.setMaxRows(1);

    // run the database query and if it yields a result, construct a new node; the method call will
    // take care of
    // caching the constructed node for future calls
    ResultSet result = query.executeQuery();
    try {
      if (result.next()) {
        return constructCacheEntry(result);
      } else {
        return null;
      }
    } finally {
      result.close();
    }
  }
Exemple #3
0
  public void addListener(DistributionRequest listener) {
    synchronized (listeners) {
      if (!listeners.contains(listener)) {
        logger.warn("Adding a listener to Distributer:" + listener.toString());
        boolean needsAnd =
            SQLValidator.removeSingleQuotes(SQLValidator.removeQuotes(listener.getQuery()))
                    .indexOf(" where ")
                > 0;
        String query = SQLValidator.addPkField(listener.getQuery());
        if (needsAnd) query += " AND ";
        else query += " WHERE ";
        query += " timed > " + listener.getStartTime() + " and pk > ? order by timed asc ";
        PreparedStatement prepareStatement = null;
        try {
          prepareStatement =
              getPersistantConnection(listener.getVSensorConfig())
                  .prepareStatement(
                      query); // prepareStatement =
                              // StorageManager.getInstance().getConnection().prepareStatement(query);
          prepareStatement.setMaxRows(1000); // Limit the number of rows loaded in memory.
        } catch (Exception e) {
          throw new RuntimeException(e);
        }
        preparedStatements.put(listener, prepareStatement);
        listeners.add(listener);
        addListenerToCandidates(listener);

      } else {
        logger.warn(
            "Adding a listener to Distributer failed, duplicated listener! " + listener.toString());
      }
    }
  }
  public void setMaxRows(int max) throws SQLException {
    Profiler profiler = _profilerPoint.start();

    try {
      _preparedStatement.setMaxRows(max);
    } finally {
      profiler.finish();
    }
  }
Exemple #5
0
 @Test
 public void setmaxrows() throws SQLException {
   PreparedStatement prep = conn.prepareStatement("select 1 union select 2;");
   prep.setMaxRows(1);
   ResultSet rs = prep.executeQuery();
   assertTrue(rs.next());
   assertEquals(1, rs.getInt(1));
   assertFalse(rs.next());
   prep.close();
 }
 private void testMaxRowsChange(Connection conn) throws SQLException {
   PreparedStatement prep = conn.prepareStatement("SELECT * FROM SYSTEM_RANGE(1, 100)");
   ResultSet rs;
   for (int j = 1; j < 20; j++) {
     prep.setMaxRows(j);
     rs = prep.executeQuery();
     for (int i = 0; i < j; i++) {
       assertTrue(rs.next());
     }
     assertFalse(rs.next());
   }
 }
  public Authentificator(
      String DBMSHost,
      int DBMSPort,
      String DBMSUser,
      String DBMSPassword,
      String DBName,
      String tableName)
      throws SQLException {
    String connectionURL = String.format(CONNECTION_URL_TEMPLATE, DBMSHost, DBMSPort, DBName);
    DBConnection = DriverManager.getConnection(connectionURL, DBMSUser, DBMSPassword);

    userGetStatement = DBConnection.prepareStatement(String.format(USER_GET_SQL, tableName));
    userGetStatement.setMaxRows(1);
  }
  private boolean isValid(final Connection connection) {
    if (connection == null) {
      return false;
    }

    if (status != PwmService.STATUS.OPEN) {
      return false;
    }

    try {
      final Method getFreeSpaceMethod = File.class.getMethod("isValid");
      final Object rawResult = getFreeSpaceMethod.invoke(connection, 10);
      return (Boolean) rawResult;
    } catch (NoSuchMethodException e) {
      /* no error, pre java 1.6 doesn't have this method */
    } catch (Exception e) {
      LOGGER.debug(
          "error checking for isValid for " + connection.toString() + ",: " + e.getMessage());
    }

    final StringBuilder sb = new StringBuilder();
    sb.append("SELECT * FROM ")
        .append(DatabaseTable.PWM_META.toString())
        .append(" WHERE " + KEY_COLUMN + " = ?");
    PreparedStatement statement = null;
    ResultSet resultSet = null;
    try {
      statement = connection.prepareStatement(sb.toString());
      statement.setString(1, KEY_ENGINE_START_PREFIX + instanceID);
      statement.setMaxRows(1);
      resultSet = statement.executeQuery();
      if (resultSet.next()) {
        resultSet.getString(VALUE_COLUMN);
      }
    } catch (SQLException e) {
      final ErrorInformation errorInformation =
          new ErrorInformation(
              PwmError.ERROR_DB_UNAVAILABLE, "isValid operation failed: " + e.getMessage());
      lastError = errorInformation;
      LOGGER.error(errorInformation.toDebugStr());
      return false;
    } finally {
      close(statement);
      close(resultSet);
    }
    return true;
  }
Exemple #9
0
 public CourseInstance getCourseInstance(int instanceId) {
   CourseInstance courseInstance = null;
   PreparedStatement selectStatement;
   try {
     String query =
         "SELECT * FROM COURSE, COURSEINSTANCE WHERE INSTANCEID = ? AND COURSE.COURSEID = COURSEINSTANCE.COURSEID";
     selectStatement = ConnectionUtil.getOracleConnection().prepareStatement(query);
     selectStatement.setMaxRows(1);
     selectStatement.setInt(1, instanceId);
     ResultSet rs = selectStatement.executeQuery();
     List<CourseInstance> instances = resultSetToList(rs);
     if (instances.size() > 0) courseInstance = instances.stream().findFirst().get();
   } catch (SQLException e) {
     e.printStackTrace();
   }
   return courseInstance;
 }
Exemple #10
0
  public List<Agent> queryAllAgents(Page page) {
    List<Agent> agent_list = null;

    try {
      conn = JDBC_Conn.getConnection();
      String sql = "select * from agents natural join position";
      stmt =
          conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
      stmt.setMaxRows(page.getEndIndex());
      rs = stmt.executeQuery();
      if (page.getBeginIndex() > 0) {
        rs.absolute(page.getBeginIndex());
      }

      while (rs.next()) {
        if (agent_list == null) agent_list = new ArrayList<Agent>();

        Agent agent = new Agent();

        agent.setAgent_id(rs.getString("agent_id"));
        agent.setPhone_number(rs.getString("phone_number"));
        agent.setEmail(rs.getString("email"));
        agent.setFirst_nm(CommUtils.initCap(rs.getString("first_nm")));
        agent.setLast_nm(CommUtils.initCap(rs.getString("last_nm")));

        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
        Date dob = sdf.parse(rs.getString("dob"));

        agent.setDob(dob);
        agent.setZip(rs.getString("zip"));
        agent.setGender(rs.getString("gender"));

        agent.setPosition(rs.getString("position"));

        agent_list.add(agent);
      }

    } catch (Exception e) {
      e.printStackTrace();
      throw new DAOException();
    } finally {
      JDBC_Conn.releaseConnection(conn, stmt, rs);
    }
    return agent_list;
  }
  @Override
  public String get(final DatabaseTable table, final String key) throws DatabaseException {
    if (traceLogging) {
      LOGGER.trace("attempting get operation for table=" + table + ", key=" + key);
    }
    preOperationCheck();
    final StringBuilder sb = new StringBuilder();
    sb.append("SELECT * FROM ").append(table.toString()).append(" WHERE " + KEY_COLUMN + " = ?");

    PreparedStatement statement = null;
    ResultSet resultSet = null;
    String returnValue = null;
    try {
      statement = connection.prepareStatement(sb.toString());
      statement.setString(1, key);
      statement.setMaxRows(1);
      resultSet = statement.executeQuery();

      if (resultSet.next()) {
        returnValue = resultSet.getString(VALUE_COLUMN);
      }
    } catch (SQLException e) {
      final ErrorInformation errorInformation =
          new ErrorInformation(
              PwmError.ERROR_DB_UNAVAILABLE, "get operation failed: " + e.getMessage());
      lastError = errorInformation;
      throw new DatabaseException(errorInformation);
    } finally {
      close(statement);
      close(resultSet);
    }

    if (traceLogging) {
      final LinkedHashMap<String, Object> debugOutput = new LinkedHashMap<>();
      debugOutput.put("table", table);
      debugOutput.put("key", key);
      debugOutput.put("result", returnValue);
      LOGGER.trace(
          "get operation result: " + JsonUtil.serializeMap(debugOutput, JsonUtil.Flag.PrettyPrint));
    }

    updateStats(true, false);
    return returnValue;
  }
  /** Returns all rows from the producto table that match the specified arbitrary SQL statement */
  public Producto[] findByDynamicSelect(String sql, Object[] sqlParams)
      throws ProductoDaoException {
    // declare variables
    final boolean isConnSupplied = (userConn != null);
    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;

    try {
      // get the user-specified connection or get a connection from the ResourceManager
      conn = isConnSupplied ? userConn : ResourceManager.getConnection();

      // construct the SQL statement
      final String SQL = sql;

      if (logger.isDebugEnabled()) {
        logger.debug("Executing " + SQL);
      }

      // prepare statement
      stmt = conn.prepareStatement(SQL);
      stmt.setMaxRows(maxRows);

      // bind parameters
      for (int i = 0; sqlParams != null && i < sqlParams.length; i++) {
        stmt.setObject(i + 1, sqlParams[i]);
      }

      rs = stmt.executeQuery();

      // fetch the results
      return fetchMultiResults(rs);
    } catch (Exception _e) {
      logger.error("Exception: " + _e.getMessage(), _e);
      throw new ProductoDaoException("Exception: " + _e.getMessage(), _e);
    } finally {
      ResourceManager.close(rs);
      ResourceManager.close(stmt);
      if (!isConnSupplied) {
        ResourceManager.close(conn);
      }
    }
  }
Exemple #13
0
 public Course getCourseByCode(String code) {
   Course course = null;
   PreparedStatement selectStatement;
   try {
     String query = "SELECT * FROM COURSE WHERE COURSECODE = ?";
     selectStatement = ConnectionUtil.getOracleConnection().prepareStatement(query);
     selectStatement.setMaxRows(1);
     selectStatement.setString(1, code);
     ResultSet rs = selectStatement.executeQuery();
     while (rs.next())
       course =
           CourseBuilder.createCourseBuilder()
               .setId(rs.getInt("COURSEID"))
               .setCode(rs.getString("COURSECODE"))
               .setTitle(rs.getString("TITLE"))
               .buildCourse();
   } catch (SQLException e) {
     e.printStackTrace();
   }
   return course;
 }
  /**
   * Returns all rows from the carrito_compras table that match the specified arbitrary SQL
   * statement
   */
  public CarritoCompras[] findByDynamicWhere(String sql, Object[] sqlParams)
      throws CarritoComprasDaoException {
    // declare variables
    final boolean isConnSupplied = (userConn != null);
    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;

    try {
      // get the user-specified connection or get a connection from the ResourceManager
      conn = isConnSupplied ? userConn : ResourceManager.getConnection();

      // construct the SQL statement
      final String SQL = SQL_SELECT + " WHERE " + sql;

      System.out.println("Executing " + SQL);
      // prepare statement
      stmt = conn.prepareStatement(SQL);
      stmt.setMaxRows(maxRows);

      // bind parameters
      for (int i = 0; sqlParams != null && i < sqlParams.length; i++) {
        stmt.setObject(i + 1, sqlParams[i]);
      }

      rs = stmt.executeQuery();

      // fetch the results
      return fetchMultiResults(rs);
    } catch (Exception _e) {
      _e.printStackTrace();
      throw new CarritoComprasDaoException("Exception: " + _e.getMessage(), _e);
    } finally {
      ResourceManager.close(rs);
      ResourceManager.close(stmt);
      if (!isConnSupplied) {
        ResourceManager.close(conn);
      }
    }
  }
 /**
  * 初始化真正的PreparedStatement,对当前对象的操作全部都设置到真正的PreparedStatement
  *
  * @throws SQLException
  */
 private void prepare() throws SQLException {
   DALFactory dalFactory = DALFactory.getDefault();
   List<Object> values = dalParameters.getValues();
   Map<String, Object> context = new HashMap<String, Object>();
   SQLStruct sqlStruct = dalFactory.getSqlAnalyzer().parse(sql, context);
   SQLInfo sqlInfo = null;
   if (sqlStruct.isCanParse()) {
     sqlInfo =
         dalFactory
             .getSqlAnalyzer()
             .analyse(sql, sqlStruct, values.toArray(new Object[values.size()]), context);
   }
   this.parsePartition(sqlStruct, sqlInfo);
   this.initRealPreparedStatement();
   if (this.maxFieldSize != 0) {
     ps.setMaxFieldSize(maxFieldSize);
   }
   if (this.maxRows != 0) {
     ps.setMaxRows(maxRows);
   }
   if (!this.escapeProcessing) {
     ps.setEscapeProcessing(escapeProcessing);
   }
   if (this.queryTimeout != 0) {
     ps.setQueryTimeout(queryTimeout);
   }
   if (this.cursorName != null) {
     ps.setCursorName(cursorName);
   }
   if (this.fetchDirection != 0) {
     ps.setFetchDirection(fetchDirection);
   }
   if (this.fetchSize != 0) {
     ps.setFetchSize(fetchSize);
   }
   if (!this.poolable) {
     ps.setPoolable(poolable);
   }
   this.dalParameters.initRealPreparedStatement(ps);
 }
 private void checkStatementExecute(Connection connection, boolean prepare, int maxRowCount)
     throws SQLException {
   final String sql =
       "select * from (\n" + "  values (1, 'a'), (null, 'b'), (3, 'c')) as t (c1, c2)";
   final Statement statement;
   final ResultSet resultSet;
   final ParameterMetaData parameterMetaData;
   if (prepare) {
     final PreparedStatement ps = connection.prepareStatement(sql);
     statement = ps;
     ps.setMaxRows(maxRowCount);
     parameterMetaData = ps.getParameterMetaData();
     assertTrue(ps.execute());
     resultSet = ps.getResultSet();
   } else {
     statement = connection.createStatement();
     statement.setMaxRows(maxRowCount);
     parameterMetaData = null;
     assertTrue(statement.execute(sql));
     resultSet = statement.getResultSet();
   }
   if (parameterMetaData != null) {
     assertThat(parameterMetaData.getParameterCount(), equalTo(0));
   }
   final ResultSetMetaData metaData = resultSet.getMetaData();
   assertEquals(2, metaData.getColumnCount());
   assertEquals("C1", metaData.getColumnName(1));
   assertEquals("C2", metaData.getColumnName(2));
   for (int i = 0; i < maxRowCount || (maxRowCount == 0 && i < 3); i++) {
     assertTrue(resultSet.next());
   }
   assertFalse(resultSet.next());
   resultSet.close();
   statement.close();
   connection.close();
 }
  private List<String> getCategoryNameStartWith(
      Connection pConn, int pStoreId, String pName, int pMaxRows) throws Exception {

    List<String> list = new ArrayList<String>();

    String reqName = pName.toUpperCase().replaceAll("'", "''");
    String sql =
        "SELECT DISTINCT JD_CATEGORY1 FROM DW_CATEGORY_DIM WHERE STORE_DIM_ID = ? AND UPPER(JD_CATEGORY1) LIKE '"
            + reqName
            + "%' ORDER BY JD_CATEGORY1";

    PreparedStatement pstmt = pConn.prepareStatement(sql);

    pstmt.setInt(1, pStoreId);

    pstmt.setMaxRows(pMaxRows);

    ResultSet rs = pstmt.executeQuery();
    while (rs.next()) {
      list.add(rs.getString(1));
    }

    return list;
  }
Exemple #18
0
  @SuppressWarnings("unchecked")
  public List<Job> dequeueImmediate(String nodeId, long maxtime, int maxjobs)
      throws DatabaseException {
    ArrayList<Job> ret = new ArrayList<Job>(maxjobs);
    Connection con = null;
    PreparedStatement ps = null;
    try {
      con = getConnection();
      ps = con.prepareStatement(SCHEDULE_IMMEDIATE);
      ps.setString(1, nodeId);
      ps.setLong(2, maxtime);
      ps.setMaxRows(maxjobs);

      ResultSet rs = ps.executeQuery();
      while (rs.next()) {
        Scheduler.JobDetails details = new Scheduler.JobDetails();
        details.instanceId = asLong(rs.getObject("instanceId"));
        details.mexId = (String) rs.getObject("mexId");
        details.processId = (String) rs.getObject("processId");
        details.type = (String) rs.getObject("type");
        details.channel = (String) rs.getObject("channel");
        details.correlatorId = (String) rs.getObject("correlatorId");
        details.correlationKeySet = (String) rs.getObject("correlationKeySet");
        details.retryCount = asInteger(rs.getObject("retryCount"));
        details.inMem = asBoolean(rs.getInt("inMem"));
        if (rs.getObject("detailsExt") != null) {
          try {
            ObjectInputStream is = new ObjectInputStream(rs.getBinaryStream("detailsExt"));
            details.detailsExt = (Map<String, Object>) is.readObject();
            is.close();
          } catch (Exception e) {
            throw new DatabaseException("Error deserializing job detailsExt", e);
          }
        }

        {
          // For compatibility reasons, we check whether there are entries inside
          // jobDetailsExt blob, which correspond to extracted entries. If so, we
          // use them.

          Map<String, Object> detailsExt = details.getDetailsExt();
          if (detailsExt.get("type") != null) {
            details.type = (String) detailsExt.get("type");
          }
          if (detailsExt.get("iid") != null) {
            details.instanceId = (Long) detailsExt.get("iid");
          }
          if (detailsExt.get("pid") != null && detailsExt.get("pid") instanceof String) {
            details.processId = (String) detailsExt.get("pid");
          }
          if (detailsExt.get("inmem") != null) {
            details.inMem = (Boolean) detailsExt.get("inmem");
          }
          if (detailsExt.get("ckey") != null) {
            details.correlationKeySet = (String) detailsExt.get("ckey");
          }
          if (detailsExt.get("channel") != null) {
            details.channel = (String) detailsExt.get("channel");
          }
          if (detailsExt.get("mexid") != null) {
            details.mexId = (String) detailsExt.get("mexid");
          }
          if (detailsExt.get("correlatorId") != null) {
            details.correlatorId = (String) detailsExt.get("correlatorId");
          }
          if (detailsExt.get("retryCount") != null) {
            details.retryCount = Integer.parseInt((String) detailsExt.get("retryCount"));
          }
        }

        Job job =
            new Job(
                rs.getLong("ts"),
                rs.getString("jobid"),
                asBoolean(rs.getInt("transacted")),
                details);
        ret.add(job);
      }
      rs.close();
      ps.close();
    } catch (SQLException se) {
      throw new DatabaseException(se);
    } finally {
      close(ps);
      close(con);
    }
    return ret;
  }
 @Override
 public void setMaxRows(int max) throws SQLException {
   stmt.setMaxRows(max);
 }
  /**
   * Executes the query request.
   *
   * @throws SQLException if a database exception occurs
   * @throws IOException
   * @throws SAXException
   * @throws ParserConfigurationException
   */
  public void execute()
      throws SQLException, IdentityException, NamingException, ParserConfigurationException,
          SAXException, IOException {

    // intitalize
    PreparedStatement st = null;
    PreparedStatement stCount = null;
    MmdQueryCriteria criteria = getQueryCriteria();
    MmdRecords records = getQueryResult().getRecords();
    PageCursor pageCursor = getQueryResult().getPageCursor();
    criteria.getDateRange().check();
    pageCursor.setTotalRecordCount(0);

    adminDao = new ImsMetadataAdminDao(getRequestContext());
    tblImsUser = getRequestContext().getCatalogConfiguration().getUserTableName();
    Users editablePublishers = Publisher.buildSelectablePublishers(getRequestContext(), false);
    for (User u : editablePublishers.values()) {
      if (u.getName().length() > 0) {
        hmEditablePublishers.put(u.getName().toLowerCase(), u.getKey());
      }
    }
    User tmpUser = new User();
    tmpUser.setDistinguishedName("*");
    getRequestContext().newIdentityAdapter().readUserGroups(tmpUser);
    allGroups = tmpUser.getGroups();

    isGptAdministrator = new RoleMap(getRequestContext().getUser()).get("gptAdministrator");

    // determine if we are in ArcIMS metadata server proxy mode

    try {

      // establish the connection
      ManagedConnection mc = returnConnection();
      Connection con = mc.getJdbcConnection();

      // start the SQL expression
      StringBuilder sbSql = new StringBuilder();
      StringBuilder sbCount = new StringBuilder();
      StringBuilder sbFrom = new StringBuilder();
      StringBuilder sbWhere = new StringBuilder();
      sbSql.append("SELECT A.TITLE,A.DOCUUID,A.SITEUUID,C.USERNAME");
      sbSql.append(",A.APPROVALSTATUS,A.PUBMETHOD,A.UPDATEDATE,A.ACL");
      sbSql.append(",A.ID,A.HOST_URL,A.FREQUENCY,A.SEND_NOTIFICATION,A.PROTOCOL");
      sbSql.append(",A.FINDABLE,A.SEARCHABLE,A.SYNCHRONIZABLE");
      sbCount.append("SELECT COUNT(*)");

      // append from clause
      sbFrom.append(" FROM ").append(tblImsUser).append(" C");
      sbFrom.append(",").append(getResourceTableName()).append(" A");
      sbSql.append(sbFrom);
      sbCount.append(sbFrom);

      // build the where clause
      if (sbWhere.length() > 0) {
        sbWhere.append(" AND");
      }
      sbWhere.append(" (A.OWNER = C.USERID)");

      Map<String, Object> args = criteria.appendWherePhrase("A", sbWhere, getPublisher());

      // append the where clause expressions
      if (sbWhere.length() > 0) {
        sbSql.append(" WHERE ").append(sbWhere.toString());
        sbCount.append(" WHERE ").append(sbWhere.toString());
      }

      // append the order by clause
      String sSortColumn = criteria.getSortOption().getColumnKey();
      String sSortDir = criteria.getSortOption().getDirection().toString();
      if (sSortColumn.equalsIgnoreCase("title")) {
        sSortColumn = "UPPER(A.TITLE)";
      } else if (sSortColumn.equalsIgnoreCase("uuid")) {
        sSortColumn = "A.DOCUUID";
      } else if (sSortColumn.equalsIgnoreCase("owner")) {
        sSortColumn = "UPPER(C.USERNAME)";
      } else if (sSortColumn.equalsIgnoreCase("status")) {
        sSortColumn = "A.APPROVALSTATUS";
      } else if (sSortColumn.equalsIgnoreCase("method")) {
        sSortColumn = "A.PUBMETHOD";
      } else if (sSortColumn.equalsIgnoreCase("acl")) {
        sSortColumn = "A.ACL";
      } else if (sSortColumn.equalsIgnoreCase("updatedate")) {
        sSortColumn = "A.UPDATEDATE";
      } else {
        sSortColumn = "A.UPDATEDATE";
        sSortDir = "DESC";
        criteria.getSortOption().setColumnKey("updatedate");
        criteria.getSortOption().setDirection("desc");
      }
      sbSql.append(" ORDER BY ");
      sbSql.append(sSortColumn).append(" ").append(sSortDir.toUpperCase());
      if (!sSortColumn.equalsIgnoreCase("A.UPDATEDATE")) {
        sbSql.append(", A.UPDATEDATE DESC");
      }

      // prepare the statements
      st = con.prepareStatement(sbSql.toString());
      stCount = con.prepareStatement(sbCount.toString());

      int n = 1;
      criteria.applyArgs(st, n, args);
      criteria.applyArgs(stCount, n, args);

      // query the count
      logExpression(sbCount.toString());
      ResultSet rsCount = stCount.executeQuery();
      if (rsCount.next()) {
        pageCursor.setTotalRecordCount(rsCount.getInt(1));
      }
      stCount.close();
      stCount = null;

      // query records if a count was found
      pageCursor.checkCurrentPage();
      if (pageCursor.getTotalRecordCount() > 0) {

        // set the start record and the number of records to retrieve
        int nCurPage = pageCursor.getCurrentPage();
        int nRecsPerPage = getQueryResult().getPageCursor().getRecordsPerPage();
        int nStartRecord = ((nCurPage - 1) * nRecsPerPage) + 1;
        int nMaxRecsToRetrieve = nCurPage * nRecsPerPage;
        st.setMaxRows(nMaxRecsToRetrieve);

        // determine publisher names associated with editable records

        // execute the query
        logExpression(sbSql.toString());
        ResultSet rs = st.executeQuery();

        // build the record set
        int nCounter = 0;

        while (rs.next()) {
          n = 1;
          nCounter++;
          if (nCounter >= nStartRecord) {
            MmdRecord record = new MmdRecord();
            records.add(record);

            readRecord(rs, record);

            // break if we hit the max value for the cursor
            if (records.size() >= nRecsPerPage) {
              break;
            }
          }
        }

        TreeMap<String, MmdRecord> recordsMap =
            new TreeMap<String, MmdRecord>(String.CASE_INSENSITIVE_ORDER);
        StringBuilder keys = new StringBuilder();

        for (MmdRecord r : records) {
          if (r.getProtocol() == null) continue;
          recordsMap.put(r.getUuid(), r);
          if (keys.length() > 0) {
            keys.append(",");
          }
          keys.append("'").append(r.getUuid().toUpperCase()).append("'");
        }

        readJobStatus(con, recordsMap, keys.toString());
        readLastHarvestDate(con, recordsMap, keys.toString());
      }

    } finally {
      closeStatement(st);
      closeStatement(stCount);
    }
  }
  public synchronized void execute() {
    NetLogger netLog = NetLogger.getTlogger();
    netlogger.info(netLog.start("maddash.CheckSchedulerJob.execute"));
    int schedJobCount = 0;
    int totalJobCount = 0;

    // query database
    Connection conn = null;
    try {
      /* garbage collect before hitting database. memory spikes occur
       * here as we pull in new check data so this maximizes memory
       * available prior to spike and should help prevent heap errors */
      System.gc();

      MaDDashGlobals globals = MaDDashGlobals.getInstance();
      conn = globals.getDataSource().getConnection();
      long time = System.currentTimeMillis() / 1000;
      netlogger.debug(netLog.start("maddash.CheckSchedulerJob.execute.queryDb"));
      PreparedStatement selStmt =
          conn.prepareStatement(
              "SELECT c.id, c.gridName, "
                  + "c.rowName, c.colName, t.checkType, t.checkParams, t.checkInterval, "
                  + "t.retryInterval, t.retryAttempts, t.timeout, c.statusMessage FROM checkTemplates AS t, "
                  + "checks AS c WHERE c.active = 1 AND t.id = c.checkTemplateId AND "
                  + "c.nextCheckTime <= ? AND c.checkStatus != ? ORDER BY c.nextCheckTime ASC FETCH FIRST ? ROWS ONLY");
      selStmt.setLong(1, time);
      selStmt.setInt(2, CheckConstants.RESULT_MAINTENANCE);
      selStmt.setInt(3, globals.getJobBatchSize());
      selStmt.setMaxRows(globals.getJobBatchSize());
      ResultSet checksToRun = selStmt.executeQuery();
      netlogger.debug(netLog.end("maddash.CheckSchedulerJob.execute.queryDb"));

      while (checksToRun.next()) {
        totalJobCount++;
        if (globals.isCheckScheduled(checksToRun.getInt(1))) {
          continue;
        }
        String jobKey = UUID.randomUUID().toString();
        String triggerName = "runCheckTrigger-" + jobKey;
        String jobName = "runCheckJob-" + jobKey;
        SimpleTrigger trigger = new SimpleTrigger(triggerName, null, new Date(), null, 0, 0L);
        JobDetail jobDetail = new JobDetail(jobName, "RUN_CHECKS", RunCheckJob.class);
        JobDataMap dataMap = new JobDataMap();
        dataMap.put("checkId", checksToRun.getInt(1));
        dataMap.put("gridName", checksToRun.getString(2));
        dataMap.put("rowName", checksToRun.getString(3));
        dataMap.put("colName", checksToRun.getString(4));
        dataMap.put("checkType", globals.getCheckTypeClassMap().get(checksToRun.getString(5)));
        dataMap.put("checkParams", checksToRun.getString(6));
        dataMap.put("checkInterval", checksToRun.getInt(7));
        dataMap.put("retryInterval", checksToRun.getInt(8));
        dataMap.put("retryAttempts", checksToRun.getInt(9));
        dataMap.put("timeout", checksToRun.getInt(10));
        dataMap.put("statusMessage", checksToRun.getString(11));
        dataMap.put("rowVars", DimensionUtil.getParams(checksToRun.getString(3), conn));
        dataMap.put("colVars", DimensionUtil.getParams(checksToRun.getString(4), conn));
        jobDetail.setJobDataMap(dataMap);
        globals.updateScheduledChecks(checksToRun.getInt(1), true);
        globals.getScheduler().scheduleJob(jobDetail, trigger);
        schedJobCount++;
      }
      conn.close();
      netlogger.info(netLog.end("maddash.CheckSchedulerJob.execute"));
      log.debug("Scheduled " + schedJobCount + "/" + totalJobCount + " new jobs");
    } catch (Exception e) {
      if (conn != null) {
        try {
          conn.close();
        } catch (SQLException e2) {
        }
      }
      netlogger.info(netLog.error("maddash.CheckSchedulerJob.execute", e.getMessage()));
      log.error("Error scheduling job " + e.getMessage());
      e.printStackTrace();
      throw new RuntimeException(e.getMessage());
    }
  }
Exemple #22
0
 protected void setMaxRowsToFetch(PreparedStatement statement, int maxRowsToFetch)
     throws SQLException {
   statement.setMaxRows(maxRowsToFetch);
 }
 public void setMaxRows(int max) throws SQLException {
   this.maxRows = max;
   if (ps != null) {
     ps.setMaxRows(max);
   }
 }
  public ArrayList<Album> getLastAlbums(Integer nbAlbum) throws ListAlbumException {
    ArrayList<Album> arrayAlbum = new ArrayList<Album>();

    try {
      Context ctx = new InitialContext();
      DataSource source = (DataSource) ctx.lookup("jdbc/MusicStore");
      connexion = source.getConnection();

      String requeteSQL =
          "SELECT Album.idAlbum, Album.titre, Album.image, Artiste.nom, Album.Prix, "
              + " CASE WHEN Promotion_Artiste.idArtiste = Artiste.idArtiste "
              + "     THEN true "
              + "     ELSE false "
              + " END, "
              + " CASE WHEN Promotion_Artiste.idArtiste = Artiste.idArtiste "
              + "     THEN (Album.Prix - (Album.Prix * Promotion.prcremise * 0.01))"
              + " END"
              + " FROM Album, Artiste_Album, Artiste, Promotion, Promotion_Artiste "
              + " WHERE Artiste_Album.idAlbum = Album.idAlbum AND Artiste_Album.idArtiste = Artiste.idArtiste "
              + " AND Promotion_Artiste.idPromotion = Promotion.idPromotion AND Promotion.datedeb <= current_date AND Promotion.datefin >= current_date"
              + " ORDER BY album.idalbum DESC";

      PreparedStatement prepStat = connexion.prepareStatement(requeteSQL);
      if (nbAlbum != 0) {
        prepStat.setMaxRows(nbAlbum);
      }

      ResultSet donnees = prepStat.executeQuery();
      while (donnees.next()) {

        Album album = new Album();
        album.setIdAlbum(donnees.getInt(1));
        album.setTitre(donnees.getString(2));
        album.setImage(donnees.getString(3));
        album.setArtiste(donnees.getString(4));
        album.setPrix(donnees.getDouble(5));
        album.setPromo(donnees.getBoolean(6));
        album.setPrixPromo(donnees.getDouble(7));

        arrayAlbum.add(album);
      }

      if (arrayAlbum.isEmpty() == true) // Envoi erreur si aucune album
      {
        throw new ListAlbumException("emptyListAlbum");
      }

    } catch (SQLException e) {
      throw new ListAlbumException("sqlException");
    } catch (NamingException e) {
      throw new ListAlbumException("errorNaming");
    } finally {
      try {
        connexion.close();
      } catch (SQLException e) {
        throw new ListAlbumException("sqlConnexionError");
      }
    }

    return arrayAlbum;
  }
Exemple #25
0
  public List<Agent> queryAgentByCondition(Agent agent, Page page) {

    List<Agent> agent_list = null;
    int count = 0;

    try {
      conn = JDBC_Conn.getConnection();
      String sql = "select * from agents natural join position where ";

      if (agent.getFirst_nm() != null && !"".equals(agent.getFirst_nm())) {
        sql += " first_nm='" + agent.getFirst_nm() + "'" + " and ";
        count++;
      }

      if (agent.getLast_nm() != null && !"".equals(agent.getLast_nm())) {
        sql += " last_nm='" + agent.getLast_nm() + "'" + " and ";
        count++;
      }

      if (agent.getGender() != null && !"".equals(agent.getGender())) {
        sql += " gender='" + agent.getGender() + "'" + " and ";
        count++;
      }

      if (agent.getZip() != null && !"".equals(agent.getZip())) {
        sql += " zip='" + agent.getZip() + "'" + " and ";
        count++;
      }

      if (agent.getEmail() != null && !"".equals(agent.getEmail())) {
        sql += " email='" + agent.getEmail() + "'" + " and ";
        count++;
      }

      if (agent.getPhone_number() != null && !"".equals(agent.getPhone_number())) {
        sql += " phone_number='" + agent.getPhone_number() + "'" + " and ";
        count++;
      }

      if (agent.getPosition() != null && !"".equals(agent.getPosition())) {
        sql += " position='" + agent.getPosition() + "'" + " and ";
        count++;
      }

      if (agent.getDob() != null) {

        SimpleDateFormat sdf = new SimpleDateFormat("MM/dd/yyyy");
        String dob_str = sdf.format(agent.getDob());
        sql += " dob= " + "to_date('" + dob_str + "'," + "'MM/dd/yyyy')" + " and ";
        count++;
      }

      if (count == 0) return queryAllAgents(page);

      int last_index = sql.lastIndexOf("and");
      sql = sql.substring(0, last_index);

      stmt =
          conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
      stmt.setMaxRows(page.getEndIndex());
      rs = stmt.executeQuery();
      if (page.getBeginIndex() > 0) {
        rs.absolute(page.getBeginIndex());
      }

      while (rs.next()) {
        if (agent_list == null) agent_list = new ArrayList<Agent>();

        Agent agent1 = new Agent();

        agent1.setAgent_id(rs.getString("agent_id"));
        agent1.setPhone_number(rs.getString("phone_number"));
        agent1.setEmail(rs.getString("email"));
        agent1.setFirst_nm(CommUtils.initCap(rs.getString("first_nm")));
        agent1.setLast_nm(CommUtils.initCap(rs.getString("last_nm")));

        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
        Date dob = sdf.parse(rs.getString("dob"));

        agent1.setDob(dob);
        agent1.setZip(rs.getString("zip"));
        agent1.setGender(rs.getString("gender"));
        agent1.setPosition(rs.getString("position"));

        agent_list.add(agent1);
      }

    } catch (Exception e) {
      e.printStackTrace();
      throw new DAOException();
    } finally {
      JDBC_Conn.releaseConnection(conn, stmt, rs);
    }
    return agent_list;
  }
 public void setMaxRows(int max) throws SQLException {
   preparedStatement.setMaxRows(max);
 }
 public void setMaxRows(int max) throws SQLException {
   delegate.setMaxRows(max);
 }