Android数据库SQLite应用基础一

SQLite数据库

在Android中存储数据，还可以通过SQLite轻量级嵌入式数据库来保存一些数据

SQLite数据库特性：

1、轻型，占用资源低，主要应用于嵌入式产品中

2、支持跨平台，不仅仅支持Linux、同样适用于Windows、IOS操作系统

3、支持多语言编程使用，主流的Java、C、.NET、php都可以

4、关系型数据库，支持SQL-92的大多数SQL语法，支持事务

5、在android系统中，各个app自己的SQLite数据库存储目录/data/data/app包名/databases/数据库名.db

6、缺点是SQLite是数据库级别锁，不支持多进程同时操作同一数据库、表级锁、行级锁

 

SQLite数据库工具

一般的对于数据库，我们最关系的就是数据库里面的存储数据

为了方便查看SQLite数据库里面的数据，有2个比较方便的工具，可方便查看数据库里面的数据

1、sqlite3

     默认安装完Andorid SDK后，在$ANDROID_HOME/sdk/tools目录下面就有了sqlite3

     使用步骤：

       

     截图只是查询的应用，更多详细可以百度其它资料    

 

2、SQLite Expert

     使用步骤：㈠、下载SQLite Expert，并安装。这里提供一个免费的个人版的SQLite Expert百度网盘的下载地址http://pan.baidu.com/s/1sjDRw9R

                     ㈡、导出android应用/data/data/app包名/databases/数据库.db 到本机

                     ㈢、打开SQLite Expert Pro，File -> Open数据库即可

      

 

SQLite数据库编程使用方法

1、直接使用SQLiteDatabase           类来增删改查数据

2、通过继承SQLiteOpenHelper 帮助类来增删改查数据

下面我们来通过实例一一看下这两种方法的使用

 

1、直接使用SQLiteDatabase           类来增删改查数据

使用步骤：㈠、通过context方法openOrCreateDatabase( , , , )创建数据库

                ㈡、数据库exeSQL(增删改查语句)

布局文件：activity_main.xml

<LinearLayout 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"    android:orientation="vertical"    android:paddingBottom="@dimen/activity_vertical_margin"    android:paddingLeft="@dimen/activity_horizontal_margin"    android:paddingRight="@dimen/activity_horizontal_margin"    android:paddingTop="@dimen/activity_vertical_margin"    tools:context=".MainActivity" ><!-- 创建数据库  按钮 -->    <Button        android:layout_width="match_parent"        android:layout_height="wrap_content"        android:text="创建SQLite数据库"        android:onClick="open"/>    <!-- 数据库 增加数据 按钮 -->    <Button        android:layout_width="match_parent"        android:layout_height="wrap_content"        android:text="增"        android:onClick="c"/>    <!-- 数据库 删除数据 按钮 -->        <Button        android:layout_width="match_parent"        android:layout_height="wrap_content"        android:text="删"        android:onClick="d"/>    <!-- 数据库 更新数据 按钮 -->            <Button        android:layout_width="match_parent"        android:layout_height="wrap_content"        android:text="改"        android:onClick="u"/>    <!-- 数据库 查询数据 按钮 -->    <Button        android:layout_width="match_parent"        android:layout_height="wrap_content"        android:text="查"        android:onClick="r"/>        <TextView         android:id="@+id/tv_main_infor"        android:layout_width="match_parent"        android:layout_height="0dip"        android:layout_weight="1"        android:textColor="#ff0000"/></LinearLayout>

代码文件：MainActivity.java

