JPA development tool fastjpa use introduction

Introduce fastjpa, a tool for JPA development. It provides a secondary encapsulation of JPA operations in the SpringBoot framework, and provides an object-oriented way to operate JPQL/HQL. It aims to reduce the writing of sql statements and quickly improve development efficiency. Code writing is more elegant and readability

Tool features:

---

• Object-oriented update, delete and query operations
• Query the specified column name and function column
• Group query and filter
• List query and filtering
• Table join query and filter
• Support subqueries
• Paging query and filtering

Install

---

<dependency>
      <groupId>com.github.paganini2008.springworld</groupId>
      <artifactId>fastjpa-spring-boot-starter</artifactId>
      <version>2.0.2</version>
</dependency>

fastjpa-spring-boot-starter relies on spring-boot-starter-data-jpa, which essentially repackages the JPA Criteria query API (QBC) and is designed as a stream style API (somewhat similar to python’s orm framework sqlalchemy) , Making the JPA object-oriented query API no longer difficult to use

fastjpa core interface:

---

• EntityDao
• Model
• JpaQuery
• JpaPage
• Filter
• Column
• Field
• JpaGroupBy
• JpaSort
• JpaPageResultSet
• JpaQueryResultSet
• JpaUpdate
• JpaDelete
  If you are interested, you can study its source code

Let's use a few examples to demonstrate the usage of several core interfaces of fastjpa

For example, there are now 3 entities, users, orders, and products

User entity

@Getter
@Setter
@Entity
@Table(name = "demo_user")
public class User {
    
    @Id
    @Column(name = "id", nullable = false, unique = true)
    private Long id;
    
    @Column(name = "name", nullable = false, length = 45)
    private String name;
    
    @Column(name = "phone", nullable = false, length = 45)
    private String phone;
    
    @Column(name = "vip", nullable = true)
    @org.hibernate.annotations.Type(type = "yes_no")
    private Boolean vip;

}

Commodity entity

@Getter
@Setter
@Entity
@Table(name = "demo_product")
public class Product {

    @Id
    @Column(name = "id", nullable = false, unique = true)
    private Long id;

    @Column(name = "name", nullable = false, length = 45)
    private String name;

    @Column(name = "price", nullable = false, precision = 11, scale = 2)
    private BigDecimal price;

    @Column(name = "origin", nullable = true, length = 225)
    private String origin;

}

Order entity

@Getter
@Setter
@Entity
@Table(name = "demo_order")
public class Order {

    @Id
    @Column(name = "id", nullable = false, unique = true)
    private Long id;

    @Column(name = "discount", nullable = true)
    private Float discount;

    @Column(name = "price", nullable = false, precision = 11, scale = 2)
    private BigDecimal price;

    @OneToOne(targetEntity = Product.class)
    @JoinColumn(nullable = false, name = "product_id", foreignKey = @ForeignKey(name = "none", value = ConstraintMode.NO_CONSTRAINT))
    private Product product;

    @ManyToOne(targetEntity = User.class)
    @JoinColumn(nullable = false, name = "user_id", foreignKey = @ForeignKey(name = "none", value = ConstraintMode.NO_CONSTRAINT))
    private User user;

    @Temporal(TemporalType.TIMESTAMP)
    @Column(name = "create_time", columnDefinition = "timestamp null ")
    private Date createTime;

}

Then define the corresponding Dao, you need to inherit the EntityDao provided by fastjpa, but if you don’t want to use fastjpa, just inherit JpaRepositoryImplementation directly

UserDao

public interface UserDao extends EntityDao<User, Long> {

}

OrderDao

public interface OrderDao extends EntityDao<Order, Long> {

}

ProductDao

public interface ProductDao extends EntityDao<Product, Long> {

}

EntityDao is the entrance to all APIs of fastjpa. Take a look at its source code:

@NoRepositoryBean
public interface EntityDao<E, ID> extends JpaRepositoryImplementation<E, ID>, NativeSqlOperations<E> {

    Class<E> getEntityClass();

