Spring Data JPA Tutorial: Creating Database Queries With Named Queries

The previous part of this tutorial described how we can create database queries with the @Query annotation.

This tutorial has now covered two of the three methods which we can use to create query methods with Spring Data JPA. This blog post describes the last method. We will learn to create database queries by using named queries.

We will also implement a search function that has two requirements:

  • It must return todo entries whose title or description contains the given search term.
  • The search must be case-insensitive.

Let’s get started.

Additional Reading:

If you are not familiar with Spring Data JPA, you should read the following blog posts before you continue reading this blog post:

Using Named Queries With Spring Data JPA

Before we can implement our search function, we have to understand how we can use named queries with Spring Data JPA. In other words, we have to find the answers to these questions:

  1. How can we create named queries?
  2. How can we create the query methods that invokes our named queries?

Let’s start by finding out the answer to the first question.

Creating Named Queries

We can specify named queries with Spring Data JPA by using a properties file, annotations, or the orm.xml file.

Before can take a closer look at these methods, we have to learn a few rules that we must follow when we are specifying the names of our named queries. These rules are:

  • If we want to use the default naming strategy of Spring Data JPA, we have to specify the name of the named query by using this syntax: [entity class name].[name of the invoked query method].
  • If we want to use some other syntax, we have to configure the name of named query when we create the query method that invokes it. We can do this by using the name attribute of the @Query annotation.

We are now ready to create named queries with Spring Data JPA. Let’s start by adding our named queries into a properties file.

Using a Properties File

We can declare named queries by adding them into the jpa-named-queries.properties file that is found from the META-INF folder of our classpath.

If we want to use a different properties file or move the jpa-named-queries.properties file to a different directory, we can configure its location by using these two options:

We can declare a new named query by following these steps:

  1. Set the name of the named query as the name of the property.
  2. Set the invoked query as the value of the property.

In other words, our properties file must use the following format:

name=query

Example:

We want to create a named query whose name is 'Todo.findByTitleIs'. It returns all todo entries whose title is 'title'.

If we want to use JPQL, we have to add the following line into our properties file:

Todo.findByTitleIs=SELECT t FROM Todo t WHERE t.title = 'title'

If we want to use SQL, we have to add the following line into our properties file:

Todo.findByTitleIs=SELECT * FROM todos t WHERE t.title = 'title'

Let’s move on and find out how we can declare named queries by using annotations.

Using Annotations

We can declare named queries by annotating our entities with the following annotations:

  • If we want to create a JPQL query, we have to annotate our entity with the @NamedQuery annotation.
  • If we want to create a SQL query, we have to annotate our entity with the @NamedNativeQuery annotation.
If we want to create more than one named query, we have to wrap our queries inside the @NamedQueries or the @NamedNativeQueries annotation.

Example:

We want to create a named query whose name is 'Todo.findByTitleIs'. It returns all todo entries whose title is 'title'.

If we want to create a JPQL query, we must follow these steps:

  1. Annotate the entity with the @NamedQuery annotation.
  2. Set the name of the named query (Todo.findByTitleIs) as the value of the @NamedQuery annotation’s name attribute.
  3. Set the JPQL query (SELECT t FROM Todo t WHERE t.title = 'title') as the value of the @NamedQuery annotation’s query attribute.

The relevant part of our entity looks as follows:

import javax.persistence.Entity;
import javax.persistence.NamedQuery;
import javax.persistence.Table;

@Entity
@NamedQuery(name = "Todo.findByTitleIs",
        query = "SELECT t FROM Todo t WHERE t.title = 'title'" 
)
@Table(name = "todos")
final class Todo {
	
}

If we want to create a SQL query, we must follow these steps:

  1. Annotate the entity with the @NamedNativeQuery annotation.
  2. Set the name of the named query (Todo.findByTitleIs) as the value of the @NamedNativeQuery annotation’s name attribute.
  3. Set the SQL query (SELECT * FROM todos t WHERE t.title = 'title') as the value of the @NamedNativeQuery annotation’s name attribute.
  4. Set the returned entity class (Todo.class) as the value of the of the @NamedNativeQuery annotation’s resultClass attribute.

The relevant part of our entity looks as follows:

import javax.persistence.Entity;
import javax.persistence.NamedNativeQuery;
import javax.persistence.Table;

@Entity
@NamedNativeQuery(name = "Todo.findByTitleIs”,
        query="SELECT * FROM todos t WHERE t.title = 'title'",
        resultClass = Todo.class
)
@Table(name = "todos")
final class Todo {
	
}
Additional Reading:

