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;@Overrideprotected 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);}@Overridepublic 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>