1. Initialize the database
First, you need to get the database instance:
// Create or open a databaseDatabaseHelper dbHelper = new DatabaseHelper(context); SQLiteDatabase db = (); // If you only need to read the data, you can use// SQLiteDatabase db = ();
2. Insert data (Create)
Method 1: Use ContentValues + insert()
ContentValues values = new ContentValues();
("name", "Zhang San");
("age", 25);
("email", "zhangsan@");
// Insert data, return the ID of the new row, and return -1 if the insert failslong newRowId = (
"users", // Table name null, // When values are empty, specify the column name that can be null values // Data to be inserted);
if(newRowId == -1) {
// Insert failure processing} else {
// Insert successfully, newRowId is the primary key value of the new record}
Method 2: Execute SQL directly
String sql = "INSERT INTO users (name, age, email) VALUES ('Zhang San', 25, 'zhangsan@')";
(sql);
3. Query data (Read)
Method 1: Use query() method
// Query all columnsCursor cursor = (
"users", // Table name null, // The column name array to be queryed, null means all columns null, // WHERE clause, null means none null, // Parameters of WHERE clause null, // GROUP BY clause null, // HAVING clause "age DESC" // ORDER BY clause);
// Query with conditionsString[] columns = {"name", "age"};
String selection = "age > ?";
String[] selectionArgs = {"20"};
String orderBy = "name ASC";
Cursor cursor = (
"users",
columns,
selection,
selectionArgs,
null,
null,
orderBy
);
// traversal Cursorwhile (()) {
String name = (("name"));
int age = (("age"));
// Processing data...}
// Remember to close Cursor();
Method 2: Use rawQuery() to execute raw SQL
String sql = "SELECT * FROM users WHERE age > ?";
Cursor cursor = (sql, new String[]{"20"});
// Handle Cursor...();
4. Update data (Update)
Method 1: Use ContentValues + update()
ContentValues values = new ContentValues();
("age", 26);
("email", "new_email@");
// Update conditionsString whereClause = "id = ?";
String[] whereArgs = {"1"};
// Update data and return the number of affected rowsint count = (
"users", // Table name values, // New value whereClause, // WHERE clause whereArgs // Parameters of WHERE clause);
Method 2: Execute SQL directly
String sql = "UPDATE users SET age = 26, email = 'new_email@' WHERE id = 1"; (sql);
5. Delete data (Delete)
Method 1: Use delete() method
// Delete conditionsString whereClause = "id = ?";
String[] whereArgs = {"1"};
// Delete data and return the number of deleted rowsint count = (
"users", // Table name whereClause, // WHERE clause whereArgs // Parameters of WHERE clause);
Method 2: Execute SQL directly
String sql = "DELETE FROM users WHERE id = 1"; (sql);
6. Transaction processing
For batch operations, using transactions can improve performance and ensure data consistency:
();
try {
// Perform multiple database operations for (int i = 0; i < 100; i++) {
ContentValues values = new ContentValues();
("name", "User " + i);
("age", i % 50);
("users", null, values);
}
// Mark the transaction as successful ();
} catch (Exception e) {
// Handle exceptions} finally {
// End the transaction ();
}
7. Close the database
When the database connection is no longer needed, it should be closed:
(); ();
8. Best Practices
- Always close Cursor and database connections: Avoid memory leaks
- Using transaction processing batch operations: Improve performance
- Avoid time-consuming operations on the main thread: Database operations should be performed in child thread
- Use parameterized query: Prevent SQL injection
- Consider using Room: The official recommended SQLite ORM library by Android
Complete example
public class UserDao {
private DatabaseHelper dbHelper;
public UserDao(Context context) {
dbHelper = new DatabaseHelper(context);
}
// Add user public long addUser(User user) {
SQLiteDatabase db = ();
ContentValues values = new ContentValues();
("name", ());
("age", ());
("email", ());
long id = ("users", null, values);
();
return id;
}
// Get all users public List<User> getAllUsers() {
List<User> userList = new ArrayList<>();
SQLiteDatabase db = ();
Cursor cursor = ("users", null, null, null, null, null, "name ASC");
while (()) {
User user = new User();
((("id")));
((("name")));
((("age")));
((("email")));
(user);
}
();
();
return userList;
}
// Update user public int updateUser(User user) {
SQLiteDatabase db = ();
ContentValues values = new ContentValues();
("name", ());
("age", ());
("email", ());
int count = (
"users",
values,
"id = ?",
new String[]{(())}
);
();
return count;
}
// Delete the user public int deleteUser(int userId) {
SQLiteDatabase db = ();
int count = (
"users",
"id = ?",
new String[]{(userId)}
);
();
return count;
}
// Close the database help class public void close() {
();
}
}
The above is the detailed operation of using SQLiteDatabase to add, delete, modify and search in Android. With Android development, Google recommends using the Room persistence library instead of direct use of SQLiteDatabase, which provides a cleaner API and compile-time SQL checking.
Dynamic SQLite help class implementation
The following is an improved SQLite help class that does not fix tables and column structures, supports dynamic creation of tables and performs CRUD operations:
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
public class DynamicDBHelper extends SQLiteOpenHelper {
private static final String TAG = "DynamicDBHelper";
private static final String DATABASE_NAME = "dynamic_db";
private static final int DATABASE_VERSION = 1;
private static DynamicDBHelper instance;
private SQLiteDatabase db;
// Table structure cache: key=table name, value=column definition map (column name->type) private Map<String, Map<String, String>> tableSchemas = new HashMap<>();
// Singleton mode public static synchronized DynamicDBHelper getInstance(Context context) {
if (instance == null) {
instance = new DynamicDBHelper(());
}
return instance;
}
private DynamicDBHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
db = getWritableDatabase();
}
@Override
public void onCreate(SQLiteDatabase db) {
// Don't create fixed tables here, dynamically create them by calling the createTable method externally }
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// Table structure can be migrated according to version number // More complex upgrade logic is needed in actual projects for (String tableName : ()) {
("DROP TABLE IF EXISTS " + tableName);
}
();
}
/**
* Dynamically create tables
* @param tableName table name
* @param columns column definition map (column name -> data type, such as "TEXT", "INTEGER", etc.)
* @param primaryKey primary key column name (optional)
*/
public void createTable(String tableName, Map<String, String> columns, String primaryKey) {
if (tableExists(tableName)) {
(TAG, "Table " + tableName + " already exists");
return;
}
StringBuilder sql = new StringBuilder("CREATE TABLE " + tableName + " (");
// Add column definition for (<String, String> entry : ()) {
String columnName = ();
String columnType = ();
(columnName).append(" ").append(columnType).append(", ");
}
// Add primary key if (primaryKey != null && !()) {
("PRIMARY KEY (").append(primaryKey).append(")");
} else {
// Remove the last comma and space (() - 2, ());
}
(")");
(());
(tableName, new HashMap<>(columns));
(TAG, "Table created: " + tableName);
}
/**
* Check whether the table exists
*/
public boolean tableExists(String tableName) {
Cursor cursor = (
"SELECT name FROM sqlite_master WHERE type='table' AND name=?",
new String[]{tableName});
boolean exists = () > 0;
();
return exists;
}
/**
* Insert data
* @param tableName table name
* @param values Data key-value pairs
* @return ID of the newly inserted row
*/
public long insert(String tableName, ContentValues values) {
validateTable(tableName);
return (tableName, null, values);
}
/**
* Insert data in batches
* @param tableName table name
* @param valuesList Data key-value pair list
* @return Number of successfully inserted
*/
public int bulkInsert(String tableName, List<ContentValues> valuesList) {
validateTable(tableName);
int count = 0;
try {
();
for (ContentValues values : valuesList) {
if ((tableName, null, values) != -1) {
count++;
}
}
();
} finally {
();
}
return count;
}
/**
* Query data
* @param tableName table name
* @param columns to query
* @param selection WHERE condition
* @param selectionArgs WHERE conditional parameters
* @param orderBy sort
* @return Cursor for query results
*/
public Cursor query(String tableName, String[] columns, String selection,
String[] selectionArgs, String orderBy) {
validateTable(tableName);
return (tableName, columns, selection, selectionArgs,
null, null, orderBy);
}
/**
* Query all data
* @param tableName table name
* @return Cursor containing all rows
*/
public Cursor queryAll(String tableName) {
return query(tableName, null, null, null, null);
}
/**
* Update data
* @param tableName table name
* @param values The value to be updated
* @param selection WHERE condition
* @param selectionArgs WHERE conditional parameters
* @return Number of affected rows
*/
public int update(String tableName, ContentValues values, String selection,
String[] selectionArgs) {
validateTable(tableName);
return (tableName, values, selection, selectionArgs);
}
/**
* Delete data
* @param tableName table name
* @param selection WHERE condition
* @param selectionArgs WHERE conditional parameters
* @return Number of affected rows
*/
public int delete(String tableName, String selection, String[] selectionArgs) {
validateTable(tableName);
return (tableName, selection, selectionArgs);
}
/**
* Delete all data in the table
* @param tableName table name
* @return Number of affected rows
*/
public int deleteAll(String tableName) {
return delete(tableName, null, null);
}
/**
* Delete table
* @param tableName table name
*/
public void dropTable(String tableName) {
if (tableExists(tableName)) {
("DROP TABLE " + tableName);
(tableName);
(TAG, "Table dropped: " + tableName);
}
}
/**
* Add column
* @param tableName table name
* @param columnName columnname
* @param columnType Column type
*/
public void addColumn(String tableName, String columnName, String columnType) {
validateTable(tableName);
if (!(tableName).containsKey(columnName)) {
("ALTER TABLE " + tableName + " ADD COLUMN " + columnName + " " + columnType);
(tableName).put(columnName, columnType);
(TAG, "Column " + columnName + " added to table " + tableName);
} else {
(TAG, "Column " + columnName + " already exists in table " + tableName);
}
}
/**
* Execute raw SQL query
* @param sql SQL statement
* @param selectionArgs Parameters
* @return Results Cursor
*/
public Cursor rawQuery(String sql, String[] selectionArgs) {
return (sql, selectionArgs);
}
/**
* Execute the original SQL statement
* @param sql SQL statement
*/
public void execSQL(String sql) {
(sql);
}
/**
* Close the database connection
*/
public void closeDB() {
if (db != null && ()) {
();
}
}
/**
* Verify that the table exists
*/
private void validateTable(String tableName) {
if (!(tableName)) {
throw new IllegalArgumentException("Table " + tableName + " does not exist");
}
}
/**
* Get the column information of the table
* @param tableName table name
* @return Map of column name to type
*/
public Map<String, String> getTableColumns(String tableName) {
validateTable(tableName);
return new HashMap<>((tableName));
}
}
Example of usage
// InitializationDynamicDBHelper dbHelper = (context);
// Create tableMap<String, String> columns = new HashMap<>();
("id", "INTEGER");
("name", "TEXT");
("age", "INTEGER");
("email", "TEXT");
("users", columns, "id");
// Insert dataContentValues values = new ContentValues();
("name", "Zhang San");
("age", 25);
("email", "zhangsan@");
long id = ("users", values);
// Query dataCursor cursor = ("users", null, "age > ?", new String[]{"20"}, "name ASC");
while (()) {
String name = (("name"));
int age = (("age"));
// Processing data...}
();
// Update dataContentValues updateValues = new ContentValues();
("age", 26);
("users", updateValues, "name = ?", new String[]{"Zhang San"});
// Delete data("users", "age < ?", new String[]{"18"});
// Add new column("users", "address", "TEXT");
// Delete the table("users");
Features
- Dynamic table structure: No fixed tables and columns, you can create tables of any structure at runtime
- Complete CRUD operation: Provide complete operations such as insertion, query, update, and deletion
- Bulk operation support: Support batch insertion of data
- Table structure cache: Cache table structure information to avoid frequent query of system tables
- Type safety: Ensure that the table and column of the operation exist
- Transaction support: Bulk operations use transactions to ensure data consistency
- Raw SQL support: The original SQL statement can be executed directly
This implementation can be further expanded as needed, such as adding index support, more complex table structure changes and other functions.
The above is the detailed content of the operation tutorial for Android using SQLiteDatabase Add, Deletion, Revise (CRUD). For more information about Android SQLiteDatabase Add, Deletion, Revise (CRUD). Please pay attention to my other related articles!