SQLite CRUD操作

SQLite CRUD操作代码实例：

1：首先创建一个继承了SQLiteOpenHelper类的MyDatabaseHelper类。实现他的onCreate(SQLiteDatabase db)

     onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)方法。

     

package dataBase.databasetest;

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

public class MyDatabaseHelper extends SQLiteOpenHelper {
    private Context mContext;
    
    public MyDatabaseHelper(Context context, String name,
            CursorFactory factory, int version) {
        super(context, name, factory, version);
        mContext=context;
    }
    
    public static final String CREATE_BOOK="create table book("    //创建book表
            +"id integer primary key autoincrement,"
            +"author text,"
            +"price real,"
            +"pages integer,"
            +"name text)";
     public static final String CREATE_CATEGORY="create table category("  //创建category表
            +"id integer primary key autoincrement,"
            +"category_name text,"
            +"category_code integer)";
    @Override
    public void onCreate(SQLiteDatabase db) {
        // TODO 自动生成的方法存根
        db.execSQL(CREATE_BOOK);
        db.execSQL(CREATE_CATEGORY);
        Toast.makeText(mContext, "创建数据库成功", Toast.LENGTH_LONG).show();
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // TODO 自动生成的方法存根
        db.execSQL("drop table if exists book");      //如果已有这两个表。先删除，
        db.execSQL("drop table if exists category");  //在调用onCreate()分别创建
        onCreate(db);
    }
    
}

2.在MainActivity中通过几个按钮事件测试对数据的CRUD:

package dataBase.databasetest;

import android.app.Activity;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.Toast;


public class MainActivity extends Activity {
    private MyDatabaseHelper dbHelper;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        //修改参数为2，执行onUpgrade()方法跟新数据库
        dbHelper=new MyDatabaseHelper(this, "BookStor.db", null, 2);  //实现构造函数，传入参数，第一个为context。第二个为数据库名称
        //创建表
        Button create=(Button)this.findViewById(R.id.creat);
        create.setOnClickListener(new OnClickListener() {
            @Override
            public void onClick(View arg0) {
                dbHelper.getWritableDatabase();    
            }
        });
        
        //增加数据
        Button add=(Button)this.findViewById(R.id.add);
        add.setOnClickListener(new OnClickListener() {
            @Override
            public void onClick(View arg0) {
                SQLiteDatabase db=dbHelper.getWritableDatabase();
                ContentValues values=new ContentValues();
                //开始组装数据
                values.put("name", "Android第一行代码");
                values.put("author", "郭霖");
                values.put("price",66.78);
                values.put("pages",400 );
                //写入数据
                db.insert("book", null, values);
                values.clear();
                //准备再次写入数据
                values.put("name","java讲义");
                values.put("author", "张三");
                values.put("price", 66.76);
                values.put("pages", 789);
                db.insert("book", null, values);
                values.clear();
                Toast.makeText(getApplicationContext(), "数据写入成功", Toast.LENGTH_LONG).show();
            }
        });
        
        //更新数据
        Button updata=(Button)this.findViewById(R.id.updata);
        updata.setOnClickListener(new OnClickListener() {
            @Override
            public void onClick(View arg0) {
                SQLiteDatabase db=dbHelper.getWritableDatabase();
                ContentValues values=new ContentValues();
                values.put("price", 100.00);
                db.update("book", values, "name=?",new String[]{"平凡的世界"});
                values.clear();
                Toast.makeText(getApplicationContext(), "数据更新成功", Toast.LENGTH_SHORT).show();
                
            }
        });
        
        //删除数据
        Button delete =(Button)this.findViewById(R.id.delete);
        delete.setOnClickListener(new OnClickListener() {
            @Override
            public void onClick(View arg0) {
                SQLiteDatabase db=dbHelper.getWritableDatabase();
                db.delete("book", "id>?", new String[]{"23"});     //第一个参数为表名，二三个参数为限制条件
                Toast.makeText(getApplicationContext(), "数据删除成功", Toast.LENGTH_SHORT).show();
            }
        });
        
        //查询数据
        Button find =(Button)this.findViewById(R.id.find);
        find.setOnClickListener(new OnClickListener() {
            @Override
            public void onClick(View arg0) {
                SQLiteDatabase db=dbHelper.getWritableDatabase();
                Cursor cursor=db.query("book",null, null, null, null, null, null);
                if(cursor.moveToFirst()){
                    do {
                        String name=cursor.getString(cursor.getColumnIndex("name"));
                        String author=cursor.getString(cursor.getColumnIndex("author"));
                        int pages=cursor.getInt(cursor.getColumnIndex("pages"));
                        double price=cursor.getDouble(cursor.getColumnIndex("price")); 
                        Log.d("MainActivity", name);
                        Log.d("MainActivity", author);
                        Log.d("MainActivity", String.valueOf(pages));
                        Log.d("MainActivity", String.valueOf(price));
                    } while (cursor.moveToNext());
                }
                cursor.close();
                Toast.makeText(getApplicationContext(), "数据查找完毕",Toast.LENGTH_SHORT).show();
            }
        });
        
        //事物
        Button transAction=(Button)this.findViewById(R.id.transaction);
        transAction.setOnClickListener(new OnClickListener() {
            @Override
            public void onClick(View arg0) {
                SQLiteDatabase db=dbHelper.getWritableDatabase();
                db.beginTransaction();//开启事务
                try {
                    ContentValues values=new ContentValues();
                    db.delete("book", "id=?", new String[]{"11"}); //删除
                    values.put("name", "newnewnew");
                    values.put("price",111);
                    values.put("author", "牛人");
                    values.put("pages", 222);
                    db.update("book", values, "name=?", new String[]{"Android第一行代码"});
                    Toast.makeText(getApplicationContext(), "事务执行完毕", Toast.LENGTH_SHORT).show();
                    values.clear();
                } catch (Exception e) {
                    // TODO: handle exception
                }finally{
                    db.endTransaction();//关闭事务
                }
            }
        });
    }
}

3:查看数据库数据可以通过在dos中运行 adb shell 或者 SQLite Expert软件查看。