SQLite使用方法

Android系统集成了一个轻量级的数据库SQLite，是一个嵌入式的数据库引擎，适用于资源有限的设备适量数据存取

Android提供了SQLiteOpenHelper类，作为访问SQLite的助手类，通常都会继承SQLiteOpenHelper开发子类

SQLiteOpenHelper类提供两个方面的功能：

第一，getReadableDatabase(),getWritableDatabase()可以获得SQLiteDatabse对象，通过该对象可以对数据库进行操作

第二，提供了onCreate()和onUpgrade()两个回调函数，允许我们在创建和升级数据库时，进行自己的操作

例程：

DatabaseHelper.java

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseHelper extends SQLiteOpenHelper {
	
	private static final int VERSION  = 1;

	//在SQLiteOepnHelper的子类当中，必须有该构造函数
	public DatabaseHelper(Context context, String name, CursorFactory factory,
			int version) {
		//必须通过super调用父类当中的构造函数
		super(context, name, factory, version);
		// TODO Auto-generated constructor stub
	}
	

	public DatabaseHelper(Context context, String name) {
		this(context, name, VERSION);
		// TODO Auto-generated constructor stub
		}
	
	public DatabaseHelper(Context context,String name,int version){
		this(context,name,null,version);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		System.out.println("create a Database");
		//execSQL函数用于执行SQL语句，创建一个表名为user,有两列数据id、name
		db.execSQL("create table user(id int,name varchar(20))");
		
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		System.out.println("update a Database");
		
	}

}

主要Activity

SQLiteActivity.java(五个按钮：创建数据库、更新数据库版本、插入数据、更新数据、查询数据、删除数据)

import android.app.Activity;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;

import com.mars.sqlite.db.DatabaseHelper;

public class SQLiteActivity extends Activity {

	private Button createButton;
	private Button updateButton;
	private Button insertButton;
	private Button updateRecordButton;
	private Button queryButton;
	private Button deleteButton; 
	
	@Override
	protected void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.main);
		createButton = (Button)findViewById(R.id.createDatabase);
		updateButton = (Button)findViewById(R.id.updateDatabase);
		insertButton = (Button)findViewById(R.id.insert);
		updateRecordButton = (Button)findViewById(R.id.update);
		queryButton = (Button)findViewById(R.id.query);
		deleteButton = (Button)findViewById(R.id.delete);
		
		createButton.setOnClickListener(new CreateListener());
		updateButton.setOnClickListener(new UpdateListener());
		insertButton.setOnClickListener(new InsertListener());
		updateRecordButton.setOnClickListener(new UpdateRecordListener());
		queryButton.setOnClickListener(new QueryListener());
		deleteButton.setOnClickListener(new DeleteListener());
		
	}
	
	class CreateListener implements OnClickListener{
		public void onClick(View v){
			DatabaseHelper dbHelper = new DatabaseHelper(SQLiteActivity.this, "test_db");
			//只有调用了DatabaseHelper对象的getReadableDatabase()方法，或者是getWritableDatabase()方法之后，才会创建，或打开一个数据库
			SQLiteDatabase db = dbHelper.getReadableDatabase();
		}
		
	}
	
	class UpdateListener implements OnClickListener{
		@Override
		public void onClick(View v) {
			DatabaseHelper dbHelper = new DatabaseHelper(SQLiteActivity.this, "test_db",2);
			SQLiteDatabase db = dbHelper.getReadableDatabase();	
			
		}
	}
	//insert into <表名>（key1,key2,..） values（value1，value2） where
	class InsertListener implements OnClickListener{
		@Override
		public void onClick(View v) {
			ContentValues values = new ContentValues();
			values.put("id",1);
			values.put("name","wanger");
			DatabaseHelper dbHelper = new DatabaseHelper(SQLiteActivity.this, "test_db",2);
			SQLiteDatabase db = dbHelper.getWritableDatabase();	
			db.insert("user", null, values);		
			
		}
	}
	
    //updata table_name set XXCOL=XXX where XXCOL=XX...
	class UpdateRecordListener implements OnClickListener{
		@Override
		public void onClick(View v) {
			DatabaseHelper dbHelper = new DatabaseHelper(SQLiteActivity.this, "test_db",2);
			SQLiteDatabase db = dbHelper.getWritableDatabase();	
			ContentValues values = new ContentValues();
			values.put("name", "wangerniang");
			db.update("user",values,"id = ?",new String[]{"1"});
			
			
		}
	}
	
	class QueryListener implements OnClickListener{
		@Override
		public void onClick(View v) {
			DatabaseHelper dbHelper = new DatabaseHelper(SQLiteActivity.this,"test_db",2);
			SQLiteDatabase db = dbHelper.getReadableDatabase();
			Cursor cursor = db.query("user", new String[]{"id","name"}, "id=?", new String[]{"1"}, null, null, null);
			while(cursor.moveToNext()){
				String name = cursor.getString(cursor.getColumnIndex("name"));
				System.out.println("query--->" + name);
			}
			
		}
	}
	//delete <table> where <whereClause>
	class DeleteListener implements OnClickListener{
		@Override
		public void onClick(View v) {
			// TODO Auto-generated method stub
			DatabaseHelper dbHelper = new DatabaseHelper(SQLiteActivity.this,"test_db",2);
			SQLiteDatabase db = dbHelper.getWritableDatabase();
			db.delete("user", "name = ?",new String[]{"wanger"} );
			
		}
	}	

}

备注:

当更新数据库的版本为2时，后面增改查删操作之前打开数据库就要用版本为2，

如果选择默认（默认版本为1），会报错（不能从版本2降级到版本1）