org.fisco.bcos.web3j.precompile.crud.Condition Java Examples

/**
 * CRUD: update table through webase-sign
 */
public int update(int groupId, String signUserId, Table table,
				  Entry entry, Condition condition) throws Exception {
	checkTableKeyLength(table);
	// trans
	String entryJsonStr =
			ObjectMapperFactory.getObjectMapper().writeValueAsString(entry.getFields());
	String conditionStr =
			ObjectMapperFactory.getObjectMapper().writeValueAsString(condition.getConditions());
	List<Object> funcParams = new ArrayList<>();
	funcParams.add(table.getTableName());
	funcParams.add(table.getKey());
	funcParams.add(entryJsonStr);
	funcParams.add(conditionStr);
	funcParams.add(table.getOptional());
	TransactionReceipt receipt = (TransactionReceipt) transService.transHandleWithSignForPrecompile(groupId,
			signUserId, PrecompiledTypes.CRUD, FUNC_UPDATE, funcParams);
	return PrecompiledCommon.handleTransactionReceiptForCRUD(receipt);
}
 

/**
 * CRUD: remove table through webase-sign
 */
public int remove(int groupId, String signUserId, Table table, Condition condition)
        throws Exception {
    checkTableKeyLength(table);
    // trans
    String conditionStr =
            ObjectMapperFactory.getObjectMapper().writeValueAsString(condition.getConditions());
    List<Object> funcParams = new ArrayList<>();
    funcParams.add(table.getTableName());
    funcParams.add(table.getKey());
    funcParams.add(conditionStr);
    funcParams.add(table.getOptional());
    TransactionReceipt receipt =
            (TransactionReceipt) transService.transHandleWithSignForPrecompile(groupId,
                    signUserId, PrecompiledTypes.CRUD, FUNC_REMOVE, funcParams);
    return PrecompiledCommon.handleTransactionReceiptForCRUD(receipt);
}
 

public static void parseRemove(String sql, Table table, Condition condition)
        throws JSQLParserException, FrontException {
    Statement statement = CCJSqlParserUtil.parse(sql);
    Delete delete = (Delete) statement;

    // parse table name
    net.sf.jsqlparser.schema.Table sqlTable = delete.getTable();
    table.setTableName(sqlTable.getName());

    // parse where clause
    Expression where = delete.getWhere();
    if (where != null) {
        BinaryExpression expr = (BinaryExpression) (where);
        handleExpression(condition, expr);
    }
    Limit limit = delete.getLimit();
    parseLimit(condition, limit);
}
 

private static void parseLimit(Condition condition, Limit limit)
        throws FrontException {
    if (limit != null) {
        Expression offset = limit.getOffset();
        Expression count = limit.getRowCount();
        try {
            if (offset != null) {
                condition.Limit(
                        Integer.parseInt(offset.toString()),
                        Integer.parseInt(count.toString()));
            } else {
                condition.Limit(Integer.parseInt(count.toString()));
            }
        } catch (NumberFormatException e) {
            throw new FrontException(PrecompiledUtils.CRUD_SQL_ERROR,
                    "Please provide limit parameters by non-negative integer mode, "
                            + PrecompiledUtils.NonNegativeIntegerRange
                            + ".");
        }
    }
}
 

public static void handleKey(Table table, Condition condition) throws Exception {

        String keyName = table.getKey();
        String keyValue = "";
        Map<EnumOP, String> keyMap = condition.getConditions().get(keyName);
        if (keyMap == null) {
            throw new FrontException(PrecompiledUtils.CRUD_SQL_ERROR,
                    "Please provide a equal condition for the key field '"
                            + keyName
                            + "' in where clause.");
        } else {
            Set<EnumOP> keySet = keyMap.keySet();
            for (EnumOP enumOP : keySet) {
                if (enumOP != EnumOP.eq) {
                    throw new FrontException(PrecompiledUtils.CRUD_SQL_ERROR,
                            "Please provide a equal condition for the key field '"
                                    + keyName
                                    + "' in where clause.");
                } else {
                    keyValue = keyMap.get(enumOP);
                }
            }
        }
        table.setKey(keyValue);
    }
 

