java - 在 Java 中运行查询的问题

Question

我正在尝试使用 Java 中的查询从 MySQL 数据库中获取数据。相同的查询在 MySQL 中运行良好，但在 Java 中尝试时显示错误。

错误说：

sqlGrammarException：无法提取结果集

Java 代码

此代码编写在 EmployeeRepository 上，用于根据 EmployeeId 搜索 uid，因为员工和用户使用 OneToOne 连接相互连接。

@Repository
public interface EmployeeRepository extends JpaRepository<Employee, Long>{
    @Query(value = "SELECT e.dob, e.name, e.marital FROM employee e\n" +
                    "JOIN empuser eu ON e.empid = eu.empid \n" +
                    "JOIN user u ON u.uid = eu.empid WHERE u.uid = = :uid", nativeQuery = true)
    public Employee findEmployeeByUid(@Param("uid") long uid);
}

员工实体类

此代码是员工实体，它与不同的实体连接以使员工完整。

@Getter
@Setter
@Entity
@Table(name="employee")
public class Employee implements Serializable{
    @Id
    @Column(name="empid")
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    
    long empId;
    String name;
    Date dob;
    char gender;
    String marital;
    String nationality;
    String photoFile;
    
    //joining employee with address
    @ManyToOne(cascade=CascadeType.ALL, fetch=FetchType.LAZY)
    @JoinTable
    (
            name="tempaddress",
            joinColumns=@JoinColumn(name="empid"),
            inverseJoinColumns=@JoinColumn(name="aid")
    )
    Address tempAddress;
    
    //joining employee with address
    @ManyToOne(cascade=CascadeType.ALL, fetch=FetchType.LAZY)
    @JoinTable
    (
            name="permaaddress",
            joinColumns=@JoinColumn(name="empid"),
            inverseJoinColumns=@JoinColumn(name="aid")
    )
    Address permaAddress;
    
    //joining employee with contact
    @OneToMany(cascade=CascadeType.ALL, fetch=FetchType.LAZY)
    @JoinTable
    (
            name="empcontact",
            joinColumns=@JoinColumn(name="empid"),
            inverseJoinColumns=@JoinColumn(name="cid")
    )
    List <Contact> EmpContact;
    
    //joining employee with familydetail
    @OneToOne(cascade=CascadeType.ALL, fetch=FetchType.LAZY)
    @JoinTable
    (
            name="empfamilydetail",
            joinColumns=@JoinColumn(name="empid"),
            inverseJoinColumns=@JoinColumn(name="fid")
    )
    FamilyDetail EmpFamilyDetail;
    
    //joining employee with Health
    @OneToOne(cascade=CascadeType.ALL, fetch=FetchType.LAZY)
    @JoinTable
    (
            name="emphealth",
            joinColumns=@JoinColumn(name="empid"),
            inverseJoinColumns=@JoinColumn(name="hid")
    )
    Health EmpHealth;
    
    //joining employee with OfficeDetail
    @OneToOne(cascade=CascadeType.ALL, fetch=FetchType.LAZY)
    @JoinTable
    (
            name="empofficedetail",
            joinColumns=@JoinColumn(name="empid"),
            inverseJoinColumns=@JoinColumn(name="oid")
    )
    OfficeDetail EmpOfficeDetail;
    
    //joining employee with team
    @ManyToMany(cascade=CascadeType.ALL, fetch=FetchType.LAZY)
    @JoinTable
    (
            name="empteam",
            joinColumns=@JoinColumn(name="empid"),
            inverseJoinColumns=@JoinColumn(name="tid")
    )
    Set <Team> empteam;
    
    //joining employee with IdentityDocuments
    @OneToMany(cascade=CascadeType.ALL, fetch=FetchType.LAZY)
    @JoinTable
    (
            name="empidentitydocuments",
            joinColumns=@JoinColumn(name="empid"),
            inverseJoinColumns=@JoinColumn(name="iid")
    )
    List <IdentityDocuments> EmpIdentityDocuments;
    
    
    //joining employee with education 
    @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
            @JoinTable(
                    name="empdetails",
                    joinColumns = @JoinColumn(name="empid"),
                    inverseJoinColumns = @JoinColumn(name="eid")
                )
    List<Education> empDetails;
    
    //joining employee with role
    @OneToOne(cascade=CascadeType.ALL, fetch=FetchType.LAZY)
            @JoinTable
            (
                    name="emprole",
                    joinColumns=@JoinColumn(name="empid"),
                    inverseJoinColumns=@JoinColumn(name="rid")
            )
    Role EmpRole;
    
    //joining employee with user
    @OneToOne(cascade=CascadeType.ALL, fetch=FetchType.LAZY)
    @JoinTable
    (
            name="empuser",
            joinColumns=@JoinColumn(name="empid"),
            inverseJoinColumns=@JoinColumn(name="uid")
    )
    User EmpUser;
    
    //Joining Employee with Attendance
    @OneToMany(cascade=CascadeType.ALL, fetch=FetchType.LAZY)
    @JoinTable
    (
            name="empattendance",
            joinColumns=@JoinColumn(name="empid"),
            inverseJoinColumns=@JoinColumn(name="attenid")
    )
    List<Attendance> EmpAttendance;

    //Joining Employee with LeaveUsed
    @OneToMany(cascade=CascadeType.ALL, fetch=FetchType.LAZY)
    @JoinTable
    (
            name="empleaveused",
            joinColumns=@JoinColumn(name="lusedid"),
            inverseJoinColumns=@JoinColumn(name="leaveid")
    )
    List<LeaveUsed> EmpLeaveUsed;

用户实体类

@Getter
@Setter
@Table(name="user")
@Entity
public class User implements Serializable{
    @Id
    @Column(name="uid")
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    private long uid;
    private String userName;
    private String password;
    private boolean enabled;
    private Date lastLogin;
    
    //joining user with role
    @OneToOne(cascade=CascadeType.ALL, fetch=FetchType.EAGER)
    @JoinTable
    (
            name="user_role",
            joinColumns=@JoinColumn(name="uid"),
            inverseJoinColumns=@JoinColumn(name="rid")
    )
    Role user_role;

标签： javamysqlspring-data-jpa

Answer 1

通常，您的 JPA 方法签名将是List<Employee>，而不仅仅是Employee. 但是，当您使用本机查询时，返回类型应该是List<Object[]>，所以使用这个版本：

@Repository
public interface EmployeeRepository extends JpaRepository<Employee, Long> {
    @Query(value = "SELECT e.dob, e.name, e.marital FROM employee e\n" +
                   "JOIN empuser eu ON e.empid = eu.empid \n" +
                   "JOIN user u ON u.uid = eu.empid WHERE u.uid = = :uid", nativeQuery = true)
    public List<Object[]> findEmployeeByUid(@Param("uid") long uid);
}

在访问上述列表中的各个组件时Object[]，您需要进行一些手动编组。例如，要访问员工姓名：

List<Object[]> employees = employeeRepository.findEmployeeByUid(123L);
for (Object[] emp : employees) {
    String name = (String)emp[1];
}

本机查询通常需要这样没有吸引力的代码，因此我建议尽可能使用纯 JPA 来表达您的查询。