Savladaj CRUD operacije nad MySQL tabelama — pravi CRUD boy upravo nastaje

Put ka boljoj Javi2110 reči7 min

Sad kad imamo bazu, u njoj možemo vršiti CRUD (kreiranje, čitanje, ažuriranje, brisanje) operacije nad tabelama — što znači da pravi CRUD Boy upravo nastaje (😁).

Kreiranje tabele

Pošto nema tabela, hajde da kreiramo jednu. Sintaksa za kreiranje tabele je:

create table ime_tabele(
    ime_kolone1 tip_podataka1,
    ime_kolone2 tip_podataka2,
    ...
    ime_kolonen tip_podatakan
);

Na primer, kreirajmo tabelu članaka koja sadrži naslov, sadržaj, autora, vreme objave i broj pregleda članka — to možemo uraditi ovako:

create table article(
    id int primary key auto_increment,
    title varchar(100) not null,
    content text not null,
    author varchar(20) not null,
    create_time datetime not null,
    read_count int default 0
);

article je ime tabele;
id je primarni ključ, tipa int, auto-inkrementalno;
title je naslov, tipa varchar, dužine 100, ne dozvoljava null;
content je sadržaj, tipa text, ne dozvoljava null;
author je autor, tipa varchar, dužine 20, ne dozvoljava null;
create_time je vreme objave, tipa datetime, ne dozvoljava null;
read_count je broj pregleda, tipa int, podrazumevana vrednost 0.

Nakon izvršavanja gornje naredbe, komandom show tables; možemo videti sve tabele u trenutnoj bazi — vidimo da je kreirana tabela article.

Naravno, u stvarnom radu tabele obično ne kreiramo direktno SQL naredbama, već putem alata kao što su Navicat, DataGrip i sl. — desni klik na bazu → New Table/Create Table, pa se kolone, tipovi i indeksi definišu kroz grafički obrazac, a alat u pozadini generiše odgovarajući CREATE TABLE iskaz.

Kao i alata za modelovanje poput PowerDesigner i chiner. Više o upotrebi ovih alata pogledajte u:

Jedan alat za dizajn baza otvorenog koda

Dodavanje komentara tabeli

Prilikom kreiranja tabele možemo joj dodati komentar. Sintaksa je:

create table ime_tabele(
    ime_kolone1 tip_podataka1 comment 'komentar1',
    ime_kolone2 tip_podataka2 comment 'komentar2',
    ...
    ime_kolonen tip_podatakan comment 'kommentarn'
) comment 'komentar tabele';

Na taj način u kasnijem održavanju lakše razumemo značenje tabele.

Hajde kroz jednostavan primer — na prethodnu verziju dodali smo komentare za neka polja i komentar tabele:

create table article(
    id int primary key auto_increment comment 'primarni ključ',
    title varchar(100) not null comment 'naslov',
    content text not null comment 'sadržaj',
    author varchar(20) not null comment 'autor',
    create_time datetime not null comment 'vreme objave',
    read_count int default 0 comment 'broj pregleda'
) comment 'tabela članaka';

Ako tabela već postoji

Pošto je tabela article već kreirana, ponovno izvršavanje gornje naredbe prijaviće grešku Table 'article' already exists:

ERROR 1050 (42S01): Table 'article' already exists

Da bismo izbegli ovakvu situaciju, pri kreiranju tabele možemo prvo proveriti da li ona postoji i kreirati je samo ako ne postoji. Sintaksa je:

create table if not exists ime_tabele(
    ime_kolone1 tip_podataka1,
    ime_kolone2 tip_podataka2,
    ...
    ime_kolonen tip_podatakan
);

Konkretan primer:

create table if not exists article(
    id int primary key auto_increment comment 'primarni ključ',
    title varchar(100) not null comment 'naslov',
    content text not null comment 'sadržaj',
    author varchar(20) not null comment 'autor',
    create_time datetime not null comment 'vreme objave',
    read_count int default 0 comment 'broj pregleda'
) comment 'tabela članaka';

Brisanje tabele

Sintaksa za brisanje tabele je:

drop table ime_tabele;

Isto tako, pri brisanju tabele treba malo da trepnete 😂 i proverite da li vas možda neko zao ne kontroliše — u suprotnom opet sledi tragedija „obrišite bazu i beži":

