DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

How does AI transform chaos engineering from an experiment into a critical capability? Learn how to effectively operationalize the chaos.

Data quality isn't just a technical issue: It impacts an organization's compliance, operational efficiency, and customer satisfaction.

Are you a front-end or full-stack developer frustrated by front-end distractions? Learn to move forward with tooling and clear boundaries.

Developer Experience: Demand to support engineering teams has risen, and there is a shift from traditional DevOps to workflow improvements.

Related

  • JQueue: A Library to Implement the Outbox Pattern
  • A Robust Distributed Payment Network With Enchanted Audit Functionality - Part 2: Spring Boot, Axon, and Implementation
  • Manage Hierarchical Data in MongoDB With Spring
  • Spring Data: Data Auditing Using JaVers and MongoDB

Trending

  • Enterprise-Grade Distributed JMeter Load Testing on Kubernetes: A Scalable, CI/CD-Driven DevOps Approach
  • How You Can Use Few-Shot Learning In LLM Prompting To Improve Its Performance
  • When Caching Goes Wrong: How One Misconfigured Cache Took Down an Entire System
  • Building Generative AI Services: An Introductory and Practical Guide
  1. DZone
  2. Data Engineering
  3. Databases
  4. Running Native Queries With the Spring Native Query Library

Running Native Queries With the Spring Native Query Library

What is the Spring Native Query Library? And how do you run native queries with it?

By 
gaspar barancelli user avatar
gaspar barancelli
·
Jun. 04, 19 · Tutorial
Likes (14)
Comment
Save
Tweet
Share
49.8K Views

Join the DZone community and get the full member experience.

Join For Free

Running native queries to a relational database using Java often leads to confusing the source code, particularly when one has too many filter conditions and/or changes in table bindings.

Because of this, I decided to create the "Spring Native Query" library to facilitate the execution of native queries, with a focus on simplifying the source code, making it more readable and clean, creating files that contain the native queries, and dynamically injecting assets to execute those queries.

The library's idea is to run convention queries, similar to Spring Data, and was built to work only with Spring Boot and Spring Data JPA.

When creating a new interface that extends the NativeQuery interface, we create fake objects from these interfaces, where we use proxy to intercept method calls and execute queries. In the end, we register the beans of those interfaces dynamically, so we can inject the interfaces into all the components of the Spring.

The convention works as follows. The method name is the name of the file that contains the SQL query; the parameters of the methods will then be passed as parameters to the entity manager. The method return is the object that will be transformed with the result returned from the query.

The file that contains the SQL query is a Jtwig template where we can apply validations modifying the whole query, adding filters, changing links between tables, and finally any changes in SQL.

By default, native query files must be added to a folder named nativeQuery inside the resource folder. Remember, the file name must be the same as the method name.

Here are some examples for a better understanding. Let's create a Spring Boot project with dependence, Spring Data JPA and the H2 database. When starting the project, let's create a SQL script by creating a new table and inserting some records. All sample source code is in GitHub.

In your project, add the dependency of the library. Let's take an example using Maven.

<dependency>
    <groupId>io.github.gasparbarancelli</groupId>
    <artifactId>spring-native-query</artifactId>
    <version>1.0.1</version>
</dependency>


Inside the resource folder, create a file named data.sql and insert the script:

CREATE TABLE USER (
  cod INT NOT NULL,
  full_name VARCHAR(45) NULL,
  active INT NULL,
  PRIMARY KEY (cod)
);

INSERT INTO USER (cod, full_name, active)
VALUES (1, 'Gaspar', 1),
       (2, 'Elton', 1),
       (3, 'Lucini', 1),
       (4, 'Diogo', 1),
       (5, 'Daniel', 1),
       (6, 'Marcos', 1),
       (7, 'Fernanda', 1),
       (8, 'Maicon', 1),
       (9, 'Rafael', 0);


In your application/bootstrap properties/YAML configuration file, you must configure which package will contain the NativeQuery interfaces.

native-query.package-scan=io.github.gasparbarancelli.demospringnativequery


Let's create a new class that will be used in the returns of our queries.

import lombok.*;

@Data
public class UserTO {

  private Number id;
  private String name;

}


Let's create a new class that will be used as a filter for one of the queries.

import io.github.gasparbarancelli.NativeQueryOperator;
import io.github.gasparbarancelli.NativeQueryParam;
import lombok.*;

@Data
public class UserFilter {
  private Number id;

  /*
    Custom operator, when add parameter value in query and jwitg, the paramter is transformed
  */
  @NativeQueryParam(value = "name", operator = NativeQueryOperator.CONTAINING)
  private String name;

}