    boolean exists(Filter filter);

    long count(Filter filter);

    List<E> findAll(Filter filter);

    List<E> findAll(Filter filter, Sort sort);

    Page<E> findAll(Filter filter, Pageable pageable);

    Optional<E> findOne(Filter filter);

    <T extends Comparable<T>> T max(String property, Filter filter, Class<T> requiredType);

    <T extends Comparable<T>> T min(String property, Filter filter, Class<T> requiredType);

    <T extends Number> T avg(String property, Filter filter, Class<T> requiredType);

    <T extends Number> T sum(String property, Filter filter, Class<T> requiredType);

    JpaUpdate<E> update();

    JpaDelete<E> delete();

    JpaQuery<E, E> query();

    <T> JpaQuery<E, T> query(Class<T> resultClass);

    JpaQuery<E, Tuple> multiquery();

    JpaPage<E, E> select();

    <T> JpaPage<E, T> select(Class<T> resultClass);

    JpaPage<E, Tuple> multiselect();

}

in:
The update() method corresponds to the update operation
The delete() method corresponds to the delete operation
The query() method corresponds to the list operation
The select() method corresponds to the paging operation
The multiquery() method corresponds to a list operation, but unlike the query() method, it returns data of type javax.persistence.Tuple, which is used to encapsulate the data structure of grouping or multi-table connection query
multiselect() is similar
In addition, the fastjpa component also supports the use of local sql queries, which will be briefly introduced at the end of the article.

Now, go back to the previous example and continue to talk about how to use the fastjpa API,

First, insert some data for these 3 entities, and set up related association relationships
For example, here, suppose that there is one order for one product, and one user can place multiple orders. This is just for demonstration purposes.

Filter

Equivalent to where condition
Support lt, lte, gt, gte, eq, ne, like, in, between, isNull, notNull and other comparison operators
Support and, or, not logical operators

Examples of comparison operators:

        LogicalFilter filter = Restrictions.gt("price", 50); // 价格大于50元
        productDao.query().filter(filter).selectThis().list().forEach(pro -> {
            System.out.println(pro);
        });
// Hibernate: select product0_.id as id1_1_, product0_.name as name2_1_, product0_.origin as origin3_1_, product0_.price as price4_1_ from demo_product product0_ where product0_.price>50.0

Similar to:

LogicalFilter filter = Restrictions.between("price", 10, 50);
filter = Restrictions.in("price", Arrays.asList(10,20,30,40,50));
filter =Restrictions.like("name", "%猴头菇%");
filter = Restrictions.eq("orignal", "Shanghai");

Examples of logical operators:

and

LogicalFilter filter = Restrictions.between("price", 10, 50);
filter = filter.and(Restrictions.like("name", "%猴头菇%"));
filter = filter.and(Restrictions.eq("orignal", "Shanghai"));
// 相当于 where price between (10,50) and name like '%猴头菇%' and orignal='Shanghai'
productDao.query().filter(filter).selectThis().list().forEach(pro -> {
    System.out.println(pro);
});

or

LogicalFilter filter = Restrictions.eq("orignal", "Shanghai");
filter = filter.or(Restrictions.eq("orignal", "New York"));
// 相当于 where orignal='Shanghai' or orignal='New York'
productDao.query().filter(filter).selectThis().list().forEach(pro -> {
    System.out.println(pro);
});

not

LogicalFilter filter = Restrictions.eq("orignal", "Shanghai");
filter = filter.and(Restrictions.eq("orignal", "New York"));
filter = filter.not();
 // 取反，相当于 where orignal!='Shanghai' and orignal!='New York'
productDao.query().filter(filter).selectThis().list().forEach(pro -> {
    System.out.println(pro);
});

JpaGroupBy

Grouping, equivalent to group by
For example:

productDao.multiquery().groupBy("origin").select(Column.forName("origin"), Fields.count(Fields.toInteger(1)).as("count")).list()
.forEach(t -> {
        System.out.println("origin: "+t.get("origin") + "\tcount: " + t.get("count"));
});
// Hibernate: select product0_.origin as col_0_0_, count(1) as col_1_0_ from demo_product product0_ group by product0_.origin

