com.healthmarketscience.sqlbuilder.SelectQuery Java Examples

The following examples show how to use com.healthmarketscience.sqlbuilder.SelectQuery. You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example #1
Source File: JdbcReader.java    From deep-spark with Apache License 2.0 6 votes vote down vote up 
/**
 * Initialized the reader
 * 
 * @param p
 *            Spark partition.
 * @throws Exception
 */
public void init(Partition p) throws Exception {
    Class.forName(jdbcDeepJobConfig.getDriverClass());
    conn = DriverManager.getConnection(jdbcDeepJobConfig.getConnectionUrl(),
            jdbcDeepJobConfig.getUsername(),
            jdbcDeepJobConfig.getPassword());
    Statement statement = conn.createStatement();
    SelectQuery query = jdbcDeepJobConfig.getQuery();
    JdbcPartition jdbcPartition = (JdbcPartition)p;
    if(jdbcDeepJobConfig.getNumPartitions() > 1) {
        Column partitionKey = jdbcDeepJobConfig.getPartitionKey();
        query.getWhereClause().addCondition(BinaryCondition.lessThan(partitionKey, jdbcPartition.upper(), true))
                .addCondition(BinaryCondition.greaterThan(partitionKey, jdbcPartition.lower(), true));
    }
    resultSet = statement.executeQuery(query.toString());
    // Fetches first element
    this.hasNext = resultSet.next();
}
Example #2
Source File: JdbcNeo4JReaderTest.java    From deep-spark with Apache License 2.0 6 votes vote down vote up 
@Test
public void testNextRowOnInitWhenResultSetIsNotEmpty() throws Exception {

    // Stubbing external objects
    PowerMockito.mockStatic(DriverManager.class);

    when(config.getConnectionUrl()).thenReturn(WHATEVER_CONSTANT);
    when(config.getUsername()).thenReturn(WHATEVER_CONSTANT);
    when(config.getPassword()).thenReturn(WHATEVER_CONSTANT);
    when(config.getQuery()).thenReturn(mock(SelectQuery.class));
    when(DriverManager.getConnection(anyString(), anyString(), anyString())).thenReturn(conn);
    when(conn.createStatement()).thenReturn(statement);
    when(statement.executeQuery(anyString())).thenReturn(resultSet);
    when(resultSet.next()).thenReturn(true);

    // Setting up test scenario
    JdbcNeo4JReader reader = new JdbcNeo4JReader(config);

    reader.init(partition);

    // Assertions
    Class<?> clazz = JdbcNeo4JReader.class;
    Field hasNextField = clazz.getSuperclass().getDeclaredField("hasNext");
    hasNextField.setAccessible(true);
    assertEquals(hasNextField.get(reader), true, "hasNext field should be set to TRUE");
}
Example #3
Source File: JdbcNeo4JReaderTest.java    From deep-spark with Apache License 2.0 6 votes vote down vote up 
@Test
public void testNextRowOnInitWhenResultSetIsEmpty() throws Exception {

    // Stubbing external objects
    PowerMockito.mockStatic(DriverManager.class);

    when(config.getConnectionUrl()).thenReturn(WHATEVER_CONSTANT);
    when(config.getUsername()).thenReturn(WHATEVER_CONSTANT);
    when(config.getPassword()).thenReturn(WHATEVER_CONSTANT);
    when(config.getQuery()).thenReturn(mock(SelectQuery.class));
    when(DriverManager.getConnection(anyString(), anyString(), anyString())).thenReturn(conn);
    when(conn.createStatement()).thenReturn(statement);
    when(statement.executeQuery(anyString())).thenReturn(resultSet);
    when(resultSet.next()).thenReturn(false);

    // Setting up test scenario
    JdbcNeo4JReader reader = new JdbcNeo4JReader(config);

    reader.init(partition);

    // Assertions
    Class<?> clazz = JdbcNeo4JReader.class;
    Field hasNextField = clazz.getSuperclass().getDeclaredField("hasNext");
    hasNextField.setAccessible(true);
    assertEquals(hasNextField.get(reader), false, "hasNext field should be set to FALSE");
}
Example #4
Source File: JdbcReaderTest.java    From deep-spark with Apache License 2.0 6 votes vote down vote up 
@Test
public void testNoConditionsAddedIfNotPartitioning() throws Exception {
    PowerMockito.mockStatic(DriverManager.class);
    SelectQuery selectQuery = PowerMockito.mock(SelectQuery.class);
    ComboCondition comboCondition = PowerMockito.mock(ComboCondition.class);

    when(selectQuery.getWhereClause()).thenReturn(comboCondition);
    when(comboCondition.addCondition(any(Condition.class))).thenReturn(comboCondition);
    when(config.getDriverClass()).thenReturn(JDBC_CELL_EXTRACTOR_CLASSNAME_CONSTANT);
    when(config.getConnectionUrl()).thenReturn(WHATEVER_CONSTANT);
    when(config.getUsername()).thenReturn(WHATEVER_CONSTANT);
    when(config.getPassword()).thenReturn(WHATEVER_CONSTANT);
    when(config.getQuery()).thenReturn(selectQuery);
    when(DriverManager.getConnection(anyString(), anyString(), anyString())).thenReturn(conn);
    when(conn.createStatement()).thenReturn(statement);
    when(statement.executeQuery(anyString())).thenReturn(resultSet);
    when(resultSet.next()).thenReturn(true);

    JdbcReader reader = new JdbcReader(config);

    reader.init(partition);

    verify(comboCondition, times(0)).addCondition(any((BinaryCondition.class)));

}
Example #5
Source File: JdbcDeepJobConfig.java    From deep-spark with Apache License 2.0 6 votes vote down vote up 
/**
 * {@inheritDoc}
 */
