数据库增删改查（二）

public class MyDatabaseHelper extends SQLiteOpenHelper {
	
	//建表Book
	public static final String CREATE_BOOK="create table Book ("
			+"id integer primary key autoincrement,"
			+"author text,"
			+"price real,"
			+"pages integer,"
			+"name text )";
	//建表Category
	public static final String CREATE_CATEGORY="create table Category ("
			+"id integer primary key autoincrement,"
			+"category_name text," +
			"category_code integer )";
	private Context mContext;
	public MyDatabaseHelper(Context context, String name,
			CursorFactory factory, int version) {
		super(context, name, factory, version);
		mContext=context;
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		//执行建表语句
		db.execSQL(CREATE_BOOK);
		db.execSQL(CREATE_CATEGORY);
		//弹出建表成功的提示
		Toast.makeText(mContext, "Create successed",Toast.LENGTH_SHORT).show();
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		//如果表存在，先干掉
		db.execSQL("drop table if exists Book");
		db.execSQL("drop table if exists Category");
		//然后调用onCreate方法，重新创建
		onCreate(db);
	}

}

<pre name="code" class="java">public class MainActivity extends Activity {

	private MyDatabaseHelper dbHelper;

	@Override
	protected void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.activity_main);
		//升级数据库
		dbHelper = new MyDatabaseHelper(this, "BookStore.db", null, 2);
		//创建数据库
		findViewById(R.id.create_database).setOnClickListener(
				new OnClickListener() {

					@Override
					public void onClick(View v) {
						//getWritableDatabase和getReadableDatabase，都可以创建或打开一个现有数据库（没有，则创建），并返回一个可对数据库进行读写操作的对象
						dbHelper.getWritableDatabase();
					}
				});
		//插入数据
		findViewById(R.id.add_data).setOnClickListener(new OnClickListener() {

			@Override
			public void onClick(View v) {
				SQLiteDatabase db = dbHelper.getWritableDatabase();
				ContentValues values = new ContentValues();
				values.put("name", "The Da Vinci Code");
				values.put("author", "Dan Brown");
				values.put("pages", 454);
				values.put("price", 16.96);
				db.insert("Book", null, values);
				values.clear();

				values.put("name", "The Lost Symbol");
				values.put("author", "Dan Brown");
				values.put("pages", 510);
				values.put("price", 19.95);
				db.insert("Book", null, values);
			}
		});
		//更新数据
		findViewById(R.id.update_data).setOnClickListener(new OnClickListener() {
			
			@Override
			public void onClick(View v) {
				SQLiteDatabase db=dbHelper.getWritableDatabase();
				ContentValues values=new ContentValues();
				values.put("price",10.99);
				db.update("Book", values,"name=?",new String[]{"The Da Vinci Code"});
			}
		});
		//删除数据
		findViewById(R.id.delete_data).setOnClickListener(new OnClickListener() {
			
			@Override
			public void onClick(View v) {
				SQLiteDatabase db=dbHelper.getWritableDatabase();
				db.delete("Book","pages>?",new String[]{"500"});
			}
		});
		//查询数据
		findViewById(R.id.query_data).setOnClickListener(new OnClickListener() {
			
			@Override
			public void onClick(View v) {
				SQLiteDatabase db=dbHelper.getWritableDatabase();
				Cursor cursor=db.query("Book", null, null, null, null, null, null);
				if (cursor.moveToFirst()) {
					do {
						String name=cursor.getString(cursor.getColumnIndex("name"));
						String author=cursor.getString(cursor.getColumnIndex("author"));
						int pages=cursor.getInt(cursor.getColumnIndex("pages"));
						double price=cursor.getDouble(cursor.getColumnIndex("price"));
						Log.d("TAG","\n"+name+"\n"+author+"\n"+pages+"\n"+price+"\n");
					} while (cursor.moveToNext());
				}
			}
		});
	}
}