If you need to create complex SQL queries, you might have to map the results of your SQL query by using the @SqlResultSetMapping and the @SqlResultSetMappings annotations. If you want to get more information about this, check out the following webpages:

Let’s find out how we can create named queries by using the orm.xml file.

Using the orm.xml File

We can declare named queries by adding them into the orm.xml file that is found from the META-INF folder of our classpath. We have to use one of these two XML elements:

  • If we want to create a JPQL query, we have to use the named-query element.
  • If we want to create a SQL query, we have to use the named-native-query element.

Example:

We want to create a named query whose name is 'Todo.findByTitleIs'. It returns all todo entries whose title is 'title'.

If we want to create a JPQL query, we must follow these steps:

  1. Add a named-query element into the orm.xml file.
  2. Set the name of the named query (Todo.findByTitleIs) as the value of the named-query element’s name attribute.
  3. Add a query element as the child of the named-query element and set the invoked JPQL query (SELECT t FROM Todo t WHERE t.title = 'title') as the value of the query element.

The relevant part of the orm.xml file looks as follows:

<?xml version="1.0" encoding="UTF-8"?>
<entity-mappings
        xmlns="http://java.sun.com/xml/ns/persistence/orm"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://java.sun.com/xml/ns/persistence/orm http://java.sun.com/xml/ns/persistence/orm_2_0.xsd"
        version="2.0">

    <named-query name="Todo.findByTitleIs">
        <query>SELECT t FROM Todo t WHERE t.title = 'title'</query>
    </named-query>
</entity-mappings>

If we want to create a SQL query, we must follow these steps:

  1. Add a named-native-query element to the orm.xml file.
  2. Set the name of the named query (Todo.findByTitleIs) as the value of the named-native-query element’s name attribute.
  3. Set the type of the returned object (net.petrikainulainen.springdata.jpa.todo.Todo) as the value of the named-native-query element’s result-class attribute.
  4. Add a query element as the child of the named-native-query element and set the invoked SQL query (SELECT * FROM todos t WHERE t.title = 'title') as the value of the query element.

The relevant part of the orm.xml file looks as follows:

<?xml version="1.0" encoding="UTF-8"?>
<entity-mappings
        xmlns="http://java.sun.com/xml/ns/persistence/orm"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://java.sun.com/xml/ns/persistence/orm http://java.sun.com/xml/ns/persistence/orm_2_0.xsd"
        version="2.0">

    <named-native-query name="Todo.findByTitleIs"
                        result-class="net.petrikainulainen.springdata.jpa.todo.Todo">
        <query>SELECT * FROM todos t WHERE t.title = 'title'</query>
    </named-native-query>
</entity-mappings>
If you need to create complex SQL queries, you might have to map the results of your SQL query by using the sql-result-set-mapping element. If you want to get more information about SQL result set mapping, you should read these blog posts:

Let’s move on and find out how we can create the query methods that invoke our named queries.

Creating the Query Methods

We can create the query method that invokes a specific named query by following these steps:

  1. Add a query method into our repository interface and follow these rules:
    • If our named query uses the default naming strategy of Spring Data JPA, we must ensure that the name of the query method identifies the invoked named query.
    • If our named query doesn’t use the default naming strategy, we have to annotate the query method with the @Query annotation and configure the name of invoked named query by using the name attribute of the @Query annotation.
  2. If the invoked named query is a SQL query, we have to annotate the query method with the @Query annotation and set the value of its nativeQuery attribute to true.
  3. Add the correct method parameters to the query method.
  4. Specify the return type of the query method.

Example 1:

We want to create a query method that invokes the named query whose name is: Todo.findByTitleIs. Because this named query returns todo entries whose title is 'title', it doesn't have any parameters.

If the invoked named query is a JPQL query, we have to add the following query method into our repository interface:

import org.springframework.data.repository.Repository;

import java.util.List;

interface TodoRepository extends Repository<Todo, Long> {

	public List<Todo> findByTitleIs();
}

If the invoked named query is a SQL query, we have to add the following query method into our repository interface:

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.Repository;

import java.util.List;

interface TodoRepository extends Repository<Todo, Long> {

	@Query(nativeQuery = true)
	public List<Todo> findByTitleIs();
}

Example 2:

We want to create the query method which invokes the named query whose name is: Todo.findByDesc. This named query has one named parameter called description.

If the invoked query is a JPQL query, we have to add the following query method into our repository interface:

