SQLite CRUD操作

浏览数：33 / 时间：2015年06月12日

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:

  1 package dataBase.databasetest;
  2 
  3 import android.app.Activity;
  4 import android.content.ContentValues;
  5 import android.database.Cursor;
  6 import android.database.sqlite.SQLiteDatabase;
  7 import android.os.Bundle;
  8 import android.util.Log;
  9 import android.view.View;
 10 import android.view.View.OnClickListener;
 11 import android.widget.Button;
 12 import android.widget.Toast;
 13 
 14 
 15 public class MainActivity extends Activity {
 16     private MyDatabaseHelper dbHelper;
 17     @Override
 18     protected void onCreate(Bundle savedInstanceState) {
 19         super.onCreate(savedInstanceState);
 20         setContentView(R.layout.activity_main);
 21         //修改参数为2，执行onUpgrade()方法跟新数据库
 22         dbHelper=new MyDatabaseHelper(this, "BookStor.db", null, 2);  //实现构造函数，传入参数，第一个为context。第二个为数据库名称
 23         //创建表
 24         Button create=(Button)this.findViewById(R.id.creat);
 25         create.setOnClickListener(new OnClickListener() {
 26             @Override
 27             public void onClick(View arg0) {
 28                 dbHelper.getWritableDatabase();    
 29             }
 30         });
 31         
 32         //增加数据
 33         Button add=(Button)this.findViewById(R.id.add);
 34         add.setOnClickListener(new OnClickListener() {
 35             @Override
 36             public void onClick(View arg0) {
 37                 SQLiteDatabase db=dbHelper.getWritableDatabase();
 38                 ContentValues values=new ContentValues();
 39                 //开始组装数据
 40                 values.put("name", "Android第一行代码");
 41                 values.put("author", "郭霖");
 42                 values.put("price",66.78);
 43                 values.put("pages",400 );
 44                 //写入数据
 45                 db.insert("book", null, values);
 46                 values.clear();
 47                 //准备再次写入数据
 48                 values.put("name","java讲义");
 49                 values.put("author", "张三");
 50                 values.put("price", 66.76);
 51                 values.put("pages", 789);
 52                 db.insert("book", null, values);
 53                 values.clear();
 54                 Toast.makeText(getApplicationContext(), "数据写入成功", Toast.LENGTH_LONG).show();
 55             }
 56         });
 57         
 58         //更新数据
 59         Button updata=(Button)this.findViewById(R.id.updata);
 60         updata.setOnClickListener(new OnClickListener() {
 61             @Override
 62             public void onClick(View arg0) {
 63                 SQLiteDatabase db=dbHelper.getWritableDatabase();
 64                 ContentValues values=new ContentValues();
 65                 values.put("price", 100.00);
 66                 db.update("book", values, "name=?",new String[]{"平凡的世界"});
 67                 values.clear();
 68                 Toast.makeText(getApplicationContext(), "数据更新成功", Toast.LENGTH_SHORT).show();
 69                 
 70             }
 71         });
 72         
 73         //删除数据
 74         Button delete =(Button)this.findViewById(R.id.delete);
 75         delete.setOnClickListener(new OnClickListener() {
 76             @Override
 77             public void onClick(View arg0) {
 78                 SQLiteDatabase db=dbHelper.getWritableDatabase();
 79                 db.delete("book", "id>?", new String[]{"23"});     //第一个参数为表名，二三个参数为限制条件
 80                 Toast.makeText(getApplicationContext(), "数据删除成功", Toast.LENGTH_SHORT).show();
 81             }
 82         });
 83         
 84         //查询数据
 85         Button find =(Button)this.findViewById(R.id.find);
 86         find.setOnClickListener(new OnClickListener() {
 87             @Override
 88             public void onClick(View arg0) {
 89                 SQLiteDatabase db=dbHelper.getWritableDatabase();
 90                 Cursor cursor=db.query("book",null, null, null, null, null, null);
 91                 if(cursor.moveToFirst()){
 92                     do {
 93                         String name=cursor.getString(cursor.getColumnIndex("name"));
 94                         String author=cursor.getString(cursor.getColumnIndex("author"));
 95                         int pages=cursor.getInt(cursor.getColumnIndex("pages"));
 96                         double price=cursor.getDouble(cursor.getColumnIndex("price")); 
 97                         Log.d("MainActivity", name);
 98                         Log.d("MainActivity", author);
 99                         Log.d("MainActivity", String.valueOf(pages));
100                         Log.d("MainActivity", String.valueOf(price));
101                     } while (cursor.moveToNext());
102                 }
103                 cursor.close();
104                 Toast.makeText(getApplicationContext(), "数据查找完毕",Toast.LENGTH_SHORT).show();
105             }
106         });
107         
108         //事物
109         Button transAction=(Button)this.findViewById(R.id.transaction);
110         transAction.setOnClickListener(new OnClickListener() {
111             @Override
112             public void onClick(View arg0) {
113                 SQLiteDatabase db=dbHelper.getWritableDatabase();
114                 db.beginTransaction();//开启事务
115                 try {
116                     ContentValues values=new ContentValues();
117                     db.delete("book", "id=?", new String[]{"11"}); //删除
118                     values.put("name", "newnewnew");
119                     values.put("price",111);
120                     values.put("author", "牛人");
121                     values.put("pages", 222);
122                     db.update("book", values, "name=?", new String[]{"Android第一行代码"});
123                     Toast.makeText(getApplicationContext(), "事务执行完毕", Toast.LENGTH_SHORT).show();
124                     values.clear();
125                 } catch (Exception e) {
126                     // TODO: handle exception
127                 }finally{
128                     db.endTransaction();//关闭事务
129                 }
130             }
131         });
132     }
133 }