QueryDSL配置

weixin_46373361

已于 2023-06-02 10:02:02 修改

阅读量677

点赞数

文章标签： java 开发语言

于 2023-04-28 10:54:40 首次发布

本文链接：https://blog.csdn.net/weixin_46373361/article/details/130421309

版权

文章介绍了如何在SpringBoot项目中配置QueryDSL，包括maven依赖、Apt编译插件设置、JPA和SQL查询配置类的创建，以及Repository接口的自定义方法。此外，还展示了实体类的映射和QueryDSLSQL操作示例。

摘要由CSDN通过智能技术生成

QueryDSL配置

1：maven配置-

 <dependency>
      <groupId>com.querydsl</groupId>
      <artifactId>querydsl-jpa</artifactId>
      <version>5.0.0</version>
 </dependency>
 <dependency>
      <groupId>com.querydsl</groupId>
      <artifactId>querydsl-sql</artifactId>
      <version>5.0.0</version>
 </dependency>
 <dependency>
      <groupId>com.querydsl</groupId>
      <artifactId>querydsl-apt</artifactId>
      <version>5.0.0</version>
 </dependency>

2：maven配置

<build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>

            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-surefire-plugin</artifactId>
                <version>2.22.1</version>
                <configuration>
                    <skipTests>true</skipTests>
                </configuration>
            </plugin>
            <!--apt编译插件-->
            <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>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-resources-plugin</artifactId>
                <configuration>
                    <encoding>UTF-8</encoding>
                    <nonFilteredFileExtensions>
                        <nonFilteredFileExtension>xlsx</nonFilteredFileExtension>
                        <nonFilteredFileExtension>xls</nonFilteredFileExtension>
                    </nonFilteredFileExtensions>
                </configuration>
            </plugin>
            <!--打包去掉jar包内的配置文件-->
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-jar-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>application.yml</exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>

3：配置类JpaQueryConfig

@Configuration
public class JpaQueryConfig {
    @Bean
    public JPAQueryFactory jpaQuery(EntityManager entityManager) {
        return new JPAQueryFactory(entityManager);
    }
}

4：配置类

@Configuration
public class SqlQueryConfig {
    @Bean
    public SQLQueryFactory sqlQueryFactory(DataSource dataSource){
        SQLTemplates sqlTemplates = MySQLTemplates.builder().printSchema().build();
        com.querydsl.sql.Configuration configuration = new com.querydsl.sql.Configuration(sqlTemplates);
        configuration.setUseLiterals(true);
        return new SQLQueryFactory(configuration,dataSource);
    }
}

5：application.yml 看着改

server:
  port: 8080
spring:
  application:
    name: spring-boot-jpa-hikari
  jpa:
    #database: mysql
    show-sql: true
    #generate-ddl: true
    #database-platform: org.hibernate.dialect.MySQL5InnoDBDialect
    hibernate:
      ddl-auto: update
    properties:
      hibernate:
        format_sql: true
  datasource:
    url: jdbc:mysql://localhost:3306/db2022?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8&useSSL=false
    username: root
    password: 123456
    driver-class-name: com.mysql.cj.jdbc.Driver
    type: com.zaxxer.hikari.HikariDataSource

    hikari:
      auto-commit: true
      minimum-idle: 2
      idle-timeout: 60000
      connection-timeout: 30000
      max-lifetime: 1800000
      pool-name: DatebookHikariCP
      maximum-pool-size: 5
logging:
  level:
    root: error

6：repository类根据entity配置JpaRepository<T,ID> ID是主键类型，（JPA语法，可用），自定义方法。

public interface UserRepository extends JpaRepository<User,Integer> {
    @Transactional
    @Modifying(clearAutomatically = true)
    @Query(value = "update user set username=?1 where id=?2",nativeQuery = true)
    void updateUser(String name, Integer id);

    @Transactional
    //@Modifying(clearAutomatically = true)
    @Query(value = "select username from user where id=?1",nativeQuery = true)
    String findUserNameById(Integer id);

