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:io.stallion.dataAccess.db.DB.java

/**
 * Find an object using an arbitrary SQL WHERE ... clause
 *
 * @param model//  www  .  ja  va 2 s .  c  om
 * @param where
 * @param args
 * @return
 */
public Object where(Class model, String where, Object... args) {
    Schema schema = getSchemaForModelClass(model);

    QueryRunner runner = new QueryRunner(dataSource);
    //ResultSetHandler handler = new BeanHandler(model);
    String sql = "SELECT * FROM \"" + schema.getName() + "\" WHERE " + where;
    ModelListHandler handler = new ModelListHandler(schema);
    try {
        return runner.query(sql, handler, args);
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }
}

---

From source file:io.stallion.dataAccess.db.DB.java

/**
 * Find a list of objects via arbitrary SQL, checking the cache first, and storing to the
 * cache if retrieved fromt he database.
 *
 * @param model/* ww w .  j a  v a  2s .c  o m*/
 * @param sql
 * @param args
 * @param <T>
 * @return
 */
public <T extends Model> List<T> cachedQuery(Class<T> model, String sql, Object... args) {
    String cacheKey = buildCacheKey(model, sql, args);
    Object result = SmartQueryCache.getSmart(model.getCanonicalName(), cacheKey);
    if (result != null) {
        return (List<T>) result;
    }
    QueryRunner runner = new QueryRunner(dataSource);
    List<T> items = list();
    try {
        Schema schema = getSchemaForModelClass(model);
        if (!emptyInstance(schema)) {
            ModelListHandler<T> handler = new ModelListHandler<T>(schema);
            items = runner.query(sql, handler, args);
        } else {
            BeanListHandler<T> handler = new BeanListHandler(model);
            items = runner.query(sql, handler, args);
        }
        SmartQueryCache.set(model.getCanonicalName(), cacheKey, items);
        return items;
    } catch (SQLException e) {
        SmartQueryCache.set(model.getCanonicalName(), cacheKey, items);
        throw new RuntimeException(e);
    }
}

---

From source file:azkaban.executor.JdbcExecutorLoader.java

/**
 * {@inheritDoc}//  ww w  .  j  av  a 2 s  .com
 *
 * @see azkaban.executor.ExecutorLoader#fetchExecutor(int)
 */
@Override
public Executor fetchExecutor(int executorId) throws ExecutorManagerException {
    QueryRunner runner = createQueryRunner();
    FetchExecutorHandler executorHandler = new FetchExecutorHandler();

    try {
        List<Executor> executors = runner.query(FetchExecutorHandler.FETCH_EXECUTOR_BY_ID, executorHandler,
                executorId);
        if (executors.isEmpty()) {
            return null;
        } else {
            return executors.get(0);
        }
    } catch (Exception e) {
        throw new ExecutorManagerException(String.format("Error fetching executor with id: %d", executorId), e);
    }
}

---

From source file:io.stallion.dataAccess.db.DB.java

/**
 * Fetch an object where field==value//  w  ww.  ja va  2s .c o  m
 *
 * @param model
 * @param field
 * @param value
 * @param <T>
 * @return
 */
public <T> T fetchOne(Class<? extends T> model, String field, Object value) {
    Schema schema = getSchemaForModelClass(model);
    QueryRunner runner = new QueryRunner(dataSource);
    //ResultSetHandler handler = new BeanHandler(model);
    ResultSetHandler handler = new ModelResultHandler(schema);
    String sql = "SELECT * FROM " + schema.getName() + " WHERE " + field + "=? LIMIT 1";
    T record = null;
    try {
        record = (T) runner.query(sql, handler, value);
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }
    return record;
}

---

From source file:io.stallion.dataAccess.db.DB.java

/**
 * Select from the given model, using just an arbitrary WHERE ... clause, and use the cache.
 *
 * @param model/*  w w w .  j a va2s.  c o  m*/
 * @param where
 * @param args
 * @param <T>
 * @return
 */
public <T extends Model> List<T> cachedWhere(Class<T> model, String where, Object... args) {
    String cacheKey = buildCacheKey(model, where, args);
    Object result = SmartQueryCache.getSmart(model.getCanonicalName(), cacheKey);
    if (result != null) {
        return (List<T>) result;
    }
    Schema schema = getSchemaForModelClass(model);

    QueryRunner runner = new QueryRunner(dataSource);
    //ResultSetHandler handler = new BeanHandler(model);
    String sql = "SELECT * FROM " + schema.getName() + " WHERE " + where;
    ModelListHandler<T> handler = new ModelListHandler<T>(schema);
    List<T> items = list();
    try {
        items = runner.query(sql, handler, args);

        SmartQueryCache.set(model.getCanonicalName(), cacheKey, items);
        return items;
    } catch (SQLException e) {
        SmartQueryCache.set(model.getCanonicalName(), cacheKey, items);
        throw new RuntimeException(e);
    }
}

---

From source file:azkaban.executor.JdbcExecutorLoader.java

@Override
public ExecutableFlow fetchExecutableFlow(int id) throws ExecutorManagerException {
    QueryRunner runner = createQueryRunner();
    FetchExecutableFlows flowHandler = new FetchExecutableFlows();

    try {//from  ww  w.  j  av a 2  s.co  m
        List<ExecutableFlow> properties = runner.query(FetchExecutableFlows.FETCH_EXECUTABLE_FLOW, flowHandler,
                id);
        if (properties.isEmpty()) {
            return null;
        } else {
            return properties.get(0);
        }
    } catch (SQLException e) {
        throw new ExecutorManagerException("Error fetching flow id " + id, e);
    }
}

