Hibernate select query returns nothing

Question

I got a little issue with hibernate since hibernate doesn't accept a normal sql query syntax. When i send my query with a select statement which should return an exact integer of 37 to the database i get nothing in return instead. This is the query in sql syntax: "select id from tbl_employee where bsn = '36372837'" this returns 37. But when i execute this query from within hibernate with al the object references and crap it doesn't work.

Please check my code and see if you know how to solve the problem:

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) ;
}}

Thanks in advance, Benjamin

Answer 1

You are using the method getFirstResult().
But this method returns the position (integer) of the record in the table.

To retrieve the record (object), you should use getSingleResult() instead.

See http://docs.oracle.com/javaee/6/api/javax/persistence/Query.html.

Answer 2

I found the solution,

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

getSingleResult() returns an object, so that is why you have to use Integer instead of int.

Answer 3

Am not a JPA person so apologise if didnt make sense, try wrapping your query inside a transaction

    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();