首页 > 代码库 > 第十二章:Android数据存储(上)

第十二章:Android数据存储(上)

一、SQLite介绍

提到数据存储问题,数据库是不得不提的。数据库是用来存储关系型数据的不二利器。Android为开发者提供了强大的数据库支持,可以用来轻松地构造基于数据库的应用。Android的数据库应用,依托于当下最流行的开源嵌入式数据库SQLite。在Android中,应用的数据库文件是该应用私有的,存储在应用数据目录下的databases子目录内。从代码结构来看,Android的数据库实现可以分成两个层次,在底层通过C++调用SQLite的接口来执行SQL语句,并通过JNI向上暴露Java可访问的接口。

SQLite可视化工具:SQLite Expert Professional 3;

二、Android访问SQLite

主要步骤:

(1)创建某个类继承SQLiteOpenHelper,并重写
-- 带Context参数的构造函数; //用来创建数据库
-- onCreate(SQLiteDatabase db); //在创建数据库时调用
-- onUpgrade(SQLiteDatabase db,int old,int newversion);  //数据库版本更改时调用
(2)创建数据库:SQLiteDatabase db = helper.getWritableDatabase();
(3)SQL语句:db.execSQL(String sql);
 
语句使用:

1.打开数据库

 
SQLiteDatabase db = helper.getWritableDatabase();
SQLiteDatabase db = helper.getReadableDatabase(); //此函数内部其实也调用了getWritableDatabase()函数,即调用了此函数,也可以写数据
 

2.insert语句

 
(1)db.execSQL("insert into person(name,age) values(?,?)",new Object[]{"xiazdong",20});
(2)
ContentValues values = new ContentValues();values.put("name","xiazdong");values.put("age",20);db.insert("tablename",null,values);

3.delete语句

 
(1)db.execSQL("delete from person where id=?",new Object[]{id});
(2)db.delete("tablename","id=?",new String[]{id+""});
 

4.update语句

 
(1)
db.exeSQL("update person set age=? where name=?",new Object[]{30,"xiazdong"});
(2)
ContentValues values = new ContentValues();values.put("age",30);/*set子句*/db.update("tablename",values,"name=?"/*where子句*/,new String[]{"xiazdong"}/*where子句参数*/);

