Hibernate Native SQL

54.8K views

Hibernate Native SQL - Introduction Use parameter binding in native SQL queries Adding scalars Native Joins Adding joins with entity mapping Basic Result set mapping using @SqlResultSetMapping Mapping Non-managed entities via result transformer Mapping Non-managed entities via result Custom Transformer

Previous: Use parameter binding in native SQL queries Next: Native Joins

Adding scalars

To avoid the overhead of using ResultSetMetadata, or simply to be more explicit in what is returned, we use scalars while creating Native Queries

A quick sample. Check out the JAVA class and SQL file

// {
package com.tu.nativesqlsample;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.hibernate.Session;
import org.hibernate.type.IntegerType;
import org.hibernate.type.StringType;
import com.tu.hibernate.HibernateUtil;
public class NativeQueryScalars {
    static Logger logger = Logger.getLogger(NativeQueryScalars.class.getName());
    static String dash = "--------------------------------------------------------------";
    public static void main(String[] args) {
        Session session = null;
        try {
            session = HibernateUtil.getSessionFactory().openSession();
            addScalar(session);
        } catch (Exception e) {
            logger.warning(e.toString());
        } finally {
            if (session != null) {
                session.close();
            }
        }
        HibernateUtil.shutdown();
    }
    @SuppressWarnings("unchecked")
    public static void addScalar(Session session) {
        logger.info(dash);
        // } 
        List<Object[]> domain = session.createNativeQuery("SELECT * FROM domain")
                .addScalar("domain_id", IntegerType.INSTANCE).addScalar("domainName", StringType.INSTANCE).list();
        domain.stream().forEach(objects -> {
            Integer id = (Integer) objects[0];
            String name = (String) objects[1];
            System.out.println(String.format("Info: Domain[ %d, %s ]", id, name));
        });
    }

In this case, only domain_id and domainName would be returned back, though, we have requested for * from the table.
Also, we would still be returned with an array of Object – List<Object[]>, but now it will not use the ResultSetMetadata to determine the type of columns, but will instead explicitly get the domain_id, and domainName column as respectively a Integer, and a String from the underlying resultset.
Adding scalars to native Queries is preferable, considering the reduced overhead on the ResultSetmetaData and a marginal improvement on the performance.

Create your playground on Tech.io

This playground was created on Tech.io, our hands-on, knowledge-sharing platform for developers.

Go to tech.io

ABOUT US CAREERS PRIVACY FAQ

The #1 tech hiring platform

SCREENING INTERVIEWING

ABOUT US CAREERS PRIVACY FAQ