Android 操作Sqlite

首先要用一个类来继承SQLiteOpenHelper，并必须实现  

 public DatabaseHelper(Context context, String name, CursorFactory factory,
            int version) {
        super(context, name, factory, version);
    }

具体类如下（关键地方我都加了注释）：

package com.example.sqllitedemo;

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

public class DatabaseHelper extends SQLiteOpenHelper {

    private static final int VERSION = 2;

    // SQLiteOpenHelper子类必须有该构造函数
    // 第一次参数Activity对象，第二个表名，第三个暂时空值，第四个数据库的版本号
    public DatabaseHelper(Context context, String name, CursorFactory factory,
            int version) {
        super(context, name, factory, version);
        // TODO Auto-generated constructor stub
    }

    public DatabaseHelper(Context context, String name, int version) {
        this(context, name, null, version);
    }

    public DatabaseHelper(Context context, String name) {
        this(context, name, VERSION);
    }

    // 该函数第一次创建数据库的时候执行，实际上是在第一次得到SqliteDatabase对象的时候执行
    @Override
    public void onCreate(SQLiteDatabase db) {
        // TODO Auto-generated method stub
        System.out.println("create a database");
        db.execSQL("create table user(id int,name verchar(20))");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // TODO Auto-generated method stub
        System.out.println("update a database");
    }
}

调用代码如下：

package com.example.sqllitedemo;

import android.app.Activity;
import android.app.ActionBar;
import android.app.Fragment;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.ArrayAdapter;
import android.widget.Button;
import android.widget.AutoCompleteTextView;

public class MainActivity extends Activity implements OnClickListener {


    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        Button btnCreate = (Button) findViewById(R.id.btnCreateDatabase);
        btnCreate.setOnClickListener(this);

        Button btnUpdatDatabase = (Button) findViewById(R.id.btnUpdateDatabase);
        btnUpdatDatabase.setOnClickListener(this);

        Button btnInsert = (Button) findViewById(R.id.btnInsert);
        btnInsert.setOnClickListener(this);

        Button btnUpdate = (Button) findViewById(R.id.btnUpdate);
        btnUpdate.setOnClickListener(this);

        Button btnQuery = (Button) findViewById(R.id.btnQuery);
        btnQuery.setOnClickListener(this);
    }

    @Override
    public void onClick(View v) {
        // TODO Auto-generated method stub
        switch (v.getId()) {
        case R.id.btnCreateDatabase:
            DatabaseHelper helper = new DatabaseHelper(this, "School_db");
            SQLiteDatabase db = helper.getReadableDatabase();
            break;
        case R.id.btnUpdateDatabase:
            DatabaseHelper helper1 = new DatabaseHelper(this, "School_db", 2);
            SQLiteDatabase db1 = helper1.getReadableDatabase();
            break;
        case R.id.btnInsert:
            // 用于存储数据的键值对，键为列名
            ContentValues values = new ContentValues();
            values.put("name", "张山");
            values.put("id", 1);
            DatabaseHelper helper2=new DatabaseHelper(this,"School_db");
            SQLiteDatabase db2=helper2.getWritableDatabase();
            db2.insert("user", null, values);
            //方法二、直接使用ExecSQL
            db2.execSQL("insert into user(id,name) values(?,?)", new Object[]{2,"李四"});
            break;
        case R.id.btnUpdate:
            DatabaseHelper helper3=new DatabaseHelper(this,"School_db");
            SQLiteDatabase db3=helper3.getWritableDatabase();
            ContentValues values1=new ContentValues();
            values1.put("name", "zhangshan");
            
            db3.update("user", values1, "id=?"    , new String[]{"1"});
            break;
        case R.id.btnQuery:
            DatabaseHelper helper4=new DatabaseHelper(this,"School_db");
            SQLiteDatabase db4=helper4.getReadableDatabase();
            Cursor cursor=db4.query("user",new String[]{"id","name"},"id=?",new String[]{"1"},
                    "","","");
            while(cursor.moveToNext())
            {
                String name=cursor.getString(cursor.getColumnIndex("name"));
                System.out.println(name);
            }
            break;

        default:
            break;
        }
    }
}

除了上述的crud的方法，还可以使用下面的方式

DatabaseHelper myDBHelper=new DatabaseHelper(this,"School_db");
//增加
    public void addPerson(String name) {
        SQLiteDatabase database = myDBHelper.getWritableDatabase();
        //先判断数据库是否可用
        if (database.isOpen()) {

            database.execSQL("insert into user(name) values(?,?)", new Object[]{name});
            database.close();
        }
    }

    //查找
    public boolean findPerson(String name) {
        boolean result = false;
        SQLiteDatabase database = myDBHelper.getReadableDatabase();
        if (database.isOpen()) {
            Cursor cursor = database.rawQuery("select * from user where name=?", new String[]{name});
            if (cursor.moveToFirst()) {//游标是否移动到下一行,如果是,那说明有数据返回
                Log.d(tag, "count:" + cursor.getColumnCount());
                int nameIndex = cursor.getColumnIndex("name");
                Log.d(tag, "name:" + cursor.getString(nameIndex));
                cursor.close();
                result = true;
            } else {
                result = false;

            }
            database.close();
        }
        return result;
    }

    //删除一条数据
    public void deletePerson(String name) {
        SQLiteDatabase database = myDBHelper.getWritableDatabase();
        if (database.isOpen()) {
            database.execSQL("delete from user where name=?", new Object[]{name});
        }
        database.close();
    }

    //更新一条数据
    public void updatePerson( String newName,String name) {
        SQLiteDatabase database = myDBHelper.getWritableDatabase();
        if (database.isOpen()) {
            database.execSQL("update user set name=? where name=?", new Object[]{newName, name});
        }
        database.close();
    }

    //查找所有person
    public List<user> findAllPerson(){
        List<user> userList = new ArrayList<user>();
        SQLiteDatabase database = myDBHelper.getReadableDatabase();
        if(database.isOpen()){
            Cursor cursor = database.rawQuery("select * from user", null);
            while(cursor.moveToNext()){
                int nameIndex = cursor.getColumnIndex("name");
  

                Person  person = new Person(name);

                personList.add(user);
            }

        }
        database.close();
        return uerList;
    }