Java SQL.WHERE属性代码示例

public String update(String[] fields, String conditions) {
	SQL sql = new SQL().UPDATE(metadata.getTableName() + " AS _this");
	for (ColumnProperty property : metadata.getUpdateProperties()) {
		String propertyName = property.getName();
		// 不包含字段
		if (fields.length > 0 && !ArrayUtils.contains(fields, propertyName)) {
			continue;
		}
		Path path = root.getPath(property);
		sql.SET(path.getColumn() + "=#{" + path.getPropertyName() + "}");
	}
	if (conditions != null && conditions.length() > 0) {
		sql.WHERE(conditions);
	}
	return sql.toString();
}
 

/**
 * This method was generated by MyBatis Generator.
 * This method corresponds to the database table user
 *
 * @mbg.generated
 */
public String updateByPrimaryKeySelective(User record) {
    SQL sql = new SQL();
    sql.UPDATE("user");
    
    if (record.getUsername() != null) {
        sql.SET("username = #{username,jdbcType=VARCHAR}");
    }
    
    if (record.getPsw() != null) {
        sql.SET("psw = #{psw,jdbcType=VARCHAR}");
    }
    
    sql.WHERE("id = #{id,jdbcType=INTEGER}");
    
    return sql.toString();
}
 

private void where(SQL sql, String conditions) {
	// 外连接其他表
	for (AssociationProperty association : metadata.getAssociations()) {
		// 非Join方式不处理
		if (association.getFetchMode() != FetchMode.JOIN) {
			continue;
		}
		Class<?> referenceType = association.getReferenceType();
		EntityMetadata refMetadata = EntityMetadata.get(referenceType);
		// 获取主表和从表的关联属性
		StringBuilder joinBuilder = new StringBuilder();
		joinBuilder.append(refMetadata.getTableName());
		joinBuilder.append(" AS ");
		joinBuilder.append("_").append(association.getName());
		joinBuilder.append(" ON ");
		List<ColumnProperty> assocProperties = association.getProperties();
		List<ColumnProperty> refReferences = association.getReferences();
		for (int i = 0; i < assocProperties.size(); i++) {
			if (i > 0) {
				joinBuilder.append(" AND ");
			}
			ColumnProperty property = assocProperties.get(i);
			ColumnProperty reference = refReferences.get(i);
			joinBuilder.append(root.getPath(property).getColumn());
			joinBuilder.append("=");
			joinBuilder.append(root.getPath(association, reference).getColumn());
		}
		sql.LEFT_OUTER_JOIN(joinBuilder.toString());
	}
	if (!StringUtils.isEmpty(conditions)) {
		sql.WHERE(conditions);
	}
}
 

@SuppressWarnings("rawtypes")
public String updateSql(Object obj) throws Exception {
    String tableName = getTableName(obj);
    Map oMap = getBeanMap(obj);
    SQL uSql = new SQL();
    uSql.UPDATE(tableName);
    for (Object key : oMap.keySet()) {
        // 属性为null，统一不做修改
        if (null != oMap.get(key) && !key.toString().equals("id")) {
            uSql.SET(key.toString() + " = #{" + key.toString() + "}");
        }
    }
    uSql.WHERE("id = #{id}");
    return uSql.toString();
}
 

@SuppressWarnings("rawtypes")
private String deleteByExampleSql(Object obj, boolean flag) throws Exception {
    String tableName = getTableName(obj);
    Map oMap = getBeanMap(obj);
    SQL dSql = new SQL();
    dSql.DELETE_FROM(tableName);
    dSql.WHERE("1 = 1");
    for (Object key : oMap.keySet()) {
        if (null != oMap.get(key)) {
            if (flag) {
                dSql.AND();
            } else {
                dSql.OR();
            }
            dSql.WHERE(key.toString() + " = #{" + key.toString() + "}");
        }
    }
    return dSql.toString();
}
 

@SuppressWarnings("rawtypes")
private String findByExampleSql(Object obj, boolean flag) throws Exception {
    String tableName = getTableName(obj);
    Map oMap = getBeanMap(obj);
    SQL fSql = new SQL();
    fSql.SELECT("*");
    fSql.FROM(tableName);
    fSql.WHERE("1 = 1");
    for (Object key : oMap.keySet()) {
        if (null != oMap.get(key)) {
            if (flag) {
                fSql.AND();
            } else {
                fSql.OR();
            }
            fSql.WHERE(key.toString() + " = #{" + key.toString() + "}");
        }
    }
    return fSql.toString();
}
 

public String updateByPrimaryKeySelective(Object record) {
    final String[] primaryKeyName = {null};
    final Column[] primaryKeyAnnotation = {null};
    SQL sql = new SQL().UPDATE(this.getTableName());
    getAllDeclaredFields(record.getClass()).stream()
            .filter(field -> !field.isAnnotationPresent(Ignore.class))
            .forEach(field -> {
                String fieldName = field.getName();
                if (field.isAnnotationPresent(PK.class)) {
                    primaryKeyName[0] = field.getName();
                    if (field.isAnnotationPresent(Column.class)) {
                        primaryKeyAnnotation[0] = field.getAnnotation(Column.class);
                    }
                }
                Object value = valueOfField(record, fieldName);
                if (null != value) {
                    if (field.isAnnotationPresent(Column.class)) {
                        Column annotation = field.getAnnotation(Column.class);
                        sql.SET(annotation.column().trim() + "=" + fieldExpression(fieldName, annotation));
                    } else {
                        sql.SET(columnName(fieldName) + "=" + fieldExpression(fieldName, null));
                    }
                }
            });
    if (null == primaryKeyName[0]) {
        return null;
    }
    if (null != primaryKeyAnnotation[0]) {
        sql.WHERE(primaryKeyAnnotation[0].column().trim()
                + "=" + fieldExpression(primaryKeyName[0], primaryKeyAnnotation[0]));
    } else {
        sql.WHERE(columnName(primaryKeyName[0])
                + "=" + fieldExpression(primaryKeyName[0], null));
    }
    return sql.toString();
}
 