Column

Represents a column
For example:

productDao.multiquery().select(Column.forName("name"), Column.forName("price")).list(10).forEach(t -> {
        System.out.println("name: "+t.get("name") + "\t price: " + t.get("price"));
});
// Hibernate: select product0_.name as col_0_0_, product0_.price as col_1_0_ from demo_product product0_ limit ?

You can also query multiple columns like this:

productDao.multiquery().select(new String[] { "name", "price" }).list(10).forEach(t -> {
    System.out.println("name: " + t.get("name") + "\t price: " + t.get("price"));
});
// 或者这样：
ColumnList columnList = new ColumnList().addColumn("name").addColumn("price");
productDao.multiquery().select(columnList).list(10).forEach(t -> {
    System.out.println("name: " + t.get("name") + "\t price: " + t.get("price"));
});

Field

Used to represent functions, constants, etc.
For example:
Aggregate function

ColumnList columnList = new ColumnList()
                .addColumn(Fields.max("price", BigDecimal.class), "maxPrice")
                .addColumn(Fields.min("price", BigDecimal.class), "minPrice")
                .addColumn(Fields.avg("price", Double.class), "avgPrice")
                .addColumn(Fields.count(Fields.toInteger(1)), "count")
                .addColumn("origin");
productDao.multiquery().groupBy("origin").select(columnList).setTransformer(Transformers.asBean(ProductVO.class)).list().forEach(vo -> {
    System.out.println(vo);
});

Commonly used functions:
concat

ColumnList columnList = new ColumnList()
    .addColumn(Fields.concat(Fields.concat(Fields.max("price", String.class), "/"), Fields.min("price", String.class)), "repr")
    .addColumn("origin");
productDao.multiquery().groupBy("origin").select(columnList).setTransformer(Transformers.asBean(ProductVO.class)).list().forEach(vo -> {
    System.out.println(vo);
});
// Hibernate: select concat(concat(max(cast(product0_.price as char)), '/'), min(cast(product0_.price as char))) as col_0_0_, product0_.origin as col_1_0_ from demo_product product0_ group by product0_.origin

Other commonly used functions

ColumnList columnList = new ColumnList()
       .addColumn(Function.build("LOWER", String.class, "name"), "name")
       .addColumn(Function.build("UPPER", String.class, "origin"), "origin");
productDao.multiquery().select(columnList).list(10).forEach(t -> {
    System.out.println("name: " + t.get("name") + "\t origin: " + t.get("origin"));
});
// Hibernate: select lower(product0_.name) as col_0_0_, upper(product0_.origin) as col_1_0_ from demo_product product0_ limit ?

Case When

        IfExpression<String, String> ifExpression = new IfExpression<String, String>(Property.forName("origin", String.class));
        ifExpression = ifExpression.when("Shanghai", "Asia")
                                   .when("Tokyo", "Asia")
                                   .when("New York", "North America")
                                   .when("Washington", "North America")
                                   .otherwise("Other Area");
        ColumnList columnList = new ColumnList().addColumn(ifExpression, "Area")
                                                .addColumn(Fields.count(Fields.toInteger(1)), "Count");
        productDao.multiquery().groupBy(Fields.toInteger(1)).select(columnList).list().forEach(t -> {
            System.out.println("Area: " + t.get(0) + "\t Count: " + t.get(1));
        });
// Hibernate: select case product0_.origin when 'Shanghai' then 'Asia' when 'Tokyo' then 'Asia' when 'New York' then 'North America' when 'Washington' then 'North America' else 'Other Area' end as col_0_0_, count(1) as col_1_0_ from demo_product product0_ group by 1

Example of subquery

Example 1

        JpaSubQuery<Order,Order> subQuery = orderDao.query().subQuery(Order.class, "o").filter(Restrictions.eq("o", "id", "100")).select("o", "product.id");
        Product product = productDao.query().filter(Restrictions.eq("id", subQuery)).selectThis().first();
        System.out.println(product);
