Java Query类代码示例

import org.skife.jdbi.v2.Query; //导入依赖的package包/类
@Override
public ReportList getAll(int pageNum, int pageSize) {
	String sql;
	if (DaoUtils.isMsSql(dbHandle)) {
		sql = "/* getAllReports */ SELECT " + REPORT_FIELDS + ", " + PersonDao.PERSON_FIELDS
				+ ", COUNT(*) OVER() AS totalCount FROM reports, people "
				+ "WHERE reports.authorId = people.id "
				+ "ORDER BY reports.createdAt DESC OFFSET :offset ROWS FETCH NEXT :limit ROWS ONLY";
	} else {
		sql = "/* getAllReports */ SELECT " + REPORT_FIELDS + ", " + PersonDao.PERSON_FIELDS
				+ "FROM reports, people "
				+ "WHERE reports.authorId = people.id "
				+ "ORDER BY reports.createdAt DESC LIMIT :limit OFFSET :offset";
	}
	Query<Report> query = dbHandle.createQuery(sql)
		.bind("limit", pageSize)
		.bind("offset", pageSize * pageNum)
		.map(new ReportMapper());
	return ReportList.fromQuery(query, pageNum, pageSize);
}
 

import org.skife.jdbi.v2.Query; //导入依赖的package包/类
public LocationList getAll(int pageNum, int pageSize) { 
	String sql;
	if (DaoUtils.isMsSql(dbHandle)) { 
		sql = "/* getAllLocations */ SELECT locations.*, COUNT(*) OVER() AS totalCount "
				+ "FROM locations ORDER BY createdAt DESC "
				+ "OFFSET :offset ROWS FETCH NEXT :limit ROWS ONLY";
	} else {
		sql = "/* getAllLocations */ SELECT * from locations "
				+ "ORDER BY createdAt ASC LIMIT :limit OFFSET :offset";
	}
	
	Query<Location> query = dbHandle.createQuery(sql)
			.bind("limit", pageSize)
			.bind("offset", pageSize * pageNum)
			.map(new LocationMapper());
	return LocationList.fromQuery(query, pageNum, pageSize);
}
 

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 LocationList runSearch(LocationSearchQuery query, Handle dbHandle) {
	LocationList result = new LocationList();
	result.setPageNum(query.getPageNum());
	result.setPageSize(query.getPageSize());
	if (query.getText() == null || query.getText().trim().length() == 0) {
		return result;
	}
	
	Query<Location> sqlQuery = dbHandle.createQuery("/* MssqlLocationSearch */ SELECT *, count(*) over() as totalCount "
			+ "FROM locations WHERE CONTAINS (name, :name) "
			+ "ORDER BY name ASC OFFSET :offset ROWS FETCH NEXT :limit ROWS ONLY")
		.bind("name", Utils.getSqlServerFullTextQuery(query.getText()))
		.bind("offset", query.getPageSize() * query.getPageNum())
		.bind("limit", query.getPageSize())
		.map(new LocationMapper());
	
	result.setList(sqlQuery.list());
	if (result.getList().size() > 0) { 
		result.setTotalCount((Integer) sqlQuery.getContext().getAttribute("totalCount"));
	} else { 
		result.setTotalCount(0);
	}
	return result;
}
 

import org.skife.jdbi.v2.Query; //导入依赖的package包/类
@Override
public Map<DetailRace, Map<String, Double>> getRaceAlleleFrequencyMap() {
	try (Handle handle = dbi.open()) {
		Query<Map<String, Object>> query = handle.createQuery(
				"select brf.locus, dr.detail_race, brf.allele, ifnull(drf.frequency, brf.frequency) freq"
				//+ " ifnull(drf.detail_race, 'broad:' || brf.detail_race)"
				+ " from detail_race dr"
				+ " join race_freq brf on dr.broad_race = brf.detail_race"
				+ " left join race_freq drf on dr.detail_race = drf.detail_race"
				+ "     and brf.locus = drf.locus"
				+ "     and brf.allele = drf.allele"
				+ " where brf.locus = 'HLA-DPB1'");
		return StreamSupport.stream(query.spliterator(), false).collect(
				Collectors.groupingBy(
						m -> DetailRace.valueOf((String)m.get("detail_race")), 
		        		Collectors.toMap(
		        				m -> (String)m.get("locus") + "*" + (String)m.get("allele"), 
		        				m -> (Double)m.get("freq"))));
	}
}
 

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包/类
public CustomDataSource getDatabase(String project, String schema) {
    try (Handle handle = dbi.open()) {
        Query<Map<String, Object>> bind = handle.createQuery("SELECT type, options FROM custom_data_source WHERE project = :project AND lower(schema_name) = :schema_name")
                .bind("project", project)
                .bind("schema_name", schema.toLowerCase(Locale.ENGLISH));

        CustomDataSource first = bind.map((index, r, ctx) ->
                new CustomDataSource(r.getString(1), schema, JsonHelper.read(r.getString(2), JDBCSchemaConfig.class))).first();

        if (first == null) {
            throw new RakamException(NOT_FOUND);
        }

        return first;
    }
}
 

import org.skife.jdbi.v2.Query; //导入依赖的package包/类
public RemoteTable getFile(String project, String tableName) {
    try (Handle handle = dbi.open()) {
        Query<Map<String, Object>> bind = handle.createQuery("SELECT options FROM custom_file_source WHERE project = :project AND table_name = :table_name")
                .bind("project", project)
                .bind("table_name", tableName);

        RemoteTable first = bind.map((index, r, ctx) -> {
            return JsonHelper.read(r.getString(1), RemoteTable.class);
        }).first();

        if (first == null) {
            throw new RakamException(NOT_FOUND);
        }

        return first;
    }
}
 

