Java Query.list方法代码示例

import org.skife.jdbi.v2.Query; //导入方法依赖的package包/类
public Person getPersonInPosition(Position b, DateTime dtg) { 
	String sql;
	if (DaoUtils.isMsSql(dbHandle)) {
		sql = "/*positionFindPerson */ SELECT TOP(1) " + PersonDao.PERSON_FIELDS + " FROM peoplePositions "
			+ " LEFT JOIN people ON people.id = peoplePositions.personId "
			+ "WHERE peoplePositions.positionId = :positionId "
			+ "AND peoplePositions.createdAt < :dtg "
			+ "ORDER BY peoplePositions.createdAt DESC";
	} else {
		sql = "/*positionFindPerson */ SELECT " + PersonDao.PERSON_FIELDS + " FROM peoplePositions "
			+ " LEFT JOIN people ON people.id = peoplePositions.personId "
			+ "WHERE peoplePositions.positionId = :positionId "
			+ "AND peoplePositions.createdAt < :dtg "
			+ "ORDER BY peoplePositions.createdAt DESC LIMIT 1";
	}
	Query<Person> query = dbHandle.createQuery(sql)
		.bind("positionId", b.getId())
		.bind("dtg", dtg)
		.map(new PersonMapper());
	List<Person> results = query.list();
	if (results.size() == 0) { return null; }
	return results.get(0);
}
 

import org.skife.jdbi.v2.Query; //导入方法依赖的package包/类
public Organization getOrganizationForPerson(int personId) {
	String sql;
	if (DaoUtils.isMsSql(dbHandle)) { 
		sql = "/* getOrganizationForPerson */ SELECT TOP(1) " + OrganizationDao.ORGANIZATION_FIELDS 
				+ "FROM organizations, positions, peoplePositions WHERE "
				+ "peoplePositions.personId = :personId AND peoplePositions.positionId = positions.id "
				+ "AND positions.organizationId = organizations.id "
				+ "ORDER BY peoplePositions.createdAt DESC";
	} else { 
		sql = "/* getOrganizationForPerson */ SELECT " + OrganizationDao.ORGANIZATION_FIELDS
				+ "FROM organizations, positions, peoplePositions WHERE "
				+ "peoplePositions.personId = :personId AND peoplePositions.positionId = positions.id "
				+ "AND positions.organizationId = organizations.id " 
				+ "ORDER BY peoplePositions.createdAt DESC LIMIT 1";
	}
	
	Query<Organization> query = dbHandle.createQuery(sql)
		.bind("personId", personId)
		.map(new OrganizationMapper());
	List<Organization> rs = query.list();
	if (rs.size() == 0) { return null; } 
	return rs.get(0);
}
 

import org.skife.jdbi.v2.Query; //导入方法依赖的package包/类
@Override
public List<AlarmDefinition> listAll() {
  try (Handle h = db.open()) {
    final String sql =
        "select * from alarm_definition where deleted_at is NULL order by created_at";

    final Query<AlarmDefinition> q =
        h.createQuery(sql).map(new AlarmDefinitionMapper());

    final List<AlarmDefinition> alarmDefs = q.list();
    for (final AlarmDefinition alarmDef : alarmDefs) {
      alarmDef.setSubExpressions(findSubExpressions(h, alarmDef.getId()));
    }
    return alarmDefs;
  }
}
 

import org.skife.jdbi.v2.Query; //导入方法依赖的package包/类
@Override
public List<String> listNotificationMethodTypes() {

  List<String>  notification_method_types = new ArrayList<String>();
  try (Handle h = db.open()) {

    String query = " SELECT name from notification_method_type";

    Query<Map<String, Object>> q  = h.createQuery(query);
    List<Map<String, Object>>  result = q.list();

    for (Map<String, Object> m : result) {
      notification_method_types.add((String)m.get("name"));
    }
    return notification_method_types;

  }
}
 

import org.skife.jdbi.v2.Query; //导入方法依赖的package包/类
@Override
public Report getById(int id) {
	Query<Report> query = dbHandle.createQuery("/* getReportById */ SELECT " + REPORT_FIELDS + ", " + PersonDao.PERSON_FIELDS
			+ "FROM reports, people "
			+ "WHERE reports.id = :id "
			+ "AND reports.authorId = people.id")
			.bind("id", id)
			.map(new ReportMapper());
	List<Report> results = query.list();
	if (results.size() == 0) { return null; }
	Report r = results.get(0);
	return r;
}
 

import org.skife.jdbi.v2.Query; //导入方法依赖的package包/类
@Override
public Location getById(@Bind("id") int id) { 
	Query<Location> query = dbHandle.createQuery("/* getLocationById */ SELECT * from locations where id = :id")
			.bind("id", id)
			.map(new LocationMapper());
		List<Location> results = query.list();
		if (results.size() == 0) { return null; } 
		return results.get(0);
}
 

import org.skife.jdbi.v2.Query; //导入方法依赖的package包/类
/**
 * Returns all approval actions ever taken for a particular report. 
 * Ordered by their date ascending (earliest to most recent). 
 */
public List<ApprovalAction> getActionsForReport(int reportId) {
	Query<ApprovalAction> query = dbHandle.createQuery("/* getReportApprovals */ SELECT * FROM approvalActions " 
			+ "WHERE reportId = :reportId ORDER BY createdAt ASC")
		.bind("reportId", reportId)
		.map(new ApprovalActionMapper());
	return query.list();
}
 