mysql> drop table article;
Query OK, 0 rows affected (0.03 sec)

Isto tako, pri brisanju tabele možemo dodati if exists. Sintaksa je:

drop table if exists ime_tabele;

Tako sprečavamo grešku kada tabela ne postoji.

Pregled strukture tabele

Ponekad želimo da vidimo kako izgleda struktura tabele, odnosno koje kolone i osobine kolona sadrži naredba create table. Tada možemo koristiti sledeće komande:

desc ime_tabele;
describe ime_tabele;
explain ime_tabele;
show columns from ime_tabele;
show fields from ime_tabele;

Rezultat je svuda isti — prema sopstvenom ukusu zapamtite bilo koju od njih:

mysql> desc article;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int          | NO   | PRI | NULL    | auto_increment |
| title       | varchar(100) | NO   |     | NULL    |                |
| content     | text         | NO   |     | NULL    |                |
| author      | varchar(20)  | NO   |     | NULL    |                |
| create_time | datetime     | NO   |     | NULL    |                |
| read_count  | int          | YES  |     | 0       |                |
+-------------+--------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

Postoji i komanda show create table ime_tabele;, kojom se prikazuje naredba koja je kreirala tabelu:

mysql> show create table article\G
*************************** 1. row ***************************
       Table: article
Create Table: CREATE TABLE `article` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT 'primarni ključ',
  `title` varchar(100) NOT NULL COMMENT 'naslov',
  `content` text NOT NULL COMMENT 'sadržaj',
  `author` varchar(20) NOT NULL COMMENT 'autor',
  `create_time` datetime NOT NULL COMMENT 'vreme objave',
  `read_count` int DEFAULT '0' COMMENT 'broj pregleda',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='tabela članaka'
1 row in set (0.00 sec)

Što se tiče tipova podataka kolona — poput int, varchar, datetime itd. — o njima će biti reči u narednim poglavljima.

Ako komandom use ime_baze; nismo naveli bazu nad kojom radimo, strukturu tabele možemo videti i putem show columns from ime_baze.ime_tabele;, na primer:

show create table javasi_dev.article;

Informacije dobijene završetkom sa ; prikazuju se u neurednom formatu, ali se pomoću \G izlaz može formatirati, na primer:

show create table javasi_dev.article \G;

Izlaz sa \G je pregledan — svako polje u svom redu, pa se i dugački CREATE TABLE iskazi lako čitaju (kao u gornjem primeru).

Izmena tabele

U pravilu, pre kreiranja tabele treba dobro osmisliti dizajn i po mogućstvu dodati nekoliko redundantnih kolona koje pokrivaju buduće promene zahteva — tada su izmene u programu najmanje, a šanse za bagove manje.

Naime, svaka izmena strukture tabele povlači izmene u odgovarajućim SQL naredbama, logici programskog koda i test slučajevima, pa lako dolazi do propusta koji izazivaju neočekivane bagove.

Zato operacije izmene tabele moraju biti pažljive.

S druge strane, tabelu ne treba ni prekomerno projektovati, jer prekomerni dizajn čini strukturu tabele previše složenom, povećava troškove održavanja i otežava kasnije proširenja.

Dakle, izmena tabele je sama po sebi veština koju postepeno usvajate kroz stvarni rad.

Dodavanje kolone

U praksi je dodavanje kolone najčešća operacija pri izmeni tabele. Sintaksa je:

alter table ime_tabele add ime_kolone tip_podataka;

Na primer, ako tabeli article želimo da dodamo kolonu update_time tipa datetime, napisaćemo:

alter table article add update_time datetime;

mysql> alter table article add update_time datetime;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

Pozicija nove kolone

U gornjem primeru dodali smo tabeli article kolonu update_time, ali je ona dodata na kraj. Ako je želimo dodati ispred neke druge kolone, napisaćemo:

alter table article add update_time datetime after create_time;

mysql> desc article;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int          | NO   | PRI | NULL    | auto_increment |
| title       | varchar(100) | NO   |     | NULL    |                |
| content     | text         | NO   |     | NULL    |                |
| author      | varchar(20)  | NO   |     | NULL    |                |
| create_time | datetime     | NO   |     | NULL    |                |
| update_time | datetime     | YES  |     | NULL    |                |  ← nova kolona
| read_count  | int          | YES  |     | 0       |                |
+-------------+--------------+------+-----+---------+----------------+

