Query Example

Hibernate Query Example

In this simple query I’ll bring active clubs and memberships. In the query we use object names, not the names seen in the persistence unit

SQL Query

 SELECT c.name as clubname, c.legacyId as clublegacyId, c.clubCity as clubcity, c.isclubactive as clubactive,
 m.id as membershipId , m.type as membershiptype, m.legacy_id as membershiplegacyid, m.ismembershipactive as membershipactive, m.fee as membershipfee
 FROM ols.club_table c inner join membership_table m on c.id = m.club_fk where c.isclubactive = true and m.ismembershipactive=true ;

Native SQL

Query sqlQuery = getSession().createSQLQuery(
				"select {c.*}, {m.*} from club_table c, membership_table m where c.id=m.club_fk")
				.addEntity("c", ClubTable.class)
				.addJoin("m", "c.memberships")
				.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);

Criteria way

Criteria c = getSession()
                //the defined value club is an allias to use it anywhere in the query
                .createCriteria(ClubTable.class, "club");
                //by using the alias club, we can reach out to the club table object
                //we are also setting the clubActive value, mind that it is the value in the entity object not in db table
		c.add(Restrictions.eq("club.clubActive",Boolean.TRUE));
                //here we create another alias to reach out to the defined collection relationship in the club entity
                //by using mem alias we will get to use membership table entity object variables
		c.createAlias("club.memberships", "mem");
                //we are setting a variable defined in the membership table entity
		c.add(Restrictions.eq("mem.membershipActive",Boolean.TRUE));
		//Defining join relation
		criteria.setFetchMode("club.memberships", org.hibernate.FetchMode.JOIN);
		//lastly this entry will apply distinct results
		criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);

Related Tables

ClubTable Entity

import java.util.Date;
import java.util.List;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.JoinTable;
import javax.persistence.ManyToOne;
import javax.persistence.OneToMany;
import javax.persistence.Table;

import org.hibernate.annotations.Cache;
import org.hibernate.annotations.CacheConcurrencyStrategy;

import com.google.common.collect.Lists;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.NoArgsConstructor;
import lombok.ToString;

@Data
@AllArgsConstructor
@NoArgsConstructor
@Entity
@Table(name = "club_table")
@EqualsAndHashCode(of = { "id" })
@ToString(of = { "id" })
@Cache(usage = CacheConcurrencyStrategy.READ_WRITE)
public class ClubTable {
	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	@Column(name = "id")
	private Long id;

	@Column(name = "name", nullable = false)
	private String name;

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

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

	@Column(name = "openingDate")
	private Date openingDate;

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

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

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

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

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

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

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

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

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

	@Column(name = "legacyId")
	private Long legacyId;

	@OneToMany(cascade = CascadeType.ALL, mappedBy = "clubTable", fetch = FetchType.LAZY)
	@Cache(usage = CacheConcurrencyStrategy.READ_WRITE)
	private List<MembershipTable> memberships = Lists.newArrayList();

	@Column(name = "clubcity")
	private String clubCity;

	@OneToMany(cascade = CascadeType.ALL, mappedBy = "club", fetch = FetchType.LAZY)
	private List<MobileFacilityTable> facility;

	@Column(name = "isclubactive")
	private boolean isClubActive;

}

MembershipTableEntity

import com.google.common.collect.Lists;
import com.tr.adesso.macfitOls.enums.MembershipType;
import lombok.*;
import org.hibernate.annotations.*;
import org.hibernate.annotations.Cache;
import org.springframework.transaction.annotation.Transactional;

import javax.persistence.*;
import javax.persistence.CascadeType;
import javax.persistence.Entity;
import javax.persistence.Table;
import java.math.BigDecimal;
import java.util.Date;
import java.util.List;


@Data
@AllArgsConstructor
@NoArgsConstructor
@Entity
@Table(name="membership_table")
@EqualsAndHashCode(of={"id"})
@ToString(of={"id"})
@Cache(usage = CacheConcurrencyStrategy.READ_WRITE)
public class MembershipTable{
    @Id
    @GeneratedValue(strategy= GenerationType.IDENTITY)
    @Column(name="id" )
    private Long id;

    @OneToOne(cascade=CascadeType.ALL, fetch = FetchType.LAZY)
    @JoinColumn(name = "description_tr_fk")
    @Cache(usage = CacheConcurrencyStrategy.READ_WRITE)
    private TranslationReferenceTable description;

    @Transient
    private String localizedDescription;

    @OneToOne(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    @JoinColumn(name = "textOne_tr_fk")
    @Cache(usage = CacheConcurrencyStrategy.READ_WRITE)
    private TranslationReferenceTable textOne;

    @Transient
    private String localizedTextOne;

    @OneToOne(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    @JoinColumn(name = "textTwo_tr_fk")
    @Cache(usage = CacheConcurrencyStrategy.READ_WRITE)
    private TranslationReferenceTable textTwo;

    @Transient
    private String localizedTextTwo;

    @Column(name="entrance_fee")
    private BigDecimal entranceFee;

    @Column(name="fee")
    private BigDecimal fee;

    @Column(name="opening_date")
    private Date openingDate;

    @Column(name="legacy_id")
    private Long legacyId;

    @Column(name = "type")
    @Enumerated(EnumType.STRING)
    @Cache(usage = CacheConcurrencyStrategy.READ_WRITE)
    private MembershipType type = MembershipType.MONTHLY;

    @Transient
    private String localizedType;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "club_fk")
    @Cache(usage = CacheConcurrencyStrategy.READ_WRITE)
    private ClubTable clubTable;
    
    @Column(name="ismembershipactive")
    private boolean isMembershipActive;

}

 

Leave a Reply

Your email address will not be published. Required fields are marked *