@Override
public SelectQuery getQuery() {
    SelectQuery selectQuery = new SelectQuery();
    List<DbColumn> columns = dbTable.getColumns();
    if(!columns.isEmpty()) {
        selectQuery.addColumns(columns.toArray(new Column[columns.size()]));
    } else {
        selectQuery.addAllTableColumns(dbTable);
    }
    selectQuery.addFromTable(dbTable);
    if(sort != null) {
        selectQuery.addOrderings(sort);
    }
    applyFilters(selectQuery);
    query = selectQuery;
    return query;
}
Example #6
Source File: CustomSyntaxTest.java    From sqlbuilder with Apache License 2.0 6 votes vote down vote up 
public void testSQLServerTopClause()
{
  String selectQuery1 = new SelectQuery()
    .addColumns(_table1_col1)
    .addCustomization(new MssTopClause(10))
    .validate().toString();
  checkResult(selectQuery1,
              "SELECT TOP 10 t0.col1 FROM Schema1.Table1 t0");

  String selectQuery2 = new SelectQuery()
    .addColumns(_table1_col1)
    .setIsDistinct(true)
    .addCustomization(new MssTopClause(30, true))
    .validate().toString();
  checkResult(selectQuery2,
              "SELECT DISTINCT TOP 30 PERCENT t0.col1 FROM Schema1.Table1 t0");
}
Example #7
Source File: CustomSyntaxTest.java    From sqlbuilder with Apache License 2.0 5 votes vote down vote up 
public void testPostgresqlBinaryCondition()
{
  String selectQuery1 = new SelectQuery()
    .addColumns(_table1_col1)
    .addCondition(PgBinaryCondition.iLike(_table1_col1, "foo%")
                  .setLikeEscapeChar('\\'))
    .validate().toString();
  checkResult(selectQuery1,
              "SELECT t0.col1 FROM Schema1.Table1 t0 WHERE (t0.col1 ILIKE 'foo%' ESCAPE '\\')");
}
Example #8
Source File: CustomSyntaxTest.java    From sqlbuilder with Apache License 2.0 5 votes vote down vote up 
public void testOracleLimitClause()
{
  String selectQuery1 = new SelectQuery()
    .addColumns(_table1_col1)
    .addCondition(BinaryCondition.lessThan(OraObjects.ROWNUM, 100, false))
    .validate().toString();
  checkResult(selectQuery1,
              "SELECT t0.col1 FROM Schema1.Table1 t0 WHERE (ROWNUM < 100)");
}
Example #9
Source File: JdbcDeepJobConfig.java    From deep-spark with Apache License 2.0 5 votes vote down vote up 
private void applyFilters(SelectQuery query) {
    if(this.filters != null && this.filters.length > 0) {
        ComboCondition comboCondition = new ComboCondition(ComboCondition.Op.AND);
        if (filters.length > 0) {
            for(int i=0; i<filters.length; i++) {
                Filter filter = filters[i];
                FilterType filterType = filter.getFilterType();
                DbColumn filterColumn = new DbColumn(dbTable, filter.getField(), "",null,null);
                if(filterType.equals(FilterType.EQ)) {
                    comboCondition.addCondition(BinaryCondition.equalTo(filterColumn, filter.getValue()));
                } else if(filterType.equals(FilterType.GT)) {
                    comboCondition.addCondition(BinaryCondition.greaterThan(filterColumn, filter.getValue(), false));
                } else if(filterType.equals(FilterType.LT)) {
                    comboCondition.addCondition(BinaryCondition.lessThan(filterColumn, filter.getValue(), false));
                } else if(filterType.equals(FilterType.GTE)) {
                    comboCondition.addCondition(BinaryCondition.greaterThan(filterColumn, filter.getValue(), true));
                } else if(filterType.equals(FilterType.LTE)) {
                    comboCondition.addCondition(BinaryCondition.lessThan(filterColumn, filter.getValue(), true));
                } else if(filterType.equals(FilterType.NEQ)) {
                    comboCondition.addCondition(BinaryCondition.notEqualTo(filterColumn, filter.getValue()));
                } else if(filterType.equals(FilterType.IN)) {
                    ComboCondition comboConditionOR = new ComboCondition(ComboCondition.Op.OR);
                    String[] condicion =filter.getValue().toString().split(",");
                    for (int z=0; z < condicion.length ; z++) {
                        comboConditionOR.addCondition(BinaryCondition.equalTo(filterColumn, condicion[z]));
                    }
                    comboCondition.addCondition(comboConditionOR);
                }
                else {
                    throw new UnsupportedOperationException("Currently, the filter operation " + filterType + " is not supported");
                }
            }
        }
        query.addCondition(comboCondition);
    }
}
Example #10
Source File: JdbcReaderTest.java    From deep-spark with Apache License 2.0 5 votes vote down vote up 
@Test
public void testNextRowOnInitWhenResultSetIsEmpty() throws Exception {

    // Stubbing external objects
    PowerMockito.mockStatic(DriverManager.class);

    when(config.getDriverClass()).thenReturn(JDBC_CELL_EXTRACTOR_CLASSNAME_CONSTANT);
    when(config.getConnectionUrl()).thenReturn(WHATEVER_CONSTANT);
    when(config.getUsername()).thenReturn(WHATEVER_CONSTANT);
    when(config.getPassword()).thenReturn(WHATEVER_CONSTANT);
    when(config.getQuery()).thenReturn(mock(SelectQuery.class));
    when(DriverManager.getConnection(anyString(), anyString(), anyString())).thenReturn(conn);
    when(conn.createStatement()).thenReturn(statement);
    when(statement.executeQuery(anyString())).thenReturn(resultSet);
    when(resultSet.next()).thenReturn(false);

    // Setting up test scenario
    JdbcReader reader = new JdbcReader(config);

    reader.init(partition);

    // Assertions
    Class<?> clazz = JdbcReader.class;
    Field hasNextField = clazz.getDeclaredField("hasNext");
    hasNextField.setAccessible(true);
    assertEquals(hasNextField.get(reader), false, "hasNext field should be set to FALSE");
}
Example #11
Source File: JdbcReaderTest.java    From deep-spark with Apache License 2.0 5 votes vote down vote up 
@Test
public void testNextRowOnInitWhenResultSetIsNotEmpty() throws Exception {

    // Stubbing external objects
    PowerMockito.mockStatic(DriverManager.class);

    when(config.getDriverClass()).thenReturn(JDBC_CELL_EXTRACTOR_CLASSNAME_CONSTANT);
    when(config.getConnectionUrl()).thenReturn(WHATEVER_CONSTANT);
    when(config.getUsername()).thenReturn(WHATEVER_CONSTANT);
    when(config.getPassword()).thenReturn(WHATEVER_CONSTANT);
    when(config.getQuery()).thenReturn(mock(SelectQuery.class));
    when(DriverManager.getConnection(anyString(), anyString(), anyString())).thenReturn(conn);
    when(conn.createStatement()).thenReturn(statement);
    when(statement.executeQuery(anyString())).thenReturn(resultSet);
    when(resultSet.next()).thenReturn(true);

    // Setting up test scenario
    JdbcReader reader = new JdbcReader(config);

    reader.init(partition);

    // Assertions
    Class<?> clazz = JdbcReader.class;
    Field hasNextField = clazz.getDeclaredField("hasNext");
    hasNextField.setAccessible(true);
    assertEquals(hasNextField.get(reader), true, "hasNext field should be set to TRUE");
}
Example #12
Source File: JdbcReaderTest.java    From deep-spark with Apache License 2.0 5 votes vote down vote up 
@Test
public void testConditionIsAddedForPartitioning() throws Exception {

    PowerMockito.mockStatic(DriverManager.class);
    SelectQuery selectQuery = PowerMockito.mock(SelectQuery.class);
    ComboCondition comboCondition = PowerMockito.mock(ComboCondition.class);

    when(selectQuery.getWhereClause()).thenReturn(comboCondition);
    when(comboCondition.addCondition(any(Condition.class))).thenReturn(comboCondition);
    when(config.getDriverClass()).thenReturn(JDBC_CELL_EXTRACTOR_CLASSNAME_CONSTANT);
    when(config.getConnectionUrl()).thenReturn(WHATEVER_CONSTANT);
    when(config.getUsername()).thenReturn(WHATEVER_CONSTANT);
    when(config.getPassword()).thenReturn(WHATEVER_CONSTANT);
    when(config.getPartitionKey()).thenReturn(PowerMockito.mock(DbColumn.class));
    when(config.getNumPartitions()).thenReturn(NUM_PARTITIONS);
    when(config.getQuery()).thenReturn(selectQuery);
    when(DriverManager.getConnection(anyString(), anyString(), anyString())).thenReturn(conn);
    when(partition.lower()).thenReturn(0L);
    when(partition.upper()).thenReturn(100L);
    when(conn.createStatement()).thenReturn(statement);
    when(statement.executeQuery(anyString())).thenReturn(resultSet);
    when(resultSet.next()).thenReturn(true);

    JdbcReader reader = new JdbcReader(config);

    reader.init(partition);

    verify(comboCondition, times(2)).addCondition(any((BinaryCondition.class)));
}
Example #13
Source File: MssTopClause.java    From sqlbuilder with Apache License 2.0 4 votes vote down vote up 
@Override
public void apply(SelectQuery query) {
  query.addCustomization(SelectQuery.Hook.DISTINCT, HookType.AFTER, this);
}
Example #14
Source File: MysLimitClause.java    From sqlbuilder with Apache License 2.0 4 votes vote down vote up 
@Override
public void apply(SelectQuery query) {
  query.addCustomization(SelectQuery.Hook.FOR_UPDATE, HookType.BEFORE, this);
}
Example #15
Source File: SetSubquery.java    From olaper with MIT License 4 votes vote down vote up 
public Condition condition() {
	
	if((values==null || values.size()==0) && from_value==null && to_value==null){
		if(except==null)
			return null;
		else
			return new NotCondition(except.condition());
	}
		
	DimensionTable dim_table = join.getDimensionTable();
	
	SelectQuery dim_query = new SelectQuery().
			addFromTable(dim_table.getDbTable()).
			addColumns(dim_table.getDbKey());
	
	if(from_value!=null){
		dim_query = dim_query.addCondition(BinaryCondition.greaterThan(column.getDbColumn(), from_value, true));
	}

	if(to_value!=null){
		dim_query = dim_query.addCondition(BinaryCondition.lessThan(column.getDbColumn(), to_value, true));
	}

	
	if(values!=null && values.size()>0){
		if(values.size()==1){
			String value = values.iterator().next();
			if(LevelMember.NULL_MEMBER.equals(value))
				dim_query = dim_query.addCondition(UnaryCondition.isNull(column.getDbColumn()));
			else		
				dim_query = dim_query.addCondition(BinaryCondition.equalTo(column.getDbColumn(), value));
		}else{ 
			
			if(values.contains(LevelMember.NULL_MEMBER)){
				Set<String> values_without_null = new HashSet<String>(values);
				values_without_null.remove(LevelMember.NULL_MEMBER);
				dim_query = dim_query.addCondition(ComboCondition.or(
						UnaryCondition.isNull(column.getDbColumn()),
						new InCondition(column.getDbColumn(), values_without_null)));
			}else{
				dim_query = dim_query.addCondition(new InCondition(column.getDbColumn(), values));	
			}
			
		}
	}
	
	Condition myCondition = new InCondition(join.getForeign_key(), new Subquery(dim_query) );
	
	if(except!=null)
		myCondition = ComboCondition.and(myCondition, new NotCondition(except.condition()) );

	if(exists!=null)
		myCondition = ComboCondition.and(myCondition, exists.condition());
	
	return myCondition;
	 
	
}
Example #16
Source File: JdbcNeo4JDeepJobConfig.java    From deep-spark with Apache License 2.0 4 votes vote down vote up 
/**
 * {@inheritDoc}
 */
