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) throws SQLException 

Source Link

Document

Executes the given SELECT SQL without any replacement parameters.

Usage

From source file:gr.osmosis.rcpsamples.contact.db.derby.DerbyContactsDAO.java

public int getMaxId() {

    StringBuffer sbSelect = new StringBuffer();
    sbSelect.append("SELECT MAX(ID) AS MAX_ID ");
    sbSelect.append(" FROM ");
    sbSelect.append(ContactsConstants.CONTACTS_TABLE_NAME);

    // Create a QueryRunner that will use connections from
    // the given DataSource
    DataSource d = DerbyDAOFactory.getDataSource();
    QueryRunner run = new QueryRunner(d);

    ResultSetHandler h = new ResultSetHandler() {
        public Object handle(ResultSet rs) throws SQLException {

            if (!rs.next()) {
                return null;
            }//from ww  w  .  j av a  2  s .com

            int max;

            max = rs.getInt("MAX_ID");

            Integer value = new Integer(max);

            return value;
        }
    };

    Object result = null;

    try {
        result = run.query(sbSelect.toString(), h);

    } catch (SQLException sex) {
        sex.printStackTrace();
    } catch (Exception ex) {
        ex.printStackTrace();
    }

    int max;

    if (result != null) {
        max = ((Integer) result).intValue();
    } else {
        max = 0;
    }

    return max;
}

---

From source file:jdao.JDAO.java

/**
 * queries connection according to give dbType and returns data as given by resultsethandler
 *
 * @param  dbType, type of database/* www .ja  v  a  2s .c  om*/
 * @param  rsHandler, resultsethandler
 * @param  conn, database connection
 * @param  ds, query runner
 * @param  sql, sql query
 * @param  args, sql parameters
 * @return object of type T or null
 */

public static <T> T queryForT(int dbType, ResultSetHandler<T> rsHandler, Connection conn, QueryRunner ds,
        String sql, Object... args) throws Exception {
    if (args == null) {
        if (conn == null) {
            return ds.query(sql, rsHandler);
        } else {
            return ds.query(conn, sql, rsHandler);
        }
    } else if (args.length == 1 && args[0] instanceof Map) {
        List nArgs = new Vector();
        sql = preparseParameters(dbType, sql, nArgs, (Map) args[0]);
        if (conn == null) {
            return ds.query(sql, rsHandler, nArgs.toArray());
        } else {
            return ds.query(conn, sql, rsHandler, nArgs.toArray());
        }
    } else if (args.length > 0 && args[0] instanceof Collection) {
        if (conn == null) {
            return ds.query(sql, rsHandler, ((Collection) args[0]).toArray());
        } else {
            return ds.query(conn, sql, rsHandler, ((Collection) args[0]).toArray());
        }
    } else {
        if (conn == null) {
            return ds.query(sql, rsHandler, args);
        } else {
            return ds.query(conn, sql, rsHandler, args);
        }
    }
}

---

From source file:azkaban.executor.JdbcExecutorLoader.java

@Override
public int fetchNumExecutableFlows() throws ExecutorManagerException {
    QueryRunner runner = createQueryRunner();

    IntHandler intHandler = new IntHandler();
    try {/*from   w w  w  .ja va2  s. c om*/
        int count = runner.query(IntHandler.NUM_EXECUTIONS, intHandler);
        return count;
    } catch (SQLException e) {
        throw new ExecutorManagerException("Error fetching num executions", e);
    }
}

---

From source file:azkaban.executor.JdbcExecutorLoader.java

/**
 *
 * {@inheritDoc}//from  w w w . j  a  va 2s  . co m
 *
 * @see azkaban.executor.ExecutorLoader#fetchActiveExecutors()
 */
@Override
public List<Executor> fetchAllExecutors() throws ExecutorManagerException {
    QueryRunner runner = createQueryRunner();
    FetchExecutorHandler executorHandler = new FetchExecutorHandler();

    try {
        List<Executor> executors = runner.query(FetchExecutorHandler.FETCH_ALL_EXECUTORS, executorHandler);
        return executors;
    } catch (Exception e) {
        throw new ExecutorManagerException("Error fetching executors", e);
    }
}

