Бази данни

Проблем

Каталог във файл

Каталогът на магазина е CSV файл, както в модула за файловете. Докато продуктите са сто, става. После стават сто хиляди и се появяват проблеми:

• за да намериш един продукт, четеш целия файл;
• „всички под 5 €, подредени по цена“ е цикъл, if и сортиране, писани на ръка;
• две каси пишат във файла едновременно и единият запис се губи или файлът се поврежда;
• токът спира по средата на записа и половината файл го няма.

Затова данните на почти всяка истинска програма са в база данни: програма, чиято работа е да пази данни, да ги търси бързо и да не ги губи.

Идеята

Таблици и SQL

Релационната база пази данните в таблици. Таблицата има колони (име, тип) и редове (по един за всеки продукт). Колоната id е първичен ключ: стойността ѝ е различна за всеки ред, затова по нея се намира точно един.

С базата се говори на езика SQL. Петте най-чести команди:

SQL

CREATE TABLE products (
    id    INT PRIMARY KEY,
    name  VARCHAR(50) NOT NULL,
    price DECIMAL(8, 2)
);

INSERT INTO products VALUES (1, 'Кафе', 7.50);
SELECT name, price FROM products WHERE price < 5 ORDER BY price;
UPDATE products SET price = 6.90 WHERE id = 1;
DELETE FROM products WHERE id = 3;

• CREATE TABLE прави таблица, INSERT добавя ред, UPDATE променя, DELETE трие.
• SELECT чете: кои колони, от коя таблица, при какво условие (WHERE) и в какъв ред (ORDER BY).

Ти казваш какво искаш, а базата решава как да го намери бързо.

По-точно, за любопитните

• Известни релационни бази са PostgreSQL, MySQL, Oracle и SQL Server. Те работят като отделни сървъри. Тук ползваме H2: малка база, написана на Java, която може да живее изцяло в паметта на програмата. Удобна е за примери и тестове: всяко пускане започва с празна база.
• Текстът в SQL е в единични кавички: 'Кафе'. DECIMAL(8, 2) е точно число с 2 знака след запетаята, подходящо за пари.

Виж го

JDBC: връзка, заявка, резултат

JDBC е стандартът, с който Java говори с всяка релационна база. Връзката е Connection, заявката се праща през Statement, а резултатът е ResultSet. Методът setup прави таблицата и добавя три продукта:

static void setup(Connection conn) throws SQLException {
    try (Statement st = conn.createStatement()) {
        st.executeUpdate("CREATE TABLE products (id INT PRIMARY KEY, name VARCHAR(50) NOT NULL, price DECIMAL(8, 2))");
        st.executeUpdate("INSERT INTO products VALUES (1, 'Кафе', 7.50), (2, 'Чай', 3.20), (3, 'Шоколад', 2.40)");
    }
}

Четем редовете и правим от всеки обект от record Product(int id, String name, double price):

try (Connection conn = DriverManager.getConnection("jdbc:h2:mem:shop")) {
    setup(conn);
    List<Product> products = new ArrayList<>();
    try (Statement st = conn.createStatement();
         ResultSet rs = st.executeQuery("SELECT id, name, price FROM products ORDER BY price")) {
        while (rs.next()) {
            products.add(new Product(rs.getInt("id"), rs.getString("name"), rs.getDouble("price")));
        }
    }
    for (Product p : products) {
        System.out.println(p);
    }
}

Изход

Product[id=3, name=Шоколад, price=2.4]
Product[id=2, name=Чай, price=3.2]
Product[id=1, name=Кафе, price=7.5]

• DriverManager.getConnection(адрес) отваря връзката. Адресът казва коя база: тук H2 в паметта с име shop.
• executeUpdate е за команди, които променят (CREATE, INSERT, UPDATE, DELETE), executeQuery е за SELECT.
• rs.next() минава на следващия ред и връща false, когато редовете свършат. getInt, getString и getDouble взимат колона по име.
• Връзката, заявката и резултатът се затварят: затова са в try-with-resources.
• Почти всеки метод на JDBC хвърля проверено SQLException. Тук main и setup го обявяват с throws.