import org.springframework.data.repository.Repository;
import org.springframework.data.repository.query.Param;

import java.util.List;

interface TodoRepository extends Repository<Todo, Long> {

	public List<Todo> findByDesc(@Param("description") String description);
}

If the invoked query is a SQL query, we have to add the following query method into our repository interface:

 
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.Repository;
import org.springframework.data.repository.query.Param;

import java.util.List;

interface TodoRepository extends Repository<Todo, Long> {

	@Query(nativeQuery=true)
	public List<Todo> findByDesc(@Param("description") String description);
}

We are now ready to implement our search function. Let’s find out how we can do it.

Implementing the Search Function

We can implement our search function by following these steps:

  1. Create the JPQL and SQL queries that are case-insensitive and return todo entries whose title or description contains the given search term.
  2. Create the named queries that invoke the created JPQL and SQL queries.
  3. Create the query methods that invoke our named queries.

Let’s get started.

Creating the Database Queries

Our search function must fulfil two requirements:

  • It must return todo entries whose title or description contains the given search term.
  • It must be case-insensitive.

This section describes the JPQL and SQL queries that fulfil these requirements.

First, the JPQL query that fulfils our requirements looks as follows:

SELECT t FROM Todo t WHERE 
	LOWER(t.title) LIKE LOWER(CONCAT('%',:searchTerm, '%')) OR 
	LOWER(t.description) LIKE LOWER(CONCAT('%',:searchTerm, '%'))

Second, the SQL query that fulfils our requirements looks as follows:

SELECT * FROM todos t WHERE
	LOWER(t.title) LIKE LOWER(CONCAT('%',:searchTerm, '%')) OR
	LOWER(t.description) LIKE LOWER(CONCAT('%',:searchTerm, '%'))

Let’s move on and create our named queries.

Creating the Named Queries

This section describes how we can specify the required named queries by using a properties file, annotations, and the orm.xml file. We need to create two named queries:

  • The Todo.findBySearchTermNamed is a named query that uses JPQL.
  • The Todo.findBySearchTermNamedNative is a named query that uses SQL.

Let’s get started.

Using a Properties File

After we have added both named queries (Todo.findBySearchTermNamed and Todo.findBySearchTermNamedNative) into the META-INF/jpa-named-queries.properties file, its content looks as follows:

Todo.findBySearchTermNamed=SELECT t FROM Todo t WHERE LOWER(t.title) LIKE LOWER(CONCAT('%', :searchTerm, '%')) OR LOWER(t.description) LIKE LOWER(CONCAT('%', :searchTerm, '%'))
Todo.findBySearchTermNamedNative=SELECT * FROM todos t WHERE LOWER(t.title) LIKE LOWER(CONCAT('%',:searchTerm, '%')) OR LOWER(t.description) LIKE LOWER(CONCAT('%',:searchTerm, '%'))

Let’s move on and declare these named queries by using annotations.

Using Annotations

After we have created both named queries (Todo.findBySearchTermNamed and Todo.findBySearchTermNamedNative) by using the @NamedQuery and the @NamedNativeQuery annotations, the relevant part of our entity class looks as follows:

import javax.persistence.Entity;
import javax.persistence.NamedNativeQuery;
import javax.persistence.NamedQuery;
import javax.persistence.Table;

@Entity
@NamedNativeQuery(name = "Todo.findBySearchTermNamedNative",
        query="SELECT * FROM todos t WHERE " +
                "LOWER(t.title) LIKE LOWER(CONCAT('%',:searchTerm, '%')) OR " +
                "LOWER(t.description) LIKE LOWER(CONCAT('%',:searchTerm, '%'))",
        resultClass = Todo.class
)
@NamedQuery(name = "Todo.findBySearchTermNamed",
        query = "SELECT t FROM Todo t WHERE " +
                "LOWER(t.title) LIKE LOWER(CONCAT('%', :searchTerm, '%')) OR " +
                "LOWER(t.description) LIKE LOWER(CONCAT('%', :searchTerm, '%'))"
)
@Table(name = "todos")
final class Todo {

}

Let’s find out how we can declare these named queries by using the orm.xml file.

Using the orm.xml File

After we have created both named queries (Todo.findBySearchTermNamed and Todo.findBySearchTermNamedNative) by using the named-query and the named-native-query elements, the META-INF/orm.xml file looks as follows:

