Menu

Explain Named SQL Query

Explain Named SQL Query

A Named SQL Query in Hibernate is a predefined SQL query that you define in the Hibernate mapping file or via annotations in the entity class. Named SQL queries provide a convenient way to reuse SQL queries across different parts of your application. By defining these queries once and referring to them by name, you can avoid repeating SQL code and make your codebase more maintainable.

Benefits of Named SQL Queries:

  1. Reusability : Named queries can be reused across multiple places in the application.
  2. Centralization : Centralizes query definitions, making it easier to manage and modify queries.
  3. Performance : Named queries can be precompiled, potentially improving performance.
  4. Readability : Improves code readability by reducing inline SQL and consolidating query definitions.

Named SQL Query

Java Example Using Named SQL Queries

Let’s consider an Employee entity and define a named SQL query to fetch employees by department name.

Entities Definition:

 Employee.java 
java
@Entity
@Table(name = "employee")
@NamedNativeQuery(
    name = "Employee.findByDepartmentName",
    query = "SELECT e.* FROM employee e JOIN department d ON e.department_id = d.id WHERE d.name = :departmentName",
    resultClass = Employee.class
)
public class Employee {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

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

    @ManyToOne
    @JoinColumn(name = "department_id")
    private Department department;

    // Getters and Setters
}


 Department.java 
java
@Entity
@Table(name = "department")
public class Department {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

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

    // Getters and Setters
}

Using Named SQL Query: 
Here is how you can use the named SQL query defined in the Employee entity:

java
Session session = sessionFactory.openSession();
Transaction transaction = session.beginTransaction();

// Using the named query
Query query = session.getNamedQuery("Employee.findByDepartmentName");
query.setParameter("departmentName", "HR");
List<Employee> employees = query.list();

for (Employee employee : employees) {
    System.out.println(employee.getName());
}

transaction.commit();
session.close();

Example

  • The @NamedNativeQuery annotation is used to define a named SQL query Employee.findByDepartmentName.
  • The query string is written in SQL, and the resultClass attribute is set to map the result to the Employee entity.
  • The named query is used by calling session.getNamedQuery(“Employee.findByDepartmentName”) and setting the required parameter.

Homepage

Readmore