try (Connection conn = DriverManager.getConnection("jdbc:h2:mem:shop")) {
    setup(conn);
    List<Product> products = new ArrayList<>();
    try (Statement st = conn.createStatement();
         ResultSet rs = st.executeQuery("SELECT id, name, price FROM products ORDER BY price")) {
        while (rs.next()) {
            products.add(new Product(rs.getInt("id"), rs.getString("name"), rs.getDouble("price")));
        }
    }
    for (Product p : products) {
        System.out.println(p);
    }
}

Стъпка по стъпка, с думи

1. Връзка с базата в паметта. setup прави таблицата products и добавя 3 реда.
2. Празен списък, в който ще влязат обектите.
3. SELECT с ORDER BY price: базата връща редовете като ResultSet, от най-евтиния.
4. rs.next() минава на ред 1. getInt, getString и getDouble взимат колоните, а new Product прави от тях обект: Шоколад.
5. rs.next() минава на ред 2. getInt, getString и getDouble взимат колоните, а new Product прави от тях обект: Чай.
6. rs.next() минава на ред 3. getInt, getString и getDouble взимат колоните, а new Product прави от тях обект: Кафе.
7. rs.next() връща false: редовете свършиха и цикълът спира.
8. Списъкът е от обикновени Java обекти. Печатът показва record-а с полетата му.

По-точно, за любопитните

• С истинска база на сървър адресът е друг и има потребител и парола. Също трябва и драйверът на базата като зависимост в проекта (за MySQL: mysql-connector-j):

String url = "jdbc:mysql://localhost:3306/shop";
try (Connection conn = DriverManager.getConnection(url, "shop_app", "парола")) {
    System.out.println("Връзката с базата е успешна!");
}

• Паролата не се пише в кода, а се чете от настройките или от променлива на средата.

• Цените тук са double, за да е кратко. В истински магазин колоната DECIMAL се чете с rs.getBigDecimal("price"), за да няма грешки от закръгляне.

Пробвай

Кое ще излезе?

Продуктите са Кафе 7,50 €, Чай 3,20 € и Шоколад 2,40 €. Какво ще отпечата програмата?

try (Connection conn = DriverManager.getConnection("jdbc:h2:mem:shop")) {
    setup(conn);
    try (Statement st = conn.createStatement();
         ResultSet rs = st.executeQuery("SELECT name FROM products WHERE price < 5 ORDER BY price DESC")) {
        while (rs.next()) {
            System.out.println(rs.getString("name"));
        }
    }
}

Отговор

Шоколад
Чай

Чай
Шоколад

Кафе
Чай
Шоколад

Провери

Проблем

Търсачката, която показва всичко

Сайтът търси продукт по име, което потребителят пише. Заявката се сглобява с +:

static void search(Connection conn, String name) throws SQLException {
    String sql = "SELECT name, price FROM products WHERE name = '" + name + "'";
    try (Statement st = conn.createStatement();
         ResultSet rs = st.executeQuery(sql)) {
        while (rs.next()) {
            System.out.println("  " + rs.getString("name") + ": " + rs.getDouble("price") + " €");
        }
    }
}

try (Connection conn = DriverManager.getConnection("jdbc:h2:mem:shop")) {
    setup(conn);
    System.out.println("Търся: Чай");
    search(conn, "Чай");
    System.out.println("Търся: x' OR '1'='1");
    search(conn, "x' OR '1'='1");
}

Изход

Търся: Чай
  Чай: 3.2 €
Търся: x' OR '1'='1
  Кафе: 7.5 €
  Чай: 3.2 €
  Шоколад: 2.4 €

