Szkolenia programowania we Wrocławiu
Kurs Java Podstawy - rozszerzony

Bazy danych SQLite w Javie

Wprowadzenie

1. Co to jest baza danych

2. Podstawy SQL

3. SQLite JDBC w Javie

4. Przykład - Biblioteka

5. Praktyczne porady

 

 

Wprowadzenie

Pisząc coraz to bardziej złożone aplikacje często problemem staje się to, że nasze dane chcielibyśmy przechowywać w nieco bardziej elastyczny sposób, niż tylko w pamięci (czyli do zamknięcia aplikacji), lub jako pliki tekstowe na dysku twardym.  Drugie rozwiązanie o ile w niektórych sytuacjach jest jak najbardziej w porządku, tak w przypadku przechowywania wielu danych powiązanych ze sobą informacji staje się po prostu niepraktyczne.

W celu rozwiązania tego problemu stosuje się bazy danych, które pozwalają w uporządkowany sposób zarządzać wszystkimi informacjami, które są wynikiem jakichś działań, czy akcji użytkownika. W ostatnich latach zdecydowanie najpopularniejsze są relacyjne bazy danych, ponieważ o ile są dobrze zaprojektowane to pozwalają uniknąć redundancji (powtarzania się) tych samych danych oraz w łatwy sposób pozwalają tworzyć relacje (połączenia) pomiędzy różnymi danymi. W tej lekcji dowiesz się jak wykorzystać bazę SQLite do przechowywania danych. Niemal dokładnie tak samo możesz posługiwać się inną relacyjną bazą danych jak np MySQL (jedynie zmieniając sterownik i bibliotekę). SQLite posiada tę zaletę, że do jej działania nie musisz instalować serwera, co w przypadku MySQL nie jest już takie proste. SQLite jest również na licencji Public Domain co oznacza, że możesz ją wykorzystywać za darmo nawet w komercyjnych aplikacjach.

1. Co to jest baza danych

Baza danych to dane (w przypadku bazy relacyjnej reprezentowane w postaci tabel) oraz relacje między nimi. Możesz dzięki niej stworzyć miejsce do przechowywania informacji na temat pewnego wycinka rzeczywistości, którym będzie np fabryka, biblioteka, sklep itd.

Każda tabela powinna zawierać tzw. klucz główny (czyli identyfikator), który jednoznacznie identyfikuje konkretny rekord z tej tabeli. Klucz główny może składać się z kilku elementów. Najprostsza tabela przechowująca informacje o imieniu i nazwisku osób będzie więc wyglądać w ten sposób:

przykładowa tabela w sqlite

Najprostsza baza danych może składać się nawet z jednej tabeli, choć w takiej sytuacji dobrze jest się zastanowić, czy na pewno przyniesie nam to jakąś korzyść.

Na potrzeby lekcji musimy jednak nieco skomplikować sobie życie i zobrazować działanie bazy SQLite na czymś troszkę bardziej złożonym - najlepiej będzie to pokazać na nudnym i oklepanym przykładzie biblioteki.

Potrzebujemy w najprostszej formie przechowywać informacje o:

  • Czytelnikach
  • Książkach
  • Wypożyczeniach (jaki czytelnik wypożyczył jaką książkę)

Schemat naszej bazy danych będzie więc wyglądał w uproszczonej formie tak:

baza danych biblioteka w javie

Widzimy tutaj trzy tabele o których przed chwilą mówiliśmy. Ważne jest to, żeby rekordy były unikalne - u nas tę unikalność zapewniają różne id. W przypadku czytelnika mógłby to być PESEL (ale co wtedy z osobami, które np nie są z Polski itp.). Nie jest to lekcja poświęcona projektowaniu baz danych, więc nie będziemy się zgłębiać w tę tematykę - z pewnością znajdziecie w sieci mnóstwo informacji na ten temat.

