| /** |
| * Licensed to the Apache Software Foundation (ASF) under one |
| * or more contributor license agreements. See the NOTICE file |
| * distributed with this work for additional information |
| * regarding copyright ownership. The ASF licenses this file |
| * to you under the Apache License, Version 2.0 (the |
| * "License"); you may not use this file except in compliance |
| * with the License. You may obtain a copy of the License at |
| * |
| * http://www.apache.org/licenses/LICENSE-2.0 |
| * |
| * Unless required by applicable law or agreed to in writing, |
| * software distributed under the License is distributed on an |
| * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY |
| * KIND, either express or implied. See the License for the |
| * specific language governing permissions and limitations |
| * under the License. |
| */ |
| package org.apache.fineract.infrastructure.core.service.database; |
| |
| import static java.lang.String.format; |
| |
| import jakarta.validation.constraints.NotNull; |
| import java.math.BigInteger; |
| import java.util.Collection; |
| import java.util.List; |
| import java.util.Map; |
| import java.util.stream.Collectors; |
| import org.apache.fineract.infrastructure.core.service.DateUtils; |
| import org.apache.fineract.infrastructure.dataqueries.data.ResultsetColumnHeaderData; |
| import org.apache.logging.log4j.util.Strings; |
| import org.springframework.beans.factory.annotation.Autowired; |
| import org.springframework.data.domain.Sort; |
| import org.springframework.jdbc.support.GeneratedKeyHolder; |
| import org.springframework.stereotype.Component; |
| |
| @Component |
| public class DatabaseSpecificSQLGenerator { |
| |
| private final DatabaseTypeResolver databaseTypeResolver; |
| public static final String SELECT_CLAUSE = "SELECT %s"; |
| |
| @Autowired |
| public DatabaseSpecificSQLGenerator(DatabaseTypeResolver databaseTypeResolver) { |
| this.databaseTypeResolver = databaseTypeResolver; |
| } |
| |
| public DatabaseType getDialect() { |
| return databaseTypeResolver.databaseType(); |
| } |
| |
| public String escape(String arg) { |
| if (databaseTypeResolver.isMySQL()) { |
| return format("`%s`", arg); |
| } else if (databaseTypeResolver.isPostgreSQL()) { |
| return format("\"%s\"", arg); |
| } |
| return arg; |
| } |
| |
| public String formatValue(JdbcJavaType columnType, String value) { |
| return (columnType.isStringType() || columnType.isAnyDateType()) ? format("'%s'", value) : value; |
| } |
| |
| public String groupConcat(String arg) { |
| if (databaseTypeResolver.isMySQL()) { |
| return format("GROUP_CONCAT(%s)", arg); |
| } else if (databaseTypeResolver.isPostgreSQL()) { |
| // STRING_AGG only works with strings |
| return format("STRING_AGG(%s::varchar, ',')", arg); |
| } else { |
| throw new IllegalStateException("Database type is not supported for group concat " + databaseTypeResolver.databaseType()); |
| } |
| } |
| |
| public String limit(int count) { |
| return limit(count, 0); |
| } |
| |
| public String limit(int count, int offset) { |
| if (databaseTypeResolver.isMySQL()) { |
| return format("LIMIT %s,%s", offset, count); |
| } else if (databaseTypeResolver.isPostgreSQL()) { |
| return format("LIMIT %s OFFSET %s", count, offset); |
| } else { |
| throw new IllegalStateException("Database type is not supported for limit " + databaseTypeResolver.databaseType()); |
| } |
| } |
| |
| public String calcFoundRows() { |
| if (databaseTypeResolver.isMySQL()) { |
| return "SQL_CALC_FOUND_ROWS"; |
| } else { |
| return ""; |
| } |
| } |
| |
| public String countLastExecutedQueryResult(@NotNull String sql) { |
| if (databaseTypeResolver.isMySQL()) { |
| return "SELECT FOUND_ROWS()"; |
| } else { |
| return countQueryResult(sql); |
| } |
| } |
| |
| public String countQueryResult(@NotNull String sql) { |
| // Needs to remove the limit and offset |
| sql = sql.replaceAll("LIMIT \\d+", "").replaceAll("OFFSET \\d+", "").trim(); |
| return format("SELECT COUNT(*) FROM (%s) AS temp", sql); |
| } |
| |
| public String currentBusinessDate() { |
| if (databaseTypeResolver.isMySQL()) { |
| return format("DATE('%s')", DateUtils.getBusinessLocalDate().format(DateUtils.DEFAULT_DATE_FORMATTER)); |
| } else if (databaseTypeResolver.isPostgreSQL()) { |
| return format("DATE '%s'", DateUtils.getBusinessLocalDate().format(DateUtils.DEFAULT_DATE_FORMATTER)); |
| } else { |
| throw new IllegalStateException("Database type is not supported for current date " + databaseTypeResolver.databaseType()); |
| } |
| } |
| |
| public String currentTenantDate() { |
| if (databaseTypeResolver.isMySQL()) { |
| return format("DATE('%s')", DateUtils.getLocalDateOfTenant().format(DateUtils.DEFAULT_DATE_FORMATTER)); |
| } else if (databaseTypeResolver.isPostgreSQL()) { |
| return format("DATE '%s'", DateUtils.getLocalDateOfTenant().format(DateUtils.DEFAULT_DATE_FORMATTER)); |
| } else { |
| throw new IllegalStateException("Database type is not supported for current date " + databaseTypeResolver.databaseType()); |
| } |
| } |
| |
| public String currentTenantDateTime() { |
| if (databaseTypeResolver.isMySQL()) { |
| return format("TIMESTAMP('%s')", DateUtils.getLocalDateTimeOfSystem().format(DateUtils.DEFAULT_DATETIME_FORMATTER)); |
| } else if (databaseTypeResolver.isPostgreSQL()) { |
| return format("TIMESTAMP '%s'", DateUtils.getLocalDateTimeOfSystem().format(DateUtils.DEFAULT_DATETIME_FORMATTER)); |
| } else { |
| throw new IllegalStateException("Database type is not supported for current date time" + databaseTypeResolver.databaseType()); |
| } |
| } |
| |
| public String subDate(String date, String multiplier, String unit) { |
| if (databaseTypeResolver.isMySQL()) { |
| return format("DATE_SUB(%s, INTERVAL %s %s)", date, multiplier, unit); |
| } else if (databaseTypeResolver.isPostgreSQL()) { |
| return format("(%s::TIMESTAMP - %s * INTERVAL '1 %s')", date, multiplier, unit); |
| } else { |
| throw new IllegalStateException("Database type is not supported for subtracting date " + databaseTypeResolver.databaseType()); |
| } |
| } |
| |
| public String dateDiff(String date1, String date2) { |
| if (databaseTypeResolver.isMySQL()) { |
| return format("DATEDIFF(%s, %s)", date1, date2); |
| } else if (databaseTypeResolver.isPostgreSQL()) { |
| return format("EXTRACT(DAY FROM (%s::TIMESTAMP - %s::TIMESTAMP))", date1, date2); |
| } else { |
| throw new IllegalStateException("Database type is not supported for date diff " + databaseTypeResolver.databaseType()); |
| } |
| } |
| |
| public String castChar(String sql) { |
| if (databaseTypeResolver.isMySQL()) { |
| return format("CAST(%s AS CHAR)", sql); |
| } else if (databaseTypeResolver.isPostgreSQL()) { |
| return format("%s::CHAR", sql); |
| } else { |
| throw new IllegalStateException( |
| "Database type is not supported for casting to character " + databaseTypeResolver.databaseType()); |
| } |
| } |
| |
| public String castInteger(String sql) { |
| if (databaseTypeResolver.isMySQL()) { |
| return format("CAST(%s AS SIGNED INTEGER)", sql); |
| } else if (databaseTypeResolver.isPostgreSQL()) { |
| return format("%s::INTEGER", sql); |
| } else { |
| throw new IllegalStateException("Database type is not supported for casting to bigint " + databaseTypeResolver.databaseType()); |
| } |
| } |
| |
| public String currentSchema() { |
| if (databaseTypeResolver.isMySQL()) { |
| return "SCHEMA()"; |
| } else if (databaseTypeResolver.isPostgreSQL()) { |
| return "CURRENT_SCHEMA()"; |
| } else { |
| throw new IllegalStateException("Database type is not supported for current schema " + databaseTypeResolver.databaseType()); |
| } |
| } |
| |
| public String castJson(String sql) { |
| if (databaseTypeResolver.isMySQL()) { |
| return format("%s", sql); |
| } else if (databaseTypeResolver.isPostgreSQL()) { |
| return format("%s ::json", sql); |
| } else { |
| throw new IllegalStateException("Database type is not supported for casting to json " + databaseTypeResolver.databaseType()); |
| } |
| } |
| |
| public String alias(@NotNull String field, String alias) { |
| return Strings.isEmpty(alias) ? field : (alias + '.') + field; |
| } |
| |
| public String buildSelect(Collection<String> fields, String alias, boolean embedded) { |
| if (fields == null || fields.isEmpty()) { |
| return ""; |
| } |
| String select = ""; |
| if (!embedded) { |
| select = "SELECT "; |
| } |
| return select + fields.stream().map(e -> alias(escape(e), alias)).collect(Collectors.joining(", ")); |
| } |
| |
| public String buildFrom(String definition, String alias, boolean embedded) { |
| if (definition == null) { |
| return ""; |
| } |
| String from = ""; |
| if (!embedded) { |
| from = "FROM "; |
| } |
| return from + escape(definition) + (Strings.isEmpty(alias) ? "" : (" " + alias)); |
| } |
| |
| public String buildJoin(@NotNull String definition, String alias, @NotNull String fkCol, String refAlias, @NotNull String refCol, |
| String joinType) { |
| String join = Strings.isEmpty(joinType) ? "JOIN" : (joinType + " JOIN"); |
| alias = Strings.isEmpty(alias) ? "" : (" " + alias); |
| return format("%s %s%s ON %s = %s", join, escape(definition), alias, alias(escape(fkCol), alias), alias(escape(refCol), refAlias)); |
| } |
| |
| public String buildOrderBy(List<Sort.Order> orders, String alias, boolean embedded) { |
| if (orders == null || orders.isEmpty()) { |
| return ""; |
| } |
| String orderBy = ""; |
| if (!embedded) { |
| orderBy = "ORDER BY "; |
| } |
| return orderBy + orders.stream().map(e -> String.join(" ", alias(escape(e.getProperty()), alias), e.getDirection().name())) |
| .collect(Collectors.joining(", ")); |
| } |
| |
| public String buildInsert(@NotNull String definition, List<String> fields, Map<String, ResultsetColumnHeaderData> headers) { |
| if (fields == null || fields.isEmpty()) { |
| return ""; |
| } |
| return "INSERT INTO " + escape(definition) + '(' + fields.stream().map(this::escape).collect(Collectors.joining(", ")) |
| + ") VALUES (" + fields.stream().map(e -> decoratePlaceHolder(headers, e, "?")).collect(Collectors.joining(", ")) + ")"; |
| } |
| |
| public String buildUpdate(@NotNull String definition, List<String> fields, Map<String, ResultsetColumnHeaderData> headers) { |
| if (fields == null || fields.isEmpty()) { |
| return ""; |
| } |
| return "UPDATE " + escape(definition) + " SET " |
| + fields.stream().map(e -> escape(e) + " = " + decoratePlaceHolder(headers, e, "?")).collect(Collectors.joining(", ")); |
| } |
| |
| private String decoratePlaceHolder(Map<String, ResultsetColumnHeaderData> headers, String field, String placeHolder) { |
| DatabaseType dialect = getDialect(); |
| if (dialect.isPostgres()) { |
| ResultsetColumnHeaderData header = headers.get(field); |
| if (header != null) { |
| JdbcJavaType columnType = header.getColumnType(); |
| if (columnType.isJsonType()) { |
| return placeHolder + "::" + columnType.getJdbcName(dialect); |
| } |
| } |
| } |
| return placeHolder; |
| } |
| |
| public Long fetchPK(GeneratedKeyHolder keyHolder) { |
| return switch (getDialect()) { |
| case POSTGRESQL -> (Long) keyHolder.getKeys().get("id"); |
| case MYSQL -> { |
| // Mariadb |
| BigInteger generatedKey = (BigInteger) keyHolder.getKeys().get("insert_id"); |
| if (generatedKey == null) { |
| // Mysql |
| generatedKey = (BigInteger) keyHolder.getKeys().get("GENERATED_KEY"); |
| } |
| yield generatedKey.longValue(); |
| } |
| }; |
| } |
| } |