С „Чай“ работи. С x' OR '1'='1 заявката става ... WHERE name = 'x' OR '1'='1'. Условието е винаги вярно и излиза цялата таблица. Входът на потребителя е станал част от SQL. Това е SQL injection, една от най-честите атаки срещу сайтове. Със същия трик може да се влезе без парола или да се изтрие таблица.

Идеята

PreparedStatement

Решението е заявката и стойностите да вървят отделно. PreparedStatement има ? на местата на стойностите, а те се подават със setString, setInt, setDouble (номерата са от 1):

static void search(Connection conn, String name) throws SQLException {
    String sql = "SELECT name, price FROM products WHERE name = ?";
    try (PreparedStatement ps = conn.prepareStatement(sql)) {
        ps.setString(1, name);
        try (ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                System.out.println("  " + rs.getString("name") + ": " + rs.getDouble("price") + " €");
            }
        }
    }
}

Изход

Търся: Чай
  Чай: 3.2 €
Търся: x' OR '1'='1

Сега x' OR '1'='1 е просто текст, който се търси като име. Такъв продукт няма и не излиза нищо. Каквото и да напише потребителят, то не може да стане SQL.

Правилото е просто: стойност от потребителя, от файл или от мрежата никога не се долепя в SQL. Винаги ?.

Пробвай

Кафе с апостроф

Методът преименува продукт. С името Кафе 'Арабика' програмата спира с „Syntax error in SQL statement "UPDATE products SET name = 'Кафе '[*]Арабика'' WHERE id = 1"“. На кой ред е грешката?

Избери реда с грешката.

1static void rename(Connection conn, int id, String newName) throws SQLException {2 try (Statement st = conn.createStatement()) {3 st.executeUpdate("UPDATE products SET name = '" + newName + "' WHERE id = " + id);4 }5}

Провери

Идеята

Транзакция: всичко или нищо

Ана превежда 150 € на Борис. Това са две заявки: добави на Борис, извади от Ана. В таблицата има условие CHECK (balance >= 0), затова втората заявка се проваля: Ана има само 100 €.

static void openAccounts(Connection conn) throws SQLException {
    try (Statement st = conn.createStatement()) {
        st.executeUpdate("CREATE TABLE accounts (id INT PRIMARY KEY, owner VARCHAR(50), balance INT CONSTRAINT no_debt CHECK (balance >= 0))");
        st.executeUpdate("INSERT INTO accounts VALUES (1, 'Ана', 100), (2, 'Борис', 50)");
    }
}

static void move(Connection conn, int id, int amount) throws SQLException {
    try (PreparedStatement ps = conn.prepareStatement("UPDATE accounts SET balance = balance + ? WHERE id = ?")) {
        ps.setInt(1, amount);
        ps.setInt(2, id);
        ps.executeUpdate();
    }
}

static void printBalances(Connection conn) throws SQLException {
    try (Statement st = conn.createStatement();
         ResultSet rs = st.executeQuery("SELECT owner, balance FROM accounts ORDER BY id")) {
        while (rs.next()) {
            System.out.println("  " + rs.getString("owner") + ": " + rs.getInt("balance") + " €");
        }
    }
}

static void transfer(Connection conn, int from, int to, int amount) throws SQLException {
    move(conn, to, amount);
    move(conn, from, -amount);
}

try (Connection conn = DriverManager.getConnection("jdbc:h2:mem:bank")) {
    openAccounts(conn);
    try {
        transfer(conn, 1, 2, 150);
    } catch (SQLException e) {
        System.out.println("Преводът на 150 € е отказан.");
    }
    printBalances(conn);
}

Изход

Преводът на 150 € е отказан.
  Ана: 100 €
  Борис: 200 €

Преводът е отказан, но Борис вече е получил 150 € от нищото. По подразбиране всяка заявка се записва веднага. Двете заявки трябва да са транзакция: или минават и двете, или нито една.