public String updateByPrimaryKeySelective(T record) {
    final String[] primaryKeyName = {null};
    final Column[] primaryKeyAnnotation = {null};
    SQL sql = new SQL().UPDATE(this.getTableName());
    getAllDeclaredFields(entityClass).stream()
            .filter(field -> !field.isAnnotationPresent(Ignore.class))
            .forEach(field -> {
                String fieldName = field.getName();
                if (field.isAnnotationPresent(PK.class)) {
                    primaryKeyName[0] = field.getName();
                    if (field.isAnnotationPresent(Column.class)) {
                        primaryKeyAnnotation[0] = field.getAnnotation(Column.class);
                    }
                }
                Object value = valueOfField(record, fieldName);
                if (null != value) {
                    if (field.isAnnotationPresent(Column.class)) {
                        Column annotation = field.getAnnotation(Column.class);
                        sql.SET(annotation.column().trim() + "=" + fieldExpression(fieldName, annotation));
                    } else {
                        sql.SET(columnName(fieldName) + "=" + fieldExpression(fieldName, null));
                    }
                }
            });
    if (null == primaryKeyName[0]) {
        return null;
    }
    if (null != primaryKeyAnnotation[0]) {
        sql.WHERE(primaryKeyAnnotation[0].column().trim()
                + "=" + fieldExpression(primaryKeyName[0], primaryKeyAnnotation[0]));
    } else {
        sql.WHERE(columnName(primaryKeyName[0])
                + "=" + fieldExpression(primaryKeyName[0], null));
    }
    return sql.toString();
}
 

public String selectByCriteria(Map<String, Object> params) {
    Criteria criteria = (Criteria) params.get("criteria");
    Pageable pageable = null;
    if (params.containsKey("pageable")) {
        pageable = (Pageable) params.get("pageable");
    }
    EntityTable entityTable = getEntityTable(criteria.getEntityClass());
    SQL sql = new SQL();
    sql.SELECT(entityTable.getSelectColumns());
    sql.FROM(entityTable.getName());
    entityTable.getInnerJoins().forEach(sql::INNER_JOIN);
    sql.WHERE(criteria.getClause());
    StringJoiner stringJoiner = new StringJoiner(", ");
    if (criteria.getOrderBys() != null && !criteria.getOrderBys().isEmpty()) {
        criteria.getOrderBys().forEach(stringJoiner::add);
    } else if (entityTable.getOrderBys() != null && !entityTable.getOrderBys().isEmpty()) {
        entityTable.getOrderBys().forEach(stringJoiner::add);
    }
    if (stringJoiner.length() > 0) {
        sql.ORDER_BY(stringJoiner.toString());
    }
    if (pageable == null) {
        entityTable.getOrderBys().forEach(sql::ORDER_BY);
        return sql.toString();
    } else {
        return applyPageable(sql, pageable);
    }
}
 

private SQL WHERE(SQL sql, T findParams, String operator) {
    if (findParams == null) {
        return sql;
    }

    Map<String, Property> properties = ModelUtils.getProperties(findParams, ColumnTarget.WHERE);

    for (Property property : properties.values()) {
        if (operator.equalsIgnoreCase(OPERATOR_LIKE)) {
        }
        sql.WHERE(property.getColumnName() + operator + "#{findParams." + property.getName() + "}");
    }
    return sql;
}
 

public static SQL WHERE_CUSTOM(SQL sql, Class modelClass, Map<String, Object> dataMap, List<CustomQueryParam> customQueryParams, String tableAlias) {
        Map<String, Property> properties = ModelUtils.getProperties(modelClass, null);

//        int i = 0;
        for (CustomQueryParam customQueryParam : customQueryParams) {
            String key = customQueryParam.getProperty();
            Property property = properties.get(key);
            if (property == null) {
                continue;
            }
            String condition = "";
            if (StringUtils.isNotEmpty(tableAlias)) {
                condition = tableAlias + "." + condition;
            }
            if (customQueryParam instanceof WithValueQueryParam) {
                WithValueQueryParam withValueQueryParam = (WithValueQueryParam) customQueryParam;
                dataMap.put(dataMap.size() + "", withValueQueryParam.getValue());
                // 不能以property为key放入dataMap，因为可能有相同的property，如> & <
                condition = condition + property.getColumnName() + " " + withValueQueryParam.getOperator() + " #{" + (dataMap.size() - 1) + "}";
//                i++;
            } else if (customQueryParam instanceof NoValueQueryParam) {
                NoValueQueryParam noValueQueryParam = (NoValueQueryParam) customQueryParam;
                condition = condition + property.getColumnName() + " " + noValueQueryParam.getCondition();
            }
            sql.WHERE(condition);
        }
        return sql;
    }