Native Queries – How to call native SQL queries with JPA

Native Queries – How to call native SQL queries with JPA

 

The Java Persistence Query Language (JPQL) is the most common way to query data from a database with JPA. But it supports only a small subset of the SQL standard and it also provides no support for database specific features.

So what shall you do, if you need to use a database specific query feature or your DBA gives you a highly optimized query that can not be transformed into JPQL? Just ignore it and do all the work in the Java code?

Of course not! JPA has its own query language but it also supports native SQL. You can create these queries in a very similar way as JPQL queries and they can even return managed entities, if you want.

 

Create dynamic native queries

Creating a dynamic native query is quite simple. The EntityManager interface provides a method called createNativeQuery for it. This method returns an implementation of the Query interface which is the same as if you call the createQuery method to create a JPQL query.

The following code snippet shows a simple example in which I used a native query to select the first and last names from the author table. I know, there is no need to do this with a native SQL query. I could use a standard JPQL query for this, but I want to focus on the JPA part and not bother you with some crazy SQL stuff ?
The persistence provider does not parse the SQL statement, so you can use any SQL statement that is supported by your database. In one of my recent projects for example, I used it to query PostgreSQL specific jsonb columns with Hibernate and mapped the query results to POJOs and entities.

Query q = em.createNativeQuery(SELECT a.firstname, a.lastname FROM Author a);
List<Object[]> authors = q.getResultList();
for (Object[] a : authors) {
System.out.println(Author
+ a[0]
+
+ a[1]);
}

As you can see, the created Query can be used in the same way as any JPQL query. I didn’t provide any mapping information for the result and so the EntityManager returns a List of Object[] which need to be handled afterwards. Instead of mapping the result yourself, you can also provide additional mapping information and let the EntityManager do the mapping for you. I get into more details about that in the result handling section at the end of this post.

 

Parameter binding

Similar to JPQL queries, you can and should use parameter bindings for your query parameters instead of putting the values directly into the query String. This provides several advantages:

  • you do not need to worry about SQL injection,
  • the persistence provider maps your query parameters to the correct types and
  • the persistence provider can do internal optimizations to provide a better performance.

JPQL and native SQL queries use the same Query interface which provides a setParameter method for positional and named parameter bindings. But the use of named parameter bindings for native queries is not defined by the JPA specification. Positional parameters are referenced as “?” in your native Query and their numbering starts at 1.

Query q = em.createNativeQuery(SELECT a.firstname, a.lastname FROM Author a WHERE a.id = ?);
q.setParameter(1, 1);
Object[] author = (Object[]) q.getSingleResult();
System.out.println(Author
+ author[0]
+
+ author[1]);

EclipseLink also supports named parameter bindings for native queries but as I already said, this is not defined by the specification and might not be portable to other JPA implementations.
By using named parameter bindings, you define a name for each parameter and provide it to the setParameter method to bind a value to it. The name is case-sensitive and needs to be prefixed with a “:” symbol.

Query q = em.createNativeQuery(SELECT a.firstname, a.lastname FROM Author a WHERE a.id = :id);
q.setParameter(id, 1);
Object[] author = (Object[]) q.getSingleResult();
System.out.println(Author
+ author[0]
+
+ author[1]);

 

Result handling

As you have seen in the previous code snippets, your native Query returns an Object[] or a Listof Object[]. You can change that, if you provide additional mapping information to the EntityManager. By doing this you can tell the EntityManager to map the result into managed entities, scalar values of specific types or POJOs. The simplest way to map the result of a native query into a managed entity is to select all properties of the entity and provide its as a parameter to the createNativeQuery method.

Query q = em.createNativeQuery(SELECT a.id, a.version, a.firstname, a.lastname FROM Author a, Author.class);
List<Author> authors = q.getResultList();
for (Author a : authors) {
System.out.println(Author
+ a.getFirstName()
+
+ a.getLastName());
}

All other mappings, like the following one which maps the query result into a POJO, need to be defined as SQLResultSetMappings.

@SqlResultSetMapping(
name = AuthorValueMapping,
classes = @ConstructorResult(
targetClass = AuthorValue.class,
columns = {
@ColumnResult(name = id, type = Long.class),
@ColumnResult(name = firstname),
@ColumnResult(name = lastname),
@ColumnResult(name = numBooks, type = Long.class)}))

To use this mapping, you need to provide the name of the mapping as a parameter to the createNativeQuery method.

Query q = em.createNativeQuery(SELECT a.id, a.firstname, a.lastname, count(b.id) as numBooks FROM Author a JOIN BookAuthor ba on a.id = ba.authorid JOIN Book b ON b.id = ba.bookid GROUP BY a.id, AuthorValueMapping);
List<AuthorValue> authors = q.getResultList();
for (AuthorValue a : authors) {
System.out.println(Author
+ a.getFirstName()
+
+ a.getLastName()
+ wrote
+ a.getNumBooks()
+ books.);
}

These mappings are quite powerful and you can even combine them to map a query result into multiple entities, POJOs and scalar values. Have a look at the following posts to dive deeper into SQLResultMappings:

  • Result Set Mapping: The Basics
  • Result Set Mapping: Complex Mappings
  • Result Set Mapping: Constructor Result Mappings
  • Result Set Mapping: Hibernate specific features

 

Create named native queries

You will not be surprised, if I tell you that the definition and usage of a named native query is again very similar to a named JPQL query.

In the previous code snippets, I created 3 dynamic native queries to select the names of all authors, to select all authors as entities and to select all authors and map them into AuthorValuePOJOs. I used the same queries in the following code snippet and created named queries for them.

