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&characterEncoding=UTF-8&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>
注:queryDSL-sql需要手动生成Q类