All Downloads are FREE. Search and download functionalities are using the official Maven repository.
  • Log In
  • Register

    • org.noorm.jdbc.StatementBuilder Maven / Gradle / Ivy

    package org.noorm.jdbc;

import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;

/**
 * Helper class for JDBCProcedureProcessor and JDBCQueryProcessor for the textual creation of SQL statements.
 *
 * @author Ulf Pietruschka / [email protected]
 *         Date: 02.06.11
 *         Time: 16:31
 */
public class StatementBuilder {

	public static final String OLD_VERSION_APPENDIX = "_OLD";

	private static final String CALL_PREFIX = "{ call ";
	private static final String CALL_DELIM_1 = "(";
	private static final String CALL_DELIM_2 = ",";
	private static final String CALL_DELIM_3 = ")";
	private static final String CALL_ASG2 = " => ?";
	private static final String CALL_POSTFIX = " }";

	public String buildProcedureCall(final String pCallable,
                                     final String pOutParamName,
                                     final Map pInParameters) {

		final StringBuilder call = new StringBuilder();
		call.append(CALL_PREFIX).append(pCallable);
		String delim = CALL_DELIM_1;
		if (pOutParamName != null) {
            call.append(delim).append(pOutParamName).append(CALL_ASG2);
			delim = CALL_DELIM_2;
		}
		if (pInParameters != null) {
			final Map orderedParameters = new TreeMap<>(pInParameters);
			for (final String paramName : orderedParameters.keySet()) {
				final Object value = orderedParameters.get(paramName);
				if (value == null) {
					continue;
				}
				if (value instanceof String) {
					if ((value).equals("")) {
						continue;
					}
				}
				if (value instanceof byte[]) {
					if (((byte[]) value).length == 0) {
						continue;
					}
				}
                call.append(delim).append(paramName).append(CALL_ASG2);
				delim = CALL_DELIM_2;
			}
		}
		if (delim.equals(CALL_DELIM_2)) {
			// When no parameters are used, PL/SQL does not even expect empty brackets,
			// but nothing behind the callables name
			call.append(CALL_DELIM_3);
		}
		call.append(CALL_POSTFIX);
		return call.toString();
	}

    private static final String SELECT_PREFIX = "SELECT * FROM ";
    private static final String SELECT_LOCK = " FOR UPDATE";
    private static final String SELECT_IN_CLAUSE_START = "(";
    private static final String SELECT_IN_CLAUSE_END = ")";
    private static final String SELECT_IN_CLAUSE_DELIM = ",";

    public String buildSQLStatement(final String pTableName,
                                    final Map pInParameters,
                                    final boolean pUseNamedParameters,
                                    final boolean pAcquireLock) {

        final StringBuilder pSQLStatement = new StringBuilder();
        if (!pTableName.equals(pTableName.toUpperCase())) {
            pSQLStatement.append(SELECT_PREFIX).append("\"".concat(pTableName).concat("\""));
        } else {
            pSQLStatement.append(SELECT_PREFIX).append(pTableName);
        }
        final StringBuilder sqlStatement = buildQueryWhereClause(pInParameters, pUseNamedParameters, pSQLStatement);
        if (pAcquireLock) {
            sqlStatement.append(SELECT_LOCK);
        }
        return sqlStatement.toString();
    }

	private static final String INSERT_PREFIX = "INSERT INTO ";
	private static final String INSERT_DELIM_1 = " (";
	private static final String INSERT_DELIM_2 = ",";
	private static final String INSERT_VALUES = ") VALUES ";
	private static final String INSERT_NEXT_PK_VAL = ".NEXTVAL";
	private static final String INSERT_DELIM_3 = ")";

