为什么LoadGraphs不起作用？

Question

我有一个用户和角色的存储库。两个实体在DB中有不同的表。我尝试加载角色像Hibernate.initialize（User.get角色（））或相同的想法从用户u加入获取u.role其中u.username：=用户名和u.password：=密码，但使用Spring数据CrudRepository与LoadGraph。但是当我调用findDIinctFirstByUsernameAndPassword时，我得到了异常。请帮我解决这个问题。谢谢你。

存储库：

public interface UserRepository extends CrudRepository<User, Integer> {
    @EntityGraph(value = "User.detail", type = EntityGraphType.LOAD)
    User findDistinctFirstByUsernameAndPassword(String username, String password);
}

例外：

WARN： HHH000104： firstResult/max使用集合读取指定的结果；在内存中应用！Hibernate：选择不同的user0_. id作为id1_1_0_，role1_user_role_iduser_rol1_0_1_，user0_启用enabled2_1_0_，user0_密码password3_1_0_，user0_用户名username4_1_0_，role1_角色role2_0_1_，role1_用户名username3_0_1_，role1_用户名username3_0_0__，role1_user_role_id用户user_rol1_0_0__user0_左向外连接user_rolesrole1_在user0_. id=role1_. usernameuser0_. username=？user0_密码16, 2017 10:21:41PMorg.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions警告：SQL错误： 0， SQLState： 4288316, 2017 10:21:41PMorg.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions错误：错误：运算符不存在：整数=字符变化没有运算符匹配给定的名称和参数类型。可能需要添加显式类型转换。职位： 383

org.springframework.dao.InvalidDataAccessResourceUsageExc0019：无法提取ResultSet；SQL[n/a]；嵌套异常是org.hibernate.exception.SQLGrammarExc0019：无法提取ResultSet

这是实体：

@Entity(name = "users")
@NamedEntityGraph(name = "User.detail", attributeNodes = @NamedAttributeNode("role"))
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;

    @Column(name = "username")
    private String username;

    @Column(name = "password")
    private String password;

    @Column(name = "enabled")
    private boolean enabled;

    @OneToMany()
    @JoinColumn(name = "username")
    private List<Role> role;
    ...set, get, empty constructor...
}

@Entity(name = "user_roles")
public class Role {
    @Id
    @Column(name = "user_role_id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;

    @Column(name = "username")
    private String username;

    @Column(name = "role")
    private String role;
    ...set, get, empty constructor...

}

数据库架构：

CREATE TABLE users (
  id       SERIAL      NOT NULL,
  username VARCHAR(20) NOT NULL,
  password VARCHAR(20) NOT NULL,
  enabled  BOOLEAN     NOT NULL DEFAULT FALSE,
  PRIMARY KEY (username)
);

CREATE TABLE user_roles (
  user_role_id SERIAL PRIMARY KEY,
  username     VARCHAR(20) NOT NULL,
  role         VARCHAR(20) NOT NULL,
  UNIQUE (username, role),
  FOREIGN KEY (username) REFERENCES users (username)
);

运行方法：

private final ClassPathXmlApplicationContext context =
        new ClassPathXmlApplicationContext("spring-data-context.xml");

private UserRepository repository = context.getBean(UserRepository.class);

@Test
public void whenUserExistInDBThenGetUserWithLoadedRoles() {

    final User result = repository
            .findDistinctFirstByUsernameAndPassword("peter", "peter");

    assertNotNull(result);

    result.getRole().forEach(System.out::println);
}

Answer 1

我认为您的实体定义不正确，从执行的SQL中可以看出：

... on user0_.id=role1_.username

看看Wikibooks中的JPA示例。你的情况应该是这样的

public class User {
    ...
    @OneToMany
    private List<Role> role;
    ...
}

public class Role {
    ...
    @ManyToOne
    @JoinColumn(name = "username", referencedColumnName = "username")
    private User user;
    ...
}

（用户名列应从Role中删除）