在JAVA中运行查询的问题

Question

  sqlGrammarException: could not extract ResultSet

@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;

感谢任何帮助。多谢了。

Answer 1

通常，JPA方法签名是list ，而不仅仅是employee。但是，由于您使用的是本机查询，返回类型应为list ，因此请使用此版本：

@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来表达您的查询。