Handson
1. Create a appl with Employee entity class with id, name, age, gender, department and salary and insert some dummy values in to the table
2. Use JPAQL, Get the details of youngest male employee in the product development department?
3. Using JPAQL, What is the average age of male employees?
1. Create maven java project with 2 dependency(hibernate-core, mysql driver)
<dependencies>
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-core</artifactId>
<version>5.4.24.Final</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.19</version>
</dependency>
</dependencies>
2. In mysql db , we create a database
mysql> create database aspirejava;
Query OK, 1 row affected (0.04 sec)
3. Configure db info and hibernate properties in persistence.xml inside resources/META-INF folder
<?xml version="1.0" encoding="UTF-8"?>
<persistence xmlns=http://java.sun.com/xml/ns/persistence version="2.0">
<persistence-unit name="student-info" transaction-type="RESOURCE_LOCAL">
<provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>
<class>com.pack.Student</class>
<properties>
<!-- Database information -->
<property name="javax.persistence.jdbc.url" value="jdbc:mysql://localhost:3306/aspirejava"/>
<property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver"/>
<property name="javax.persistence.jdbc.user" value="root"/>
<property name="javax.persistence.jdbc.password" value="root"/>
<!-- Hibernate properties -->
<property name="hibernate.hbm2ddl.auto" value="update" />
<property name="hibernate.dialect" value="org.hibernate.dialect.MySQL5Dialect" />
<property name="hibernate.show_sql" value="true"/>
<property name="hibernate.format_sql" value="true"/>
<property name="hibernate.use_sql_comments" value="true"/>
</properties>
</persistence-unit>
</persistence>
4. Create entity/persistent class using JPA annotation - Simple POJO class which contains getter and setter method based on the columns of the database table
public enum Gender {
MALE, FEMALE, OTHERS
}
@Entity
@Table(name="stud100")
@DynamicInsert
@DynamicUpdate
public class Student {
@Id
@GeneratedValue(strategy=GenerationType.AUTO)
@Column(name="stuid")
private Integer id;
@Column(name="stuname")
private String name;
private Integer age;
private LocalDate dob;
@Enumerated(EnumType.ORDINAL)
private Gender gender;
@Transient
private boolean status;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public LocalDate getDob() {
return dob;
}
public void setDob(LocalDate dob) {
this.dob = dob;
}
public Gender getGender() {
return gender;
}
public void setGender(Gender gender) {
this.gender = gender;
}
public boolean isStatus() {
return status;
}
public void setStatus(boolean status) {
this.status = status;
}
public Student(Integer id, String name, Integer age, LocalDate dob, Gender gender, boolean status) {
super();
this.id = id;
this.name = name;
this.age = age;
this.dob = dob;
this.gender = gender;
this.status = status;
}
public Student(String name, Integer age, LocalDate dob, Gender gender) {
super();
this.name = name;
this.age = age;
this.dob = dob;
this.gender = gender;
}
public Student() {
super();
// TODO Auto-generated constructor stub
}
public Student(String name, Integer age) {
super();
this.name = name;
this.age = age;
}
}
5. Configure entity class in xml file
<class>com.pack.Student</class>
6. Create main class
1. Create EntityManagerFactory
2. Create EntityManager
3. Create EntityTransaction
@DynamicInsert - if we want to generate sql query only for the values we are inserting
@DynamicUpdate - if we want to generate sql query only for the values we are updating
public class Main {
public static void main(String[] args) {
EntityManagerFactory emf=Persistence.createEntityManagerFactory("student-info");
EntityManager em=emf.createEntityManager();
EntityTransaction et=em.getTransaction();
et.begin();
//Insertion
//Student s1=new Student("Ram",23,LocalDate.parse("2000-10-22"),Gender.MALE);
//em.persist(s1);
//Student s2=new Student("Sam",24,LocalDate.parse("2001-01-02"),Gender.MALE);
//em.persist(s2);
//Student s3=new Student("Tam",25,LocalDate.parse("2008-08-08"),Gender.MALE);
//em.persist(s3);
//Student s4=new Student("Tina",22,LocalDate.parse("2022-08-28"),Gender.FEMALE);
//em.persist(s4);
/*Student s5=new Student();
s5.setName("Saju");
s5.setAge(26);
s5.setDob(LocalDate.parse("2020-11-23"));
s5.setGender(Gender.OTHERS);
em.persist(s5);*/
//Updation
/*Student st=(Student)em.find(Student.class,5);
st.setGender(Gender.MALE);
st.setAge(29);
em.persist(st);*/
//Deletion
/*Student st=(Student)em.find(Student.class,5);
em.remove(st);*/
/*Student st=new Student("Raju",30);
em.persist(st);*/
Student st=(Student)em.find(Student.class,7);
st.setDob(LocalDate.parse("1989-09-11"));
st.setGender(Gender.FEMALE);
em.persist(st);
et.commit();
System.out.println("Success");
em.close();
emf.close();
}
}
JPAQL (JPA Query Language)
- Database independent query, because we are going to query the entity class directly using Query interface
Query q=em.createQuery(String query) //query entity class
Query q=em.createNativeQuery(String query) //query table
Whether it is JPAQL or SQL query we have written query in main class, but now we write the queries in entity class and we can refer those queries in main class by their name which is called as named queries
JPAQL - @NamedQuery - single JPAQL
SQL - @NamedNativeQuery - single sql
JPAQL - @NamedQueries - multiple JPAQL
SQL - @NamedNativeQueries - multiple sql
1. Create Person entity class
@Entity
@NamedQueries({ //identify JPAQL based on their name
@NamedQuery(name="findPerson",query="select p from Person p"),
@NamedQuery(name="findPersonById",query="select p.name from Person p where p.id=?1")
})
@NamedNativeQueries({
@NamedNativeQuery(name="Person.findAllPerson",query="select * from person",resultClass=Person.class)
})
public class Person {
@Id
private Integer id;
private String name;
private String address;
private Integer age;
private String city;
//getter,setter,constructor
}
2. Configure entity class in xml file
<class>com.pack.Person</class>
3. Create main class
public class Main {
public static void main(String[] args) {
EntityManagerFactory emf=Persistence.createEntityManagerFactory("student-info");
EntityManager em=emf.createEntityManager();
EntityTransaction et=em.getTransaction();
et.begin();
/*Person p1=new Person(100,"Ram","ABC street",24,"Chennai");
em.persist(p1);
Person p2=new Person(101,"Sam","XYZ street",22,"Mumbai");
em.persist(p2);
Person p3=new Person(102,"Raj","PQR street",21,"Pune");
em.persist(p3);
Person p4=new Person(103,"Tam","MNQ street",30,"Bangalore");
em.persist(p4);
Person p5=new Person(104,"Jam","EFG street",25,"Hyderabad");
em.persist(p5);
Person p6=new Person(105,"Jim","UVW street",26,"Delhi");
em.persist(p6);*/
//1. Select all 5 properties from entity class
//Query q=em.createQuery("from Person p");
/*Query q=em.createQuery("select p from Person p");
List<Person> l=q.getResultList();
for(Person p:l)
System.out.println(p.getName()+" "+p.getAddress()+" "+p.getCity());*/
//2. Select only particular properties from entity class
/*Query q=em.createQuery("select p.name,p.city,p.age from Person p");
List l=q.getResultList();
Iterator i=l.iterator();
while(i.hasNext()) {
Object[] obj=(Object[])i.next();
System.out.println(obj[0]+" "+obj[1]+" "+obj[2]);
}*/
//3. Select only single property from entity class
/*Query q=em.createQuery("select p.name from Person p");
List<String> l=q.getResultList();
for(String s:l) {
System.out.println(s);
}*/
//4. Select all columns but specific rows
/*Query q=em.createQuery("select p from Person p where p.id=100");
Person p=(Person)q.getSingleResult();
System.out.println(p.getName());*/
//5. Passing parameters to the query - 2 ways
//1. Positional parameter - ?1,?2,?3 etc
/*Query q=em.createQuery("select p from Person p where p.id=?1 and p.city=?2");
q.setParameter(1, 100);
q.setParameter(2, "Chennai");
Person p=(Person)q.getSingleResult();
System.out.println(p.getName());*/
//2. Named parameter - using : followed by any name
/*Query q=em.createQuery("select p from Person p where p.id=:abc and p.city=:xyz");
q.setParameter("abc", 100);
q.setParameter("xyz", "Chennai");
Person p=(Person)q.getSingleResult();
System.out.println(p.getName());*/
//6. Execute DML stmt(insert,update,delete) using executeUpdate()
/*Query q=em.createQuery("update Person p set p.age=:page,p.city=:pcity where p.id=:pid");
q.setParameter("page", 29);
q.setParameter("pcity","Goa");
q.setParameter("pid", 101);
int i=q.executeUpdate();
System.out.println(i+" rows updated");*/
//7. Pagination - Limiting the records
/*Query q=em.createQuery("select p from Person p");
q.setFirstResult(2); //starts from 0th position, it skips 2 records
q.setMaxResults(2); //prints only 2 records
List<Person> l=q.getResultList();
for(Person p:l)
System.out.println(p.getName()+" "+p.getAddress()+" "+p.getCity());*/
//8. Native Queries - SQL query(query the table directly)
/*Query q=em.createNativeQuery("select * from person");
List l=q.getResultList();
Iterator i=l.iterator();
while(i.hasNext()) {
Object[] o=(Object[])i.next();
System.out.println(o[0]+" "+o[1]+" "+o[2]+" "+o[3]+" "+o[4]);
}*/
/*Query q=em.createNativeQuery("select * from person",Person.class);
List<Person> l=q.getResultList();
for(Person p:l)
System.out.println(p.getName()+" "+p.getAddress()+" "+p.getCity());*/
//9. JPAQL Named query
Query q=em.createNamedQuery("findPerson");
List<Person> l=q.getResultList();
for(Person p:l)
System.out.println(p.getName()+" "+p.getAddress()+" "+p.getCity());
q=em.createNamedQuery("findPersonById");
q.setParameter(1, 100);
Person p1=(Person)q.getSingleResult();
System.out.println(p1.getName());
q=em.createNativeQuery("Person.findAllPerson",Person.class);
List<Person> l1=q.getResultList();
for(Person p:l1)
System.out.println(p.getName()+" "+p.getAddress()+" "+p.getCity());
et.commit();
System.out.println("Success");
em.close();
emf.close();
}
}
Composite primary key
- more than one column as primary key where its combination should be unique
- Using @IdClass or @Embeddable
A(PK) B(PK) C
1 1 1
2 1 -
1 2 1
1 3 2
1. Create entity class
@Entity
@Table(name="mgr100")
public class Manager {
@Id
private Integer id;
@Id
private Integer deptId;
private String name;
private String email;
private String phone;
}
2. Create class which contains info abt composite PK
- It should implements Serializable interface
- It should have only default constructor
- override equals() and hashCode()
public class ManagerPKID implements Serializable {
private Integer id;
private Integer deptId;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public Integer getDeptId() {
return deptId;
}
public void setDeptId(Integer deptId) {
this.deptId = deptId;
}
@Override
public int hashCode() {
final int prime = 31;
int result = 1;
result = prime * result + ((deptId == null) ? 0 : deptId.hashCode());
result = prime * result + ((id == null) ? 0 : id.hashCode());
return result;
}
@Override
public boolean equals(Object obj) {
if (this == obj)
return true;
if (obj == null)
return false;
if (getClass() != obj.getClass())
return false;
ManagerPKID other = (ManagerPKID) obj;
if (deptId == null) {
if (other.deptId != null)
return false;
} else if (!deptId.equals(other.deptId))
return false;
if (id == null) {
if (other.id != null)
return false;
} else if (!id.equals(other.id))
return false;
return true;
}
}
3. Configure info abt composite PK class to entity class using @IdClass
@Entity
@Table(name="mgr100")
@IdClass(ManagerPKID.class)
public class Manager {
@Id
private Integer id;
@Id
private Integer deptId;
private String name;
private String email;
private String phone;
}
4. Configure entity class in xml file
<class>com.pack.Manager</class>
5. Create main class
public class Main {
public static void main(String[] args) {
EntityManagerFactory emf=Persistence.createEntityManagerFactory("student-info");
EntityManager em=emf.createEntityManager();
EntityTransaction et=em.getTransaction();
et.begin();
/*Manager m1=new Manager();
m1.setId(10);
m1.setDeptId(100);
m1.setName("John");
m1.setEmail(john@gmail.com);
m1.setPhone("2634646");
em.persist(m1);*/
Manager m1=new Manager();
m1.setId(11);
m1.setDeptId(100);
m1.setName("Johny");
m1.setEmail(johny@gmail.com);
m1.setPhone("2633346");
em.persist(m1);
et.commit();
System.out.println("Success");
em.close();
emf.close();
}
}
mysql> select * from mgr100;
+--------+----+-----------------+-------+---------+
| deptId | id | email | name | phone |
+--------+----+-----------------+-------+---------+
| 100 | 10 | john@gmail.com | John | 2634646 |
| 100 | 11 | johny@gmail.com | Johny | 2633346 |
+--------+----+-----------------+-------+---------+
2 rows in set (0.00 sec)
- Using @Embeddable annotation
@Embeddable
public class ManagerPKID implements Serializable {
private Integer id;
private Integer deptId;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public Integer getDeptId() {
return deptId;
}
public void setDeptId(Integer deptId) {
this.deptId = deptId;
}
@Override
public int hashCode() {
final int prime = 31;
int result = 1;
result = prime * result + ((deptId == null) ? 0 : deptId.hashCode());
result = prime * result + ((id == null) ? 0 : id.hashCode());
return result;
}
@Override
public boolean equals(Object obj) {
if (this == obj)
return true;
if (obj == null)
return false;
if (getClass() != obj.getClass())
return false;
ManagerPKID other = (ManagerPKID) obj;
if (deptId == null) {
if (other.deptId != null)
return false;
} else if (!deptId.equals(other.deptId))
return false;
if (id == null) {
if (other.id != null)
return false;
} else if (!id.equals(other.id))
return false;
return true;
}
}
@Entity
@Table(name="mgr100")
public class Manager {
@EmbeddedId
private ManagerPKID mgr;
private String name;
private String email;
private String phone;
public ManagerPKID getMgr() {
return mgr;
}
public void setMgr(ManagerPKID mgr) {
this.mgr = mgr;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public Manager(ManagerPKID mgr, String name, String email, String phone) {
super();
this.mgr = mgr;
this.name = name;
this.email = email;
this.phone = phone;
}
public Manager() {
super();
// TODO Auto-generated constructor stub
}
}
public class Main {
public static void main(String[] args) {
EntityManagerFactory emf=Persistence.createEntityManagerFactory("student-info");
EntityManager em=emf.createEntityManager();
EntityTransaction et=em.getTransaction();
et.begin();
ManagerPKID mgr=new ManagerPKID();
mgr.setId(12);
mgr.setDeptId(101);
Manager m1=new Manager();
m1.setMgr(mgr);
m1.setName("Jack");
m1.setEmail(jack@gmail.com);
m1.setPhone("2634646");
em.persist(m1);
et.commit();
System.out.println("Success");
em.close();
emf.close();
}
}
No comments:
Post a Comment