Najciekawsza z naszych tabel jest tabela wypozyczenia - jak widać nie przechowuje ona informacji o czytelniku, ani o książce, posiada jedynie identyfikatory dzięki którym można się z niej odwołać do rekordów w tabelach czytelnicy i ksiazki.

2. Podstawy SQL

Artykuł ten ma na celu pokazać wykorzystanie relacyjnej bazy SQLite w Javie, nie będzie więc w tym miejscu rozwijany poradnik dotyczący SQL. Czytelnik z pewnością poradzi sobie z odnalezieniem potrzebnych informacji w internecie. Wykorzystywane przez nas zapytania to:

  • CREATE TABLE - do utworzenia odpowiednich tabel
  • INSERT INTO - do umieszczania danych w tabelach
  • SELECT - do "wyciągania" odpowiednich danych z bazy danych
  • DELETE - do usuwania rekordów z bazy danych

3. SQLite JDBC w Javie - Konfiguracja

Aby móc posługiwać się bazą SQLite w Javie potrzebujesz tak naprawdę tylko jednej rzeczy - biblioteki ze sterownikiem konkretnej bazy danych. Osobiście wykorzystuję JDBC Xerial, ponieważ jest stosunkowo szybka i po prostu niezawodna (choć już trochę stara).

Pobierz najnowszą wersję sterownika z poniższej strony (wersja wykorzystana w lekcji to 3.7.2):

http://www.xerial.org/maven/repository/artifact/org/xerial/sqlite-jdbc/

 

Jeśli ktoś korzysta z Mavena, instrukcję można znaleźć tutaj.

Utwórz nowy projekt w eclipse, kliknij na niego prawym przyciskiem, wybierz Properties i przejdź do zakładki Java Build Path. Następnie w zakładce Libraries wybierz opcję Add External Jar i znajdź przed chwilą ściągnięty sterownik. W moim przypadku jest to "sqlite-jdbc-3.7.2.jar"

Warto z powyższej strony dociągnąć sobie również źródła, dzięki czemu eclipse będzie nam w stanie podpowiadać działanie poszczególnych metod, czy znaczenie stałych. W tym celu rozwiń dodaną przed chwilą bibliotekę, wybierz Source attachment, kliknij Edit po prawej stronie i wskaż na plik ze źródłem  - w moim przypadku "sqlite-jdbc-3.7.2-sources.jar".

jdbc sqlite w eclipse

 

4. SQLite w akcji - przykład "Biblioteka"

Pierwsze co musimy utworzyć to klasy "modelu". Przez model rozumiemy klasy, które mogą odwzorować elementy przechowywane w naszej bazie danych. Potrzebujemy więc klas: Czytelnik, Ksiazka, Wypozyczenie - dzięki temu będziemy mogli albo pobrać wszystkie rekordy z bazy danych przy starcie programu, lub robić to "w locie" bez potrzeby zastanawiania się "co przed chwilą wyciągnąłem z bazy danych?".

Ksiazka.java

package model;

public class Ksiazka {
    private int id;
    private String tytul;
    private String autor;

    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getTytul() {
        return tytul;
    }
    public void setTytul(String tytul) {
        this.tytul = tytul;
    }
    public String getAutor() {
        return autor;
    }
    public void setAutor(String autor) {
        this.autor = autor;
    }

    public Ksiazka() {}
    public Ksiazka(int id, String tytul, String autor) {
        this.id = id;
        this.tytul = tytul;
        this.autor = autor;
    }

    @Override
    public String toString() {
        return "["+id+"] - "+tytul+" - "+autor;
    }
}

Czytelnik.java

package model;

public class Czytelnik {
    private int id;
    private String imie;
    private String nazwisko;
    private String pesel;

    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getImie() {
        return imie;
    }
    public void setImie(String imie) {
        this.imie = imie;
    }
    public String getNazwisko() {
        return nazwisko;
    }
    public void setNazwisko(String nazwisko) {
        this.nazwisko = nazwisko;
    }
    public String getPesel() {
        return pesel;
    }
    public void setPesel(String pesel) {
        this.pesel = pesel;
    }

