如何在Spring Boot项目中避免硬编码QueryDSL-SQL的数据库配置？

我正在开发一个使用QueryDSL-SQL库的Spring Boot项目，根据官方文档，该库的数据库配置需添加在pom.xml中。但这是生产环境项目，不能在pom.xml中配置数据库信息，想咨询是否可通过application.properties文件或@Configuration类配置？另外因需使用SQL窗口函数，无法改用QueryDSL-JPA。当前项目的pom.xml代码如下：

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.5.6</version>
        <relativePath /> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.lion</groupId>
    <artifactId>querydsl-bookstore-demo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>spring-data-jpa-querydsl</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>11</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>42.5.0</version>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>com.querydsl</groupId>
            <artifactId>querydsl-sql</artifactId>
            <version>5.0.0</version>
        </dependency>

        <dependency>
            <groupId>com.querydsl</groupId>
            <artifactId>querydsl-sql-codegen</artifactId>
            <version>5.0.0</version>
            <scope>provided</scope>
        </dependency>

    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
            <plugin>
                <groupId>com.querydsl</groupId>
                <artifactId>querydsl-maven-plugin</artifactId>
                <version>5.0.0</version>
                <executions>
                    <execution>
                        <goals>
                            <goal>export</goal>
                        </goals>
                    </execution>
                </executions>
                <configuration>
                    <!-- <jdbcDriver>org.postgresql.Driver</jdbcDriver>
                    <jdbcUrl>jdbc:postgresql://127.0.0.1:5432/test</jdbcUrl> -->
                    <packageName>com.lion.bookstore</packageName>
                    <targetFolder>${project.basedir}/target/generated-sources/java</targetFolder>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

解决方案 #

一、代码生成阶段（Q类生成）的数据库配置优化 #

QueryDSL-Maven插件用于生成实体对应的Q类，此阶段需要数据库连接信息，但完全无需硬编码在pom.xml中，以下是两种安全可行的方案：

1. 通过Maven属性引用外部配置

将数据库配置放在Maven的settings.xml或系统环境变量中，在pom.xml中通过占位符引用：

<plugin>
    <groupId>com.querydsl</groupId>
    <artifactId>querydsl-maven-plugin</artifactId>
    <version>5.0.0</version>
    <executions>
        <execution>
            <goals>
                <goal>export</goal>
            </goals>
        </execution>
    </executions>
    <configuration>
        <jdbcDriver>${db.driver}</jdbcDriver>
        <jdbcUrl>${db.url}</jdbcUrl>
        <jdbcUser>${db.username}</jdbcUser>
        <jdbcPassword>${db.password}</jdbcPassword>
        <packageName>com.lion.bookstore</packageName>
        <targetFolder>${project.basedir}/target/generated-sources/java</targetFolder>
    </configuration>
</plugin>

在settings.xml的<profiles>节点中添加生产环境配置：

<profile>
    <id>prod</id>
    <properties>
        <db.driver>org.postgresql.Driver</db.driver>
        <db.url>jdbc:postgresql://prod-host:5432/prod-db</db.url>
        <db.username>prod-user</db.username>
        <db.password>prod-pass</db.password>
    </properties>
</profile>

执行Maven命令时指定环境profile：mvn clean install -Pprod，也可通过CI/CD流水线传递这些属性值。

2. 改用Java代码生成Q类（完全脱离Maven插件配置）

若不想依赖Maven插件的配置体系，可编写Java代码直接读取Spring的application.properties来生成Q类：

import com.querydsl.sql.codegen.MetaDataExporter;
import com.querydsl.sql.postgresql.PostgreSQLTemplates;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.support.PropertiesLoaderUtils;

import javax.sql.DataSource;
import java.io.File;
import java.sql.Connection;
import java.util.Properties;

public class QueryDSLCodeGenerator {
    public static void main(String[] args) throws Exception {
        // 读取application.properties配置
        Properties props = PropertiesLoaderUtils.loadProperties(new ClassPathResource("application.properties"));
        
        // 构建数据源
        DataSource dataSource = DataSourceBuilder.create()
                .driverClassName(props.getProperty("spring.datasource.driver-class-name"))
                .url(props.getProperty("spring.datasource.url"))
                .username(props.getProperty("spring.datasource.username"))
                .password(props.getProperty("spring.datasource.password"))
                .build();

        try (Connection conn = dataSource.getConnection()) {
            MetaDataExporter exporter = new MetaDataExporter();
            exporter.setPackageName("com.lion.bookstore");
            exporter.setTargetFolder(new File("target/generated-sources/java"));
            exporter.setTemplates(new PostgreSQLTemplates());
            exporter.export(conn.getMetaData());
        }
    }
}

运行该main方法即可生成Q类，也可将其集成到Maven的compile阶段作为自定义执行任务。

二、运行时QueryDSL-SQL的配置（通过@Configuration和application.properties） #

运行时的QueryDSL-SQL核心组件完全可以通过Spring配置类和application.properties来管理，与pom.xml彻底解耦：

1. 在application.properties中配置数据库连接信息：

spring.datasource.driver-class-name=org.postgresql.Driver
spring.datasource.url=jdbc:postgresql://your-prod-host:5432/your-prod-db
spring.datasource.username=prod-user
spring.datasource.password=prod-password

2. 编写Spring配置类创建SQLQueryFactory：

import com.querydsl.sql.PostgreSQLTemplates;
import com.querydsl.sql.SQLQueryFactory;
import com.querydsl.sql.SQLTemplates;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;

@Configuration
public class QueryDSLConfig {

    @Bean
    public SQLQueryFactory sqlQueryFactory(DataSource dataSource) {
        SQLTemplates templates = PostgreSQLTemplates.builder().build();
        return new SQLQueryFactory(templates, dataSource);
    }
}

3. 在业务代码中注入SQLQueryFactory使用（含窗口函数示例）：

import com.querydsl.sql.SQLQueryFactory;
import org.springframework.stereotype.Service;
import java.util.List;

@Service
public class BookService {
    private final SQLQueryFactory queryFactory;
    private final QBook book = QBook.book;

    public BookService(SQLQueryFactory queryFactory) {
        this.queryFactory = queryFactory;
    }

    // 使用窗口函数实现行号查询
    public List<Object[]> getBooksWithRowNumber() {
        return queryFactory.select(book.id, book.name, 
                        com.querydsl.sql.SQLExpressions.rowNumber()
                                .over()
                                .orderBy(book.price)
                                .as("row_num"))
                .from(book)
                .fetch();
    }
}

以上方案既满足了生产环境下数据库配置的安全性要求，也保留了QueryDSL-SQL对窗口函数的支持能力。

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