Java Code Examples for com.healthmarketscience.sqlbuilder.BinaryCondition

The following examples show how to use com.healthmarketscience.sqlbuilder.BinaryCondition. These examples are extracted from open source projects. 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 Project: deep-spark   Source File: JdbcReader.java    License: 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 Project: deep-spark   Source File: JdbcReaderTest.java    License: 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 Project: sqlbuilder   Source File: CustomSyntaxTest.java    License: 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 Project: sqlbuilder   Source File: CustomSyntaxTest.java    License: 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 Project: sqlbuilder   Source File: CustomSyntaxTest.java    License: 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 Project: sqlbuilder   Source File: CustomSyntaxTest.java    License: 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 Project: deep-spark   Source File: JdbcDeepJobConfig.java    License: 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 Project: deep-spark   Source File: JdbcReaderTest.java    License: 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 Project: olaper   Source File: SetSubquery.java    License: 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;
	 
	
}