Spring项目DevExpress表格：如何替换data.js为SQL Server数据库数据

Hey there! Let's walk through how to swap out that static data.js content with real SQL Server data in your Spring + DevExpress + jQuery setup. I'll break this down into clear, actionable steps:

First, we need to create backend components to connect to your SQL Server database and expose the order data via a REST endpoint.

1.1 Create an Entity Class #

This class maps directly to your SQL Server orders table (adjust the table/column names to match your actual database schema):

import javax.persistence.*;

@Entity
@Table(name = "orders")
public class Order {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer ID;
    private Integer OrderNumber;
    private String OrderDate;
    private String DeliveryDate;
    private Double SaleAmount;
    private String Terms;
    private String CustomerStoreCity;
    private String Employee;

    // Required: No-arg constructor, all-arg constructor, and getter/setter methods for all fields
}

1.2 Create a Repository Interface #

Use Spring Data JPA to handle database queries with minimal code:

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

public interface OrderRepository extends JpaRepository<Order, Integer> {
    // The default `findAll()` method will fetch all orders for us
}

1.3 Add a Service Layer (Optional but Recommended) #

Separate business logic from the controller for better maintainability:

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;

@Service
public class OrderService {
    private final OrderRepository orderRepository;

    @Autowired
    public OrderService(OrderRepository orderRepository) {
        this.orderRepository = orderRepository;
    }

    public List<Order> getAllOrders() {
        return orderRepository.findAll();
    }
}

1.4 Create a REST Controller #

Expose an endpoint to return order data as JSON:

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;

@RestController
@RequestMapping("/api/orders")
public class OrderController {
    private final OrderService orderService;

    @Autowired
    public OrderController(OrderService orderService) {
        this.orderService = orderService;
    }

    @GetMapping
    public List<Order> getOrders() {
        return orderService.getAllOrders();
    }
}

1.5 Configure SQL Server Connection #

Add these settings to your application.properties file (replace placeholders with your database details):

spring.datasource.url=jdbc:sqlserver://localhost:1433;databaseName=YourDatabaseName;encrypt=true;trustServerCertificate=true
spring.datasource.username=your_db_username
spring.datasource.password=your_db_password
spring.datasource.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.jpa.hibernate.ddl-auto=none
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.SQLServerDialect

Now we'll replace the static orders array with a dynamic API call using jQuery.

2.1 Adjust data.js (or Remove It) #

You can either delete the static var orders = [...] line from data.js, or repurpose it to hold the fetched data later. For simplicity, we'll handle the data fetch directly in index.js.

2.2 Modify index.js to Fetch Data #

Update your DevExpress DataGrid initialization to use jQuery's ajax method to pull data from the backend:

$(function() {
    // Fetch data from the Spring API
    $.ajax({
        url: "/api/orders",
        method: "GET",
        dataType: "json",
        success: function(fetchedOrders) {
            // Initialize DevExpress DataGrid with the fetched data
            $("#gridContainer").dxDataGrid({
                dataSource: fetchedOrders,
                columns: [
                    { dataField: "ID" },
                    { dataField: "OrderNumber" },
                    { dataField: "OrderDate", dataType: "date" },
                    { dataField: "DeliveryDate", dataType: "datetime" },
                    { dataField: "SaleAmount", format: "currency" },
                    { dataField: "Terms" },
                    { dataField: "CustomerStoreCity" },
                    { dataField: "Employee" }
                ],
                // Keep your existing DevExpress config (filtering, pagination, etc.)
                filtering: { mode: "row" },
                paging: { pageSize: 10 },
                pager: {
                    showPageSizeSelector: true,
                    allowedPageSizes: [5, 10, 20]
                }
            });
        },
        error: function(xhr, status, error) {
            console.error("Failed to load order data:", error);
            alert("Oops! Couldn't load order data. Check if the backend is running correctly.");
        }
    });
});

• CORS Configuration: If your frontend and backend ever run on different ports (e.g., frontend on 8080, backend on 8081), add cross-origin support in Spring. You can annotate the OrderController with @CrossOrigin(origins = "http://your-frontend-url") or set up a global CORS config.
• Date Format Matching: Ensure the date strings returned from SQL Server align with what DevExpress expects. If using Date types in your entity, Spring will return ISO-format dates (e.g., 2017-04-13T09:00:00), which DevExpress can parse automatically.
• Test the API First: Before debugging the frontend, visit http://localhost:8080/api/orders in your browser to confirm it returns valid JSON data.

内容的提问来源于stack exchange，提问作者BENHASSINE MohamedFourat