Brisanje kolone

Sintaksa za brisanje kolone je:

alter table ime_tabele drop ime_kolone;

Na primer, ako iz tabele article želimo da obrišemo kolonu update_time, napisaćemo:

alter table article drop update_time;

Izmena kolone

Izmene kolona se zapravo događaju vrlo retko — dizajn se, naime, obično radi promišljeno. Ipak, ponekad iz nekog razloga treba izmeniti kolonu, na primer njen tip ili dužinu.

Sintaksa za izmenu kolone je:

alter table ime_tabele modify ime_kolone tip_podataka;

Na primer, ako u tabeli article želimo da izmenimo kolonu title tako da joj tip iz varchar(100) postane varchar(200), napisaćemo:

alter table article modify title varchar(200);

Treba imati na umu da, ako u tabeli već postoje podaci, pri izmeni tabele treba biti posebno obazriv. Na primer, ako je prvobitna dužina kolone title bila 100, a sada se postavi na 10, stari podaci više neće stati i prijaviće se greška.

To ćemo demonstrirati kada budemo obrađivali ubacivanje podataka.

Izmena imena kolone

Ponekad, pri dizajnu kolona, napravimo grešku i koloni damo loše ime — tada je potrebno promeniti ime kolone. Sintaksa je:

alter table ime_tabele change staro_ime_kolone novo_ime_kolone tip_podataka;

Na primer, ako u tabeli article ime kolone title želimo da promenimo u article_title, napisaćemo:

alter table article change title article_title varchar(100);

mysql> alter table article change title article_title varchar(100);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

Izmena imena tabele

Isto tako, kada je potrebno promeniti ime tabele, možemo uraditi sledeće:

alter table staro_ime_tabele rename novo_ime_tabele;

Na primer, ako ime tabele article želimo da promenimo u article_info, napisaćemo:

alter table article rename article_info;

mysql> alter table article rename article_info;
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
+----------------------+
| Tables_in_javasi_dev |
+----------------------+
| article_info         |  ← preimenovana tabela
+----------------------+

Dizajn tabela u projektu Jishupai

Dizajn baze i tabela projekta Jishupai obradili smo u okviru uputstava za taj projekat — pogledajte Dizajn baze i tabela projekta paicoding.

Kod dizajna cele baze i tabela prvo treba razjasniti biznis i zahteve, a tek zatim pristupiti dizajnu — samo tako on može biti smislen i efikasan.

Kako Jishupai inicijalizuje tabele?

Inicijalizacija tabela u projektu Jishupai realizovana je pomoću Liquibase-a. Sam postupak obradili smo u uputstvima za taj projekat — tabele se opisuju kroz changelog fajlove (XML/YAML/SQL), a Liquibase pri pokretanju aplikacije proverava evidenciju izmena (databasechangelog tabela) i izvršava samo one skupove izmena (changeset) koji još nisu primenjeni.

Liquibase pruža strukturiran i sistematičan pristup upravljanju izmenama šeme baze. Pomoću evidencija izmena (changelog) i skupova izmena (changeset) kontroliše promene stanja baze, čime se obezbeđuje sledljivost, ponovljivost i doslednost izmena šeme baze, a podržava i saradnju između timova. Pogodan je za okruženja kontinuirane integracije i kontinuiranog isporučivanja.

Ovde ću vam ostaviti dva mala domaća zadatka:

Prvi: bez upotrebe Liquibase-a realizujte inicijalizaciju tabela.
Drugi: pomoću Liquibase-a realizujte inicijalizaciju tabela.

Ovde ću prikazati samo prvi pristup; za drugi možete pogledati uputstva za projekat Jishupai.

@Slf4j
public class ForumDataSourceInitializerTest extends BasicTest {
    @Value("classpath:liquibase/data/init_schema_221209.sql")
    private Resource schemaSql;
    @Value("classpath:liquibase/data/init_data_221209.sql")
    private Resource initData;