// Hibernate: select product0_.id as id1_1_, product0_.name as name2_1_, product0_.origin as origin3_1_, product0_.price as price4_1_ from demo_product product0_ where product0_.id=(select order1_.product_id from demo_order order1_ where order1_.id=100) limit ?

Example 2

        JpaQuery<Order,Order> jpaQuery = orderDao.query();
        JpaSubQuery<Product, BigDecimal> subQuery = jpaQuery.subQuery(Product.class, "p", BigDecimal.class)
                .select(Fields.avg(Property.forName("p", "price")));
        jpaQuery.filter(Restrictions.gte("price", subQuery)).selectThis().list(10).forEach(pro -> {
            System.out.println(pro);
        });
// Hibernate: select order0_.id as id1_0_, order0_.create_time as create_t2_0_, order0_.discount as discount3_0_, order0_.price as price4_0_, order0_.product_id as product_6_0_, order0_.receiver as receiver5_0_, order0_.user_id as user_id7_0_ from demo_order order0_ where order0_.price>=(select avg(product1_.price) from demo_product product1_) limit ?

Example 3

        JpaQuery<Order,Order> jpaQuery = orderDao.query();
        JpaSubQuery<Product, BigDecimal> subQuery = jpaQuery.subQuery(Product.class, "p", BigDecimal.class)
                .select(Fields.avg(Property.forName("p", "price")));
        jpaQuery.filter(Restrictions.gte("price", subQuery)).selectThis().list(10).forEach(pro -> {
            System.out.println(pro);
        });
// Hibernate: select order0_.id as id1_0_, order0_.create_time as create_t2_0_, order0_.discount as discount3_0_, order0_.price as price4_0_, order0_.product_id as product_6_0_, order0_.receiver as receiver5_0_, order0_.user_id as user_id7_0_ from demo_order order0_ where 1=1 and (exists (select user1_.id from demo_user user1_ where user1_.name=order0_.receiver)) limit ?

Sorting example

        orderDao.query().filter(Restrictions.gte("price", 50)).sort(JpaSort.desc("createTime"), JpaSort.asc("price")).selectThis().list(10)
                .forEach(pro -> {
                    System.out.println(pro);
                });
// Hibernate: select order0_.id as id1_0_, order0_.create_time as create_t2_0_, order0_.discount as discount3_0_, order0_.price as price4_0_, order0_.product_id as product_6_0_, order0_.receiver as receiver5_0_, order0_.user_id as user_id7_0_ from demo_order order0_ where order0_.price>=50.0 order by order0_.create_time desc, order0_.price asc limit ?

Related query example

Left connect

        PageResponse<Tuple> pageResponse = orderDao.multiselect().leftJoin("product", "p")
                .filter(Restrictions.gte("p", "price", 50)).sort(JpaSort.desc("createTime")).selectAlias("p")
                .list(PageRequest.of(10));
        for (PageResponse<Tuple> current : pageResponse) {
            System.out.println("第" + current.getPageNumber() + "页");
            for (Tuple tuple : current.getContent()) {
                System.out.println(Arrays.toString(tuple.toArray()));
            }
        }
// Hibernate: select product1_.id as id1_1_, product1_.name as name2_1_, product1_.origin as origin3_1_, product1_.price as price4_1_ from demo_order order0_ left outer join demo_product product1_ on order0_.product_id=product1_.id where product1_.price>=50.0 order by order0_.create_time desc limit ?, ?

Right connection

        ColumnList columnList = new ColumnList();
        columnList.addColumn("id");
        columnList.addColumn("u", "name");
        columnList.addColumn("price");
        columnList.addColumn("createTime");
        PageResponse<Order> pageResponse = orderDao.select().rightJoin("user", "u").filter(Restrictions.gte("price", 50))
                .sort(JpaSort.desc("createTime")).select(columnList).list(PageRequest.of(10));
        for (PageResponse<Order> current : pageResponse) {
            System.out.println("第" + current.getPageNumber() + "页");
            for (Order order : current.getContent()) {
                System.out.println(order);
            }
        }
  // 然而运行上面代码，JPA会报错，因为JPA目前尚不支持Right Join!

