7
\$\begingroup\$

I created this library just for fun. Since I was a bit tired to write 10 lines of code to execute a simple select query in Android, I created a way make it simple.

It's not so advanced and some Android query features are not ported, such as beginTransaction.

BananaQuery is the main class which should be used to create instances of BananaUpdate, BananaInsert and BananaSelect.

About checkNotNull: I know Guava has it, but I don't want to include a library for one method.

Things I care about:

  1. Everything
  2. I would like to avoid to repeat some blocks of code, see value methods They are the same in the 3 classes! too for execute method. Ah, and for the algorithm methods too.
  3. Named arguments: I created a way to allow to use named arguments (:name instead of ?), but I would like to improve it since it executes two times the same Regex.

I removed all the documentation to let it fit here, that's why it's without documentation.

BananaQuery

public class BananaQuery {

    /* package */ BananaQuery() {
    }

    public static BananaInsert insert(SQLiteDatabase database) {
        return new BananaInsert(checkNotNull(database));
    }

    public static BananaSelect select(SQLiteDatabase database) {
        return new BananaSelect(checkNotNull(database));
    }

    public static BananaUpdate update(SQLiteDatabase database) {
        return new BananaUpdate(checkNotNull(database));
    }

    /* package */ static <T> T checkNotNull(T element) {
        if (element == null) throw new NullPointerException("Null not valid here");
        return element;
    }

    /* package */ static Pair<String, List<String>> parseNamedArguments(String condition, Map<String, Object> bindParams) {
        List<String> argumentsPair = new LinkedList<String>();

        Pattern pattern = Pattern.compile(":(\\w+)");
        Matcher matcher = pattern.matcher(condition);

        while (matcher.find()) {
            String identifier = matcher.group(1);

            if (!bindParams.containsKey(identifier)) {
                throw new IllegalStateException("Named argument " + identifier + " is not binded.");
            }

            argumentsPair.add(bindParams.get(identifier).toString());
        }
        condition = condition.replaceAll(":(\\w+)", "?");

        return Pair.create(condition, argumentsPair);
    }
}

BananaSelect

public class BananaSelect {

    private final SQLiteDatabase database;
    private String tableName;
    private List<String> columns = new LinkedList<String>();
    private List<String> whereConditions = new LinkedList<String>();
    private StringBuilder groupByConditions = new StringBuilder();
    private Map<String, Object> bindParams = new HashMap<String, Object>();
    private StringBuilder orderByConditions = new StringBuilder();
    private int limitOffset;
    private int limitCount;
    private String having;
    private boolean distinct;
    private SQLiteDatabase.CursorFactory cursorFactory;

    /* package */ BananaSelect(SQLiteDatabase database) {
        this.database = database;
    }

    public BananaSelect column(String columnName) {
        columns.add(columnName);
        return this;
    }

    public BananaSelect from(String tableName) {
        this.tableName = BananaQuery.checkNotNull(tableName);
        return this;
    }

    public BananaSelect where(String condition) {
        whereConditions.add(condition);
        return this;
    }

    public BananaSelect bind(String key, Object value) {
        bindParams.put(key, value);
        return this;
    }

    public BananaSelect groupBy(String column) {
        // TODO Implement named arguments here too?
        groupByConditions.append(column);
        return this;
    }

    public BananaSelect orderBy(String column) {
        // TODO Implement named arguments here too?
        orderByConditions.append(column);
        return this;
    }

    public BananaSelect having(String having) {
        this.having = having;
        return this;
    }

    public BananaSelect limit(int offset) {
        return limit(offset, 0);
    }

    public BananaSelect limit(int offset, int count) {
        limitOffset = offset;
        limitCount = count;
        return this;
    }

    public BananaSelect onlyUniqueRows() {
        distinct = true;
        return this;
    }

    public BananaSelect useCursorFactory(SQLiteDatabase.CursorFactory cursorFactory) {
        this.cursorFactory = cursorFactory;
        return this;
    }

