# Реалізація інформаційного та програмного забезпечення

# SQL-скрипт для створення на початкового наповнення бази даних

-- MySQL Script generated by MySQL Workbench -- Thu May 25 17:59:40 2023 -- Model: New Model Version: 1.0 -- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES, NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- Schema mydb

DROP SCHEMA IF EXISTS mydb ;

-- Schema mydb

CREATE SCHEMA IF NOT EXISTS mydb DEFAULT CHARACTER SET utf8 ; USE mydb ;

-- Table mydb.user

DROP TABLE IF EXISTS mydb.user ;

CREATE TABLE IF NOT EXISTS mydb.user ( id INT NOT NULL AUTO_INCREMENT, email VARCHAR(255) NOT NULL, password VARCHAR(255) NOT NULL, fullname VARCHAR(255) NULL, PRIMARY KEY (id), UNIQUE INDEX id_UNIQUE (id ASC) VISIBLE, UNIQUE INDEX email_UNIQUE (email ASC) VISIBLE) ENGINE = InnoDB;

-- Table mydb.quiz

DROP TABLE IF EXISTS mydb.quiz ;

CREATE TABLE IF NOT EXISTS mydb.quiz ( id INT NOT NULL AUTO_INCREMENT, text VARCHAR(255) NOT NULL, topic VARCHAR(255) NOT NULL, creation_date DATETIME NOT NULL, creator_id INT NOT NULL, PRIMARY KEY (id, creator_id), INDEX fk_quiz_user_idx (creator_id ASC) VISIBLE, UNIQUE INDEX id_UNIQUE (id ASC) VISIBLE, CONSTRAINT fk_quiz_user FOREIGN KEY (creator_id) REFERENCES mydb.user (id) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;

-- Table mydb.question

DROP TABLE IF EXISTS mydb.question ;

CREATE TABLE IF NOT EXISTS mydb.question ( id INT NOT NULL AUTO_INCREMENT, text VARCHAR(255) NOT NULL, option_type VARCHAR(255) NOT NULL, quiz_id INT NOT NULL, PRIMARY KEY (id, quiz_id), UNIQUE INDEX id_UNIQUE (id ASC) VISIBLE, INDEX fk_question_quiz1_idx (quiz_id ASC) VISIBLE, CONSTRAINT fk_question_quiz1 FOREIGN KEY (quiz_id) REFERENCES mydb.quiz (id) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;

-- Table mydb.option

DROP TABLE IF EXISTS mydb.option ;

CREATE TABLE IF NOT EXISTS mydb.option ( id INT NOT NULL AUTO_INCREMENT, text VARCHAR(255) NOT NULL, question_id INT NOT NULL, PRIMARY KEY (id), UNIQUE INDEX id_UNIQUE (id ASC) VISIBLE, INDEX fk_option_question1_idx (question_id ASC) VISIBLE, CONSTRAINT fk_option_question1 FOREIGN KEY (question_id) REFERENCES mydb.question (id) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;

-- Table mydb.choosen_option

DROP TABLE IF EXISTS mydb.choosen_option ;

CREATE TABLE IF NOT EXISTS mydb.choosen_option ( option_id INT NOT NULL, question_id INT NOT NULL, quiz_id INT NOT NULL, user_id INT NOT NULL, INDEX fk_choosen_option_option1_idx (option_id ASC) VISIBLE, INDEX fk_choosen_option_question1_idx (question_id ASC) VISIBLE, INDEX fk_choosen_option_quiz1_idx (quiz_id ASC) VISIBLE, INDEX fk_choosen_option_user1_idx (user_id ASC) VISIBLE, CONSTRAINT fk_choosen_option_option1 FOREIGN KEY (option_id) REFERENCES mydb.option (id) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT fk_choosen_option_question1 FOREIGN KEY (question_id) REFERENCES mydb.question (id) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT fk_choosen_option_quiz1 FOREIGN KEY (quiz_id) REFERENCES mydb.quiz (id) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT fk_choosen_option_user1 FOREIGN KEY (user_id) REFERENCES mydb.user (id) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;

-- Table mydb.answer

DROP TABLE IF EXISTS mydb.answer ;

CREATE TABLE IF NOT EXISTS mydb.answer ( option_id INT NOT NULL, question_id INT NOT NULL, quiz_id INT NOT NULL, INDEX fk_answer_option1_idx (option_id ASC) VISIBLE, INDEX fk_answer_question1_idx (question_id ASC) VISIBLE, INDEX fk_answer_quiz1_idx (quiz_id ASC) VISIBLE, CONSTRAINT fk_answer_option1 FOREIGN KEY (option_id) REFERENCES mydb.option (id) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT fk_answer_question1 FOREIGN KEY (question_id) REFERENCES mydb.question (id) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT fk_answer_quiz1 FOREIGN KEY (quiz_id) REFERENCES mydb.quiz (id) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;

SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

# RESTfull сервіс для управління даними

# Entity User.java

package ua.master.survey.api.entity;

import jakarta.persistence.*;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

@Entity
@Table(name = "user")
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    @Column(name = "email")
    private String email;
    @Column(name = "password")
    private String password;
    @Column(name = "fullname")
    private String fullname;
}

# Repository UserRepository.java

package ua.master.survey.api.repository;

import org.springframework.data.jpa.repository.JpaRepository;
import ua.master.survey.api.entity.User;

public interface UserRepository extends JpaRepository<User, Integer> {
}

# Service UserService.java

package ua.master.survey.api.service;

import lombok.RequiredArgsConstructor;
import org.springframework.stereotype.Service;
import ua.master.survey.api.entity.User;
import ua.master.survey.api.repository.UserRepository;

import java.util.List;
import java.util.Optional;

@Service
@RequiredArgsConstructor
public class UserService {
    private final UserRepository userRepository;

    public List<User> getAllUsers() {
        return userRepository.findAll();
    }

    public void saveUser(User user) {
        userRepository.save(user);
    }

    public User getUser(int id) {
        User user = null;
        Optional<User> optional = userRepository.findById(id);
        if (optional.isPresent()) {
            user = optional.get();
        }
        return user;
    }

    public void deleteUser(int id) {
        userRepository.deleteById(id);
    }
}

# Controller RestController.java

package ua.master.survey.api.controller;

import lombok.RequiredArgsConstructor;
import org.springframework.web.bind.annotation.*;
import ua.master.survey.api.entity.User;
import ua.master.survey.api.service.UserService;

import java.util.List;

@org.springframework.web.bind.annotation.RestController
@RequestMapping("/api")
@RequiredArgsConstructor
public class RestController {
    private final UserService userService;

    @GetMapping("/users")
    public List<User> getAllUsers() {
        return userService.getAllUsers();
    }

    @GetMapping("/users/{id}")
    public User getUserById(@PathVariable int id) {
        return userService.getUser(id);
    }

    @PostMapping("/users")
    public String addNewUser(@RequestBody User user) {
        userService.saveUser(user);
        return "User was added successfully!";
    }

    @PutMapping("/users")
    public String updateUser(@RequestBody User user) {
        userService.saveUser(user);
        return "User was updated successfully!";
    }

    @DeleteMapping("/users/{id}")
    public String deleteUser(@PathVariable int id) {
        userService.deleteUser(id);
        return "User with ID = " + id + " was deleted!";
    }
}

# Starter JavaApiApplication.java

package ua.master.survey.api;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class JavaApiApplication {

    public static void main(String[] args) {
        SpringApplication.run(JavaApiApplication.class, args);
    }

}

← Проектування інформаційного забезпечення Тестування працездатності системи →