Java QueryRunner.query方法代码示例

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包/类
@Override
public Props fetchProjectProperty(int projectId, int projectVer,
    String propsName) throws ProjectManagerException {
  QueryRunner runner = createQueryRunner();

  ProjectPropertiesResultsHandler handler =
      new ProjectPropertiesResultsHandler();
  try {
    List<Pair<String, Props>> properties =
        runner.query(ProjectPropertiesResultsHandler.SELECT_PROJECT_PROPERTY,
            handler, projectId, projectVer, propsName);

    if (properties == null || properties.isEmpty()) {
      return null;
    }

    return properties.get(0).getSecond();
  } catch (SQLException e) {
    throw new ProjectManagerException("Error fetching property " + propsName,
        e);
  }
}
 

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包/类
/**
 *
 * {@inheritDoc}
 * @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);
  }
}
 

import org.apache.commons.dbutils.QueryRunner; //导入方法依赖的package包/类
@Override
public List<Triple<String, Boolean, Permission>> getProjectPermissions(
    int projectId) throws ProjectManagerException {
  ProjectPermissionsResultHandler permHander =
      new ProjectPermissionsResultHandler();
  QueryRunner runner = createQueryRunner();
  List<Triple<String, Boolean, Permission>> permissions = null;
  try {
    permissions =
        runner.query(
            ProjectPermissionsResultHandler.SELECT_PROJECT_PERMISSION,
            permHander, projectId);
  } catch (SQLException e) {
    throw new ProjectManagerException("Query for permissions for "
        + projectId + " failed.", e);
  }

  return permissions;
}
 

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

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

import org.apache.commons.dbutils.QueryRunner; //导入方法依赖的package包/类
/**
 *
 * {@inheritDoc}
 *
 * @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;
}
 

import org.apache.commons.dbutils.QueryRunner; //导入方法依赖的package包/类
public static ProxyHistory getDetail(Connection dbconn, final String logContext, final int messageRef)
        throws SQLException {
    String sql =
        "select log_context, message_ref, protocol, host, port, client_ip, url, request_method, request_bytes, request_charset, response_status_code, response_bytes, response_charset, send_at, received_at from proxy_history where log_context = ? and message_ref = ? order by send_at desc";
    QueryRunner runner = new QueryRunner();
    return runner.query(dbconn, sql, createDetailResultSetHandler(), logContext, messageRef);
}
 

import org.apache.commons.dbutils.QueryRunner; //导入方法依赖的package包/类
@Test
public void testQueryRunner() throws Exception {

    QueryRunner runner = new QueryRunner();
    String sql = "SELECT * FROM biz_pay_order where id = ?";

    ResultSetHandler<Object[]> rsh = new ResultSetHandler<Object[]>() {
        @Override
        public Object[] handle(ResultSet rs) throws SQLException {
            if (!rs.next()) {
                return null;
            }

            ResultSetMetaData metaData = rs.getMetaData();
            int cols = metaData.getColumnCount();
            Object[] result = new Object[cols];
            for (int i = 0; i < cols; i++) {
                result[i] = rs.getObject(i + 1);
            }

            return result;
        }
    };

    Object[] ret = runner.query(conn, sql, rsh, 3);

    for (int i = 0; i < ret.length; i++) {
        System.out.print(ret[i] + "  ");
    }

    DbUtils.close(conn);
}
 

import org.apache.commons.dbutils.QueryRunner; //导入方法依赖的package包/类
public static String resultSetToJson(Connection connection, String query) {
    List<Map<String, Object>> listOfMaps = null;
    try {
        QueryRunner queryRunner = new QueryRunner();
        listOfMaps = queryRunner.query(connection, query, new MapListHandler());
    } catch (SQLException se) {
        throw new RuntimeException("Couldn't query the database.", se);
    } finally {
        DbUtils.closeQuietly(connection);
    }
    return new Gson().toJson(listOfMaps);
}
 

import org.apache.commons.dbutils.QueryRunner; //导入方法依赖的package包/类
@Override
public <T extends BaseDto> List<T> executeQuery(String query, QueryResultTransformer<T> transformer, Connection conn) {
    ResultSetHandler<List<T>> rsHandler = getListResultSetHandler(transformer);
    QueryRunner qRunner = getQueryRunner();
    try {
        return qRunner.query(conn, query, rsHandler);
    } catch (SQLException e) {
        throw new RTSQLException(e);
    }
}
 

import org.apache.commons.dbutils.QueryRunner; //导入方法依赖的package包/类
public static String resultSetToJson(String query) {
    Connection connection = null;
    List<Map<String, Object>> listOfMaps = null;

    try {
        Class.forName("com.mysql.jdbc.Driver");
        connection = DriverManager.getConnection("jdbc:mysql://localhost:3306?"
                + "useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&allowMultiQueries"
                + "=true", "root", "123456");
    } catch (Exception ex) {
        System.err.println("***exception trying to connect***");
        ex.printStackTrace();
    }

    try {
        QueryRunner queryRunner = new QueryRunner();
        listOfMaps = queryRunner.query(connection, query, new MapListHandler());
    } catch (SQLException se) {
        throw new RuntimeException("Couldn't query the database.", se);
    } finally {
        DbUtils.closeQuietly(connection);
    }

    try {
        return new ObjectMapper().writeValueAsString(listOfMaps);

    } catch (JsonProcessingException e) {
        System.out.println(e.toString());
    }

    return null;
}