sharp-database 使用注解 @Sql 自定义查询

实体类中添加注解 @Sql

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@SuperBuilder
@Table(value = "t_book", comment = "书")
public class Book extends BaseEntity {

    private String title;

    /**
     * 书的拥有者
     */
    @ParamName({"person_id", "personId"})
    @JsonAlias("personId")
    @JsonDeserialize(using = EntityWithLongIdPropertyDeserializer.class)
    @ManyToOne(value = "person_id", parentTable = "t_person")
    private Person person;

    @Sql("select * from t_person")
    private List<Person> allPerson;

    @Sql("select * from t_person where id = 552098712424472576")
    private Person p1;

    @Sql(value = "select * from t_person where id = :id", params = "id@person.id")
    private Person p2;

    @Sql(value = "select * from t_person where id = :id AND name like :title", params = "id@person.id, title@title")
    private Person p3;

}

public @interface Sql {

    String value();

    String params() default "";

    /**
     * 参数为null的时候， 直接返回结果null，无需数据库查询
     * @return
     */
    String[] nullWhenParamsIsNull() default {};

}

value: sql 语句
params: sql 参数. 多个参数用 , 隔开; 每个参数 参数名@参数值. 参数值可以是静态的,也可以是来自对象的属性值
nullWhenParamsIsNull: 参数为null的时候， 直接返回结果null，无需数据库查询

Bean类中添加注解 @Sql

BookQuery.java 虽然标记 @Table 但是不是实体对象

@Data
@Table
public class BookQuery {

    private Long id;

    private String title;

    private String personId;

    @Sql(value = "select * from t_person where id = :personId", params = "personId@personId")
    private Person person;

    /**
     * 标签
     */
    @Sql(value = "select t_tag.* from t_book, t_tag, t_book_tag where t_book_tag.book_id = t_book.id AND t_book_tag.tag_id = t_tag.id AND t_book.id = :id", params = "id@id")
    private List<Tag> tagList;

    @Sql("select * from t_person")
    private List<Person> allPerson;

    @Sql("select * from t_person where id = 552098712424472576")
    private Person p1;

    @Sql(value = "select * from t_person where id = :id", params = "id@person.id")
    private Person p2;

    @Sql(value = "select * from t_person where id = :id AND name like :title", params = "id@person.id, title@title")
    private Person p3;

    @Sql(value = "select * from t_person where id = :id", params = "id@person.id")
    Map<String, Object> personMap;
}

测试,借助对象 EntityDAOSupport 进行查询

    @Autowired
    private EntityDAOSupport entityDAOSupport;

    @Test
    public void testBySql() {
        List<BookQuery> query = entityDAOSupport.query("select * from t_book where id = 617342584087388160", null, BookQuery.class);
        System.out.println(query);

        Optional<BookQuery> optional = entityDAOSupport.queryForObject("select * from t_book where id = 617342584087388160", null, BookQuery.class);
        System.out.println(optional.get());
    }

    @Test
    public void testByInstance() {
        BookQuery bookQuery = new BookQuery();
        bookQuery.setId(617342584087388160L);
        bookQuery.setPersonId("552100575806939136");

        entityDAOSupport.query(bookQuery);
        System.out.println(bookQuery);
    }

很多时候DAO继承父类都可以满足操作需求, 所以大部分情况下继承 EntityDAOImpl 变成一个样板代码, sharp-database 做了增强,可以不显示地继承 EntityDAOImpl, 可以通过扫描实体类自动完成. 如果自己继承父类,那么就会忽略扫描.

// 这类可以有扫描实体类自动完成
@Repository
public class BomDAO extends EntityDAOImpl<Bom, Long> {
    // 空
}

实例

添加对 entity 自动扫描的支持

启动类添加注解 @DependsOn("entityDAOSupport")

@SpringBootApplication
@EnableCaching
@DependsOn("entityDAOSupport")
public class SharpAdminApplication {

    public static void main(String[] args) {
        SpringApplication.run(SharpAdminApplication.class, args);
    }

}

实体类

编写实体类 Bom.java Item.java
Bom 1 <=> N Item

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@FieldDefaults(level = AccessLevel.PRIVATE)
@SuperBuilder
@Table(value = "produce_bom", comment = "物料BOM表")
public class Bom extends BaseEntity {

    @Column(comment = "物料")
    Long materialId;