@NamedNativeQueries({
@NamedNativeQuery(name = selectAuthorNames, query = SELECT a.firstname, a.lastname FROM Author a),
@NamedNativeQuery(name = selectAuthorEntities, query = SELECT a.id, a.version, a.firstname, a.lastname FROM Author a, resultClass = Author.class),
@NamedNativeQuery(name = selectAuthorValue, query = SELECT a.id, a.firstname, a.lastname, count(b.id) as numBooks FROM Author a JOIN BookAuthor ba on a.id = ba.authorid JOIN Book b ON b.id = ba.bookid GROUP BY a.id, resultSetMapping = AuthorValueMapping)
})

As you can see, the definition looks very similar to the definition of named JPQL queries and you can even provide the result class or the name of an SQLResultSetMapping. The named native queries are used in exactly the same way as named JPQL queries. You only need to provide the name of the named native query as a parameter to the createNamedQuery method of the EntityManager.

Query q = em.createNamedQuery(selectAuthorValue);
List<AuthorValue> authors = q.getResultList();
for (AuthorValue a : authors) {
System.out.println(Author
+ a.getFirstName()
+
+ a.getLastName()
+ wrote
+ a.getNumBooks()
+ books.);
}

 

Conclusion

JPQL provides an easy way to query data from the database but it supports only a small subset of the SQL standard and it also does not support database specific features. But this is not a real issue. You can use all these features by creating native SQL queries via EntityManager.createNativeQuery(String sql) which are send directly to the database.

Do you have any questions or comments? Do you use native SQL queries often in your projects or do you try to avoid them?
Tell about it in the comments below.

Install GlassFish 4.1 on a Centos 7

install-glassfish-on-a-centos-7-vpsIn this tutorial, we will explain how to install GlassFish Server Open Source Edition 4.1 on a Centos 7.1 VPS. GlassFish is an open-source application server project and it’s Oracle’s concrete implementation of Java EE. This guide should work on other Linux systems as well but was tested and written for a Centos 7.

Login to your System via SSH

ssh root@host

Update the system and install necessary packages

[root]$ yum -y update
[root]$ yum install unzip wget

Create a new system user

Create a new user for the Glassfish server:

[root]$ adduser \
   --comment 'Glassfish User' \
   --home-dir /home/glassfish \
   glassfish

Install Oracle JDK 8

GlassFish 4.1 requires JDK 8 u20 or above. Use the command below to download the Oracle JDK 8 from the command line using wget:

[root]$ wget --no-cookies \
    --no-check-certificate \
    --header "Cookie: oraclelicense=accept-securebackup-cookie" \
    "http://download.oracle.com/otn-pub/java/jdk/8u45-b14/jdk-8u45-linux-x64.rpm" \
    -O jdk-8u45-linux-x64.rpm

Once the rpm package is downloaded, install it with:

[root]$ yum install jdk-8u45-linux-x64.rpm

To check if JAVA has been properly installed on your CentOS box run java -version, and the output should be similar to the following:

[root]$ java -version
java version "1.8.0_45"
Java(TM) SE Runtime Environment (build 1.8.0_45-b14)
Java HotSpot(TM) 64-Bit Server VM (build 25.45-b02, mixed mode)

Download and extract the GlassFish 4.1 archive

Switch to the glassfish user:

[root]$ su - glassfish

As the glassfish user, download and extract the latest GlassFish 4.1 archive

[glassfish]$ wget http://download.java.net/glassfish/4.1/release/glassfish-4.1.zip
[glassfish]$ unzip glassfish-4.1.zip
[glassfish]$ rm -f glassfish-4.1.zip

Switch back to the root user:

[glassfish]$ exit

Create systemd service

To create a new systemd service for GlassFish, open your editor of choice and create a new file:

[root]$ nano /etc/systemd/system/glassfish.service

and add the following code lines:

[Unit]
Description = GlassFish Server v4.1
After = syslog.target network.target

[Service]
User=glassfish
ExecStart = /usr/bin/java -jar /home/glassfish/glassfish4/glassfish/lib/client/appserver-cli.jar start-domain
ExecStop = /usr/bin/java -jar /home/glassfish/glassfish4/glassfish/lib/client/appserver-cli.jar stop-domain
ExecReload = /usr/bin/java -jar /home/glassfish/glassfish4/glassfish/lib/client/appserver-cli.jar restart-domain
Type = forking

[Install]
WantedBy = multi-user.target

Start the glassfish server and set it to start automatically on boot:

systemctl enable glassfish.service
systemctl start glassfish.service

To verify the unit started, run journalctl -f -u glassfish.service and you should see something like below:

[root]$ journalctl -f -u glassfish.service
Apr 26 17:36:13 vps systemd[1]: Starting GlassFish Server v4.1...
Apr 26 17:36:19 vps java[2767]: Waiting for domain1 to start .....
Apr 26 17:36:19 vps java[2767]: Successfully started the domain : domain1
Apr 26 17:36:19 vps java[2767]: domain  Location: /home/glassfish/glassfish4/glassfish/domains/domain1
Apr 26 17:36:19 vps java[2767]: Log File: /home/glassfish/glassfish4/glassfish/domains/domain1/logs/server.log
Apr 26 17:36:19 vps java[2767]: Admin Port: 4848
Apr 26 17:36:19 vps java[2767]: Command start-domain executed successfully.
Apr 26 17:36:19 vps systemd[1]: Started GlassFish Server v4.1.

Firewall

The default GlassFish port numbers are:

  • Administration: 4848
  • HTTP listener: 8080
  • HTTPS listener: 8181

For example to open port 8080 in the firewall, run:

firewall-cmd --zone=pubic --add-port=8080/tcp --permanent  
firewall-cmd --reload  


For slower systems

# vi /etc/systemd/system.conf
DefaultTimeoutStartSec=90s

That’s it. You have successfully installed your GlassFish . For more information about GlassFish , please refer to the Glassfish website.