More implementation of DB handling and tests for it

4 files changed, 307 insertions, 6 deletions

diff --git a/MobiReader/sample.db b/MobiReader/sample.db
new file mode 100644
index 0000000..0effe82
--- /dev/null
+++ b/MobiReader/sample.db
diff --git a/MobiReader/src/mobireader/Book.java b/MobiReader/src/mobireader/Book.java
index fa5a4d6..824b547 100644
--- a/MobiReader/src/mobireader/Book.java
+++ b/MobiReader/src/mobireader/Book.java
@@ -1,7 +1,4 @@
-/*
- * To change this template, choose Tools | Templates
- * and open the template in the editor.
- */
+
 package mobireader;
 
 import java.util.ArrayList;
@@ -17,33 +14,71 @@ public class Book {
     String title;
     Author author;
     DateTime timestamp;
+    String content = "";
+    String pathToContent = "";
+    Boolean isContentFetched = false;
+    
+    
     public Book(String title, Author author, DateTime timestamp)
     {
         this.title = title;
         this.author = author;
         this.timestamp = timestamp;
     }
+    
     public Book(String title, Author author)
     {
         this.title = title;
         this.author = author;
         this.timestamp = new DateTime();
     }
+    
+    public Book(String title, Author author, String pathToContent)
+    {
+        this.title = title;
+        this.author = author;
+        this.timestamp = new DateTime();
+        this.pathToContent = pathToContent;
+    }
+    public String getContent()
+    {
+        return this.content;
+    }
+    
+    public String getPathToContent()
+    {
+        return this.pathToContent;
+    }
+    
+    public Boolean isContentAvailable()
+    {
+        return isContentFetched;
+    }
+    
     public String getTitle()
     {
         return this.title;
     }
+    
     public Author getAuthor()
     {
         return this.author;
     }
+    
     public DateTime getTimestamp()
     {
         return this.timestamp;
     }
-    static public List<Book> createSomeExamples()
+    
+    static public Book exemplaryBook()
+    {
+        return new Book("Hakuna Matata",
+                        new Author("Mr. Elephant", "Hakuna Matata"));
+    }
+    
+    static public ArrayList<Book> createSomeExamples()
     {
-        List<Book> books = new ArrayList<>();
+        ArrayList<Book> books = new ArrayList<>();
         books.add(new Book("Hakuna Matata", new Author("Mr. Elephant", "Hakuna Matata")));
         books.add(new Book("Life of a star", new Author("Elvis Presley","Life of a star")));
         String[] titles = {"Real physics", "Kartofle"};
diff --git a/MobiReader/src/mobireader/SqliteDatabaseHandler.java b/MobiReader/src/mobireader/SqliteDatabaseHandler.java
new file mode 100644
index 0000000..82fbda4
--- /dev/null
+++ b/MobiReader/src/mobireader/SqliteDatabaseHandler.java
@@ -0,0 +1,186 @@
+/*
+ * To change this template, choose Tools | Templates
+ * and open the template in the editor.
+ */
+package mobireader;
+
+/**
+ *
+ * @author att
+ */
+
+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.ArrayList;
+
+public class SqliteDatabaseHandler
+{
+    static String DATABASE_FILE = "sample.db";
+    String driver;
+    
+    public SqliteDatabaseHandler(String driver)
+    {
+        this.driver = driver;
+    }
+    
+    public SqliteDatabaseHandler()
+    {
+        this.driver = "org.sqlite.JDBC";
+    }
+    
+    public Connection prepareConnection() throws ClassNotFoundException, SQLException
+    {
+        Class.forName(this.driver);
+        return DriverManager.getConnection("jdbc:sqlite:" + DATABASE_FILE);
+    }
+    
+    public PreparedStatement prepareAddingBookStatement(Connection connection) 
+            throws SQLException, ClassNotFoundException
+    {
+        PreparedStatement statement = connection.prepareStatement(
+                "insert into books values($next_id, ?, ?, ?)");
+        statement.setQueryTimeout(30);  // set timeout to 30 sec.
+        return statement;
+    }
+    
+    public PreparedStatement prepareFindingBookStatement(Connection connection) 
+            throws SQLException, ClassNotFoundException
+    {
+        PreparedStatement statement = connection.prepareStatement(
+                "select * from books where title=?");
+        statement.setQueryTimeout(30);  // set timeout to 30 sec.
+        return statement;
+    }
+    
+    public  void addBooks(ArrayList<Book> books) throws ClassNotFoundException
+    {
+        for(Book book : books)
+        {
+            addBook(book);
+        }
+    }
+    
+    public  void addBook(Book book) throws ClassNotFoundException
+    {
+       try
+        {
+            Connection connection = prepareConnection();
+            try
+            {
+                //TODO
+                //Check if author is in db, if he is, check his id
+                int authorId = 1;
+                PreparedStatement stat;
+                stat= this.prepareAddingBookStatement(connection);
+                stat.setString(2, book.getTitle());
+                stat.setInt(3, authorId);
+                stat.setString(4, book.getPathToContent());
+                stat.executeUpdate();
+            }
+            catch(SQLException e) { System.err.println(e.getMessage()); }
+            finally
+            {
+              try
+              {
+                if(connection != null)
+                  connection.close();
+              }
+              catch(SQLException e) { System.err.println(e.getMessage()); }
+            }
+        }
+        catch (SQLException e)
+        {
+            System.err.println("Problem occured during making connection to db");
+            System.err.println(e.getMessage());     
+        }
+    }
+    
+    public Book findBook(String title) throws ClassNotFoundException
+    {
+        try
+        {
+            Connection connection = prepareConnection();
+            try
+            {
+                PreparedStatement stat;
+                stat= this.prepareFindingBookStatement(connection);
+                
+                stat.setString(1, title);
+                
+                stat.executeQuery();
+                
+                ResultSet rs = stat.executeQuery();
+                int authorId = rs.getInt("author_id");
+                String path = rs.getString("path_to_content");
+                Author author = new Author("Gepetto", title);
+                return new Book(title, author, path);
+            }
+            catch(SQLException e) { System.err.println(e.getMessage()); }
+            finally
+            {
+              try
+              {
+                if(connection != null)
+                  connection.close();
+              }
+              catch(SQLException e) { System.err.println(e.getMessage()); }
+            }
+        }
+        catch (SQLException e)
+        {
+            System.err.println("Problem occured during making connection to db");
+            System.err.println(e.getMessage());     
+        }
+        return null;
+    }
+
+    public  Book getBook(Integer id)
+    {
+        return Book.exemplaryBook();
+    }
+
+
+    public static void main () throws Exception
+    {// load the sqlite-JDBC driver using the current class loader
+    Class.forName("org.sqlite.JDBC");
+
+    Connection connection = null;
+    try
+    {
+      // create a database connection
+      connection = DriverManager.getConnection("jdbc:sqlite:sample.db");
+      Statement statement = connection.createStatement();
+      statement.setQueryTimeout(30);  // set timeout to 30 sec.
+
+      statement.executeUpdate("drop table if exists books");
+      statement.executeUpdate("create table books (id integer, title string, " +
+              "author_id integer, path_to_content string)");
+      statement.executeUpdate("insert into books values(1, 'leo', 1, '/somepath/leo.txt')");
+      statement.executeUpdate("insert into books values(2, 'yui', 1, '/somepath/yui.txt')");
+      ResultSet rs = statement.executeQuery("select * from books");
+    }
+    catch(SQLException e)
+    {
+      // if the error message is "out of memory",
+      // it probably means no database file is found
+      System.err.println(e.getMessage());
+    }
+    finally
+    {
+      try
+      {
+        if(connection != null)
+          connection.close();
+      }
+      catch(SQLException e)
+      {
+        // connection close failed.
+        System.err.println(e);
+      }
+    }
+  }
+}
diff --git a/MobiReader/test/tests/SqlConnectionJUnitTest.java b/MobiReader/test/tests/SqlConnectionJUnitTest.java
new file mode 100644
index 0000000..909ba12
--- /dev/null
+++ b/MobiReader/test/tests/SqlConnectionJUnitTest.java
@@ -0,0 +1,80 @@
+/*
+ * To change this template, choose Tools | Templates
+ * and open the template in the editor.
+ */
+package tests;
+
+import java.util.ArrayList;
+import java.util.List;
+import mobireader.Author;
+import mobireader.Book;
+import mobireader.SqliteDatabaseHandler;
+import org.junit.After;
+import org.junit.AfterClass;
+import org.junit.Before;
+import org.junit.BeforeClass;
+import org.junit.Test;
+import static org.junit.Assert.*;
+
+/**
+ *
+ * @author att
+ */
+public class SqlConnectionJUnitTest {
+    
+    public SqlConnectionJUnitTest() {
+    }
+    
+    @BeforeClass
+    public static void setUpClass() {
+    }
+    
+    @AfterClass
+    public static void tearDownClass() {
+    }
+    
+    @Before
+    public void setUp() {
+    }
+    
+    @After
+    public void tearDown() {
+    }
+    // TODO add test methods here.
+    // The methods must be annotated with annotation @Test. For example:
+    //
+    @Test
+    public void testIfConnectionWorks() throws Exception
+    {
+        SqliteDatabaseHandler.main();
+    }
+    
+    @Test
+    public void testAddingBookToDb() throws Exception
+    {
+        SqliteDatabaseHandler handler = new SqliteDatabaseHandler();
+        
+        String title = "Title";
+        String authorName = "Gepetto";
+        Author gepetto = new Author(authorName, title);
+        String path = "/somepath/book.txt";
+        Book bookToBeAdded = new Book(title, gepetto, path);
+        handler.addBook(bookToBeAdded);
+        Book bookFromDB = handler.findBook(title);
+        assertEquals(title, bookFromDB.getTitle());
+        assertEquals(authorName, bookFromDB.getAuthor().getName());
+        assertEquals(path, bookFromDB.getPathToContent());
+    }
+    
+    public void testAddingFewBooksToDB() throws Exception
+    {
+        SqliteDatabaseHandler handler = new SqliteDatabaseHandler();
+        ArrayList<Book> books = Book.createSomeExamples();
+        handler.addBooks(books);
+        for(Book book : books)
+        {
+            Book bookFromDB = handler.findBook(book.getTitle());
+            assertTrue(bookFromDB != null);
+        }
+    }
+}
\ No newline at end of file