Bind jqxChart to MySQL with Spring MVC, Spring Data JPA and Thymeleaf

In this tutorial jqxChart compares the stock of each product category: units in stock and units on order, stacked in one column per category. The application uses the Spring MVC layers of a current Spring Boot 4 project: a JPA entity and a Spring Data repository with an aggregate query, a REST controller that returns JSON, and a Thymeleaf view for the page.

This replaces the older Spring MVC setup with web.xml, a dispatcher servlet XML file, JSP views and a controller that opened JDBC connections itself.

1. Prepare the database #

Create the northwind database and the application user as in Set Up MySQL and a Spring Boot Project. Then add the categories and products tables, shaped like the Northwind ones:

USE northwind;

CREATE TABLE categories (
    CategoryID   INT PRIMARY KEY,
    CategoryName VARCHAR(15) NOT NULL
);

CREATE TABLE products (
    ProductID    INT AUTO_INCREMENT PRIMARY KEY,
    ProductName  VARCHAR(40) NOT NULL,
    CategoryID   INT NOT NULL,
    UnitPrice    DECIMAL(10, 2),
    UnitsInStock SMALLINT NOT NULL DEFAULT 0,
    UnitsOnOrder SMALLINT NOT NULL DEFAULT 0,
    FOREIGN KEY (CategoryID) REFERENCES categories (CategoryID)
);

INSERT INTO categories VALUES (1, 'Beverages'), (2, 'Condiments'), (3, 'Confections'), (4, 'Dairy Products');

INSERT INTO products (ProductName, CategoryID, UnitPrice, UnitsInStock, UnitsOnOrder) VALUES
('Chai', 1, 18.00, 39, 0), ('Chang', 1, 19.00, 17, 40), ('Guaraná Fantástica', 1, 4.50, 20, 0),
('Sasquatch Ale', 1, 14.00, 111, 0), ('Steeleye Stout', 1, 18.00, 20, 0), ('Côte de Blaye', 1, 263.50, 17, 0),
('Chartreuse verte', 1, 18.00, 69, 0), ('Ipoh Coffee', 1, 46.00, 17, 10), ('Outback Lager', 1, 15.00, 15, 10),
('Aniseed Syrup', 2, 10.00, 13, 70), ('Chef Anton''s Cajun Seasoning', 2, 22.00, 53, 0),
('Grandma''s Boysenberry Spread', 2, 25.00, 120, 0), ('Northwoods Cranberry Sauce', 2, 40.00, 6, 0),
('Genen Shouyu', 2, 15.50, 39, 0), ('Sirop d''érable', 2, 28.50, 113, 0), ('Louisiana Hot Spiced Okra', 2, 17.00, 4, 100),
('Pavlova', 3, 17.45, 29, 0), ('Teatime Chocolate Biscuits', 3, 9.20, 25, 0), ('Sir Rodney''s Scones', 3, 10.00, 3, 40),
('NuNuCa Nuß-Nougat-Creme', 3, 14.00, 76, 0), ('Schoggi Schokolade', 3, 43.90, 49, 0), ('Chocolade', 3, 12.75, 15, 70),
('Maxilaku', 3, 20.00, 10, 60), ('Tarte au sucre', 3, 49.30, 17, 0),
('Queso Cabrales', 4, 21.00, 22, 30), ('Queso Manchego La Pastora', 4, 38.00, 86, 0), ('Gorgonzola Telino', 4, 12.50, 0, 70),
('Mascarpone Fabioli', 4, 32.00, 9, 40), ('Geitost', 4, 2.50, 112, 0), ('Raclette Courdavault', 4, 55.00, 79, 0),
('Camembert Pierrot', 4, 34.00, 19, 0), ('Mozzarella di Giovanni', 4, 34.80, 14, 0);

2. Create the project #

On start.spring.io, create a Maven project with Java 21, Spring Boot 4.1.x and the dependencies Spring Web, Spring Data JPA, MySQL Driver and Thymeleaf:

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-webmvc</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-thymeleaf</artifactId>
    </dependency>
    <dependency>
        <groupId>com.mysql</groupId>
        <artifactId>mysql-connector-j</artifactId>
        <scope>runtime</scope>
    </dependency>
</dependencies>

Configure src/main/resources/application.properties:

spring.datasource.url=jdbc:mysql://localhost:3306/northwind
spring.datasource.username=${DB_USER:northwind_app}
spring.datasource.password=${DB_PASS}
spring.jpa.hibernate.ddl-auto=none
spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
spring.jpa.open-in-view=false

The naming strategy keeps the Northwind column names (UnitsInStock) exactly as written in @Column; the default would look for units_in_stock.

3. The model: entity, projection and repository #

Create src/main/java/com/example/northwind/Product.java. The entity maps the products table:

package com.example.northwind;

import java.math.BigDecimal;

import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.Id;
import jakarta.persistence.Table;

@Entity
@Table(name = "products")
public class Product {

    @Id
    @Column(name = "ProductID")
    private Integer id;

    @Column(name = "ProductName")
    private String name;

