Some problems with sql to get all books. I need internet connection to fix it (to read docs).

2 files changed, 73 insertions, 24 deletions

diff --git a/MobiReader/src/mobireader/SqliteDatabaseHandler.java b/MobiReader/src/mobireader/SqliteDatabaseHandler.java
index b374271..7cc16ad 100644
--- a/MobiReader/src/mobireader/SqliteDatabaseHandler.java
+++ b/MobiReader/src/mobireader/SqliteDatabaseHandler.java
@@ -19,6 +19,13 @@ import java.util.ArrayList;
 
 class StatementBuilder
 {
+    private PreparedStatement prepareStatement(Connection connection, String statementText) throws SQLException
+    {
+        PreparedStatement statement = connection.prepareStatement(
+               statementText);
+        statement.setQueryTimeout(30);  // set timeout to 30 sec.
+        return statement;
+    }
     public PreparedStatement findAuthorByNameStatement(Connection connection) throws SQLException
     {
         PreparedStatement statement = connection.prepareStatement(
@@ -30,61 +37,61 @@ class StatementBuilder
     public PreparedStatement addAuthorStatement(Connection connection) 
             throws SQLException, ClassNotFoundException
     {
-        PreparedStatement statement = connection.prepareStatement(
-                "insert or replace into authors(name, additional_info) values( ?, ?)");
-        statement.setQueryTimeout(30);  // set timeout to 30 sec.
-        return statement;
+        String statementText;
+        statementText = "insert or replace into authors(name, additional_info) values( ?, ?)";
+        return prepareStatement(connection, statementText);
     }
     
     
     public PreparedStatement addBookStatement(Connection connection) 
             throws SQLException, ClassNotFoundException
     {
-        PreparedStatement statement = connection.prepareStatement(
-                "insert or replace into books(title, author_name, path_to_content) values( ?, ?, ?)");
-        statement.setQueryTimeout(30);  // set timeout to 30 sec.
-        return statement;
+        String statementText = "insert or replace into books(title, author_name, path_to_content) values( ?, ?, ?)";
+        return prepareStatement(connection, statementText);
     }
     
     public PreparedStatement findBookStatement(Connection connection) 
             throws SQLException, ClassNotFoundException
     {
-        PreparedStatement statement = connection.prepareStatement(
-                "select * from books where title=?");
-        statement.setQueryTimeout(30);  // set timeout to 30 sec.
-        return statement;
+        String statementText = "select * from books where title=?";
+        return prepareStatement(connection, statementText);
     }
     
     public PreparedStatement findBookByAuthorStatement(Connection connection) 
             throws SQLException, ClassNotFoundException
     {
-        PreparedStatement statement = connection.prepareStatement(
-                "select * from books where author_name=?");
-        statement.setQueryTimeout(30);  // set timeout to 30 sec.
-        return statement;
+        String statementText = "select * from books where author_name=?";
+        return prepareStatement(connection, statementText);
+    }
+    
+    public PreparedStatement getAllBooksStatement(Connection connection) 
+            throws SQLException, ClassNotFoundException
+    {
+        String statementText = "select * from books";
+        return prepareStatement(connection, statementText);
     }
     
 }
 
 public class SqliteDatabaseHandler
 {
-    static String DATABASE_FILE = "sample.db";
-    String driver;
+    String dbFile = "sample.db";
+    String driver = "org.sqlite.JDBC";
     StatementBuilder statBld = new StatementBuilder();
-    public SqliteDatabaseHandler(String driver)
+    public SqliteDatabaseHandler(String databaseFile)
     {
-        this.driver = driver;
+        this.dbFile = databaseFile;
     }
     
     public SqliteDatabaseHandler()
     {
-        this.driver = "org.sqlite.JDBC";
+        this.dbFile = "sample.db";
     }
     
     public Connection prepareConnection() throws ClassNotFoundException, SQLException
     {
         Class.forName(this.driver);
-        return DriverManager.getConnection("jdbc:sqlite:" + DATABASE_FILE);
+        return DriverManager.getConnection("jdbc:sqlite:" + dbFile);
     }
     
     
@@ -211,6 +218,44 @@ public class SqliteDatabaseHandler
         return null;
     }
     
+    public ArrayList<Book> getAllBooks(Author author) throws ClassNotFoundException
+    {
+        try
+        {
+            Connection connection = prepareConnection();
+            try
+            {
+                PreparedStatement stat;
+                stat = statBld.getAllBooksStatement(connection);
+                ResultSet rs = stat.executeQuery();
+                ArrayList<Book> books = new ArrayList<>();
+                while(rs.next())
+                {
+                    String path = rs.getString("path_to_content");
+                    String title = rs.getString("title");
+                    books.add(new Book(title, author, path));
+                }
+                return books;
+            }
+            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  void addAuthor(Author author) throws ClassNotFoundException
     {
        try
diff --git a/MobiReader/src/mobireader/gui/ui/AppMain.java b/MobiReader/src/mobireader/gui/ui/AppMain.java
index 1059de7..562d972 100644
--- a/MobiReader/src/mobireader/gui/ui/AppMain.java
+++ b/MobiReader/src/mobireader/gui/ui/AppMain.java
@@ -103,6 +103,7 @@ import javafx.stage.Popup;
 import javafx.stage.PopupBuilder;
 import javafx.stage.Stage;
 import mobireader.Book;
+import mobireader.SqliteDatabaseHandler;
 import mobireader.gui.model.GuiModel;
 
 /**
@@ -120,14 +121,17 @@ public class AppMain extends Application {
   Label htmlLabel;
   Popup alertPopup;
   Tab webViewTab;
-  List<Book> myBooks = Book.createSomeExamples();
+  ArrayList<Book> myBooks = Book.createSomeExamples();
+  SqliteDatabaseHandler db = new SqliteDatabaseHandler("my_books.db");
   
   public static void main(String[] args) {
     Application.launch(args);
   }
     
   @Override
-  public void start(final Stage primaryStage) {
+  public void start(final Stage primaryStage) throws ClassNotFoundException {
+    db.addBooks(myBooks);
+    myBooks = db.getAllBooks();
     stage = primaryStage;
     Scene scene = SceneBuilder.create()
       .width(800)