Examples with Database com.codename1.db.Database used on opensource projects

Example 1 with Database

use of com.codename1.db.Database in project CodenameOne by codenameone.

the class DatabaseTests method testSimpleQueriesInCustomPath.

private void testSimpleQueriesInCustomPath() throws Exception {
    if (Database.isCustomPathSupported()) {
        String dbName = new File("somedir/testdb").getAbsolutePath();
        new File(dbName).getParentFile().mkdirs();
        Database.delete(dbName);
        Database db = Database.openOrCreate(dbName);
        db.execute("create table tests (name text)");
        db.execute("insert into tests values ('Steve'), ('Mike'), ('Ryan')");
        Cursor c = db.executeQuery("select count(*) from tests");
        c.next();
        this.assertEqual(3, c.getRow().getInteger(0), "Expected result of 3 for count(*) after inserting 3 rows");
    } else {
        Log.p("testSimpleQueriesInCustomPath skipped on this platform");
    }
}

Example 2 with Database

use of com.codename1.db.Database in project CodenameOne by codenameone.

the class DatabaseTests method testCustomPaths.

private void testCustomPaths() throws Exception {
    if (Database.isCustomPathSupported()) {
        File dbFile = new File("somedir/testdb");
        dbFile.getParentFile().mkdirs();
        String dbName = dbFile.getAbsolutePath();
        // Start out fresh
        Database.delete(dbName);
        Database db = Database.openOrCreate(dbName);
        this.assertNotNull(db, "Failed to open database with custom path " + dbName);
        this.assertTrue(Database.exists(dbName), "Database.exists() returns false after openOrCreate with custom path: " + dbName);
        String path = Database.getDatabasePath(dbName);
        this.assertTrue(FileSystemStorage.getInstance().exists(path), "Database doesn't exist after creation with custom path: " + dbName);
        this.assertEqual(dbFile.getAbsolutePath(), path, "Result of getDatabasePath() doesn't match input path with custom path");
        db.close();
        Database.delete(dbName);
        this.assertTrue(!FileSystemStorage.getInstance().exists(path), "Failed to delete database with custom path: " + dbName);
    } else {
        Throwable ex = null;
        File dbFile = new File("somedir/testdb");
        dbFile.getParentFile().mkdirs();
        String dbName = dbFile.getAbsolutePath();
        try {
            Database.openOrCreate(dbName);
        } catch (Throwable t) {
            ex = t;
        }
        this.assertTrue(ex instanceof IllegalArgumentException, "Platforms that don't support custom paths should throw an illegalArgumentException when trying to open databases with file separators in them");
        ex = null;
        try {
            Database.exists(dbName);
        } catch (Throwable t) {
            ex = t;
        }
        this.assertTrue(ex instanceof IllegalArgumentException, "Platforms that don't support custom paths should throw an illegalArgumentException when trying to open databases with file separators in them");
        ex = null;
        try {
            Database.delete(dbName);
        } catch (Throwable t) {
            ex = t;
        }
        this.assertTrue(ex instanceof IllegalArgumentException, "Platforms that don't support custom paths should throw an illegalArgumentException when trying to open databases with file separators in them");
        ex = null;
        try {
            Database.getDatabasePath(dbName);
        } catch (Throwable t) {
            ex = t;
        }
        this.assertTrue(ex instanceof IllegalArgumentException, "Platforms that don't support custom paths should throw an illegalArgumentException when trying to open databases with file separators in them");
    }
}

Example 3 with Database

use of com.codename1.db.Database in project CodenameOne by codenameone.

the class SQLExplorerSample method start.

