【Android學習專題】数据存储篇：SQLite

【Android學習專題】数据存储篇：SQLite

   SkySeraph Feb 5th 2012  SZTCL

Email：zgzhaobo@gmail.com    QQ：452728574

-----------------------------------------------------------------------------------------------------------------------------------------------------------

前一篇：【Android学习专题】数据存储篇：SharedPreferences/Files/SDCard

-----------------------------------------------------------------------------------------------------------------------------------------------------------

【SQLite】

-----------------------------------------------------------------------------------------------------------------------------------------------------------

Ⅰ 测试操作

1 操作（gif）

本来录制了gif，但是博客园限制了大小，无法上传，就截图一个吧。。。

2 SQLite数据库保存地址

 

　3 SQLite数据库查看（SQLiteSpy，该软件请参阅Refs-10）

 

-----------------------------------------------------------------------------------------------------------------------------------------------------------

Ⅱ 操作

 

* 1 在AndroidManifest文件中加入sdcard操作权限
　　* <!--在SDCard中创建与删除文件权限 -->
　　* <uses-permissioandroid:name="android.permission.MOUNT_UNMOUNT_FILESYSTEMS"/>
　　* <!--往SDCard写入数据权限 -->
　　* <uses-permissionandroid:name="android.permission.WRITE_EXTERNAL_STORAGE"/>
* 2 确认sdcard的存在
　　* android.os.Environment.getExternalStorageState().equals(android.os.Environment.MEDIA_MOUNTED)
* 3 获取扩展存储设备的文件目录
　　* android.os.Environment.getExternalStorageDirectory();

-----------------------------------------------------------------------------------------------------------------------------------------------------------

Ⅲ  常用方法

Cursor游标方法

-----------------------------------------------------------------------------------------------------------------------------------------------------------

Ⅳ 实例源码

1 java源码

public class sqlite extends Activity
{
    // 按钮和编辑框对象
    private Button mBtn1, mBtn2, mBtn3, mBtn4, mBtn5, mBtn6;
    private EditText mEt1,mEt2,mEt3;
    // 数据库对象
//SQLiteDatabase mDB = null;
    MyDBAdapter mDBAdapter;
    
    Context mContext = null; 
    
    // 添加记录时的id累加标记
    int sqlite_id = 0; 
    
    @Override
    protected void onCreate(Bundle savedInstanceState)
    {
        // TODO Auto-generated method stub
        super.onCreate(savedInstanceState);
        mContext = this;
        setContentView(R.layout.mydatastorage_sqlite);    
        
        mEt1 = (EditText) findViewById(R.id.mydatastorage_sqlite_ET01);
        mEt2 = (EditText) findViewById(R.id.mydatastorage_sqlite_ET02);
        mEt3 = (EditText) findViewById(R.id.mydatastorage_sqlite_ET03);
        mEt1.setInputType(InputType.TYPE_NULL);//禁止弹出键盘
        mEt2.setInputType(InputType.TYPE_NULL);
        mBtn1 = (Button) findViewById(R.id.mydatastorage_sqlite_Btn01);
        mBtn2 = (Button) findViewById(R.id.mydatastorage_sqlite_Btn02);
        mBtn3 = (Button) findViewById(R.id.mydatastorage_sqlite_Btn03);
        mBtn4 = (Button) findViewById(R.id.mydatastorage_sqlite_Btn04);
        mBtn5 = (Button) findViewById(R.id.mydatastorage_sqlite_Btn05);
        mBtn6 = (Button) findViewById(R.id.mydatastorage_sqlite_Btn06);

        mBtn1.setOnClickListener(new ClickEvent());
        mBtn2.setOnClickListener(new ClickEvent());
        mBtn3.setOnClickListener(new ClickEvent());
        mBtn4.setOnClickListener(new ClickEvent());
        mBtn5.setOnClickListener(new ClickEvent());
        mBtn6.setOnClickListener(new ClickEvent());        
    }
    
