com.healthmarketscience.sqlbuilder.dbspec.basic.DbColumn Java Examples

/**
 * {@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;
}
 

public void testInsert()
{
  String insertStr1 = new InsertQuery(_table1)
    .addColumns(new DbColumn[]{_table1_col1, _table1_col3, _table1_col2},
                new Object[]{13, "feed me seymor", true})
    .validate().toString();
  checkResult(insertStr1,
              "INSERT INTO Schema1.Table1 (col1,col3,col2) VALUES (13,'feed me seymor',1)");

  String insertStr2 = new InsertQuery(_table1)
    .addColumns(new DbColumn[]{_table1_col1},
                new Object[]{"13"})
    .addPreparedColumns(_table1_col2, _table1_col3)
    .validate().toString();
  checkResult(insertStr2,
              "INSERT INTO Schema1.Table1 (col1,col2,col3) VALUES ('13',?,?)");

  String insertStr3 = new InsertQuery(_defTable1)
    .addColumns(new DbColumn[]{_defTable1_col_id},
                new Object[]{13})
    .addPreparedColumns(_defTable1_col2, _defTable1_col3)
    .validate().toString();
  checkResult(insertStr3,
              "INSERT INTO Table1 (col_id,col2,col3) VALUES (13,?,?)");

  try {
    new InsertQuery(_table1)
      .addColumns(new DbColumn[]{_table1_col1, _table1_col3},
                  new Object[]{13})
      .validate();
    fail("ValidationException should have been thrown");
  } catch(ValidationException e) {}
}
 

@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)));
}
 

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);
    }
}
 

public DbColumn getForeign_key() {
	return foreign_key;
}
 

public DbColumn getDbColumn() {
	return dbColumn;
}
 

public void setDbColumn(DbColumn dbColumn) {
	this.dbColumn = dbColumn;
}
 

/**
 * {@inheritDoc}
 */
@Override
public DbColumn getPartitionKey() {
    return this.partitionKey;
}
 

/**
 * {@inheritDoc}
 */
@Override
public DbColumn getSort() {
    return this.sort;
}
 

public DbColumn getDbColumn() {
	return dbColumn;
}
 

/**
 * {@inheritDoc}
 */
@Override
public DbColumn getPartitionKey() {
    throw new UnsupportedOperationException("Cannot configure partitioning for Neo4J extractor");
}
 

/**
 * {@inheritDoc}
 */
@Override
public DbColumn getSort() {
    throw new UnsupportedOperationException("Cannot configure sort for Neo4J extractor");
}
 

public void testAlterTable()
{
  @SuppressWarnings("deprecation")
  String queryStr1 =
    new AlterTableQuery(_table1)
    .setAction(new AlterTableQuery.AddUniqueConstraintAction()
               .addColumns(_table1_col2))
    .validate().toString();
  checkResult(queryStr1, "ALTER TABLE Schema1.Table1 ADD UNIQUE (col2)");

  @SuppressWarnings("deprecation")
  String queryStr2 =
    new AlterTableQuery(_defTable1)
    .setAction(new AlterTableQuery.AddPrimaryConstraintAction()
               .addColumns(_defTable1_col_id))
    .validate().toString();
  checkResult(queryStr2, "ALTER TABLE Table1 ADD PRIMARY KEY (col_id)");

  @SuppressWarnings("deprecation")
  String queryStr3 =
    new AlterTableQuery(_defTable1)
    .setAction(new AlterTableQuery.AddForeignConstraintAction(_defTable2)
               .addPrimaryKeyReference(_defTable1_col_id))
    .validate().toString();
  checkResult(queryStr3,
              "ALTER TABLE Table1 ADD FOREIGN KEY (col_id) REFERENCES Table2");

  @SuppressWarnings("deprecation")
  String queryStr4 =
    new AlterTableQuery(_defTable1)
    .setAction(new AlterTableQuery.AddForeignConstraintAction(_defTable2)
               .addReference(_defTable1_col_id, _defTable2_col4)
               .addReference(_defTable1_col2, _defTable2_col5))
    .validate().toString();
  checkResult(queryStr4,
              "ALTER TABLE Table1 ADD FOREIGN KEY (col_id,col2) " +
              "REFERENCES Table2 (col4,col5)");

  String queryStr5 =
    new AlterTableQuery(_defTable2)
    .setAddConstraint(_defTable2.getConstraints().get(0))
    .validate().toString();
  checkResult(queryStr5,
              "ALTER TABLE Table2 ADD CONSTRAINT t2_fk FOREIGN KEY (col4,col5) REFERENCES Table1 (col2,col3)");

  DbColumn toAdd = _defTable1.addColumn("col5", Types.VARCHAR, 255);
  toAdd.notNull();
  String queryStr6 =
    new AlterTableQuery(_defTable1)
    .setAction(new AlterTableQuery.AddColumnAction(toAdd)
               .addConstraint(new ConstraintClause(ConstraintClause.Type.UNIQUE, null)))
    .validate().toString();
  checkResult(queryStr6,
              "ALTER TABLE Table1 ADD col5 VARCHAR(255) NOT NULL UNIQUE");

  toAdd.setDefaultValue("someValue");
  String queryStr7 =
    new AlterTableQuery(_defTable1)
    .setAddColumn(toAdd)
    .validate().toString();
  checkResult(queryStr7,
              "ALTER TABLE Table1 ADD col5 VARCHAR(255) DEFAULT 'someValue' NOT NULL");

  String queryStr8 =
    new AlterTableQuery(_defTable1)
    .setAction(new AlterTableQuery.AddColumnAction(toAdd)
               .setTypeName("NVARCHAR"))
    .validate().toString();
  checkResult(queryStr8,
              "ALTER TABLE Table1 ADD col5 NVARCHAR(255) DEFAULT 'someValue' NOT NULL");

}
 

public DbColumn getDbKey() {
	return dbKey;
}
 

public DbColumn getColumn() {
	return column;
}
 

public Sorter(DbColumn dbColumn, String direction) {
	this.column = dbColumn;
	this.direction = parseDir(direction);

}
 

private void addGroupedBy(DbColumn col){
	if(!groupings.contains(col))
		groupings.add(col);
}
 

public void setForeign_key(DbColumn foreign_key) {
	this.foreign_key = foreign_key;
}
 

public void setDbColumn(DbColumn dbColumn) {
	this.dbColumn = dbColumn;
}
 

/**
 * Gets the column used for sorting.
 * @return Column used for sorting.
 */
DbColumn getSort();
 

/**
 * Returns the name of the column used for partitioning.
 * @return Name of the column used for partitioning.
 */
DbColumn getPartitionKey();