Bazy danych SQLite w Javie

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.

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:

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:

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.

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

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".

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:

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.

Praktyczne porady

Przetwarzanie batchowe

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.

Zarządzanie bazą z konsoli

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:

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.