Example usage for org.apache.commons.dbutils QueryRunner query

Introduction

In this page you can find the example usage for org.apache.commons.dbutils QueryRunner query.

Prototype

public <T> T query(String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException 

Source Link

Document

Executes the given SELECT SQL query and returns a result object.

Usage

From source file:azkaban.executor.JdbcExecutorLoader.java

private synchronized void uploadExecutableFlow(Connection connection, ExecutableFlow flow, EncodingType encType)
        throws ExecutorManagerException, IOException {
    final String INSERT_EXECUTABLE_FLOW = "INSERT INTO execution_flows "
            + "(project_id, flow_id, version, status, submit_time, submit_user, update_time) "
            + "values (?,?,?,?,?,?,?)";
    QueryRunner runner = new QueryRunner();
    long submitTime = System.currentTimeMillis();

    long id;/*ww w. j  a v  a  2 s .c om*/
    try {
        flow.setStatus(Status.PREPARING);
        runner.update(connection, INSERT_EXECUTABLE_FLOW, flow.getProjectId(), flow.getFlowId(),
                flow.getVersion(), Status.PREPARING.getNumVal(), submitTime, flow.getSubmitUser(), submitTime);
        connection.commit();
        id = runner.query(connection, LastInsertID.LAST_INSERT_ID, new LastInsertID());

        if (id == -1L) {
            throw new ExecutorManagerException("Execution id is not properly created.");
        }
        logger.info("Flow given " + flow.getFlowId() + " given id " + id);
        flow.setExecutionId((int) id);

        updateExecutableFlow(connection, flow, encType);
    } catch (SQLException e) {
        throw new ExecutorManagerException("Error creating execution.", e);
    }
}

---

From source file:nl.b3p.catalog.arcgis.ArcSDE9xJDBCHelper.java

@Override
public void saveMetadata(ArcSDEJDBCDataset dataset, String metadata) throws Exception {
    Connection c = getConnection();
    PreparedStatement ps = null;/*  w  ww .  j a v a 2s  . c o m*/
    try {
        c.setAutoCommit(false);

        // gebruik geen DbUtils; setBinaryStream() werkt niet met setObject()
        // welke DbUtils gebruikt

        String sql = "update " + getTableName(TABLE_USERMETADATA) + " set xml = ? where name = ? and owner = ?";
        sql += databaseNameSQL(dataset);
        ps = c.prepareStatement(sql);
        byte[] xml = metadata.getBytes(ENCODING);
        ps.setBinaryStream(1, new ByteArrayInputStream(xml), xml.length);
        ps.setString(2, dataset.getName());
        ps.setString(3, dataset.getOwner());
        if (dataset.getDatabaseName() != null) {
            ps.setString(4, dataset.getDatabaseName());
        }
        int rowsAffected = ps.executeUpdate();
        ps.close();
        ps = null;

        if (rowsAffected > 1) {
            throw new Exception("Updating metadata should affect maximum one row; got rows affected count of "
                    + rowsAffected);
        }

        if (rowsAffected == 0) {
            // try to insert new row

            QueryRunner runner = new QueryRunner();

            // determine highest id
            Object id = runner.query(c, "select coalesce(max(id)+1,1) from " + getTableName(TABLE_USERMETADATA),
                    new ScalarHandler());

            Integer datasetType = determineDatasetType(c, dataset);

            // weer setBinaryStream nodig
            ps = c.prepareStatement("insert into " + getTableName(TABLE_USERMETADATA)
                    + " (id, databasename, owner, name, datasettype, xml) values(?,?,?,?,?,?)");
            ps.setObject(1, id);
            ps.setObject(2, dataset.getDatabaseName());
            ps.setString(3, dataset.getOwner());
            ps.setString(4, dataset.getName());
            ps.setObject(5, datasetType);
            ps.setBinaryStream(6, new ByteArrayInputStream(xml), xml.length);
            ps.executeUpdate();
            ps.close();
            ps = null;
        }

        DbUtils.commitAndClose(c);
    } catch (Exception e) {
        DbUtils.rollbackAndCloseQuietly(c);
        throw e;
    } finally {
        DbUtils.closeQuietly(ps);
    }
}

---

From source file:nl.opengeogroep.safetymaps.server.stripes.FotoFunctionActionBean.java

public List<Map<String, Object>> getFromDb() throws Exception {
    QueryRunner qr = DB.qr();

    List<Map<String, Object>> rows = qr.query(
            "SELECT \"filename\", \"omschrijving\" from wfs." + TABLE + " where incident_nummer =?",
            new MapListHandler(), incidentNummer);

    return rows;//from w ww.j  a  va  2  s . c om
}

---

From source file:nl.opengeogroep.safetymaps.server.stripes.NLExtractBagAddressSearchActionBean.java

public Resolution search() {
    try {//from  w  w w  .j  a  va2 s. co m
        JSONArray result = new JSONArray();

        if (term != null && term.trim().length() > 2) {
            term = term.trim().toLowerCase();

            QueryRunner qr = DB.bagQr();

            String where;
            String param;

            boolean isPostCode = term.matches("^[0-9]{4}[a-z]{0,2}$");
            if (term.indexOf(' ') != -1 || isPostCode) {
                where = "(textsearchable_adres @@ to_tsquery('dutch',?)) ";
                param = term.replaceAll("\\s+", "&");
            } else {
                where = "openbareruimtenaam like ?";
                param = term.substring(0, 1).toUpperCase() + term.substring(1) + "%";
            }

            List<Map<String, Object>> rows = qr.query(
                    "select openbareruimtenaam || ' ' || "
                            + "CASE WHEN lower(woonplaatsnaam) = lower(gemeentenaam) THEN woonplaatsnaam "
                            + "ELSE woonplaatsnaam || ', ' || gemeentenaam END as display_name, "
                            + "st_x(st_centroid(st_collect(geopunt))) as lon, "
                            + "st_y(st_centroid(st_collect(geopunt))) as lat " + "from bag_actueel.adres where "
                            + where + "group by woonplaatsnaam, gemeentenaam, openbareruimtenaam limit 10",
                    new MapListHandler(), param);

            if (rows.size() == 1) {
                // Only one grouped by result, get more details

                rows = qr.query("select openbareruimtenaam || ' ' || "
                        + "COALESCE(CAST(huisnummer as varchar) || ' ','') || "
                        + "COALESCE(CAST(huisletter as varchar) || ' ','') || "
                        + "COALESCE(CAST(huisnummertoevoeging as varchar) || ' ','') || "
                        + "COALESCE(CAST(postcode as varchar) || ' ','') || "
                        + "CASE WHEN lower(woonplaatsnaam) = lower(gemeentenaam) THEN woonplaatsnaam "
                        + "ELSE woonplaatsnaam || ', ' || gemeentenaam END as display_name, "
                        + "st_x(st_centroid(st_collect(geopunt))) as lon, "
                        + "st_y(st_centroid(st_collect(geopunt))) as lat " + "from bag_actueel.adres where "
                        + where
                        + "group by woonplaatsnaam, gemeentenaam, openbareruimtenaam, huisnummer, huisletter, huisnummertoevoeging, postcode limit 10",
                        new MapListHandler(), param);
            }

            for (Map<String, Object> row : rows) {
                result.put(rowToJson(row, false, false));
            }
        }

        return new StreamingResolution("application/json", new StringReader(result.toString(4)));
    } catch (Exception e) {
        return new ErrorMessageResolution(
                logExceptionAndReturnJSONObject(log, "Error searching BAG database for address", e)
                        .toString(4));

    }
}

---

From source file:org.apache.lens.server.query.LensServerDAO.java

public void getFailedAttempts(final FinishedLensQuery query) {
    if (query != null) {
        String handle = query.getHandle();
        ResultSetHandler<List<FailedAttempt>> rsh = new BeanHandler<List<FailedAttempt>>(null) {
            @Override//w  w  w .ja  v  a2 s.co m
            public List<FailedAttempt> handle(ResultSet rs) throws SQLException {
                List<FailedAttempt> attempts = Lists.newArrayList();
                while (rs.next()) {
                    FailedAttempt attempt = new FailedAttempt(rs.getString(3), rs.getDouble(4), rs.getString(5),
                            rs.getString(6), rs.getLong(7), rs.getLong(8));
                    attempts.add(attempt);
                }
                return attempts;
            }
        };
        String sql = "select * from failed_attempts where handle=? order by attempt_number";
        QueryRunner runner = new QueryRunner(ds);
        try {
            query.setFailedAttempts(runner.query(sql, rsh, handle));
        } catch (SQLException e) {
            log.error("SQL exception while executing query.", e);
        }
    }
}

---

From source file:org.apache.lens.server.query.LensServerDAO.java

/**
 * Fetch Finished query from Database./*from w ww  . j av a  2 s  .com*/
 *
 * @param handle to be fetched
 * @return Finished query.
 */
public FinishedLensQuery getQuery(String handle) {
    ResultSetHandler<FinishedLensQuery> rsh = new BeanHandler<>(FinishedLensQuery.class,
            new BasicRowProcessor(new FinishedLensQueryBeanProcessor()));
    String sql = "select * from finished_queries where handle=?";
    QueryRunner runner = new QueryRunner(ds);
    try {
        FinishedLensQuery finishedQuery = runner.query(sql, rsh, handle);
        getFailedAttempts(finishedQuery);
        return finishedQuery;
    } catch (SQLException e) {
        log.error("SQL exception while executing query.", e);
    }
    return null;
}

---

From source file:org.apache.lens.server.query.LensServerDAO.java

private <T> List<T> findInternal(List<QueryStatus.Status> states, String user, String driverName,
        String queryName, long fromDate, long toDate, final ResultSetHandler<T> handler, String projection)
        throws LensException {
    StringBuilder builder = new StringBuilder("SELECT " + projection + " FROM finished_queries");
    List<Object> params = new ArrayList<>(3);
    builder.append(" WHERE ");
    List<String> filters = new ArrayList<>(3);

    if (states != null && !states.isEmpty()) {
        StringBuilder statusFilterBuilder = new StringBuilder("status in (");
        String sep = "";
        for (QueryStatus.Status status : states) {
            statusFilterBuilder.append(sep).append("?");
            sep = ", ";
            params.add(status.toString());
        }/*from   w w w.ja  va2s  . co  m*/
        filters.add(statusFilterBuilder.append(")").toString());
    }

    if (StringUtils.isNotBlank(user)) {
        filters.add("submitter=?");
        params.add(user);
    }

    if (StringUtils.isNotBlank(queryName)) {
        filters.add("queryname like ?");
        params.add("%" + queryName + "%");
    }

    if (StringUtils.isNotBlank(driverName)) {
        filters.add("lower(drivername)=?");
        params.add(driverName.toLowerCase());
    }

    filters.add("submissiontime BETWEEN ? AND ?");
    params.add(fromDate);
    params.add(toDate);
    builder.append(StringUtils.join(filters, " AND "));

    ResultSetHandler<List<T>> resultSetHandler = new ResultSetHandler<List<T>>() {
        @Override
        public List<T> handle(ResultSet resultSet) throws SQLException {
            List<T> results = new ArrayList<T>();
            while (resultSet.next()) {
                try {
                    results.add(handler.handle(resultSet));
                } catch (RuntimeException e) {
                    log.warn("Unable to handle row " + LensServerDAO.toString(resultSet), e);
                }
            }
            return results;
        }
    };

    QueryRunner runner = new QueryRunner(ds);
    String query = builder.toString();
    try {
        return runner.query(query, resultSetHandler, params.toArray());
    } catch (SQLException e) {
        throw new LensException(e);
    }
}

---

From source file:org.apache.lens.server.util.UtilityMethods.java

/**
 * Query database./* www  . j a v a  2 s.c o m*/
 *
 * @param ds        the ds
 * @param querySql  the query sql
 * @param allowNull the allow null
 * @param args      the args
 * @return the string[]
 * @throws SQLException the SQL exception
 */
public static String[] queryDatabase(DataSource ds, String querySql, final boolean allowNull, Object... args)
        throws SQLException {
    QueryRunner runner = new QueryRunner(ds);
    return runner.query(querySql, new ResultSetHandler<String[]>() {
        @Override
        public String[] handle(ResultSet resultSet) throws SQLException {
            String[] result = new String[resultSet.getMetaData().getColumnCount()];
            if (!resultSet.next()) {
                if (allowNull) {
                    return null;
                }
                throw new SQLException("no rows retrieved in query");
            }
            for (int i = 1; i <= resultSet.getMetaData().getColumnCount(); i++) {
                result[i - 1] = resultSet.getString(i);
            }
            if (resultSet.next()) {
                throw new SQLException("more than one row retrieved in query");
            }
            return result;
        }
    }, args);
}

---

From source file:org.codesearch.commons.database.DBAccessImpl.java

/**
 * {@inheritDoc}/*  w ww. jav  a 2  s  .  c  o  m*/
 */
@Override
public synchronized String getFilePathForTypeDeclaration(String className, String repository,
        List<String> asteriskImports) throws DatabaseAccessException {
    if (asteriskImports.isEmpty()) {
        return null;
    }
    ResultSetHandler<String> handler = new SingleValueStringHandler();
    StringBuilder importString = new StringBuilder();
    for (String currentImport : asteriskImports) {
        importString.append("'").append(currentImport.substring(0, currentImport.length() - 1))
                .append(className).append("',");
    }
    importString.deleteCharAt(importString.length() - 1);
    importString.append(")");
    QueryRunner run = new QueryRunner(dataSource);
    try {
        return run.query(STMT_GET_FILE_PATH_FOR_TYPE_DECLARATION_WITH_PACKAGES + importString.toString(),
                handler, repository);
    } catch (SQLException ex) {
        throw new DatabaseAccessException("SQLException while trying to access the database\n" + ex);
    }
}

---

From source file:org.codesearch.commons.database.DBAccessImpl.java

/**
 * {@inheritDoc}//from w w  w.j  a  v  a 2s  .c  o  m
 */
@Override
public synchronized String getLastAnalyzedRevisionOfRepository(String repositoryName)
        throws DatabaseAccessException {
    ResultSetHandler<String> handler = new SingleValueStringHandler();
    QueryRunner run = new QueryRunner(dataSource);
    try {
        String result = run.query(STMT_GET_LAST_ANALYZED_REVISION_OF_REPOSITORY, handler, repositoryName);
        if (StringUtils.isEmpty(result)) {
            createRepositoryEntry(repositoryName);
            return VersionControlPlugin.UNDEFINED_VERSION;
        } else {
            return result;
        }
    } catch (SQLException ex) {
        throw new DatabaseAccessException(
                "Could not close the stream used to retrieve the content of the binary-index field\n" + ex);
    }
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• »