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

List of usage examples for org.apache.commons.dbutils QueryRunner query

  1. HOME
  2. Java
  3. org.apache.commons
  4. org.apache.commons.dbutils.*
  5. QueryRunner
  6. query

Introduction

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

Prototype

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

Source Link

Document

Execute an SQL SELECT query with replacement parameters.

Usage

From source file:com.aw.core.dao.DAOSql.java

/**
 * Helper method used to retrieve a list of objects. The object element of the list
 * is determined by the resultSetHandler
 *
 * @param sql              SQL query// w ww  . j  a v  a  2 s .  c  o m
 * @param filterKeys       key used to restrict the search
 * @param resultSetHandler maps a resultset row to a object (array, bean, etc)
 * @return List of objects
 */
public Object executeQuery(String sql, Object[] filterKeys, ResultSetHandler resultSetHandler) {
    QueryRunner queryRunner = new QueryRunner();
    try {
        if (logger.isDebugEnabled())
            logger.debug("Executing:" + AWQueryRunner.buildSQLLog(sql, filterKeys));

        Connection con = getHibernateConnection();
        return queryRunner.query(con, sql, filterKeys, resultSetHandler);
    } catch (Exception e) {
        //logger.error("Error executing query", e);
        throw AWBusinessException.wrapUnhandledException(logger, e);
    }
}

From source file:com.odap.server.audit.ConfigHandler.java

public ConfigMessage registerNewServer(ConfigMessage config) throws TException {
    logger.info("Enter registerNewServer()");
    ConfigMessage msg = new ConfigMessage();

    QueryRunner qRunner = new QueryRunner();

    Integer account_id = null;/*from w  w  w.  java 2  s .c o  m*/
    Integer server_id = null;
    //Authenticate the user and get the account_id;
    String query = "SELECT * FROM joomla.cloud_users WHERE username = ? AND password = ?";
    {
        String parameters[] = { config.getUsername().replaceAll("[^A-Za-z0-9 ]", ""),
                DigestUtils.md5Hex(config.getPassword().replaceAll("[^A-Za-z0-9 ]", "")) };
        try {
            List<Map<String, Object>> mapList = (List<Map<String, Object>>) qRunner
                    .query(AuditServer.connectionPool.getConnection(), query, new MapListHandler(), parameters);

            if (mapList.size() < 1) {
                logger.warn("Username " + config.getUsername() + " not authenticated");
                return msg;
            }

            account_id = (Integer) mapList.get(0).get("account_id");

        } catch (SQLException e) {
            logger.error("Issue finding user account", e);
            return msg;
        }
    }

    String session_id = nextSessionId();
    {
        try {
            {
                query = "INSERT INTO servers (account_id,server_name,server_software,server_port,server_authentication_token,server_timezone,strip_predicates) VALUES (?,?,?,?,?,?,?)";
                Object parameters[] = { account_id.toString(),
                        config.getServer_name().replaceAll("[^A-Za-z0-9 ]", ""), config.getServer_software(),
                        new Short(config.getPort()), session_id, new Double(config.getTimezone_offset()),
                        config.strip_predicates };
                qRunner.update(AuditServer.connectionPool.getConnection(), query, parameters);
            }
            {
                String parameters[] = { account_id.toString(), config.getServer_name(), session_id };
                query = "SELECT * FROM servers WHERE account_id = ? AND server_name = ? and server_authentication_token = ?";
                List<Map<String, Object>> mapList = (List<Map<String, Object>>) qRunner.query(
                        AuditServer.connectionPool.getConnection(), query, new MapListHandler(), parameters);

                if (mapList.size() < 1) {
                    logger.error("Unable to find server after after registering it");
                    return msg;
                }

                server_id = (Integer) mapList.get(0).get("id");
            }
        } catch (SQLException e) {
            logger.error("Issue registering server", e);
        }
    }

    msg.token = session_id;
    msg.server_id = server_id.shortValue();
    msg.server = "dbauditcloud.com";
    logger.info("Exiting registerNewServer()");
    return msg;
}

From source file:com.versatus.jwebshield.securitylock.SecurityLockService.java