---

From source file:io.stallion.dataAccess.db.DB.java

/**
 * Find a list of objects of the given model via arbitrary SQL
 *
 * @param model/*from   www . ja v a 2 s .  com*/
 * @param sql
 * @param args
 * @param <T>
 * @return
 */
public <T extends Model> List<T> query(Class<T> model, String sql, Object... args) {
    QueryRunner runner = new QueryRunner(dataSource);
    Schema schema = null;
    if (Model.class.isAssignableFrom(model)) {
        schema = getSchemaForModelClass(model);
    }
    if (schema != null) {
        ModelListHandler<T> handler = new ModelListHandler<T>(schema);
        try {
            return runner.query(sql, handler, args);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    } else {
        BeanListHandler<T> handler = new BeanListHandler(model);
        try {
            return runner.query(sql, handler, args);
        } catch (SQLException e) {
            Log.exception(e.getNextException(), "Root exception in query");
            throw new RuntimeException(e);
        }
    }
}

---

From source file:azkaban.executor.JdbcExecutorLoader.java

/**
 *
 * {@inheritDoc}/*from  w w  w .  jav  a 2 s  . c om*/
 *
 * @see azkaban.executor.ExecutorLoader#fetchExecutorByExecutionId(int)
 */
@Override
public Executor fetchExecutorByExecutionId(int executionId) throws ExecutorManagerException {
    QueryRunner runner = createQueryRunner();
    FetchExecutorHandler executorHandler = new FetchExecutorHandler();
    Executor executor = null;
    try {
        List<Executor> executors = runner.query(FetchExecutorHandler.FETCH_EXECUTION_EXECUTOR, executorHandler,
                executionId);
        if (executors.size() > 0) {
            executor = executors.get(0);
        }
    } catch (SQLException e) {
        throw new ExecutorManagerException("Error fetching executor for exec_id : " + executionId, e);
    }
    return executor;
}

---

From source file:io.stallion.dataAccess.db.DB.java

/**
 * Fetch all objects where column field is of value
 *
 * @param model/*ww w. j a  v a2s  .  c o m*/
 * @param field
 * @param value
 * @param <T>
 * @return
 */
public <T extends Model> List<T> fetchAll(Class<? extends T> model, String field, Object value) {
    Schema schema = getSchemaForModelClass(model);
    QueryRunner runner = new QueryRunner(dataSource);
    ModelListHandler<T> handler = new ModelListHandler<T>(schema);

    String sql = "SELECT * FROM " + schema.getName();
    if (!empty(field)) {
        sql += " WHERE " + field + "=?";
    }
    List records = null;
    try {
        if (!empty(field)) {
            records = runner.query(sql, handler, value);
        } else {
            records = runner.query(sql, handler);
        }
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }
    return records;
}

---

From source file:azkaban.executor.JdbcExecutorLoader.java

@Override
public List<ExecutableFlow> fetchFlowHistory(String projContain, String flowContains, String userNameContains,
        int status, long startTime, long endTime, int skip, int num) throws ExecutorManagerException {
    String query = FetchExecutableFlows.FETCH_BASE_EXECUTABLE_FLOW_QUERY;
    ArrayList<Object> params = new ArrayList<Object>();

    boolean first = true;
    if (projContain != null && !projContain.isEmpty()) {
        query += " ef JOIN projects p ON ef.project_id = p.id WHERE name LIKE ?";
        params.add('%' + projContain + '%');
        first = false;//from   ww  w  . j  av a2  s .  c  o  m
    }

    if (flowContains != null && !flowContains.isEmpty()) {
        if (first) {
            query += " WHERE ";
            first = false;
        } else {
            query += " AND ";
        }

        query += " flow_id LIKE ?";
        params.add('%' + flowContains + '%');
    }

    if (userNameContains != null && !userNameContains.isEmpty()) {
        if (first) {
            query += " WHERE ";
            first = false;
        } else {
            query += " AND ";
        }
        query += " submit_user LIKE ?";
        params.add('%' + userNameContains + '%');
    }

    if (status != 0) {
        if (first) {
            query += " WHERE ";
            first = false;
        } else {
            query += " AND ";
        }
        query += " status = ?";
        params.add(status);
    }

    if (startTime > 0) {
        if (first) {
            query += " WHERE ";
            first = false;
        } else {
            query += " AND ";
        }
        query += " start_time > ?";
        params.add(startTime);
    }

    if (endTime > 0) {
        if (first) {
            query += " WHERE ";
            first = false;
        } else {
            query += " AND ";
        }
        query += " end_time < ?";
        params.add(endTime);
    }

    if (skip > -1 && num > 0) {
        query += "  ORDER BY exec_id DESC LIMIT ?, ?";
        params.add(skip);
        params.add(num);
    }

    QueryRunner runner = createQueryRunner();
    FetchExecutableFlows flowHandler = new FetchExecutableFlows();

    try {
        List<ExecutableFlow> properties = runner.query(query, flowHandler, params.toArray());
        return properties;
    } catch (SQLException e) {
        throw new ExecutorManagerException("Error fetching active flows", e);
    }
}

---

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