    class ClickEvent implements View.OnClickListener 
    {
        @Override
        public void onClick(View v)
        {
            if(v == mBtn1)        // 创建数据库
            {                
                mDBAdapter = new MyDBAdapter(mContext);
                mDBAdapter.open();
            }
            else if(v == mBtn2)    // 关闭数据库
            {
                mDBAdapter.close();
            }
            else if(v == mBtn3)    // 添加数据
            { 
                mDBAdapter = new MyDBAdapter(mContext);
                mDBAdapter.open();
                try
                {
                    int num = Integer.valueOf(mEt3.getText().toString()).intValue(); //获得EditText输入密码数据
                    Log.e("添加数据-密码", Integer.toString(num));  
                    String data = mEt2.getText().toString(); //获得EditText输入文本
                    Log.e("添加数据-文本", data);  
                    mDBAdapter.insertData(num, data);//插入
                    sqlite_id++;//记录id
                    
                } catch (Exception e)
                {
                    // TODO: handle exception
                }                
            }
            else if(v == mBtn4)    // 查询数据
            {    
                try
                {
                    int id = Integer.valueOf(mEt1.getText().toString()).intValue(); // 获得id
                    Log.e("查询数据-ID", Integer.toString(id));
                    Cursor cur = mDBAdapter.fetchData(id);
                    String data = cur.getString(2);
                    int num = Integer.valueOf(cur.getString(1)).intValue();
                    Log.e("查询数据-密码", Integer.toString(num));
                    Log.e("查询数据-文本", data);
                    mEt2.setText(data);
                    mEt3.setText(num);
                } catch (Exception e)
                {
                    // TODO: handle exception
                }
                
            }
            else if(v == mBtn5)    // 更新/修改数据
            {
                try
                {
                    int id = Integer.parseInt(mEt1.getText().toString()); //获得id
                    int num = Integer.parseInt(mEt3.getText().toString()); //获得数据
                    String data = mEt2.getText().toString(); //获得文本
                    mDBAdapter.updateData(id, num, data);
                } catch (Exception e)
                {
                    // TODO: handle exception
                }                
            }
            else if(v == mBtn6)    // 删除数据
            {
                try
                {
                    int id = Integer.parseInt(mEt1.getText().toString()); //获得数据
                    mDBAdapter.deleteData(id);
                    sqlite_id--;
                    if (sqlite_id < 0)
                    {
                        sqlite_id = 0;
                    }
                } catch (Exception e)
                {
                    // TODO: handle exception
                }
                
            }
        }
    }
}

2 xml布局文件

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="fill_parent"
    android:layout_height="fill_parent"
    android:orientation="vertical" >

    <TextView
android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:text="SQLite Test"
        android:textSize="25dp" />

    <LinearLayout
android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:orientation="vertical" >

        <Button
android:id="@+id/mydatastorage_sqlite_Btn01"
            android:layout_width="fill_parent"
            android:layout_height="wrap_content"
            android:text="创建SQLite数据库" >
        </Button>

        <Button
android:id="@+id/mydatastorage_sqlite_Btn02"
            android:layout_width="fill_parent"
            android:layout_height="wrap_content"
            android:text="关闭SQLite数据库" >
        </Button>

        <LinearLayout
android:layout_width="fill_parent"
            android:layout_height="wrap_content" >

            <Button
android:id="@+id/mydatastorage_sqlite_Btn03"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:text="插入数据" >
            </Button>

            <Button
android:id="@+id/mydatastorage_sqlite_Btn04"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:text="查询数据" >
            </Button>

            <Button
android:id="@+id/mydatastorage_sqlite_Btn05"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:text="修改数据" >
            </Button>

            <Button
android:id="@+id/mydatastorage_sqlite_Btn06"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:text="删除数据" >
            </Button>
        </LinearLayout>
    </LinearLayout>

    <LinearLayout
android:layout_width="fill_parent"
        android:layout_height="wrap_content" >

        <TextView
android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="ID："
        android:textSize="20dp" />
        
        <EditText
android:id="@+id/mydatastorage_sqlite_ET01"
            android:layout_width="fill_parent"
            android:layout_height="wrap_content"
            android:height="50px"
            android:hint="请输入待查询/删除数据的ID" >
        </EditText>
    </LinearLayout>

    <LinearLayout
android:layout_width="fill_parent"
        android:layout_height="wrap_content" >

        <TextView
android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="姓名:"
        android:textSize="20dp" />
        
        <EditText
android:id="@+id/mydatastorage_sqlite_ET02"
            android:layout_width="fill_parent"
            android:layout_height="wrap_content"
            android:height="50px"
            android:hint="请输入姓名"
            android:textStyle="bold" 
            android:singleLine="false" >
        </EditText>
    </LinearLayout>
    
    <LinearLayout
android:layout_width="fill_parent"
        android:layout_height="wrap_content" >

        <TextView
android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="密码:"
        android:textSize="20dp" />
        
        <EditText
android:id="@+id/mydatastorage_sqlite_ET03"
            android:layout_width="fill_parent"
            android:layout_height="wrap_content"
            android:height="50px"
            android:hint="请输入密码"
            android:inputType="textPassword">
        </EditText>
    </LinearLayout>
     
</LinearLayout>

3 MyDBAdapter类