    public Cursor execute() {
        StringBuilder where = new StringBuilder();
        List<String> selectionArguments = new LinkedList<String>();
        String[] argumentSelection = new String[0];

        if (whereConditions.size() > 0) {
            for (String condition : whereConditions) {
                Pair<String, List<String>> pair = BananaQuery.parseNamedArguments(condition, bindParams);

                where.append(pair.first).append(" AND ");
                selectionArguments.addAll(pair.second);
            }

            // 4 => AND  chars
            where.setLength(where.length() - 4);

            argumentSelection = new String[selectionArguments.size()];
            selectionArguments.toArray(argumentSelection);
        }

        String[] columns = new String[this.columns.size()];
        this.columns.toArray(columns);

        return database.queryWithFactory(
                cursorFactory,
                distinct,
                tableName,
                columns,
                where.toString(),
                argumentSelection,
                groupByConditions.length() == 0 ? null : groupByConditions.toString(),
                having,
                orderByConditions.length() == 0 ? null : orderByConditions.toString(),
                limitOffset == 0 ? "" : (limitCount == 0 ? String.valueOf(limitOffset) : String.valueOf(limitOffset)
                        + ", " + limitCount)
        );
    }
}

BananaInsert

public class BananaInsert {

    private final SQLiteDatabase database;
    private String tableName;
    private ContentValues values = new ContentValues();
    private int conflictAlgorithm = SQLiteDatabase.CONFLICT_NONE;

    /* package */ BananaInsert(SQLiteDatabase database) {
        this.database = database;
    }

    public BananaInsert into(String tableName) {
        this.tableName = BananaQuery.checkNotNull(tableName);
        return this;
    }

    public BananaInsert value(String column, int value) {
        values.put(column, value);
        return this;
    }

    public BananaInsert value(String column, float value) {
        values.put(column, value);
        return this;
    }

    public BananaInsert value(String column, double value) {
        values.put(column, value);
        return this;
    }

    public BananaInsert value(String column, byte[] value) {
        values.put(column, value);
        return this;
    }

    public BananaInsert value(String column, String value) {
        values.put(column, value);
        return this;
    }

    public BananaInsert value(String column, byte value) {
        values.put(column, value);
        return this;
    }

    public BananaInsert value(String column, long value) {
        values.put(column, value);
        return this;
    }

    public BananaInsert value(String column, boolean value) {
        values.put(column, value);
        return this;
    }

    public BananaInsert value(String column) {
        values.putNull(column);
        return this;
    }

    public BananaInsert rollbackOnConflict() {
        this.conflictAlgorithm = SQLiteDatabase.CONFLICT_ROLLBACK;
        return this;
    }

    public BananaInsert abortOnConflict() {
        this.conflictAlgorithm = SQLiteDatabase.CONFLICT_ABORT;
        return this;
    }

    public BananaInsert failOnConflict() {
        this.conflictAlgorithm = SQLiteDatabase.CONFLICT_FAIL;
        return this;
    }

    public BananaInsert ignoreOnConflict() {
        this.conflictAlgorithm = SQLiteDatabase.CONFLICT_IGNORE;
        return this;
    }

    public BananaInsert replaceOnConflict() {
        this.conflictAlgorithm = SQLiteDatabase.CONFLICT_REPLACE;
        return this;
    }

    public BananaInsert nothingOnConflict() {
        this.conflictAlgorithm = SQLiteDatabase.CONFLICT_NONE;
        return this;
    }

    public BananaInsert conflictAlgorithm(int conflictAlgorithm) {
        this.conflictAlgorithm = conflictAlgorithm;
        return this;
    }

    public long execute() {
        if (tableName == null) {
            throw new IllegalStateException("Table name cannot be null!");
        }

        return database.insertWithOnConflict(tableName, null, values, conflictAlgorithm);
    }

