Spring Boot - MySQL Connectivity - An Example Study

Here's an example of how to connect a Spring Boot application to a MySQL database, and use it to persist customer and order data:

1. Add the MySQL dependency to your pom.xml file:

<dependency>
   <groupId>mysql</groupId>
   <artifactId>mysql-connector-java</artifactId>
   <scope>runtime</scope>
</dependency>

2. Create a application.properties file in the src/main/resources directory and add the following properties to configure the connection to the MySQL database:

spring.datasource.url=jdbc:mysql://localhost:3306/test_db spring.datasource.username=root spring.datasource.password=root spring.datasource.driver-class-name=com.mysql.jdbc.Driver spring.jpa.generate-ddl=true spring.jpa.hibernate.ddl-auto=update 

3. Create two JPA entities, Customer and Order, to represent the customers and orders in your database:

@Entity
public class Customer {
   @Id
   @GeneratedValue(strategy = GenerationType.IDENTITY)
   private Long id;
   private String name;
   private String email;
   ...
}

@Entity
public class Order {
   @Id
   @GeneratedValue(strategy = GenerationType.IDENTITY)
   private Long id;
   private String orderNumber;
   private LocalDateTime orderDate;
   ...
   @ManyToOne
   @JoinColumn(name = "customer_id")
   private Customer customer;
   ...
}

4. Create a repository interface for each entity to handle database operations:

public interface CustomerRepository extends JpaRepository<Customer, Long> {}

public interface OrderRepository extends JpaRepository<Order, Long> {}

5. In a service class, use the repositories to persist and retrieve customer and order data:

@Service
public class CustomerOrderService {
   private final CustomerRepository customerRepository;
   private final OrderRepository orderRepository;

   public CustomerOrderService(CustomerRepository customerRepository, OrderRepository orderRepository) {
      this.customerRepository = customerRepository;
      this.orderRepository = orderRepository;
   }

   public Customer addCustomer(Customer customer) {
      return customerRepository.save(customer);
   }

   public Order addOrder(Order order) {
      return orderRepository.save(order);
   }

   public List<Order> findOrdersByCustomer(Customer customer) {
      return orderRepository.findByCustomer(customer);
   }
   ...
}

6. Finally, you can use the service in a controller to handle HTTP requests:

@RestController
public class CustomerOrderController {
   private final CustomerOrderService customerOrderService;

   public CustomerOrderController(CustomerOrderService customerOrderService) {
      this.customerOrderService = customerOrderService;
   }

public Customer addCustomer(@RequestBody Customer customer) {
      return customerOrderService.addCustomer(customer);
   }

   @PostMapping("/orders")
   public Order addOrder(@RequestBody Order order) {
      return customerOrderService.addOrder(order);
   }

   @GetMapping("/customers/{id}/orders")
   public List<Order> findOrdersByCustomer(@PathVariable Long id) {
      Customer customer = customerOrderService.findCustomerById(id);
      return customerOrderService.findOrdersByCustomer(customer);
   }
}

This is a basic example of connecting a Spring Boot application to a MySQL database and using it to persist customer and order data. You can add additional functionality and error handling as needed.

Here's an example of what the MySQL storage structure might look like based on the entities defined in the code:

-- customers table
CREATE TABLE customers (
   id INT AUTO_INCREMENT PRIMARY KEY,
   name VARCHAR(255) NOT NULL,
   email VARCHAR(255) NOT NULL
);

-- orders table
CREATE TABLE orders (
   id INT AUTO_INCREMENT PRIMARY KEY,
   order_number VARCHAR(255) NOT NULL,
   order_date DATETIME NOT NULL,
   customer_id INT,
   FOREIGN KEY (customer_id) REFERENCES customers(id)
);

This structure assumes that each order is associated with one customer and that each customer can have many orders. The customer_id column in the orders table is a foreign key that references the id column in the customers table, ensuring that each order is associated with a valid customer.

Request to Customer :

POST /customers
Content-Type: application/json

{
   "name": "John Doe",
   "email": "john.doe@example.com"
}

Response from Customer :

HTTP/1.1 201 Created
Content-Type: application/json

{
   "id": 1,
   "name": "John Doe",
   "email": "john.doe@example.com"
}

Order Request :

POST /orders
Content-Type: application/json

{
   "orderNumber": "123456",
   "orderDate": "2023-02-05T12:00:00.000Z",
   "customerId": 1
}

Order Response:

HTTP/1.1 201 Created
Content-Type: application/json

{
   "id": 1,
   "orderNumber": "123456",
   "orderDate": "2023-02-05T12:00:00.000Z",
   "customerId": 1
}