INSY5 — MitarbeitsÜbung (3. Trimester)

GitHub Repository : 08.04.2025 (Andri)

• Version Tag
• Description
• Collaborators : @HTL-Warrior && @perdix

MySQL Server Setup Commands (Andri)

ssh root@IP
sudo apt update && sudo apt upgrade -y
sudo apt install mariadb-server -y
sudo systemctl enable mariadb
sudo systemctl start mariadb
sudo mysql_secure_installation
sudo mariadb

CREATE USER 'USER'@'%' IDENTIFIED BY 'PASSWORD';
GRANT ALL PRIVILEGES ON *.* TO 'USER'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
EXIT;

sudo ufw allow 3306
sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

# FIND THIS LINE:
# bind-address = 127.0.0.1

# CHANGE TO:
# bind-address = 0.0.0.0


# RESTART MARIADB
sudo systemctl restart mariadb

MySQL Database Structure (Anxhelo)

CREATE DATABASE IF NOT EXISTS MySql_Exercise_Andri_Anxhelo;
USE MySql_Exercise_Andri_Anxhelo;
CREATE TABLE IF NOT EXISTS books(
    bookid  int auto_increment primary key,
    title   varchar(255),
    pages_length    int,
    author  varchar(255)
);
INSERT INTO books(title, pages_length, author) VALUES
('To Kill a Mockingbird', 281, 'Harper Lee'),
('The Great Gatsby', 180, 'F. Scott Fitzgerald'),
('Moby-Dick', 635, 'Herman Melville'),
('Pride and Prejudice', 432, 'Jane Austen'),
('War and Peace', 1225, 'Leo Tolstoy');

Java Database Connector (Anxhelo)

##Java Database Connector
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 * A simple Java example demonstrating how to connect to a MySQL database,
 * execute a SELECT query, and display results from the 'books' table.
 *
 * This example connects to a database using JDBC and prints book details.
 */
public class MySQLExample {
    /**
     * Main method that initiates the database connection and queries the 'books' table.
     *
     * @param args command-line arguments (not used)
     */
    public static void main(String[] args) {
        // JDBC URL, username and password for the database connection
        String url = "jdbc:mysql://64.226.127.206:3306/MySql_Exercise_Andri_Anxhelo";
        String user = "anxhelo";
        String password = "1Schueler@";

        try {
            // Establish the database connection
            Connection connection = DriverManager.getConnection(url, user, password);

            // Create a statement to execute SQL queries
            Statement statement = connection.createStatement();

            // SQL query to select all rows from the 'books' table
            String query = "SELECT * FROM books";

            // Execute the query and get the result set
            ResultSet resultSet = statement.executeQuery(query);

            // Iterate through the result set and print book details
            while (resultSet.next()) {
                System.out.println("Book ID: " + resultSet.getInt("bookid"));
                System.out.println("Title: " + resultSet.getString("title"));
                System.out.println("Author: " + resultSet.getString("author"));
                System.out.println("Pages: " + resultSet.getInt("pages_length"));
                System.out.println();
            }

            // Close the database connection
            connection.close();

        } catch (Exception e) {
            // Print any exceptions that occur
            e.printStackTrace();
        }
    }
}