Android SQLite的理解和应用

SQLite，是一款轻型的数据库，是遵守ACID的关系型数据库管理系统，它包含在一个相对小的C库中。它是D.RichardHipp建立的公有领域项目。它的设计目标是嵌入式的，而且目前已经在很多嵌入式产品中使用了它，它占用资源非常的低，在嵌入式设备中，可能只需要几百K的内存就够了。

而在Android开发中，由于真机内置SQLite使得数据的增删改查变得容易轻松。

SQLite的核心实现类SQLiteOpenHelper 实现了数据库表的创建。具体代码如下：

package com.nanguabing.sqlitedemo;

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

public class DBHelper extends SQLiteOpenHelper {
	/**
	 * 数据库名称
	 */
	private static final String DB_NAME="mydb.db";
	/**
	 * 数据库版本
	 */
	private static final int DB_VERSION=1;
	public DBHelper(Context context, String name, CursorFactory factory,
			int version) {
		super(context, name, factory, version);
	}
	public DBHelper(Context context) {
		this(context,DB_NAME,null,DB_VERSION);
	}
	/**
	 * 创建数据库
	 */
	public void onCreate(SQLiteDatabase db) {
		String sql="create table customers(id integer primary key autoincrement,name varchar(20),age int)" ;
		db.execSQL(sql);
	}

	/**
	 * 用于升级
	 */
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		
	}

}

对于数据库的操纵，根据JDBC思想，我们新建一个CURD实体类进行增删改查操作

package com.nanguabing.sqlitedemo;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import java.util.ArrayList;
import java.util.List;

public class CRUD {
	DBHelper helper;

	public CRUD(Context ctx) {
		helper = new DBHelper(ctx);
	}

	/**
	 * insert操作
	 */
	public void insert(String name, int age) {
		String sql = "insert into customers(name,age) values(?,?)";
		SQLiteDatabase db = helper.getWritableDatabase();
		db.execSQL(sql, new Object[] { name, age });
	}
	/**
	 * update操作
	 */
	public void update(String name, int age,int id){
		String sql = "update customers set name = ? , age = ? where id = ?" ;
		SQLiteDatabase db = helper.getWritableDatabase();
		db.execSQL(sql, new Object[]{name,age,id});
	}
	
	/**
	 * delete操作
	 */
	public void delete(int id){
		String sql = "delete from customers where id = ?" ;
		SQLiteDatabase db = helper.getWritableDatabase();
		db.execSQL(sql, new Object[]{id});
	}
	/**
	 * 查询所有客户信息
	 */
	public List<Object[]> findAll(){
		List<Object[]> list = new ArrayList<Object[]>();
		Object[] o = null ;
		String sql = "select * from customers" ;
		SQLiteDatabase db = helper.getWritableDatabase();
		Cursor cur = db.rawQuery(sql, null);
		while(cur.moveToNext()){
			o = new Object[3];
			o[0] = cur.getInt(cur.getColumnIndex("id"));
			o[1] = cur.getString(cur.getColumnIndex("name"));
			o[2] = cur.getInt(cur.getColumnIndex("age"));
			list.add(o);
		}
		cur.close();
		return list ;
	}
}

并在MainActivity中调用

package com.nanguabing.sqlitedemo;

import java.util.List;

import android.app.Activity;
import android.os.Bundle;
import android.view.Menu;
import android.view.View;
import android.widget.EditText;
import android.widget.TextView;

public class MainActivity extends Activity {
	CRUD mcrud;
	EditText edit1, edit2, edit3;
	List<Object[]> mlist;
	String txt="";
	TextView text1;
	@Override
	protected void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.activity_main);
		mcrud = new CRUD(MainActivity.this);
		edit1 = (EditText) findViewById(R.id.editText1);
		edit2 = (EditText) findViewById(R.id.editText2);
		edit3 = (EditText) findViewById(R.id.editText3);
		text1=(TextView) findViewById(R.id.textView1);
	}

	public void onClick(View v) {
		switch (v.getId()) {
		case R.id.button1:
			mcrud.insert(edit2.getText().toString(),
					Integer.parseInt(edit3.getText().toString()));
			select();
			break;
		case R.id.button2:
			mcrud.delete(Integer.parseInt(edit1.getText().toString()));
			select();
			break;
		case R.id.button3:
			mcrud.update(edit2.getText().toString(),
					Integer.parseInt(edit3.getText().toString()),
					Integer.parseInt(edit1.getText().toString()));

			select();
			break;
		case R.id.button4:
			
			select();
			break;
		default:
			break;
		}
	}
	private void select(){
		mlist = mcrud.findAll();
		txt="";
		for (Object[] objects : mlist) {
			txt += objects[0].toString() + objects[1] + objects[2]+"\n";
		}
		text1.setText(txt);
	}
	@Override
	public boolean onCreateOptionsMenu(Menu menu) {
		// Inflate the menu; this adds items to the action bar if it is present.
		getMenuInflater().inflate(R.menu.activity_main, menu);
		return true;
	}

}

主布局文件

<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    tools:context=".MainActivity" >

    <Button
        android:id="@+id/button1"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignParentLeft="true"
        android:layout_alignParentTop="true"
        android:onClick="onClick"
        android:text="增加" />

    <Button
        android:id="@+id/button2"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_above="@+id/editText1"
        android:layout_alignLeft="@+id/editText1"
        android:onClick="onClick"
        android:text="删除" />

    <Button
        android:id="@+id/button3"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignParentTop="true"
        android:layout_toRightOf="@+id/button2"
        android:onClick="onClick"
        android:text="修改" />

    <Button
        android:id="@+id/button4"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignParentTop="true"
        android:layout_toRightOf="@+id/button3"
        android:onClick="onClick"
        android:text="查询" />

    <EditText
        android:id="@+id/editText1"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_below="@+id/button1"
        android:layout_toRightOf="@+id/button1"
        android:ems="10"
        android:hint="ID" >

        <requestFocus />
    </EditText>

    <EditText
        android:id="@+id/editText2"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignLeft="@+id/editText1"
        android:layout_below="@+id/editText1"
        android:ems="10"
        android:hint="姓名" />

    <EditText
        android:id="@+id/editText3"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_below="@+id/editText2"
        android:layout_toRightOf="@+id/button1"
        android:ems="10"
        android:hint="年龄"
        android:inputType="number" />

    <TextView
        android:id="@+id/textView1"
        android:layout_width="wrap_content"
        android:layout_height="100dip"
        android:layout_alignLeft="@+id/editText3"
        android:layout_alignRight="@+id/editText3"
        android:layout_below="@+id/editText3" />

</RelativeLayout>