@iamfox
2015-07-02T16:20:43.000000Z
字数 23589
阅读 2342
由于英迈思乐虎网交付的代码对持久层的封装原始且难用,进行查询需要编写大量的SQL拼接,关联查询还要依赖大量的数据库视图,对开发业务逻辑操作的效率和代码可维护性影响巨大,因此针对目前数据库表重构了业务实体,并通过对Hibernate进一步封装,即保留了Hibernate的开发效率高的特性,也要追求接近于Mybatis的灵活性和SQL性能。
以下是基本改造思路:
数据库不动,另建java工程对原有库表进行操作,针对现有库表建立新的service层,dao层和entity业务实体。
目前新建了两个maven工程如下:
工程 | 主要内容 | 依赖 |
---|---|---|
hy-lehu-application | 包含service、dao层、entity类 | hy-lehu-pojo、lazypack-common |
hy-lehu-pojo | 包含跨层传递的参数对象,各种javabean | lazypack-common |
这两个工程也可以用来开发乐虎网上后续可以独立开发的新增功能,接下来将用它来实现对wap端进行2000个入口的微信推广功能中的业务逻辑和实体、库表开发。
将来模块化设计后还会对这两个工程做细化拆分。
所有代码已提交至新的git服务器,地址是:http://192.168.19.21:8082/gitblit
开发人员可以用自己的帐号登录获取代码签出地址,帐号规则和默认密码同禅道。
lazypack-common是一个自行开发的工具包,其最重要的功能就是围绕Hibernate封装了一套极简的操作框架,此外还有些常用工具类。
工具及基本封装类工程lazypack-common,jar包及源码包已上传至http://192.168.19.21:8081/nexus
<dependency>
<groupId>lazypack</groupId>
<artifactId>lazypack-common</artifactId>
<version>0.1</version>
</dependency>
以下代码可以直观地看出如何去使用封装好的工具,这些代码通常是写在Action
或Controller
中的代码,不需要往Service
,Dao
中添加任何方法就能运行,依赖lazypack-common
的基本封装类和方法。
全部代码已经通过测试,JUNIT测试类位于hy-lehu-application
工程src/test/java
目录下。
按id查询:
UserQO qo = new UserQO();
// 按用户id查询
qo.setId(2550L);
User user = userServiceImpl.queryUnique(qo);
效果:
select
*
from
( select
this_.ID as ID1_2_1_,
this_.LOGIN_NAME as LOGIN_NA2_2_1_,
this_.PASSWORD as PASSWORD3_2_1_,
this_.USER_NAME as USER_NAM4_2_1_,
...字段过多,这里是全部字段查询,此处省略
from
T_USER this_
where
this_.ID=2550L )
where
rownum <= 1
加上字段投影筛选:
UserQO qo = new UserQO();
qo.setId(2550L);
// 只查两个字段
String[] projs = {"id","authInfo.userName"};
qo.setProjectionProperties(projs);
User user = userServiceImpl.queryUnique(qo);
注:第二个字段写成
authInfo.userName
是因为userName
这个属性放在User
中的名为authInfo
的值对象中,见4.1.1。
效果:
select
*
from
( select
this_.ID as y0_,
this_.USER_NAME as y1_
from
T_USER this_
where
this_.ID=2550 )
where
rownum <= 1
多条查询:
UserQO qo = new UserQO();
// 查所在城市标识为999的用户
qo.setCity(999);
String[] projs = {"id","authInfo.userName"};
qo.setProjectionProperties(projs);
List<User> userList = userServiceImpl.queryList(qo);
效果:
select
this_.ID as y0_,
this_.USER_NAME as y1_
from
T_USER this_
where
this_.CITY=999
分页查询:
UserQO qo = new UserQO();
// 查第二页,每页五条
Pagination pagination = new Pagination();
pagination.setPageNo(2);
pagination.setPageSize(5);
pagination.setCondition(qo);
qo.setCity(999);
String[] projs = {"id","authInfo.userName"};
qo.setProjectionProperties(projs);
pagination = userServiceImpl.queryPagination(pagination);
List<User> list = (List<User>) pagination.getList();
效果:
select
count(*) as y0_
from
T_USER this_
where
(
this_.CITY=?
)
select
*
from
( select
row_.*,
rownum rownum_
from
( select
this_.ID as y0_,
this_.USER_NAME as y1_
from
T_USER this_
where
(
this_.CITY=999
) ) row_
where
rownum <= 10
)
where
rownum_ > 5
范围查询:
Calendar c1 = Calendar.getInstance();
c1.set(Calendar.YEAR, 2015);
c1.set(Calendar.MONTH, 4);
c1.set(Calendar.DAY_OF_MONTH, 1);
UserQO qo = new UserQO();
// 查注册日期在5月1日至今的用户
qo.setGtRegTimestamp(c1.getTime());
qo.setLtRegTimestamp(new Date());
String[] projs = {"id","authInfo.userName"};
qo.setProjectionProperties(projs);
userServiceImpl.queryList(qo);
效果:
select
this_.ID as y0_,
this_.USER_NAME as y1_
from
T_USER this_
where
this_.REGISTRATIONTIMETAMP<=?
and this_.REGISTRATIONTIMETAMP>=?
注:很遗憾目前的乐虎数据库不支持这条日期范围查询SQL,因为数据库里日期并没有被设为DATE型,而受制于该版本的jdbc驱动,如果将
java.util.Date
映射到其Timestamp
类型上会产生异常,只能暂时使用String
来映射Timestamp
。对数据库中使用非DATE类型的日期,需要另行编写SQL。
立即加载被配置成lazy的关联实体
UserQO qo = new UserQO();
qo.setId(2550L);
qo.setFetchUserGrade(true);
String[] projs = {"id","authInfo.userName"};
qo.setProjectionProperties(projs);
User user = userServiceImpl.queryUnique(qo);
效果:
select
this_.ID as y0_,
this_.USER_NAME as y1_
from
T_USER this_
where
this_.ID=2550
上面没有出现join效果,这是因为我们筛选了列,并且没有获取UserGrade实体里的列,所以Hibernate给优化掉了。
修改上面的条件:
UserQO qo = new UserQO();
qo.setId(2550L);
qo.setFetchUserGrade(true);
// String[] projs = {"id","authInfo.userName"};
// qo.setProjectionProperties(projs);
User user = userServiceImpl.queryUnique(qo);
效果:
select
*
from
( select
this_.ID as ID1_2_1_,
this_.LOGIN_NAME as LOGIN_NA2_2_1_,
this_.PASSWORD as PASSWORD3_2_1_,
...省略
this_.SIGNDAY_TIME as SIGNDAY54_2_1_,
this_.SIGNDAY_TIME_TAMP as SIGNDAY55_2_1_,
usergrade2_.ID as ID1_3_0_,
usergrade2_.CREATETIME as CREATETI2_3_0_,
usergrade2_.CREARTTIMETAMP as CREARTTI3_3_0_,
...省略
usergrade2_.MIN_GROWTH_VALUE as MIN_GRO16_3_0_,
usergrade2_.SORT as SORT17_3_0_
from
T_USER this_
left outer join
T_USER_GRADE usergrade2_
on this_.USER_GRADE_ID=usergrade2_.ID
where
this_.ID=2550 )
where
rownum <= 1
两张表的所有字段都出来了,然后我们会需要筛选。要动用到关联实体的QO。
UserQO qo = new UserQO();
qo.setId(2550L);
// 从User取两个字段
String[] projs = {"id","authInfo.userName"};
qo.setProjectionProperties(projs);
// 从UserGrade取两个字段
UserGradeQO userGradeQO = new UserGradeQO();
String[] projs2 = {"id", "baseInfo.title"};
userGradeQO.setProjectionProperties(projs2);
// 将两个查询条件对象合二为一
qo.setUserGradeQO(userGradeQO);
User user = userServiceImpl.queryUnique(qo);
因为操作了关联实体的查询条件,所以我们还要在Dao
里做些额外工作让上面的qo.setUserGradeQO(userGradeQO);
生效。这里只有片段,完整的UserDao
见4.3.3:
@Override
protected Criteria buildCriteria(Criteria criteria, UserQO qo) {
if (qo != null) {
// 将关联表的查询条件纳入条件解析
if(qo.getUserGradeQO() != null){
// 创建一个子查询Criteria用于组装UserGrade实体上的查询条件
Criteria gradeCriteria = criteria.createCriteria("grade", "g",
JoinType.LEFT_OUTER_JOIN);
// 将UserGradeQO里的查询条件加载到子查询Criteria里
gradeDao.buildCriteriaOut(gradeCriteria, qo.getUserGradeQO());
}
return criteria;
}
return criteria;
}
开发人员要写的所有查询代码已经全部在上面了,没有其他要写的了。
效果:
select
*
from
( select
this_.ID as y0_,
this_.USER_NAME as y1_,
g1_.ID as y2_,
g1_.TITLE as y3_
from
T_USER this_
left outer join
T_USER_GRADE g1_
on this_.USER_GRADE_ID=g1_.ID
where
this_.ID=2550 )
where
rownum <= 1
同时按User和UserGrade里的值做条件查询:
UserQO qo = new UserQO();
// 按邮箱查
qo.setEmail("745040361@qq.com");
String[] projs = {"id","authInfo.userName"};
qo.setProjectionProperties(projs);
UserGradeQO userGradeQO = new UserGradeQO();
String[] projs2 = {"id", "baseInfo.title"};
userGradeQO.setProjectionProperties(projs2);
// 按等级名称查
userGradeQO.setTitle("普通会员");
qo.setUserGradeQO(userGradeQO);
User user = userServiceImpl.queryUnique(qo);
效果:
select
*
from
( select
this_.ID as y0_,
this_.USER_NAME as y1_,
g1_.ID as y2_,
g1_.TITLE as y3_
from
T_USER this_
left outer join
T_USER_GRADE g1_
on this_.USER_GRADE_ID=g1_.ID
where
(
this_.EMAIL='abc@163.com'
)
and g1_.TITLE='普通会员' )
where
rownum <= 1
UserQO qo = new UserQO();
qo.setEmail("745040361@qq.com");
qo.setEmailLike(true);
String[] projs = {"id","authInfo.userName"};
qo.setProjectionProperties(projs);
User user = userServiceImpl.queryUnique(qo);
效果:
select
*
from
( select
this_.ID as y0_,
this_.USER_NAME as y1_
from
T_USER this_
where
(
this_.EMAIL like ?
) )
where
rownum <= 1
看完了上面的效果,现在看一下要完成这样的自动化查询效果,都需要Service层以下提供哪些类。
以用户表(T_USER
),用户会员级别表(T_USER_GRADE
),汇银地点表(T_SIDE_HUIYIN
)、行业信息表(T_INDUSTRY_HOBBY
)为例。
四张表的关联关系是
T_USER
与T_USER_GRADE
多对一, 每个会员级别有多个用户,每个用户只属于一个会员级别,外键是USER_GRADE_ID
。
T_USER
与T_SIDE_HUIYIN
多对一,每个用户有一个注册/登录的附近门店,外键是NEARBYID
,即T_SIDE_HUIYIN
的主键。
T_USER
与T_INDUSTRY_HOBBY
多对一。
以该四张表建立四个对应的主要根实体User
,UserGrade
,SideHuiyin
,Industry
。
由于表字段过多,全放在一个实体类里会造成阅读和维护困难,因此对其中三个实体进行了值对象的拆分,将字段分散到不同的组件对象里,多个对象映射一张表,如下图:
用户的字段被拆分到了UserBaseInfo
,UserAuthInfo
,UserBindingIds
,UserContactInfo
,UserStatus
几个对象中,所有字段映射全注解。与数据库的映射使用了Hibernate的Component机制。以下贴出两个实体类的代码,其余的类似:
注:由于数据库表历史原因及oracle版本和对应的jdbc驱动包所限,某些字段映射类型不合理,暂迁就历史数据库,确保能正常运行。以下代码均已经过测试。
package com.lehumall.domain.user.user;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.Table;
import com.lehumall.domain.base.Industry;
import com.lehumall.domain.user.sidehuiyin.SideHuiyin;
import com.lehumall.domain.user.usergrade.UserGrade;
import lazypack.common.component.LongIdBaseEntity;
@Entity
@Table(name = "T_USER")
@SuppressWarnings("serial")
public class User extends LongIdBaseEntity {
/**
* 用户基本信息
*/
private UserBaseInfo baseInfo;
/**
* 用户认证信息
*/
private UserAuthInfo authInfo;
/**
* 用户绑定的外部标识
*/
private UserBindingIDs bindingIDs;
/**
* 用户联系信息
*/
private UserContactInfo contactInfo;
/**
* 用户状态信息
*/
private UserStatus status;
/**
* 用户所属行业
*/
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "INDUSTRY_ID")
private Industry industry;
/**
* 用户会员等级
*/
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "USER_GRADE_ID")
private UserGrade grade;
/**
* 注册/登录的附近门店
*/
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "NEARBYID")
private SideHuiyin nearBy;
// getter/setter省略
}
package com.lehumall.domain.user.user;
import javax.persistence.Column;
import javax.persistence.Embeddable;
/**
* 用户认证信息
* @author yuxiaoxiang
*
*/
@Embeddable
public class UserAuthInfo {
/**
* 用户名
*/
@Column(name = "USER_NAME")
private String userName;
/**
* 登录名(彩票用户)
*/
@Column(name = "LOGIN_NAME")
private String loginName;
/**
* 密码
*/
@Column(name = "PASSWORD")
private String password;
}
package com.lehumall.domain.user.user;
import javax.persistence.Column;
import javax.persistence.Embeddable;
/**
* 用户基本信息
*
* @author yuxiaoxiang
*
*/
@Embeddable
public class UserBaseInfo {
/**
* 真实姓名
*/
@Column(name = "REAL_NAME")
private String realName;
/**
* 性别
*/
@Column(name = "SEX")
private Integer sex;
public final static Integer SEX_MALE = 1; // 男
public final static Integer SEX_FEMAIL = 0; // 女
/**
* 生日
*/
@Column(name = "BIRTHDAY")
private String birthday;
/**
* 身份证号
*/
@Column(name = "CARD_NUMBER")
private String cardNumber;
/**
* 婚姻状态
*/
@Column(name = "MARITAL_STATUS")
private Integer maritalStatus;
public final static Integer MARITAL_STATUS_NO = 1; // 未婚
public final static Integer MARITAL_STATUS_YES = 2; // 已婚
public final static Integer MARITAL_STATUS_SECRET = 3; // 保密
/**
* 月收入 月收入(1,2000元以下,2.2000-3999元 3.4000-5999元,4,6000-7999元,5,8000元以上)
*/
@Column(name = "MONTHLY_INCOME")
private Integer monthlyIncome;
public final static Integer MONTHLY_INCOME_2000 = 1;
public final static Integer MONTHLY_INCOME_2000_3999 = 2;
public final static Integer MONTHLY_INCOME_4000_5999 = 3;
public final static Integer MONTHLY_INCOME_6000_7999 = 4;
public final static Integer MONTHLY_INCOME_8000 = 5;
/**
* 教育程度( 1初中, 2高中,3中专,4大专,5本科,6硕士,7博士,8其他)
*/
@Column(name = "EDUCATION")
private Integer education;
public final static Integer EDUCATION_CZ = 1;
public final static Integer EDUCATION_GZ = 2;
public final static Integer EDUCATION_ZZ = 3;
public final static Integer EDUCATION_DZ = 4;
public final static Integer EDUCATION_BK = 5;
public final static Integer EDUCATION_SS = 6;
public final static Integer EDUCATION_BS = 7;
public final static Integer EDUCATION_QT = 8;
/**
* 用户头像
*/
@Column(name = "USER_URL")
private String userUrl;
/**
* 排序
*/
@Column(name = "SORT")
private Integer sort;
/**
* 来源: 1: web 2: pc 3:android, 4:ios 5:wp 6.微信
*/
@Column(name = "ORIGIN")
private Integer origin;
public final static Integer ORIGIN_WEB = 1;
public final static Integer ORIGIN_PC = 2;
public final static Integer ORIGIN_ANDROID = 3;
public final static Integer ORIGIN_IOS = 4;
public final static Integer ORIGIN_WP = 5;
public final static Integer ORIGIN_WX = 6;
/**
* 用户类型(1,个人用户。2,企业用户)
*/
@Column(name = "USER_TYPE")
private Integer type;
public final static Integer TYPE_PERSON = 1; // 个人用户
public final static Integer TYPE_COMPANY = 2; // 企业用户
/**
* 是否是老用户1,不是老用户 2是老用户
*/
@Column(name = "OLD_TYPE")
private Integer oldType;
public final static Integer OLD_TYPE_NO = 1; // 不是老用户
public final static Integer OLD_TYPE_YES = 2; // 是老用户
/**
* 注册时间
*/
@Column(name = "REGISTRATION_TIME")
private String registrationTime;
/**
* 注册时间戳
*/
@Column(name = "REGISTRATIONTIMETAMP")
private String registrationTimestamp;
}
package com.lehumall.domain.user.user;
import javax.persistence.Column;
import javax.persistence.Embeddable;
/**
* 用户绑定的标识
*
* @author yuxiaoxiang
*
*/
@Embeddable
public class UserBindingIds {
/**
* 手机设备号
*/
@Column(name = "PHONE_TOKEN")
private String phoneToken;
/**
* QQ登录的OPEN ID
*/
@Column(name = "QQ_OPEN_ID")
private String qqOpenId;
/**
* 微博登录的OPEN ID
*/
@Column(name = "WEIBO_OPEN_ID")
private String weiboOpenId;
/**
* 支付宝登录的OPEN ID
*/
@Column(name = "ALIPAY_OPEN_ID")
private String alipayOpenId;
/**
* 百度登录的OPEN ID
*/
@Column(name = "BAIDU_OPEN_ID")
private String baiduOpenId;
/**
* 微信登录的OPEN ID
*/
@Column(name = "WEIXIN")
private String weixinOpenId;
}
package com.lehumall.domain.user.user;
import javax.persistence.Column;
import javax.persistence.Embeddable;
@Embeddable
public class UserContactInfo {
/**
* 手机
*/
@Column(name = "PHONE")
private String phone;
/**
* 邮箱
*/
@Column(name = "EMAIL")
private String email;
/**
*/
@Column(name = "QQ")
private String qq;
/**
* 省
*/
@Column(name = "PROVINCE")
private Integer province;
/**
* 市
*/
@Column(name = "CITY")
private Integer city;
/**
* 区
*/
@Column(name = "AREA")
private Integer area;
/**
* 小区
*/
@Column(name = "DISTRICT")
private String district;
/**
* 详细地址
*/
@Column(name = "DETAILS")
private String details;
}
package com.lehumall.domain.user.user;
import javax.persistence.Column;
import javax.persistence.Embeddable;
/**
* 用户状态
* @author yuxiaoxiang
*
*/
@Embeddable
public class UserStatus {
/**
* 手机验证状态
*/
@Column(name = "PHONE_STATUS")
private Integer phoneStatus;
public final static Integer PHONE_STATUS_VALID = 1; // 已验证
public final static Integer PHONE_STATUS_UNVALID = 0; // 未验证
/**
* 邮箱验证状态
*/
@Column(name = "EMAIL_STATUS")
private Integer emailStatus;
public final static Integer EMAIL_STATUS_VALID = 1; // 已验证
public final static Integer EMAIL_STATUS_UNVALID = 0; // 未验证
/**
* 成长值
*/
@Column(name = "GROWTH_VALUE")
private Integer growthValue;
/**
* 积分
*/
@Column(name = "INTEGRAL")
private Integer integral;
/**
* 帐号状态(1 是,0否)
*/
@Column(name = "ENABLE")
private Integer enable;
/**
* 购买状态(1 是,0否)
*/
@Column(name = "BUY_STATUS")
private Integer buyStatus;
/**
* 点评状态(1 是,0否)
*/
@Column(name = "COMMENT_STATUS")
private Integer commentStatus;
/**
* 登录状态(1 是,0否)
*/
@Column(name = "LOGIN_STATUS")
private Integer loginStatus;
/**
* 举报状态(1 是,0否)
*/
@Column(name = "REPORT_STATUS")
private Integer reportStatus;
public final static Integer STATUS_ACTIVED = 1; // 已验证
public final static Integer STATUS_FORBIDDEN = 0; // 未验证
/**
* 最后登录时间
*/
@Column(name = "LOGIN_TIME")
private String loginTime;
/**
* 最后登录时间戳
*/
@Column(name = "LOGINTIMETAMP")
private String loginTimestamp;
/**
* 最后登录IP
*/
@Column(name = "LOGIN_IP")
private String loginIP;
/**
* 累计签到天数
*/
@Column(name = "SIGNDAY")
private Integer signDay;
/**
* 最后一次签到时间
*/
@Column(name = "SIGNDAY_TIME")
private String signdayTime;
/**
* 最后一次签到时间戳
*/
@Column(name = "SIGNDAY_TIME_TAMP")
private Long signdayTimetamp;
/**
* 预付卡余额
*/
@Column(name = "BALANCE")
private Double balance;
/**
* 快捷服务id,多个id逗号隔开
*/
@Column(name = "FASTIDS")
private String fastIds;
/**
* 连续登录天数
*/
@Column(name = "CONTINUE_LOGIN_COUNT")
private Integer continueLoginCount;
/**
* 密码强度 1弱 2中 3强 BYTE
*/
@Column(name = "PWD_SFAE")
private Integer pwdSafe;
public final static Integer PWD_SAFE_WEAK = 1;
public final static Integer PWD_SAFE_NORMAL = 2;
public final static Integer PWD_SAFE_STRONG = 3;
/**
* 是否获得过完善基本资料获得的积分0没有 1有
*/
@Column(name = "MZJF")
private Integer mzjf;
/**
* 是否获得完善更多资料获取的积分0没有 1有
*/
@Column(name = "MMJF")
private Integer mmjf;
/**
* 是否获得上传头像获取的积分0没有 1有
*/
@Column(name = "SCTX")
private Integer sctx;
/**
* 是否获得首次登录送虎券0没有 1有
*/
@Column(name = "SCHQ")
private Integer schq;
public final static Integer JF_STATUS_YES = 0;
public final static Integer JF_STATUS_NO = 1;
}
package com.lehumall.domain.entity.user.usergrade;
import javax.persistence.Entity;
import javax.persistence.Table;
import lazypack.common.component.LongIdBaseEntity;
/**
* 用户会员等级
*
* @author yuxiaoxiang
*
*/
@Entity
@Table(name = "T_USER_GRADE")
@SuppressWarnings("serial")
public class UserGrade extends LongIdBaseEntity {
/**
* 会员等级基本信息
*/
private UserGradeBaseInfo baseInfo;
/**
* 会员等级成长信息
*/
private UserGradeGrowthInfo growthInfo;
/**
* 会员等级优惠信息
*/
private UserGradeFavorableInfo favorableInfo;
}
package com.lehumall.domain.entity.user.usergrade;
import java.util.Date;
import javax.persistence.Column;
import javax.persistence.Embeddable;
/**
* 用户会员等级信息
*
* @author yuxiaoxiang
*
*/
@Embeddable
public class UserGradeBaseInfo {
@Column(name = "TITLE")
private String title;
@Column(name = "CREATETIME")
private String createTime;
@Column(name = "CREARTTIMETAMP")
private String createTimestamp;
@Column(name = "UPDATETIME")
private String updateTime;
@Column(name = "UPDATETIMETAMP")
private String updateTimestamp;
/**
* 是否启用
*/
@Column(name = "ENABLE")
private Integer enable;
public final static Integer ENABLE_ACTIVED = 1; // 启用
public final static Integer ENABLE_FORBIDDEN = 2; // 禁用
/**
* 等级图片
*/
@Column(name = "URL")
private String url;
/**
* 会员类型
*/
@Column(name = "TYPE")
private Integer type;
public final static Integer TYPE_PERSON = 0; // 个人
public final static Integer TYPE_COMPANY = 1; // 企业
/**
* 说明
*/
@Column(name = "EXPLAIN")
private String explain;
}
package com.lehumall.domain.entity.user.usergrade;
import javax.persistence.Column;
import javax.persistence.Embeddable;
@Embeddable
public class UserGradeGrowthInfo {
/**
* 排序
*/
@Column(name = "SORT")
private Integer sort;
/**
* 最大成长值
*/
@Column(name = "MAX_GROWTH_VALUE")
private Integer maxGrowthValue;
/**
* 最小成长值
*/
@Column(name = "MIN_GROWTH_VALUE")
private Integer minGrowthValue;
}
package com.lehumall.domain.entity.user.usergrade;
import javax.persistence.Column;
import javax.persistence.Embeddable;
/**
* 会员级别优惠信息
*
* @author yuxiaoxiang
*
*/
@Embeddable
public class UserGradeFavorableInfo {
/**
* 运费状态
*/
@Column(name = "FREE_STATUS")
private Integer freeStatus;
public final static Integer FREE_STATUS_ACTIVED = 1; // 启用
public final static Integer FREE_STATUS_FORBIDDEN = 2; // 禁用
/**
* 免运费金额
*/
@Column(name = "FREE_MONEY")
private Integer freeMoney;
/**
* 折扣状态
*/
@Column(name = "DISCOUNT_STATUS")
private Integer discountStatus;
public final static Integer DISCOUNT_STATUS_ACTIVED = 1; // 启用
public final static Integer DISCOUNT_STATUS_FORBIDDEN = 2; // 禁用
/**
* 折扣金额
*/
@Column(name = "DISCOUNT")
private Integer discount;
}
为了能从这些表里做查询,我们要把所有可用的查询条件封装到一个查询条件对象里,以方便框架能自动解析并生成SQL语句。以下列出其中两个类,其中的条件字段可以不断增加。
注:加在查询条件字段上的是一组自定义注解,有了这些注解,框架会自动解析并生成SQL,不需要再手工拼接SQL、HQL或者Criteria条件,Service和Dao也不需要自己写任何的查询方法。QO的具体用法会在后面JUNIT例子中演示。
package com.lehumall.pojo.qo;
import java.util.Date;
import lazypack.common.annotation.QueryCondition;
import lazypack.common.annotation.QueryConditionType;
import lazypack.common.annotation.QueryConfig;
import lazypack.common.component.BaseQO;
@QueryConfig(daoBeanId = "userDao")
@SuppressWarnings("serial")
public class UserQO extends BaseQO<Long> {
/**
* 按用户名查询,如果userNameLike设为true模糊查询,否则精确查询
*/
@QueryCondition(name = "authInfo.userName", ifTrueUseLike = "userNameLike")
private String userName;
/**
* 按邮箱查询,如果emailLike设为true模糊查询,否则精确查询
*/
@QueryCondition(name = "contactInfo.email", ifTrueUseLike = "emailLike")
private String email;
/**
* 按手机号查询,如果phoneLike设为true模糊查询,否则精确查询
*/
@QueryCondition(name = "contactInfo.phone", ifTrueUseLike = "phoneLike")
private String phone;
private Boolean userNameLike;
private Boolean emailLike;
private Boolean phoneLike;
/**
* 按省份代码查询
*/
@QueryCondition(name = "contactInfo.province")
private Integer province;
/**
* 按城市代码查询
*/
@QueryCondition(name = "contactInfo.city")
private Integer city;
/**
* 按地区代码查询
*/
@QueryCondition(name = "contactInfo.area")
private Integer area;
/**
* 按小区代码查询
*/
@QueryCondition(name = "contactInfo.district")
private Integer district;
@QueryCondition(name = "nearBy", type = QueryConditionType.FATCH_EAGER)
private Boolean fetchNearBy = false;
/**
* 范围查询,该条件值小于等于注册时间
*/
@QueryCondition(name = "baseInfo.registrationTimestamp", type = QueryConditionType.LE)
private Date leRegTimestamp;
/**
* 范围查询,该条件值大于等于注册时间
*/
@QueryCondition(name = "baseInfo.registrationTimestamp", type = QueryConditionType.GE)
private Date geRegTimestamp;
/**
* 用户等级信息查询条件,当需要用关联表里的字段当查询条件时使用
*/
private UserGradeQO userGradeQO;
}
注:没有
QueryConditionType
属性的注解,都是默认为and
组装和=
判断的条件。框架也有提供or条件的注解,但or
条件的组装注解可能不具有通用性,在某些情况下是or
,在另一些情况下可能会用and
,因此建议在dao
的条件组装通用方法中进行or
的处理。or
条件组装的注解如下:
/**
* 按用户名查询,如果userNameLike设为true模糊查询,否则精确查询
*/
@QueryCondition(name = "authInfo.userName", ifTrueUseLike = "userNameLike")
@QueryConditionGroup("group1")
private String userName;
/**
* 按邮箱查询,如果emailLike设为true模糊查询,否则精确查询
*/
@QueryCondition(name = "contactInfo.email", ifTrueUseLike = "emailLike")
@QueryConditionGroup("group1")
private String email;
同在一个
QueryConditionGroup
中的这两个查询条件,具有and (userName = xxx or email = xxx)
的效果
package com.lehumall.pojo.qo;
import lazypack.common.annotation.QueryCondition;
import lazypack.common.annotation.QueryConfig;
import lazypack.common.component.BaseQO;
@QueryConfig(daoBeanId = "userGradeDao")
@SuppressWarnings("serial")
public class UserGradeQO extends BaseQO<Long> {
/**
* 按等级标题查询
*/
@QueryCondition(name = "baseInfo.title")
private String title;
}
为简化演示代码,这里只设了一个根据标题查询等级的条件。
package lazypack.common.component;
import java.io.Serializable;
import java.util.List;
/**
* 基础查询类 名词解释:query object 简称 qo
*
* @author
*/
public class BaseQO<T> implements Serializable {
private static final long serialVersionUID = 1L;
/**
* 别名,在进行关联查询时设置关联对象的别名
*/
private String alias;
/**
* 需要筛选出来的实体字段名,可以带“.”进入组件对象的下一级属性。用于非select *查询
*/
private String[] projectionProperties;
/**
* 实体ID
*/
private T id;
/**
* 实体ID集合,设置后会进行in查询
*/
private List<T> ids;
// ------------------不包含的属性条件------------------
/**
* 不包含的ID集合,设置后会加上这些id的不等于条件,相当于not in查询
*/
private T[] idNotIn;
// ------------------状态类条件------------------
// 分页条件
private Integer pageNo;
private Integer pageSize;
/**
* 是否解析QO上的注解,当注解中的条件解析方式不适合自己的查询需要时关闭,进行手工条件组装
*/
private Boolean enableQueryAnnotation = true;
}
Service
类中的方法只接受两种参数,一种是QO
,一种是Command
,QO
用于查询方法,Command
用于增删改方法,它们有不同的父类,父类中定义了一些通用字段。
... 待续 ...
在具体的业务操作Service
实现类中,只有接收Command
对象的写操作,接收QO
对象的查询操作queryUnique(QO qo)
,queryList(QO qo)
,queryPagination(QO qo)
,queryCount(QO qo)
全部都在BaseServiceImpl
中。
每个具体实体泛型对应的Dao
实现类,也只有一个重写BaseDao
的Criteria buildCriteria(Criteria criteria, XXXQO qo)
方法,该方法中组装的Criteria
条件对象可复用于上述四种查询方法。
package com.lehumall.application.service.user;
import lazypack.common.component.BaseServiceImpl;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import com.lehumall.application.dao.user.UserDao;
import com.lehumall.domain.entity.user.user.User;
import com.lehumall.pojo.qo.UserQO;
@Service
@Transactional
public class UserServiceImpl extends BaseServiceImpl<User, UserQO, UserDao> {
@Autowired
private UserDao userDao;
@Override
protected UserDao getDao() {
return userDao;
}
}
package com.lehumall.application.service.user;
import lazypack.common.component.BaseServiceImpl;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import com.lehumall.application.dao.user.UserGradeDao;
import com.lehumall.domain.entity.user.usergrade.UserGrade;
import com.lehumall.pojo.qo.UserGradeQO;
@Service
@Transactional
public class UserGradeServiceImpl extends BaseServiceImpl<UserGrade, UserGradeQO, UserGradeDao> {
@Autowired
private UserGradeDao userGradeDao;
@Override
protected UserGradeDao getDao() {
return userGradeDao;
}
}
package com.lehumall.application.dao.user;
import lazypack.common.component.BaseDao;
import org.hibernate.Criteria;
import org.hibernate.sql.JoinType;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import com.lehumall.domain.entity.user.user.User;
import com.lehumall.pojo.qo.UserQO;
@Repository
public class UserDao extends BaseDao<User, UserQO> {
@Autowired
private UserGradeDao gradeDao;
@Override
protected Criteria buildCriteria(Criteria criteria, UserQO qo) {
if (qo != null) {
// 这里可以将在UserQO的注解中没有完善的更多查询条件加入criteria,实现更复杂的查询
// ...
// 将关联表的查询条件纳入条件解析
if(qo.getUserGradeQO() != null){
Criteria gradeCriteria = criteria.createCriteria("grade", "g",
JoinType.LEFT_OUTER_JOIN);
gradeDao.buildCriteriaOut(gradeCriteria, qo.getUserGradeQO());
}
return criteria;
}
return criteria;
}
@Override
protected Class<User> getEntityClass() {
return User.class;
}
}
package com.lehumall.application.dao.user;
import lazypack.common.component.BaseDao;
import org.hibernate.Criteria;
import org.springframework.stereotype.Repository;
import com.lehumall.domain.entity.user.usergrade.UserGrade;
import com.lehumall.pojo.qo.UserGradeQO;
@Repository
public class UserGradeDao extends BaseDao<UserGrade, UserGradeQO> {
@Override
protected Criteria buildCriteria(Criteria criteria,UserGradeQO qo) {
if (qo != null) {
// 这里可以将在UserGradeQO的注解中没有完善的更多查询条件加入criteria,实现更复杂的查询
// ...
return criteria;
}
return criteria;
}
@Override
protected Class<UserGrade> getEntityClass() {
return UserGrade.class;
}
}
save(...)
或update(...)
或delete()
,其中又以查询最为复杂,如果封装做得不好会出现诸多问题,比如N+1。Dao
中对Criteria
增加补充条件搞定,最后2%通过HQL
或SQL
搞定。极大减少在数据库操作上耗费的开发时间。NULL
值条件自动忽略。