java\db\CRUDUtils.java

package db;

import Entity.User;
import org.springframework.boot.jackson.autoconfigure.JacksonProperties;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class CRUDUtils {

    private static String INSERT_USER = "INSERT INTO users (name, surname, email, address) VALUES (?, ?, ?, ?)";

    public static List<User> getAllUsers(String query) {
        List<User> all_users = new ArrayList<User>();
        try (Connection connection = DBUtils.getConnection(); PreparedStatement preparedStatement = connection.prepareStatement(query)) {
            ResultSet rs = preparedStatement.executeQuery();
            while (rs.next()) {
//                int id = rs.getInt("id");
                String name = rs.getString("name");
                String surname = rs.getString("surname");
                String email = rs.getString("email");
                String address = rs.getString("address");

                all_users.add(new User(name, surname, email, address));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();

        } finally {

        }
        return all_users;
    }

    public static List<User> addUser(User user) {
        List<User> all_users = new ArrayList<User>();
        try (Connection connection = DBUtils.getConnection(); PreparedStatement preparedStatement = connection.prepareStatement(INSERT_USER)) {
            preparedStatement.setString(1, user.getName());
            preparedStatement.setString(2, user.getSurname());
            preparedStatement.setString(3, user.getEmail());
            preparedStatement.setString(4, user.getAddress());
            preparedStatement.executeUpdate();

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
        }
        return all_users;
    }

    public static JacksonProperties.Json showAllUsers(int id) {
        getAllUsers("SELECT * FROM users WHERE id = " + id);

        return null;
    }
}

java\db\DBUtils.java

package db;

import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

public class DBUtils {

    private static final String DB_URL = "jdbc:h2:mem:test;INIT=RUNSCRIPT FROM 'classpath:init.sql'";
    private static final String DB_USERNAME = "sa";
    private static final String DB_PASSWORD = "";

    public static Connection getConnection() throws SQLException {

//        String DB_URL = null, DB_USERNAME = null, DB_PASSWORD = null;

        FileInputStream fis;
        Properties prop = new Properties();

//        try {
//            fis = new FileInputStream("src/main/resources/config.properties");
//            prop.load(fis);
//            DB_URL = prop.getProperty("db.url");
//            DB_USERNAME = prop.getProperty("db.username");
//            DB_PASSWORD = prop.getProperty("db.password");
//            fis.close();
//        } catch (FileNotFoundException e) {
//            throw new RuntimeException(e);
//        } catch (IOException e) {
//            throw new RuntimeException(e);
//        }

        Connection connect = null;
        connect = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
        return connect;
    }

}

java\Entity\User.java

package Entity;

import java.sql.*;

import db.DBUtils;

public class User {
    private int id;
    private String name;
    private String surname;
    private String email;
    private String address;


    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", surname='" + surname + '\'' +
                ", email='" + email + '\'' +
                ", address='" + address + '\'' +
                '}';
    }

    public User(String name, String surname, String email, String address) {
//        this.id = id; auto increment
        this.name = name;
        this.surname = surname;
        this.email = email;
        this.address = address;
    }

    public int getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public String getSurname() {
        return surname;
    }

    public void setSurname(String surname) {
        this.surname = surname;
    }

    public String getEmail() {
        return email;
    }

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

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

}

java\Service\CrudApplication.java

package Service;

import Entity.User;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import db.CRUDUtils;
import tools.jackson.databind.ObjectMapper;

import java.io.PrintStream;
import java.util.List;

@SpringBootApplication
@RestController
public class CrudApplication {

    private ObjectMapper objectMapper;

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

    @GetMapping("/api/get_all_users")
    public PrintStream users(@RequestParam(value = "id", defaultValue = "") String id) {
        List<User> allUsers = null;
        if (id.isEmpty()) {
            allUsers = CRUDUtils.getAllUsers("SELECT * FROM users");
        } else {
            allUsers = CRUDUtils.getAllUsers("SELECT * FROM users WHERE id = " + id);
        }

//        List<User> allUsers = CRUDUtils.getAllUsers("SELECT * FROM users");
//        System.out.println(allUsers);
        System.out.println(allUsers);
        return System.out;
    }

    @PostMapping("/api/add_user")
    public int addUser(
            @RequestParam(required = false) String name,
            @RequestParam(required = false) String surname,
            @RequestParam(required = false) String email,
            @RequestParam(required = false) String address
    ) {
        try {
            CRUDUtils.addUser(new User(name, surname, email, address));
        } catch (Exception e) {
            e.printStackTrace();
        }
        return 1;
    }
}

resources\application.properties

spring.application.name=CRUD
server.port=9090

resources\config.properties

db.url = jdbc:h2:mem:test:INIT=RUNSCRIPT FROM 'classpath:db/init.sql'

resources\init.sql

CREATE SCHEMA IF NOT EXISTS test;
SET SCHEMA test;

DROP TABLE IF EXISTS users;

CREATE TABLE users
(
    id      BIGINT PRIMARY KEY AUTO_INCREMENT,
    name    VARCHAR(80),
    surname VARCHAR(80),
    email   VARCHAR(80),
    address VARCHAR(255)
);

INSERT INTO users (name, surname, email, address)
VALUES ('John', 'Doe', '', '123 Entity.User St');