	public String buildInsert(final IBean pBean, final Map pField2ParameterIndex, final boolean pUseNamedParameters) {

        final String tableName = pBean.getTableName();
        final String[] primaryKeyColumnNames = pBean.getPrimaryKeyColumnNames();
        final String sequenceName = pBean.getSequenceName();
        final boolean useInlineSequenceValueGeneration = pBean.useInlineSequenceValueGeneration();
        final Field[] fields = BeanMetaDataUtil.getDeclaredFieldsInclParent(pBean.getClass());
		final StringBuilder insert = new StringBuilder();
        if (pBean.isTableNameCaseSensitive()) {
            insert.append(INSERT_PREFIX).append("\"".concat(tableName).concat("\""));
        } else {
            insert.append(INSERT_PREFIX).append(tableName);
        }
		String delim = INSERT_DELIM_1;
        for (final Field field : fields) {
            final JDBCColumn colAnn = BeanMetaDataUtil.getJDBCColumnAnnotation(field);
            if (colAnn != null && colAnn.insertable()) {
                if (colAnn.caseSensitiveName()) {
                    insert.append(delim).append("\"".concat(colAnn.name()).concat("\""));
                } else {
                    insert.append(delim).append(colAnn.name());
                }
                delim = INSERT_DELIM_2;
            }
        }
		insert.append(INSERT_VALUES);
		delim = INSERT_DELIM_1;

        Integer parameterIndex = 1;
        for (final Field field : fields) {
            final JDBCColumn colAnn = BeanMetaDataUtil.getJDBCColumnAnnotation(field);
            if (colAnn != null && colAnn.insertable()) {
                insert.append(delim);
                boolean isPKColumn = false;
                for (final String pkColumnName : primaryKeyColumnNames) {
                    if (colAnn.name().equals(pkColumnName)) {
                        isPKColumn = true;
                    }
                }
                if (isPKColumn && sequenceName != null && !sequenceName.isEmpty() && useInlineSequenceValueGeneration) {
                    if (!sequenceName.equals(sequenceName.toUpperCase())) {
                        insert.append("\"".concat(sequenceName).concat("\"")).append(INSERT_NEXT_PK_VAL);
                    } else {
                        insert.append(sequenceName).append(INSERT_NEXT_PK_VAL);
                    }
                    delim = INSERT_DELIM_2;
                } else {
                    pField2ParameterIndex.put(colAnn.name(), parameterIndex++);
                    if (pUseNamedParameters) {
                        insert.append(ASG).append(colAnn.name());
                    } else {
                        insert.append(ASG2);
                    }
                    delim = INSERT_DELIM_2;
                }
            }
		}
		insert.append(INSERT_DELIM_3);
		return insert.toString();
	}

	private static final String UPDATE_PREFIX = "UPDATE ";
	private static final String UPDATE_DELIM_1 = " SET ";
	private static final String UPDATE_DELIM_2 = ",";
    private static final String UPDATE_NULL = "NULL";

	public String buildUpdate(final IBean pBean,
                              final boolean pUseOptLockFullRowCompare,
                              final Map pField2ParameterIndex,
                              final boolean pUseNamedParameters) {

        final String tableName = pBean.getTableName();
        final String[] primaryKeyColumnNames = pBean.getPrimaryKeyColumnNames();
        final Field[] fields = BeanMetaDataUtil.getDeclaredFieldsInclParent(pBean.getClass());
		final StringBuilder update = new StringBuilder();
        if (pBean.isTableNameCaseSensitive()) {
            update.append(UPDATE_PREFIX).append("\"".concat(tableName).concat("\""));
        } else {
            update.append(UPDATE_PREFIX).append(tableName);
        }
		String delim = UPDATE_DELIM_1;

        Integer parameterIndex = 1;
        for (final Field field : fields) {
            final JDBCColumn colAnn = BeanMetaDataUtil.getJDBCColumnAnnotation(field);
            if (colAnn != null && colAnn.updatable()) {
                boolean isPKColumn = false;
                for (final String pkColumnName : primaryKeyColumnNames) {
                    if (colAnn.name().equals(pkColumnName)) {
                        isPKColumn = true;
                    }
                }
                if (!isPKColumn) {
                    String fieldName = colAnn.name();
                    if (colAnn.caseSensitiveName()) {
                        fieldName = "\"".concat(colAnn.name()).concat("\"");
                    }
                    pField2ParameterIndex.put(colAnn.name(), parameterIndex++);
                    update.append(delim).append(fieldName).append(EQUALS);
                    if (pUseNamedParameters) {
                        update.append(ASG).append(colAnn.name());
                    } else {
                        update.append(ASG2);
                    }
                    delim = UPDATE_DELIM_2;
                }
            }
		}
        buildWhereClause(pBean, update, pUseOptLockFullRowCompare,
                pField2ParameterIndex, parameterIndex, pUseNamedParameters);
		return update.toString();
	}