public Map<Long, String> listAddress() throws BrokerException {
    log.info("associated groupId: {}", this.groupId);

    List<Map<String, String>> records;
    try {
        this.table.setKey(ContractAddress);
        Condition condition = this.table.getCondition();
        records = this.crud.select(this.table, condition);
        log.info("records in CRUD, {}", records);
    } catch (Exception e) {
        log.error("select from CRUD table failed", e);
        throw new BrokerException(ErrorCode.UNKNOWN_SOLIDITY_VERSION);
    }

    Map<Long, String> addresses = new HashMap<>();
    for (Map<String, String> record : records) {
        addresses.put(Long.valueOf(record.get(TableVersion)), record.get(TableValue));
    }
    return addresses;
}
 

/**
 * select
 */
public List<Map<String, String>> select(int groupId, Table table,
                                        Condition conditions) throws Exception {
    CRUDService crudService = new CRUDService(web3ApiService.getWeb3j(groupId),
            keyStoreService.getCredentialsForQuery());
    List<Map<String, String>> selectRes = crudService.select(table, conditions);
    return selectRes;
}
 

private static Condition handleExpression(Condition condition, Expression expr)
        throws FrontException {
    if (expr instanceof BinaryExpression) {
        condition = getWhereClause((BinaryExpression) (expr), condition);
    }
    if (expr instanceof OrExpression) {
        throw new FrontException(PrecompiledUtils.CRUD_SQL_ERROR, "The OrExpression is not supported.");
    }
    if (expr instanceof NotExpression) {
        throw new FrontException(PrecompiledUtils.CRUD_SQL_ERROR, "The NotExpression is not supported.");
    }
    if (expr instanceof InExpression) {
        throw new FrontException(PrecompiledUtils.CRUD_SQL_ERROR, "The InExpression is not supported.");
    }
    if (expr instanceof LikeExpression) {
        throw new FrontException(PrecompiledUtils.CRUD_SQL_ERROR, "The LikeExpression is not supported.");
    }
    if (expr instanceof SubSelect) {
        throw new FrontException(PrecompiledUtils.CRUD_SQL_ERROR, "The SubSelect is not supported.");
    }
    if (expr instanceof IsNullExpression) {
        throw new FrontException(PrecompiledUtils.CRUD_SQL_ERROR, "The IsNullExpression is not supported.");
    }
    Map<String, Map<EnumOP, String>> conditions = condition.getConditions();
    Set<String> keys = conditions.keySet();
    for (String key : keys) {
        Map<EnumOP, String> value = conditions.get(key);
        EnumOP operation = value.keySet().iterator().next();
        String itemValue = value.values().iterator().next();
        String newValue = trimQuotes(itemValue);
        value.put(operation, newValue);
        conditions.put(key, value);
    }
    condition.setConditions(conditions);
    return condition;
}
 

public static void parseUpdate(String sql, Table table, Entry entry, Condition condition)
        throws JSQLParserException, FrontException {
    Statement statement = CCJSqlParserUtil.parse(sql);
    Update update = (Update) statement;

    // parse table name
    List<net.sf.jsqlparser.schema.Table> tables = update.getTables();
    String tableName = tables.get(0).getName();
    table.setTableName(tableName);

    // parse cloumns
    List<Column> columns = update.getColumns();
    List<Expression> expressions = update.getExpressions();
    int size = expressions.size();
    String[] values = new String[size];
    for (int i = 0; i < size; i++) {
        values[i] = expressions.get(i).toString();
    }
    for (int i = 0; i < columns.size(); i++) {
        entry.put(trimQuotes(columns.get(i).toString()), trimQuotes(values[i]));
    }

    // parse where clause
    Expression where = update.getWhere();
    if (where != null) {
        BinaryExpression expr2 = (BinaryExpression) (where);
        handleExpression(condition, expr2);
    }
    Limit limit = update.getLimit();
    parseLimit(condition, limit);
}
 

/**
 * update
 */
public int update(int groupId, String signUserId, Table table, Entry entry, Condition condition)
        throws Exception {
    int res = precompiledWithSignService.update(groupId, signUserId, table, entry, condition);
    return res;
}
 

/**
 * remove
 */
public int remove(int groupId, String signUserId, Table table, Condition condition)
        throws Exception {
    int res = precompiledWithSignService.remove(groupId, signUserId, table, condition);
    return res;
}
 

