Android(安卓)原生SQLite数据库操作实战
16lz
2021-12-04
-
创建DB工具类
MyDBHelper.java(创建数据库的操作)
package com.amos.android_db;import android.content.Context;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteOpenHelper;/** * Created by amosli on 14-6-12. */public class MyDBHelper extends SQLiteOpenHelper{ /** * * @param context */ public MyDBHelper(Context context) { super(context, "sqlitedb", null, 1); } /** * 数据库第一次创建的时候调用此方法 * @param db */ @Override public void onCreate(SQLiteDatabase db) { db.execSQL("create table if not exists person (personid integer primary key autoincrement ,name varchar(30) ,age integer(3) )"); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { }}
-
PersonDao.java (实现增删改查)
package com.amos.android_db.dao;import android.content.ContentValues;import android.content.Context;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import android.util.Log;import com.amos.android_db.MyDBHelper;import java.util.ArrayList;import java.util.List;/** * Created by amosli on 14-6-12. */public class PersonDao { private Context context; MyDBHelper dbHelper; public PersonDao(Context context) { this.context = context; dbHelper = new MyDBHelper(context); } /** * 添加一条记录 */ public void add(String name, int age) { SQLiteDatabase db = dbHelper.getWritableDatabase(); if (db.isOpen()) { ContentValues values = new ContentValues(); values.put("age", age); values.put("name", name); //不允许插入一个空值,如果contentvalue,一般第二个参 db.insert("person", null, values);//通过组拼完成的添加的操作 } db.close(); }}
-
测试添加方法
public void testAdd() throws Exception{ PersonDao personDao = new PersonDao(this.getContext()); personDao.add("amosli",10); personDao.add("amosli",10); for(int i=0;i<10;i++){ personDao.add("amos"+i,10+i); } }
-
删除数据
delete方法,主要是调用了SQLiteDatabase的delete方法.其实质上也是在拼sql语句.public void delete(String name) { SQLiteDatabase db = dbHelper.getWritableDatabase(); if (db.isOpen()) { db.delete("person", "name=?", new String[]{name}); db.close(); } } //测试 delete方法: public void testDelete() throws Exception{ PersonDao personDao = new PersonDao(this.getContext()); personDao.delete("amosli"); }
-
更新数据
public void update(String name, String newname, int newage) { SQLiteDatabase db = dbHelper.getWritableDatabase(); if (db.isOpen()) { ContentValues contentValues = new ContentValues(); contentValues.put("name", newname); contentValues.put("age", newage); db.update("person", contentValues, "name=?", new String[]{name}); db.close(); } } //测试update public void testUpdate() throws Exception{ PersonDao personDao = new PersonDao(this.getContext()); personDao.update("amos0","0amos",35); }
- 查询数据
public boolean find(String name) { boolean status_result = false; SQLiteDatabase db = dbHelper.getReadableDatabase();// public android.database.Cursor query(// String table,// String[] columns,// String selection,// String[] selectionArgs,// String groupBy,// String having,// String orderBy) if (db.isOpen()) { Cursor cursor = db.query("person", null, "name=?", new String[]{name}, null, null, null); if (cursor.moveToFirst()) { status_result = true; } cursor.close(); db.close(); } return status_result; } //测试select public void testFind() throws Exception{ PersonDao personDao = new PersonDao(this.getContext()); assertEquals(true,personDao.find("amos1")); }
- 查询所有数据
public List findAll(){ List persons = null; SQLiteDatabase db = dbHelper.getReadableDatabase(); if(db.isOpen()){ persons = new ArrayList(); Cursor cursor = db.query("person", null, null, null, null, null, null); while(cursor.moveToNext()){ Person person = new Person(); person.setName(cursor.getString(cursor.getColumnIndex("name"))); person.setAge(cursor.getInt(cursor.getColumnIndex("age"))); persons.add(person); } cursor.close(); db.close(); } return persons; } // 测试public void testFindAll() throws Exception{ PersonDao personDao = new PersonDao(getContext()); List personList = personDao.findAll(); for(Person person:personList){ Log.d("person:",person.toString()); } }
-
扩展 (事务)
这里以amos1向amos2转钱200元为例:1),amos1账户初始1000元,amos2账户初始0元.
2),从amos1中减去200元,amos2中加上200元,这两个步骤要么同时成功,要么同时失败,不能一方成功,另一主失败,这就是事务.
代码实现:升级数据库
package com.amos.android_db;import android.content.Context;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteOpenHelper;/** * Created by amosli on 14-6-12. */public class MyDBHelper extends SQLiteOpenHelper{ public MyDBHelper(Context context) { super(context, "sqlitedb", null, 2); } /** * 数据库第一次创建的时候调用此方法 * @param db */ @Override public void onCreate(SQLiteDatabase db) { db.execSQL("create table if not exists person (personid integer primary key autoincrement ,name varchar(30) ,age integer(3) )"); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { db.execSQL("alter table person add account integer null"); }}
在初始化时更新表的结构,添加account一列,用来表示账户余额.
在PersonDao中添加如下方法:
public void transferMoney() { SQLiteDatabase db = dbHelper.getWritableDatabase(); if(db.isOpen()){ try{ db.beginTransaction(); //给amos1账户里设置1000元,amost account=0; db.execSQL("update person set account=? where name = ?",new Object[]{1000,"amos1"}); db.execSQL("update person set account=? where name = ?",new Object[]{0,"amos2"}); //从amos1账户里扣除200元 db.execSQL("update person set account=account-? where name = ?",new Object[]{200,"amos1"}); //把amos1的钱转给amos2 db.execSQL("update person set account=account+? where name=?",new Object[]{200,"amos2"}); }catch(Exception e){ e.printStackTrace(); }finally{ //显示的设置数据事务是否成功 db.setTransactionSuccessful(); db.endTransaction(); db.close(); } } }
这里先beginTransaction,然后要注意的是setTransactionSuccessful和endTransaction.
public void testTransaction() throws Exception{ PersonDao personDao = new PersonDao(getContext()); personDao.transferMoney(); }
更多相关文章
- 浅谈Java中Collections.sort对List排序的两种方法
- Python list sort方法的具体使用
- python list.sort()根据多个关键字排序的方法实现
- Android(安卓)Battery一些信息获取方法
- android之activity中onSaveInstanceState和onRestoreInstanceSta
- 动态创建ImageView视图
- 高德地图Android,绘制自定义定位蓝点、marker、面
- [Android] ListView 结合SimpleAdapter使用
- Android菜单实现两种方式