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