How to get ID from db using EntityManager?

Question

Using Entity Manager How to get the ID of the row from the database whose one of the column value is true and then update that row. My db structure is like this:

ID   EMPLOYEES  START_TIME  END_TIME  MONDAY    TUESDAY     WEDNESSDAY      THURSDAY    FRIDAY   SATURDAY  SUNDAY
1       5          1.0        7.0     TRUE      FALSE       FALSE            FALSE      FALSE     FALSE     FALSE

How to get ID of the row which has MONDAY: FALSE and rest of the days are TRUE and after getting ID update that row.

In sql to get ID it would something similar to like this:

SELECT id 
FROM histogram 
WHERE monday = true 
AND tueday = false 
AND wednessday = false 
AND thursday = false 
AND friday = false 
AND saturday = false 
AND sunday = false ;

This is my model class:

import java.io.Serializable;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;
import javax.validation.constraints.NotNull;
import javax.validation.constraints.Size;

import org.springframework.stereotype.Component;


@Entity
@Table(name = "HISTOGRAM")
public class Histogram implements Serializable {

    private long id;
    private int employees;
    private double startTime; 
    private double endTime;

    private boolean monday;
    private boolean tuesday;
    private boolean wednessday;
    private boolean thursday;
    private boolean friday;
    private boolean saturday;
    private boolean sunday;

    public Histogram() {
    }

    public Histogram(int employees, double startTime, double endTime, boolean monday, boolean tuesday,
            boolean wednessday, boolean thursday, boolean friday, boolean saturday, boolean sunday) {
        this.employees = employees;
        this.startTime = startTime;
        this.endTime = endTime;
        this.monday = monday;
        this.tuesday = tuesday;
        this.wednessday = wednessday;
        this.thursday = thursday;
        this.friday = friday;
        this.saturday = saturday;
        this.sunday = sunday;
    }

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    public long getId() {
        return id;
    }


    public void setId(long id) {
        this.id = id;
    }

    //@Size(min = 2, max = 255, message = "Enter between 2 and 255 characters!")
    @Column(name = "employees")
    @NotNull
    public int getEmployees() {
        return employees;
    }

    public void setEmployees(int employees) {
        this.employees = employees;
    }

    @Column(name = "startTime")
    @NotNull
    public double getStartTime() {
        return startTime;
    }

    public void setStartTime(double startTime) {
        this.startTime = startTime;
    }

    @Column(name = "endTime")
    @NotNull
    public double getEndTime() {
        return endTime;
    }

    public void setEndTime(double endTime) {
        this.endTime = endTime;
    }

    @Column(name = "monday")
    @NotNull
    public boolean isMonday() {
        return monday;
    }

    public void setMonday(boolean monday) {
        this.monday = monday;
    }

    @Column(name = "tuesday")
    @NotNull
    public boolean isTuesday() {
        return tuesday;
    }

    public void setTuesday(boolean tuesday) {
        this.tuesday = tuesday;
    }

    @Column(name = "wednessday")
    @NotNull
    public boolean isWednessday() {
        return wednessday;
    }

    public void setWednessday(boolean wednessday) {
        this.wednessday = wednessday;
    }

    @Column(name = "thursday")
    @NotNull
    public boolean isThursday() {
        return thursday;
    }

    public void setThursday(boolean thursday) {
        this.thursday = thursday;
    }

    @Column(name = "friday")
    @NotNull
    public boolean isFriday() {
        return friday;
    }

    public void setFriday(boolean friday) {
        this.friday = friday;
    }

    @Column(name = "saturday")
    @NotNull
    public boolean isSaturday() {
        return saturday;
    }

    public void setSaturday(boolean saturday) {
        this.saturday = saturday;
    }

    @Column(name = "sunday")
    @NotNull
    public boolean isSunday() {
        return sunday;
    }

    public void setSunday(boolean sunday) {
        this.sunday = sunday;
    }

}

This is my DAO:

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.ParameterExpression;
import javax.persistence.criteria.Root;

import org.springframework.stereotype.Repository;

/**
 * @author Junaid KHALID
 *
 */
@Repository
public class HistogramDAO {
 @PersistenceContext
 private EntityManager entityManager;


 public void create(Histogram histogram) {
 entityManager.persist(histogram);
 }


 public void update(Histogram histogram) {
 entityManager.merge(histogram);
 }


 public Histogram getBesoinRequestById(long id) {
 return entityManager.find(Histogram.class, id);
 }


 public void delete(long id) {
 Histogram histogram = getBesoinRequestById(id);
 if (histogram != null) {
 entityManager.remove(histogram);
 }
 }
}

This is my service class:

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;

/**
 * @author Junaid KHALID
 *
 */
@Component
@Transactional
public class HistogramService {

@Autowired
 private HistogramDAO histogramDAO;

 public void create(Histogram histogram) {
     histogramDAO.create(histogram);
 }

 public void update(Histogram histogram) {
     histogramDAO.update(histogram);
     }

 public void delete(long id) {
     histogramDAO.delete(id);
 }
}

score 0 · Answer 1 · answered Sep 11 '17 at 09:43

Since your are familiar wih sql you can use jpql to find the items with the criteria specified.

TypedQuery<Long> query = entityManager.createQuery("SELECT h.id FROM Histogram h WHERE h.monday = true AND h.tuesday = false AND h.wednessday = false AND h.thursday = false AND h.friday = false AND h.saturday = false AND h.sunday = false ");

List<Long> histograms = query.getResultList();

Then use the id retrieved to get the histogram entity and apply changes

Histogram histogram = entityManager.find(Histogram.class,histogramId);
//changes on the entity
...
entityManager.persist(histogram);

The other choice is to use the criteria api.

How to get ID from db using EntityManager?

1 Answers1