今天要完成的是对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
     
       getList() {        List
      
        all = new ArrayList
       
        ();         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
        
          getList(int currentPage, int pageSize) {        List
         
           list = new ArrayList
          
           (); 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);    }}