Now, let's create an interface that will extend NativeQuery.

import io.github.gasparbarancelli.NativeQuery;
import io.github.gasparbarancelli.NativeQueryParam;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;

import java.util.List;

public interface UserNativeQuery extends NativeQuery {

  List<UserTO> findUsers();

  /*
    Add fields children of parameter
  */
  List<UserTO> findUsersByFilter(@NativeQueryParam(value = "filter", addChildren = true) UserFilter filter);

  /*
    Add pagination
  */
  List<UserTO> findActiveUsers(Pageable pageable);

  /*
    Add pagination and return object with values for the pagination (count, page, size)
  */
  Page<UserTO> findActiveUsersWithPage(Pageable pageable);

  /*
    Custom parameter name
  */
  UserTO findUserById(@NativeQueryParam(value = "codigo") Number id);

  List<Number> getUsersId();

  String getUserName(Number id);

}


For each method of the interface created above, we have to create a file containing the queries.

/* findUsers.twig file example */
SELECT cod as "id", full_name as "name" FROM USER


/* findUsersByFilter.twig file example, only add parameter when variables is not null*/
SELECT cod as "id", full_name as "name" FROM USER
WHERE 1=1
/* if (filterId != null) */
AND cod = :filterId
/* endif  */
/* if (filterName != null) */
AND full_name like :filterName
/* endif  */


/* findActiveUsers.twig file example */
SELECT cod as "id", full_name as "name" FROM USER WHERE ACTIVE = true


/* findActiveUsersWithPage.twig file example */
SELECT cod as "id", full_name as "name" FROM USER WHERE ACTIVE = true


/* findUserById.twig file example */
SELECT cod as "id", full_name as "name" FROM USER WHERE cod = :codigo


/* getUsersId.twig file example */
SELECT cod as "id" FROM USER


/* getUserName.twig file example */
SELECT full_name as "name" FROM USER WHERE cod = :id


Finally, we will create a RestController to return the data of the queries.

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.web.bind.annotation.*;

import java.util.List;

@RestController
@RequestMapping("user")
public class UserController {

  @Autowired private UserNativeQuery userNativeQuery;

  @GetMapping()
  public List<UserTO> findUsers() {
    return userNativeQuery.findUsers();
  }

  @PostMapping("filter")
  public List<UserTO> findUsers(@RequestBody UserFilter filter) {
    return userNativeQuery.findUsersByFilter(filter);
  }

  @GetMapping("active")
  public List<UserTO> findUsers(
          @RequestParam(value = "page", defaultValue = "0", required = false) int page,
          @RequestParam(value = "size", defaultValue = "10", required = false) int size) {
    return userNativeQuery.findActiveUsers(PageRequest.of(page, size));
  }

  @GetMapping("activeWithPage")
  public Page<UserTO> findActiveUsersWithPage(
          @RequestParam(value = "page", defaultValue = "0", required = false) int page,
          @RequestParam(value = "size", defaultValue = "5", required = false) int size) {
    return userNativeQuery.findActiveUsersWithPage(PageRequest.of(page, size));
  }

  @GetMapping("{id}")
  public UserTO findUsers(@PathVariable("id") Number id) {
    return userNativeQuery.findUserById(id);
  }

  @GetMapping("ids")
  public List<Number> getIds() {
    return userNativeQuery.getUsersId();
  }

  @GetMapping("{id}/name")
  public String getUserName(@PathVariable("id") Number id) {
    return userNativeQuery.getUserName(id);
  }

}


Access the features exposed to run the queries and check the returns.

If you have multiple interfaces and want to split the files of the queries into folders, just add the following annotation on top of the interface informing which files folder.

import io.github.gasparbarancelli.NativeQueryFolder;

@NativeQueryFolder("user")


Now, inside the resource/ nativeQueryfolder, you should create a folder called user and add the query files.

For the future of the library, we have some improvements, such as allowing the user to change the folder where the queries are found, as well as the extension of the files, for example .sql.

So, stay tuned!

Database Relational database Spring Framework Library

Opinions expressed by DZone contributors are their own.

Related

  • JQueue: A Library to Implement the Outbox Pattern
  • A Robust Distributed Payment Network With Enchanted Audit Functionality - Part 2: Spring Boot, Axon, and Implementation
  • Manage Hierarchical Data in MongoDB With Spring
  • Spring Data: Data Auditing Using JaVers and MongoDB

Partner Resources

×

Comments

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • [email protected]

Let's be friends: