Close

SELECT-FROM-WHERE Query Example

[Updated: Apr 13, 2018, Created: Mar 30, 2018]

Java Persistence Query Language (JPQL) allows us to write string based queries. The syntax of JPQL is very similar to the standard SQL but it's an abstraction on the underlying native queries, and that is why it is portable and work for any supported data store.

In this getting started with JPQL example, we will see how to use SELECT .. FROM .. WHERE .. query. SELECT, FROM and WHERE are JPQL keywords and serve the similar purpose as of the corresponding SQL keywords. The complete list of the keywords can be found here.

Example

The Entity

@Entity
public class Employee {
  @Id
  @GeneratedValue
  private long id;
  private String name;
  private String dept;
    .............
}

Using Query

public class ExampleMain {
  private static EntityManagerFactory entityManagerFactory =
          Persistence.createEntityManagerFactory("example-unit");

  public static void main(String[] args) {
      try {
          persistEmployees();
          findEmployeesByDept("IT");
      } finally {
          entityManagerFactory.close();
      }
  }

  public static void persistEmployees() {
      Employee employee1 = Employee.create("Diana", "IT");
      Employee employee2 = Employee.create("Rose", "Admin");
      Employee employee3 = Employee.create("Denise", "IT");
      EntityManager em = entityManagerFactory.createEntityManager();
      em.getTransaction().begin();
      em.persist(employee1);
      em.persist(employee2);
      em.persist(employee3);
      em.getTransaction().commit();
      em.close();
  }

  private static void findEmployeesByDept(String dept) {
      EntityManager em = entityManagerFactory.createEntityManager();
      Query query = em.createQuery("SELECT e FROM Employee e WHERE e.dept = :deptName");
      query.setParameter("deptName", dept);
      List<Employee> resultList = query.getResultList();
      resultList.forEach(System.out::println);
      em.close();
  }
}
Employee{id=1, name='Diana', dept='IT'}
Employee{id=3, name='Denise', dept='IT'}
In above query, the name after the FROM keyword is not the table name but the entity's name. By default entity name is same as it's class simple name. In above example, if we use @Entity(name = "Emp") then the query statement would be: SELECT e FROM Emp e WHERE ....
The Entity name (Employee) is case sensitive but all key words like SELECT/FROM are case insensitive.
'e' is the identifier to represent the entity and can be referenced everywhere.
The Keyword AS:
The above statement can also be written as SELECT e FROM Emp AS e WHERE ....
The AS keyword is optional.
Query object prepares and controls the query execution.
Query#setParameter() method is used to bind an argument value to a named parameter. There's another way to bind parameters, i.e. via positional parameters.

Using Positional Parameter

public class ExampleMain2 {
  private static EntityManagerFactory entityManagerFactory =
          Persistence.createEntityManagerFactory("example-unit");

  public static void main(String[] args) {
      try {
          persistEmployees();
          findEmployeesByDept("IT");
      } finally {
          entityManagerFactory.close();
      }
  }

  public static void persistEmployees() {
      Employee employee1 = Employee.create("Diana", "IT");
      Employee employee2 = Employee.create("Rose", "Admin");
      Employee employee3 = Employee.create("Denise", "IT");
      EntityManager em = entityManagerFactory.createEntityManager();
      em.getTransaction().begin();
      em.persist(employee1);
      em.persist(employee2);
      em.persist(employee3);
      em.getTransaction().commit();
      em.close();
  }

  private static void findEmployeesByDept(String dept) {
      EntityManager em = entityManagerFactory.createEntityManager();
      Query query = em.createQuery("SELECT e FROM Employee e WHERE e.dept = ?1");
      query.setParameter(1, dept);
      List<Employee> resultList = query.getResultList();
      resultList.forEach(System.out::println);
      em.close();
  }
}
Employee{id=1, name='Diana', dept='IT'}
Employee{id=3, name='Denise', dept='IT'}

Example Project

Dependencies and Technologies Used:

  • h2 1.4.197: H2 Database Engine.
  • hibernate-core 5.2.13.Final: The core O/RM functionality as provided by Hibernate.
    Implements javax.persistence:javax.persistence-api version 2.1
  • JDK 1.8
  • Maven 3.3.9

SELECT-FROM-WHERE query Example Select All Download
  • select-from-where-example
    • src
      • main
        • java
          • com
            • logicbig
              • example
        • resources
          • META-INF

See Also