@Override
public SelectQuery getQuery() {
    throw new UnsupportedOperationException("Cannot configure SQL query for Neo4J extractor");
}
Example #17
Source File: PgOffsetClause.java    From sqlbuilder with Apache License 2.0 4 votes vote down vote up 
@Override
public void apply(SelectQuery query) {
  query.addCustomization(SelectQuery.Hook.FOR_UPDATE, HookType.BEFORE, this);
}
Example #18
Source File: PgLimitClause.java    From sqlbuilder with Apache License 2.0 4 votes vote down vote up 
@Override
public void apply(SelectQuery query) {
  query.addCustomization(SelectQuery.Hook.FOR_UPDATE, HookType.BEFORE, this);
}
Example #19
Source File: SqlQuery.java    From olaper with MIT License 4 votes vote down vote up 
public SelectQuery getSql() {
	return sql;
}
Example #20
Source File: SqlMembersQuery.java    From olaper with MIT License 3 votes vote down vote up 
public void generateQuery() throws OlapException {
	
	DimensionTable dtable = physicalSchema.findDimensionTable(level.getDimension());
	if(dtable==null)
		throw new OlapException("Not found dimension mapping: "+level.getDimension());
	TableColumn column = dtable.findColumnByAttribute(level.getName());
	if(column==null)
		throw new OlapException("Not found column mapping: "+level.getName());
	
	TableColumn id_column = column.identified_by==null ? column : dtable.findColumnByName(column.identified_by);
	TableColumn sort_column = column.sorted_by==null ? column : dtable.findColumnByName(column.sorted_by);
	
	
	sql = new SelectQuery();
	
	sql.addAliasedColumn(id_column.getDbColumn(), ID_ALIAS);
	sql.addAliasedColumn(column.getDbColumn(), NAME_ALIAS);		
	
	sql.addGroupings(id_column.getDbColumn());	
	
	if(id_column!=column){
		sql.addGroupings(column.getDbColumn());
	}
	
	if(sort_column!=column && sort_column!=id_column){
		sql.addGroupings(sort_column.getDbColumn());
	}
	
	sql.addOrderings(sort_column.getDbColumn());		
	sql.addCustomization(new MysLimitClause(1000));

	
}
Example #21
Source File: CustomSyntax.java    From sqlbuilder with Apache License 2.0 2 votes vote down vote up 
/**
 * Called by {@link SelectQuery#addCustomization(CustomSyntax)} to add this
 * custom syntax to the appropriate location in the SelectQuery.
 */
public void apply(SelectQuery query) {
  throw new UnsupportedOperationException();
}
Example #22
Source File: IJdbcDeepJobConfig.java    From deep-spark with Apache License 2.0 2 votes vote down vote up 
/**
 * Returns the query to execute.
 * @return Query to execute.
 */
SelectQuery getQuery();