    public long executeOrThrow() {
        if (tableName == null) {
            throw new IllegalStateException("Table name cannot be null!");
        }

        return database.insertOrThrow(tableName, null, values);
    }
}

BananaUpdate

public class BananaUpdate {

    private final SQLiteDatabase database;
    private String tableName;
    private ContentValues values = new ContentValues();
    private int conflictAlgorithm = SQLiteDatabase.CONFLICT_NONE;
    private List<String> whereConditions = new LinkedList<String>();
    private Map<String, Object> bindParams = new HashMap<String, Object>();

    /* package */ BananaUpdate(SQLiteDatabase database) {
        this.database = database;
    }

    public BananaUpdate into(String tableName) {
        this.tableName = BananaQuery.checkNotNull(tableName);
        return this;
    }

    public BananaUpdate value(String column, int value) {
        values.put(column, value);
        return this;
    }

    public BananaUpdate value(String column, float value) {
        values.put(column, value);
        return this;
    }

    public BananaUpdate value(String column, double value) {
        values.put(column, value);
        return this;
    }

    public BananaUpdate value(String column, byte[] value) {
        values.put(column, value);
        return this;
    }

    public BananaUpdate value(String column, String value) {
        values.put(column, value);
        return this;
    }

    public BananaUpdate value(String column, byte value) {
        values.put(column, value);
        return this;
    }

    public BananaUpdate value(String column, long value) {
        values.put(column, value);
        return this;
    }

    public BananaUpdate value(String column, boolean value) {
        values.put(column, value);
        return this;
    }

    public BananaUpdate value(String column) {
        values.putNull(column);
        return this;
    }

    public BananaUpdate rollbackOnConflict() {
        this.conflictAlgorithm = SQLiteDatabase.CONFLICT_ROLLBACK;
        return this;
    }

    public BananaUpdate abortOnConflict() {
        this.conflictAlgorithm = SQLiteDatabase.CONFLICT_ABORT;
        return this;
    }

    public BananaUpdate failOnConflict() {
        this.conflictAlgorithm = SQLiteDatabase.CONFLICT_FAIL;
        return this;
    }

    public BananaUpdate ignoreOnConflict() {
        this.conflictAlgorithm = SQLiteDatabase.CONFLICT_IGNORE;
        return this;
    }

    public BananaUpdate replaceOnConflict() {
        this.conflictAlgorithm = SQLiteDatabase.CONFLICT_REPLACE;
        return this;
    }

    public BananaUpdate nothingOnConflict() {
        this.conflictAlgorithm = SQLiteDatabase.CONFLICT_NONE;
        return this;
    }

    public BananaUpdate conflictAlgorithm(int conflictAlgorithm) {
        this.conflictAlgorithm = conflictAlgorithm;
        return this;
    }

    public BananaUpdate where(String condition) {
        whereConditions.add(condition);
        return this;
    }

    public BananaUpdate bind(String key, Object value) {
        bindParams.put(key, value);
        return this;
    }

    public int execute() {
        if (tableName == null) {
            throw new IllegalStateException("Table name cannot be null!");
        }

        StringBuilder where = new StringBuilder();
        List<String> selectionArguments = new LinkedList<String>();
        String[] argumentSelection = new String[0];

        if (whereConditions.size() > 0) {
            for (String condition : whereConditions) {
                Pair<String, List<String>> pair = BananaQuery.parseNamedArguments(condition, bindParams);

                where.append(pair.first).append(" AND ");
                selectionArguments.addAll(pair.second);
            }

            // 4 => AND  chars
            where.setLength(where.length() - 4);

            argumentSelection = new String[selectionArguments.size()];
            selectionArguments.toArray(argumentSelection);
        }


        return database.updateWithOnConflict(tableName, values, where.toString(), argumentSelection, conflictAlgorithm);
    }
}

Here's a way to use its methods:

int count = BananaQuery.update(database)
    .value("a", random.nextInt(10))
    .into("test")
    .where("id = :id")
    .bind("id", id)
    .execute();

