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:
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 }
3:查看数据库数据可以通过在dos中运行 adb shell 或者 SQLite Expert软件查看。
