怎么使用SQLite建表和查询呢?

[zheyimiao]

小弟刚学android, 希望大侠能给个例子.我使用的是SQLiteDatabase类, 不知道该怎么用方法.

[纠结的木棉花]

http://wenku.baidu.com/view/c8df3b86bceb19e8b8f6ba0c.html
你可以看一下《android开发入门和实践》这本书，里面有一章都是讲述数据存储！


Though this example we can learn as the following：
1、如何新建一个数据库和数据表？
2、如何在数据表中插入数据？
3、如何查询数据库？
4、如何删除一条记录？如何删除数据表？
5、在发生异常时，如何重新建立数据表？
新建一个数据库和数据表：

private static class DatabaseHelper extends SQLiteOpenHelper {
		DatabaseHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}
		@Override
		public void onCreate(SQLiteDatabase db) {
			String sql = "CREATE TABLE " + TABLE_NAME + " (" + TITLE
					+ " text not null, " + BODY + " text not null " + ");";
			Log.i("haiyang:createDB=", sql);
			db.execSQL(sql);
		}
		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		}
	}
	@Override
	public void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.main);
		prepareListener();
		initLayout();
		mOpenHelper = new DatabaseHelper(this);
	}

在数据表中插入数据：

	

/*
* 插入两条数据
*/
	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', 'android的发展真是迅速啊');";
		try {
			Log.i("haiyang:sql1=", sql1);
			Log.i("haiyang:sql2=", sql2);
			db.execSQL(sql1);
			db.execSQL(sql2);
			setTitle("插入两条数据成功");
		} catch (SQLException e) {
			setTitle("插入两条数据失败");
		}
	}

查询当前数据表中的数据：

	

/*
* 在屏幕的title区域显示当前数据表当中的数据的条数。
*/
	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) + " 条记录");
	}
}

删除一条记录：

	

/*
	 * 删除其中的一条数据
	 */
	private void deleteItem() {
		try {
			SQLiteDatabase db = mOpenHelper.getWritableDatabase();
			db.delete(TABLE_NAME, " title = 'haiyang'", null);
			setTitle("删除title为haiyang的一条记录");
		} catch (SQLException e) {
		}
	}
	

删除数据表：

	

/*
* 删除数据表
*/
	private void dropTable() {
		SQLiteDatabase db = mOpenHelper.getWritableDatabase();
		String sql = "drop table " + TABLE_NAME;
		try {
			db.execSQL(sql);
			setTitle("数据表成功删除：" + sql);
		} catch (SQLException e) {
			setTitle("数据表删除错误");
		}
	}

重新建立数据表：

	

	private void CreateTable() {
		SQLiteDatabase db = mOpenHelper.getWritableDatabase();
		String sql = "CREATE TABLE " + TABLE_NAME + " (" + TITLE
				+ " text not null, " + BODY + " text not null " + ");";
		Log.i("haiyang:createDB=", sql);

		try {
			db.execSQL("DROP TABLE IF EXISTS diary");
			db.execSQL(sql);
			setTitle("数据表成功重建");
		} catch (SQLException e) {
			setTitle("数据表重建错误");
		}
	}

[william_ycyh]

刚写的作练习的
不是很好 你可以看看API里有个自带的NotePAD例子

public class DBHelper {
public static SQLiteDatabase mSQLiteDatabase = null;
public static final String TABLE_NAME = "NOTEPAD";
public static final String DB_NAME = "NOTEPAD.db";
public static final String TABLE_ID = "_id";
public static final String NOTE_TITLE = "title";
public static final String NOTE_DETAIL = "detail";
public static final String CREATED_DATE = "created";
public static final String MODIFIED_DATE = "modified";

public static final String CREATE_TABLE = "CREATE TABLE " + TABLE_NAME +"("
+ TABLE_ID + " INTEGER PRIMARY KEY, "
+ NOTE_TITLE +" TEXT, "
+ NOTE_DETAIL + " TEXT, "
+ CREATED_DATE + " TEXT,"
+ MODIFIED_DATE + " TEXT "
+ ")";
public static final String SELECT_TABLE = "SELECT * FROM " + TABLE_NAME + " ORDER BY " + NOTE_TITLE;
public static final String DELETE_SQL = "DELETE FROM " + TABLE_NAME + " WHERE " + TABLE_ID + " = " ;
public static final String QUERY_BY_ID = "SELECT * FROM " + TABLE_NAME + " WHERE " + TABLE_ID + " = ";

public static void doInsert(ContentValues cv){

mSQLiteDatabase.insert(TABLE_NAME, null, cv);
}

public static void doDelete(int id){
mSQLiteDatabase.execSQL(DELETE_SQL + id);
}

public static Cursor doQueryAll(){
Cursor mCursor = mSQLiteDatabase.rawQuery(SELECT_TABLE, null);
return mCursor;
}

public static Cursor queryById(int id){
Cursor mCursor = mSQLiteDatabase.rawQuery(QUERY_BY_ID + id, null);
return mCursor;
}

public static void doModify(ContentValues cv,int id){
mSQLiteDatabase.update(TABLE_NAME, cv, TABLE_ID + "=" + id, null);
}
}

可以在activity里创建
DBHelper.mSQLiteDatabase = this.openOrCreateDatabase(DBHelper.DB_NAME, MODE_PRIVATE, null);
try{
DBHelper.mSQLiteDatabase.execSQL(DBHelper.CREATE_TABLE);
}catch(Exception e){

}
查询：
Cursor mCursor = DBHelper.queryById(id);

if(mCursor != null){
if(mCursor.moveToFirst()){
int titleIndex = mCursor.getColumnIndex(DBHelper.NOTE_TITLE);
int detailIndex = mCursor.getColumnIndex(DBHelper.NOTE_DETAIL);
title = mCursor.getString(titleIndex);
detail = mCursor.getString(detailIndex);

}
}

[悲催的码农]

你可以参考下android模拟器的例子：notepad
一般而言不会去直接操作SQLiteDatabase
而是使用辅助类SQLiteOpenHelper 重载里面的oncreate和update