Розробка уроку — практичної роботи

Тема: cтворення програмного проекта простої бази даних «Учні» з даними про прізвище, ім'я, успішність, забезпечення виведення даних про учнів за зазначиними критеріями.

Мета: навчити основам роботи з базами даних SQL з використанням мови програмування C++.

Учні повинні знати:

Учні повинні мати уявлення про можливості комплексу C++ i MySQL.

Учні повинні вміти:

Обладнання: комп'ютери зі встановленими ОС Linux Mint/Ubuntu, компілятором g++ і LAMP або хоча б MySQL.

Примітка. Цей урок проводять лише після успішного опанування матеріалом уроків щодо вивчення SQL і виконання усіх його практичних завдань. Операційна система може бути іншою, але тоді вказівки для компіляції і виконання програми будуть відрізнятися від тих, які подано нижче.

Структура уроку

  1. Організаційний момент.
  2. Актуалізація опорних знань.
  3. Вивчення нового матеріалу.
  4. Інструктаж з ТБ.
  5. Закріплення вивченого матеріалу.
  6. Підбиття підсумків уроку.
  7. Домашнє завдання.

Хід уроку

1. Організаційний момент
Вітання з класом. Перевірка присутності і готовності учнів до уроку.

2. Актуалізація опорних знань
Дати відповідь на питання і порівняти з очікуваним.

  1. Що таке SQL?
  2. Яка ознака кінця вказівки SQL?
  3. Які вказівки SQL створюють базу даних, таблицю, запит?
  4. Що таке агрегатна функція SQL? Назвати приклади
  5. Що таке внутрішнє і зовнішнє об'єднання?
  6. Що таке MySQL?
  7. Що таке XAMPP?
  8. Що таке LAMP?
  9. Що таке MariaDB?

3. Вивчення нового матеріалу

Під'єднання бази даних і здійснення запиту мовою SQL мовою C++ здійснюють за допомогою драйвера, бібліотеку з яким встановлюють з сайту dev.mysql.com. Є настанови розробнику (англіською мовою) разом з прикладами 1 i 2.

Щонайменше при ОС Linux Mint 19 потрібно додатково встановити пакунок libmysqlcppconn-dev. Це можна здійснити такими вказівками Терміналу:

sudo apt-get update
sudo apt-get install libmysqlcppconn-dev

 Створення програми мовою С++ з використанням MySQL Connector/C++ з динамічно підвантажуваними бібліотеками при ОС Linux Mint/Ubuntu можна здійснити такою вказівкою терміналу:

g++ -Wall -I/usr/include/cppconn demo.cpp -L/usr/lib -lmysqlcppconn -o demo

 У цьому випадку буде використано код з файлу demo.cpp і створено виконуваний файл demo, вказаний наприкінці вказівки. Програму можна виконати за допомогою іншої вказівки:

./demo

 Розглянемо випадок, коли попередньо виконано такі вказівки MySQL.

SET collation_connection = 'utf8_unicode_ci';
CREATE DATABASE school;
ALTER DATABASE school  CHARACTER SET utf8 COLLATE utf8_unicode_ci;
USE school;
SOURCE structure.sql;
SOURCE data.sql;

Примітка. Дія всіх вказівок з CHARACTER SET utf8 COLLATE utf8_unicode_ci спрямована на уникнення винятків, породжених конфліктами різних схем упорядкування й порівняння.

У цьому випадку програма мовою C++ може мати такий вигляд

#include <stdlib.h>
#include <iostream>
#include "mysql_connection.h"
#include <cppconn/driver.h>
#include <cppconn/exception.h>
#include <cppconn/resultset.h>
#include <cppconn/statement.h>
#include <cppconn/prepared_statement.h>

using namespace std;

int main(void)
{ try
  { sql::Driver *driver;
    sql::Connection *con;
    sql::Statement *stmt;
    sql::ResultSet *res;
    sql::PreparedStatement *pstmt;

    // Сполучення з сервером
    driver = get_driver_instance();
    con = driver->connect("tcp://127.0.0.1:3306", // 127.0.0.1 - адреса сервера
                                        // 3306 — номер порту;
                          "root",       // обліковий запис користувача
                          "!@#123Qwe"   // пароль
                         );

     // Сполучення з базою даних school
    con->setSchema("school");

    stmt = con->createStatement();
    stmt->execute("DROP TABLE IF EXISTS test");
    stmt->execute("CREATE TABLE test(i INT, i2 INT)");
    delete stmt;

    pstmt = con->prepareStatement("INSERT INTO test(i,i2) VALUES (?,?)");
    for (int i = 1; i < 4; i++)
    { pstmt->setInt(1, i);
      pstmt->setInt(2, i*i);
      pstmt->executeUpdate();
    }
    delete pstmt;

    pstmt = con->prepareStatement("SELECT * FROM test;");
    res   = pstmt->executeQuery();
    while (res->next()) cout << res->getString(1) << " " << res->getString(2) << endl;
    delete res;
    delete pstmt;

    pstmt = con->prepareStatement("SELECT * FROM pupils WHERE form = '11Б';");
    res   = pstmt->executeQuery();
    while (res->next())
    cout << res->getString(1) << " " << res->getString(2) << " "
         << res->getString(3) << " " << res->getString(4) << endl;
    delete res;
    delete pstmt;
    delete con; 
  }
  catch (sql::SQLException &e)
  { cout << "Помилка: виняток SQL у " << __FILE__ << "("
      << __FUNCTION__ << ") у рядку "  << __LINE__ << endl;
    cout << "Помилка: " << e.what() << " (MySQL error code: " 
      << e.getErrorCode() << ", SQLState: " << e.getSQLState() << " )" << endl;
  }
  return 0;
}