    @OneToMany(subTable = "produce_bom_detail", reversePropertyName = "bomId", cascadeInsertOrUpdate = true, joinValue = "bom_id")
    List<Item> itemList;

    @Getter
    @Setter
    @NoArgsConstructor
    @AllArgsConstructor
    @FieldDefaults(level = AccessLevel.PRIVATE)
    @SuperBuilder
    @Table(value = "produce_bom_detail", comment = "物料BOM表详情")
    public static class Item extends BaseEntity {

        @Column(comment = "物料")
        Long materialId;

        BigDecimal quantity;

        String unit;

        String remark;

        Long bomId;
    }

}

实体类生成Table

    tableGenerator.createTable(Bom.class);
    tableGenerator.createTable(Bom.Item.class);

配置实体扫描

application.yml

sharp:
  database:
    entity-base-package: com.rick.admin.module.**.entity

测试

1. EntityDAOSupport

    @Autowired
    private EntityDAOSupport entityDAOSupport

    @Test
    public void testInsertBom() {
        Bom bom = Bom.builder()
                .materialId(697175396411510784L)
                .itemList(Arrays.asList(
                        Bom.Item.builder()
                                .materialId(697175443165417472L)
                                .quantity(BigDecimal.TEN)
                                .unit("KG")
                                .remark("remark")
                                .build()
                ))
                .build();

        // 不参与包扫描也可以使用
        entityDAOSupport.getEntityDAO(bom.getClass()).insert(bom);
    }

2. 包扫描自动注入

bomDAO 是DAO bean的名字. 规则是「实体类名驼峰+DAO」,如果是内部类「外部类+内部实体类名驼峰+DAO」,比如例子中的 bomItemDAO

    /**
     * entity 参与包扫描，可以注入使用
     */
    @Autowired(required = false)
    @Qualifier("bomDAO")
    private EntityDAO<Bom, Long> entityDAO;

    @Test
    public void testInsertBom() {
        Bom bom = Bom.builder()
                .materialId(697175396411510784L)
                .itemList(Arrays.asList(
                        Bom.Item.builder()
                                .materialId(697175443165417472L)
                                .quantity(BigDecimal.TEN)
                                .unit("KG")
                                .remark("remark")
                                .build()
                ))
                .build();

        entityDAO.insert(bom);
    }

3. 手动获取DAO bean

    @Resource
    private ApplicationContext applicationContext;

    @Test
    public void testInsertBom() {
        Bom bom = Bom.builder()
                .materialId(697175396411510784L)
                .itemList(Arrays.asList(
                        Bom.Item.builder()
                                .materialId(697175443165417472L)
                                .quantity(BigDecimal.TEN)
                                .unit("KG")
                                .remark("remark")
                                .build()
                ))
                .build();

        applicationContext.getBean("bomDAO", EntityDAO.class)
                .insert(bom);
    }

04.属性名和bean名保持一致,spring按照名称自动注入(推荐)

    // @Autowired
    @Resource
    private EntityDAO<Bom, Long> bomDAO;

    @Test
    public void testInsertBom() {
        Bom bom = Bom.builder()
                .materialId(697175396411510784L)
                .itemList(Arrays.asList(
                        Bom.Item.builder()
                                .materialId(697175443165417472L)
                                .quantity(BigDecimal.TEN)
                                .unit("KG")
                                .remark("remark")
                                .build()
                ))
                .build();

        bomDAO.insert(bom);
    }

注意事项

如果没有手动编写 DA0 类,那么根据 BeanName 进行注册, 写法是固定的.

@Resource
private EntityDAO<Entity, Long> entityDAO;

如果手动编写 DA0 类,那么根据 BeanType 或 BeanName 进行注册, 可以使用两种方式进行注册. 都是同一引用. 比如:

    @Resource
    private EntityDAO<Dog, Long> dogDAO2; // 指定了范型, 根据类型注册名字随便起 

    // @Resource
    // private EntityDAO dogDAO3; // 如果写成这样, 启动会抛出异常找不到合适的Bean

    // @Resource
    // private EntityDAO dogDAO; // 如果写成这样没有使用范型, 根据Bean的名称注册, 使用的时候需要强转, 不推荐使用此方式. 

    @Resource
    private DogDAO dogDAO;

    @Test
    public void test() {
        Assertions.assertThat(dogDAO2 == dogDAO).isEqualTo(true);
    }