<?xml version="1.0" encoding="UTF-8"?>
<entity-mappings 
        xmlns="http://java.sun.com/xml/ns/persistence/orm" 
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
        xsi:schemaLocation="http://java.sun.com/xml/ns/persistence/orm http://java.sun.com/xml/ns/persistence/orm_2_0.xsd" 
        version="2.0">

    <named-query name="Todo.findBySearchTermNamedOrmXml">
        <query>SELECT t FROM Todo t WHERE LOWER(t.title) LIKE LOWER(CONCAT('%', :searchTerm, '%')) OR LOWER(t.description) LIKE LOWER(CONCAT('%', :searchTerm, '%'))</query>
    </named-query>

    <named-native-query name="Todo.findBySearchTermNamedNativeOrmXml"
                        result-class="net.petrikainulainen.springdata.jpa.todo.Todo">
        <query>SELECT * FROM todos t WHERE LOWER(t.title) LIKE LOWER(CONCAT('%',:searchTerm, '%')) OR LOWER(t.description) LIKE LOWER(CONCAT('%',:searchTerm, '%'))</query>
    </named-native-query>
</entity-mappings>

Let’s move on and create the query methods that invoke these named queries.

Creating the Query Methods

We can create the query methods that invoke our named queries by following these steps:

  1. Create the query method that invokes the JPQL query (Todo.findBySearchTermNamed) by following these steps:
    1. Create a query method called findBySearchTermNamed().
    2. Set the return type of the query method to List<Todo>.
    3. Add one method parameter to the query method and configure the name of the named parameter (searchTerm) by annotating the method parameter with the @Param annotation.
  2. Create the query method that invokes the SQL query (Todo.findBySearchTermNamedNative) by following these steps:
    1. Create a query method called findBySearchTermNamedNative().
    2. Annotate the method with the @Query annotation and set value of its nativeQuery attribute to true.
    3. Set the return type of the query method to List<Todo>.
    4. Add one method parameter to the query method and configure the name of the named parameter (searchTerm) by annotating the method parameter with the @Param annotation.

The source code of our repository interface looks as follows:

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.Repository;
import org.springframework.data.repository.query.Param;

import java.util.List;

interface TodoRepository extends Repository<Todo, Long> {

    List<Todo> findBySearchTermNamed(@Param("searchTerm") String searchTerm);

    @Query(nativeQuery = true)
    List<Todo> findBySearchTermNamedNative(@Param("searchTerm") String searchTerm);
}

Let’s move on and find out when we should create our database queries by using named queries.

When Should We Use Named Queries?

Named queries have the following benefits:

  • Named queries support both JPQL and SQL.
  • If we have an existing application that uses named queries, it is easy to refactor it to use use Spring Data JPA (if we want to).
  • Named queries provide support for SQL result set mapping. This means that we can write complex SQL queries and map the query results into objects.

Named queries have the following drawbacks:

  • We cannot see the invoked database query from the repository interface.
  • There is no support for dynamic queries.
  • If we specify named queries by using annotations, they "litter" the source code of our entity classes.

If we think about the pros and cons of named queries, it becomes clear that the query methods which use named queries aren’t as easy to read or write as the query methods that use either the query generation from the method name strategy or the @Query annotation.

However, named queries have two advantages:

  1. We can write complex SQL queries and map the query results into objects.
  2. If we need to refactor an existing application to use Spring Data JPA, we don’t have to move its named queries to our repository interfaces.

Thus, I think that we should create our query methods by using the query generation from the method name strategy or the @Query annotation.

However, if we cannot create the query method by using these two methods, we can use named queries.

Also, if we are refactoring an existing application to use Spring Data JPA, using its existing named queries is a no-brainer because it can save us a lot of work.

Let’s move on and summarize what we learned from this blog post.

Summary

This blog post has taught us four things:

  • We can create named queries by using a properties file, annotations, or the orm.xml file.
  • Named queries support both JPQL and SQL.
  • If we need to create complex SQL queries, we can map the query results into objects by using SQL result set mapping.
  • We should use named queries only if we don’t have a choice OR we are refactoring an existing application to use Spring Data JPA.

The next part of this tutorial describes how we can create dynamic queries by using the JPA Criteria API.

P.S. You can get the example application of this blog post from Github.

