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:
- Everything
- I would like to avoid to repeat some blocks of code, see
valuemethods They are the same in the 3 classes! too forexecutemethod. Ah, and for the algorithm methods too. - Named arguments: I created a way to allow to use named arguments (
:nameinstead 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?