package com.skyseraph.android.project.mydatastorage;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class MyDBAdapter
{
    // 用于打印log
    private static final String MyDBAdapter_TAG = "MyDBAdapter";
    // 表中一条数据的名称
    public static final String MyDBAdapter_KEY_ID = "_id";
    // 表中一条数据的内容
    public static final String MyDBAdapter_KEY_NUM = "num";
    // 表中一条数据的id
    public static final String MyDBAdapter_KEY_DATA = "data";
    // 数据库名称为data
    private static final String MyDBAdapter_DB_NAME = "MyDB.db";
    // 数据库表名
    private static final String MyDBAdapter_DB_TABLE = "table1";
    // 数据库版本
    private static final int MyDBAdapter_DB_VERSION = 1;
    // 本地Context对象
    private Context mContext = null;
    // 创建一个表
    private static final String MyDBAdapter_DB_CREATE = "CREATE TABLE " + MyDBAdapter_DB_TABLE + 
            " (" + MyDBAdapter_KEY_ID + " INTEGER PRIMARY KEY," 
            + MyDBAdapter_KEY_NUM + " INTERGER," + MyDBAdapter_KEY_DATA + " TEXT)";
    // 执行open（）打开数据库时，保存返回的数据库对象
    private SQLiteDatabase mSQLiteDatabase = null;
    // 由SQLiteOpenHelper继承过来
    private DatabaseHelper mDatabaseHelper = null;
    
    private static class DatabaseHelper extends SQLiteOpenHelper
    {
        /* 构造函数-创建一个数据库 */
        DatabaseHelper(Context context)
        {
            // 当调用getWritableDatabase() 或 getReadableDatabase()方法时 则创建一个数据库
            super(context, MyDBAdapter_DB_NAME, null, MyDBAdapter_DB_VERSION);
        }
        
        /* 创建一个表 */
        @Override
        public void onCreate(SQLiteDatabase db)
        {
            // 数据库没有表时创建一个
            db.execSQL(MyDBAdapter_DB_CREATE);
        }

        /* 升级数据库 */
        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
        {
            db.execSQL("DROP TABLE IF EXISTS notes");
            onCreate(db);
        }
    }
    
    /* 构造函数-取得Context */
    public MyDBAdapter(Context context)
    {
        mContext = context;
    }


    // 打开数据库，返回数据库对象
    public void open() throws SQLException
    {
        mDatabaseHelper = new DatabaseHelper(mContext);
        mSQLiteDatabase = mDatabaseHelper.getWritableDatabase();
    }


    // 关闭数据库
    public void close()
    {
        mDatabaseHelper.close();
    }

    /* 插入一条数据 */
    public long insertData(int num, String data)
    {
        ContentValues initialValues = new ContentValues();
        initialValues.put(MyDBAdapter_KEY_NUM, num);
        initialValues.put(MyDBAdapter_KEY_DATA, data);

        return mSQLiteDatabase.insert(MyDBAdapter_DB_TABLE, MyDBAdapter_KEY_ID, initialValues);
    }

    /* 删除一条数据 */
    public boolean deleteData(long rowId)
    {
        return mSQLiteDatabase.delete(MyDBAdapter_DB_TABLE, MyDBAdapter_KEY_ID + "=" + rowId, null) > 0;
    }

    /* 通过Cursor查询所有数据 */
    public Cursor fetchAllData()
    {
        return mSQLiteDatabase.query(MyDBAdapter_DB_TABLE, new String[] { MyDBAdapter_KEY_ID, 
                MyDBAdapter_KEY_NUM, MyDBAdapter_KEY_DATA }, null, null, null, null, null);
    }

    /* 查询指定数据 */
    public Cursor fetchData(long rowId) throws SQLException
    {

        Cursor mCursor =

        mSQLiteDatabase.query(true, MyDBAdapter_DB_TABLE, new String[] { MyDBAdapter_KEY_ID, 
                MyDBAdapter_KEY_NUM, MyDBAdapter_KEY_DATA }, MyDBAdapter_KEY_ID + "=" + rowId, 
                null, null, null, null, null);
        /*参数含义：#、表名称、列名称数组、条件子句、条件子句、分组列、分组条件、排序列、分页查询限制*/
        if (mCursor != null)
        {
            mCursor.moveToFirst();//移到第一条记录
        }
        return mCursor;

    }

    /* 更新一条数据 */
    public boolean updateData(long rowId, int num, String data)
    {
        ContentValues args = new ContentValues();
        args.put(MyDBAdapter_KEY_NUM, num);
        args.put(MyDBAdapter_KEY_DATA, data);

        return mSQLiteDatabase.update(MyDBAdapter_DB_TABLE, args, 
                MyDBAdapter_KEY_ID + "=" + rowId, null) > 0;
    }
}

-----------------------------------------------------------------------------------------------------------------------------------------------------------

Refs：

1 http://blog.csdn.net/jiahui524

2 http://www.cnblogs.com/linjiqin/archive/2011/05/26/2059182.html 

3 http://www.cnblogs.com/linjiqin/archive/2011/05/27/2059937.html

4 http://blog.csdn.net/hellogv/article/details/6019301

5 http://blog.csdn.net/hellogv/article/details/6011934

6 http://blog.csdn.net/like7xiaoben/article/details/7089887

7 http://blog.csdn.net/like7xiaoben/article/details/7098775

8 http://www.sqlite.org/

9 http://blog.csdn.net/xys289187120/article/details/6661099

10 http://download.csdn.net/download/xys289187120/3481140

-----------------------------------------------------------------------------------------------------------------------------------------------------------