import org.skife.jdbi.v2.Query; //导入依赖的package包/类
private List<ScheduledEmailTask> list(Handle handle, String predicate, Consumer<Query> queryConsumer) {
    Query<Map<String, Object>> query = handle.createQuery("SELECT id, name, date_interval, hour_of_day, type, type_id, emails, enabled, last_executed_at, user_id, project_id " +
            "FROM scheduled_email WHERE " + predicate);
    queryConsumer.accept(query);

    return query
            .map((index, r, ctx) -> {
                return new ScheduledEmailTask(
                        r.getInt(1), r.getString(2),
                        r.getString(3), r.getInt(4),
                        TaskType.valueOf(r.getString(5)),
                        r.getInt(6), Arrays.asList((String[]) r.getArray(7).getArray()),
                        r.getBoolean(8), r.getTimestamp(9) == null ? null : r.getTimestamp(9).toInstant(),
                        r.getInt(10), r.getInt(11));
            }).list();
}
 

import org.skife.jdbi.v2.Query; //导入依赖的package包/类
static void bindDimensionsToQuery(Query<?> query, Map<String, String> dimensions) {
  if (dimensions != null) {
    int i = 0;
    for (Iterator<Map.Entry<String, String>> it = dimensions.entrySet().iterator(); it.hasNext(); i++) {
      Map.Entry<String, String> entry = it.next();
      query.bind("dname" + i, entry.getKey());
      if (!Strings.isNullOrEmpty(entry.getValue())) {
        List<String> values = BAR_SPLITTER.splitToList(entry.getValue());
        if (values.size() > 1) {
          for (int j = 0; j < values.size(); j++) {
            query.bind("dvalue" + i + '_' + j, values.get(j));
          }
        }
        else {
          query.bind("dvalue" + i, entry.getValue());
        }
      }
    }
  }
}
 

import org.skife.jdbi.v2.Query; //导入依赖的package包/类
public static void bindDimensionsToQuery(
    Query<?> query,
    Map<String, String> dimensions) {

  if (dimensions != null) {
    int i = 0;
    for (Iterator<Map.Entry<String, String>> it = dimensions.entrySet().iterator(); it.hasNext(); i++) {
      Map.Entry<String, String> entry = it.next();
      query.bind("dname" + i, entry.getKey());
      if (!Strings.isNullOrEmpty(entry.getValue())) {
        List<String> values = Splitter.on('|').splitToList(entry.getValue());
        int j = 0;
        for (String value : values) {
          query.bind("dvalue" + i + '_' + j, value);
          j++;
        }
      }
    }
  }
}
 

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 AlarmDefinition findById(String tenantId, String alarmDefId) {

  try (Handle h = db.open()) {
    String query = "SELECT alarm_definition.id, alarm_definition.tenant_id, alarm_definition.name, alarm_definition.description, "
        + "alarm_definition.expression, alarm_definition.severity, alarm_definition.match_by, alarm_definition.actions_enabled, "
        +" alarm_definition.created_at, alarm_definition.updated_at, alarm_definition.deleted_at, "
        + "GROUP_CONCAT(alarm_action.action_id) AS notificationIds,group_concat(alarm_action.alarm_state) AS states "
        + "FROM alarm_definition LEFT OUTER JOIN alarm_action ON alarm_definition.id=alarm_action.alarm_definition_id "
        + " WHERE alarm_definition.tenant_id=:tenantId AND alarm_definition.id=:alarmDefId AND alarm_definition.deleted_at "
        + " IS NULL GROUP BY alarm_definition.id";

    Query<?> q = h.createQuery(query);
    q.bind("tenantId", tenantId);
    q.bind("alarmDefId", alarmDefId);

    q.registerMapper(new AlarmDefinitionMapper());
    q = q.mapTo(AlarmDefinition.class);
    AlarmDefinition alarmDefinition = (AlarmDefinition) q.first();
   if(alarmDefinition == null)
   {
     throw new EntityNotFoundException("No alarm definition exists for %s", alarmDefId);
   }
    return alarmDefinition;
  }
}
 

import org.skife.jdbi.v2.Query; //导入依赖的package包/类
@Override
public Map<Table, Long> getUsages(Iterable<Table> tables)
{
    try (Handle handle = dbi.open()) {
        Query<Map<String, Object>> query = handle.createQuery(
                "SELECT connector_id AS connectorId, schema_ AS \"schema\", table_ AS \"table\", COUNT(*) AS count " +
                        "FROM jobs j " +
                        "LEFT OUTER JOIN job_tables jt ON j.id = jt.job_id " +
                        "LEFT OUTER JOIN tables t ON jt.table_id = t.id " +
                        "WHERE " + Util.getQueryFinishedCondition(dbType) + " " +
                        "AND (" + Util.getTableCondition(Lists.newArrayList(tables)) + ") " +
                        "GROUP BY connector_id, schema_, table_ " +
                        "ORDER BY count DESC")
                .bind("day_interval", 1);

        return query.
                map(RosettaResultSetMapperFactory.mapperFor(JobUsageCountRow.class)).
                fold(new HashMap<Table, Long>(), new JobUsageCountRow.CountFolder());
    } catch (Exception e) {
        log.error("getTables caught exception", e);
        return Collections.emptyMap();
    }
}