Hibernate原生多表关联查询结果如何转换为实体对象？

使用Hibernate原生SQL关联查询address、users、user_roles三张表，仅返回users.username、user_roles.role_name、address.address三个字段。当前代码可遍历Object[]数组打印字段值，但尝试将数组元素强转为Users、UserRoles、Address实体类时报错，需将这类查询结果转换为实体对象。

表结构说明：address表通过iduser关联users表，users表通过iduser关联user_roles表。

代码示例：

public void doTestQuery() {
    try (Session session = HibernateUtilities.getSessionFactory().openSession()) {
        Transaction transaction = session.beginTransaction();
        String sql = "SELECT\r\n"
                + " users.username, \r\n"
                + " user_roles.role_name, \r\n"
                + " address.address\r\n"
                + "FROM\r\n"
                + " address\r\n"
                + " INNER JOIN\r\n"
                + " users\r\n"
                + " ON \r\n"
                + "     address.iduser = users.iduser\r\n"
                + " INNER JOIN\r\n"
                + " user_roles\r\n"
                + " ON \r\n"
                + " users.iduser = user_roles.iduser";
        NativeQuery query = session.createNativeQuery(sql);
        List<Object[]> results = query.list();
        for (Object[] arr : results) {
            // 此段强转代码报错
            Users user = (Users) arr[0];
            UserRoles userRole = (UserRoles) arr[1];
            Address _address = (Address) arr[2];    
            System.out.println(user.getUsername() + userRole.getRolename() + _address.getAddress());
        }
        transaction.commit();
    }
}

方法1：自定义DTO映射（推荐） #

因为仅查询了部分字段，不是完整实体属性，最合理的方式是创建DTO（数据传输对象）封装结果，通过构造函数映射：

1. 创建DTO类 #

public class UserRoleAddressDTO {
    private String username;
    private String roleName;
    private String address;

    // 构造函数参数顺序必须与SQL查询字段顺序完全一致
    public UserRoleAddressDTO(String username, String roleName, String address) {
        this.username = username;
        this.roleName = roleName;
        this.address = address;
    }

    // 按需添加getter方法
    public String getUsername() { return username; }
    public String getRoleName() { return roleName; }
    public String getAddress() { return address; }
}

2. 修改查询代码 #

public void doTestQuery() {
    try (Session session = HibernateUtilities.getSessionFactory().openSession()) {
        Transaction transaction = session.beginTransaction();
        String sql = "SELECT\r\n"
                + " users.username, \r\n"
                + " user_roles.role_name, \r\n"
                + " address.address\r\n"
                + "FROM\r\n"
                + " address\r\n"
                + " INNER JOIN\r\n"
                + " users\r\n"
                + " ON \r\n"
                + "     address.iduser = users.iduser\r\n"
                + " INNER JOIN\r\n"
                + " user_roles\r\n"
                + " ON \r\n"
                + " users.iduser = user_roles.iduser";
        // 指定结果映射到DTO类
        NativeQuery<UserRoleAddressDTO> query = session.createNativeQuery(sql, UserRoleAddressDTO.class);
        List<UserRoleAddressDTO> results = query.list();
        for (UserRoleAddressDTO dto : results) {
            System.out.println(dto.getUsername() + dto.getRoleName() + dto.getAddress());
        }
        transaction.commit();
    }
}

方法2：使用@SqlResultSetMapping映射到实体类 #

如果一定要用现有实体类，需通过注解定义结果集映射，同时实体类要提供对应参数的构造函数：

1. 给实体类添加注解和构造函数 #

@Entity
@Table(name = "users")
@SqlResultSetMapping(
    name = "UserRoleAddressMapping",
    classes = {
        @ConstructorResult(
            targetClass = Users.class,
            columns = @ColumnResult(name = "username")
        ),
        @ConstructorResult(
            targetClass = UserRoles.class,
            columns = @ColumnResult(name = "role_name")
        ),
        @ConstructorResult(
            targetClass = Address.class,
            columns = @ColumnResult(name = "address")
        )
    }
)
public class Users {
    // 原有实体属性和代码
    private String username;

    // 添加对应参数的构造函数
    public Users(String username) {
        this.username = username;
    }

    // 原有getter/setter
    public String getUsername() { return username; }
}

// 同理给UserRoles和Address添加对应构造函数
public class UserRoles {
    private String roleName;

    public UserRoles(String roleName) {
        this.roleName = roleName;
    }

    public String getRolename() { return roleName; }
}

public class Address {
    private String address;

    public Address(String address) {
        this.address = address;
    }

    public String getAddress() { return address; }
}

2. 修改查询代码 #

public void doTestQuery() {
    try (Session session = HibernateUtilities.getSessionFactory().openSession()) {
        Transaction transaction = session.beginTransaction();
        String sql = "SELECT\r\n"
                + " users.username, \r\n"
                + " user_roles.role_name, \r\n"
                + " address.address\r\n"
                + "FROM\r\n"
                + " address\r\n"
                + " INNER JOIN\r\n"
                + " users\r\n"
                + " ON \r\n"
                + "     address.iduser = users.iduser\r\n"
                + " INNER JOIN\r\n"
                + " user_roles\r\n"
                + " ON \r\n"
                + " users.iduser = user_roles.iduser";
        // 使用自定义的结果集映射名称
        NativeQuery query = session.createNativeQuery(sql, "UserRoleAddressMapping");
        List<Object[]> results = query.list();
        for (Object[] arr : results) {
            Users user = (Users) arr[0];
            UserRoles userRole = (UserRoles) arr[1];
            Address _address = (Address) arr[2];    
            System.out.println(user.getUsername() + userRole.getRolename() + _address.getAddress());
        }
        transaction.commit();
    }
}

方法3：手动赋值转换（最直接） #

直接遍历Object[]，将字段值手动赋值给实体对象：

public void doTestQuery() {
    try (Session session = HibernateUtilities.getSessionFactory().openSession()) {
        Transaction transaction = session.beginTransaction();
        String sql = "SELECT\r\n"
                + " users.username, \r\n"
                + " user_roles.role_name, \r\n"
                + " address.address\r\n"
                + "FROM\r\n"
                + " address\r\n"
                + " INNER JOIN\r\n"
                + " users\r\n"
                + " ON \r\n"
                + "     address.iduser = users.iduser\r\n"
                + " INNER JOIN\r\n"
                + " user_roles\r\n"
                + " ON \r\n"
                + " users.iduser = user_roles.iduser";
        NativeQuery query = session.createNativeQuery(sql);
        List<Object[]> results = query.list();
        for (Object[] arr : results) {
            Users user = new Users();
            user.setUsername((String) arr[0]);
            
            UserRoles userRole = new UserRoles();
            userRole.setRolename((String) arr[1]);
            
            Address _address = new Address();
            _address.setAddress((String) arr[2]);
            
            System.out.println(user.getUsername() + userRole.getRolename() + _address.getAddress());
        }
        transaction.commit();
    }
}

内容的提问来源于stack exchange，提问作者poldoj