5.query语句

 
(1)
Cursor cursor = db.rawQuery("select * from person where name=?",new Object[]{"xiazdong"});while(cursor.moveToNext()){    //int index = cursor.getColumnIndex(String name);//根据name获得索引    //String name = cursor.getString(int index);    //根据索引获得值    String name = cursor.getString(cursor.getColumnIndex("name")); }
(2)
Cursor cursor = db.query("tablename",null/*表示select * */,"name=?"/*where语句*/,new String[]{"xiazdong"},null/*group by 语句*/,null/*having 语句*/,null/*order by语句*/,null/*limit 语句*/);
 

6.分页语句

 
Cursor cursor = db.rawQuery("select * from person limit ?,?",new Object[]{5,5});    //第一个5表示跳过5条记录,下一个5为查询结果的记录个数
while(cursor.moveToNext()){
    String name = cursor.getString(cursor.getColumnIndex("name")); 
}

7.获得记录个数语句

 
(1)
Cursor cursor = db.rawQuery("select count(*) from person", null);cursor.moveToFirst();int count = cursor.getInt(0);
 
(2)
db.query("person",new String[]{"count(*)"},null,null,null,null,null,null);
 
 
 
 示例代码:
MySQLiteOpenHelper.java:
package com.example.helloandroid;import android.content.Context;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteOpenHelper;import android.database.sqlite.SQLiteDatabase.CursorFactory;public class MySQLiteOpenHelper extends SQLiteOpenHelper {    public MySQLiteOpenHelper(Context context, String name,            CursorFactory factory, int version) {        super(context, name, factory, version);        // TODO Auto-generated constructor stub    }    @Override    public void onCreate(SQLiteDatabase db) {        // TODO Auto-generated method stub            }    @Override    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {        // TODO Auto-generated method stub            }}
View Code

DataBaseActivity.java

package com.example.helloandroid;import android.app.Activity;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteOpenHelper;import android.os.Bundle;import android.view.Menu;import android.view.MenuItem;import android.view.View;import android.view.View.OnClickListener;import android.widget.Button;import android.widget.Toast;public class DataBaseActivity extends Activity {      private Button createDatabase, createTable, selectData, insertData,      updateData, deleteDaata;      private MySQLiteOpenHelper helper;    @Override    protected void onCreate(Bundle savedInstanceState) {        super.onCreate(savedInstanceState);        setContentView(R.layout.activity_data_base);                // 初始化按钮        createDatabase = (Button) findViewById(R.id.createDatabase);        createTable = (Button) findViewById(R.id.createTable);        selectData = (Button) findViewById(R.id.selectData);        insertData = (Button) findViewById(R.id.insertData);        updateData = (Button) findViewById(R.id.updateData);        deleteDaata = (Button) findViewById(R.id.deleteDaata);                        helper = new MySQLiteOpenHelper(DataBaseActivity.this, "mydb.db",                null, 1);                /*         * 创建数据库         */        createDatabase.setOnClickListener(new OnClickListener() {            @Override            public void onClick(View v) {                // TODO Auto-generated method stub                SQLiteDatabase sqlitedatabase = helper.getWritableDatabase();                Toast.makeText(DataBaseActivity.this, "数据库创建成功", 1000).show();            }        });                /*         * 创建表         */        createTable.setOnClickListener(new OnClickListener() {            public void onClick(View arg0) {                // TODO Auto-generated method stub                SQLiteDatabase sqlitedatabase = helper.getWritableDatabase();                // 创建表                sqlitedatabase                        .execSQL("create table student(id INTEGER PRIMARY KEY autoincrement,name text)");                Toast.makeText(DataBaseActivity.this, "表创建成功", 1000).show();            }        });                        /*         * 插入数据         */        insertData.setOnClickListener(new OnClickListener() {            public void onClick(View v) {                // TODO Auto-generated method stub                SQLiteDatabase sqlitedatabase = helper.getWritableDatabase();                sqlitedatabase                        .execSQL("insert into student(name) values (‘tinyphp‘)");                Toast.makeText(DataBaseActivity.this, "插入数据成功", 1000).show();            }        });        /*         * 更新数据         */        updateData.setOnClickListener(new OnClickListener() {            @Override            public void onClick(View v) {                // TODO Auto-generated method stub                SQLiteDatabase sqlitedatabase = helper.getWritableDatabase();                sqlitedatabase                        .execSQL("update student set name=‘monkey‘ where id=‘1‘");                Toast.makeText(DataBaseActivity.this, "数据更新成功", 1000).show();            }        });        /*         * 查询数据         */        selectData.setOnClickListener(new OnClickListener() {            @Override            public void onClick(View v) {                // TODO Auto-generated method stub                SQLiteDatabase sqlitedatabase = helper.getReadableDatabase();                                //参数:表名、查询的列、查询条件、查询参数、查询结果分组、分组结果限制、排序                Cursor cursor = sqlitedatabase.query("student", new String[] {                        "id", "name" }, "id=?", new String[] { "1" }, null,                        null, null);                String name=null;                while(cursor.moveToNext()){                    name =cursor.getString(cursor.getColumnIndex("name"));                                    }                Toast.makeText(DataBaseActivity.this, "查询结果name为:"+name, 1000).show();            }        });                /*         * 删除数据         * */                        deleteDaata.setOnClickListener(new OnClickListener() {                        @Override            public void onClick(View v) {                // TODO Auto-generated method stub                SQLiteDatabase sqlitedatabase =helper.getWritableDatabase();                sqlitedatabase.delete("student", "id=?", new String[]{"1"});                Toast.makeText(DataBaseActivity.this, "数据删除成功", 1000).show();            }        });    }}
View Code

activity_data_base.xml

<?xml version="1.0" encoding="utf-8"?><LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"    android:layout_width="match_parent"    android:layout_height="match_parent"    android:orientation="vertical" >     <Button        android:id="@+id/createDatabase"        android:layout_width="match_parent"        android:layout_height="wrap_content"        android:text="创建数据库" />          <Button        android:id="@+id/createTable"        android:layout_width="match_parent"        android:layout_height="wrap_content"        android:text="创建表" />            <Button        android:id="@+id/insertData"        android:layout_width="match_parent"        android:layout_height="wrap_content"        android:text="插入" />              <Button        android:id="@+id/updateData"        android:layout_width="match_parent"        android:layout_height="wrap_content"        android:text="更新" />       <Button           android:id="@+id/selectData"           android:layout_width="match_parent"           android:layout_height="wrap_content"           android:text="查询" />               <Button        android:id="@+id/deleteDaata"        android:layout_width="match_parent"        android:layout_height="wrap_content"        android:text="删除" /></LinearLayout>
View Code

 效果图:

 

第十二章:Android数据存储(上)