android SQLite数据库的使用

android提供了一个抽象类SQLiteOpenHelper用来管理数据库，因此我们需要创建一个类来继承它，并且重写他的两个方法onCreate()（创建数据库）和onUpgrade()（更新数据库）。他还有两个重要的实例方法，这两个方法都可一创建或打开一个数据库，并且返回一个可读写的数据库对象，区别是如果数据库不可写入时：

getReadableDatabase()  返回一个只可读的

getWriteableDatabase()   出现异常

自定义MyDatabaseHelper

public class MyDatabaseHelper extends SQLiteOpenHelper{
	
	private Context mContext;
	
	public static final String CREATE_BOOK="create table Book("
			+ "id integer primary key autoincrement,"
			+ "author text,"
			+ "price real,"
			+ "pages integer,"
			+ "name text)";
	

	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);
		Toast.makeText(mContext, "Create Succeeded", Toast.LENGTH_SHORT).show();
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		
	}

}

对数据库进行增删查改(CRUD(Create,Retrieve,Update,Delete))操作，为了给不熟悉sql语句的人方便操作，android提供了insert(),update(),delete(),query()方法进行操作数据库。用法很简单

public class MainActivity extends BaseActivity implements OnClickListener{
	
	private Button mCreateDbButton;
	
	private MyDatabaseHelper mHelper;
	
	private Button mAddDataButton;
	
	private Button mUpdateButton;
	
	private Button mDeleteButton;
	
	private Button mQueryButton;

	
	private static final String TAG="MainActivity";
	@Override
	protected void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.activity_main);
		
		//mHelper=new MyDatabaseHelper(this, "BookStore.db", null,1);
		mHelper=new MyDatabaseHelper(this, "BookStore.db",null,2);
	}

	@Override
	public void findId() {
		mCreateDbButton=(Button) findViewById(R.id.main_createDbButton);
		mAddDataButton=(Button) findViewById(R.id.main_addData_Button);
		mUpdateButton=(Button) findViewById(R.id.main_update_Button);
		mDeleteButton=(Button) findViewById(R.id.main_Delete_Button);
		mQueryButton=(Button) findViewById(R.id.main_Query_Button);
	}

	@Override
	public void setListener() {
		mCreateDbButton.setOnClickListener(this);
		mAddDataButton.setOnClickListener(this);
		mUpdateButton.setOnClickListener(this);
		mDeleteButton.setOnClickListener(this);
		mQueryButton.setOnClickListener(this);
	}

	@Override
	public void onClick(View v) {
		SQLiteDatabase db=mHelper.getWritableDatabase();
		ContentValues values=new ContentValues();
		switch (v.getId()) {
		case R.id.main_createDbButton:
			mHelper.getWritableDatabase();
			break;
		case R.id.main_addData_Button:
			//db=mHelper.getWritableDatabase();
			//values=new ContentValues();
			values.put("name", "c语言从入门到放弃");
			values.put("author", "c语言");
			values.put("pages", 520);
			values.put("price", 50.2);
			db.insert("Book", null, values);
			values.clear();
			
			values.put("name", "java从入门到放弃");
			values.put("author", "java");
			values.put("pages", 385);
			values.put("price", 47.8);
			db.insert("Book", null, values);
			values.clear();
			break;
		case R.id.main_update_Button:
			values.put("price", 88.8);
			db.update("Book", values, "name=?",new String[]{"c语言从入门到放弃"});
			values.clear();
			break;
		case R.id.main_Delete_Button:
			db.delete("Book", "pages>?",new String[]{"500"});
			break;
		case R.id.main_Query_Button:
			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.i(TAG,name+author+pages+price);
					
				}while(cursor.moveToNext());
			}
			break;
		default:
			break;
		}
	}
}

也可以使用sql语句进行操作，有时候用SQL语句操作更简单。

增删查改

db.execSQL("insert into Book(name,author,pages,price)values(?,?,?,?)",
					new String[]{"c#语言","c#","452","12.9"});

db.execSQL("delete from Book where price<?",new String[]{"50"});

Cursor cursor=db.rawQuery("select * from Book where name=?",new String[]{"c#语言"});			

db.execSQL("update Book set price=? where name=?",new String[]{"100","c#语言"});

升级数据库的最佳写法,我在第二个版本里面加了一个表Category，第三个版本里面在Book表中加了一个字段category_id

如果是新用户直接安装就执行oncreate()方法，两个表直接创建,如果是第一个版本的用户升级到第三个版本，就执行onUpgrade()方法，先创建第二个表，再在Book表中添加字段的操作。

注意switch case语句不写break

public class MyDatabaseHelper extends SQLiteOpenHelper{
	
	private Context mContext;
	
	public static final String CREATE_BOOK="create table Book("
			+ "id integer primary key autoincrement,"
			+ "author text,"
			+ "price real,"
			+ "pages integer,"
			+ "name text,"
			+ "category_id integer)";
	
	public static final String CREATE_CATEGORY="create table Category("
			+ "id integer primary key autoincrement,"
			+ "category_name text,"
			+ "category_code integer)";

	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 Succeeded", Toast.LENGTH_SHORT).show();
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		switch (oldVersion) {
		case 1:
			db.execSQL(CREATE_CATEGORY);
		case 2:
			db.execSQL("alter table Book add column category_id integer");
		default:
			
		}
	}

}