这里有一个名为“join query”的联接查询。
我需要将这个查询设置到我的bean中,是否可以使用行映射器实现,或者有其他方式可以实现。我在此发布了我练习的代码,它是否可行?如果不正确,请给我建议。
在DAOImpl中:
SELECT um.USER_ID, um.USERNAME, um.PASSWORD, um.AGENCIA,
um.EMAIL, um.GRABADO_POR, um.MOBILENUMBER,
um.USER_STATUS, um.ZONE, um.NAME,
um.USER_TYPE, urmm.USERROLEMAPPING_ID,
r.ROLE_ID, r.ROLE_NAME, r.PRIORITY,
rcmm.COMPONENT_ID, am.ACTION_ID, am.ACTION_NMAE,
cm.COMPONENTID, cm.COMPONENTNAME, cm.COMPONENTIDENTIFICATION,
cm.COMPONENTSTATE
FROM USER_MASTER um, role r, USER_ROLE_MAPPING_MASTER urmm,
ACTION_MASTER am, ROLE_COMPONENT_MAPPING_MASTER rcmm,
ACTION_COMPONENT_MAPPINGMASTER acm,COMPONENT_MASTER cm
WHERE upper(um.USERNAME) = upper(?) AND um.USER_ID = urmm.USER_ID AND
urmm.ROLE_ID = r.ROLE_ID AND r.ROLE_ID = rcmm.ROLE_ID AND
urmm.ROLE_ID = rcmm.ROLE_ID AND
acm.ACTION_ID = am.ACTION_ID AND
rcmm.COMPONENT_NAME = acm.COMPONENT_NAME AND
acm.COMPONENT_NAME=cm.COMPONENTNAME(+)
我需要将这个查询设置到我的bean中,是否可以使用行映射器实现,或者有其他方式可以实现。我在此发布了我练习的代码,它是否可行?如果不正确,请给我建议。
在DAOImpl中:
@Override
public List<User> getUserTest(String username) {
final List<User> userList=new ArrayList<User>();
String query="SELECT um.USER_ID, um.USERNAME, um.PASSWORD, um.AGENCIA, um.EMAIL, um.GRABADO_POR, um.MOBILENUMBER, um.USER_STATUS, um.ZONE, um.NAME, um.USER_TYPE, urmm.USERROLEMAPPING_ID, r.ROLE_ID, r.ROLE_NAME, r.PRIORITY, rcmm.COMPONENT_ID, am.ACTION_ID, am.ACTION_NMAE, cm.COMPONENTID, cm.COMPONENTNAME, cm.COMPONENTIDENTIFICATION, cm.COMPONENTSTATE FROM USER_MASTER um, role r, USER_ROLE_MAPPING_MASTER urmm, ACTION_MASTER am, ROLE_COMPONENT_MAPPING_MASTER rcmm, ACTION_COMPONENT_MAPPINGMASTER acm,COMPONENT_MASTER cm WHERE upper(um.USERNAME)=upper(?) AND um.USER_ID =urmm.USER_ID AND urmm.ROLE_ID =r.ROLE_ID AND r.ROLE_ID =rcmm.ROLE_ID AND urmm.ROLE_ID =rcmm.ROLE_ID AND acm.ACTION_ID =am.ACTION_ID AND rcmm.COMPONENT_NAME =acm.COMPONENT_NAME AND acm.COMPONENT_NAME=cm.COMPONENTNAME(+)";
System.out.println("in dao");
try{
List<Object> users = getJdbcTemplate().query(query, new Object[] { username },
new RowMapper<Object>(){
@Override
public Object mapRow(ResultSet rs, int arg1)
throws SQLException {
User user=new User();
user.setId(rs.getInt(1));
user.setUsername(rs.getString(2));
user.setPassword(rs.getString(3));
user.setAgencia(rs.getString(4));
user.setEmail(rs.getString(5));
user.setGrabadoPor(rs.getString(6));
user.setMobileNumber(rs.getString(7));
user.setUserStatus(rs.getString(8));
user.setZone(rs.getString(9));
user.setName(rs.getString(10));
user.setUserType(rs.getString(11));
Set<UserRole> urole=user.getUserRoles();
if(urole == null){
urole = new HashSet<UserRole>();
}
UserRole userRole=new UserRole();
userRole.setId(rs.getInt(12));
// System.out.println("id is user role"+user.getId());
userRole.setUser(user);
Role role=userRole.getRole();
if(role == null){
role = new Role();
}
role.setRoleId(rs.getInt(13));
role.setRoleName(rs.getString(14));
role.setPriority(rs.getInt(15));
role.setUserRoles(urole);
Set<RoleAction> roleAction=role.getRoleActions();
if(roleAction == null){
roleAction = new HashSet<RoleAction>();
}
RoleAction roleaction=new RoleAction();
roleaction.setId(rs.getInt(16));
roleaction.setRole(role);
Action action= roleaction.getAction();
if(action == null){
action = new Action();
}
action.setActionId(rs.getInt(17));
action.setActionName(rs.getString(18));
Set<CustomizableMenus> custmenusSet=action.getCustomizablemenu();
if(custmenusSet == null){
custmenusSet = new HashSet<CustomizableMenus>();
}
CustomizableMenus custmenus=new CustomizableMenus();
custmenus.setComponentId(rs.getInt(19));
custmenus.setComponentName(rs.getString(20));
custmenus.setComponentIdentification(rs.getString(21));
custmenus.setComponentState(rs.getString(22));
custmenusSet.add(custmenus);
action.setCustomizablemenu(custmenusSet);
System.out.println("action id and name is "+rs.getInt(17)+" "+rs.getString(18));
action.setRoleActions(roleAction);
roleaction.setAction(action);
roleAction.add(roleaction);
role.setRoleActions(roleAction);
userRole.setRole(role);
urole.add(userRole);
user.setUserRoles(urole);
userList.add(user);
System.out.println("cust menus in db custid "+ custmenus.getComponentId()+"component name is "+custmenus.getComponentName()+"identification "+custmenus.getComponentIdentification());
return user;
}
});
if (userList.size()>0) {
System.out.println("the list is "+ userList);
return userList;
}
}catch(Exception e){
e.printStackTrace();
}
return null;
}
user.java
private Integer id;
private String username;
private String password;
private String name;
private String email;
private String mobileNumber;
private String reTypeMobileNumber;
private String status;
private String userType;
private String userDescription;
private String grabadoPor;
private String userStatus;
private String zone;
private String agencia;
private Set<UserRole> userRoles = new HashSet<UserRole>(0);
UserRole.java
public class UserRole implements Serializable{
private Integer id;
private User user;
private Role role;
Role.java
public class Role implements Serializable{
private Integer roleId;
private String roleName;
private Integer priority;
private String roleType;
private String roleDescription;
private String roledentification;
private String grabadoPor;
private Date fechaDeCreacion;
private Date fechaDeModificada;
private Set<UserRole> userRoles = new HashSet<UserRole>(0);
private Set<RoleAction> roleActions = new HashSet<RoleAction>(0);
RoleAction.java
public class RoleAction implements Serializable{
private Integer id;
private Role role;
private Action action;
Action.java
public class Action implements Serializable{
private Integer actionId;
private String actionName;
private String actionType;
private String url;
private Date fechaDeCreacion;
private Date fechaDeModificada;
private Set<RoleAction> roleActions = new HashSet<RoleAction>(0);
private Set<CustomizableMenus> customizablemenu=new HashSet<CustomizableMenus>(0);
CustomizableMenus.java
public class CustomizableMenus implements Serializable{
private Integer componentId;
private String componentName;
private String componentIdentification;
//private String componentType;
private String componentState;
能否减少代码量?使用 Hibernate 后,它只返回一个用户对象,其中包含所有与用户相关的角色、角色相关的操作和操作相关的自定义菜单,因此我返回 list.get(0)。我的联接查询返回了他拥有多少个操作,它会返回相应数量的记录。所以我得到了多个用户对象,如果我将用户列表发送到前端,它会返回查询返回的最后一条记录。