Hibernate select 查询不返回任何内容

Question

我在使用 hibernate 时遇到了一些问题，因为 hibernate 不接受正常的 sql 查询语法。当我使用 select 语句发送查询时，该语句应该向数据库返回精确的整数 37，但我没有得到任何回报。这是sql语法中的查询： “select id from tbl_employee where bsn = '36372837'”这将返回37。但是当我从hibernate中使用所有对象引用执行此查询时，它不起作用。

请检查我的代码，看看您是否知道如何解决问题：

public void RegisterWorkHours(TimeRegistration object)
    {
        EntityManagerFactory emf = javax.persistence.Persistence.createEntityManagerFactory("timereg");
        EntityManager em = emf.createEntityManager();
        try
        {


            String get_employee_id = "SELECT emp.id FROM Employee as emp WHERE emp.bsn=:bsn";
            Query employee_query = em.createQuery(get_employee_id);
            employee_query.setParameter("bsn", object.getEmployee().getBsn());
            int id = employee_query.getFirstResult();
            System.out.println("query returns employee id: " + id);
            object.getEmployee().setId(id);


            String get_project_id = "SELECT p.projectID FROM Project as p WHERE p.projectname=:projectname";
            Query project_query = em.createQuery(get_project_id); 
            project_query.setParameter("projectname", object.getProject().getProjectname());
            int projectid = project_query.getFirstResult();
            System.out.println("query returns projectid: " + projectid);
            object.getProject().setProjectID(projectid);

            em.getTransaction().begin();



                em.persist(object);
                em.getTransaction().commit();

        }
        catch (Exception ex)
        {
            System.out.println(ex);
        }
    }

Employee class:

@Entity
@Table(name = "tbl_employee")
public class Employee
{
    @Id
    @SequenceGenerator(name="employeeSequence", sequenceName="SEQ_EMPLOYEE", allocationSize =1)
    @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="employeeSequence")
    @Column(name="id")
    private int id;


    @Column(name = "bsn")
    @NaturalId
    private String bsn;

    @Column(name = "first_name")
    private String firstname;

    @Column(name = "last_name")
    private String lastname;

    @Column(name="birth_date")
    private String birthDate;

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

    @Column(name="house_number")
    private String houseNumber;

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

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

    //Constructor
    protected Employee() {}

    public Employee(String bsn, String firstname, String lastname)
    {
        setBsn(bsn);
        setFirstname(firstname);
        setLastname(lastname);

    }
    public Employee(String bsn, String firstname, String lastname, String address, String housenumber)
    {
        setBsn(bsn);
        setFirstname(firstname);
        setLastname(lastname);
        setAddress(address);
        setHouseNumber(housenumber);
    }
    public Employee(String bsn, String firstname, String lastname, String address, String housenumber, String zip, String city)
    {
        setBsn(bsn);
        setFirstname(firstname);
        setLastname(lastname);
        setAddress(address);
        setHouseNumber(housenumber);
        setZip(zip);
        setCity(city);
    }

    //The rest is one big list of getters and setters.
}

TimeRegistration class

@Entity
@Table(name = "tbl_timeregtest")
@Inheritance(strategy = InheritanceType.TABLE_PER_CLASS)
public class TimeRegistration
{
    @ManyToOne(cascade = {CascadeType.PERSIST, CascadeType.MERGE})
    private Project project;

    @ManyToOne(cascade = {CascadeType.PERSIST, CascadeType.MERGE})
    private Employee employee;

    @Id
    @SequenceGenerator(name="timeregSequence", sequenceName="SEQ_TIMEREG", allocationSize =1)
    @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="timeregSequence")
    @Column(name="ID")
    private int ID;

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


    @Column(name="hours")
    private int hours;

    //Constructor
    protected TimeRegistration() {}

    public TimeRegistration(Project project, Employee employee, String date, int hours  )
    {
        setProject(project);
        setEmployee(employee);
        setDate(date);
        setHours(hours);
    }

//the rest is all getter setter stuff
}

main void

public class Main
{

    public static void main(String [ ] args)
    {
        Persistence persistence = new Persistence();
        Project project = new Project("AlphaMouse", "11-2-2013", "12-4-2019");
        Employee employee = new Employee("398723912", "Stoel",  "Stra");    
        TimeRegistration register = new TimeRegistration(project, employee, "21-2-2024", 8);
        persistence.RegisterWorkHours(register) ;
}}

提前致谢， 本杰明

Answer 1

您正在使用方法getFirstResult()。
但此方法返回记录在表中的位置（整数）。

要检索记录（对象），您应该使用getSingleResult()。

请参阅http://docs.oracle.com/javaee/6/ api/javax/persistence/Query.html。

Answer 2

我不是 JPA 人员，所以如果没有意义请道歉，尝试将您的查询包装在事务中

    em.getTransaction().begin();

    String get_employee_id = "SELECT emp.id FROM Employee as emp WHERE emp.bsn=:bsn";
    Query employee_query = em.createQuery(get_employee_id);
    employee_query.setParameter("bsn", object.getEmployee().getBsn());
    long id = employee_query.getFirstResult();
    System.out.println("query returns employee id: " + id);

    em.getTransaction().commit();

Answer 3

我找到了解决方案，

    Integer id = (Integer) em.createQuery("select id from Employee where bsn =:bsn")
.setParameter("bsn", object.getEmployee.getBsn())
.getSingleResult();

getSingleResult() 返回一个对象，所以这就是为什么你必须使用 Integer 而不是 int 。