com.healthmarketscience.sqlbuilder.BinaryCondition Java Examples

The following examples show how to use com.healthmarketscience.sqlbuilder.BinaryCondition. 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: 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 #3
Source File: CustomSyntaxTest.java    From sqlbuilder with Apache License 2.0 5 votes vote down vote up
public void testMysqlExtractExpression()
{
  String exprStr = BinaryCondition.equalTo(
      "2016",
      new ExtractExpression(MysExtractDatePart.MICROSECOND, "2016-01-01"))
    .toString();
  checkResult(exprStr, "('2016' = EXTRACT(MICROSECOND FROM '2016-01-01'))");
}
 
Example #4
Source File: CustomSyntaxTest.java    From sqlbuilder with Apache License 2.0 5 votes vote down vote up
public void testPostgresqlExtractExpression()
{
  String exprStr = BinaryCondition.equalTo(
      "2016",
      new ExtractExpression(PgExtractDatePart.CENTURY, "2016-01-01"))
    .toString();
  checkResult(exprStr, "('2016' = EXTRACT(CENTURY FROM '2016-01-01'))");
}
 
Example #5
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 #6
Source File: CustomSyntaxTest.java    From sqlbuilder with Apache License 2.0 5 votes vote down vote up
public void testOracleExtractExpression()
{
  String exprStr = BinaryCondition.equalTo(
      "2016",
      new ExtractExpression(OraExtractDatePart.TIMEZONE_ABBR, "2016-01-01"))
    .toString();
  checkResult(exprStr, "('2016' = EXTRACT(TIMEZONE_ABBR FROM '2016-01-01'))");
}
 
Example #7
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 #8
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 #9
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;
	 
	
}