private Select( Connection connection, QueryBuilder<ResultQuery<? extends R>> queryBuilder, RecordMapper<? super R, ? extends T> recordMapper ) { super( subscriber -> { try (ResultQuery<? extends R> query = queryBuilder.build(connection)) { Cursor<? extends R> cursor = query.fetchLazy(); setupUnsubscription(subscriber, query, cursor); log.debug("Select setProducer for {}", query); subscriber.setProducer(new SelectProducer<>( subscriber, query, cursor, recordMapper )); } catch (Throwable t) { handleException(t, subscriber); } } ); }
private void cleanupServiceEventTable(Date cutoff) { ResultQuery<Record1<Long>> ids = create() .select(SERVICE_EVENT.ID) .from(SERVICE_EVENT) .where(SERVICE_EVENT.CREATED.lt(cutoff)) .and(SERVICE_EVENT.STATE.eq(CommonStatesConstants.CREATED)) .limit(QUERY_LIMIT_ROWS.getValue()); List<Long> toDelete = null; int rowsDeleted = 0; while ((toDelete = ids.fetch().into(Long.class)).size() > 0) { rowsDeleted += create().delete(SERVICE_EVENT) .where(SERVICE_EVENT.ID.in(toDelete)).execute(); } if (rowsDeleted > 0) { log.info("[Rows Deleted] service_event={}", rowsDeleted); } }
@SuppressWarnings(value={"unused"}) private int select (String tableName, List<String> rows, List<String> columns) { Timer timer = Timer.start ("flat-dataset"); Table<Record> table = tableByName (tableName); List<Field<String>> selectFields = createFieldList (columns); ResultQuery<Record> query; List<String> rowKeys = getRowKeys (tableName, ID_FIELD_NAME); if (rowKeys.size () == rows.size ()) query = queryAllRows (selectFields, table); else { query = queryINRows (tableName, selectFields, rows); } int count = read (query); timer.read (); return count; }
private List<String> fetchList (ResultQuery<Record1<String>> query, String columnField) { Cursor<Record1<String>> cursor = null; List<String> result = new ArrayList<String> (1000); try { cursor = query.fetchLazy (); // Cursor has similar methods as Iterator<R> while (cursor.hasNext ()) { Record record = cursor.fetchOne (); String sValue = (String) record.getValue (columnField); result.add (sValue); } } finally { if (cursor != null) { cursor.close (); } } return result; }
@Synchronized @SuppressWarnings("unused") private List<String> getRowKeys (String tableName, String fieldRowIdName) { Timer timer = Timer.start ("get-row-keys"); if (this.rowKeys == null) { Table<Record> table = tableByName (tableName); Field<String> fieldRowId = fieldByName (String.class, fieldRowIdName); // get rows Timer timer2 = Timer.start ("fetch-array"); ResultQuery<Record1<String>> queryRowKeys = context.select (fieldRowId).from (table).getQuery (); String[] arows = queryRowKeys.fetchArray (fieldRowId); timer2.read (); // List<String> rows = Arrays.asList (arows); timer2 = Timer.start ("copy-array-to-list"); // this.rowKeys = new ArrayList<String>(arows.length); // Collections.addAll (this.rowKeys, arows); // this.rowKeys = Arrays.asList (arows); this.rowKeys = fetchList (queryRowKeys, ID_FIELD_NAME); timer2.read (); log.debug ("rows.size(): " + this.rowKeys.size ()); } timer.read (); return this.rowKeys; }
@SuppressWarnings(value={"unused"}) private int select(String tableName, List<String> rows, List<String> columns){ Timer timer = Timer.start ("flat-dataset"); Table<Record> table = tableByName (tableName); List<Field<String>> selectFields = createFieldList (columns); ResultQuery<Record> query; List<String> rowKeys = getRowKeys (tableName, ID_FIELD_NAME); if(rowKeys.size ()==rows.size ()) query = queryAllRows (selectFields, table); else{ query = queryINRows (tableName, selectFields, rows); } int count = read(query); timer.read (); return count; }
@SuppressWarnings(value={"unused"}) private int select (String tableName, List<String> rows, List<String> columns) { Timer timer = Timer.start ("flat-dataset"); Table<Record> table = tableByName (tableName); Field<String> fieldRowId = fieldByName (String.class, ID_FIELD_NAME); List<Field<String>> selectFields = createFieldList (columns); ResultQuery<Record> query; List<String> rowKeys = getRowKeys (tableName, ID_FIELD_NAME); if (rowKeys.size () == rows.size ()) query = queryAllRows (selectFields, table); else { query = queryINRows (tableName, selectFields, rows); } int count = read (query); timer.read (); return count; }
@Synchronized private List<String> getRowKeys(String tableName, String fieldRowIdName){ if(rowCasche.containsKey (tableName)){ return rowCasche.get (tableName); }else{ Table<Record> table = tableByName (tableName); Field<String> fieldRowId = fieldByName (String.class, fieldRowIdName); //get rows Timer timer = Timer.start ("get-row-keys"); ResultQuery<Record1<String>> queryRowKeys =context.select(fieldRowId).from(table).orderBy (fieldRowId).getQuery (); String[] arows = queryRowKeys.fetchArray (fieldRowId); timer.read ("fetch-array"); // List<String> rows = Arrays.asList (arows); List<String> rows = new ArrayList<String>(arows.length); Collections.addAll (rows, arows); timer.read ("collection"); if(log.isDebugEnabled ()){ timer.read(); log.debug ("rows.size(): " + rows.size ()); } rowCasche.put (tableName, rows); return rows; } }
private List<String> getColumnKeys(String tableName, String fieldRowIdName){ //get columns Table<Record> table = tableByName (tableName); Timer timer = Timer.start ("get-column-keys"); ResultQuery<Record> queryColumnKeys =context.selectFrom(table).limit (1) .getQuery (); Record recordColumnKeys = queryColumnKeys.fetchOne (); List<String> columns = new ArrayList<String>(); for(Field<?> field : recordColumnKeys.fields()) if(!field.getName ().equals (ID_FIELD_NAME)) columns.add (field.getName ()); if(log.isDebugEnabled ()){ timer.read(); log.debug ("columns.size(): " + columns.size ()); log.debug ("columns: " + columns); } return columns; }
@Override public InputStream getAsStream (Dimension dimension) { // SelectQuery<Tables> query = context.selectQuery() Table<?> table = tableByName (TABLE_NAME_PREFIX+this.platformId ()); Field<String> probeId = fieldByName (String.class, "PROBESET_ID"); InputStream input=null; ResultQuery<?> query = context.selectFrom(table) .where(probeId.in (dimension.keys ())); log.debug(query.toString ()); String csv = query.fetch().formatCSV('\t'); try { input = new ByteArrayInputStream (csv.getBytes ("UTF-8")); } catch (UnsupportedEncodingException e) { // TODO Auto-generated catch block e.printStackTrace(); } return input; }
public SelectProducer( Subscriber<? super T> subscriber, ResultQuery<? extends R> query, Cursor<? extends R> cursor, RecordMapper<? super R, ? extends T> recordMapper ) { this.subscriber = subscriber; this.query = query; this.cursor = cursor; this.recordMapper = recordMapper; }
private static <R extends Record, T> void setupUnsubscription( Subscriber<? super T> subscriber, ResultQuery<? extends R> query, Cursor<? extends R> cursor ) { subscriber.add( Subscriptions.create( () -> { closeQuietly(cursor); closeQuietly(query); } ) ); }
private ResultQuery<Record> limit(Integer startPos, Integer maxRows, SelectSeekStep1<Record, Timestamp> selectSeekStep) { if (maxRows != null) { if (startPos == null) { startPos = 0; } return selectSeekStep.limit(startPos, maxRows); } else { return selectSeekStep; } }
protected ResultQuery<?> toQuery(Class<?> clz, Map<Object, Object> values) { String type = schemaFactory.getSchemaName(clz); if (type == null) { throw new IllegalArgumentException("Failed to find type of class [" + clz + "]"); } Class<UpdatableRecord<?>> recordClass = JooqUtils.getRecordClass(schemaFactory, clz); Table<?> table = JooqUtils.getTableFromRecordClass(recordClass); return create().selectFrom(table).where(JooqUtils.toConditions(metaDataManager, type, values)); }
protected ResultQuery<?> toQuery(Class<?> clz, Map<Object, Object> values) { String type = schemaFactory.getSchemaName(clz); if ( type == null ) { throw new IllegalArgumentException("Failed to find type of class [" + clz + "]"); } Class<UpdatableRecord<?>> recordClass = JooqUtils.getRecordClass(schemaFactory, clz); Table<?> table = JooqUtils.getTableFromRecordClass(recordClass); return create() .selectFrom(table) .where(JooqUtils.toConditions(metaDataManager, type, values)); }
@Test @Ignore @SuppressWarnings("unused") public void testSimpleSelectAll () { Table<Record> table = tableByName (tsvFileName); long startTime = System.nanoTime (); int count = 0; ResultQuery<Record> query = context.selectFrom (table); // log.debug ("PresetValuesFlatTable sql:"+query.getSQL ()); Cursor<Record> cursor = null; try { cursor = query.fetchLazy (); // Cursor has similar methods as Iterator<R> while (cursor.hasNext ()) { Record record = cursor.fetchOne (); for (Field<?> columnField : record.fields ()) { String value = (String) record.getValue (columnField); count++; } } } finally { if (cursor != null) { cursor.close (); } } long endTime = System.nanoTime (); long duration = endTime - startTime; log.debug ("flat-count:" + count + ", duration: " + duration); }
private ResultQuery<Record> queryINRows (String tableName, List<Field<String>> selectFields, List<String> rows) { Table<Record> table = tableByName (tableName); Field<String> fieldRowId = fieldByName (String.class, ID_FIELD_NAME); return context.select (selectFields) .from (table).where (fieldRowId.in (rows)) .getQuery (); }
private ResultQuery<Record> queryRowByID (String tableName, List<Field<String>> selectFields, String row) { Table<Record> table = tableByName (tableName); Field<String> fieldRowId = fieldByName (String.class, ID_FIELD_NAME); return context.select (selectFields) .from (table).where (fieldRowId.eq (row)) .getQuery (); }
/** * @param tableName * @param rows * @param columns * @return */ @SuppressWarnings(value={"all"}) private int select2 (String tableName, List<String> rows, List<String> columns) { Timer timer = Timer.start ("flat-dataset"); Table<Record> table = tableByName (tableName); Field<String> fieldRowId = fieldByName (String.class, ID_FIELD_NAME); List<Field<String>> selectFields = createFieldList (columns); int totalCellCount = 0; ResultQuery<Record> query; List<String> rowKeys = getRowKeys (tableName, ID_FIELD_NAME); if(rowKeys.size ()==rows.size ()){ // TODO:hardcoded data size for test only // if (90797 == rows.size ()){ query = queryAllRows (selectFields, table); totalCellCount = read (query); }else { final int FETCH_LIMIT = 1; List<String> batchRows = new ArrayList<String> (FETCH_LIMIT); for (String row : rows) { batchRows.add (row); if (batchRows.size () % FETCH_LIMIT == 0) { if (FETCH_LIMIT == 1) query = queryRowByID (tableName, selectFields, row); else query = queryINRows (tableName, selectFields, rows); totalCellCount += read (query); batchRows = new ArrayList<String> (FETCH_LIMIT); // reset } } if (batchRows.size () > 0) { query = queryINRows (tableName, selectFields, rows); totalCellCount += read (query); } } timer.read (); return totalCellCount; }
private int read (ResultQuery<Record> query) { Cursor<Record> cursor = null; int count = 0; try { cursor = query.fetchLazy (); // Cursor has similar methods as Iterator<R> while (cursor.hasNext ()) { Record record = cursor.fetchOne (); for (Field<?> columnField : record.fields ()) { String sValue = (String) record.getValue (columnField); @SuppressWarnings("unused") double value = NaN; if ("Inf".equalsIgnoreCase (sValue)) value = POSITIVE_INFINITY; else if ("-Inf".equalsIgnoreCase (sValue)) value = NEGATIVE_INFINITY; else if ("NA".equalsIgnoreCase (sValue)) value = NaN; else if ("null".equalsIgnoreCase (sValue)) value = NaN; else if (sValue == null) value = NaN; else value = Double.parseDouble (sValue); count++; } } } finally { if (cursor != null) { cursor.close (); } } return count; }
private List<String> getColumnKeys (String tableName, String fieldRowIdName) { // get columns Table<Record> table = tableByName (tableName); Timer timer = Timer.start ("get-column-keys"); ResultQuery<Record> queryColumnKeys = context.selectFrom (table).limit (1).getQuery (); Record recordColumnKeys = queryColumnKeys.fetchOne (); List<String> columns = new ArrayList<String> (); for (Field<?> field : recordColumnKeys.fields ()) if (!field.getName ().equals (ID_FIELD_NAME)) columns.add (field.getName ()); timer.read (); log.debug ("columns.size(): " + columns.size ()); log.debug ("columns: " + columns); return columns; }
@Test @Ignore @SuppressWarnings("unused") public void testSimpleSelectAll () { String tsvFileName="LGG.AgilentG4502A_07_3.Level_2.tsv"; Table<Record> table = tableByName (tsvFileName); long startTime = System.nanoTime(); int count=0; ResultQuery<Record> query =context.selectFrom (table); // log.debug ("PresetValuesFlatTable sql:"+query.getSQL ()); Cursor<Record> cursor = null; try { cursor = query.fetchLazy(); // Cursor has similar methods as Iterator<R> while (cursor.hasNext()) { Record record = cursor.fetchOne(); for(Field<?> columnField: record.fields()){ String value = (String)record.getValue (columnField); count++; } } }finally { if (cursor != null) {cursor.close();} } long endTime = System.nanoTime(); long duration = endTime - startTime; log.debug ("flat-count:"+count+", duration: "+duration); }
private ResultQuery<Record> queryINRows(String tableName, List<Field<String>> selectFields, List<String> rows){ Table<Record> table = tableByName (tableName); Field<String> fieldRowId = fieldByName (String.class, ID_FIELD_NAME); return context.select(selectFields) .from(table).where (fieldRowId.in (rows)) .getQuery (); }
private ResultQuery<Record> queryRowByID(String tableName, List<Field<String>> selectFields, String row){ Table<Record> table = tableByName (tableName); Field<String> fieldRowId = fieldByName (String.class, ID_FIELD_NAME); return context.select(selectFields) .from(table).where (fieldRowId.eq (row)) .getQuery (); }
/** * @param tableName * @param rows * @param columns * @return */ @SuppressWarnings(value={"all"}) private int select2(String tableName, List<String> rows, List<String> columns, boolean bTimer){ Timer timer = Timer.start ("flat-dataset"); Table<Record> table = tableByName (tableName); Field<String> fieldRowId = fieldByName (String.class, ID_FIELD_NAME); List<Field<String>> selectFields = createFieldList (columns); int totalCellCount=0; ResultQuery<Record> query; List<String> rowKeys = getRowKeys (tableName, ID_FIELD_NAME); if(rowKeys.size ()==rows.size ()) query = queryAllRows (selectFields, table); else{ final int FETCH_LIMIT=1; List<String> batchRows = new ArrayList<String>(FETCH_LIMIT); for(String row : rows){ batchRows.add(row); if(batchRows.size () % FETCH_LIMIT == 0){ if(FETCH_LIMIT==1) query = queryRowByID (tableName, selectFields, row); else query = queryINRows (tableName, selectFields, rows); totalCellCount += read(query); batchRows = new ArrayList<String>(FETCH_LIMIT); //reset } } if(batchRows.size ()>0){ query = queryINRows (tableName, selectFields, rows); totalCellCount += read(query); } } if(bTimer) timer.read (); return totalCellCount; }
private int read(ResultQuery<Record> query){ Cursor<Record> cursor = null; int count=0; try { cursor = query.fetchLazy(); // Cursor has similar methods as Iterator<R> while (cursor.hasNext()) { Record record = cursor.fetchOne(); for(Field<?> columnField: record.fields()){ String sValue = (String)record.getValue (columnField); @SuppressWarnings("unused") double value=NaN; if ("Inf".equalsIgnoreCase (sValue)) value = POSITIVE_INFINITY; else if ("-Inf".equalsIgnoreCase (sValue)) value = NEGATIVE_INFINITY; else if ("NA".equalsIgnoreCase (sValue)) value = NaN; else if ("null".equalsIgnoreCase (sValue)) value = NaN; else if (sValue==null) value = NaN; else value = Double.parseDouble (sValue); count++; } } }finally { if (cursor != null) {cursor.close();} } return count; }
private List<String> getRowKeys(String tableName, String fieldRowIdName){ Table<Record> table = tableByName (tableName); Field<String> fieldRowId = fieldByName (String.class, fieldRowIdName); //get rows Timer timer = Timer.start ("get-row-keys"); ResultQuery<Record1<String>> queryRowKeys =context.select(fieldRowId).from(table).getQuery (); String[] arows = queryRowKeys.fetchArray (fieldRowId); // List<String> rows = Arrays.asList (arows); List<String> rows = new ArrayList<String>(arows.length); Collections.addAll (rows, arows); timer.read(); log.debug ("rows.size(): " + rows.size ()); return rows; }
private List<String> getColumnKeys(String tableName, String fieldRowIdName){ //get columns Table<Record> table = tableByName (tableName); Timer timer = Timer.start ("get-column-keys"); ResultQuery<Record> queryColumnKeys =context.selectFrom(table).limit (1) .getQuery (); Record recordColumnKeys = queryColumnKeys.fetchOne (); List<String> columns = new ArrayList<String>(); for(Field<?> field : recordColumnKeys.fields()) if(!field.getName ().equals (ID_FIELD_NAME)) columns.add (field.getName ()); timer.read(); log.debug ("columns.size(): " + columns.size ()); log.debug ("columns: " + columns); return columns; }
/** * @param tableName * @param rows * @param columns * @return */ @SuppressWarnings(value={"all"}) private int select2 (String tableName, List<String> rows, List<String> columns) { Timer timer = Timer.start ("flat-dataset"); Table<Record> table = tableByName (tableName); Field<String> fieldRowId = fieldByName (String.class, ID_FIELD_NAME); List<Field<String>> selectFields = createFieldList (columns); int totalCellCount = 0; ResultQuery<Record> query; // List<String> rowKeys = getRowKeys (tableName, ID_FIELD_NAME); // if(rowKeys.size ()==rows.size ()) // TODO:hardcoded data size for test only if (90797 == rows.size ()){ query = queryAllRows (selectFields, table); totalCellCount = read (query); }else { final int FETCH_LIMIT = 1; List<String> batchRows = new ArrayList<String> (FETCH_LIMIT); for (String row : rows) { batchRows.add (row); if (batchRows.size () % FETCH_LIMIT == 0) { if (FETCH_LIMIT == 1) query = queryRowByID (tableName, selectFields, row); else query = queryINRows (tableName, selectFields, rows); totalCellCount += read (query); batchRows = new ArrayList<String> (FETCH_LIMIT); // reset } } if (batchRows.size () > 0) { query = queryINRows (tableName, selectFields, rows); totalCellCount += read (query); } } timer.read (); return totalCellCount; }
@SuppressWarnings("unused") private int read (ResultQuery<Record> query) { Cursor<Record> cursor = null; int count = 0; try { cursor = query.fetchLazy (); // Cursor has similar methods as Iterator<R> while (cursor.hasNext ()) { Record record = cursor.fetchOne (); for (Field<?> columnField : record.fields ()) { String sValue = (String) record.getValue (columnField); double value = NaN; if ("Inf".equalsIgnoreCase (sValue)) value = POSITIVE_INFINITY; else if ("-Inf".equalsIgnoreCase (sValue)) value = NEGATIVE_INFINITY; else if ("NA".equalsIgnoreCase (sValue)) value = NaN; else if ("null".equalsIgnoreCase (sValue)) value = NaN; else if (sValue == null) value = NaN; else value = Double.parseDouble (sValue); count++; } } } finally { if (cursor != null) { cursor.close (); } } return count; }
public ResultQuery<Record> queryAllRows(List<Field<String>> selectFields, Table<Record> table){ Field<String> fieldRowId = fieldByName (String.class, ID_FIELD_NAME); return context.select(selectFields) .from(table) .orderBy (fieldRowId) .getQuery (); }
public ResultQuery<Record> queryINRows(List<Field<String>> selectFields, List<String> rows, Table<Record> table){ Field<String> fieldRowId = fieldByName (String.class, ID_FIELD_NAME); return context.select(selectFields) .from(table).where (fieldRowId.in (rows)) .orderBy (fieldRowId) .getQuery (); }
public ResultQuery<Record> queryRowByID(String tableName, List<Field<String>> selectFields, String row){ Table<Record> table = tableByName (tableName); Field<String> fieldRowId = fieldByName (String.class, ID_FIELD_NAME); return context.select(selectFields) .from(table).where (fieldRowId.eq (row)) .getQuery (); }