Java QueryRunner类代码示例

import org.apache.commons.dbutils.QueryRunner; //导入依赖的package包/类
@Override
public List<Executor> fetchExecutor(String clusterGroup) throws ExecutorManagerException {
  QueryRunner runner = createQueryRunner();
  FetchExecutorHandler fetchExecutorHandler = new FetchExecutorHandler();
  try {
    List<Executor> executors = runner.query(FetchExecutorHandler.FETCH_EXECUTOR_BY_CLUSTERGROUP,
            fetchExecutorHandler, clusterGroup);
    if (executors.isEmpty()) {
      return null;
    } else {
      return executors;
    }
  } catch (Exception e) {
    throw new ExecutorManagerException(String.format(
            "Error fetching executor with clusterGroup: %s", clusterGroup), e);
  }
}
 

import org.apache.commons.dbutils.QueryRunner; //导入依赖的package包/类
public static boolean migrateOrCreateIfNotExists(String dbname) {
    String dbpath = getDbPath(dbname);
    String jdbcurl = "jdbc:h2:file:" + dbpath + ";DB_CLOSE_ON_EXIT=FALSE";
    LOG.info("new jdbcurl = {}", jdbcurl);
    Connection conn = null;
    try {
        conn = DriverManager.getConnection(jdbcurl, "sa", "");

        DataSource ds = new CustomFlywayDataSource(new PrintWriter(System.out), conn);
        Flyway flyway = new Flyway();
        flyway.setDataSource(ds);
        // explicitly set h2db driver loaded (= including this jar file) class loader
        flyway.setClassLoader(conn.getClass().getClassLoader());
        flyway.migrate();
        LOG.info("db[{}] migration success", dbname);

        // may be already closed -> reconnect.
        DbUtils.closeQuietly(conn);
        conn = DriverManager.getConnection(jdbcurl, "sa", "");

        QueryRunner r = new QueryRunner();
        Long cnt = r.query(conn, "select count(*) from PROXY_HISTORY", new ScalarHandler<Long>());
        LOG.info("db[{}] open/creation success(select count(*) from logtable returns {})", dbname, cnt);

        return true;
    } catch (SQLException e) {
        LOG.error("db[" + dbname + "] open/migrate/creation error", e);
        return false;
    } finally {
        DbUtils.closeQuietly(conn);
    }
}
 

import org.apache.commons.dbutils.QueryRunner; //导入依赖的package包/类
@Override
public Flow fetchFlow(Project project, String flowId)
    throws ProjectManagerException {
  QueryRunner runner = createQueryRunner();
  ProjectFlowsResultHandler handler = new ProjectFlowsResultHandler();

  try {
    List<Flow> flows =
        runner.query(ProjectFlowsResultHandler.SELECT_PROJECT_FLOW, handler,
            project.getId(), project.getVersion(), flowId);
    if (flows.isEmpty()) {
      return null;
    } else {
      return flows.get(0);
    }
  } catch (SQLException e) {
    throw new ProjectManagerException("Error fetching flow " + flowId, e);
  }
}
 

import org.apache.commons.dbutils.QueryRunner; //导入依赖的package包/类
public static int insert(Connection dbconn, final String logContext, final int messageRef,
        final IHttpService httpService, final String clientIp, final String requestUrl, final String method,
        final byte[] reqdata) throws SQLException {
    QueryRunner runner = new QueryRunner();
    String sql =
        "insert into proxy_history(log_context, message_ref, protocol, host, port, client_ip, url, request_method, request_bytes, send_at)"
            + " values (?, ?, ?, ?, ?, ?, ?, ?, ?, now())";
    return runner.update(
        dbconn,
        sql,
        logContext,
        messageRef,
        httpService.getProtocol(),
        httpService.getHost(),
        httpService.getPort(),
        clientIp,
        requestUrl,
        method,
        reqdata);
}
 

import org.apache.commons.dbutils.QueryRunner; //导入依赖的package包/类
public static int selectCountBySql(String countSql) {
	Connection conn=getConnection();
	QueryRunner runner=new QueryRunner();
	ResultSetHandler<Integer> rsh=new ResultSetHandler<Integer>() {
		@Override
		public Integer handle(ResultSet rs) throws SQLException {
			while (rs.next()) {
				return rs.getInt(1);
			}
			return 1;
		}
	};
	int count=0;
	try {
		count=runner.query(conn,countSql, rsh);
	} catch (SQLException e) {
		e.printStackTrace();
	}
	return count;
}
 