    @Test
    public void dataSourceInitializer() throws SQLException {
        DataSource dataSource = createCustomDataSource();
        log.info(dataSource.getConnection().getMetaData().getURL());
        final DataSourceInitializer initializer = new DataSourceInitializer();
        // postavljanje izvora podataka
        initializer.setDataSource(dataSource);
        initializer.setEnabled(true);

        final ResourceDatabasePopulator populator = new ResourceDatabasePopulator();
        populator.addScript(schemaSql);
        populator.addScript(initData);
        initializer.setDatabasePopulator(populator);
        initializer.afterPropertiesSet();
    }

    private DataSource createCustomDataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://localhost:3306/javasi_dev");
        dataSource.setUsername("root");
        dataSource.setPassword("123456");
        return dataSource;
    }
}

Ukratko ćemo objasniti delove koda koji su vam možda manje poznati:

① Pomoću anotacije @Value datoteke init_schema_221209.sql i init_data_221209.sql učitavaju se u Resource objekat.

@Value("classpath:liquibase/data/init_schema_221209.sql")
private Resource schemaSql;
@Value("classpath:liquibase/data/init_data_221209.sql")
private Resource initData;

schema.sql je datoteka strukture tabele, a data.sql datoteka sa podacima tabele.

② Metod createCustomDataSource služi za kreiranje izvora podataka; ovde se koristi DriverManagerDataSource koji dolazi uz Spring, a mogu se koristiti i DruidDataSource, HikariDataSource i sl.

private DataSource createCustomDataSource() {
    DriverManagerDataSource dataSource = new DriverManagerDataSource();
    dataSource.setDriverClassName("com.mysql.jdbc.Driver");
    dataSource.setUrl("jdbc:mysql://localhost:3306/javasi_dev");
    dataSource.setUsername("root");
    dataSource.setPassword("123456");
    return dataSource;
}

③ ResourceDatabasePopulator je pomoćna klasa-alatka za popunjavanje baze, koja implementira interfejs DatabasePopulator. Prvenstveno služi za izvršavanje SQL skripti — te datoteke mogu sadržavati SQL komande poput kreiranja tabela, ubacivanja podataka i sl., dakle gore pomenute schema.sql i data.sql.

final ResourceDatabasePopulator populator = new ResourceDatabasePopulator();
populator.addScript(schemaSql);
populator.addScript(initData);

④ DataSourceInitializer služi za automatsku inicijalizaciju baze pri pokretanju aplikacije. Tu obavlja izvršavanjem SQL skripti.

final DataSourceInitializer initializer = new DataSourceInitializer();
// postavljanje izvora podataka
initializer.setDataSource(dataSource);
// da li je inicijalizacija uključena
initializer.setEnabled(true);
// postavljanje pomoćne klase za popunjavanje baze
initializer.setDatabasePopulator(populator);
// izvršavanje inicijalizacije
initializer.afterPropertiesSet();

Nakon izvršavanja te test klase, u konzoli se vide odgovarajuće poruke iz loga:

INFO ... ForumDataSourceInitializerTest : HikariPool-1 - Starting...
INFO ... ForumDataSourceInitializerTest : HikariPool-1 - Start completed.
INFO ... ScriptUtils                 : Executing SQL script from class path resource
                                       [liquibase/data/init_schema_221209.sql]
INFO ... ScriptUtils                 : Executed 42 SQL statements from init_schema_221209.sql
INFO ... ScriptUtils                 : Executing SQL script from class path resource
                                       [liquibase/data/init_data_221209.sql]
INFO ... ScriptUtils                 : Executed 128 SQL statements from init_data_221209.sql

Zatim komandom show tables; vidimo da su u bazi kreirane odgovarajuće tabele:

mysql> show tables;
+------------------------+
| Tables_in_javasi_dev   |
+------------------------+
| article                |
| article_count          |
| comment                |
| user                   |
| user_info              |
...
+------------------------+

Adresa izvornog koda: inicijalizacija tabela u projektu Jishupai

Zaključak

U ovom poglavlju uglavnom smo obradili osnovne operacije nad MySQL tabelama: pregled tabela, kreiranje tabela, brisanje tabela, uvid u strukturu tabele i izmenu tabela.

Uz to, povezano sa praktičnim projektom Jishupai, objasnili smo i kako inicijalizovati tabele u Spring Boot-u.

Nadamo se da ćete sve ovo probati i sami implementirati — samo tako se zaista savladaju CRUD operacije nad tabelama.