	private static final String DELETE_PREFIX = "DELETE FROM ";

	public String buildDelete(final IBean pBean,
                              final boolean pUseOptLockFullRowCompare,
                              final Map pField2ParameterIndex,
                              final boolean pUseNamedParameters) {

        final String tableName = pBean.getTableName();
		final StringBuilder delete = new StringBuilder();
        if (pBean.isTableNameCaseSensitive()) {
            delete.append(DELETE_PREFIX).append("\"".concat(tableName).concat("\""));
        } else {
            delete.append(DELETE_PREFIX).append(tableName);
        }
        Integer parameterIndex = 1;
        buildWhereClause(pBean, delete, pUseOptLockFullRowCompare,
                pField2ParameterIndex, parameterIndex, pUseNamedParameters);
		return delete.toString();
	}

    public String buildDelete(final String pTableName,
                              final Map pInParameters,
                              final boolean pUseNamedParameters) {

        final StringBuilder pSQLStatement = new StringBuilder();
        if (!pTableName.equals(pTableName.toUpperCase())) {
            pSQLStatement.append(DELETE_PREFIX).append("\"".concat(pTableName).concat("\""));
        } else {
            pSQLStatement.append(DELETE_PREFIX).append(pTableName);
        }
        return buildQueryWhereClause(pInParameters, pUseNamedParameters, pSQLStatement).toString();
    }

    public String buildUpdate(final String pTableName,
                              final Map pUpdateParameters,
                              final Map pInParameters,
                              final boolean pUseNamedParameters) {

        final StringBuilder pSQLStatement = new StringBuilder();
        if (!pTableName.equals(pTableName.toUpperCase())) {
            pSQLStatement.append(UPDATE_PREFIX).append("\"".concat(pTableName).concat("\""));
        } else {
            pSQLStatement.append(UPDATE_PREFIX).append(pTableName);
        }
        String delimiter = UPDATE_DELIM_1;
        for (final String updateColumn : pUpdateParameters.keySet()) {
            pSQLStatement.append(delimiter);
            if (pUpdateParameters.get(updateColumn) == null) {
                pSQLStatement.append(updateColumn).append(EQUALS).append(UPDATE_NULL);
            } else {
                if (pUseNamedParameters) {
                    pSQLStatement.append(updateColumn).append(EQUALS).append(ASG).append(updateColumn);
                } else {
                    pSQLStatement.append(updateColumn).append(EQUALS).append(ASG2);
                }
            }
            delimiter = UPDATE_DELIM_2;
        }
        return buildQueryWhereClause(pInParameters, pUseNamedParameters, pSQLStatement).toString();
    }

    private StringBuilder buildQueryWhereClause(final Map pInParameters,
                                                final boolean pUseNamedParameters,
                                                final StringBuilder pSQLStatement) {

        if (pInParameters.size() > 0) {
            String delim = WHERE;
            final Map orderedParameters = new TreeMap<>(pInParameters);
            for (final QueryColumn queryColumn : orderedParameters.keySet()) {
                final Object value = orderedParameters.get(queryColumn);
                boolean addWHERECondition = false;
                if (value != null || queryColumn.getOperator().isUnary()) {
                    addWHERECondition = true;
                    if (value instanceof List) {
                        if (((List) value).size() == 0) {
                            addWHERECondition = false;
                        }
                    }
                }
                if (addWHERECondition) {
                    pSQLStatement.append(delim);
                    if (queryColumn.getOperator().getOperatorName() == Operator.Name.CUSTOM) {
                        pSQLStatement.append(queryColumn.getCustomExpression());
                    } else {
                        pSQLStatement.append(queryColumn.getColumnName());
                        pSQLStatement.append(queryColumn.getOperator().getOperatorSyntax());
                        if (!queryColumn.getOperator().isUnary()) {
                            if (value instanceof List) {
                                final List inClauseValues = ((List) value);
                                String inClauseDelim = SELECT_IN_CLAUSE_START;
                                for (int i = 0; i < inClauseValues.size(); i++) {
                                    pSQLStatement.append(inClauseDelim);
                                    if (pUseNamedParameters) {
                                        pSQLStatement.append(ASG).append(queryColumn.getColumnName());
                                    } else {
                                        pSQLStatement.append(ASG2);
                                    }
                                    inClauseDelim = SELECT_IN_CLAUSE_DELIM;
                                }
                                pSQLStatement.append(SELECT_IN_CLAUSE_END);
                            } else {
                                if (pUseNamedParameters) {
                                    pSQLStatement.append(ASG).append(queryColumn.getColumnName());
                                } else {
                                    pSQLStatement.append(ASG2);
                                }
                            }
                        }
                    }
                    delim = AND;
                }
            }
        }
        return pSQLStatement;
    }

