The GluonSQLite App

public class BasicView extends View {

    private final static String DB_NAME = "sample.db";
    private Connection connection = null;
    private Statement stmt;
    private ResultSet rs;

    private void createDB() {
        status.getItems().add("Creating a Database with SQLite");
        File dir;
        String dbUrl = "jdbc:sqlite:";
        try {
            dir = Services.get(StorageService.class)
                    .map(s -> s.getPrivateStorage().get())
                    .orElseThrow(() -> new IOException("Error: PrivateStorage not available"));
            File db = new File (dir, DB_NAME);
            dbUrl = dbUrl + db.getAbsolutePath();
        } catch (IOException ex) {
            status.getItems().add("Error " + ex.getMessage());
            return;
        }

        try {
            connection = DriverManager.getConnection(dbUrl);
            status.getItems().add("Connection established: " + dbUrl);
        } catch (SQLException ex) {
            status.getItems().add("Error establishing connection " + ex.getSQLState());
            return;
        }

        List<Person> list = new ArrayList<>();

        try {
            if (connection != null) {
                stmt = connection.createStatement();
                stmt.setQueryTimeout(30);

                status.getItems().add("Creating table 'person'...");
                stmt.executeUpdate("drop table if exists person");
                stmt.executeUpdate("create table person (id integer, firstname string, lastname string)");
                stmt.executeUpdate("insert into person values(1, 'Johan', 'Vos')");
                stmt.executeUpdate("insert into person values(2, 'Eugene', 'Ryzhikov')");
                stmt.executeUpdate("insert into person values(3, 'Joeri', 'Sykora')");
                stmt.executeUpdate("insert into person values(4, 'Erwin', 'Morrhey')");

                status.getItems().add("Retrieving records from table 'person'...");
                rs = stmt.executeQuery("select * from person");
                while (rs.next()) {
                    String firstname = rs.getString("firstname");
                    String lastname = rs.getString("lastname");
                    list.add(new Person(firstname, lastname));
                }
                status.getItems().add("End creating table and retrieving records");
            }
        } catch (SQLException ex) {
            status.getItems().add("SQL error " + ex.getMessage());
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException ex) {
                status.getItems().add("SQL error " + ex.getSQLState());
            }
            try {
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException ex) {
                status.getItems().add("SQL error " + ex.getSQLState());
            }
        }

        listView.getItems().setAll(list);
    }
}

public class DBUtils {

    public static void copyDatabase(String pathIni, String pathEnd, String name)  {

        try (InputStream myInput = GluonSQLite.class.getResourceAsStream(pathIni+name)) {
            String outFileName =  pathEnd + "/" + name;
            try (OutputStream myOutput = new FileOutputStream(outFileName)) {
                byte[] buffer = new byte[1024];
                int length;
                while ((length = myInput.read(buffer)) > 0) {
                    myOutput.write(buffer, 0, length);
                }
                myOutput.flush();

            } catch (IOException ex) {
                System.out.println("Error " + ex);
            }
        } catch (IOException ex) {
            System.out.println("Error " + ex);
        }
    }
}

public class BasicView extends View {

    private void readDB() {

        status.getItems().add("Reading an existing Database with SQLite");
        String dbUrl = "jdbc:sqlite:";
        if (JavaFXPlatform.isDesktop()) {
            dbUrl= dbUrl + ":resource:" + GluonSQLite.class.getResource("/databases/" + DB_NAME).toExternalForm();
        } else {
            File dir;
            try {
                dir = Services.get(StorageService.class)
                        .map(s -> s.getPrivateStorage().get())
                        .orElseThrow(() -> new IOException("Error: PrivateStorage not available"));
                File db = new File (dir, DB_NAME);
                status.getItems().add("Copying database " + DB_NAME + " to private storage");
                DBUtils.copyDatabase("/databases/", dir.getAbsolutePath(), DB_NAME);
                dbUrl = dbUrl + db.getAbsolutePath();
            } catch (IOException ex) {
                status.getItems().add("IO error " + ex.getMessage());
                return;
            }
        }

        try {
            connection = DriverManager.getConnection(dbUrl);
            status.getItems().add("Connection established: " + dbUrl);
        } catch (SQLException ex) {
            status.getItems().add("Error establishing connection " +ex.getSQLState());
            return;
        }

        List<Person> list = new ArrayList<>();
        try {
            DatabaseMetaData md = connection.getMetaData();
            rs = md.getTables(null, null, "%", null);
            status.getItems().add("Tables in Database " + DB_NAME);
            while (rs.next()) {
                status.getItems().add(" * " + rs.getString(3));
            }

            status.getItems().add("Reading table 'person'");
            stmt = connection.createStatement();
            rs = stmt.executeQuery("select * from person");
            while (rs.next()) {
                String firstname = rs.getString("firstname");
                String lastname = rs.getString("lastname");
                list.add(new Person(firstname, lastname));
            }
            status.getItems().add("End reading table and retrieving records");
        } catch (SQLException ex) {
            status.getItems().add("SQL error " + ex.getSQLState());
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException ex) {
                status.getItems().add("SQL error " + ex.getSQLState());
            }
            try {
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException ex) {
                status.getItems().add("SQL error " + ex.getSQLState());
            }
        }

        listView.getItems().setAll(list);
    }
}