    @Column(name = "CategoryID")
    private Integer categoryId;

    @Column(name = "UnitPrice")
    private BigDecimal unitPrice;

    @Column(name = "UnitsInStock")
    private int unitsInStock;

    @Column(name = "UnitsOnOrder")
    private int unitsOnOrder;

    protected Product() {
        // required by JPA
    }

    public Integer getId() { return id; }
    public String getName() { return name; }
    public Integer getCategoryId() { return categoryId; }
    public BigDecimal getUnitPrice() { return unitPrice; }
    public int getUnitsInStock() { return unitsInStock; }
    public int getUnitsOnOrder() { return unitsOnOrder; }
}

The chart needs totals per category, not single products, so the repository declares an aggregate query. The result columns are read through an interface projection: Spring Data matches each alias (categoryName, unitsInStock, …) to a getter. Create ProductRepository.java:

package com.example.northwind;

import java.util.List;

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

public interface ProductRepository extends JpaRepository<Product, Integer> {

    interface CategoryStock {
        String getCategoryName();
        Number getUnitsInStock();   // SUM() returns DECIMAL in MySQL
        Number getUnitsOnOrder();
    }

    @Query(value = """
            SELECT c.CategoryName      AS categoryName,
                   SUM(p.UnitsInStock) AS unitsInStock,
                   SUM(p.UnitsOnOrder) AS unitsOnOrder
            FROM products p
            JOIN categories c ON c.CategoryID = p.CategoryID
            GROUP BY c.CategoryName
            ORDER BY c.CategoryName
            """, nativeQuery = true)
    List<CategoryStock> stockByCategory();
}

4. The controllers #

The REST controller turns the projections into plain records, so the JSON has exactly three fields per category. Create StockApiController.java:

package com.example.northwind;

import java.util.List;

import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class StockApiController {

    public record CategoryStockDto(String categoryName, long unitsInStock, long unitsOnOrder) {
    }

    private final ProductRepository products;

    public StockApiController(ProductRepository products) {
        this.products = products;
    }

    @GetMapping("/api/stock-by-category")
    public List<CategoryStockDto> stockByCategory() {
        return products.stockByCategory().stream()
                .map(s -> new CategoryStockDto(s.getCategoryName(),
                        s.getUnitsInStock().longValue(), s.getUnitsOnOrder().longValue()))
                .toList();
    }
}

The view controller returns the name of the Thymeleaf template, stock. Create StockPageController.java:

package com.example.northwind;

import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;

@Controller
public class StockPageController {

    @GetMapping("/stock")
    public String stock() {
        return "stock";
    }
}

5. The Thymeleaf view #

Create src/main/resources/templates/stock.html. th:data-url="@{/api/stock-by-category}" writes the API address, including the context path if the application has one, into a data-url attribute. The chart stacks the two series (type: 'stackedcolumn'), so each column shows the total and how it splits.

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>Stock by category</title>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/jqwidgets-scripts@27/jqwidgets/styles/jqx.base.css">
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/jqwidgets-scripts@27/jqwidgets/styles/jqx.material-purple.css">
    <script src="https://cdn.jsdelivr.net/npm/jqwidgets-scripts@27/jqwidgets/jqxcore.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/jqwidgets-scripts@27/jqwidgets/jqxdata.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/jqwidgets-scripts@27/jqwidgets/jqxdraw.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/jqwidgets-scripts@27/jqwidgets/jqxchart.core.js"></script>
    <style>
        #chart { width: 100%; height: 420px; }
    </style>
</head>
<body>
    <h1>Stock by category</h1>
    <div id="chart" th:data-url="@{/api/stock-by-category}"></div>

    <script>
        const chartElement = document.getElementById('chart');

        const dataAdapter = new jqx.dataAdapter({
            datatype: 'json',
            url: chartElement.dataset.url,
            datafields: [
                { name: 'categoryName', type: 'string' },
                { name: 'unitsInStock', type: 'int' },
                { name: 'unitsOnOrder', type: 'int' }
            ]
        });

        $('#chart').jqxChart({
            theme: 'material-purple',
            title: 'Stock by category',
            description: 'Units in stock and on order',
            source: dataAdapter,
            enableAnimations: true,
            xAxis: {
                dataField: 'categoryName',
                gridLines: { visible: false }
            },
            valueAxis: {
                minValue: 0,
                title: { text: 'Units' }
            },
            seriesGroups: [{
                type: 'stackedcolumn',
                columnsGapPercent: 40,
                series: [
                    { dataField: 'unitsInStock', displayText: 'In stock' },
                    { dataField: 'unitsOnOrder', displayText: 'On order' }
                ]
            }]
        });
    </script>
</body>
</html>

6. Run it #

DB_PASS=change-me ./mvnw spring-boot:run

Open http://localhost:8080/stock. The chart shows four stacked columns; http://localhost:8080/api/stock-by-category shows the JSON behind them.

Related topics #

• Bind jqxChart with a plain REST controller and JdbcClient
• Bind jqxGrid with Spring MVC
• jqxChart API