---

From source file:azkaban.executor.JdbcExecutorLoader.java

/**
 *
 * {@inheritDoc}//from  ww w  . ja  v  a2s. c  o m
 *
 * @see azkaban.executor.ExecutorLoader#fetchActiveExecutors()
 */
@Override
public List<Executor> fetchActiveExecutors() throws ExecutorManagerException {
    QueryRunner runner = createQueryRunner();
    FetchExecutorHandler executorHandler = new FetchExecutorHandler();

    try {
        List<Executor> executors = runner.query(FetchExecutorHandler.FETCH_ACTIVE_EXECUTORS, executorHandler);
        return executors;
    } catch (Exception e) {
        throw new ExecutorManagerException("Error fetching active executors", e);
    }
}

---

From source file:azkaban.executor.JdbcExecutorLoader.java

/**
 *
 * {@inheritDoc}/*  www. j  a v a 2  s.co m*/
 * @see azkaban.executor.ExecutorLoader#fetchQueuedFlows()
 */
@Override
public List<Pair<ExecutionReference, ExecutableFlow>> fetchQueuedFlows() throws ExecutorManagerException {
    QueryRunner runner = createQueryRunner();
    FetchQueuedExecutableFlows flowHandler = new FetchQueuedExecutableFlows();

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

---

From source file:azkaban.executor.JdbcExecutorLoader.java

@Override
public Map<Integer, Pair<ExecutionReference, ExecutableFlow>> fetchActiveFlows()
        throws ExecutorManagerException {
    QueryRunner runner = createQueryRunner();
    FetchActiveExecutableFlows flowHandler = new FetchActiveExecutableFlows();

    try {/*from w ww  .j a v  a2 s.c  om*/
        Map<Integer, Pair<ExecutionReference, ExecutableFlow>> properties = runner
                .query(FetchActiveExecutableFlows.FETCH_ACTIVE_EXECUTABLE_FLOW, flowHandler);
        return properties;
    } catch (SQLException e) {
        throw new ExecutorManagerException("Error fetching active flows", e);
    }
}

---

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

/**
 * Fetch all objects, sorted/*from   ww w.  j  a  v a 2s.c om*/
 *
 * @param model
 * @param <T>
 * @return
 */
public <T extends Model> List<T> fetchAllSorted(Class<? extends T> model, String sortField,
        String sortDirection) {
    Schema schema = getSchemaForModelClass(model);
    QueryRunner runner = new QueryRunner(dataSource);
    ModelListHandler<T> handler = new ModelListHandler<T>(schema);
    sortDirection = sortDirection.toUpperCase();
    if (!"ASC".equals(sortDirection) && !"DESC".equals(sortDirection)) {
        throw new UsageException("Invalid sort direction: " + sortDirection);
    }

    if (!"id".equals(sortField) && !"row_updated_at".equals(sortField)
            && !schema.getKeyNames().contains(sortField)) {
        throw new UsageException("Sort field must be a database key. Sort field was: " + sortField
                + " on model " + model.getCanonicalName());
    }

    String sql = "SELECT * FROM " + schema.getName() + " ORDER BY " + sortField + " " + sortDirection;
    List records = null;
    try {
        records = runner.query(sql, handler);
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }
    return records;
}

---

From source file:de.iritgo.aktario.jdbc.LoadAllObjects.java

/**
 * Perform the command.//w  ww  .  j a  va 2s. c om
 */
public void perform() {
    if (properties.getProperty("type") == null) {
        Log.logError("persist", "LoadObjects", "The type of the objects to load wasn't specified");

        return;
    }

    final String type = ((String) properties.getProperty("type"));

    final AbstractIObjectFactory factory = (AbstractIObjectFactory) Engine.instance().getIObjectFactory();

    IObject sample = null;

    try {
        sample = factory.newInstance(type);
    } catch (NoSuchIObjectException ignored) {
        Log.logError("persist", "LoadObjects", "Attemting to load objects of unknown type '" + type + "'");

        return;
    }

    if (!DataObject.class.isInstance(sample)) {
        Log.logError("persist", "LoadObjects", "Attemting to load objects that are not persitable");

        return;
    }

    final BaseRegistry registry = Engine.instance().getBaseRegistry();

    JDBCManager jdbcManager = (JDBCManager) Engine.instance().getManager("persist.JDBCManager");
    DataSource dataSource = jdbcManager.getDefaultDataSource();

    try {
        QueryRunner query = new QueryRunner(dataSource);

        ResultSetHandler resultSetHandler = properties.get("resultSetHandle") != null
                ? (ResultSetHandler) properties.get("resultSetHandler")
                : new ResultSetHandler() {
                    public Object handle(ResultSet rs) throws SQLException {
                        ResultSetMetaData meta = rs.getMetaData();

                        int numObjects = 0;

                        while (rs.next()) {
                            try {
                                DataObject object = (DataObject) factory.newInstance(type);

                                object.setUniqueId(rs.getLong("id"));

                                for (Iterator i = object.getAttributes().entrySet().iterator(); i.hasNext();) {
                                    Map.Entry attribute = (Map.Entry) i.next();

                                    if (attribute.getValue() instanceof IObjectList) {
                                        //                               loadList (
                                        //                                  dataSource, object,
                                        //                                  object.getIObjectListAttribute (
                                        //                                     (String) attribute.getKey ()));
                                    } else {
                                        object.setAttribute((String) attribute.getKey(),
                                                rs.getObject((String) attribute.getKey()));
                                    }
                                }

                                registry.add(object);
                                ++numObjects;
                            } catch (NoSuchIObjectException ignored) {
                            }
                        }

                        return new Integer(numObjects);
                    }
                };

        Object numObjects = query.query("select * from " + type, resultSetHandler);

        Log.logVerbose("persist", "LoadObjects",
                "Successfully loaded " + numObjects + " objects of type '" + type + "'");
    } catch (Exception x) {
        Log.logError("persist", "LoadObjects", "Error while loading objects of type '" + type + "': " + x);
    }
}

---

From source file:org.ice.etl.myetl.pool.connection.MyConnectionTest.java

@Test
public void testGetConnection() throws SQLException, ClassNotFoundException, PropertyVetoException {
    //        MyConnection connection = new MyConnection();
    //        Connection c = connection.getConnection();
    //        PreparedStatement ps = c.prepareStatement("select count(1) from test.soccer;");
    //        ResultSet rs = ps.executeQuery();
    //        while (rs.next()) {
    //            System.out.println(rs.getString(1));
    //        }// w w w  . j  ava  2  s .co m

    ResultSetHandler<Object[]> rsh = new ResultSetHandler<Object[]>() {

        @Override
        public Object[] handle(ResultSet rs) throws SQLException {
            if (!rs.next()) {
                return null;
            }
            ResultSetMetaData meta = rs.getMetaData();
            int cols = meta.getColumnCount();
            Object[] objs = new Object[cols];

            for (int i = 0; i < cols; i++) {
                objs[i] = rs.getObject(i + 1);
            }
            return objs;
            //                throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
        }
    };

    ComboPooledDataSource dataSource = new ComboPooledDataSource();
    dataSource.setDriverClass("com.mysql.jdbc.Driver");
    dataSource.setJdbcUrl("jdbc:mysql://localhost");
    dataSource.setUser("root");
    dataSource.setPassword("");
    QueryRunner qr = new QueryRunner(dataSource);
    Object[] result = qr.query("SELECT count(1) FROM test.soccer", rsh);
    System.out.println(result[0]);
}

---

• «
• 1
• 2
• 3
• 4
• 5
• »