package com.yihui.sqlitedemo;import android.app.Activity;import android.content.Context;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import android.os.Bundle;import android.view.View;import android.widget.TextView;public class MainActivity extends Activity {private SQLiteDatabase sqliteDB;private TextView tv_main_infor;    @Override    protected void onCreate(Bundle savedInstanceState) {        super.onCreate(savedInstanceState);        setContentView(R.layout.activity_main);                tv_main_infor = (TextView) findViewById(R.id.tv_main_infor);               }        /*创建数据库*/    public void open(View v){    sqliteDB = openOrCreateDatabase("test.db", Context.MODE_PRIVATE, null);        sqliteDB.execSQL("drop table if exists person");    String createTableSQL = "create table person"                + " (_id integer primary key,"//编号                + "  name varchar(20),"//姓名                + "  age integer"//年龄                + " );";    sqliteDB.execSQL(createTableSQL);    tv_main_infor.setText("创建数据库成功:\n" + getDatabasePath("test.db").getPath());    }        /*增c - Create*/    public void c(View v){    String insertSQL = "insert into person values(?,?,?)";    sqliteDB.execSQL(insertSQL, new Object[]{1369,"李5辉",29});    sqliteDB.execSQL(insertSQL, new Object[]{1368,"李4辉",28});    sqliteDB.execSQL(insertSQL, new Object[]{1367,"李3辉",27});    sqliteDB.execSQL(insertSQL, new Object[]{1366,"李2辉",26});    sqliteDB.execSQL(insertSQL, new Object[]{1365,"李1辉",25});    tv_main_infor.setText("插入数据成功!");    }        /*删d - Delete*/    public void d(View v){    String deleteSQL = "delete from person where _id = ?";    sqliteDB.execSQL(deleteSQL, new Object[]{1365});    tv_main_infor.setText("1365 id用户删除数据成功!");    }        /*改u - Update*/    public void u(View v){    String updateSQL = "update person set name = ? where _id = ?";    sqliteDB.execSQL(updateSQL, new Object[]{"李update辉",1367});    tv_main_infor.setText("1367 id用户更新数据成功!");    }        /*查r - Retrieve*/    public void r(View v){    StringBuffer sb = null;    String selectSQL = "select * from person where name like ? order by _id";    Cursor rawQuery = sqliteDB.rawQuery(selectSQL,new String[]{"李%辉"});    if(rawQuery != null && rawQuery.getCount() > 0){    sb = new StringBuffer();    while(rawQuery.moveToNext()){    sb.append("id:" + rawQuery.getInt(0)).append("\t");    sb.append("姓名:" + rawQuery.getString(1)).append("\t");    sb.append("年龄:" +  rawQuery.getString(2)).append("\n");    }    }    tv_main_infor.setText(sb);    }}

运行效果：

             

2、通过继承SQLiteOpenHelper 帮助类来增删改查数据

使用步骤：㈠、继承SQLiteOpenHelper类

                       复写 public void onCreate(SQLiteDatabase db)                                                     //数据库创建时的回调方法

                              public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)   //数据库更新时的回调方法

                ㈡、通过子类getReadableDatabase() 、getWritableDatabase() 得到可读或可写的SQLiteDatabase

                ㈢、再通过SQLiteDatabase，执行exeSQL() 增删改查

Demo目录结构：

         

①、数据库帮助类SQLiteOpenHelper

package com.yihui.sqlitedemo.dbutils;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 SQLiteDBUtils extends SQLiteOpenHelper {private static final String TAG = "SQLiteDBUtils";public SQLiteDBUtils(Context context, String name, CursorFactory factory,int version) {super(context, name, factory, version);}/* 数据库创建时调用的回调方法   */@Overridepublic void onCreate(SQLiteDatabase db) {Log.i(TAG, "onCreate -- 数据库创建时调用的回调方法");String createTableSQL = "create table person"              + " (_id integer primary key,"//编号              + "  name varchar(20),"//姓名              + "  age integer"//年龄              + " );";db.execSQL(createTableSQL);}/* 数据库更新时调用的回调方法   */@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {if(oldVersion == 1 && newVersion == 2){//当数据库前一个版本为1，新版本为2时更新操作Log.i(TAG, "onUpgrade -- 数据库更新时调用的回调方法");String upgradeTableSQL = "alter table person"   + " add balance integer;";db.execSQL(upgradeTableSQL);}}}

②、实体类

package com.yihui.entities;public class Person {private int id;private String name;private int age;private int balance;public Person(int id, String name, int age) {this.id = id;this.name = name;this.age = age;}public int getId() {return id;}public void setId(int id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public int getAge() {return age;}public void setAge(int age) {this.age = age;}public int getBalance() {return balance;}public void setBalance(int balance) {this.balance = balance;}}

③、实体类DAO

package com.yihui.dao;import android.content.Context;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import android.util.Log;import com.yihui.entities.Person;import com.yihui.sqlitedemo.dbutils.SQLiteDBUtils;public class PersonDAO {private static final String TAG = "SQLiteDBUtils";private SQLiteDBUtils personSQLiteDBUtils;public PersonDAO(Context context){personSQLiteDBUtils = new SQLiteDBUtils(context, "hello.db", null, 1);}/* 增  */public void insert(Person person){SQLiteDatabase writableDatabase = personSQLiteDBUtils.getWritableDatabase();if(writableDatabase.isOpen()){writableDatabase.execSQL("insert into person(_id,name,age) values (?,?,?)", new Object[]{person.getId(),person.getName(),person.getAge()});writableDatabase.close();}}/* 删  */public void delete(Person person){SQLiteDatabase writableDatabase = personSQLiteDBUtils.getWritableDatabase();if(writableDatabase.isOpen()){writableDatabase.execSQL("delete from person where _id = ?", new Object[]{person.getId()});writableDatabase.close();}}/* 改   */public void update(Person person){SQLiteDatabase writableDatabase = personSQLiteDBUtils.getWritableDatabase();if(writableDatabase.isOpen()){writableDatabase.execSQL("update person set name = ?, age = ? where _id = ?", new Object[]{person.getName(),person.getAge(),person.getId()});writableDatabase.close();}}/* 查   */public void queryAll(){SQLiteDatabase readableDatabase = personSQLiteDBUtils.getReadableDatabase();if(readableDatabase.isOpen()){Cursor rawQuery = readableDatabase.rawQuery("select * from person", null);if(rawQuery!=null && rawQuery.getCount()>0){while(rawQuery.moveToNext()){Log.i(TAG, "person - _id:" + rawQuery.getInt(0) + " name:" + rawQuery.getString(1) + " age:" + rawQuery.getInt(2) + "\n");}}}}}

④、Junit测试

package com.yihui.sqlitedemo.junit;import android.test.AndroidTestCase;import com.yihui.dao.PersonDAO;import com.yihui.entities.Person;import com.yihui.sqlitedemo.dbutils.SQLiteDBUtils;public class TestDBSQLite extends AndroidTestCase {/* 创建数据库 或者 更新数据库 */public void testDBSQLiteOpenHelper(){SQLiteDBUtils dbSQLHelper = new SQLiteDBUtils(getContext(), "hello.db", null, 1);dbSQLHelper.getReadableDatabase();}/* 增   */public void testInsert(){PersonDAO personDAO = new PersonDAO(getContext());for(int i = 0; i < 10; i++){personDAO.insert(new Person(i, "terry" + i, 20+i));}}/* 删  */public void testDelete(){PersonDAO personDAO = new PersonDAO(getContext());personDAO.delete(new Person(0, "terry", 20));}/* 改  */public void testUpdate(){PersonDAO personDAO = new PersonDAO(getContext());personDAO.update(new Person(8, "李yi辉", 28));}/* 查   */public void testQueryAll(){PersonDAO personDAO = new PersonDAO(getContext());personDAO.queryAll();}}

http://blog.csdn.net/yihuiworld