import org.apache.commons.dbutils.QueryRunner; //导入依赖的package包/类
public static double selectSingleBySql(String avgSql) {
	Connection conn=getConnection();
	QueryRunner runner=new QueryRunner();
	ResultSetHandler<Double> rsh=new ResultSetHandler<Double>() {
		@Override
		public Double handle(ResultSet rs) throws SQLException {
			while (rs.next()) {
				return rs.getDouble(1);
			}
			return 0.0;
		}
	};
	Double avg=0.0;
	try {
		avg=runner.query(conn,avgSql, rsh);
	} catch (SQLException e) {
		e.printStackTrace();
	}
	if(avg==null) avg=0.0;
	return avg;
}
 

import org.apache.commons.dbutils.QueryRunner; //导入依赖的package包/类
@Override
public ImmutableCollection<DaRule> searchRules(final DaSchema schema, Connection conn) throws SQLException {
    QueryRunner query = new QueryRunner();  // using queryRunner so that we can reuse the connection

    // Do not use ANSI JOIN as it does not work in Sybase 11.x - the SQL below works across all versions
    String sql = "SELECT rul.name as RULE_NAME\n" +
            "FROM " + schema.getName() + "..sysobjects rul\n" +
            "    , sys.schemas sch\n" +
            "WHERE rul.type = 'R'\n" +
            "    and rul.uid = sch.schema_id and sch.name = '" + schema.getSubschemaName() + "' " +
            "and not exists (\n" +
            "\t-- Ensure that the entry is not attached to a table; otherwise, it is a regular table constraint, and will already be dropped when the table is dropped\n" +
            "\tselect 1 from " + schema.getName() + "..sysconstraints c\n" +
            "\twhere c.constid = rul.id\n" +
            ")\n";
    ImmutableList<Map<String, Object>> maps = ListAdapter.adapt(query.query(conn, sql, new MapListHandler())).toImmutable();

    return maps.collect(new Function<Map<String, Object>, DaRule>() {
        @Override
        public DaRule valueOf(Map<String, Object> map) {
            return new DaRuleImpl((String) map.get("RULE_NAME"), schema);
        }
    });
}
 

import org.apache.commons.dbutils.QueryRunner; //导入依赖的package包/类
@Override
public ImmutableCollection<DaUserType> searchUserTypes(final DaSchema schema, Connection conn) throws SQLException {
    QueryRunner query = new QueryRunner();

    String sql = "SELECT DOMAIN_NAME as USER_TYPE_NAME " +
            "FROM INFORMATION_SCHEMA.DOMAINS " +
            "WHERE DOMAIN_CATALOG = '" + schema.getName() + "' " +
            "AND DOMAIN_SCHEMA = '" + schema.getSubschemaName() + "'";
    ImmutableList<Map<String, Object>> maps = ListAdapter.adapt(query.query(conn, sql, new MapListHandler())).toImmutable();

    return maps.collect(new Function<Map<String, Object>, DaUserType>() {
        @Override
        public DaUserType valueOf(Map<String, Object> map) {
            return new DaUserTypeImpl((String) map.get("USER_TYPE_NAME"), schema);
        }
    });
}
 

import org.apache.commons.dbutils.QueryRunner; //导入依赖的package包/类
@Override
public ImmutableCollection<DaUserType> searchUserTypes(final DaSchema schema, Connection conn) throws SQLException {
    QueryRunner query = new QueryRunner();

    ImmutableList<Map<String, Object>> maps = ListAdapter.adapt(query.query(conn,
            "select dom.DOMAIN_NAME AS USER_TYPE_NAME\n" +
                    "from INFORMATION_SCHEMA.DOMAINS dom\n" +
                    "WHERE dom.DOMAIN_SCHEMA = ucase('" + schema.getName() + "')\n",
            new MapListHandler()
    )).toImmutable();

    return maps.collect(new Function<Map<String, Object>, DaUserType>() {
        @Override
        public DaUserType valueOf(Map<String, Object> map) {
            return new DaUserTypeImpl((String) map.get("USER_TYPE_NAME"), schema);
        }
    });
}
 

