Spring Data中的本地查询连接

Question

我有个类：

@Entity
public class User {
    @Id
    Long id;
    String name;

    @ManyToMany
    List<Mission> missions;
}

@Entity
public class Mission {
    @Id
    Long id;
    String name;

    @ManyToMany
    List<User> users;
}

public interface MissionRepository extends CrudRepository<Mission, Long> {
    @Query(nativeQuery = true, "select * from mission join user on id = user_id where name = ?1")
    public List<Mission> findByname(String name);
}

我想知道在Spring Data JPA中是否可以使用本地查询连接，并且查询结果是否能像上面的示例一样正确映射到实体中。

有人能给我展示一个完整的示例来使用它吗？我必须在我的情况下使用本地查询，但我不确定是否有效。

- midy62

Answer 1

您可以通过使用命名本地查询和结果集映射来完成此操作，这是一个完整的示例。

任务实体

package com.ntg.crm.internal.entites;

import java.math.BigInteger;
import java.util.List;

import javax.persistence.Entity;
import javax.persistence.EntityResult;
import javax.persistence.FetchType;
import javax.persistence.FieldResult;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.JoinTable;
import javax.persistence.ManyToMany;
import javax.persistence.NamedNativeQuery;
import javax.persistence.SqlResultSetMapping;

@SqlResultSetMapping(name = "Mission.findAllMissionsMapping", entities = @EntityResult(entityClass = Mission.class, fields = {
        @FieldResult(name = "name", column = "mname"), @FieldResult(name = "id", column = "mid")
}))
@NamedNativeQuery(name = "Mission.findAllMissions", query = "select m.id as mid,m.name as mname , info.id uid ,info.name uname from Mission m join user_info_missions um on m.id "
        + "= um.missions_id join user_Info info on info.id = um.user_id where info.name =:userName", resultSetMapping = "Mission.findAllMissionsMapping")

@Entity
public class Mission {

    public Mission() {

    }

    public Mission(BigInteger id, String name) {
        super();
        this.id = id;
        this.name = name;
    }

    public Mission(BigInteger id, String name, List<User> users) {
        super();
        this.id = id;
        this.name = name;
        this.users = users;
    }

    @Id
    BigInteger id;
    String name;

    @ManyToMany(fetch = FetchType.EAGER)
    @JoinTable(name = "user_info_missions", joinColumns = @JoinColumn(name = "missions_id"), inverseJoinColumns = @JoinColumn(name = "user_id"))
    List<User> users;

    public BigInteger getId() {
        return id;
    }

    public void setId(BigInteger id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public List<User> getUsers() {
        return users;
    }

    public void setUsers(List<User> users) {
        this.users = users;
    }

}

用户实体

package com.ntg.crm.internal.entites;

import java.util.List;

import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.JoinTable;
import javax.persistence.ManyToMany;
import javax.persistence.Table;

import com.fasterxml.jackson.annotation.JsonIgnore;

@Entity
@Table(name = "userInfo")
public class User {

    @Id
    long id;
    String name;

    @ManyToMany(targetEntity = Mission.class)
    @JoinTable(name = "user_info_missions", joinColumns = @JoinColumn(name = "user_id"), inverseJoinColumns = @JoinColumn(name = "missions_id"))
    @JsonIgnore
    List<Mission> missions;

    public long getId() {
        return id;
    }

    public void setId(long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public List<Mission> getMissions() {
        return missions;
    }

    public void setMissions(List<Mission> missions) {
        this.missions = missions;
    }

}

仓库功能

public List<Mission> findAllMissions(@Param("userName") String userName);

控制器函数

@Autowired
    TestRepository testRepo;

    @RequestMapping(value = "", method = RequestMethod.GET, produces = "application/json", consumes = "application/json")
    @ResponseBody
    List<Mission> getall() {
        List<Mission> missions = testRepo.findAllMissions("Test");
        return missions;
    }

这是结果

[
  {
    "id": 1,
    "name": "Mission 1",
    "users": [
      {
        "id": 1,
        "name": "Test"
      }
    ]
  },
  {
    "id": 2,
    "name": "Mission 2",
    "users": [
      {
        "id": 1,
        "name": "Test"
      }
    ]
  }
]

- Bassem Salama

---

谢谢你的回答，但我想知道查询结果是否正确映射到了Mission实体。换句话说，我能否在此请求之后获得用户列表。 - midy62

很棒的例子！顺便说一下，你可以使用Lombok注解（如@Data）来减少实体中getter/setter和其他样板文件的代码量。 - Joel

Answer 2

@middy62，您可以使用以下查询。我使用了您的示例编写了查询。希望它能帮助到某些人。

---

@Query(nativeQuery = true, value = "select m from Mission join m.user u where name =:name") public List<Mission> findByname(@Param("name") String name);

请注意，只有在正确映射域实体模型时，查询才能正常工作。

- ochiWlad

Answer 3

你可以使用原生的Spring Data JPA并使用join。这将返回对象数组。

public interface MissionRepository extends Repository<Mission, Long> {

@Query(value = "SELECT * from mission m 
                inner join user u 
                on m.id = u.id 
                where m.name=:name", nativeQuery = true)
List<Object[]> findByname(@Param("name") string name);
}

- Muhammad Tariq