-
Notifications
You must be signed in to change notification settings - Fork 10
SQLite Executor
landawn edited this page May 1, 2018
·
2 revisions
SQLiteExecutor is a wrapper of SQLiteDatabase. Just like SQLExecutor, it makes things much easier to operate data with SQLite on Android by the straight support of Entity/Map/Query..., and almost all the java basic types: boolean...double...Date...String...Map...Entity.... Here is a simple sample, comparing to the implementation by SQLiteDatabase:
- CRUD by SQLiteExecutor
public void test_crudBySQLiteExecutor() {
Account account = createAccount();
// create
long id = sqliteExecutor.insert(account);
// read
String sql_getById = NE.select(ID, FIRST_NAME, LAST_NAME).from(Account.class).where(L.eq(ID, id)).sql();
Account dbAccount = sqliteExecutor.queryForEntity(Account.class, sql_getById, id);
// update
dbAccount.setFirstName("newFirstName");
sqliteExecutor.update("account", dbAccount, L.eq(ID, id));
// delete
sqliteExecutor.delete("account", L.eq(FIRST_NAME, dbAccount.getFirstName()));
// check
dbAccount = sqliteExecutor.queryForEntity(Account.class, sql_getById, id);
assertNull(dbAccount);
}
- CRUD by SQLiteDatabase
public void test_crudBySQLiteDatabase() {
Account account = createAccount();
ContentValues contentValues = new ContentValues();
contentValues.put("gui", account.getGUI());
contentValues.put("first_name", account.getFirstName());
contentValues.put("last_name", account.getLastName());
contentValues.put("last_update_time", N.stringOf(account.getLastUpdateTime()));
contentValues.put("create_name", N.stringOf(account.getCreateTime()));
// create
long id = sqliteDatabase.insert("account", null, contentValues);
Account dbAccount = null;
// read
String sql_getById = NE.select(ID, FIRST_NAME, LAST_NAME).from(Account.class).where(L.eq(ID, id)).sql();
Cursor cursor = sqliteDatabase.rawQuery(sql_getById, N.asArray(N.stringOf(id)));
try {
if (cursor.moveToNext()) {
dbAccount = new Account();
int columnIndex = 0;
dbAccount.setId(cursor.getLong(columnIndex++));
dbAccount.setFirstName(cursor.getString(columnIndex++));
dbAccount.setLastName(cursor.getString(columnIndex++));
} else {
dbAccount = null;
}
} finally {
cursor.close();
}
// update
dbAccount.setFirstName("newFirstName");
contentValues = new ContentValues();
contentValues.put("first_name", dbAccount.getFirstName());
sqliteDatabase.update("account", contentValues, "id = ?", N.asArray(N.stringOf(id)));
// delete
sqliteDatabase.delete("account", "first_name = ?", N.asArray(dbAccount.getFirstName()));
// check
cursor = sqliteDatabase.rawQuery(sql_getById, N.asArray(N.stringOf(id)));
try {
if (cursor.moveToNext()) {
dbAccount = new Account();
int columnIndex = 0;
dbAccount.setId(cursor.getLong(columnIndex++));
dbAccount.setFirstName(cursor.getString(columnIndex++));
dbAccount.setLastName(cursor.getString(columnIndex++));
} else {
dbAccount = null;
}
} finally {
cursor.close();
}
assertNull(dbAccount);
}
As you see, how simple the implementation by SQLiteExecutor is. Here are the steps to program with SQLiteExecutor:
- Prepare the database and tables with the consistent naming convention. Any java types not supported by SQLite should be defined as 'TEXT'.
- Generate the entity objects by Code Generation.
- You will be ready to operate data by Entity/Map directly and query by sql. Parameterized SQL with named parameters is supported as well.
- How to Learn/Use the APIs correctly and efficiently
- Programming in RDBMS with Jdbc/PreparedQuery/SQLExecutor/Mapper/Dao
- JSON/XML Parser
- SQLite Executor
- SQL Executor
- SQL Builder
- SQL Mapper
- DataSet
- JdbcUtil/CSVUtil
- IOUtil
- PrimitiveList
- Profiler
- Http Client
- Web Services
- Programming in Android
- Parse/Analyze/Operate (Big) Data (on N servers in parallel)
- Code Generation
- Introduction to JDBC
- Naming Convention
- Partitioning/Distribution
- SQL/NoSQL
- Model/Entity