    public Czytelnik() { }
    public Czytelnik(int id, String imie, String nazwisko, String pesel) {
        this.id = id;
        this.imie = imie;
        this.nazwisko = nazwisko;
        this.pesel = pesel;
    }

    @Override
    public String toString() {
        return "["+id+"] - "+imie+" "+nazwisko+" - "+pesel;
    }
}

Wypozyczenie.java

package model;

public class Wypozyczenie {
    private int idKsiazka;
    private int idCzytelnik;

    public int getIdKsiazka() {
        return idKsiazka;
    }
    public void setIdKsiazka(int idKsiazka) {
        this.idKsiazka = idKsiazka;
    }
    public int getIdCzytelnik() {
        return idCzytelnik;
    }
    public void setIdCzytelnik(int idCzytelnik) {
        this.idCzytelnik = idCzytelnik;
    }

    public Wypozyczenie() {}
    public Wypozyczenie(int idKsiazka, int idCzytelnik) {
        this.idKsiazka = idKsiazka;
        this.idCzytelnik = idCzytelnik;

    }
}

Biblioteka.java - główna klasa do komunikacji z bazą danych

package biblioteka;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;

import model.Czytelnik;
import model.Ksiazka;

public class Biblioteka {

    public static final String DRIVER = "org.sqlite.JDBC";
    public static final String DB_URL = "jdbc:sqlite:biblioteka.db";

    private Connection conn;
    private Statement stat;

    public Biblioteka() {
        try {
            Class.forName(Biblioteka.DRIVER);
        } catch (ClassNotFoundException e) {
            System.err.println("Brak sterownika JDBC");
            e.printStackTrace();
        }

        try {
            conn = DriverManager.getConnection(DB_URL);
            stat = conn.createStatement();
        } catch (SQLException e) {
            System.err.println("Problem z otwarciem polaczenia");
            e.printStackTrace();
        }

        createTables();
    }

    public boolean createTables()  {
        String createCzytelnicy = "CREATE TABLE IF NOT EXISTS czytelnicy (id_czytelnika INTEGER PRIMARY KEY AUTOINCREMENT, imie varchar(255), nazwisko varchar(255), pesel int)";
        String createKsiazki = "CREATE TABLE IF NOT EXISTS ksiazki (id_ksiazki INTEGER PRIMARY KEY AUTOINCREMENT, tytul varchar(255), autor varchar(255))";
        String createWypozyczenia = "CREATE TABLE IF NOT EXISTS wypozyczenia (id_wypozycz INTEGER PRIMARY KEY AUTOINCREMENT, id_czytelnika int, id_ksiazki int)";
        try {
            stat.execute(createCzytelnicy);
            stat.execute(createKsiazki);
            stat.execute(createWypozyczenia);
        } catch (SQLException e) {
            System.err.println("Blad przy tworzeniu tabeli");
            e.printStackTrace();
            return false;
        }
        return true;
    }

    public boolean insertCzytelnik(String imie, String nazwisko, String pesel) {
        try {
            PreparedStatement prepStmt = conn.prepareStatement(
                    "insert into czytelnicy values (NULL, ?, ?, ?);");
            prepStmt.setString(1, imie);
            prepStmt.setString(2, nazwisko);
            prepStmt.setString(3, pesel);
            prepStmt.execute();
        } catch (SQLException e) {
            System.err.println("Blad przy wstawianiu czytelnika");
            e.printStackTrace();
            return false;
        }
        return true;
    }

    public boolean insertKsiazka(String tytul, String autor) {
        try {
            PreparedStatement prepStmt = conn.prepareStatement(
                    "insert into ksiazki values (NULL, ?, ?);");
            prepStmt.setString(1, tytul);
            prepStmt.setString(2, autor);
            prepStmt.execute();
        } catch (SQLException e) {
            System.err.println("Blad przy wypozyczaniu");
            return false;
        }
        return true;
    }

