The preface

Because the project may be deployed to different database types, using MyBatis is simple and cannot make a full set of SQL database types available (not 100% perfect, but with as few changes as possible). The types of databases currently deployed are:

  • MySQL5.7
  • MySQL8.0. X
  • Oracle9x
  • Oracle10
  • Oracle11g
  • Oracle12c
  • Oracle19c
  • DB2-11.1.4
  • DB2-11.1.5
  • Postgres9
  • Postgres12

If you only encounter MySQL and Oracle, it will be better. There are so many database types involved, and the syntax of different versions of databases may be different. Using MyBatis will undoubtedly increase the workload of testing and development, which is less efficient and does not require high concurrency. And very efficient, QueryDSL can plug in the wings for JPA, convenient a lot of work.

QueryDSL

The website address

www.querydsl.com/static/quer… The latest official documentation

Set up

1. Introduce dependencies

<! The following configuration is to be configured in pom.xml in the module where the @Entity annotation is located. <dependency> <groupId>com.querydsl</groupId> <artifactId>querydsl-apt</artifactId> <version>${querydsl.version}</version> <scope>provided</scope> </dependency> <dependency> <groupId>com.querydsl</groupId> <artifactId>querydsl-jpa</artifactId> <version>${querydsl.version}</version> </dependency> <project> <build> <plugins> ... < 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> </project>Copy the code

2. Generate the Qxxx class

Once the QueryDSL dependencies are introduced, you need to click on maven build to generate a query class that starts with Q

Click on either of the two Maven arrows and the result will look like this:

Take a look at the generated code example for curiosity:

@Generated("com.querydsl.codegen.EntitySerializer") public class QPermission extends EntityPathBase<Permission> { private static final long serialVersionUID = 1115934067L; public static final QPermission permission = new QPermission("permission"); public final StringPath dataScopes = createString("dataScopes"); public final StringPath depends = createString("depends"); public final BooleanPath display = createBoolean("display"); public final NumberPath<Integer> formMethod = createNumber("formMethod", Integer.class); public final StringPath icon = createString("icon"); public final NumberPath<Integer> id = createNumber("id", Integer.class); public final NumberPath<Integer> level = createNumber("level", Integer.class); public final NumberPath<Integer> order = createNumber("order", Integer.class); public final NumberPath<Integer> parentId = createNumber("parentId", Integer.class); public final StringPath path = createString("path"); public final StringPath permissionCode = createString("permissionCode"); public final StringPath permissionName = createString("permissionName"); public final EnumPath<com.xuanwu.fgmp.common.enumeration.base.PermissionType> permissionType = createEnum("permissionType", com.xuanwu.fgmp.common.enumeration.base.PermissionType.class); public final EnumPath<com.xuanwu.fgmp.common.enumeration.base.PlatformType> platformFlag = createEnum("platformFlag", com.xuanwu.fgmp.common.enumeration.base.PlatformType.class); public final StringPath relevance = createString("relevance"); public final StringPath remark = createString("remark"); public QPermission(String variable) { super(Permission.class, forVariable(variable)); } public QPermission(Path<? extends Permission> path) { super(path.getType(), path.getMetadata()); } public QPermission(PathMetadata metadata) { super(Permission.class, metadata); }}Copy the code

As you can see, the generated objects are xxxxPath objects in preparation for the JPAQuery component!

use

QueryDSL currently only supports: Update,Delete,Select operations, does not support Save operations, but the official documentation has insert operations, this has not been tried!

Prepare two tables

/** ** We use Lombok to give only primary key annotations and omit the rest, @entity @table (name = "role") @data @accessors (chain = true) public class role {@id @column (name = "name" "id") @GeneratedValue(strategy = GenerationType.IDENTITY) private Integer id; private String name; private String remark; private Boolean isDefault; private Integer userId; private Date createTime; private Date updateTime; }Copy the code
*/ @entity @table (name = "user") @data @accessors (chain = true) public class BaseUser {@id @generatedValue (strategy = GenerationType.identity) @column (name = "id") @comment (Comment = "主键, Private Integer ID; private Integer enterpriseId; private String name; private String account; @Convert(converter = UserStateConverter.class) private UserState state; private Integer departmentId; private Date createTime; private Date updateTime; }Copy the code
@entity @table (name = "fgmp_user_role") public class UserRole {private Integer userId; private Integer roleId; }Copy the code

Tectonic JPAQueryFactory

@Configuration public class JPAQueryFactoryConfig { @Autowired @PersistenceContext private EntityManager entityManager; @Bean("jpaQueryFactory") public JPAQueryFactory jpaQueryFactory(){ return new JPAQueryFactory(entityManager); }}Copy the code

Single table operation

update

@Transactional @Override public boolean modifyUserById(Integer userId) { QBaseUser user = QBaseUser.baseUser; Jpaqueryfactory.update (user).set(user.isremove, Set (user.updateTime, new Date()). Where (user.id.eq(userId))// conditional.execute() == 1; SQL: update user set name = XXX,is_remove=true, is_remove= 'XXXXX' WHERE id = XXXX;Copy the code

delete

@Transactional @Override public boolean deleteUserById(Integer userId) { QBaseUser user = QBaseUser.baseUser; Jpaqueryfactory.delete (user).where(user.id.eq(userId))// conditional.execute() == 1; SQL delete from user where id = XXX;Copy the code

select

public void pageData(){ QBaseUser user = QBaseUser.baseUser; List<BaseUser> pageDate = jpaQueryFactory.selectFrom(user) .where(user.updateTime.between(new Date(), new Date())) .where(user.isRemove.eq(false).or(user.id.gt(3))) .orderBy(user.createTime.desc()) .offset(3) .limit(10) .fetch(); } translate to SQL(this is written as MySQL, other databases may not be the same): Select * from user where update_time between date 1 and date 2 and (is_remove = false or id >3) order by create_time desc Limint 3, 10; Where (XXXX). Where (XXXX) defaults to and, Where (user.updatetime.between (new Date(), new Date()).and(user.isRemove.eq(false).or(user.id.gt(3)))) == .where(user.updateTime.between(new Date(), new Date())) .where(user.isRemove.eq(false).or(user.id.gt(3)))Copy the code

Processing part of the field returns results

public void pageData(){ QBaseUser user = QBaseUser.baseUser; List<Tuple> tuples = jpaQueryFactory.select(user.account, user.name) .from(user) .where(user.id.gt(13)) .fetch(); List<SimpleUser> result = tuples.stream() .map(tuple -> new SimpleUser() .setName(tupl.get (user.name)).setName(tupl.get (user.name)).setName(tupl.get (user.name)).setName(tupl.get (user.name)) .collect(Collectors.toList()); } @Data @Accessors(chain = true) class SimpleUser{ private String account; private String name; }Copy the code

Even the table operation

This is the most important part of the business

List<Tuple> result = jpaQueryFactory.select(user.id, user.account, user.name, user.email, department.name, user.state) .from(user) .leftJoin(department) .on(user.departmentId.eq(department.id)) .where(user.isRemove.eq(false)) .where(department.path.startsWith('xxx').or(department.id.eq(xid))) .fetch(); SQL:  select user.id,user.account,user.name,user.email,department.name,user.state from user left join department on user.department_id = department.id where user.is_remove = fasle and (department.path like '%xxx%' or department.id=xid);  List<Tuple> = List<Tuple>Copy the code

The subquery

List<LoginToken> result = queryFactory.select(loginToken) .where(loginToken.id.eq(JPAExpressions.select(app.id.max()).from(app))) .fetch(); SQL: select * from loginToken where logintoken. id = (select Max (app.id) from app);Copy the code