    @Transactional
    @Modifying(clearAutomatically = true)//Jpa底层实现会有一级缓存，也就是在更新完数据库后，如果后面去用这个对象，你再去查这个对象，这个对象是在一级缓存，但是并没有跟数据库同步，此时使用clearAutomatically=true，就会刷新Hibernate的一级缓存， 否则在同一接口中，更新一个对象，接着查询这个对象，那么查出来的这个对象还是之前的没有更新前的状态。
    @Delete(value = "delete from user where username=?1")
    void deleteByUserName(String username);
}

7：complie生成Q类 service导入 JPAQueryFactory queryFactory，或者 SQLQueryFactory sqlQueryFactory;

@Autowired
JPAQueryFactory queryFactory;
//Q类
QNews news = QNews.news;
QUser user = QUser.user;
List<News> userList = queryFactory.selectFrom(news).innerJoin(news).on(user.id.eq(user.id)).fetch();

8：保存操作SQLQueryFactory ，jpa试过没生效。new RelationalPathBase<>(User.class,QUser.user.getMetadata().getName(),“db2022”,“user”)，db2022数据库名、user表名。

SQLInsertClause insert = sqlQueryFactory.insert(new RelationalPathBase<>(User.class,QUser.user.getMetadata().getName(),"db2022","user"));
long sadad = insert.columns(user.id,user.userName,user.passWord).values(2023883, "whfang22222","null").execute();
List<User> users = queryFactory.selectFrom(user).where(user.id.eq(21023883)).fetch();
users.stream().forEach(o-> System.out.println(o));

9：实体类映射

@Data
@Entity
@Table(name="user")
public class User {
    @Id
    @GeneratedValue(strategy= GenerationType.IDENTITY)
    private Integer id;
    @Column(name= "username")
    private String userName;
    @Column(name = "password")
    private String passWord;
    @Column(name = "nick_name")
    private String nickName;
    private Integer age;
    private String sex;
    private Integer role;
}

queryDSL-sql:maven配置：
tableNamePattern是表名

<build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.project.lombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
            <plugin>
                <groupId>com.querydsl</groupId>
                <artifactId>querydsl-maven-plugin</artifactId>
                <version>${querydsl.version}</version>
                <executions>
                    <execution>
                        <goals>
                            <goal>export</goal>
                        </goals>
                    </execution>
                </executions>
                <configuration>
                    <jdbcDriver>com.mysql.cj.jdbc.Driver</jdbcDriver>
                    <jdbcUrl>jdbc:mysql://localhost:3306/regulation_engine_db?useUnicode=true&amp;characterEncoding=UTF-8&amp;useSSL=false</jdbcUrl>
                    <packageName>com.iflytek.entity</packageName>
                    <jdbcUser>cdss</jdbcUser>
                    <jdbcPassword>Cdss!2018</jdbcPassword>
                    <tableNamePattern>component_db,ruler_manager,script_rule,model_entity,regulations,data_dict
                    </tableNamePattern>
                    <exportBeans>true</exportBeans>
                    <targetFolder>src/main/java</targetFolder>
                    <sourceFolder>src/main/java</sourceFolder>
                    <customTypes>
                        <customType>com.querydsl.sql.types.UtilDateType</customType>
                        <customType>com.iflytek.medicalboot.core.querydsl.DateUtilDateType</customType>
                    </customTypes>
                    <serializerClass>com.iflytek.medicalboot.core.querydsl.CoreMetaDataSerializer</serializerClass>
                    <beanSerializerClass>com.iflytek.medicalboot.core.querydsl.CoreBeanSerializer</beanSerializerClass>
                    <!--添加配置，避免生成 BigInteger 和 Byte 类型字段-->
                    <numericMappings>
                        <numericMapping>
                            <total>4</total>
                            <decimal>0</decimal>
                            <javaType>java.lang.Integer</javaType>
                        </numericMapping>
                        <numericMapping>
                            <total>5</total>
                            <decimal>0</decimal>
                            <javaType>java.lang.Integer</javaType>
                        </numericMapping>
                        <numericMapping>
                            <total>19</total>
                            <decimal>0</decimal>
                            <javaType>java.lang.Long</javaType>
                        </numericMapping>
                    </numericMappings>
                </configuration>
                <dependencies>
                    <dependency>
                        <groupId>mysql</groupId>
                        <artifactId>mysql-connector-java</artifactId>
                        <version>8.0.16</version>
                    </dependency>
                </dependencies>
            </plugin>
        </plugins>
    </build>