public static void parseSelect(
        String sql, Table table, Condition condition, List<String> selectColumns)
        throws JSQLParserException, FrontException {
    Statement statement;
    statement = CCJSqlParserUtil.parse(sql);
    Select selectStatement = (Select) statement;

    // parse table name
    TablesNamesFinder tablesNamesFinder = new TablesNamesFinder();
    List<String> tableList = tablesNamesFinder.getTableList(selectStatement);
    if (tableList.size() != 1) {
        throw new FrontException(PrecompiledUtils.CRUD_SQL_ERROR, "Please provide only one table name.");
    }
    table.setTableName(tableList.get(0));

    // parse where clause
    PlainSelect selectBody = (PlainSelect) selectStatement.getSelectBody();
    if (selectBody.getOrderByElements() != null) {
        throw new FrontException(PrecompiledUtils.CRUD_SQL_ERROR, "The order clause is not supported.");
    }
    if (selectBody.getGroupBy() != null) {
        throw new FrontException(PrecompiledUtils.CRUD_SQL_ERROR, "The group clause is not supported.");
    }
    if (selectBody.getHaving() != null) {
        throw new FrontException(PrecompiledUtils.CRUD_SQL_ERROR, "The having clause is not supported.");
    }
    if (selectBody.getJoins() != null) {
        throw new FrontException(PrecompiledUtils.CRUD_SQL_ERROR, "The join clause is not supported.");
    }
    if (selectBody.getTop() != null) {
        throw new FrontException(PrecompiledUtils.CRUD_SQL_ERROR, "The top clause is not supported.");
    }
    if (selectBody.getDistinct() != null) {
        throw new FrontException(PrecompiledUtils.CRUD_SQL_ERROR, "The distinct clause is not supported.");
    }
    Expression expr = selectBody.getWhere();
    condition = handleExpression(condition, expr);

    Limit limit = selectBody.getLimit();
    if (limit != null) {
        parseLimit(condition, limit);
    }

    // parse select item
    List<SelectItem> selectItems = selectBody.getSelectItems();
    for (SelectItem item : selectItems) {
        if (item instanceof SelectExpressionItem) {
            SelectExpressionItem selectExpressionItem = (SelectExpressionItem) item;
            Expression expression = selectExpressionItem.getExpression();
            if (expression instanceof Function) {
                Function func = (Function) expression;
                throw new FrontException(PrecompiledUtils.CRUD_SQL_ERROR,
                        "The " + func.getName() + " function is not supported.");
            }
        }
        selectColumns.add(item.toString());
    }
}
 

@SuppressWarnings("unchecked")
@Test
 public void curdTest() throws Exception {
   
 	String tableName = "t_test" + new Random().nextInt(100000);
 	String key = "name";
 	String valueFields  = "item_id, item_name";
 	Table table = new Table(tableName, key, valueFields);

 	// create table
   int resultCreate = crudService.createTable(table);
   assertEquals(resultCreate, 0);
 	
   // insert records
   int insertResult = 0;
   int num = 5;
   for(int i = 1; i <= num; i++)
   {
    Entry insertEntry = table.getEntry();
    insertEntry.put("item_id", "1");
   	insertEntry.put("item_name", "apple"+i);
   	table.setKey("fruit");
   	insertResult += crudService.insert(table, insertEntry);
   }
   assertEquals(insertResult, num);
	
   // select records
	Condition condition1 = table.getCondition();
	condition1.EQ("item_id", "1");
	condition1.Limit(1);
	
	List<Map<String, String>> resultSelect1 = crudService.select(table, condition1);
	assertEquals(resultSelect1.get(0).get("name"), "fruit");
	assertEquals(resultSelect1.get(0).get("item_id"), "1");
	assertEquals(resultSelect1.get(0).get("item_name"), "apple1");
 	
  // update records
	Entry updateEntry = table.getEntry();
  	updateEntry.put("item_id", "1");
  	updateEntry.put("item_name", "orange");
  	Condition updateCondition = table.getCondition();
  	updateCondition.EQ("item_id", "1");
  	int updateResult = crudService.update(table, updateEntry, updateCondition);
  	assertEquals(updateResult, num);
	
  // select records
	Condition condition2 = table.getCondition();
	condition2.EQ("item_id", "1");
	condition2.Limit(1);
	List<Map<String, String>> resultSelect2 = crudService.select(table, condition2);
	assertEquals(resultSelect2.get(0).get("name"), "fruit");
	assertEquals(resultSelect2.get(0).get("item_id"), "1");
	assertEquals(resultSelect2.get(0).get("item_name"), "orange");
	
  // remove records
	Condition removeCondition = table.getCondition();
	removeCondition.EQ("item_id", "1");
	int removeResult = crudService.remove(table, removeCondition);
	assertEquals(removeResult, num);
 }