    public boolean insertWypozycz(int idCzytelnik, int idKsiazka) {
        try {
            PreparedStatement prepStmt = conn.prepareStatement(
                    "insert into wypozyczenia values (NULL, ?, ?);");
            prepStmt.setInt(1, idCzytelnik);
            prepStmt.setInt(2, idKsiazka);
            prepStmt.execute();
        } catch (SQLException e) {
            System.err.println("Blad przy wypozyczaniu");
            return false;
        }
        return true;
    }

    public List<Czytelnik> selectCzytelnicy() {
        List<Czytelnik> czytelnicy = new LinkedList<Czytelnik>();
        try {
            ResultSet result = stat.executeQuery("SELECT * FROM czytelnicy");
            int id;
            String imie, nazwisko, pesel;
            while(result.next()) {
                id = result.getInt("id_czytelnika");
                imie = result.getString("imie");
                nazwisko = result.getString("nazwisko");
                pesel = result.getString("pesel");
                czytelnicy.add(new Czytelnik(id, imie, nazwisko, pesel));
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
        return czytelnicy;
    }

    public List<Ksiazka> selectKsiazki() {
        List<Ksiazka> ksiazki = new LinkedList<Ksiazka>();
        try {
            ResultSet result = stat.executeQuery("SELECT * FROM ksiazki");
            int id;
            String tytul, autor;
            while(result.next()) {
                id = result.getInt("id_ksiazki");
                tytul = result.getString("tytul");
                autor = result.getString("autor");
                ksiazki.add(new Ksiazka(id, tytul, autor));
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
        return ksiazki;
    }

    public void closeConnection() {
        try {
            conn.close();
        } catch (SQLException e) {
            System.err.println("Problem z zamknieciem polaczenia");
            e.printStackTrace();
        }
    }
}

Krótkie wyjaśnienie.

W konstruktorze najpierw wywołujemy tę linię, odpowiedzialną za załadowanie sterownika do systemu:

Class.forName(Biblioteka.DRIVER);

Później tworzymy połączenie z bazą danych:

conn = DriverManager.getConnection(DB_URL);

jako argument metody przekazując String:

jdbc:sqlite:biblioteka.db

Jak widać jest tutaj podany zarówno typ bazy danych jak i nazwa bazy danych, z której chcemy korzystać. Analogicznie korzystajc z bazy MySql ten String wyglądałby następująco:

jdbc:mysql:sciezkaDoBazyDanychMySQL

 

Dalej utworzyliśmy również obiekt klasy Statement - pozwala on na wykonywanie zapytań na podstawie zdefiniowanych Stringów.

Metoda createTables() tworzy nasz prosty schemat bazy danych (nie jest to kurs SQL, więc założeniem jest to, że większość z was wie co się tam dzieje - w ogólności tworzymy odpowiednie tabele i określamy typ danych jakie przechowują poszczególne kolumny w tabelach).

Trzy metody typu insert, czyli insertCzytelnik, insertKsiazka, insertWypozycz służą do wprowadzania danych do bazy danych. Korzystamy w nich z pomocniczej klasy PreparedStatement, która pozwala stworzyć schemat zapytania jakie chcemy wykonać do bazy danych, i na podstawie tego schematu wypełnić odpowiednie wartości elementami przekazanymi jako argumenty metody. Ponieważ Java jest językiem czysto obiektowym jest to niesamowicie pomocna rzecz.

Kolejne trzy metody, czyli selectCzytelnicy, selectKsiazki pozwalają wyciągnąć z bazy danych wszystkie informacje o czytelnikach oraz ksiażkach znajdujących się w systemie. Metodę selectWypozycz możecie traktować jako zadanie do samodzielnego przećwiczenia. Metody te zwracają listę wszystkich rekordów z danych tabel.

 

Ostatnia metoda zamyka połączenie z bazą danych - jeśli kończymy działanie aplikacji, zawsze powinniśmy to zrobić.

JdbcTest.java - klasa testowa

import java.util.List;

import model.Czytelnik;
import model.Ksiazka;
import biblioteka.Biblioteka;

public class JdbcTest {

    public static void main(String[] args) {
        Biblioteka b = new Biblioteka();
        b.insertCzytelnik("Karol", "Maciaszek", "92873847182");
        b.insertCzytelnik("Piotr", "Wojtecki", "89273849128");
        b.insertCzytelnik("Abdul", "Dabdul", "");

        b.insertKsiazka("Cień Wiatru", "Carlos Ruiz Zafon");
        b.insertKsiazka("W pustyni i w puszczy", "Henryk Sienkiewicz");
        b.insertKsiazka("Harry Potter", "Joanne Kathleen Rowling.");

        List<Czytelnik> czytelnicy = b.selectCzytelnicy();
        List<Ksiazka> ksiazki = b.selectKsiazki();

        System.out.println("Lista czytelników: ");
        for(Czytelnik c: czytelnicy)
            System.out.println(c);

        System.out.println("Lista książek:");
        for(Ksiazka k: ksiazki)
            System.out.println(k);

        b.closeConnection();
    }
}

Schemat aplikacji w eclipse powinien wyglądać następująco:

projekt z bazą danych sqlite w eclipse

Działanie aplikacji jest następujące:

Tworzymy Bibliotekę (a w niej połączenie z bazą danych)

Wstawiamy trzech czytelników oraz trzy książki do bazy danych

Wyciągamy wszystkich czytelników oraz książki z bazy danych

Wyświetlamy wyciągnięte dane.

W wyniku działania programu powinniśmy otrzymać wydruk:

Lista czytelników: 
[1] - Karol Maciaszek - 92873847182
[2] - Piotr Wojtecki - 89273849128
[3] - Abdul Dabdul - 
Lista książek:
[1] - Cień Wiatru - Carlos Ruiz Zafon
[2] - W pustyni i w puszczy - Henryk Sienkiewicz
[3] - Harry Potter - Joanne Kathleen Rowling.

Działanie aplikacji jest prymitywne, jednak pokazuje jak proste jest korzystanie z bazy danych sqlite w Javie. W ramach treningu polecam rozbudowę powyższej aplikacji i dodanie do niej funkcjonalności, która będzie pozwalała na interakcję z użytkownikiem - dodawanie nowych użytkowników, książek oraz informacji o wypożyczeniach.

5. Praktyczne porady

Porada1

Działanie relacyjnych baz danych jest w ogólności wolne, szczególnie wolne jeśli dodajemy bardzo dużo rekordów jeden po drugim (jeśli wykonywanych jest dużo atomowych transakcji). Jest jednak sposób na częściowe usprawnienie. Należy wyłączyć autoCommitowanie za pomocą metody setAutoCommit(false) wywołanej na obiekcie Connection. Pozwoli nam to zbierać większe partie danych i umieszczać rekordy nie pojedynczo, ale np w paczkach po 100. Najłatwiej skomponować "batcha" za pomocą obiektów preparedStatement i nie wywoływać na nich metody execute() tak jak w naszym przykładowym programie, ale zastąpić ją metodą addBatch(). Po dodaniu wszystkich elementów, które chcemy dodać (lub zaktualizować, czy usunąć) wywołujemy metodę executeBatch() na obiekcie PreparedStatement, a następnie commit() na obiekcie Connection na podstawie którego utworzyliśmy PreparedBatch.

Szybkie porównanie:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Statement;

public class PerformanceTest {
	public static void main(String[] args) throws Exception {
		Class.forName("org.sqlite.JDBC");

		Connection conn = DriverManager.getConnection("jdbc:sqlite:test1.db");
		Statement stat = conn.createStatement();
		stat.execute("CREATE TABLE users (id INTEGER, name varchar(255))");

		long start = System.currentTimeMillis();
		PreparedStatement prepStmt = conn
				.prepareStatement("INSERT INTO users values (?, ?);");
		for (int i = 1; i <= 100; i++) {
			prepStmt.setInt(1, i);
			prepStmt.setString(2, "someName" + i);
			prepStmt.executeUpdate();
		}
		System.out.println("Time1: " + (System.currentTimeMillis() - start));
		conn.close();

		Connection conn2 = DriverManager.getConnection("jdbc:sqlite:test2.db");
		conn2.setAutoCommit(false);
		Statement stat2 = conn2.createStatement();
		stat2.execute("CREATE TABLE users (id INTEGER, name varchar(255))");

		start = System.currentTimeMillis();
		PreparedStatement prepStmt2 = conn2
				.prepareStatement("INSERT INTO users values (?, ?);");
		for (int i = 1; i <= 100; i++) {
			prepStmt2.setInt(1, i);
			prepStmt2.setString(2, "someName" + i);
			prepStmt2.addBatch();
		}
		prepStmt2.executeBatch();
		conn2.commit();
		System.out.println("Time2: " + (System.currentTimeMillis() - start));
		conn2.close();
	}
}

Time1: 8299

Time2: 126

Komentarz jest chyba zbędny.

 

Porada 2

Możesz zarządzać swoją bazą SQLite bez konieczności pisania aplikacji. Możesz pobrać SQLite Command Shell i stamtąd zarówno przeglądać zawartość bazy jak i wykonywać na niej dowolne zapytania. Pobierz wersję na swój system stąd:

http://www.sqlite.org/download.html

 

Wrzuć plik do swojego folderu z projektem eclipse, lub dodaj zmienną środowiskową w systemie i wywołaj "sqlite3 nazwaBazyDanych" w folderze projektu z poziomu konsoli.

Przykład działania:

baza sqlite w konsoli

To już koniec lekcji o bazie danych SQLite. W innych lekcjach przeczytasz jak wykorzystać bazy danych w Androidzie, Javie EE. Niebawem także kilka słów o nierelacyjnych bazach danych.

 

Komentarze

irwi

czy tylko ja mam problem z metodą execute() z klasy Statemen? nie akceptuje string jako argument

Sławek Ludwiczak

a problemem nie jest przypadkiem błędny String, np z jakimiś znakami specjalnymi?

GMarcin

Pytanie, jeśli tworzymy tabelę z poziomu kodu, to gdzie jest zapisany teraz plik z tą bazą danych ?

Sławek Ludwiczak

W przypadku powyższego przykładu w głównym folderze projektu.

GMarcin

Jeszcze jedno pytanie, gdyby wylikać w czymś bazę to podajemy ścieżkę tutaj:
public static final
String DB_URL =
"jdbc:sqlite:biblioteka.db"; ? Na przykład "C://Projekt//jdbc:sqlite:biblioteka.db"; tak czy bez dopisku jdbc:... ?

Adrian

Przyczepię się tylko do definicji. Piszesz głupoty jeśli chodzi o teorię. Np baza danych to nie zbiór tabel. Poprzyj swoje wypociny jakąś bibliografią.
Do praktycznej części nie mam zastrzeżeń.

Aa

Czepiasz się pierdół. To tylko przykład.

Micgy

Czy ten sam kod i to samo zastosowanie, zadziała z SQLite na androidzie ?

Sławek Ludwiczak

W Androidzie bazy danych działają generalnie zupełnie inaczej niż tutaj opisano, ponieważ SQLite jest tam wbudowane w system i dostarczone są gotowe mechanizmy do obsługi baz danych. Na szczęście dzięki temu jest to generalnie nawet łatwiejsze niż w javie i daje większe możliwości. Niestety u nas jeszcze o tym nie poczytasz.

BartekeM

tak z czystej ciekawości, czy pesel nie jest za dużą liczbą jak na int?

Sebastian

Fajny tutorial, dzięki :)

pierox

Hej.

niezłe informacje .... masz może podobne rzeczy z MS SQL...
ja np. mam duży problem z jdbc do ms sql 2005 i autentykacją domenowa ////???
Pozdrawiam
Pierox