ezolnbl-dpc commented on code in PR #2659:
URL: https://github.com/apache/fineract/pull/2659#discussion_r994875227
##########
fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/service/ReadWriteNonCoreDataServiceImpl.java:
##########
@@ -183,6 +192,116 @@ public DatatableData retrieveDatatable(final String
datatable) {
return datatableData;
}
+ @Override
+ public List<JsonObject> queryDataTable(String datatable, String
columnFilter, String valueFilter, String resultColumns) {
+ Arrays.asList(datatable, columnFilter, valueFilter,
resultColumns).forEach(SQLInjectionValidator::validateDynamicQuery);
+ List<String> resultColumnNames =
Stream.of(resultColumns.split(",")).toList();
+ String sql = "select " + resultColumns + " from " + datatable + "
where " + columnFilter + " = ?";
+
+ List<ResultsetColumnHeaderData> resultsetColumnHeaderData =
genericDataService.fillResultsetColumnHeaders(datatable);
+ Object finalValueFilter = valueFilter;
+ SqlRowSet rowSet = null;
+ String filterColumnType =
resultsetColumnHeaderData.stream().filter(column ->
columnFilter.equals(column.getColumnName()))
+
.findFirst().map(ResultsetColumnHeaderData::getColumnType).orElse(columnFilter
+ " does not exist in datatable");
+ if (databaseTypeResolver.isPostgreSQL()) {
+ int[] argType = new int[1];
+ if ("bit".equalsIgnoreCase(filterColumnType)) {
+ finalValueFilter =
BooleanUtils.toString(BooleanUtils.toBooleanObject(valueFilter), "1", "0",
"null");
+ argType[0] = Types.BIT;
+ } else if ("boolean".equalsIgnoreCase(filterColumnType) ||
"bool".equalsIgnoreCase(filterColumnType)) {
+ finalValueFilter =
BooleanUtils.toString(BooleanUtils.toBooleanObject(valueFilter), "true",
"false", "null");
+ argType[0] = Types.BOOLEAN;
+ } else if ("integer".equalsIgnoreCase(filterColumnType)) {
+ argType[0] = Types.INTEGER;
+ } else if ("bigint".equalsIgnoreCase(filterColumnType)) {
+ argType[0] = Types.BIGINT;
+ } else if ("date".equalsIgnoreCase(filterColumnType)) {
+ argType[0] = Types.DATE;
+ } else if (filterColumnType.toLowerCase().contains("timestamp")) {
+ argType[0] = Types.TIMESTAMP;
+ } else if ("numeric".equalsIgnoreCase(filterColumnType)) {
+ argType[0] = Types.DECIMAL;
+ } else if ("text".equalsIgnoreCase(filterColumnType) || "character
varying".equalsIgnoreCase(filterColumnType)) {
+ argType[0] = Types.VARCHAR;
+ } else {
+ List<ApiParameterError> paramErrors = new ArrayList<>();
+ paramErrors.add(parameterErrorWithValue("400",
+ "Unsupported input type for datatable query! Column
filter: " + filterColumnType, "valueFilter", valueFilter));
+ throw new PlatformApiDataValidationException(paramErrors);
+ }
+ rowSet = jdbcTemplate.queryForRowSet(sql, new Object[] {
finalValueFilter }, argType);
+ } else if (databaseTypeResolver.isMySQL()) {
+ if ("bit".equalsIgnoreCase(filterColumnType)) {
+ int[] argType = new int[1];
+ argType[0] = Types.BIT;
+ finalValueFilter =
BooleanUtils.toString(BooleanUtils.toBooleanObject(valueFilter), "1", "0",
"null");
+ rowSet = jdbcTemplate.queryForRowSet(sql, new Object[] {
finalValueFilter }, argType);
+ } else if ("date".equalsIgnoreCase(filterColumnType)) {
+ int[] argType = new int[1];
+ argType[0] = Types.DATE;
+ try {
+ rowSet = jdbcTemplate.queryForRowSet(sql, new Object[] {
LocalDate.parse(valueFilter, DATA_TABLE_DATE_FORMAT_MYSQL) },
+ argType); // NOSONAR
+ } catch (DateTimeParseException e) {
+ List<ApiParameterError> paramErrors = new ArrayList<>();
+ paramErrors.add(parameterErrorWithValue("400",
+ "Unsupported input type for datatable query! Use
format: 'yyyy-MM-dd'. Column filter: " + filterColumnType,
+ "valueFilter", valueFilter));
+ throw new PlatformApiDataValidationException(paramErrors,
e);
+ }
+ } else if ("datetime".equals(filterColumnType)) {
+ int[] argType = new int[1];
+ argType[0] = Types.TIMESTAMP;
+ try {
+ rowSet = jdbcTemplate.queryForRowSet(sql,
+ new Object[] { LocalDateTime.parse(valueFilter,
DATA_TABLE_DATETIME_FORMAT_MYSQL) }, argType); // NOSONAR
+ } catch (DateTimeParseException e) {
+ List<ApiParameterError> paramErrors = new ArrayList<>();
+ paramErrors.add(parameterErrorWithValue("400",
+ "Unsupported input type for datatable query! Use
format: 'yyyy-MM-dd HH:mm:ss'. Column filter: "
+ + filterColumnType,
+ "valueFilter", valueFilter));
+ throw new PlatformApiDataValidationException(paramErrors,
e);
+ }
+ } else {
+ rowSet = jdbcTemplate.queryForRowSet(sql, new Object[] {
finalValueFilter }); // NOSONAR
+ }
+ }
+
+ List<JsonObject> results = new ArrayList<>();
+ while (rowSet.next()) {
+ JsonObject json = new JsonObject();
+ for (String rcn : resultColumnNames) {
+ Object rowValue = rowSet.getObject(rcn);
+ if (rowValue != null) {
+ if (rowValue instanceof Character) {
+ json.addProperty(rcn, (Character) rowValue);
+ } else if (rowValue instanceof Number) {
+ json.addProperty(rcn, new
BigDecimal(rowValue.toString()));
+ } else if (rowValue instanceof Boolean) {
+ json.addProperty(rcn, (Boolean) rowValue);
+ } else if (rowValue instanceof LocalDateTime) {
+ json.addProperty(rcn,
DATA_TABLE_DATETIME_FORMAT_MYSQL.format((LocalDateTime) rowValue));
Review Comment:
done
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