Cursor cursor = BananaQuery.select(database)
        .column("a")
        .column("b")
        .column("c")
        .column("id")
        .from("test")
        .where("a < :valore")
        .limit(1, 5)
        .bind("colonna", colonna)
        .bind("valore", valore)
        .execute();

long id = BananaQuery.insert(database)
        .into("test")
        .value("a", random.nextInt(10))
        .value("b", random.nextInt(10))
        .value("c", random.nextInt(10))
        .execute();

What about names? I tried to remember SQL syntax, but I think I failed.

What about bind? It remembers PHP, so I think it's OK, but what about with?

What could be a better message for

if (element == null) throw new NullPointerException("Null not valid here");

"Null not valid here" is not the best. Allows the method to provide one message?

\$\endgroup\$
4
\$\begingroup\$

Pattern Matching

Let's focus on just this one method, for a moment:

static Pair<String, List<String>> parseNamedArguments(String condition, Map<String, Object> bindParams) {
    List<String> argumentsPair = new LinkedList<String>();

    Pattern pattern = Pattern.compile(":(\\w+)");
    Matcher matcher = pattern.matcher(condition);

    while (matcher.find()) {
        String identifier = matcher.group(1);

        if (!bindParams.containsKey(identifier)) {
            throw new IllegalStateException("Named argument " + identifier + " is not binded.");
        }

        argumentsPair.add(bindParams.get(identifier).toString());
    }
    condition = condition.replaceAll(":(\\w+)", "?");

    return Pair.create(condition, argumentsPair);
}

First, the slow part of this method is probably compiling the pattern, and you do it multiple times, each time the method is called.

First, you compile it explicitly, second, you do a replaceAll later on with the same pattern.

How can this be improved?

Well, Patterns are thread-safe, and reusable. Let's move the pattern compile to be a static, final field:

private static final Pattern COLONWORD = Pattern.compile(":(\\w+)");

Now there is no need to ever compile that again.

The second time the pattern is used, it is used to replace the parameter named placeholders with the ? placeholder. How can this be improved? By doing the replacements during the initial matcher loop. Consider the following code:

private static final Pattern COLONWORD = Pattern.compile(":(\\w+)");

static Pair<String, List<String>> parseNamedArguments(String condition, Map<String, Object> bindParams) {
    List<String> argumentsPair = new LinkedList<String>();

    Matcher matcher = COLONWORD.matcher(condition);

    StringBuffer buffer = new StringBuffer(condition.length());
    while (matcher.find()) {
        String identifier = matcher.group(1);

        if (!bindParams.containsKey(identifier)) {
            throw new IllegalStateException("Named argument " + identifier + " is not binded.");
        }

        argumentsPair.add(bindParams.get(identifier).toString());

        matcher.appendReplacement(buffer, "?");
    }

    matcher.appendTail(buffer);
    return Pair.create(buffer.toString(), argumentsPair);
}
\$\endgroup\$
  • \$\begingroup\$ Thanks, appendReplacement was what i needed to avoid replaceAll (using the StringBuilder and then replace(start, end)) led to bugs. \$\endgroup\$ – Marco Acierno Jul 4 '14 at 21:13
  • \$\begingroup\$ I consider it a small bug in the Matcher that you have to use a StringBuffer, and not a StringBuilder, but it is better than the replaceAll. \$\endgroup\$ – rolfl Jul 4 '14 at 21:14
  • \$\begingroup\$ What bug? ----- \$\endgroup\$ – Marco Acierno Jul 4 '14 at 21:15
  • \$\begingroup\$ appendReplacement() JavaDoc <-- should use a StringBuilder, not a StringBuffer. StringBuilder is faster in a single-thread situation \$\endgroup\$ – rolfl Jul 4 '14 at 21:17
  • \$\begingroup\$ I didn't noticed that the method accepts only StringBuffer. My error. \$\endgroup\$ – Marco Acierno Jul 4 '14 at 21:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.