static void transfer(Connection conn, int from, int to, int amount) throws SQLException {
    conn.setAutoCommit(false);
    try {
        move(conn, to, amount);
        move(conn, from, -amount);
        conn.commit();
    } catch (SQLException e) {
        conn.rollback();
        throw e;
    } finally {
        conn.setAutoCommit(true);
    }
}

Изход

Преводът на 150 € е отказан.
  Ана: 100 €
  Борис: 50 €

• setAutoCommit(false) започва транзакция: промените още не са окончателни.
• commit() ги записва всички наведнъж.
• rollback() ги отменя всички, ако нещо се провали. Изключението продължава нагоре, за да разбере и викащият.
• finally връща връзката в обикновения режим.

По-точно, за любопитните

• Съобщението на H2 при провалената заявка е Check constraint violation: "NO_DEBT: ". NO_DEBT е името на условието: в таблицата то е balance INT CONSTRAINT no_debt CHECK (balance >= 0).

• Базите обещават четири неща за транзакциите, известни като ACID: всичко или нищо (atomicity), данните остават верни според правилата на таблиците (consistency), едновременните транзакции не си пречат (isolation) и записаното не се губи дори при спрял ток (durability).

Идеята

ORM: обекти вместо редове

С JDBC за всеки клас пишеш SQL и превръщаш редовете в обекти на ръка. ORM (object-relational mapping) прави това вместо теб: описваш класа с анотации, а библиотеката сама прави таблицата, заявките и обектите. Стандартът в Java е JPA, а най-известната библиотека, която го изпълнява, е Hibernate.

Product.java

@Entity
@Table(name = "products")
class Product {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(nullable = false, length = 50)
    private String name;

    private double price;

    protected Product() {
    }

    public Product(String name, double price) {
        this.name = name;
        this.price = price;
    }

    public String getName() {
        return name;
    }

    public double getPrice() {
        return price;
    }
}

• @Entity казва „този клас е таблица“, @Table дава името ѝ.
• @Id е първичният ключ, а @GeneratedValue оставя базата да му дава стойност.
• @Column задава подробности за колоната. Полетата без анотация също стават колони.
• Празният конструктор е задължителен: през него Hibernate прави обекта, преди да попълни полетата.

Записът и четенето минават през EntityManager:

static void jpa() {
    EntityManagerFactory factory = Persistence.createEntityManagerFactory("shop");
    EntityManager em = factory.createEntityManager();

    em.getTransaction().begin();
    em.persist(new Product("Кафе", 7.50));
    em.persist(new Product("Чай", 3.20));
    em.getTransaction().commit();

    List<Product> cheap = em.createQuery(
            "SELECT p FROM Product p WHERE p.price < :max ORDER BY p.price", Product.class)
        .setParameter("max", 5.0)
        .getResultList();
    for (Product p : cheap) {
        System.out.println(p.getName() + ": " + p.getPrice() + " €");
    }

    em.close();
    factory.close();
}

persist записва обекта, и то в транзакция, както в JDBC. Заявката е на JPQL: прилича на SQL, но говори за класа Product и полето му price, не за таблицата. Резултатът е списък от готови обекти. Кой е драйверът и адресът, пише в META-INF/persistence.xml:

persistence.xml

<persistence xmlns="http://xmlns.jcp.org/xml/ns/persistence" version="2.2">
    <persistence-unit name="shop">
        <class>Product</class>
        <properties>
            <property name="javax.persistence.jdbc.url" value="jdbc:h2:mem:shop"/>
            <property name="hibernate.hbm2ddl.auto" value="create"/>
        </properties>
    </persistence-unit>
</persistence>

Тук на сайта този код само се компилира: за да работи, трябват и Hibernate с всички негови библиотеки, и конфигурация.

По-точно, за любопитните

• Със собствения интерфейс на Hibernate същото е през Session, а заявките са на HQL (езикът на Hibernate, от който е тръгнал JPQL):

