Joya 是对
Spring Data JPA扩展，JPA本身功能已经很强大了，但是复杂查询语句HQL通常都是大量字符串拼接，不利于维护和阅读，提供优雅、易读和强大的链式查询语句的Joya应运而生
- 基于
Hibernate NativeQuery进行扩展,支持全字段查询和指定字段查询,支持多种风格灵活易用 - 兼容JPA，可插拔式集成，无需修改任何代码，不影响
JPA和Hibernate原有功能和特性 - 作为 JPA 的扩展和增强，兼容 Spring Data JPA 原有功能和各种特性
- 拥有使用原生SQL语句的极致体验
- SQL结果可返回指定对象实体,同样支持单个字段返回包装类和
String类 - 可扩展性强,兼容其他ORM框架底层工作量小
适用于 使用Java Spring Data JPA 和JDK 1.8 及以上的项目,完美兼容Spring Boot 2.x
Maven Project
<dependency> <groupId>com.sondertara</groupId> <artifactId>joya</artifactId> <version>0.2.0</version> </dependency>Gradle Project
implementation 'com.sondertara:joya:0.2.0'以Spring boot 项目为例,注入Bean.
@Bean public JoyaRepositoryFactoryBean joyaRepositoryFactoryBean(){ return new JoyaRepositoryFactoryBean(); }通过 JoyaRepositoryFactoryBean 会注入 JoyaRepository 和JoyaSpringContext(一个Spring全局类)
joya: # 是否打印sql日志 sql-view: trueJoya 主要提供NativeSqlQuery 来处理查询语句,关于使用有如下特殊说明：
select,from和where语句中都可以使用原生sql字符串方式来拼接查询
Joya默认会为表生成别名，按照表在select和where部分第一次出现的顺序(select)，别名依次为 t0,t1... 如果要添加自定义的sql，请使用表别名
对于联表查询到实体，避免字段冲突。如果有相同的column字段,默认会使用第一个表中的值映射到目标实体,也可以为字段指定别名来都映射到目标实体
针对where语句中的subQuery,如果where是AND查询则subQuery联接条件为OR,反之where联接为OR,subQuery则为AND
// SQL编写风格 NativeSqlQuery query = NativeSqlQuery.builder() .select() .from() .where() .groupBy() .having() .orderBy() .build(); JoyaRepository中方法摘要
/** * 查询list * @param sql 原生sql * @param clazz 目标实体 * @param params 参数 * @param <T> 泛型 * @return list */ @SuppressWarnings("unchecked") public<T> List<T> findListBySql(String sql,Class<T> clazz,Object...params); /** * find to list * @param nativeSql native query * @param resultClass result class * @param <T> generic * @return list */ @SuppressWarnings("unchecked") public<T> List<T> findListBySql(NativeSqlQuery nativeSql,Class<T> resultClass); /** * 分页查询 * * @param resultClass 查询结果接收class * @param queryParam 查询数据 * @param targetClass 查询数据库表 * @param <T> 泛型 * @return 分页数据 */ public<T> PageResult<T> queryPage(PageQueryParam queryParam,Class<T> resultClass,Class<?>...targetClass) /** * 分页查询 * * @param resultClass 查询结果接收class * @param queryParam 查询数据 * @param <T> 泛型 * @param joinPart join语句 * @return 分页数据 */ public<T> PageResult<T> queryPage(PageQueryParam queryParam,Class<T> resultClass,UnaryOperator<JoinCriterion> joinPart) /** * 通过sql 分页查询 * * @param nativeSql 查询语句 * @param resultClass 结果映射class * @param pageNo page * @param pageSize pageSize * @param <T> result * @return result */ @SuppressWarnings("unchecked") public<T> PageResult<T> queryPage(NativeSqlQuery nativeSql,Class<T> resultClass,Integer pageNo,Integer pageSize)- 查询全部字段
public class Test { /** * 等同于select * from xxx , 默认表中的字段全部列出 */ @Test public void testSelectAll() { // SELECT t0.id,t0.user_name,t0.user_email,t0.user_phone,t0.age FROM user AS t0 WHERE t0.user_name = ?1 //使用function函数和 entity对于的class类 NativeSqlQuery query = NativeSqlQuery.builder() .select() .from(UserPo.class) .where(w -> w.eq(UserPo::getUserName, "张三")) .build(); //使用字符串格式 NativeSqlQuery query = NativeSqlQuery.builder() .select() .from("user AS t0") .where(w -> w //.eq("t0.user_name","张三")) 下划线格式 .eq("t0.userName", "张三")) //驼峰格式 .build(); // 映射到UserDTO List<UserDTO> list = joyaRepository.findListBySql(query, UserDTO.class); } }- 指定字段
public class Test { @Test public void testSelectSome() { // SELECT t0.id, t0.user_name FROM user AS t0 WHERE t0.user_name = ?1 // 使用function函数接口 支持选择1-12个字段 NativeSqlQuery query = NativeSqlQuery.builder() .select(UserPo::getId, UserPo::getUserName) .from(UserPo.class) .where(w -> w.eq(UserPo::getUserName, "张三")) .build(); //使用UnaryOperator 函数接口 NativeSqlQuery query1 = NativeSqlQuery.builder() .select((UnaryOperator<SelectCriterion>) s -> { s.add(UserPo::getUserName).add(UserPo::getId); return s; }) .from(UserPo.class) .where(w -> w.eq(UserPo::getUserName, "张三")) .build(); //字符串格式指定字段 NativeSqlQuery query3 = NativeSqlQuery.builder() .select("t0.id", "t0.userName") //.select("t0.id","t0.user_name") .from(UserPo.class) .where(w -> w.eq(UserPo::getUserName, "张三")) .build(); List<UserDTO> list = joyaRepository.findListBySql(query, UserDTO.class); // 查询单个字段映射到包装类或String //SELECT t0.user_name FROM user AS t0 WHERE t0.user_name = ?1 NativeSqlQuery query3 = NativeSqlQuery.builder() .select(UserPo::getUserName) .from(UserPo.class) .where(w -> w.eq(UserPo::getUserName, "张三")) .build(); List<String> list1 = joyaRepository.findListBySql(query3, String.class); } }public class Test { @Test public void testJoin() { // 联接字段在where 语句中 //SELECT t0.id,t0.user_name,t0.user_email,t0.user_phone,t0.age,t1.user_id,t1.account_expired_time,t1.password_expired_time,t1.ext_data FROM user AS t0, user_extend AS t1 WHERE t0.id = t1.user_id AND t0.user_name = ?1 NativeSqlQuery query = NativeSqlQuery.builder() .select() .from(UserPo.class, UserExtendPo.class) .where(w -> w .eq(UserPo::getId, UserExtendPo::getUserId) .eq(UserPo::getUserName, "张三")) .build(); // 联接字段在from语句中 支持inner join,left join和 right join三种方式,目前为了提升效率,限制最大支持三张表联表查询 //SELECT t0.id,t0.user_name,t0.user_email,t0.user_phone,t0.age,t1.user_id,t1.account_expired_time,t1.password_expired_time,t1.ext_data FROM user AS t0 JOIN user_extend AS t1 ON t0.id = t1.user_id WHERE t0.user_name = ?1 NativeSqlQuery query1 = NativeSqlQuery.builder() .select() .from(j -> j.join(UserPo::getId, UserExtendPo::getUserId)) .where(w -> w .eq(UserPo::getUserName, "张三")) .build(); //对于冲突字段可以指定别名,如user表和user_extend表同时有 updateTime字段,可以通过指定别名来避免字段值覆盖 // SELECT t0.id,t0.user_name,t0.user_email,t0.user_phone,t0.update_time,t1.update_time AS modifyTime,t1.account_expired_time,t1.password_expired_time,t1.ext_data FROM user AS t0 JOIN user_extend AS t1 ON t0.id = t1.user_id WHERE t0.user_name = ?1 NativeSqlQuery query3 = NativeSqlQuery.builder() .select()//查询全部字段 .specificS("t1.updateTime AS modifyTime") //将user_extend中重名的updateTime 指定为modifyTime .from(j -> j.join(UserPo::getId, UserExtendPo::getUserId)) .where(w -> w .eq(UserPo::getUserName, "张三")) .build(); } }在Joya中为分页查询封装了PageQueryParam 提供给restFul接口使用
/** * 分页查询参数支持常用封装 */ public class PageQueryParam extends JoyaQuery implements Serializable { /** * 分页大小 */ private Integer pageSize = 10; /** * 页数 默认从0开始 */ private Integer page = 0; /** * 连接类型 默认and */ private LinkType linkType = LinkType.AND; /** * 排序字段 */ private List<OrderParam> orderList = Lists.newArrayList(); /** * 搜索参数 */ private List<SearchParam> params = Lists.newArrayList(); public enum LinkType { /** * */ AND, OR; } }如有如下查询场景
分页关联查询user表和user_extend表,查询条件为userName like '张三%'并且 age>18,按照user表中的id降序
那么接口请求的PageQueryParam则为
{ "orderList": [ { "fieldName": "t0.id", "orderType": "DESC" } ], "page": 0, "pageSize": 10, "params": [ { "fieldName": "t0.userName", "fieldValue": "张三", "operator": "LIKE_R" }, { "fieldName": "t0.age", "fieldValue": 18, "operator": "GT" } ] }使用Joya查询方法有如下两种：
- 分页查询(指定join字段)
PageQueryParam pageQueryParam=JSON.parseObject(jsonString,PageQueryParam.class); PageResult<UserDTO> pageResult=joyaRepository.queryPage(pageQueryParam,UserDTO.class,j->j.join(UserPo::getId,UserExtendPo::getUserId));- 分页查询(指定where语句中的联接字段)
PageQueryParam pageQueryParam1=JSON.parseObject("",PageQueryParam.class); //附加where pageQueryParam1.setSpecificW("t0.id=t1.userId"); //指定要查询的表，顺序要注意,因为别名按照先后顺序来生成 PageResult<UserDTO> pageResult=joyaRepository.queryPage(pageQueryParam,UserDTO.class,UserPo.class,UserExtendPo.class);在Joya中,where 查询语句支持常用查询,也可以通过specificW方法来添加特殊查询语句
.where(w -> w .eq() // = .gt() // > .lt() // < .gte() // >= .lte() // <= .isNull() // is null .isNotNull() // is not null .in() // in .notIn() // not in .endsWith() // like '%a' .contains() // like '%a%' .startsWith() // like 'a%' .specificS()) //指定特殊的查询语句where 查询语句默认是AND条件联接,可以选择OR条件联接
//SELECT t0.id,t0.user_name,t0.user_email,t0.user_phone,t0.age,t1.user_id,t1.salt,t1.account_expired_time,t1.password_expired_time,t1.ext_data FROM user AS t0, user_extend AS t1 WHERE t0.user_name = ?1 OR t0.user_name = ?2 OR ( t0.age >= ?2 AND t0.user_phone LIKE ?2 ) NativeSqlQuery query=NativeSqlQuery.builder() .select() .from(UserPo.class,UserExtendPo.class) .where(w->w .eq(UserPo::getUserName,"张三") .eq(UserPo::getUserName,"李四") .subQuery(q->q.gte(UserPo::getAge,18).startsWith(UserPo::getUserPhone,"1385")),true) .build();fork本项目,添加features或bugfix,提交Pull Requests
Joya 遵守 Apache License 2.0 许可证。