QueryDSL生成原生Oracle查询格式不符，如何获取预期物化查询

Let's break down the issue and fix it step by step. You're trying to generate a native Oracle query that matches your expected format, but QueryDSL is outputting camelCase table/column names instead of the uppercase underscore format you need. Plus, the table alias and account number value don't match your target query.

Expected vs. Generated Query #

Expected Query:

select test.TEST_KEY from TEST_TABLE test where test.TEST_CODE = 'TEST_01' and test.TEST_ACCOUNT_NUMBER = '001' and test.POSTED_UTC_DATE between timestamp '2020-06-19 23:59:59' and timestamp '2020-06-19 23:59:59'

Current Generated Query:

select testTable.testKey from testTable where testTable.testCode = 'TEST_01' and testTable.testAccountNumber = '0000124001' and testTable.postedUtcDate between timestamp '2020-06-19 23:59:59' and timestamp '2020-06-19 23:59:59'

Your Current Implementation #

public String getTestResults(DataDto dataDto) {
    SQLTemplates templates= OracleTemplates.builder().printSchema().build();
    Configuration configuration=new Configuration(templates);
    configuration.setUseLiterals(true);
    PathBuilder<?> entityPath = new PathBuilder<>(getEntityClass(), getEntityName());
    SQLQuery<Object> sqlQuery= (SQLQuery<Object>) new SQLQuery(configuration)
            .select(entityPath.getString(getColumnMap().get("TEST_KEY")))
            .from(entityPath)
            .where(buildCondition(dataDto).build());
    sqlQuery.setUseLiterals(true);
    String query=sqlQuery.getSQL().getSQL();
    return query;
}

You mentioned you've checked QueryDSL docs and references but haven't resolved this—let's fix each discrepancy one by one.

Fixes to Generate the Exact Query #

1. Force Uppercase Identifiers for Oracle

Oracle treats unquoted identifiers as uppercase, so we need to configure QueryDSL to output identifiers in uppercase underscore format instead of camelCase. Modify your SQLTemplates setup:

// Use Oracle's default template which handles uppercase correctly
SQLTemplates templates = OracleTemplates.DEFAULT;
// Alternatively, for custom settings:
// SQLTemplates templates = OracleTemplates.builder()
//         .printSchema()
//         .quoteIdentifiers(false) // Disable quoting so Oracle uses uppercase
//         .build();

2. Set the Correct Table Alias

Add the as() method to your from() clause to use the test alias you need:

.from(entityPath.as("test"))

3. Map CamelCase Properties to Uppercase Underscore Columns

Since you're using PathBuilder, add a custom naming strategy to convert camelCase property names to the uppercase underscore column names your database uses:

import org.apache.commons.lang3.StringUtils;
import com.querydsl.core.DefaultNamingStrategy;

configuration.setNamingStrategy(new DefaultNamingStrategy() {
    @Override
    public String getColumnName(String property) {
        // Convert camelCase to UPPERCASE_UNDERSCORE format
        String[] camelParts = StringUtils.splitByCharacterTypeCamelCase(property);
        return StringUtils.join(camelParts, "_").toUpperCase();
    }
});

Now, when you reference entityPath.getString("testKey"), it will automatically map to TEST_KEY in the generated SQL.

4. Fix the Account Number Value

Double-check your buildCondition(dataDto) method—currently it's passing 0000124001 instead of 001. Ensure the value pulled from dataDto is the correct 001 string (verify there's no unintended formatting or data conversion happening).

Adjusted Full Code Example #

import org.apache.commons.lang3.StringUtils;
import com.querydsl.core.DefaultNamingStrategy;
import com.querydsl.core.configuration.Configuration;
import com.querydsl.core.sql.OracleTemplates;
import com.querydsl.core.sql.SQLQuery;
import com.querydsl.core.sql.SQLTemplates;
import com.querydsl.core.types.PathBuilder;

public String getTestResults(DataDto dataDto) {
    // Configure Oracle-specific templates for uppercase identifiers
    SQLTemplates templates = OracleTemplates.DEFAULT;
    Configuration configuration = new Configuration(templates);
    configuration.setUseLiterals(true);

    // Naming strategy to convert camelCase properties to uppercase underscore columns
    configuration.setNamingStrategy(new DefaultNamingStrategy() {
        @Override
        public String getColumnName(String property) {
            String[] camelParts = StringUtils.splitByCharacterTypeCamelCase(property);
            return StringUtils.join(camelParts, "_").toUpperCase();
        }
    });

    PathBuilder<?> entityPath = new PathBuilder<>(getEntityClass(), getEntityName());
    SQLQuery<Object> sqlQuery = (SQLQuery<Object>) new SQLQuery(configuration)
            .select(entityPath.getString("testKey")) // Maps to TEST_KEY via naming strategy
            .from(entityPath.as("test")) // Set table alias to "test"
            .where(buildCondition(dataDto).build());

    sqlQuery.setUseLiterals(true);
    return sqlQuery.getSQL().getSQL();
}

Bonus: Use QueryDSL APT-Generated Q Classes (Recommended) #

If you're not already using QueryDSL's Annotation Processing Tool (APT) to generate Q classes, you should start. These classes auto-map your database tables/columns correctly, eliminating manual PathBuilder setup. For example:

// Assuming QTestTable is generated by APT
QTestTable test = QTestTable.testTable.as("test");
SQLQuery<Object> sqlQuery = new SQLQuery(configuration)
        .select(test.testKey)
        .from(test)
        .where(test.testCode.eq("TEST_01")
                .and(test.testAccountNumber.eq("001"))
                .and(test.postedUtcDate.between(
                        Timestamp.valueOf("2020-06-19 23:59:59"),
                        Timestamp.valueOf("2020-06-19 23:59:59"))));

This approach is more maintainable and less error-prone than manual PathBuilder configuration.

内容的提问来源于stack exchange，提问作者Himanshu Upadhyay