Course Module 4. Working with databases - Lecture: NativeQuery

3.1 Introduction

Another useful thing I would like to talk about is NativeQuery . As you already know, using NativeQuery, you can write queries in native SQL. However, what's even more interesting is that you don't have to use class mapping when getting the query result.

I'd rather show you an example:

List<Object[]> persons = session.createNativeQuery("SELECT * FROM Person").list();

In this example, we don't pass a class that matches the query result rows, instead we just use an array of Object objects.

This can be useful if you want to select just a couple of columns in a table. Example:


List<Object[]> persons = session.createNativeQuery("SELECT id, name FROM Person").list();
 
for(Object[] person : persons) {
    Number id = (Number) person[0];
    String name = (String) person[1];
}

This is somewhat similar to the JDBC approach when you get a ResultSet object and read data from its rows.

However, Hibernate offers different ways to make this more reliable. For example, you can specify the type of columns you want to subtract. Example:


Query<Object[]> query = session.createNativeQuery("SELECT id, name FROM Person");
query.addScalar("id", StandardBasicTypes.LONG);
query.addScalar("name", StandardBasicTypes.STRING);
List<Object[]> persons = query.list();
 
for(Object[] person : persons) {
    Long id = (Long) person[0];
    String name = (String) person[1];
}

3.2 Entity mapping

You can also explicitly specify the class that Hibernate should use when parsing the result of NativeQuery . This can be done in different ways.


Query<Person> query = session.createNativeQuery("SELECT * FROM Person")
    .addEntity(Person.class);
    .list();

And of course, the good old format you know:


Query<Person> query = session.createNativeQuery("SELECT * FROM Person", Person.class).list();

The first approach is the native Hibernate approach and the second is the JPA approach. The JPA approach is more convenient and concise, since this standard was invented after Hibernate existed for many years. And Hibernate evolved and was forced to support old approaches in order to maintain compatibility with its old versions.

By the way, thanks to its approach, Hibernate allows you to connect not one class to the query result mapping, but several classes. Example:


List<Phone> results = session.createNativeQuery(
    "SELECT {ph.*}, {pr.*}" +
    "FROM Phone ph" +
    "JOIN Person pr ON ph.person_id = pr.id")
.addEntity("ph", Phone.class)
.addJoin("pr", "ph.person")
.list();
 
for (Phone. phone : results) {
           	assertNotNull( phone.getPerson().getName() );
}

This approach using NativeQuery can be used to speed up the selection of data from the database. If you know that you don't need some columns, you can leave them out in the request.

You can also load all child entities at once, even if Hibernate wants to use Cache or the LazyLoading mechanism . In addition, your child entities may have many columns in the database, and you can select only some of them.

3.3 DTO mapping

Hibernate also allows you to use non-Entity classes for mapping the result. Classes that do not have any annotations and are not mapped to any tables.

Example:

public class PersonSummaryDTO {
    private Number id;
    private String name;

    public Number getId() {
    	return id;
    }

    public void setId(Number id) {
    	this.id = id;
    }

    public String getName() {
    	return name;
    }

    public void setName(String name) {
    	this.name = name;
	}
}

List<PersonSummaryDTO> dtos = session.createNativeQuery(
    "SELECT p.id as \"id\", p.name as \"name\" FROM Person p")
.setResultTransformer(Transformers.aliasToBean(PersonSummaryDTO.class) )
.list();

Since there are no annotations in the PersonSummaryDTO class, the names of the columns in the SQL query must exactly match the names of the fields of the PersonSummaryDTO class.

This can be very useful if you are reading data from an external database to which your application is connected only in read-only mode. That is, you were given access to tables that have 50+ columns, they store data in a denormalized form to speed up the selection.

Or let's say that someone decided to store the class hierarchy in one table, and in five years this table has grown so much that the devil will break his leg. You need to select a couple of columns from this table (Id and username) and give them to the client.

I think you understand, but if you want to dive deeper into this topic, you can read more at the link:

Native SQL Queries