android 数据存储之SQLite

今天要完成的是对SQLite的访问，并且可以进行增删改查的操作。

1、首先我们顶一个类继承自SQLiteOpenHelper，这个类将帮助我们完成创建数据库，还有创建表以及字段

package com.example.phonedemo.sql;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class MySQLiteHelper extends SQLiteOpenHelper {

    private static String DATABASE_NAME = "phone.db";
    private static int DATABASE_VERSION = 1;
    private static String TABLENAME = "mytab";

    public MySQLiteHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        // TODO Auto-generated constructor stub
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // TODO Auto-generated method stub
        String sql = "CREATE TABLE " + TABLENAME + " ("
                + "id        INTEGER            PRIMARY KEY," 
                + "name        VARCHAR(50)        NOT NULL,"
                + "age         INTEGER            NOT NULL," 
                + "email    VARCHAR(50)        NOT NULL)";
        db.execSQL(sql);
        System.out.println("***** 创建： onCreate()");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // TODO Auto-generated method stub
        System.out.println("***** 更新： onUpgrade()");
        db.execSQL("DROP TABLE IF EXISTS " + TABLENAME);
        this.onCreate(db);
    }

}

2、我们写一个数据操作类，完成一些对数据库的简单操作。

package com.example.phonedemo.sql;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class MySQLiteOperate {

    private SQLiteDatabase db = null;
    private final String TABNAME = "mytab";

    public MySQLiteOperate(SQLiteDatabase db) {
        this.db = db;
    }

    public void add(String name, int age, String email) {
        // sql 语句拼接
        // String sql = "INSERT INTO " + TABNAME +
        // " (name, age, email) VALUES ('"
        // + name + "', " + age + ", '" + email + "')";
        // this.db.execSQL(sql);
        // 占位符形式sql语句
        // String sql = "INSERT INTO " + TABNAME +
        // "(name, age, email) VALUES (?, ?, ?)";
        // Object[] args = new Object[]{name, age, email};
        // this.db.execSQL(sql, args);
        ContentValues cv = new ContentValues();
        cv.put("name", name);
        cv.put("age", age);
        cv.put("email", email);
        this.db.insert(TABNAME, null, cv);
        this.db.close();
    }

    public void update(int id, String name, int age, String email) {
        // sql 语句拼接
        // String sql = "UPDATE " + TABNAME + " SET name='" + name + "', age="
        // + age + ", email='" + email + "' WHERE id=" + id;
        // this.db.execSQL(sql);
        // // 占位符形式sql语句
        // String sql = "UPDATE " + TABNAME + " SET name=?, age=?, email=?";
        // Object[] args = new Object[]{name, age, email};
        // this.db.execSQL(sql, args);

        ContentValues cv = new ContentValues();
        cv.put("name", name);
        cv.put("age", age);
        cv.put("email", email);
        String whereArgs = "id=?";
        String[] args = new String[] { String.valueOf(id) };
        this.db.update(TABNAME, cv, whereArgs, args);
        this.db.close();
    }

    public void delete(int id) {
        // sql 语句拼接
        // String sql = "DELETE FROM " + TABNAME +" WHERE id=" + id;
        // this.db.execSQL(sql);
        // // 占位符形式sql语句
        // String sql = "DELETE FROM " + TABNAME + " WHERE id=?";
        // Object[] args = new Object[]{id};
        // this.db.execSQL(sql, args);

        String whereClause = "id=?";
        String[] whereArgs = new String[] { String.valueOf(id) };
        this.db.delete(TABNAME, whereClause, whereArgs);
        this.db.close();
    }

    public List<String> getList() {
        List<String> all = new ArrayList<String>();
         String sql = "SELECT id, name, age, email FROM " + TABNAME;
         Cursor result = this.db.rawQuery(sql, null);
        for (result.moveToFirst(); !result.isAfterLast(); result.moveToNext()) {
            all.add("id: " + result.getInt(0) + ", name: "
                    + result.getString(1) + ", age: " + result.getInt(2));
        }
        result.close();
        this.db.close();
        return all;
    }

    /**
     * 功能：分页获取数据
     * @param currentPage 当前页
     * @param pageSize 每页显示的条数
     * @return
     */
    public List<String> getList(int currentPage, int pageSize) {
        List<String> list = new ArrayList<String>();
        String limit = (currentPage - 1) * pageSize + ", " + pageSize;
        Cursor result = this.db.query(TABNAME, null, null, null, null, null,
                null, limit);
        for (result.moveToFirst(); !result.isAfterLast(); result.moveToNext()) {
            list.add("id: " + result.getInt(0) + ", name: "
                    + result.getString(1) + ", age: " + result.getInt(2));
        }
        result.close();
        return list;
    }

    public int getCount() {
        String sql = "SELECT COUNT(id) FROM " + TABNAME;
        Cursor result = this.db.rawQuery(sql, null);
        result.moveToFirst();
        return result.getInt(0);
    }

}

