Hibernate’s addScalar() Method

1. Overview

In this quick tutorial, we’ll discuss the addScalar() method used in Hibernate with the help of an example. We’ll learn how to use the method and the benefits of using it.

2. What Problem Does addScalar() Solve?

Normally, when fetching results in Hibernate using native SQL query, we use the createNativeQuery() method,  followed by the list() method:

session.createNativeQuery("SELECT * FROM Student student")
  .list();

In this case, Hibernate uses ResultSetMetadata to find column details and returns the list of Object arrays.

But, excessive use of ResultSetMetadata may result in poor performance, and this is where the addScalar() method is useful.

By using addScalar() method, we can prevent Hibernate from using ResultSetMetadata.

3. How to Use addScalar()?

Let’s create a new method to fetch a list of students using the addScalar() method:

public List<Object[]> fetchColumnWithScalar() {
    return session.createNativeQuery("SELECT * FROM Student student")
      .addScalar("studentId", StandardBasicTypes.LONG)
      .addScalar("name", StandardBasicTypes.STRING)
      .addScalar("age", StandardBasicTypes.INTEGER)
      .list();
}

Here, we need to specify the column name and its data type as arguments to the addScalar() method.

Now, Hibernate won’t use ResultSetMetadata to get column details as we’re defining it upfront in addScalar(). Therefore, we’ll get better performance as compared to the previous approach.

4. Other Advantages

Let’s see a few more use-cases where we can use the addScalar() method.

4.1. Limit Number of Columns

We can also use the addScalar() method to limit the number of columns returned by our query.

Let’s write another method fetchLimitedColumnWithScalar() to fetch only student name column:

public List<String> fetchLimitedColumnWithScalar() {
    return session.createNativeQuery("SELECT * FROM Student student")
      .addScalar("name", StandardBasicTypes.STRING)
      .list();
}

Here, we’ve used an asterisk in our query to fetch a List of students:

SELECT * FROM Student student

But, it will not fetch all the columns and only returns a single column name in the List because we’ve only specified a single column in the addScalar() method.

Let’s create a JUnit method to verify the column returned by the fetchLimitedColumnWithScalar() method:

List<String> list = scalarExample.fetchLimitedColumnWithScalar();
for (String colValue : list) {
    assertTrue(colValue.startsWith("John"));
}

As we can see, this will return the List of strings instead of the Object arrays. Also, in our sample data, we’ve kept all student names starting with “John” and that’s why we are asserting columns value against it in the unit test above.

This makes our code more explicit in terms of what it’s returning.

4.2. Return Single Scalar Value

We can also use the addScalar() method to return directly a single scalar value instead of lists.

Let’s create a method that returns the average age of all students:

public Double fetchAvgAgeWithScalar() {
    return (Double) session.createNativeQuery("SELECT AVG(age) as avgAge FROM Student student")
      .addScalar("avgAge")
      .uniqueResult();
}

Now, let’s verify the same with a unit-test method:

Double avgAge = scalarExample.fetchAvgAgeWithScalar();
assertEquals(true, (avgAge >= 5 && avgAge <= 24));

As we can see, the fetchAvgAgeScalar() method directly returns the Integer value, and we are asserting it.

In our sample data, we’ve provided random ages of students between 5 to 24 age. Hence, during assertion, we are expecting the average to be between 5 and 24.

Similarly, we can use any other aggregate functions in SQL to directly get count, max, min, sum, or any other single scalar values directly using the addScalar() method.

5. Overloaded addScalar() Method

We also have an overloaded addScalar() method, and it accepts only a column name as its single argument.

Let’s create a new method and use the overloaded addScalar() method, which fetches the “age“ column without specifying its type:

public List<Object[]> fetchWithOverloadedScalar() {
    return session.createNativeQuery("SELECT * FROM Student student")
      .addScalar("name", StandardBasicTypes.STRING)
      .addScalar("age")
      .list();
}

Now, let’s write another JUnit method to verify if our method is returning two or more columns:

List<Object[]> list = scalarExample.fetchColumnWithOverloadedScalar();
for (Object[] colArray : list) {
    assertEquals(2, colArray.length);
}

As we can see, this returns a List of Object arrays, and the size of the array is two, which represents the name and age columns in the list.

6. Conclusion

In this article, we’ve seen the uses of the addScalar() method in Hibernate, how to use it and when to use it, along with an example.