Posted on Jul 23 • Originally published at Medium Materialized views are a powerful tool in database management systems that allow users to pre-compute and store the results of a query, improving performance and reducing the need for expensive computations. However, in modern, fast-paced business environments, the data in the database may change frequently, requiring frequent updates to materialized views. Streaming databases enable us to continuously ingest and process large amounts of data from different data sources in real-time. In this article, we will explore how to query materialized views with Java, Spring, and a streaming database such as RisingWave.You will learn the following throughout the article:Before proceeding with this post, I highly recommend also reading my other posts to understand how a streaming database differs from a traditional database, why we need to use materialized views to query microservices, and how to query real-time data in Kafka using SQL.Order delivery performance is a crucial metric for any business that sells products online. It reflects the efficiency and effectiveness of the order fulfillment process and directly impacts customer satisfaction. To analyze order delivery performance, businesses need to track key performance indicators (KPIs) such as delivery time, order accuracy, and delivery cost. In the scope of this article, we will create a sample order-delivery application (You can find the source code on GitHub) built using Java and Spring Boot and learn how to use a streaming database and materialized views to analyze food order delivery performance in real-time.Below architectural diagram illustrating the overall data flow:Let's assume we have a materialized view named restaurant_orders_view that calculates the number of total orders created from different restaurants within the last 15 mins in real-time. Now our task is to expose this materialized view result as an API endpoint in an Order service to /orders/count-total-restaurant-orders. In Java, you can query materialized views using JDBC, which is a Java API for connecting to databases. Additionally, you can leverage the Spring Framework to simplify the process of working with JDBC and build a REST API.As RisingWave is wire-compatible with PostgreSQL, you can use the PostgreSQL JDBC driver to connect to RisingWave and interact with RisingWave from your Java applications. You can also use other clients’ libraries for different programming languages (NodeJS, Python, and Go).The next sections show you a step-by-step guide to querying a materialized view in Java and Spring.This tutorial consists of setting up two projects:To complete this tutorial, you need the following:First, clone the RisingWave repository to your local environment.Then, navigate to the integration_tests/delivery directory and start the demo cluster from the docker compose file.Make sure that all containers are up and running!Once the streaming database is set up, we will run our Spring Boot app. Also, fork/clone the project and open it in your Java editor.Then do mvn clean install from the project root folder. In the maven pom.xml file, we added the postgresql dependency and the spring-boot-starter-jdbc driver dependency to our project. These dependencies will provide the necessary classes and interfaces to work with JDBC in Spring.Now we can run our main DemoRisingWaveApplication.java from the editor. After you run the application, you access the endpoint on port 8080 or open this URL on your browser. http://localhost:8080/orders/count-total-restaurant-orders. You will get the materialized view response from RisingWave.Let’s break down some important files in the project to understand each role.Connect to RisingWaveTo query a materialized view in the Spring Boot app, you first need to establish a connection to the streaming database by configuring a Spring Boot application.yml file. The datasource section sets up a PostgreSQL database connection, with a RisingWave database URL of jdbc:postgresql://localhost:4566/dev (It is pointing to a dev database running on our RisingWave demo cluster), a username of "root", and an empty password.Create a data source and materialized viewNext, in the Startup.java file, we have three SQL statements that are executed when the Spring Boot application starts up to create a source as Kafka in RisingWave, drop existing materialized views, and create new ones by reading this data from the view.json file in the application's resources directory that contains a list of materialized views to be created. For example, below is an SQL statement that creates a table called delivery_orders_source in the RisingWave streaming database. The table has four columns: order_id, restaurant_id, order_state, and order_timestamp.The statement also includes a WITH clause that specifies additional options for the table. In this case, the connector option is set to kafka, which indicates that the table will be connected to a Kafka topic. The topic option is set to delivery_orders, which specifies the name of the Kafka topic.Query a materialized view with JdbcTemplate Spring provides a number of features to help you work with databases, including the JdbcTemplate class, which simplifies the process of executing SQL statements and processing the results. To query a materialized view in Spring, you can use the JdbcTemplate class to execute a SQL query that selects data from the materialized view and then extract the data from the result set.Query a materialized view with JPAOnce the materialized view has been created, it can be queried in Java using a Spring Data JPA repository as well. You can create a new entity class called RestaurantOrdersView that maps to the columns in the materialized view table in RisingWave.With the entity class in place, we can create a Spring Data JPA repository that allows us to query the materialized view using standard JPA query methods or you can a native SQL query. For example, the following code retrieves the total restaurant orders:This method countTotalRestaurantOrders can then be called from our OrderDeliveryService we can add extra business logic and pass it to OrderDeliveryController to make this data available through the REST endpoint.In conclusion, using a streaming database we can ingest streaming data from one or multiple data sources in real-time, you can merge multiple streams and create materialized views. Spring Boot provides built-in support for integrating with popular data sources, such as streaming databases. We have seen how businesses can analyze order delivery performance in real-time with the streaming database, Java, and Spring, enabling them to identify bottlenecks and improve the efficiency and effectiveness of their order fulfillment process. 🙋 Join the Risingwave CommunityVisit my blog: www.iambobur.comTemplates let you quickly answer FAQs or store snippets for re-use. Are you sure you want to hide this comment? It will become hidden in your post, but will still be visible via the comment's permalink. Hide child comments as well Confirm For further actions, you may consider blocking this person and/or reporting abuse Piyush Acharya - May 30 Franck Pachot - Apr 28 Internet Explorer - May 26 Jones - codehooks.io - May 27 Once suspended, bobur will not be able to comment or publish posts until their suspension is removed. Once unsuspended, bobur will be able to comment and publish posts again. Once unpublished, all posts by bobur will become hidden and only accessible to themselves. If bobur is not suspended, they can still re-publish their posts from their dashboard. Note: Once unpublished, this post will become invisible to the public and only accessible to Bobur Umurzokov. They can still re-publish the post if they are not suspended. Thanks for keeping DEV Community safe. Here is what you can do to flag bobur: bobur consistently posts content that violates DEV Community's code of conduct because it is harassing, offensive or spammy. Unflagging bobur will restore default visibility to their posts. DEV Community — A constructive and inclusive social network for software developers. With you every step of your journey. Built on Forem — the open source software that powers DEV and other inclusive communities.Made with love and Ruby on Rails. DEV Community © 2016 - 2023. We're a place where coders share, stay up-to-date and grow their careers.