If you want to learn how to use Spring Data JPA, you should read my Spring Data JPA tutorial.
30 comments… add one
  • Jan Oct 29, 2015 @ 17:34

    Hi,

    I followed your example for @NamedNativeQuery with a parameter, using analogy of title = 'title' in the query.
    However, in my implementation, that did not work. What did work, was title = :title

    I am on Java 8, spring data jpa 1.8.0-release.
    Any ideas?

    Thanks,
    Jan

    • Petri Oct 29, 2015 @ 20:53

      Hi,

      Do you pass this parameter to your query method as a method parameter? If so, you need to specify the name of the parameter by using syntax: :name_of_the_parameter. You can specify the name of the parameter by using the @Param annotation.

      On the other hand, if you want to specify the parameter value in the query string, you should be able to add it to your SQL query (like I did in some of my examples). Is it possible to see your named query?

      • Jan Nov 2, 2015 @ 16:47

        Hi,
        I did try to answer your question a couple of times but the answer never appeared. Let's try again ...

        • Jan Nov 2, 2015 @ 16:50

          Can't seem to get any code in the post. Send me an email if you want to see my code ...

          Cheers,
          Jan

          • Petri Nov 2, 2015 @ 20:03

            Hi Jan,

            I sent you an email!

  • Anonymous Jun 3, 2016 @ 4:52

    Hi

    can you please add some example for join query to fetch the data from two tables .

  • Doug Smith Apr 15, 2017 @ 0:19

    Great content, Petri! Thank you for taking the time to write such helpful examples. Well done!

    • Petri Apr 15, 2017 @ 9:15

      Thank you for your kind words. I really appreciate them.

  • Prem Prakash Jun 6, 2017 @ 11:46

    Thank you for writing such a great and helpful Spring data Jpa tutorial. I was searching for long long time for this kind of content for Spring data JPA Concept.

    Thank you So much
    Prem Prakash
    Software developer

    • Petri Jun 9, 2017 @ 12:36

      Thank you for your kind words. I really appreciate them. Also, it was nice to hear that this blog post was useful for you.

  • Anonymous Aug 2, 2017 @ 17:02

    Hi.

    In article I not find any clear description how to use native named query with .properties file. May be I miss something (the article quite large) I thought and tried with sources.

    But it not works. Here what happen. I cloned from git and after maven loaded dependencies I got maven error:

    No marketplace entries found to handle maven-antrun-plugin:1.7:run in Eclipse.

    I use:

    Spring Tool Suite
    Version: 3.8.4.RELEASE

    I tried to fix, but not find any solution with STS (or Eclipse). I think Spring project should be compatible with STS. Right? Could you check and provide fix?

    STS also shows errors on code in TodoRepository on findBySearchTermNamedNative method:

    Invalid derived query! No property searchTermNamedNative found for type Todo

    What I am trying to do is use named query defined as Spring property, but not with .properties file. I want to use XML instead, as large SQL requests in one line is hard to read.

    I did this but got error
    org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'advertisementRepository': Invocation of init method failed; nested exception is org.springframework.data.mapping.PropertyReferenceException: No property xmlNativeQuery found for type Advertisement!

    where xmlNativeQuery is property defined in application.xml and I can see its value with @Value annotation

    org.springframework.boot
    spring-boot-starter-parent
    1.5.4.RELEASE

    Any way thanks for your great work, it just need some update I guess.

    • Cesar Martinez Aug 6, 2017 @ 23:18

      Did you get a fix for this? I'm having the same problem:/

    • Petri Aug 9, 2017 @ 18:09

      Hi,

      In article I not find any clear description how to use native named query with .properties file. May be I miss something (the article quite large) I thought and tried with sources.

      Take a look at the section titled: Using a Properties File.

      No marketplace entries found to handle maven-antrun-plugin:1.7:run

      This is a known Eclipse problem. You can try to solve this problem by following these instructions.

      I tried to fix, but not find any solution with STS (or Eclipse). I think Spring project should be compatible with STS. Right? Could you check and provide fix?

      In theory it should, but the thing is that this is not a Spring related problem. The problem is that the Maven support of Eclipse doesn't recognize some Maven plugins and this can cause problems if your Maven build uses these plugins. That's one reason why I stopped using Eclipse over eight years ago, and this means that I am pretty much useless when it comes to solving Eclipse related issues :(

      The PropertyReferenceException states the Advertisement entity doesn't have a property called xmlNativeQuery. Unfortunately it's impossible to say what is wrong because you didn't include the source code of your repository interface and the relevant entities :( Can you show the source code of these files to me?

  • Amauri Oct 6, 2017 @ 19:28

    Hi,

    Hi, if I have two orm.xml files, how could I specify which one the dbconfig class should use?

    I posted more details about my case in https://stackoverflow.com/questions/35116065/xml-entity-mapping-error-in-spring-data-jpa

    could you please help me to solve out this matter?

    Thank you very much!

    • Petri Oct 10, 2017 @ 22:01

      Hi,

      Unfortunately I don't know the answer to your question. That being said, I suspect that you have to change the configuration of the used JPA provider. When I did a quick Google search, I found this StackOverflow question that provides some possible answers to your question. Note that I haven't tested any of them => I have no idea whether they work or not.

  • sergii Apr 5, 2018 @ 19:34

    Hi, very useful resource. I'm using it time to time.

    Please correct me if i'm wrong:

    "@Query(nativeQuery = true)" is not enough to use nativeQuery (native query has not mapped by method name for me), but it was fixed in case of using "@Query(name = "myNativeQueryName", nativeQuery = true)".

    • Petri Apr 5, 2018 @ 21:48

      Hi Sergii,

      If you are using a named native query, you have to indeed configure the name of query. This is described in the next part of this tutorial, but it seems that the Spring Data team has made some changes to Spring Data JPA because I wasn't aware that you can replace the @NamedNativeQuery annotation by simply setting the value of the @Query annotation's name attribute.

      Thank you for pointing this out. I will take this into account if I decide to update this tutorial in the future.

  • Didier Sep 25, 2018 @ 16:56

    Best tutorial on the internet for newbies to use named queries.
    Thanks for sharing this!!

    • Petri Sep 25, 2018 @ 22:50

      Thank you for your kind words. I really appreciate them.

  • Savani Oct 21, 2018 @ 17:37

    I am developing Spring boot + Spring Data JPA and developing some complex queries. I am sure how we can execute the JPQL query from the tool which was possible in Hibernate project?

    • Petri Oct 23, 2018 @ 17:01

      Hi,

      You can generate SQL queries from the method names of repository methods, use the @Query annotation, use the JPA Criteria API, or use Querydsl. I recommend that you read all parts of my Spring Data JPA Tutorial because it helps you to use these techniques.

  • Amin Jul 3, 2019 @ 14:53

    Is it possible to use Insert operation using @namednativeQuery? I did not find any relevant example for Insert operation

  • Ram C Jun 25, 2020 @ 20:00

    Thanks for the informative article. I'm unable to use named query with a parameter while following the properties file option. Here is what I have in my jpa-named-queries.properties:
    myentity.mymethod = select * from entity where entity.createddate >= DATESUB(NOW(), INTERVAL :param HOUR);
    Basically I need all entities that have been created within the last "param" number of hours.
    The repository method is like:
    @Query(name = "myentity.mymethod", nativeQuery =true)
    public List mymethod (@Param ("param") int param ) ;
    When it shows the SQL in the log, it just shows a ? in place of param. So it is unable to replace it with the value being passed to the method.
    Also tried this with a String instead of int to no avail. Any help would be appreciated.

    • Ram C Jun 25, 2020 @ 20:03

      The method signature is public List mymethod .. ignore the typo (missed copy pasting )

    • Petri Jun 26, 2020 @ 18:19

      Hi,

      It hard to say what's wrong because I cannot debug your code. That being said, the ? character shown on the log doesn't (necessarily) mean that your query parameter isn't passed to the named query. It simply means that your SQL query has a bind parameter at that location.

      If you want to write the bind parameter values to the log, you should take a look at this blog post: Hibernate Tips: How to log SQL statements and their parameters.

  • Rohit Feb 11, 2021 @ 4:19

    How can we write create statement in JPA

    @Query(value = "CREATE USER :user DISPLAY_NAME=:displayName FIRST_NAME=:firstName", nativeQuery = true)
    void createAccount(String user, String displayName, String firstName);

  • Juan Pablo Jun 21, 2021 @ 4:55

    If we want to paginate it is required a count sql query, how to add it into the xml file ? thanks!

    • Petri Jun 21, 2021 @ 18:18

      Hi,

      I have to admit that I don't know the answer to your question. I haven't been using Spring Data JPA for several years and when I did use it, it wasn't possible to paginate SQL queries. I guess that this feature was added after I stopped using Spring Data JPA. However, I took a look at the reference documentation and noticed this paragraph:

      A similar approach also works with named native queries, by adding the .count suffix to a copy of your query. You probably need to register a result set mapping for your count query, though.

      So, maybe you can specify the name of the count query by using this rule: [the name of your named query].count and use this query name when you add the count query to the XML file.

Leave a Reply