Internal connection

        ColumnList columnList = new ColumnList();
        columnList.addColumn("id");
        columnList.addColumn("p","name");
        columnList.addColumn(Property.forName("p", "price"),"originalPrice");
        columnList.addColumn("price");
        columnList.addColumn("createTime");
        PageResponse<Tuple> pageResponse = orderDao.multiselect().join("product", "p")
                .filter(Restrictions.gte("p", "price", 50)).sort(JpaSort.desc("createTime")).select(columnList)
                .list(PageRequest.of(10));
        for (PageResponse<Tuple> current : pageResponse) {
            System.out.println("第" + current.getPageNumber() + "页");
            for (Tuple tuple : current.getContent()) {
                System.out.println(Arrays.toString(tuple.toArray()));
            }
        }
// Hibernate: select order0_.id as col_0_0_, product1_.name as col_1_0_, product1_.price as col_2_0_, order0_.price as col_3_0_, order0_.create_time as col_4_0_ from demo_order order0_ inner join demo_product product1_ on order0_.product_id=product1_.id where product1_.price>=50.0 order by order0_.create_time desc limit ?, ?

List and pagination query

Query order list:

        orderDao.query().filter(Restrictions.gt("price", 50)).sort(JpaSort.desc("createTime")).selectThis()
                .setTransformer(Transformers.asBean(OrderVO.class, null, (model, order, output) -> {
                    Product product = order.getProduct();
                    output.setProductName(product.getName());
                    output.setOrigin(product.getOrigin());
                    User user = order.getUser();
                    output.setUsername(user.getName());
                    output.setPhone(user.getPhone());
                })).list(50).forEach(vo -> {
                    System.out.println(vo);
                });
// Hibernate: select order0_.id as id1_0_, order0_.create_time as create_t2_0_, order0_.discount as discount3_0_, order0_.price as price4_0_, order0_.product_id as product_6_0_, order0_.receiver as receiver5_0_, order0_.user_id as user_id7_0_ from demo_order order0_ where order0_.price>50.0 order by order0_.create_time desc limit ?

Pagination query order:

        PageResponse<OrderVO> pageResponse = orderDao.select().filter(Restrictions.gt("price", 50)).sort(JpaSort.desc("createTime"))
                .selectThis().setTransformer(Transformers.asBean(OrderVO.class, null, (model, order, output) -> {
                    Product product = order.getProduct();
                    output.setProductName(product.getName());
                    output.setOrigin(product.getOrigin());
                    User user = order.getUser();
                    output.setUsername(user.getName());
                    output.setPhone(user.getPhone());
                })).list(PageRequest.of(1, 10));
        for (PageResponse<OrderVO> current : pageResponse) {
            System.out.println("第" + current.getPageNumber() + "页");
            for (OrderVO vo : current.getContent()) {
                System.out.println(vo);
            }
        }
// Hibernate: select count(1) as col_0_0_ from demo_order order0_ where order0_.price>50.0
// Hibernate: select order0_.id as id1_0_, order0_.create_time as create_t2_0_, order0_.discount as discount3_0_, order0_.price as price4_0_, order0_.product_id as product_6_0_, order0_.receiver as receiver5_0_, order0_.user_id as user_id7_0_ from demo_order order0_ where order0_.price>50.0 order by order0_.create_time desc limit ?, ?

Paging query will have two SQL, one count statement, one query statement

Delete operation

int rows = productDao.delete().filter(Restrictions.gt("price", 990)).execute();
System.out.println("Effected rows: " + rows);
// Hibernate: delete from demo_product where price>990.0

Subquery delete:

        JpaSubQuery<Order, Order> subQuery = productDao.delete().subQuery(Order.class);
        subQuery.select("product");
        int rows = productDao.delete().filter(Restrictions.in("id", subQuery).not()).execute();
        System.out.println("Effected rows: " + rows);