public void start() {
    if (current != null) {
        current.show();
        return;
    }
    Toolbar.setGlobalToolbar(true);
    Style s = UIManager.getInstance().getComponentStyle("TitleCommand");
    FontImage icon = FontImage.createMaterial(FontImage.MATERIAL_QUERY_BUILDER, s);
    Form hi = new Form("SQL Explorer", new BorderLayout());
    hi.getToolbar().addCommandToRightBar("", icon, (e) -> {
        TextArea query = new TextArea(3, 80);
        Command ok = new Command("Execute");
        Command cancel = new Command("Cancel");
        if (Dialog.show("Query", query, ok, cancel) == ok) {
            Database db = null;
            Cursor cur = null;
            try {
                db = Display.getInstance().openOrCreate("MyDB.db");
                if (query.getText().startsWith("select")) {
                    cur = db.executeQuery(query.getText());
                    int columns = cur.getColumnCount();
                    hi.removeAll();
                    if (columns > 0) {
                        boolean next = cur.next();
                        if (next) {
                            ArrayList<String[]> data = new ArrayList<>();
                            String[] columnNames = new String[columns];
                            for (int iter = 0; iter < columns; iter++) {
                                columnNames[iter] = cur.getColumnName(iter);
                            }
                            while (next) {
                                Row currentRow = cur.getRow();
                                String[] currentRowArray = new String[columns];
                                for (int iter = 0; iter < columns; iter++) {
                                    currentRowArray[iter] = currentRow.getString(iter);
                                }
                                data.add(currentRowArray);
                                next = cur.next();
                            }
                            Object[][] arr = new Object[data.size()][];
                            data.toArray(arr);
                            hi.add(BorderLayout.CENTER, new Table(new DefaultTableModel(columnNames, arr)));
                        } else {
                            hi.add(BorderLayout.CENTER, "Query returned no results");
                        }
                    } else {
                        hi.add(BorderLayout.CENTER, "Query returned no results");
                    }
                } else {
                    db.execute(query.getText());
                    hi.add(BorderLayout.CENTER, "Query completed successfully");
                }
                hi.revalidate();
            } catch (IOException err) {
                Log.e(err);
                hi.removeAll();
                hi.add(BorderLayout.CENTER, "Error: " + err);
                hi.revalidate();
            } finally {
                Util.cleanup(db);
                Util.cleanup(cur);
            }
        }
    });
    hi.show();
}

Example 4 with Database

use of com.codename1.db.Database in project CodenameOne by codenameone.

the class SQLMap method select.

/**
 * Fetches the components from the database matching the given cmp description, the fields that aren't
 * null within the cmp will match the where clause
 * @param cmp the component to match
 * @param orderBy the column to order by, can be null to ignore order
 * @param ascending true to indicate ascending order
 * @param maxElements the maximum number of elements returned can be 0 or lower to ignore
 * @param page  the page within the query to match the max elements value
 * @return the result of the query
 */
public java.util.List<PropertyBusinessObject> select(PropertyBusinessObject cmp, Property orderBy, boolean ascending, int maxElements, int page) throws IOException, InstantiationException {
    String tableName = getTableName(cmp);
    StringBuilder createStatement = new StringBuilder("SELECT * FROM ");
    createStatement.append(tableName);
    ArrayList<Object> params = new ArrayList<Object>();
    createStatement.append(" WHERE ");
    boolean found = false;
    for (PropertyBase p : cmp.getPropertyIndex()) {
        if (p instanceof Property) {
            if (((Property) p).get() != null) {
                if (found) {
                    createStatement.append(" AND ");
                }
                found = true;
                params.add(((Property) p).get());
                createStatement.append(getColumnName(p));
                createStatement.append(" = ?");
            }
        }
    }
    // all properties are null undo the where append
    if (!found) {
        createStatement = new StringBuilder("SELECT * FROM ");
        createStatement.append(tableName);
    }
    if (orderBy != null) {
        createStatement.append(" ORDER BY ");
        createStatement.append(getColumnName(orderBy));
        if (!ascending) {
            createStatement.append(" DESC");
        }
    }
    if (maxElements > 0) {
        createStatement.append(" LIMIT ");
        createStatement.append(maxElements);
        if (page > 0) {
            createStatement.append(" OFFSET ");
            createStatement.append(page * maxElements);
        }
    }
    Cursor c = null;
    try {
        ArrayList<PropertyBusinessObject> response = new ArrayList<PropertyBusinessObject>();
        c = executeQuery(createStatement.toString(), params.toArray());
        while (c.next()) {
            PropertyBusinessObject pb = (PropertyBusinessObject) cmp.getClass().newInstance();
            for (PropertyBase p : pb.getPropertyIndex()) {
                Row currentRow = c.getRow();
                SqlType t = getSqlType(p);
                if (t == SqlType.SQL_EXCLUDE) {
                    continue;
                }
                Object value = t.getValue(currentRow, c.getColumnIndex(getColumnName(p)), p);
                if (p instanceof Property) {
                    ((Property) p).set(value);
                }
            }
            response.add(pb);
        }
        c.close();
        return response;
    } catch (Throwable t) {
        Log.e(t);
        if (c != null) {
            c.close();
        }
        if (t instanceof IOException) {
            throw ((IOException) t);
        } else {
            throw new IOException(t.toString());
        }
    }
}

