介紹一款JPA開發利器fastjpa，它提供了對SpringBoot框架中關於對JPA的操作的二次封裝 ，提供了面向對象的方式來操作JPQL/HQL，旨在減少sql語句編寫，快速提高開發效率，使代碼書寫顯的更加優雅和增加可讀性

工具特性：

• 面向對象方式的更新、刪除和查詢操作
• 查詢指定列名和函數列
• 分組查詢和過濾
• 列表查詢和過濾
• 表連接查詢和過濾
• 支持子查詢
• 分頁查詢和過濾

安裝

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

fastjpa-spring-boot-starter 依賴spring-boot-starter-data-jpa, 實質上是對JPA Criteria查詢API(QBC)的再封裝，並設計成流式風格的API(有點類似python的orm框架sqlalchemy) ，使得JPA面向對象查詢的API不再難用

fastjpa 核心接口：

• EntityDao
• Model
• JpaQuery
• JpaPage
• Filter
• Column
• Field
• JpaGroupBy
• JpaSort
• JpaPageResultSet
• JpaQueryResultSet
• JpaUpdate
• JpaDelete
  大家有興趣的話，可以研究其源碼

下面通過幾個示例來演示一下fastjpa的幾個核心接口的用法

比如，現在有3個實體，用户，訂單，商品

用户實體

@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;

}

商品實體

@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;

}

訂單實體

@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;

}

然後定義對應的Dao，需要繼承fastjpa提供的EntityDao，但如果你不想使用fastjpa, 直接繼承JpaRepositoryImplementation就行了

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是fastjpa 所有API的入口，看一下它的源碼：

@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();

}

其中：
update()方法對應的是update操作
delete()方法對應的是delete操作
query()方法對應的是列表操作
select()方法對應的是分頁操作
multiquery()方法對應的也是列表操作，但和query()方法不同的是，它返回的是javax.persistence.Tuple類型的數據，它用於封裝分組或多表連接的查詢的數據結構
multiselect()也是類似的
另外説一下，fastjpa組件還支持本地sql查詢的使用，會在文章的末尾粗略介紹一下。

現在，回到前面的例子，繼續講一下如何使用fastjpa的API,

首先要對這3個實體，分別插入一些數據，並設置相關的關聯關係
比如這裏，假定一個商品一個訂單，一個用户可以下多個訂單，這裏只是為了演示一下而已

Filter

相當於where條件
支持 lt, lte, gt, gte, eq, ne, like, in, between, isNull, notNull等比較操作符
支持 and, or, not 邏輯操作符

比較操作符舉例：

        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

類似的還有：

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");

邏輯操作符舉例：

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

分組，相當於group by
舉例：

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

表示一個列
舉例：

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 ?

查詢多個列也可以這樣：

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

用來表示函數，常量等
舉例：
聚合函數

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);
});

常用函數：
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

其他常用函數

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

子查詢示例

示例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 ?

示例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 ?

示例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 ?

排序示例

        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 ?

關聯查詢示例

左連接

        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 ?, ?

右連接

        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!

內連接

        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 ?, ?

列表和分頁查詢

查詢訂單列表：

        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 ?

分頁查詢訂單：

        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 ?, ?

分頁查詢會出現兩條sql, 一條count語句，一條查詢語句

刪除操作

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

子查詢刪除:

        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_)

更新操作

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=?

子查詢更新:

        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)

本地查詢示例

對於fastjpa操作JPA對象難以滿足實際業務的場景，還是建議直接使用sql, 即本地查詢

簡單查詢返回關聯實體

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());
            }
        }

分組查詢及分頁

        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);
            }
        }

事實上，fastjpa是為了提升開發人員使用JPA的效率而出現的，如果出現使用fastjpa不能滿足業務需求的情況，請果斷使用本地sql

最後附上啓用JPA配置的核心代碼, 可以直接食用

@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();
    }

}

別忘了配置文件添加：

#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

源碼地址：https://github.com/paganini20...