Author: Leonhard Stransky, 4AHIT

Date: 2024.04.09

This exercise is intended to demonstrate the interaction between a programming language (Java) and a persistance layer (MySQL, PostgreSQL).

First you should follow the Spring tutorial "Accessing data with MySQL" and document all important steps in your protocol. Don't forget to make notes about all problems occured during the setup. Afterwards you should extend the data model of the example and adapt it for the Data Warehouse application (used in the former projects). One relation between the entities Datawarehouse and Products is required in this example. Please read the documentation how you implementation entity relations using the ORM model.

Document all individual implementation steps and any problems that arise in a log (Markdown). Create a GITHUB repository for this project and add the link to it in the comments.

• Group size: 1 Person.
• Result by protocol and delivery meeting (in English).
• Requirements überwiegend erfüllt.
  • Answer the questions below about the ORM framework.
  • Use the tutorial "Accessing data with MySQL"
  • Implement the MySQL example with the User database
  • Document each single development step in your protocol and describe the most important code snippets in few sentences. Furthermore, the output of the application and any problems that occur should be documented in submission document.
• Requirements zur Gänze erfüllt
  • Customize the data model for the Data Warehouse application (min. 2 entities with 1 relation).
  • *. Insert following records: 2 Data Warehouse records, 10 Product records.
  • Document which parts of the program need to be adapted
• Extended Requirements überwiegend erfüllt
  • Find out which methods are available for the CrudRepository to collect data https://docs.spring.io/spring-data/commons/docs/current/api/org/springframework/data/repository/CrudRepository.html.
  • Extend the Data Warehouse repository with following functionalities:
    • Collect all data of one data warehouse specified by datawarehouseID.
    • Collect a single product of a data warehouse specified by datawarehouseID and productID.
    • Update a data warehouse using datawarehouseID.
  • Document the parts of your project which have to be extend
• Extended Requirements zur Gänze erfüllt.
  • Replace the MySQL database management system with a PostgreSQL database management system.
  • Document which configuration files need to be adapted

docker run --name mysql -e MYSQL_ROOT_PASSWORD=root -p 3306:3306 -d mysql:latest

docker exec -it mysql bash
mysql -u root -p

create database db_example; -- Creates the new database
create user 'springuser'@'%' identified by 'root'; -- Creates the user
grant all on db_example.* to 'springuser'@'%';

spring.application.name=demo
spring.jpa.hibernate.ddl-auto=update
spring.datasource.url=jdbc:mysql://${MYSQL_HOST:localhost}:3306/db_example
spring.datasource.username=springuser
spring.datasource.password=root
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

MainController.java

package org.example;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;

@Controller	// This means that this class is a Controller
@RequestMapping(path="/demo") // This means URL's start with /demo (after Application path)
public class MainController {
	@Autowired // This means to get the bean called userRepository
			   // Which is auto-generated by Spring, we will use it to handle the data
	private UserRepository userRepository;

	@PostMapping(path="/add") // Map ONLY POST Requests
	public @ResponseBody String addNewUser (@RequestParam String name
			, @RequestParam String email) {
		// @ResponseBody means the returned String is the response, not a view name
		// @RequestParam means it is a parameter from the GET or POST request

		User n = new User();
		n.setName(name);
		n.setEmail(email);
		userRepository.save(n);
		return "Saved";
	}

	@GetMapping(path="/all")
	public @ResponseBody Iterable<User> getAllUsers() {
		// This returns a JSON or XML with the users
		return userRepository.findAll();
	}
}

• Controller Definition: MainController marked with @Controller for handling HTTP requests.
• URL Mapping: Base path set to /demo using @RequestMapping.
• Dependency Injection:
  • @Autowired used to inject UserRepository for data access.
• POST /demo/add Endpoint:
  • Handles POST requests to add a new user.
  • Accepts name and email via @RequestParam.
  • Creates and saves a User object, returns "Saved" as the response.
• GET /demo/all Endpoint:
  • Handles GET requests to retrieve all users.
  • Uses userRepository.findAll() to fetch users, returns data as JSON/XML.

User.java

package org.example;

import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;

@Entity // This tells Hibernate to make a table out of this class
public class User {
	@Id
	@GeneratedValue(strategy=GenerationType.AUTO)
	private Integer id;

	private String name;

	private String email;

	public Integer getId() {
		return id;
	}

	public void setId(Integer id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getEmail() {
		return email;
	}

	public void setEmail(String email) {
		this.email = email;
	}
}

• Entity Annotation: @Entity indicates that Hibernate should map this class to a database table.
• Primary Key:
  • @Id marks the id field as the primary key of the table.
  • @GeneratedValue(strategy=GenerationType.AUTO) automatically handles the generation of unique IDs for new records.
• Fields:
  • private Integer id: Unique identifier for each user.
  • private String name: Stores the user's name.
  • private String email: Stores the user's email address.
• Getters and Setters: Provide access to the private fields.

(Add Documentation here)

• What is ORM and how is JPA used?

ORM (Object-Relational Mapping) is a technique used to map objects in object-oriented programming languages to relational databases. JPA (Java Persistence API) is a specification in Java used to abstract and manage the ORM process, allowing developers to interact with their database through Java objects rather than SQL queries.

• What is the application.properties used for and where must it be stored?

The application.properties file is used in Java projects to configure parameters for the application's runtime environment, such as database connections and application-specific settings. It must be stored in the src/main/resources directory in standard Java projects to ensure it is included in the classpath.

• Which annotations are frequently used for entity types? Which key points must be observed?

Common JPA annotations for entity types include @Entity for marking a class as a database table, @Table for specifying table-specific details, @Id for the primary key, and @Column for mapping class attributes to table columns. Key points to observe include ensuring consistency between database schema and class mappings, correctly handling entity relationships, and managing entity identifiers uniquely.

• What methods do you need for CRUD operations?

For CRUD (Create, Read, Update, Delete) operations, typical methods include:

• save() for creating or updating entities,
• findById() for retrieving entities,
• delete() for removing entities,
• findAll() for fetching all entities.

These methods can be readily implemented using Spring Data JPA repositories, which provide built-in support for these operations without the need for explicit SQL queries.

[1] https://docs.spring.io/spring-framework/reference/data-access/orm.html

[2] https://spring.io/guides/gs/accessing-data-mysql

[3] https://dzone.com/articles/what-is-the-difference-between-hibernate-and-sprin-1