Android-SQLite使用总结

android中SQLiteDatabase的创建是通过SQLiteOpenHelper类的数据库获取方法完成的，其中包括getReadableDatabase()、getWritableDatabase()等。数据创建完成后，通过对数据库增、删、改、查等方法的重载，即可完成数据库操作的应用。

具体的SQLite使用的具体步骤如下：

1.新建一个继承自SQLiteOpenHelper的类DatabaseHelper；

2.通过DatabaseHelper的对象，调用其方法getReadableDatabase()，返回SQLiteDatabase数据库对象；

3.定义Button监听函数，再对应的监听函数中进行数据库增删改查操作。

下面以代码为主简单介绍起用法：

一、新建一个类DatabaseHelper，继承自SQLiteOpenHelper。类文件名DatabaseHelper.java，内容如下：

package com.example.sqlite.db;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;public DatabaseHelper(Context context, String name, CursorFactory factory,int version) {super(context, name, factory, version);// TODO Auto-generated constructor stub}public DatabaseHelper(Context context, String name){this(context, name, VERSION);}public DatabaseHelper(Context context, String name, int version) {// TODO Auto-generated constructor stubthis(context, name, null, version);}@Overridepublic void onCreate(SQLiteDatabase db) {// TODO Auto-generated method stubString sql = "create table user(id int, name varchar(20), score int)";db.execSQL(sql);System.out.println("create a database.");}@Overridepublic void onUpgrade(SQLiteDatabase db, int arg1, int arg2) {// TODO Auto-generated method stubSystem.out.println("update a database version.");}}

该类做了如下工作：1.重载了几个构造函数；2.重载了新建列表函数onCreate()。其中类的构造函数是为了传入数据库名称；列表创建函数重载是为了定义列表名称及结构。更多数据库的知识需另行学习。

二、主Activity文件

主Activity中主要定义了几个Button空间的监听函数，在对应的Button监听函数中完成数据库增删改查的操作。对数据库的操作一般有两种方式：1.通过SQLiteDatabase类的insert/delete/update/query等方法进行操作；2.通过SQLiteDatabase类的execSQL方法，使用数据库操作命令进行操作。

主Activity文件名MainActivity.java，具体内容如下：

package com.example.sqlite;import android.R.integer;import android.app.Activity;import android.content.ContentValues;import android.content.res.Resources;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import android.graphics.drawable.Drawable;import android.os.Bundle;import android.view.Menu;import android.view.View;import android.view.View.OnClickListener;import android.widget.Button;import android.widget.EditText;import com.example.sqlite.db.DatabaseHelper;public class MainActivity extends Activity {//定义类的控件成员变量private Button btn_ist = null;private Button btn_dlt = null;private Button btn_mdf = null;private Button btn_qry = null;private Button btn_crt = null;private Button btn_upd = null;private EditText edt_id = null;private EditText edt_value = null;    @Override    protected void onCreate(Bundle savedInstanceState) {        super.onCreate(savedInstanceState);        setContentView(R.layout.activity_main);                        //set background color        Resources res = getResources();        Drawable drawable = res.getDrawable(R.drawable.bkground);        this.getWindow().setBackgroundDrawable(drawable);                //通过id获取控件对象        btn_ist = (Button)findViewById(R.id.insert);        btn_dlt = (Button)findViewById(R.id.delete);        btn_mdf = (Button)findViewById(R.id.modify);        btn_qry = (Button)findViewById(R.id.query);        btn_crt = (Button)findViewById(R.id.createdb);        btn_upd = (Button)findViewById(R.id.updatedb);        edt_id = (EditText)findViewById(R.id.id);        edt_value=(EditText)findViewById(R.id.value);                //设置button的点击消息响应函数        //无名类定义的方式        btn_ist.setOnClickListener(new View.OnClickListener() {@Overridepublic void onClick(View arg0) {// TODO Auto-generated method stub//获取edit text参数String str = edt_id.getText().toString();System.out.println("insert a data:"+str);//if( str !="" ){int id_ist = Integer.parseInt(str);String name_ist = edt_value.getText().toString();//增加一条记录ContentValues value = new ContentValues();value.put("id", id_ist);value.put("name", name_ist);value.put("score", 100);DatabaseHelper dbHelper = new DatabaseHelper(MainActivity.this, "test_db_lx");SQLiteDatabase db = dbHelper.getWritableDatabase();db.insert("user", null, value);System.out.println("insert a data.");}//else//System.out.println("Please input your record.");}});        //有名类定义的方式        btn_dlt.setOnClickListener(new deleteOnClickListener());        btn_mdf.setOnClickListener(new modifyOnClickListener());        btn_qry.setOnClickListener(new queryOnClickListener());        btn_crt.setOnClickListener(new createOnClickListener());        btn_upd.setOnClickListener(new updateOnClickListener());                //创建数据库助手类对象                    }        class deleteOnClickListener implements OnClickListener{@Overridepublic void onClick(View v) {// TODO Auto-generated method stubSystem.out.println("delete a data.");String dlt_id = edt_id.getText().toString();DatabaseHelper dbHelper = new DatabaseHelper(MainActivity.this, "test_db_lx");SQLiteDatabase db = dbHelper.getWritableDatabase();//db.delete("user", "id=?", new String[]{dlt_id});db.execSQL("delete from user where id="+dlt_id);//这句命令操作不能同时用多个条件限制}        }    class modifyOnClickListener implements OnClickListener{@Overridepublic void onClick(View v) {// TODO Auto-generated method stubSystem.out.println("modify a data.");String mdf_id = edt_id.getText().toString();String mdf_value = edt_value.getText().toString();ContentValues value = new ContentValues();value.put("score", mdf_value);DatabaseHelper dbHelper = new DatabaseHelper(MainActivity.this, "test_db_lx");SQLiteDatabase db = dbHelper.getWritableDatabase();db.update("user", value, "id=?", new String[]{mdf_id});}        }    class queryOnClickListener implements OnClickListener{@Overridepublic void onClick(View v) {// TODO Auto-generated method stubSystem.out.println("query a data.");//获取一条记录String id_qry = edt_id.getText().toString();//打开数据库DatabaseHelper dbHelper = new DatabaseHelper(MainActivity.this, "test_db_lx");SQLiteDatabase db = dbHelper.getReadableDatabase();Cursor cursor = db.query("user", new String[]{"id","name","score"}, "id=?", new String[]{ id_qry }, null, null, null);while (cursor.moveToNext()){String name = cursor.getString(cursor.getColumnIndex("name"));String score = cursor.getString(cursor.getColumnIndex("score"));System.out.println("query--->"+name+":"+score);edt_value.setText(name+":"+score);}cursor.close();}    }    class createOnClickListener implements OnClickListener{@Overridepublic void onClick(View v) {// TODO Auto-generated method stub//通过database helper创建数据库DatabaseHelper dbHelper = new DatabaseHelper(MainActivity.this, "test_db_lx");SQLiteDatabase db = dbHelper.getReadableDatabase();}        }    class updateOnClickListener implements OnClickListener{@Overridepublic void onClick(View v) {// TODO Auto-generated method stub//更新数据库版本号DatabaseHelper dbHelper = new DatabaseHelper(MainActivity.this, "test_db_lx", 2);SQLiteDatabase db = dbHelper.getReadableDatabase();}        }    @Override    public boolean onCreateOptionsMenu(Menu menu) {        // Inflate the menu; this adds items to the action bar if it is present.        getMenuInflater().inflate(R.menu.main, menu);        return true;    }    }

注意：此处在每个Button监听函数中都重新new一个数据库Helper的类，并根据读写需求获取了相应的SQLiteDatabase对象。

三、其他文件内容

其他相关文件包括：布局文件activity_main.xml和strings.xml。内容分别如下：

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"android:orientation="vertical"    android:layout_width="match_parent"    android:layout_height="match_parent"         ><LinearLayout     android:orientation="horizontal"    android:layout_width="match_parent"    android:layout_height="wrap_content"        >   <TextView        android:layout_width="fill_parent"        android:layout_height="wrap_content"        android:textColor="#aaaaaa"        android:layout_weight="1"        android:gravity="left"        android:text="@string/id" />        <TextView        android:layout_width="fill_parent"        android:layout_height="wrap_content"        android:textColor="#aaaaaa"        android:layout_weight="1"        android:gravity="left"android:text="@string/value"        /> </LinearLayout><LinearLayout android:orientation="horizontal"    android:layout_width="match_parent"    android:layout_height="wrap_content"    android:layout_marginTop="5dip"         >   <EditText   android:id="@+id/id"   android:hint="@string/id"        android:layout_width="fill_parent"        android:layout_height="wrap_content"        android:layout_marginRight="5dip"        android:background="#aaaaaa"        android:maxLines="1"        android:layout_weight="1" />        <EditText        android:id="@+id/value"        android:hint="@string/value"        android:layout_width="fill_parent"        android:layout_height="wrap_content"        android:layout_marginRight="5dip"        android:background="#aaaaaa"        android:maxLines="1"        android:layout_weight="1" /></LinearLayout><LinearLayout    android:layout_width="fill_parent"    android:layout_height="wrap_content"    android:layout_marginTop="10dip"       >            <Button        android:id="@+id/insert"        android:layout_width="match_parent"        android:layout_height="wrap_content"              android:layout_weight="1"        android:text="@string/insert"         android:clickable="true"        /><Button        android:id="@+id/delete"        android:layout_width="fill_parent"        android:layout_height="wrap_content"              android:layout_weight="1"        android:text="@string/delete"         android:clickable="true"/><Button        android:id="@+id/modify"        android:layout_width="fill_parent"        android:layout_height="wrap_content"              android:layout_weight="1"        android:text="@string/modify"        android:clickable="true"/><Button        android:id="@+id/query"        android:layout_width="fill_parent"        android:layout_height="wrap_content"              android:layout_weight="1"        android:text="@string/query"        android:clickable="true"/></LinearLayout><LinearLayout    android:layout_width="fill_parent"    android:layout_height="wrap_content"    android:layout_marginTop="5dip"        >            <Button        android:id="@+id/createdb"        android:layout_width="match_parent"        android:layout_height="wrap_content"              android:layout_weight="1"        android:text="@string/createdb"         android:clickable="true"        /><Button        android:id="@+id/updatedb"        android:layout_width="fill_parent"        android:layout_height="wrap_content"              android:layout_weight="1"        android:text="@string/updatedb"         android:clickable="true"/></LinearLayout></LinearLayout>

strings.xml

<?xml version="1.0" encoding="utf-8"?><resources>    <string name="app_name">sqlite</string>    <string name="action_settings">Settings</string>    <string name="hello_world">Hello world!</string>    <string name="id">id:</string>    <string name="value">value:</string>    <string name="operation">operation:</string>    <string name="insert">insert</string>    <string name="delete">delete</string>    <string name="modify">modifi</string>    <string name="query">query</string>    <string name="createdb">createdb</string>    <string name="updatedb">updatedb</string>    <drawable name="bkground">#000000</drawable></resources>