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

protected void createTable() throws BrokerException {
    Table table = new Table(this.tableName, TableKey, TableValue + "," + TableVersion);
    try {
        int result = this.crud.createTable(table);
        if (result == 0) {
            log.info("create table in CRUD success, {}", this.tableName);
            this.table = table;
            return;
        }

        log.error("create table in CRUD failed, " + this.tableName);
        throw new BrokerException(ErrorCode.TRANSACTION_EXECUTE_ERROR);
    } catch (Exception e) {
        log.error("create table in CRUD failed, " + this.tableName, e);
        throw new BrokerException(ErrorCode.TRANSACTION_EXECUTE_ERROR);
    }
}
 

/**
 * 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 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 static void checkUserTableParam(Entry entry, Table descTable)
        throws FrontException {
    Map<String, String> fieldsMap = entry.getFields();
    Set<String> keys = fieldsMap.keySet();
    for (String key : keys) {
        if (key.equals(descTable.getKey())) {
            if (fieldsMap.get(key).length() > PrecompiledUtils.USER_TABLE_KEY_VALUE_MAX_LENGTH) {
                throw new FrontException(PrecompiledUtils.CRUD_SQL_ERROR,
                        "The table primary key value length is greater than "
                                + PrecompiledUtils.USER_TABLE_KEY_VALUE_MAX_LENGTH
                                + ".");
            }
        } else {
            if (fieldsMap.get(key).length() > PrecompiledUtils.USER_TABLE_FIELD_VALUE_MAX_LENGTH) {
                throw new FrontException(PrecompiledUtils.CRUD_SQL_ERROR,
                        "The table field '" + key + "' value length is greater than 16M - 1.");
            }
        }
    }
}
 

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

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

public static void checkTableParams(Table table) throws FrontException {
    if (table.getTableName().length() > PrecompiledUtils.SYS_TABLE_KEY_MAX_LENGTH) {
        throw new FrontException(PrecompiledUtils.CRUD_SQL_ERROR,
                "The table name length is greater than "
                        + PrecompiledUtils.SYS_TABLE_KEY_MAX_LENGTH
                        + ".");
    }
    if (table.getKey().length() > PrecompiledUtils.SYS_TABLE_KEY_FIELD_NAME_MAX_LENGTH) {
        throw new FrontException(PrecompiledUtils.CRUD_SQL_ERROR,
                "The table primary key name length is greater than "
                        + PrecompiledUtils.SYS_TABLE_KEY_FIELD_NAME_MAX_LENGTH
                        + ".");
    }
    String[] valueFields = table.getValueFields().split(",");
    for (String valueField : valueFields) {
        if (valueField.length() > PrecompiledUtils.USER_TABLE_FIELD_NAME_MAX_LENGTH) {
            throw new FrontException(PrecompiledUtils.CRUD_SQL_ERROR,
                    "The table field name length is greater than "
                            + PrecompiledUtils.USER_TABLE_FIELD_NAME_MAX_LENGTH
                            + ".");
        }
    }
    if (table.getValueFields().length() > PrecompiledUtils.SYS_TABLE_VALUE_FIELD_MAX_LENGTH) {
        throw new FrontException(PrecompiledUtils.CRUD_SQL_ERROR,
                "The table total field name length is greater than "
                        + PrecompiledUtils.SYS_TABLE_VALUE_FIELD_MAX_LENGTH
                        + ".");
    }
}
 

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

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

/**
 * desc
 */
public Table desc(int groupId, String tableName) throws Exception {
    CRUDService crudService = new CRUDService(web3ApiService.getWeb3j(groupId),
            keyStoreService.getCredentialsForQuery());
    Table descRes = crudService.desc(tableName);
    return descRes;
}
 

public Object desc(int groupId, String sql) throws Exception {
    Instant startTime = Instant.now();
    log.info("start descTable startTime:{}, groupId:{},sql:{}", startTime.toEpochMilli(),
            groupId, sql);
    Table table = new Table();
    String[] sqlParams = sql.split(" ");
    // "desc t_demo"
    String tableName = sqlParams[1];

    if (tableName.length() > PrecompiledUtils.SYS_TABLE_KEY_MAX_LENGTH) {
        return new BaseResponse(PrecompiledUtils.CRUD_SQL_ERROR,
                "The table name length is greater than "
                        + PrecompiledUtils.SYS_TABLE_KEY_MAX_LENGTH + ".",
                "The table name length is greater than "
                        + PrecompiledUtils.SYS_TABLE_KEY_MAX_LENGTH + ".");
    }
    CRUDParseUtils.invalidSymbol(tableName);
    if (tableName.endsWith(";")) {
        tableName = tableName.substring(0, tableName.length() - 1);
    }
    try {
        table = precompiledService.desc(groupId, tableName);
        log.info("end descTable useTime:{} res:{}",
                Duration.between(startTime, Instant.now()).toMillis(), table);
        return new BaseResponse(ConstantCode.RET_SUCCESS, table);
    } catch (Exception e) {
        log.error("descTable.exception:[] ", e);
        return new BaseResponse(ConstantCode.FAIL_TABLE_NOT_EXISTS, e.getMessage());
    }
}
 