    private static final String WHERE = " WHERE ";
    private static final String AND = " AND ";
    private static final String EQUALS = " = ";
    private static final String ASG = ":";
    private static final String ASG2 = "?";
    private static final String IS_NULL = " IS NULL ";

    private StringBuilder buildWhereClause(final IBean pBean,
                                           final StringBuilder pDML,
                                           final boolean pUseOptLockFullRowCompare,
                                           final Map pField2ParameterIndex,
                                           Integer pParameterIndex,
                                           final boolean pUseNamedParameters) {

        final String[] primaryKeyColumnNames = pBean.getPrimaryKeyColumnNames();
        final String versionColumnName = pBean.getVersionColumnName();
        final BeanMapper mapper = BeanMapper.getInstance();
        final Map fieldMap = mapper.toMap(pBean);
        final Field[] fields = BeanMetaDataUtil.getDeclaredFieldsInclParent(pBean.getClass());
        final List caseSensitiveFields = new ArrayList<>();
        for (final Field field : fields) {
            final JDBCColumn colAnn = BeanMetaDataUtil.getJDBCColumnAnnotation(field);
            if (colAnn != null && colAnn.caseSensitiveName()) {
                caseSensitiveFields.add(colAnn.name());
            }
        }

        String delim = WHERE;
        for (final String pkColumnName : primaryKeyColumnNames) {
            pDML.append(delim);
            if (caseSensitiveFields.contains(pkColumnName)) {
                pDML.append("\"".concat(pkColumnName).concat("\""));
            } else {
                pDML.append(pkColumnName);
            }
            pDML.append(EQUALS);
            pField2ParameterIndex.put(pkColumnName, pParameterIndex++);
            if (pUseNamedParameters) {
                pDML.append(ASG).append(pkColumnName);
            } else {
                pDML.append(ASG2);
            }
            delim = AND;
        }
        if (versionColumnName != null && !versionColumnName.isEmpty()) {
            pDML.append(delim);
            if (caseSensitiveFields.contains(versionColumnName)) {
                pDML.append("\"".concat(versionColumnName).concat("\""));
            } else {
                pDML.append(versionColumnName);
            }
            pDML.append(EQUALS);
            final String parameterName = versionColumnName.concat(OLD_VERSION_APPENDIX);
            pField2ParameterIndex.put(parameterName, pParameterIndex++);
            if (pUseNamedParameters) {
                pDML.append(ASG).append(parameterName);
            } else {
                pDML.append(ASG2);
            }
        }
        if (pUseOptLockFullRowCompare) {
            for (final String fieldName : fieldMap.keySet()) {
                boolean isPKColumn = false;
                for (final String pkColumnName : primaryKeyColumnNames) {
                    if (fieldName.equals(pkColumnName)) {
                        isPKColumn = true;
                    }
                }
                if (!isPKColumn) {
                    pDML.append(delim);
                    if (caseSensitiveFields.contains(fieldName)) {
                        pDML.append("\"".concat(fieldName).concat("\""));
                    } else {
                        pDML.append(fieldName);
                    }
                    if (fieldMap.get(fieldName) != null) {
                        final String parameterName = fieldName.concat(OLD_VERSION_APPENDIX);
                        pField2ParameterIndex.put(parameterName, pParameterIndex++);
                        pDML.append(EQUALS);
                        if (pUseNamedParameters) {
                            pDML.append(ASG).append(parameterName);
                        } else {
                            pDML.append(ASG2);
                        }
                    } else {
                        pDML.append(IS_NULL);
                    }
                }
            }
        }
        return pDML;
    }
}
    
    
    
    

    




    
    
    © 2015 - 2025 Weber Informatics LLC | Privacy Policy