private SecurityLock checkSecurityLock(int userId, String ip) throws SQLException {

    logger.debug("checkAccountLock: userid=" + userId);
    logger.debug("checkAccountLock: ip=" + ip);

    SecurityLock res;/* ww  w. ja va2s .co  m*/
    Object[] params = new Object[] { userId, ip };

    QueryRunner run = new QueryRunner();
    Connection conn = dbHelper.getConnection();
    BeanHandler<SecurityLock> rsh = new BeanHandler(SecurityLock.class) {

        @Override
        public SecurityLock handle(ResultSet rs) throws SQLException {
            SecurityLock brp = null;
            if (rs.first()) {
                brp = new BasicRowProcessor().toBean(rs, SecurityLock.class);
            }
            return brp;
        }
    };

    try {

        res = run.query(conn, lockCheckSql, rsh, params);

        logger.debug("checkAccountLock: response=" + res);

        if (res != null) {
            if (res.isLock()) {
                logger.debug("checkAccountLock: Calendar.getInstance()=" + Calendar.getInstance().getTime());
                logger.debug("checkAccountLock: TimeWhenUnlock()=" + res.getTimeWhenUnlock());
                logger.debug("checkAccountLock: is time to ulock="
                        + Calendar.getInstance().getTime().after(res.getTimeWhenUnlock()));
                if (Calendar.getInstance().getTime().after(res.getTimeWhenUnlock())) {
                    logger.info("unlocking IP " + res.getIp());
                    int r = run.update(conn, resetLockSql, new Object[] { ip });

                    logger.debug("checkAccountLock: reset response=" + r);

                    res = run.query(conn, lockCheckSql, rsh, params);

                    logger.debug("checkAccountLock: after reset response=" + res);
                }
            }

        } else {
            res = new SecurityLock();
            res.setLock(false);
        }

    } finally {

        try {
            DbUtils.close(conn);
        } catch (SQLException e) {
            // ignore
        }
    }

    return res;
}

From source file:com.netflix.metacat.usermetadata.mysql.MySqlTagService.java

/**
 * Returns the list of <code>QualifiedName</code> of items that are tagged by the
 * given <code>includeTags</code> and do not contain the given <code>excludeTags</code>.
 * @param includeTags include items that contain tags
 * @param excludeTags include items that do not contain tags
 * @param sourceName catalog/source name
 * @param databaseName database name/* ww w.  j  av  a  2 s.c  o  m*/
 * @param tableName table name
 * @return list of qualified names of the items
 */
@Override
public List<QualifiedName> list(final Set<String> includeTags, final Set<String> excludeTags,
        final String sourceName, final String databaseName, final String tableName) {
    Set<String> includedNames = Sets.newHashSet();
    final Set<String> excludedNames = Sets.newHashSet();
    final Connection connection = DBUtil.getReadConnection(getDataSource());
    try {
        final QueryRunner runner = new QueryRunner();
        final String wildCardName = QualifiedName.toWildCardString(sourceName, databaseName, tableName);
        //Includes
        String query = String.format(QUERY_SEARCH,
                "in ('" + Joiner.on("','").skipNulls().join(includeTags) + "')");
        final Object[] params = { includeTags.size() == 0 ? 1 : 0, wildCardName == null ? 1 : 0, wildCardName };
        includedNames.addAll(runner.query(connection, query, new ColumnListHandler<>("name"), params));
        if (excludeTags != null && !excludeTags.isEmpty()) {
            //Excludes
            query = String.format(QUERY_SEARCH,
                    "in ('" + Joiner.on("','").skipNulls().join(excludeTags) + "')");
            final Object[] eParams = { excludeTags.size() == 0 ? 1 : 0, wildCardName == null ? 1 : 0,
                    wildCardName };
            excludedNames.addAll(runner.query(connection, query, new ColumnListHandler<>("name"), eParams));
        }
    } catch (SQLException e) {
        final String message = String.format("Failed getting the list of qualified names for tags %s",
                includeTags);
        log.error(message, e);
        throw new UserMetadataServiceException(message, e);
    } finally {
        DBUtil.closeReadConnection(connection);
    }

    if (excludeTags != null && !excludeTags.isEmpty()) {
        includedNames = Sets.difference(includedNames, excludedNames);
    }

    return includedNames.stream().map(s -> QualifiedName.fromString(s, false)).collect(Collectors.toList());
}

From source file:com.pinterest.deployservice.db.DBDeployDAOImpl.java

