众所周知,Android开发中使用数据库Sqlite. 这里简单记录一下SQLite的使用。
这里按CRUD顺序进行简单记录下:
直接贴操作代码,文字叙述没什么用!^_^
首先是DatabaseHelper
public class DatabaseHelper extends SQLiteOpenHelper {
private static final int DB_VERSION = 7;//数据库版本,进行手动更新
private static final String DB_NAME = "SQLiteTest.db";
private static final String TAG = "DatabaseHelper.class";
private static final String SQLITETABLE = "sqliteTable";
private static final String AGE = "age";
private static final String NAME = "name";
public DatabaseHelper(Context context) {
this(context, DB_NAME, DB_VERSION);
}
public DatabaseHelper(Context context, int version) {
this(context, DB_NAME, version);
}
public DatabaseHelper(Context context, String name, int version) {
this(context, name, null, version);
}
public DatabaseHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
super(context, name, factory, version);
}
@Override
public void onCreate(SQLiteDatabase db) {
String sql = "create table if not exists " + SQLITETABLE + "(id INTEGER primary key autoincrement," + NAME + " TEXT," + AGE + " TEXT)";
db.execSQL(sql);
Log.d(TAG, "onCreate: create database");
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("drop table if exists sqliteTable");
onCreate(db);
Log.d(TAG, "upgrade database");
}
}
Create
我这里将所有方法写到DatabaseDao类里面
public DatabaseDao(Context context) {
databaseHelper = new DatabaseHelper(context);
}
在需要调用的类中直接
new DatabaseDao(this)
即可初始化创建数据库表
Insert
public void insert(String x, String age) {
String sql = "insert into " + SQLITETABLE + "(name,age) values(?,?)";
sqLiteDatabase = databaseHelper.getWritableDatabase();
// String x = "x";
sqLiteDatabase.execSQL(sql, new String[]{x, age});
}
总结:
insert into tableName(column1,column2...) values(?,?...)
Retrieve
dataBeans.clear();
String s = "select * from " + SQLITETABLE + " where name =?";
sqLiteDatabase = databaseHelper.getWritableDatabase();
Cursor cursor = sqLiteDatabase.rawQuery(s, new String[]{name});
if (cursor.moveToFirst()) {
while (cursor.moveToNext()) {
String name1 = cursor.getString(cursor.getColumnIndex("name"));
String age1 = cursor.getString(cursor.getColumnIndex("age"));
Log.d(TAG, "retrieveName: name=" + name1 + ",age=" + age1);
DataBean dataBean = new DataBean();
dataBean.setName(name1);
dataBean.setAge(age1);
dataBeans.add(dataBean);
}
}
return dataBeans;
}
public List<DataBean> retrieveAge(List<DataBean> dataBeans, String age) {
String sql = "select * from " + SQLITETABLE + " where age=?";
sqLiteDatabase = databaseHelper.getWritableDatabase();
Cursor cursor = sqLiteDatabase.rawQuery(sql, new String[]{age});
if (cursor.moveToFirst()) {
while (cursor.moveToNext()) {
String name1 = cursor.getString(cursor.getColumnIndex("name"));
String age1 = cursor.getString(cursor.getColumnIndex("age"));
Log.d(TAG, "retrieveAge: name=" + name1 + ",age=" + age1);
DataBean dataBean = new DataBean();
dataBean.setName(name1);
dataBean.setAge(age1);
dataBeans.add(dataBean);
}
}
return dataBeans;
总结:
select * from tableName where column=?
通过SQL语句筛选,通过sqliteDatabase进行rawQuery进行相应查询
sqLiteDatabase.rawQuery(sql, new String[]{age});
public void updateName(String age, String newName) {
String sql = "update sqliteTable set name='" + newName + "' where age='" + age + "'";
sqLiteDatabase = databaseHelper.getWritableDatabase();
sqLiteDatabase.execSQL(sql);
}
总结:
update tableName set column = ‘x’ where column2='y'
需要指出的是,我在使用这个语句的时候需要加‘’才成功,不然会报错。
Delete
public void deleteName(String name) {
String sql = "delete from " + SQLITETABLE + " where name='" + name + "'";
sqLiteDatabase = databaseHelper.getWritableDatabase();
sqLiteDatabase.execSQL(sql);
}
public void deleteAge(String age) {
String sql = "delete from " + SQLITETABLE + " where age=" + age + "";
sqLiteDatabase = databaseHelper.getWritableDatabase();
sqLiteDatabase.execSQL(sql);
}
总结:
delete from tableName where column='x'
需要指出的是,我在使用这个语句的时候需要加‘’才成功,不然会报错。
结语,这篇文章没什么东西,都是通过简单的SQL语句实现,当然对于insert,update等也可以通过ContentValues来实现,我这里没那样写。
关于greenDao的使用请参考 SQLitedatabase之GreenDao进行CRUD