з таким виведенням у консоль.

1 1
2 4
3 9
ЛИСЕНКО ДМИТРО 11Б 113
РУДЕНКО ОЛЕГ 11Б 114
САВЧЕНКО ВАЛЕРІЙ 11Б 115
ПЕТРЕНКО ЛЕОНІД 11Б 116
ШУЛЬГА ГАЛИНА 11Б 117
РАДЧЕНКО ТЕТЯНА 11Б 118
КОРОЛЬ НАДІЯ 11Б 119
ОСТАПЕНКО ЛЮДМИЛА 11Б 120

Підготовлений (параметризований) запит

sql::PreparedStatement *pstmt;

 використано у поданому вище коді для попередньої компіляції коду SQL, відокремленого від даних.

Переваги підготовлених запитів:

Впровадження SQL-коду (англійською SQL injection) — поширений спосіб злому сайтів і програм, що працюють з базами даних, заснований на впровадженні у запит довільного SQL-коду.

Впровадження SQL може дати можливість зловмиснику виконати довільний запит до бази даних (наприклад, прочитати вміст будь-яких таблиць, видалити, змінити або додати дані), отримати можливість читання та/або запису локальних файлів та виконання довільних вказівок на атакованому сервері.

Підготовлений запит слугує попередньо скомпілюваним шаблоном, у який підставляють значення під час кожного виконання вказівок INSERT, SELECT або UPDATE.

Послідовність використання підготовленого запиту

  1. Підготовка: програма створює шаблон запиту та надсилає його до СУБД. Деякі значення залишають невказаними, що позначають як «?».

  2. Компіляція: СУБД компілює (аналізує, оптимізує та транслює) шаблон запиту та зберігає результат, не виконуючи його.

  3. Виконання: програма надає (прив'язує) значення для параметрів шаблону, а СУБД виконує вказівку (можливо, повертаючи результат). Програма може запросити у СУБД багаторазове виконання оператора з різними значеннями.

Альтернативою підготовленому запиту є виклик SQL безпосередньо з вихідного коду запитом:

sql::Statement *stmt;

 у якому поєднано код та дані.

У C++ є декілька ключових слів для роботи з винятками (після тире вказано, для позначення чого використовують відповідне слово):

4. Інструктаж з ТБ
5. Закріплення вивченого матеріалу

На початку виконання завдань середовище MySQL/MariaDB повинне бути активним і не містити бази даних school. При виконанні завдань використати демонстраційні розв'язання a, b, c завдань уроку 1 щодо SQL.

Завдання 1. Створити програму мовою C++, яка засобами SQL:

Порівняти з очікуваними виведенням і кодом.

Примітка. Вимога не використовувати форматоване виведення за допомогою вказівки printf викликано такими обставинами:

Завдання 2. Використовуючи зчитування з файлу попе­редньо апробо­ваного коду SQL, створити програму мовою C++ для виведення у консоль у вигляді асоціативних масивів таких даних:

Незаповнені дані (порожні поля) подавати словом nullекс. Виконати запити й порівняти з очікуваними виведенням і кодом мовою C++.

Примітка. На момент упорядкування тексту упорядник не знашов опису отримання мета-даних результатів запиту, як це втілено для мови Java. Тому у демонстраційному розв'язанні використано виведеня елементів рядка результатів до його завершення з опрацюванням виключення при виході за його межі. Цей піхід використано і в наступних демонстраційних розв'язаннях.

Завдання 3. Використовуючи попередньо апробований код SQL, створити програму мовою C++ для такого:

Виконати запити й порівняти з очікуваними виведенням і кодом.

Завдання 4. Використовуючи попередньо апробований код SQL, створити програму мовою C++ для такого:

Виконати запити й порівняти з очікуваними виведенням і кодом.

6. Підбиття підсумків уроку

Дати відповідь на питання і порівняти з очікуваним.

  1. Як під'єднати базу даних мовою C++?
  2. Як здійснити запит мовою SQL з програми мовою C++?
  3. Яка структура результату запиту мовою SQL з програми мовою C++?
  4. Як вивести результат запиту мовою SQL з програми мовою C++?

Виставлення оцінок.

7. Домашнє завдання
У разі потреби доробити завдання. Вивчити систаксис під'єднання бази даних і здійснення запиту мовою SQL з програми мовою C++. Написати програму мовою C++, яка зчитуватиме з клавіатури й виконуватиме запити SQL до тих пір, поки не буде введено порожній рядок.

Текст упорядкував Олександр Рудик.