Android之SQLite数据库的使用

jerry Android 2015年11月23日收藏

SQLite是比较小而功能比较全的关系型数据库，下面介绍一下SQLite数据库的使用方法，及增删改查操作。

创建一个Android项目；

首先我们需要创建一个DatabaseHelper这个助手类，源码：

package cn.android.sword.sqlite.db;

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

public class DatabaseHelper extends SQLiteOpenHelper{
	private static final int VERSION = 1;
	private static final String SWORD="SWORD";
	//三个不同参数的构造函数
	//带全部参数的构造函数，此构造函数必不可少
	public DatabaseHelper(Context context, String name, CursorFactory factory,
			int version) {
		super(context, name, factory, version);
		
	}
	//带两个参数的构造函数，调用的其实是带三个参数的构造函数
	public DatabaseHelper(Context context,String name){
		this(context,name,VERSION);
	}
	//带三个参数的构造函数，调用的是带所有参数的构造函数
	public DatabaseHelper(Context context,String name,int version){
		this(context, name,null,version);
	}
	//创建数据库
	public void onCreate(SQLiteDatabase db) {
		Log.i(SWORD,"create a Database");
		//创建数据库sql语句
		String sql = "create table user(id int,name varchar(20))";
		//执行创建数据库操作
		db.execSQL(sql);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		//创建成功，日志输出提示
		Log.i(SWORD,"update a Database");
	}

}

布局资源文件：

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="fill_parent"
    android:layout_height="fill_parent"
    android:orientation="vertical" >

	<Button 
	    android:id="@+id/createDatabase"
	    android:layout_width="fill_parent"
	    android:layout_height="wrap_content"
	    android:text="@string/createDatabse"/>
	<Button 
	    android:id="@+id/updateDatabase"
	    android:layout_width="fill_parent"
	    android:layout_height="wrap_content"
	    android:text="@string/updateDatabase"/>
	<Button 
	    android:id="@+id/insert"
	    android:layout_width="fill_parent"
	    android:layout_height="wrap_content"
	    android:text="@string/insert"/>
	<Button 
	    android:id="@+id/update"
	    android:layout_width="fill_parent"
	    android:layout_height="wrap_content"
	    android:text="@string/update"/>
	<Button 
	    android:id="@+id/query"
	    android:layout_width="fill_parent"
	    android:layout_height="wrap_content"
	    android:text="@string/query"/>
	<Button 
	    android:id="@+id/delete"
	    android:layout_width="fill_parent"
	    android:layout_height="wrap_content"
	    android:text="@string/delete"/>
	
</LinearLayout>

在Activity中进行增删改查操作，源码：

package cn.android.sword.sqlite;

import cn.android.sword.sqlite.db.DatabaseHelper;
import android.app.Activity;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;

public class SQLiteActivity extends Activity implements OnClickListener{
    private final static String SWORD="SWORD";
    //声明五个控件对象
    Button createDatabase=null;
    Button updateDatabase=null;
    Button insert=null;
    Button update=null;
    Button query=null;
    Button delete=null;
    
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);
        findViews();
    }

	private void findViews() {
		//根据控件ID得到控件
		createDatabase = (Button) this.findViewById(R.id.createDatabase);
		updateDatabase = (Button) this.findViewById(R.id.updateDatabase);
		insert = (Button) this.findViewById(R.id.insert);
		update = (Button) this.findViewById(R.id.update);
		query = (Button) this.findViewById(R.id.query);
		delete = (Button) this.findViewById(R.id.delete);
		//添加监听器
		createDatabase.setOnClickListener(this);
		updateDatabase.setOnClickListener(this);
		insert.setOnClickListener(this);
		update.setOnClickListener(this);
		query.setOnClickListener(this);
		delete.setOnClickListener(this);
	}

	@Override
	public void onClick(View v) {
		//判断所触发的被监听控件，并执行命令
		switch(v.getId()){
		//创建数据库
		case R.id.createDatabase:
			//创建一个DatabaseHelper对象
			DatabaseHelper dbHelper1 = new DatabaseHelper(SQLiteActivity.this, "test_db");
			//取得一个只读的数据库对象
			SQLiteDatabase db1 = dbHelper1.getReadableDatabase();
			break;
		//更新数据库
		case R.id.updateDatabase:
			DatabaseHelper dbHelper2 = new DatabaseHelper(SQLiteActivity.this, "test_db", 2);
			SQLiteDatabase db2 = dbHelper2.getReadableDatabase();
			break;
		//插入数据
		case R.id.insert:
			//创建存放数据的ContentValues对象
			ContentValues values = new ContentValues();
			//像ContentValues中存放数据
			values.put("id", 1);
			values.put("name","zhangsan");
			DatabaseHelper dbHelper3 = new DatabaseHelper(SQLiteActivity.this, "test_db");
			SQLiteDatabase db3 = dbHelper3.getWritableDatabase();
			//数据库执行插入命令
			db3.insert("user", null, values);
			break;
		//更新数据信息
		case R.id.update:
			DatabaseHelper dbHelper4 = new DatabaseHelper(SQLiteActivity.this, "test_db");
			SQLiteDatabase db4 = dbHelper4.getWritableDatabase();
			ContentValues values2 = new ContentValues();
			values2.put("name", "xiaosan");
			db4.update("user", values2, "id=?", new String[]{"1"});
			break;
		//查询信息
		case R.id.query:
			DatabaseHelper dbHelper5 = new DatabaseHelper(SQLiteActivity.this, "test_db");
			SQLiteDatabase db5 = dbHelper5.getReadableDatabase();
			//创建游标对象
			Cursor cursor = db5.query("user", new String[]{"id","name"}, "id=?", new String[]{"1"}, null, null, null, null);
			//利用游标遍历所有数据对象
			while(cursor.moveToNext()){
				String name = cursor.getString(cursor.getColumnIndex("name"));
				//日志打印输出
				Log.i(SWORD,"query-->"+name);
			}
			break;
		//删除记录
		case R.id.delete:
			DatabaseHelper dbHelper6 = new DatabaseHelper(SQLiteActivity.this,"test_db");
			SQLiteDatabase db6 = dbHelper6.getWritableDatabase();
			db6.delete("user", "id=?", new String[]{"1"});
			break;
		default:
			Log.i(SWORD,"error");
			break;
		}
	}
}

在虚拟机中运行：