// Hibernate: delete from demo_product where id not in  (select order1_.product_id from demo_order order1_)

Update operation

int rows = userDao.update().set("vip", true).filter(Restrictions.eq("vip", false)).execute();
System.out.println("Effected rows: " + rows);
// Hibernate: update demo_user set vip=? where vip=?

Subquery update:

        JpaSubQuery<Order, Order> subQuery = userDao.update().subQuery(Order.class).filter(Restrictions.gte("price", 500)).select("user");
        int rows = userDao.update().set("vip", true).filter(Restrictions.in("id", subQuery)).execute();
        System.out.println("Effected rows: " + rows);
// update demo_user set vip=? where id in (select order1_.user_id from demo_order order1_ where order1_.price>=500.0)

Local query example

---

For fastjpa operating JPA objects that are difficult to meet actual business scenarios, it is recommended to use SQL directly, that is, local query

Simple query returns related entities

ResultSetSlice<Order> resultSetSlice = orderDao.select("select * from demo_order where price>?", new Object[] { 50 });
        PageResponse<Order> pageResponse = resultSetSlice.list(PageRequest.of(1, 10));
        for (PageResponse<Order> current : pageResponse) {
            System.out.println("第" + current.getPageNumber() + "页");
            for (Order order : current.getContent()) {
                System.out.println("Order Id: "+order.getId() + ", Product Name: " + order.getProduct().getName() + ", Username: " + order.getUser().getName());
            }
        }

Group query and paging

        ResultSetSlice<Map<String, Object>> resultSetSlice = orderDao.selectForMap(
                "select origin,max(price) as maxPrice,min(price) as minPrice,avg(price) as avgPrice from demo_product group by origin",
                new Object[0]);
        PageResponse<Map<String, Object>> pageResponse = resultSetSlice.list(PageRequest.of(1, 5));
        for (PageResponse<Map<String, Object>> current : pageResponse) {
            System.out.println("第" + current.getPageNumber() + "页");
            for (Map<String, Object> vo : current.getContent()) {
                System.out.println(vo);
            }
        }

In fact, fastjpa appeared to improve the efficiency of developers using JPA. If there is a situation where fastjpa cannot meet business needs, please use local sql decisively

Finally, attach the core code to enable JPA configuration, which can be eaten directly

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(repositoryFactoryBeanClass = EntityDaoFactoryBean.class, basePackages = { "com.demo.jpalearning.dao" })
public class JpaConfig {

    public static final String PRIMARY_ENTITY_FACTORY_BEAN_NAME = "entityManagerFactory";

    @Primary
    @Bean(PRIMARY_ENTITY_FACTORY_BEAN_NAME)
    public LocalContainerEntityManagerFactoryBean entityManagerFactory(DataSource dataSource, EntityManagerFactoryBuilder builder,
            JpaProperties jpaProperties, HibernateProperties hibernateProperties) {
        Map<String, Object> properties = hibernateProperties.determineHibernateProperties(jpaProperties.getProperties(),
                new HibernateSettings());
        return builder.dataSource(dataSource).properties(properties).packages("com.demo.jpalearning.entity").build();
    }

    @Bean
    public PlatformTransactionManager transactionManager(EntityManagerFactory emf) {
        JpaTransactionManager transactionManager = new JpaTransactionManager();
        transactionManager.setEntityManagerFactory(emf);
        return transactionManager;
    }

    @Bean
    public PersistenceExceptionTranslationPostProcessor exceptionTranslation() {
        return new PersistenceExceptionTranslationPostProcessor();
    }

}

Don't forget to add the configuration file:

#Jpa Configuration
spring.jpa.database=MYSQL
spring.jpa.show-sql=true
spring.jpa.format-sql=false
spring.jpa.hibernate.ddl-auto=update
spring.jpa.hibernate.naming.physical-strategy=org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5InnoDBDialect

Source address: https://github.com/paganini2008/fastjpa-spring-boot-starter.git