static void hibernate() {
    SessionFactory factory = new Configuration().configure().buildSessionFactory();
    try (Session session = factory.openSession()) {
        Transaction tx = session.beginTransaction();
        session.save(new Product("Шоколад", 2.40));
        tx.commit();

        List<Product> cheap = session.createQuery("from Product where price < :max", Product.class)
            .setParameter("max", 5.0)
            .list();
        System.out.println("Евтини: " + cheap.size());
    }
    factory.close();
}

• Заявка може да се построи и с код, без текст: Criteria API. Удобно е, когато условията се сглобяват според избора на потребителя:

static List<Product> named(EntityManager em, String name) {
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Product> query = cb.createQuery(Product.class);
    Root<Product> p = query.from(Product.class);
    query.select(p).where(cb.equal(p.get("name"), name));
    return em.createQuery(query).getResultList();
}

• Транзакцията с EntityManager и отмяна при грешка:

EntityTransaction tx = em.getTransaction();
try {
    tx.begin();
    // промени по ентитетите
    tx.commit();
} catch (RuntimeException e) {
    if (tx.isActive()) {
        tx.rollback();
    }
    throw e;
} finally {
    em.close();
}

• Hibernate 5 и JPA 2.2 са с пакета javax.persistence. От JPA 3 (Hibernate 6) пакетът е jakarta.persistence, а останалото е почти същото.

Упражнение

Магазин с база

Реши задачите в редактора си, с H2 в проекта (com.h2database:h2) и адрес jdbc:h2:mem:....1. Каталог. Направи таблица products и методи add, setPrice, remove и list, всички с PreparedStatement освен list. Добави Кафе (7,50 €), Чай (3,20 €) и Шоколад (2,40 €), отпечатай, смени цената на кафето на 6,90 €, изтрий шоколада и отпечатай пак:

Изход

1. Кафе: 7.5 €
2. Чай: 3.2 €
3. Шоколад: 2.4 €
---
1. Кафе: 6.9 €
2. Чай: 3.2 €

2. Търсене по част от името. Продуктите са Кафе, Какао, Чай и Шоколад. Метод find(conn, text) намира всички, чието име съдържа текста, без значение от малки и главни букви: WHERE LOWER(name) LIKE ? със стойност "%" + text.toLowerCase() + "%". Пробвай „КА“, „чай“ и опит за injection:

Изход

Търся „КА“:
  Какао: 4.8 €
  Кафе: 7.5 €
Търся „чай“:
  Чай: 3.2 €
Търся „' OR '1'='1“:
  няма такива продукти

3. Превод, който се проваля наполовина. Сметките са Ана (100 €) и Борис (50 €). transfer(conn, from, to, amount) първо вади от from, после добавя на to. Ако executeUpdate върне 0 (няма такава сметка), хвърли SQLException("Няма сметка " + id). Всичко е в транзакция. Преведи 30 € от 1 към 2, после 40 € от 1 към несъществуващата 99:

Изход

Преведени 30 € от 1 към 2
Отказан: Няма сметка 99
  Ана: 70 €
  Борис: 80 €

Вторият превод е извадил 40 € от Ана, преди да се провали, но rollback ги е върнал.

Тест

Провери се

1. Какво връща executeQuery?

Броя на променените редовеResultSet: редовете от резултата, които обхождаш с next()Списък от обекти Product

2. Защо "... WHERE name = '" + name + "'" е опасно?

Защото е бавноНе е опасно, ако name е StringЗащото текстът от потребителя става част от SQL и може да промени заявката

3. Преводът е две заявки. Първата минава, втората се проваля. Какво прави rollback()?

Отменя само вторатаПовтаря втората заявкаОтменя всички промени от началото на транзакцията, и първата

4. Какво прави ORM като Hibernate?

Превръща обекти в редове и обратно, без да пишеш SQL за всяко нещоСамо ускорява SQL заявкитеЗаменя базата данни

Провери

Започни урока отначало