@Repository()
public class AccountRoleRelDAO {
@PersistenceContext
public EntityManager em;
@Transactional(readOnly = true)
public List<Object[]> getCashAccount(int accountId) {
Session session = em.unwrap(Session.class);
SQLQuery q = session.createSQLQuery(
"select {account_role_rel.*}," +
"{account.*}," +
"{role.*} " +
"from account_role_rel " +
"inner join role on " +
"role.id=account_role_rel.role_id " +
"inner join account on " +
"account.id=account_role_rel.account_id " +
"where account_role_rel.account_id=:accountId"
);
q.setParameter("accountId", accountId);
q.addEntity("account_role_rel", AccountRoleRelModel.class);
/* public abstract SQLQuery addJoin(String tableAlias, String path)
* Declare a join fetch result.
* Parameters:
* tableAlias - The SQL table alias for the data to be mapped to this fetch
* path - The association path ([owner-alias].[property-name]).
*/
//根据上述定义，addJoin的第一个参数，应该是需要join的表在SQL语句中的名字，如果我们给对应表声明了别名的话，这里填的应该是别名
//后面应该是主表在SQL中的名字，有别名用别名，然后.后面接实体中，Join的模型的变量的名字
//例如AccountRelModel中我们声明AccountModel Join的变量名为account
//因此这里填的是account_role_rel.account
q.addJoin("account", "account_role_rel.account");
//同上
q.addJoin("role", "account_role_rel.role");
//返回的内容List<Object[AccountRelModel, AccountModel, RoleModel]>，顺序根据上面addEntity、addJoin的顺序产生对应的Object[]内用的顺序
return (List<Object[]>) q.list();
}
}