在写SQL语句的时候，有三种形式的sql语句，第一种是标准的原始sql语句，第二种是占位符形式，第三种是android特有的形式，android的db对象其实都已经封装好增删改查的操作方法，只需要我们填写部分参数和条件可以了。详细可以去developer.android.com查询。

3、最后我们写一个activity，来测试一下这些功能。

package com.example.phonedemo;

import android.app.Activity;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;
import android.widget.FrameLayout.LayoutParams;
import android.widget.LinearLayout;

import com.example.phonedemo.sql.MySQLiteHelper;
import com.example.phonedemo.sql.MySQLiteOperate;

public class FileOperateBySQLite extends Activity {

    private LayoutParams wrap = new LayoutParams(LayoutParams.MATCH_PARENT,
            LayoutParams.WRAP_CONTENT);
    private LayoutParams match = new LayoutParams(LayoutParams.MATCH_PARENT,
            LayoutParams.MATCH_PARENT);

    private LinearLayout layout = null;
    
    private EditText id = null;
    private EditText name = null;
    private EditText age = null;
    private EditText email = null;

    private Button addBut = null;
    private Button updateBut = null;
    private Button deleteBut = null;

    private SQLiteOpenHelper helper = null;
    private MySQLiteOperate op = null;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        // TODO Auto-generated method stub
        super.onCreate(savedInstanceState);

        // 初始化数据库
        helper = new MySQLiteHelper(this);

        this.layout = new LinearLayout(this);
        this.layout.setOrientation(LinearLayout.VERTICAL);
        
        this.id = new EditText(this);
        this.layout.addView(this.id, wrap);
        
        this.name = new EditText(this);
        this.layout.addView(this.name, wrap);
        
        this.age = new EditText(this);
        this.layout.addView(this.age, wrap);
        
        this.email = new EditText(this);
        this.layout.addView(this.email, wrap);

        this.addBut = new Button(this);
        this.addBut.setText("增加数据");
        this.addBut.setOnClickListener(new OnClickListener() {

            @Override
            public void onClick(View v) {
                // TODO Auto-generated method stub
                FileOperateBySQLite.this.op = new MySQLiteOperate(FileOperateBySQLite.this.helper.getWritableDatabase());
                String name = FileOperateBySQLite.this.name.getText().toString();
                int age = Integer.parseInt(FileOperateBySQLite.this.age.getText().toString());
                String email = FileOperateBySQLite.this.email.getText().toString();
                FileOperateBySQLite.this.op.add(name, age, email);
            }
        });
        this.layout.addView(this.addBut, wrap);

        this.updateBut = new Button(this);
        this.updateBut.setText("修改数据");
        this.updateBut.setOnClickListener(new OnClickListener() {

            @Override
            public void onClick(View v) {
                // TODO Auto-generated method stub
                FileOperateBySQLite.this.op = new MySQLiteOperate(FileOperateBySQLite.this.helper.getReadableDatabase());
                int id = Integer.parseInt(FileOperateBySQLite.this.id.getText().toString());
                String name = FileOperateBySQLite.this.name.getText().toString();
                int age = Integer.parseInt(FileOperateBySQLite.this.age.getText().toString());
                String email = FileOperateBySQLite.this.email.getText().toString();
                FileOperateBySQLite.this.op.update(id, name, age, email);
            }
        });
        this.layout.addView(updateBut, wrap);

        this.deleteBut = new Button(this);
        this.deleteBut.setText("删除数据");
        this.deleteBut.setOnClickListener(new OnClickListener() {

            @Override
            public void onClick(View v) {
                FileOperateBySQLite.this.op = new MySQLiteOperate(FileOperateBySQLite.this.helper.getWritableDatabase());
                int id = Integer.parseInt(FileOperateBySQLite.this.id.getText().toString());
                FileOperateBySQLite.this.op.delete(id);
            }
        });
        this.layout.addView(this.deleteBut, wrap);
        super.addContentView(this.layout, match);

    }

}