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