import org.skife.jdbi.v2.Query; //导入方法依赖的package包/类
@Override
public Poam getById(int id) { 
	Query<Poam> query = dbHandle.createQuery("/* getPoamById */ SELECT * from poams where id = :id")
		.bind("id",id)
		.map(new PoamMapper());
	List<Poam> results = query.list();
	if (results.size() == 0) { return null; } 
	return results.get(0);
}
 

import org.skife.jdbi.v2.Query; //导入方法依赖的package包/类
@Override
public ApprovalStep getById(int id) {
	Query<ApprovalStep> query = dbHandle.createQuery("/* getApprovalStepById */ SELECT * from approvalSteps where id = :id")
			.bind("id", id)
			.map(new ApprovalStepMapper());
	List<ApprovalStep> results = query.list();
	if (results.size() == 0) { return null; }
	return results.get(0);
}
 

import org.skife.jdbi.v2.Query; //导入方法依赖的package包/类
public Position getById(int id) { 
	Query<Position> query = dbHandle.createQuery("/* positionGetById */ SELECT " + POSITIONS_FIELDS + ", " + PersonDao.PERSON_FIELDS
			+ "FROM positions LEFT JOIN people ON positions.currentPersonId = people.id "
			+ "WHERE positions.id = :id")
		.bind("id", id)
		.map(new PositionMapper());
	List<Position> positions = query.list();
	if (positions.size() == 0) { return null; } 
	return positions.get(0);
}
 

import org.skife.jdbi.v2.Query; //导入方法依赖的package包/类
public List<Position> getAssociatedPositions(Position p) {
	Query<Position> query = dbHandle.createQuery("/* getAssociatedPositions */ SELECT " 
			+ POSITIONS_FIELDS + ", people.* FROM positions "
			+ "LEFT JOIN people ON positions.currentPersonId = people.id "
			+ "WHERE positions.id IN "
			+ "(SELECT positionId_a FROM positionRelationships WHERE positionId_b = :positionId AND deleted = :deleted) "
			+ "OR positions.id IN (SELECT positionId_b FROM positionRelationships WHERE positionId_a = :positionId AND deleted = :deleted)")
		.bind("positionId", p.getId())
		.bind("deleted", false)
		.map(new PositionMapper());
	return query.list();
}
 

import org.skife.jdbi.v2.Query; //导入方法依赖的package包/类
public Person getById(int id) { 
	Query<Person> query = dbHandle.createQuery("/* personGetById */ SELECT " + PERSON_FIELDS + " FROM people WHERE id = :id")
			.bind("id",  id)
			.map(new PersonMapper());
	List<Person> rs = query.list();
	if (rs.size() == 0) { return null; } 
	return rs.get(0);
}
 

import org.skife.jdbi.v2.Query; //导入方法依赖的package包/类
public Organization getById(int id) { 
	Query<Organization> query = dbHandle.createQuery(
			"/* getOrgById */ SELECT " + ORGANIZATION_FIELDS + " from organizations where id = :id")
		.bind("id",id)
		.map(new OrganizationMapper());
	List<Organization> results = query.list();
	return (results.size() == 0) ? null : results.get(0);
}
 

import org.skife.jdbi.v2.Query; //导入方法依赖的package包/类
public static ReportList fromQuery(Query<Report> query, int pageNum, int pageSize) {
	ReportList results = new ReportList(pageNum, pageSize, query.list()); 
	if (results.getList().size() == 0) { 
		results.setTotalCount(0);
	} else {
		//This value gets set by the ReportMapper on each row. 
		results.setTotalCount((Integer) query.getContext().getAttribute("totalCount"));
	}
	return results;
}
 

import org.skife.jdbi.v2.Query; //导入方法依赖的package包/类
private void getAlarmedMetrics(Handle h, final Map<String, Alarm> alarmMap,
    final Map<String, String> tenantIdMap, final String additionalWhereClause, String ... params) {
  final String baseSql = "select a.id, md.name, group_concat(mdim.name, '=', mdim.value order by mdim.name) as dimensions "
      + "from metric_definition as md "
      + "inner join metric_definition_dimensions as mdd on md.id = mdd.metric_definition_id "
      + "inner join alarm_metric as am on mdd.id = am.metric_definition_dimensions_id "
      + "inner join alarm as a on am.alarm_id = a.id "
      + "left join metric_dimension as mdim on mdim.dimension_set_id = mdd.metric_dimension_set_id "
      + "where %s "
      + "group by a.id, md.name, mdim.dimension_set_id "
      + "order by dimensions";
  final String sql = String.format(baseSql, additionalWhereClause);
  final Query<Map<String, Object>> query = h.createQuery(sql);
  addQueryParameters(query, params);
  final List<Map<String, Object>> metricRows = query.list();
  for (final Map<String, Object> row : metricRows) {
    final String alarmId = getString(row, "id");
    final Alarm alarm = alarmMap.get(alarmId);
    // This shouldn't happen but it is possible an Alarm gets created after the AlarmDefinition is
    // marked deleted and any existing alarms are deleted but before the Threshold Engine gets the
    // AlarmDefinitionDeleted message
    if (alarm == null) {
      continue;
    }
    final MetricDefinition md = createMetricDefinitionFromRow(row);
    alarm.addAlarmedMetric(new MetricDefinitionAndTenantId(md, tenantIdMap.get(alarmId)));
  }
}