Java Code Examples for com.healthmarketscience.sqlbuilder.SelectQuery

The following examples show how to use com.healthmarketscience.sqlbuilder.SelectQuery. 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: sqlbuilder   Source File: CustomSyntaxTest.java    License: 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 2
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 3
Source Project: deep-spark   Source File: JdbcDeepJobConfig.java    License: 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 4
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 5
Source Project: deep-spark   Source File: JdbcNeo4JReaderTest.java    License: 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 6
Source Project: deep-spark   Source File: JdbcNeo4JReaderTest.java    License: 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 7
Source Project: sqlbuilder   Source File: CustomSyntaxTest.java    License: 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 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 9
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 10
Source Project: deep-spark   Source File: JdbcReaderTest.java    License: 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 Project: deep-spark   Source File: JdbcReaderTest.java    License: 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 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 13
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;
	 
	
}
 
Example 14
Source Project: olaper   Source File: SqlQuery.java    License: MIT License 4 votes vote down vote up
public SelectQuery getSql() {
	return sql;
}
 
Example 15
Source Project: sqlbuilder   Source File: PgLimitClause.java    License: 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 16
Source Project: sqlbuilder   Source File: PgOffsetClause.java    License: 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 17
Source Project: sqlbuilder   Source File: MssTopClause.java    License: 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 18
Source Project: sqlbuilder   Source File: MysLimitClause.java    License: 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 Project: deep-spark   Source File: JdbcNeo4JDeepJobConfig.java    License: 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 20
Source Project: olaper   Source File: SqlMembersQuery.java    License: 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 Project: sqlbuilder   Source File: CustomSyntax.java    License: 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 Project: deep-spark   Source File: IJdbcDeepJobConfig.java    License: Apache License 2.0 2 votes vote down vote up
/**
 * Returns the query to execute.
 * @return Query to execute.
 */
SelectQuery getQuery();