@Override
public DeployQueryResultBean getAllDeploys(DeployQueryFilter filterBean) throws Exception {
    QueryRunner run = new QueryRunner(dataSource);
    ResultSetHandler<List<DeployBean>> h = new BeanListHandler<>(DeployBean.class);

    String queryStr;//from   w  w  w . j a v a  2  s  . c om
    if (StringUtils.isNotEmpty(filterBean.getFilter().getCommit())) {
        // TODO pretty hacky
        // It is very important to delete the commit from the filter, since we
        // want to return all deploys with commits later than this commit
        filterBean.getFilter().setCommit(null);
        filterBean.generateClauseAndValues();
        queryStr = String.format(GET_ALL_DEPLOYMENTS_WITH_COMMIT_TEMPLATE, filterBean.getWhereClause());
    } else {
        filterBean.generateClauseAndValues();
        queryStr = String.format(GET_ALL_DEPLOYMENTS_TEMPLATE, filterBean.getWhereClause());
    }

    Connection connection = dataSource.getConnection();
    try {
        List<DeployBean> deployBeans = run.query(connection, queryStr, h, filterBean.getValueArray());
        long total = run.query(connection, FOUND_ROWS, SingleResultSetHandlerFactory.newObjectHandler());
        long maxToReturn = filterBean.getFilter().getPageIndex() * filterBean.getFilter().getPageSize();
        return new DeployQueryResultBean(deployBeans, total, total > maxToReturn);
    } finally {
        DbUtils.closeQuietly(connection);
    }
}

From source file:azkaban.project.JdbcProjectLoader.java

private List<Triple<String, Boolean, Permission>> fetchPermissionsForProject(Connection connection,
        Project project) throws ProjectManagerException {
    ProjectPermissionsResultHandler permHander = new ProjectPermissionsResultHandler();

    QueryRunner runner = new QueryRunner();
    List<Triple<String, Boolean, Permission>> permissions = null;
    try {/*from  w ww.jav  a  2  s  .c om*/
        permissions = runner.query(connection, ProjectPermissionsResultHandler.SELECT_PROJECT_PERMISSION,
                permHander, project.getId());
    } catch (SQLException e) {
        throw new ProjectManagerException("Query for permissions for " + project.getName() + " failed.", e);
    }

    return permissions;
}

From source file:azkaban.executor.JdbcExecutorLoader.java

@Override
public Props fetchExecutionJobInputProps(int execId, String jobId) throws ExecutorManagerException {
    QueryRunner runner = createQueryRunner();
    try {//from   www  . j a  va  2s  . c om
        Pair<Props, Props> props = runner.query(
                FetchExecutableJobPropsHandler.FETCH_INPUT_PARAM_EXECUTABLE_NODE,
                new FetchExecutableJobPropsHandler(), execId, jobId);
        return props.getFirst();
    } catch (SQLException e) {
        throw new ExecutorManagerException("Error querying job params " + execId + " " + jobId, e);
    }
}

From source file:azkaban.executor.JdbcExecutorLoader.java

@Override
public Props fetchExecutionJobOutputProps(int execId, String jobId) throws ExecutorManagerException {
    QueryRunner runner = createQueryRunner();
    try {//from w w  w  .  j  a va2 s .c  o  m
        Pair<Props, Props> props = runner.query(
                FetchExecutableJobPropsHandler.FETCH_OUTPUT_PARAM_EXECUTABLE_NODE,
                new FetchExecutableJobPropsHandler(), execId, jobId);
        return props.getFirst();
    } catch (SQLException e) {
        throw new ExecutorManagerException("Error querying job params " + execId + " " + jobId, e);
    }
}

From source file:azkaban.executor.JdbcExecutorLoader.java

@Override
public Pair<Props, Props> fetchExecutionJobProps(int execId, String jobId) throws ExecutorManagerException {
    QueryRunner runner = createQueryRunner();
    try {/*from  w  w  w . j a va  2s.c  o m*/
        Pair<Props, Props> props = runner.query(
                FetchExecutableJobPropsHandler.FETCH_INPUT_OUTPUT_PARAM_EXECUTABLE_NODE,
                new FetchExecutableJobPropsHandler(), execId, jobId);
        return props;
    } catch (SQLException e) {
        throw new ExecutorManagerException("Error querying job params " + execId + " " + jobId, e);
    }
}

From source file:azkaban.executor.JdbcExecutorLoader.java

@Override
public List<Object> fetchAttachments(int execId, String jobId, int attempt) throws ExecutorManagerException {
    QueryRunner runner = createQueryRunner();

    try {//  w  w w  .j  a  v a  2 s  .c o m
        String attachments = runner.query(
                FetchExecutableJobAttachmentsHandler.FETCH_ATTACHMENTS_EXECUTABLE_NODE,
                new FetchExecutableJobAttachmentsHandler(), execId, jobId);
        if (attachments == null) {
            return null;
        }

        @SuppressWarnings("unchecked")
        List<Object> attachmentList = (List<Object>) JSONUtils.parseJSONFromString(attachments);

        return attachmentList;
    } catch (IOException e) {
        throw new ExecutorManagerException("Error converting job attachments to JSON " + jobId, e);
    } catch (SQLException e) {
        throw new ExecutorManagerException("Error query job attachments " + jobId, e);
    }
}