public Object createTable(int groupId, String fromAddress, String sql) throws Exception {
    Instant startTime = Instant.now();
    log.info("start createTable startTime:{}, groupId:{},fromAddress:{},sql:{}",
            startTime.toEpochMilli(), groupId, fromAddress, sql);
    Table table = new Table();
    try {
        log.debug("start parseCreateTable.");
        CRUDParseUtils.parseCreateTable(sql, table);
        log.debug("end parseCreateTable. table:{}", table);
    } catch (Exception e) {
        log.error("parseCreateTable. table:{},exception:{}", table, e);
        return new BaseResponse(PrecompiledUtils.CRUD_SQL_ERROR,
                "Could not parse SQL statement." + CRUDParseUtils.invalidSymbolReturn(sql),
                "Could not parse SQL statement." + CRUDParseUtils.invalidSymbolReturn(sql));
    }
    CRUDParseUtils.checkTableParams(table);
    int result = precompiledService.createTable(groupId, fromAddress, table);
    log.info("end createTable useTime:{} res:{}",
            Duration.between(startTime, Instant.now()).toMillis(), result);

    if (result == 0) {
        return new BaseResponse(ConstantCode.RET_SUCCESS,
                "Create '" + table.getTableName() + "' Ok.");
    } else if (result == PrecompiledCommon.TableExist_RC3) {
        log.debug("createTable " + "Table already exists");
        return new BaseResponse(PrecompiledCommon.TableExist_RC3, "Table already exists",
                "Table already exists");
    } else if (result == PrecompiledCommon.PermissionDenied_RC3) {
        log.debug("createTable " + "Permission denied");
        return new BaseResponse(PrecompiledCommon.PermissionDenied_RC3, "Permission denied",
                "Permission denied");
    } else {
        log.debug("createTable " + "code: " + result + "Create '" + table.getTableName()
                + "' failed.");
        return new BaseResponse(PrecompiledUtils.CRUD_SQL_ERROR,
                "code: " + result + "Create '" + table.getTableName() + "' failed.",
                "code: " + result + "Create '" + table.getTableName() + "' failed.");
    }
}
 

private void checkTableKeyLength(Table table) throws PrecompileMessageException {
    if (table.getKey().length() > PrecompiledCommon.TABLE_KEY_MAX_LENGTH) {
        throw new PrecompileMessageException(
                "The value of the table key exceeds the maximum limit("
                        + PrecompiledCommon.TABLE_KEY_MAX_LENGTH + ").");
    }
}
 

/**
 * CRUD: create table through webase-sign
 */
public int createTable(int groupId, String signUserId, Table table) throws Exception {
	List<Object> funcParams = new ArrayList<>();
	funcParams.add(table.getTableName());
	funcParams.add(table.getKey());
	funcParams.add(table.getValueFields());
	TransactionReceipt receipt = (TransactionReceipt) transService.transHandleWithSignForPrecompile(groupId,
			signUserId, PrecompiledTypes.TABLE_FACTORY, FUNC_CREATETABLE, funcParams);
	return PrecompiledCommon.handleTransactionReceiptForCRUD(receipt);
}
 

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

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