import org.apache.commons.dbutils.QueryRunner; //导入依赖的package包/类
@Override
public ImmutableCollection<DaRule> searchRules(final DaSchema schema, Connection conn) throws SQLException {
    QueryRunner query = new QueryRunner();  // using queryRunner so that we can reuse the connection

    // Do not use ANSI JOIN as it does not work in Sybase 11.x - the SQL below works across all versions
    ImmutableList<Map<String, Object>> maps = ListAdapter.adapt(query.query(conn,
            "SELECT rul.name as RULE_NAME\n" +
                    "FROM " + schema.getName() + "..sysobjects rul\n" +
                    "    , " + schema.getName() + "..sysusers sch\n" +
                    "WHERE rul.type = 'R'\n" +
                    "    and rul.uid = sch.uid and sch.name = '" + schema.getSubschemaName() + "' " +
                    "and not exists (\n" +
                    "\t-- Ensure that the entry is not attached to a table; otherwise, it is a regular table constraint, and will already be dropped when the table is dropped\n" +
                    "\tselect 1 from " + schema.getName() + "..sysconstraints c\n" +
                    "\twhere c.constrid = rul.id\n" +
                    ")\n",
            new MapListHandler()
    )).toImmutable();

    return maps.collect(new Function<Map<String, Object>, DaRule>() {
        @Override
        public DaRule valueOf(Map<String, Object> map) {
            return new DaRuleImpl((String) map.get("RULE_NAME"), schema);
        }
    });
}
 

import org.apache.commons.dbutils.QueryRunner; //导入依赖的package包/类
@Override
public ImmutableCollection<DaUserType> searchUserTypes(final DaSchema schema, Connection conn) throws SQLException {
    QueryRunner query = new QueryRunner();

    ImmutableList<Map<String, Object>> maps = ListAdapter.adapt(query.query(conn,
            "SELECT s1.name as USER_TYPE_NAME\n" +
                    "FROM " + schema.getName() + "..systypes s1\n" +
                    "    , " + schema.getName() + "..sysusers sch\n" +
                    "WHERE s1.usertype>100 " +
                    "AND s1.uid = sch.uid and sch.name = '" + schema.getSubschemaName() + "' "
            , new MapListHandler()
    )).toImmutable();

    return maps.collect(new Function<Map<String, Object>, DaUserType>() {
        @Override
        public DaUserType valueOf(Map<String, Object> map) {
            return new DaUserTypeImpl((String) map.get("USER_TYPE_NAME"), schema);
        }
    });
}
 

import org.apache.commons.dbutils.QueryRunner; //导入依赖的package包/类
@Override
public ExecutableJobInfo fetchJobInfo(int execId, String jobId, int attempts)
    throws ExecutorManagerException {
  QueryRunner runner = createQueryRunner();

  try {
    List<ExecutableJobInfo> info =
        runner.query(FetchExecutableJobHandler.FETCH_EXECUTABLE_NODE,
            new FetchExecutableJobHandler(), execId, jobId, attempts);
    if (info == null || info.isEmpty()) {
      return null;
    }
    return info.get(0);
  } catch (SQLException e) {
    throw new ExecutorManagerException("Error querying job info " + jobId, e);
  }
}
 

import org.apache.commons.dbutils.QueryRunner; //导入依赖的package包/类
/**
 * Fetch all objects where column field is of value
 *
 * @param model
 * @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;
}
 

import org.apache.commons.dbutils.QueryRunner; //导入依赖的package包/类
/**
 * Find a list of objects of the given model via arbitrary SQL.
 * Accepts any java bean, does not require a Stallion Model
 *
 * @param model
 * @param sql
 * @param args
 * @param <T>
 * @return
 */
public <T> T fetchBean(Class<T> model, String sql, Object ...args) {
    QueryRunner runner = new QueryRunner(dataSource);
    //BeanListHandler<T> handler = new BeanListHandler(model);
    BeanListHandler<T> handler = new BeanListHandler(model);
    try {
        List<T> results = runner.query(sql, handler, args);
        if (results.size() == 0) {
            return null;
        } else {
            return results.get(0);
        }
    } catch (SQLException e) {
        Log.exception(e.getNextException(), "Root exception in queryBean");
        throw new RuntimeException(e);
    }
}
 

import org.apache.commons.dbutils.QueryRunner; //导入依赖的package包/类
/**
 * Find a single value based on the given model, from arbitrary SQL, and
 * use the cache.
 *
 * @param model
 * @param sql
 * @param params
 * @param <T>
 * @return
 */
public <T> T cachedScalar(Class model, String sql, Object ...params) {
    String cacheKey = buildCacheKey(model, sql, params);
    T result = (T) SmartQueryCache.getSmart(model.getCanonicalName(), cacheKey);
    if (result != null) {
        return result;
    }
    QueryRunner runner = new QueryRunner(dataSource);
    try {
        result = runner.query(sql, new ScalarHandler<T>(), params);
        SmartQueryCache.set(model.getCanonicalName(), cacheKey, result);
        return result;
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }

}