此文以个人开发记录为目的,笔拙勿喷
项目是背景是公司的E签宝平台VIP频道项目进行关联账户增加后,需要做删除时的,联合查询
当前主要表结构账户表Account、
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
CREATE TABLE `account` ( `id` int(15) NOT NULL auto_increment, `email` varchar(30) default NULL COMMENT '邮箱地址', `mobile` varchar(15) default NULL COMMENT '企业为法人手机号', `loginPwd` varchar(50) default NULL COMMENT '登录口令', `signPwd` varchar(50) default NULL COMMENT '签名口令', `type` int(5) default NULL COMMENT '账户类型,1-个人账户,2-企业账户', `status` int(5) default NULL COMMENT '状态,1-非实名,9-实名', `authProject` varchar(50) default NULL COMMENT '实名认证类型,1-管理员审核,2-二代证设备审核,3-app审核', `projRNLevel` int(2) default NULL, `createDate` timestamp NULL default NULL COMMENT '创建时间', `modifyDate` timestamp NULL default NULL, `personId` int(15) default NULL COMMENT '个人账户id', `organizeId` int(15) default NULL COMMENT '企业账户id', `pwdRequest` varchar(100) default NULL COMMENT '找回密码问题', `pwdAnswer` varchar(100) default NULL COMMENT '找回密码问题答案', `balance` double default NULL COMMENT '余额', `rate` double default NULL COMMENT '优惠比率', `overBalance` int(2) default '0' COMMENT '是否允许超额消费', `alipayId` varchar(30) default NULL COMMENT '支付宝账户id', `payCoin` double(10,2) default NULL COMMENT '消费额度', `rejReason` varchar(50) default NULL COMMENT '驳回理由', `checkPrice` double default NULL COMMENT '实名认证金额', `bankAccount` varchar(100) default NULL COMMENT '银行账户名', `bankNum` varchar(50) default NULL COMMENT '账户号', `bank` varchar(100) default NULL COMMENT '银行名称', `head` varchar(100) default NULL, `alertset` varchar(40) default NULL COMMENT '消息推送设置', `pwdRequest2` varchar(100) default NULL COMMENT '密保问题2', `pwdAnswer2` varchar(100) default NULL COMMENT '密保问题答案2', `accountUid` varchar(40) default NULL COMMENT '唯一标识账户的uuid,后续作为主键', `checktimes` int(2) default '0' COMMENT '已经校验失败次数', PRIMARY KEY (`id`), KEY `fk_account_person` (`personId`), KEY `fk_account_organize` (`organizeId`), KEY `accountUid` (`accountUid`), CONSTRAINT `fk_account_organize_1` FOREIGN KEY (`organizeId`) REFERENCES `organize` (`id`), CONSTRAINT `fk_account_person_1` FOREIGN KEY (`personId`) REFERENCES `person` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=821 DEFAULT CHARSET=utf8
关联账户表refAcocunt
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
CREATE TABLE `accountref` ( `id` varchar(40) NOT NULL, `accountUid` varchar(40) NOT NULL COMMENT '账户ID', `refAccountUid` varchar(40) NOT NULL COMMENT '被导入账户ID', `createDate` timestamp NULL default NULL COMMENT '创建日期', `modifyDate` timestamp NULL default NULL COMMENT '修改日期', PRIMARY KEY (`id`), KEY `fk_ref_accountref01` USING BTREE (`accountUid`), KEY `fk_ref_accountref02` (`refAccountUid`), CONSTRAINT `fk_ref_accountref01` FOREIGN KEY (`accountUid`) REFERENCES `account` (`accountUid`), CONSTRAINT `fk_ref_accountref02` FOREIGN KEY (`refAccountUid`) REFERENCES `account` (`accountUid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='InnoDB free: 8192 kB; (`refAccountId`) REFER `esign/account`'
业务需求是需要通过关联账户表refAccount的两个外键accountUid,refAccountUid关联账户表的accountUid,
本例使用hibernate反向工程生成DAO和实体对象
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
// Fields private Integer id; private Person person; private Organize organize; private String email; private String mobile; private String loginPwd; private String signPwd; private Integer type; private Integer status; private String authProject; private Integer projRnlevel; private Date createDate; private Date modifyDate; private String pwdRequest; private String pwdAnswer; private Double balance; private Double rate; private Integer overBalance; private String alipayId; private Double payCoin; private String rejReason; private Double checkPrice; private String bankAccount; private String bankNum; private String bank; private String head; private String alertset; private String pwdRequest2; private String pwdAnswer2; private String accountUid; //set and getter
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
// Fields private String id; private Account accountByRefAccountUid; private Account accountByAccountUid; private Date createDate; private Date modifyDate; // Constructors
注意,Accountref内的外键列并不是以java8种常规类型存在,而是Hibernate以实体对象映射的形式。
在反向工程生成的AccountrefDAO中自定义通过2个外键查询的方法,关键在于HQL查询的情况下,需要使用 ref.accountByAccountUid.accountUid,而不是ref.accountUid
1 public Accountref findByUID(String accountUid, String refAccountUid) { 2 log.debug("finding AccountRef instance with instance: "); 3 try { 4 String queryString = "from Accountref as ref where ref.accountByAccountUid.accountUid = ? and ref.accountByRefAccountUid.accountUid = ?"; 5 Query queryObject = getSession().createQuery(queryString); 6 queryObject.setParameter(0, accountUid); 7 queryObject.setParameter(1, refAccountUid); 8 List list = queryObject.list(); 9 if(list.size()>0){ 10 return (Accountref) list.get(0); 11 } 12 return null; 13 } catch (RuntimeException re) { 14 log.error("find by property name failed", re); 15 throw re; 16 } 17 }