四、常用数据库操作
1.创建表
private void CreateTable() {
SQLiteDatabase db = mOpenHelper.getWritableDatabase();//获得一个SQLiteDatabase实例
String sql = "CREATE TABLE " + TABLE_NAME + " (" + TITLE + " " + BODY + " " + ");";
try {
db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
db.execSQL(sql);
setTitle("数据表成功重建");
} catch (SQLException e) {
setTitle("数据表重建错误");
}
}
SQLiteDatabase db = mOpenHelper.getWritableDatabase();//获得一个SQLiteDatabase实例
String sql = "CREATE TABLE " + TABLE_NAME + " (" + TITLE + " " + BODY + " " + ");";
try {
db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
db.execSQL(sql);
setTitle("数据表成功重建");
} catch (SQLException e) {
setTitle("数据表重建错误");
}
}
2.删除表
private void dropTable() {
SQLiteDatabase db = mOpenHelper.getWritableDatabase();
String sql = "drop table " + TABLE_NAME;
try {
db.execSQL(sql);
setTitle("数据表成功删除：" + sql);
} catch (SQLException e) {
setTitle("数据表删除错误");
}
}
SQLiteDatabase db = mOpenHelper.getWritableDatabase();
String sql = "drop table " + TABLE_NAME;
try {
db.execSQL(sql);
setTitle("数据表成功删除：" + sql);
} catch (SQLException e) {
setTitle("数据表删除错误");
}
}
3.插入数据
private void insertItem() {
SQLiteDatabase db = mOpenHelper.getWritableDatabase();
String sql1 = "insert into " + TABLE_NAME + " (" + TITLE + ", " + BODY
+ ") values('haiyang', 'android的发展真是迅速啊');";
String sql2 = "insert into " + TABLE_NAME + " (" + TITLE + ", " + BODY
+ ") values('icesky', 'Ophone的发展真是迅速啊');";
try {
db.execSQL(sql1);
db.execSQL(sql2);
setTitle("插入两条数据成功");
} catch (SQLException e) {
setTitle("插入两条数据失败");
}
}
SQLiteDatabase db = mOpenHelper.getWritableDatabase();
String sql1 = "insert into " + TABLE_NAME + " (" + TITLE + ", " + BODY
+ ") values('haiyang', 'android的发展真是迅速啊');";
String sql2 = "insert into " + TABLE_NAME + " (" + TITLE + ", " + BODY
+ ") values('icesky', 'Ophone的发展真是迅速啊');";
try {
db.execSQL(sql1);
db.execSQL(sql2);
setTitle("插入两条数据成功");
} catch (SQLException e) {
setTitle("插入两条数据失败");
}
}
4.删除数据
private void deleteItem() {
try {
SQLiteDatabase db = mOpenHelper.getWritableDatabase();
db.delete(TABLE_NAME, " title = 'haiyang'", null);
setTitle("删除title为haiyang的一条记录");
} catch (SQLException e) {
}
}
try {
SQLiteDatabase db = mOpenHelper.getWritableDatabase();
db.delete(TABLE_NAME, " title = 'haiyang'", null);
setTitle("删除title为haiyang的一条记录");
} catch (SQLException e) {
}
}
5.显示记录
流程为：获取数据库实例，设置指针，显示记录数，移动指针，遍历并显示数据。
private void showItems() {
SQLiteDatabase db = mOpenHelper.getReadableDatabase();
String col[] = { TITLE, BODY };
Cursor cur = db.query(TABLE_NAME, col, null, null, null, null, null);
Integer num = cur.getCount();
setTitle(Integer.toString(num) + " 条记录");
cur.moveToFirst();
tv.setText("");
while (cur.getPosition()!=cur.getCount())
{
tv.append(Integer.toString(cur.getPosition())+" , "+cur.getString(cur.getColumnIndex("title"))+" , "+cur.getString(cur.getColumnIndex("body"))+"\n");
cur.moveToNext();
}
}
SQLiteDatabase db = mOpenHelper.getReadableDatabase();
String col[] = { TITLE, BODY };
Cursor cur = db.query(TABLE_NAME, col, null, null, null, null, null);
Integer num = cur.getCount();
setTitle(Integer.toString(num) + " 条记录");
cur.moveToFirst();
tv.setText("");
while (cur.getPosition()!=cur.getCount())
{
tv.append(Integer.toString(cur.getPosition())+" , "+cur.getString(cur.getColumnIndex("title"))+" , "+cur.getString(cur.getColumnIndex("body"))+"\n");
cur.moveToNext();
}
}
