Spring Data JPA+QueryDSL实现数据库动态表名

一、Spring Boot集成Spring Data JPA+QueryDSL

1、使用maven引入相关依赖

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>        
        <!-- 使用QueryDSL查询框架 -->
        <dependency>
            <groupId>com.querydsl</groupId>
            <artifactId>querydsl-jpa</artifactId>
            <version>4.2.1</version>
        </dependency>
        <dependency>
            <groupId>com.querydsl</groupId>
            <artifactId>querydsl-apt</artifactId>
            <version>4.2.1</version>
        </dependency>
        <dependency>
            <groupId>com.querydsl</groupId>
            <artifactId>querydsl-sql</artifactId>
            <version>4.2.1</version>
        </dependency>

2、添加QueryDSL插件支持

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
            <!-- 添加QueryDSL插件支持 -->
            <plugin>
                <groupId>com.mysema.maven</groupId>
                <artifactId>apt-maven-plugin</artifactId>
                <version>1.1.3</version>
                <executions>
                    <execution>
                        <goals>
                            <goal>process</goal>
                        </goals>
                        <configuration>
                            <outputDirectory>target/generated-sources/java</outputDirectory>
                            <processor>com.querydsl.apt.jpa.JPAAnnotationProcessor</processor>
                        </configuration>
                    </execution>
                </executions>
            </plugin>
        </plugins>
    </build>

3、对于项目中加了@Entity注解的实体类，编译之后都会在target目录下生成对应的Q类，如下图所示：

二、使用Querying SQL实现数据库动态表名

        一般情况下，我们使用Querying JPA生成的hql语句已经基本能完成所有的数据库操作，今天碰到了要使用动态表名，发现Querying JPA实现不了，又不想手写sql语句。查看了QueryDSL的官方文档http://www.querydsl.com，终于找到了一种解决方法，那就是使用Querying SQL。

添加配置类

@Configuration
public class QueryFactoryConfig {

    @Bean
    public JPAQueryFactory jpaQueryFactory(EntityManager entityManager) {
        return new JPAQueryFactory(entityManager);
    }

    @Bean
    public SQLQueryFactory sqlQueryFactory(DataSource dataSource) {
        SQLTemplates template = MySQLTemplates.builder().printSchema().build();
        com.querydsl.sql.Configuration configuration = new com.querydsl.sql.Configuration(template);
        // 启用文字的直接序列化，打印出完整的sql语句
        configuration.setUseLiterals(true);
        return new SQLQueryFactory(configuration, dataSource);
    }

}

一般只要用到JPAQueryFactory，这里我们使用SQLQueryFactory来实现数据库动态表名。

实现方式如下：

@Slf4j
@Repository
public class FileDaoImpl implements FileDao {

    private PathBuilder id = new PathBuilder<>(Long.class, "id");

    private PathBuilder gmtCreated = new PathBuilder<>(Long.class, "gmt_created");

    private PathBuilder gmtModified = new PathBuilder<>(Long.class, "gmt_modified");

    private PathBuilder createdBy = new PathBuilder<>(String.class, "created_by");

    private PathBuilder modifiedBy = new PathBuilder<>(String.class, "modified_by");

    private PathBuilder storePath = new PathBuilder<>(String.class, "store_path");

    private PathBuilder webServerUrl = new PathBuilder<>(String.class, "web_server_url");

    private PathBuilder fileName = new PathBuilder<>(String.class, "file_name");

    private PathBuilder compression = new PathBuilder<>(Boolean.class, "compression");

    private PathBuilder contentType = new PathBuilder<>(String.class, "content_type");

    private PathBuilder fileExtension = new PathBuilder<>(String.class, "file_extension");

    private PathBuilder fileSize = new PathBuilder<>(Long.class, "file_size");

    private final SQLQueryFactory sqlQueryFactory;

    @Autowired
    public FileDaoImpl(SQLQueryFactory sqlQueryFactory) {
        this.sqlQueryFactory = sqlQueryFactory;
    }

    @Override
    public File findById(Long fileId) {
        QFile file = QFile.file;
        RelationalPath<File> table = new RelationalPathBase<>(File.class, QFile.file.getMetadata().getName(), "quantization", "file");
        Predicate predicate = QFile.file.id.eq(fileId);
        SQLQuery<File> sqlQuery = sqlQueryFactory.select(Projections.bean(
                File.class,
                id,
                compression,
                gmtCreated.as(file.gmtCreated.getMetadata().getName()),
                gmtModified.as(file.gmtModified.getMetadata().getName()),
                createdBy.as(file.createdBy.getMetadata().getName()),
                modifiedBy.as(file.modifiedBy.getMetadata().getName()),
                storePath.as(file.storePath.getMetadata().getName()),
                webServerUrl.as(file.webServerUrl.getMetadata().getName()),
                fileName.as(file.fileName.getMetadata().getName()),
                contentType.as(file.contentType.getMetadata().getName()),
                fileExtension.as(file.fileExtension.getMetadata().getName()),
                fileSize.as(file.fileSize.getMetadata().getName())
        ))
                .from(table)
                .where(predicate);
        log.info(sqlQuery.getSQL().getSQL());
        return sqlQuery.fetchOne();
    }

    @Override
    public Long save(File file) {
        RelationalPath table = new RelationalPathBase<>(File.class, QFile.file.getMetadata().getName(), "quantization", "file");
        SQLInsertClause sqlInsertClause = sqlQueryFactory.insert(table)
                .columns(gmtCreated, gmtModified, createdBy, modifiedBy, storePath, webServerUrl, fileName, compression, contentType, fileExtension, fileSize)
                .values(System.currentTimeMillis(), System.currentTimeMillis(), JwtUtil.getUsername(), JwtUtil.getUsername(),
                        file.getStorePath(), file.getWebServerUrl(), file.getFileName(), file.getCompression(), file.getContentType(),
                        file.getFileExtension(), file.getFileSize());
        log.info(sqlInsertClause.getSQL().get(0).getSQL());
        return sqlInsertClause.executeWithKey(Long.class);
    }

}

其中数据库动态表名主要是用到该方法

RelationalPathBase(Class<? extends T> type, String variable, String schema, String table)

type：对应实体类的字节码文件

variable：表的别名

schema：数据库名字

table：表名