public static void parseCreateTable(String sql, Table table)
        throws JSQLParserException, FrontException {
    Statement statement = CCJSqlParserUtil.parse(sql);
    CreateTable createTable = (CreateTable) statement;

    // parse table name
    String tableName = createTable.getTable().getName();
    table.setTableName(tableName);

    // parse key from index
    boolean keyFlag = false;
    List<Index> indexes = createTable.getIndexes();
    if (indexes != null) {
        if (indexes.size() > 1) {
            throw new FrontException(PrecompiledUtils.CRUD_SQL_ERROR,
                    "Please provide only one primary key for the table.");
        }
        keyFlag = true;
        Index index = indexes.get(0);
        String type = index.getType().toLowerCase();
        if (PRIMARY_KEY.equals(type)) {
            table.setKey(index.getColumnsNames().get(0));
        } else {
            throw new FrontException(PrecompiledUtils.CRUD_SQL_ERROR,
                    "Please provide only one primary key for the table.");
        }
    }
    List<ColumnDefinition> columnDefinitions = createTable.getColumnDefinitions();
    // parse key from ColumnDefinition
    for (int i = 0; i < columnDefinitions.size(); i++) {
        List<String> columnSpecStrings = columnDefinitions.get(i).getColumnSpecStrings();
        if (columnSpecStrings == null) {
            continue;
        } else {
            if (columnSpecStrings.size() == 2
                    && "primary".equals(columnSpecStrings.get(0))
                    && "key".equals(columnSpecStrings.get(1))) {
                String key = columnDefinitions.get(i).getColumnName();
                if (keyFlag) {
                    if (!table.getKey().equals(key)) {
                        throw new FrontException(PrecompiledUtils.CRUD_SQL_ERROR,
                                "Please provide only one primary key for the table.");
                    }
                } else {
                    keyFlag = true;
                    table.setKey(key);
                }
                break;
            }
        }
    }
    if (!keyFlag) {
        throw new FrontException(PrecompiledUtils.CRUD_SQL_ERROR, "Please provide a primary key for the table.");
    }
    // parse value field
    List<String> fieldsList = new ArrayList<>();
    for (int i = 0; i < columnDefinitions.size(); i++) {
        String columnName = columnDefinitions.get(i).getColumnName();
        if (fieldsList.contains(columnName)) {
            throw new FrontException(PrecompiledUtils.CRUD_SQL_ERROR,
                    "Please provide the field '" + columnName + "' only once.");
        } else {
            fieldsList.add(columnName);
        }
    }
    if (!fieldsList.contains(table.getKey())) {
        throw new FrontException(PrecompiledUtils.CRUD_SQL_ERROR,
                "Please provide the field '" + table.getKey() + "' in column definition.");
    } else {
        fieldsList.remove(table.getKey());
    }
    StringBuffer fields = new StringBuffer();
    for (int i = 0; i < fieldsList.size(); i++) {
        fields.append(fieldsList.get(i));
        if (i != fieldsList.size() - 1) {
            fields.append(",");
        }
    }
    table.setValueFields(fields.toString());
}
 

public static boolean parseInsert(String sql, Table table, Entry entry)
        throws JSQLParserException, FrontException {
    Statement statement = CCJSqlParserUtil.parse(sql);
    Insert insert = (Insert) statement;

    if (insert.getSelect() != null) {
        throw new FrontException(PrecompiledUtils.CRUD_SQL_ERROR, "The insert select clause is not supported.");
    }
    // parse table name
    String tableName = insert.getTable().getName();
    table.setTableName(tableName);

    // parse columns
    List<Column> columns = insert.getColumns();
    ItemsList itemsList = insert.getItemsList();
    String items = itemsList.toString();
    String[] rawItem = items.substring(1, items.length() - 1).split(",");
    String[] itemArr = new String[rawItem.length];
    for (int i = 0; i < rawItem.length; i++) {
        itemArr[i] = rawItem[i].trim();
    }
    if (columns != null) {
        if (columns.size() != itemArr.length) {
            throw new FrontException(PrecompiledUtils.CRUD_SQL_ERROR, "Column count doesn't match value count.");
        }
        List<String> columnNames = new ArrayList<>();
        for (Column column : columns) {
            String columnName = trimQuotes(column.toString());
            if (columnNames.contains(columnName)) {
                throw new FrontException(PrecompiledUtils.CRUD_SQL_ERROR,
                        "Please provide the field '" + columnName + "' only once.");
            } else {
                columnNames.add(columnName);
            }
        }
        for (int i = 0; i < columnNames.size(); i++) {
            entry.put(columnNames.get(i), trimQuotes(itemArr[i]));
        }
        return false;
    } else {
        for (int i = 0; i < itemArr.length; i++) {
            entry.put(i + "", trimQuotes(itemArr[i]));
        }
        return true;
    }
}
 

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

/**
 * insert 校验tableName等操作放在controller
 */
public int insert(int groupId, String signUserId, Table table, Entry entry) throws Exception {
    int res = precompiledWithSignService.insert(groupId, signUserId, table, entry);
    return res;
}
 

/**
 * CRUD related Table table - validation in controller
 */
public int createTable(int groupId, String signUserId, Table table) throws Exception {
    int res = precompiledWithSignService.createTable(groupId, signUserId, table);
    return res;
}
 

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