How to get data join four tables with Spring mvc and MyBatis

Question

I'm using Spring and MyBatis, I'm facing with the problem when join four tables to get data in JSP my model:

User:

public class User {     
   private Long user_id;
   private GroupMaster group_id; 
   private String login_name;
   private String password;
   private String full_name;
   private String full_name_kana;
   private String email;
   private String tel;
   private Date birthday;
   private boolean rule;
   private String salt;
   // getter and setter
}

DetailUser:

public class DetailUserVN {
   private Long detail_user_vn_id;
   private User user_id;
   private String code_level;
   private Date start_date;
   private Date end_start;
   private int total;
   // getter and setter
}

GroupMaster:

public class GroupMaster {  
   private Long group_id;
   private String group_name;
   // getter and setter
}

VNMaster:

class VNMaster {
   private String code_level;   
   private String name_level;
   // getter and setter
}

UserMapper.java

@Select("SELECT users.user_id, "
        + "users.full_name, "
        + "users.birthday, "
        + "mst_group.group_name, "
        + "users.email, users.tel, "
        + "mst_vn.name_level, "
        + "tbl_detail_user_vn.end_date, "
        + "tbl_detail_user_vn.total "           
+ "FROM users "
+ "INNER JOIN tbl_detail_user_vn " 
+   "ON tbl_detail_user_vn.detail_user_vn_id = users.user_id "    
+ "INNER JOIN mst_vn " 
+   "ON mst_vn.code_level = tbl_detail_user_vn.code_level "   
+ "INNER JOIN mst_group "
+   "ON mst_group.group_id = users.group_id")   
public List<User> getAllUsers();

UserServiceImpl:

public List<User> getAllUsers() {
    return userMapper.getAllUsers();        
}

UserController:

public class UserController {

@Autowired
private UserService userService;
@RequestMapping(value="/login", method=RequestMethod.POST)
public String getAllUser(ModelMap model) {
List<User> listUser = userService.getAllUsers();
        model.put("listUser", listUser);
        logger.info("list of renter is: " + listUser);

        return "getUser";
  }
}

getUser.jsp:

<div class="">                              
        <c:if test="${not empty listUser}">
            <table border="1" cellpadding="5">
                <tr>
                    <th style="background-color: #e9ecef;">ID</th>
                    <th style="background-color: #e9ecef;">Họ tên ▲▽</th>
                    <th style="background-color: #e9ecef;">Ngày sinh</th>                               
                    <th style="background-color: #e9ecef;">Phòng ban</th>
                    <th style="background-color: #e9ecef;">Email</th>
                    <th style="background-color: #e9ecef;">Phone number</th>                        
                    <th style="background-color: #e9ecef;">Tring do ▲▽</th>
                    <th style="background-color: #e9ecef;">Ngay het han ▲▽</th>
                    <th style="background-color: #e9ecef;">Tong</th>
                </tr>
                <c:forEach items="${listUser}" var="user">
                <tr>
                    <td>${user.user_id}</td>
                    <td>${user.full_name}</td>
                    <td>${user.birthday}</td>     
                    <td>${user.group_id.group_name}</td>                        
                    <td>${user.email}</td>
                    <td>${user.tel}</td>
                    <td>${mst_vn.name_level}</td>
                    <td>${tbl_detail_user_vn.end_date}</td>
                    <td>${tbl_detail_user_vn.total}</td>
                </tr>
                </c:forEach>
            </table>
        </c:if>                                 
    </div>      

When I build it seem is not happen error but some value can not get from database

example:

<td>${user.group_id.group_name}</td>
<td>${mst_vn.name_level}</td>
<td>${tbl_detail_user_vn.end_date}</td>
<td>${tbl_detail_user_vn.total}</td>

How to fix this error and get data for this?

Updated:

org.apache.ibatis.reflection.ReflectionException: There is no setter for property named 'user' in 'class com.you.springmybatis.model.User'

Answer 1

The SQL query returns the result set with data that is required but you have not specified how to map this result set to the object structure you use.

Let's consider one field value you try to display user.group_id.group_name (for other fields the situation is more complex, see below some thoughts about this).

The data for group name is returned in the field that has name group_name in the result set. But mybatis does not know that for this field and object of type GroupMaster should be created and that the value from group_name should be set to the group_name property of that object.

You need to specify the mapping for the association between User and GroupMaster entities. Unfortunately when you select the entity and associated entity via single query using a join it is not possible to map such association using annotations in mybatis. But this can be done using xml mapping.

But before you start you need to include unique id of every entity that participates in the join to the result set. That means you need to include group_id etc. Without these fields mybatis will create duplicate entities but you most probably want to have only one GroupMaster created for different User entities if they refer to the same record in mst_group table.

You create xml mapping in UserMapper.xml that resides in the same package as UserMapper interface similar to this:

<resultMap id="userMap" type="User">
  <id property="user_id" column="user_id"/>
  <result property="full_name" column="full_name"/>
  <result property="birthday" column="birthday"/>
  <association property="group_id" javaType="GroupMaster">
    <id property="group_id" column="group_id"/>
    <result property="group_name" column="group_name"/>
  </association>
</resultMap>

Then you can reference this resultmap in the mapper:

@ResultMap("com.you.mapper.UserMapper.userMap")
@Select("SELECT users.user_id, "
        + "users.full_name, "
        + "users.birthday, "
        + "mst_group.group_name, "
        + "users.email, users.tel, "
        + "mst_vn.name_level, "
        + "tbl_detail_user_vn.end_date, "
        + "tbl_detail_user_vn.total "           
+ "FROM users "
+ "INNER JOIN tbl_detail_user_vn " 
+   "ON tbl_detail_user_vn.detail_user_vn_id = users.user_id "    
+ "INNER JOIN mst_vn " 
+   "ON mst_vn.code_level = tbl_detail_user_vn.code_level "   
+ "INNER JOIN mst_group "
+   "ON mst_group.group_id = users.group_id")   
public List<User> getAllUsers();

Let's see in details how association mapping works:

<resultMap id="RM" type="A">
  <association property="pB" javaType="B">
    <id property="id" column="b_id"/>
    <result property="name" column="b_name"/>
  </association>
</resultMap>

This specifies that for every result row (that represents object of type A) an object of type B should be created and its properties id and name should be set from columns b_id and b_name respectively.

So the above mapping for you example specifies that fields group_id and group_name should be put into the properties of the object of type GroupMaster and that object should be assigned to the group_id property of the User entity.

Several notes:

1. your data structure is a bit incorrect namely group_id field in User actually stores not the id of the group, but the actual object so it should be renamed to group.
2. usual naming convention for properties is a camel-case identifiers, that is instead of full_name the property should be fullName etc.

There is a problem with mapping of joins to mst_vn and tbl_detail_user_vn as there are no field representing this associations in the User. You need to think about how you want you object tree should look like to represent the data you want to fetch from the database.

For tbl_detail_user_vn you probably need to add a field to User:

private DetailUserVN detail;

Then you will be able to map this association using the same method as for GroupMaster.