Example 5 with Database

use of com.codename1.db.Database in project CodenameOne by codenameone.

the class SQLMap method selectImpl.

/**
 * Fetches the components from the database matching the given cmp description, the fields that aren't
 * null within the cmp will match the where clause
 * @param not indicates if the query should be a "not" query
 * @param cmp the component to match
 * @param orderBy the column to order by, can be null to ignore order
 * @param ascending true to indicate ascending order
 * @param maxElements the maximum number of elements returned can be 0 or lower to ignore
 * @param page  the page within the query to match the max elements value
 * @return the result of the query
 */
private java.util.List<PropertyBusinessObject> selectImpl(boolean not, PropertyBusinessObject cmp, Property orderBy, boolean ascending, int maxElements, int page) throws IOException, InstantiationException {
    String tableName = getTableName(cmp);
    StringBuilder createStatement = new StringBuilder("SELECT * FROM ");
    createStatement.append(tableName);
    ArrayList<Object> params = new ArrayList<Object>();
    createStatement.append(" WHERE ");
    boolean found = false;
    for (PropertyBase p : cmp.getPropertyIndex()) {
        if (p instanceof Property) {
            if (((Property) p).get() != null) {
                if (found) {
                    createStatement.append(" AND ");
                }
                found = true;
                params.add(((Property) p).get());
                createStatement.append(getColumnName(p));
                if (not) {
                    createStatement.append(" <> ?");
                } else {
                    createStatement.append(" = ?");
                }
            }
        }
    }
    // all properties are null undo the where append
    if (!found) {
        createStatement = new StringBuilder("SELECT * FROM ");
        createStatement.append(tableName);
    }
    if (orderBy != null) {
        createStatement.append(" ORDER BY ");
        createStatement.append(getColumnName(orderBy));
        if (!ascending) {
            createStatement.append(" DESC");
        }
    }
    if (maxElements > 0) {
        createStatement.append(" LIMIT ");
        createStatement.append(maxElements);
        if (page > 0) {
            createStatement.append(" OFFSET ");
            createStatement.append(page * maxElements);
        }
    }
    Cursor c = null;
    try {
        ArrayList<PropertyBusinessObject> response = new ArrayList<PropertyBusinessObject>();
        c = executeQuery(createStatement.toString(), params.toArray());
        while (c.next()) {
            PropertyBusinessObject pb = (PropertyBusinessObject) cmp.getClass().newInstance();
            for (PropertyBase p : pb.getPropertyIndex()) {
                Row currentRow = c.getRow();
                SqlType t = getSqlType(p);
                if (t == SqlType.SQL_EXCLUDE) {
                    continue;
                }
                Object value = t.getValue(currentRow, c.getColumnIndex(getColumnName(p)), p);
                if (p instanceof Property) {
                    ((Property) p).set(value);
                }
            }
            response.add(pb);
        }
        c.close();
        return response;
    } catch (Throwable t) {
        Log.e(t);
        if (c != null) {
            c.close();
        }
        if (t instanceof IOException) {
            throw ((IOException) t);
        } else {
            throw new IOException(t.toString());
        }
    }
}