Android SQLite数据库demo。架构组件Room
?Android架構(gòu)組件Room
Room主要包含三個(gè)部分:
- Database : 持有DB和DAO
- Entity : 定義POJO類,即數(shù)據(jù)表結(jié)構(gòu)
- DAO(Data Access Objects) : 定義訪問數(shù)據(jù)(增刪改查)的接口
- @Entity(tableName = "table_name**") 注解POJO類,定義數(shù)據(jù)表名稱;
- @PrimaryKey 定義主鍵,如果一個(gè)Entity使用的是復(fù)合主鍵,可以通過@Entity注解的primaryKeys 屬性定義復(fù)合主鍵:@Entity(primaryKeys = {"firstName", "lastName"})
- @ColumnInfo(name = “column_name”) 定義數(shù)據(jù)表中的字段名
- @Ignore 用于告訴Room需要忽略的字段或方法
- 建立索引:在@Entity注解的indices屬性中添加索引字段。例如:indices = {@Index(value = {"first_name", "last_name"}, unique = true), ...}, unique = true可以確保表中不會(huì)出現(xiàn){"first_name", "last_name"} 相同的數(shù)據(jù)。
?
1.2 Entitiy間的關(guān)系
不同于目前存在的大多數(shù)ORM庫,Room不支持Entitiy對象間的直接引用。(具體原因可以參考: Understand why Room doesn't allow object references)
但Room允許通過外鍵(Foreign Key)來表示Entity之間的關(guān)系。
?
@Entity(foreignKeys = @ForeignKey(entity = User.class,parentColumns = "id",childColumns = "user_id")) class Book {@PrimaryKeypublic int bookId;public String title;@ColumnInfo(name = "user_id")public int userId; }如上面代碼所示,Book對象與User對象是屬于的關(guān)系。Book中的user_id,對應(yīng)User中的id。 那么當(dāng)一個(gè)User對象被刪除時(shí), 對應(yīng)的Book會(huì)發(fā)生什么呢?
@ForeignKey注解中有兩個(gè)屬性onDelete和onUpdate, 這兩個(gè)屬性對應(yīng)ForeignKey中的onDelete()和onUpdate(), 通過這兩個(gè)屬性的值來設(shè)置當(dāng)User對象被刪除/更新時(shí),Book對象作出的響應(yīng)。這兩個(gè)屬性的可選值如下:
- CASCADE:User刪除時(shí)對應(yīng)Book一同刪除; 更新時(shí),關(guān)聯(lián)的字段一同更新
- NO_ACTION:User刪除時(shí)不做任何響應(yīng)
- RESTRICT:禁止User的刪除/更新。當(dāng)User刪除或更新時(shí),Sqlite會(huì)立馬報(bào)錯(cuò)。
- SET_NULL:當(dāng)User刪除時(shí), Book中的userId會(huì)設(shè)為NULL
- SET_DEFAULT:與SET_NULL類似,當(dāng)User刪除時(shí),Book中的userId會(huì)設(shè)為默認(rèn)值
2. 創(chuàng)建數(shù)據(jù)訪問對象(DAO)
?
@Dao public interface UserDao {@Query("SELECT * FROM user")List<User> getAll();@Query("SELECT * FROM user WHERE uid IN (:userIds)")List<User> loadAllByIds(int[] userIds);@Query("SELECT * FROM user WHERE first_name LIKE :first AND "+ "last_name LIKE :last LIMIT 1")User findByName(String first, String last);@Insertvoid insertAll(List<User> users);@Insert(onConflict = OnConflictStrategy.REPLACE)public void insertUsers(User... users);@Deletevoid delete(User user);@Updatepublic void updateUsers(List<User> users); }DAO 可以是一個(gè)接口,也可以是一個(gè)抽象類, Room會(huì)在編譯時(shí)創(chuàng)建DAO的實(shí)現(xiàn)。
?
2.2 查詢參數(shù)傳遞
?
@Query("SELECT * FROM user WHERE uid IN (:userIds)") List<User> loadAllByIds(int[] userIds);@Query("SELECT * FROM user WHERE first_name LIKE :first AND "+ "last_name LIKE :last LIMIT 1") User findByName(String first, String last);看代碼應(yīng)該比較好理解, 方法中傳遞參數(shù)arg, 在sql語句中用:arg即可。編譯時(shí)R
2.3 查詢表中部分字段的信息
在實(shí)際某個(gè)業(yè)務(wù)場景中, 我們可能僅關(guān)心一個(gè)表部分字段的值,這時(shí)我僅需要查詢關(guān)心的列即可。
定義子集的POJO類:
?
public class NameTuple {@ColumnInfo(name="first_name")public String firstName;@ColumnInfo(name="last_name")public String lastName; }在DAO中添加查詢方法:
?
@Query("SELECT first_name, last_name FROM user") public List<NameTuple> loadFullName();這里定義的POJO也支持使用@Embedded
2.4 聯(lián)表查詢
Room支持聯(lián)表查詢,接口定義上與其他查詢差別不大, 主要還是sql語句的差別。
?
@Dao public interface MyDao {@Query("SELECT * FROM book "+ "INNER JOIN loan ON loan.book_id = book.id "+ "INNER JOIN user ON user.id = loan.user_id "+ "WHERE user.name LIKE :userName")public List<Book> findBooksBorrowedByNameSync(String userName); }2.4 聯(lián)表查詢
Room支持聯(lián)表查詢,接口定義上與其他查詢差別不大, 主要還是sql語句的差別。
?
@Dao public interface MyDao {@Query("SELECT * FROM book "+ "INNER JOIN loan ON loan.book_id = book.id "+ "INNER JOIN user ON user.id = loan.user_id "+ "WHERE user.name LIKE :userName")public List<Book> findBooksBorrowedByNameSync(String userName); }Room提供了Migration類來實(shí)現(xiàn)數(shù)據(jù)庫的升級:
?
Room.databaseBuilder(getApplicationContext(), MyDb.class, "database-name").addMigrations(MIGRATION_1_2, MIGRATION_2_3).build();static final Migration MIGRATION_1_2 = new Migration(1, 2) {@Overridepublic void migrate(SupportSQLiteDatabase database) {database.execSQL("CREATE TABLE `Fruit` (`id` INTEGER, "+ "`name` TEXT, PRIMARY KEY(`id`))");} };static final Migration MIGRATION_2_3 = new Migration(2, 3) {@Overridepublic void migrate(SupportSQLiteDatabase database) {database.execSQL("ALTER TABLE Book "+ " ADD COLUMN pub_year INTEGER");} };在創(chuàng)建Migration類時(shí)需要指定startVersion和endVersion, 代碼中MIGRATION_1_2和MIGRATION_2_3的startVersion和endVersion是遞增的, Migration其實(shí)是支持從版本1直接升到版本3,只要其migrate()方法里執(zhí)行的語句正常即可。那么Room是怎么實(shí)現(xiàn)數(shù)據(jù)庫升級的呢?其實(shí)本質(zhì)上還是調(diào)用SQLiteOpenHelper.onUpgrade,Room中自己實(shí)現(xiàn)了一個(gè)SQLiteOpenHelper, 在onUpgrade()方法被調(diào)用時(shí)觸發(fā)Migration,當(dāng)?shù)谝淮卧L問數(shù)據(jù)庫時(shí),Room做了以下幾件事:
- 創(chuàng)建Room Database實(shí)例
- SQLiteOpenHelper.onUpgrade被調(diào)用,并且觸發(fā)Migration
- 打開數(shù)據(jù)庫
架構(gòu)組件Room、demo了解:https://download.csdn.net/download/meixi_android/19386289
?
?
原生sqlite、demo鏈接:https://download.csdn.net/download/meixi_android/10710400
?
創(chuàng)建數(shù)據(jù)庫版本v.1
/*** 作者:created by meixi* 郵箱:13164716840@163.com* 日期:2018/10/9 11*/ public class DBHelper extends SQLiteOpenHelper {private static final String DATABASE_NAME = "mall.db";private static final int DATABASE_VERSION = 1000;private static DBHelper instance = null;public DBHelper(Context context) {super(context, DATABASE_NAME, null, DATABASE_VERSION);}public synchronized static DBHelper getInstance(Context context) {if (instance == null) {instance = new DBHelper(context);}return instance;}@Overridepublic void onCreate(SQLiteDatabase db) {db.execSQL(SQL.CREATE_TABLE_FAVORITE);// 若不是第一個(gè)版本安裝,直接執(zhí)行數(shù)據(jù)庫升級// 請不要修改FIRST_DATABASE_VERSION的值,其為第一個(gè)數(shù)據(jù)庫版本大小final int FIRST_DATABASE_VERSION = 1000;onUpgrade(db, FIRST_DATABASE_VERSION, DATABASE_VERSION);}@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {// 使用for實(shí)現(xiàn)跨版本升級數(shù)據(jù)庫for (int i = oldVersion; i < newVersion; i++) {switch (i) {default:break;}}} }?
創(chuàng)建數(shù)據(jù)庫語句:
public class SQL {public static final String T_FAVORITE = "favorite";public static final String CREATE_TABLE_FAVORITE ="CREATE TABLE IF NOT EXISTS " + T_FAVORITE + "(" +"_id integer primary key autoincrement, " +"title VARCHAR, " +"url VARCHAR, " +"createDate VARCHAR " +")"; }實(shí)現(xiàn)增刪改查activity:
public class MainActivity extends AppCompatActivity {private static String CREATE_TABLE ="create table number(_id integer primary key autoincrement ,phone real)";int ab = 0,cd;@Overrideprotected void onCreate(Bundle savedInstanceState) {super.onCreate(savedInstanceState);setContentView(R.layout.activity_main);Button button1=findViewById(R.id.baocunid);Button button2=findViewById(R.id.duquid);Button button3=findViewById(R.id.shanchuid);Button button4=findViewById(R.id.xiugaiid);button1.setOnClickListener(new View.OnClickListener() {@Overridepublic void onClick(View view) {ab++;boolean nn = baocun(MainActivity.this, new String[] { "114499title","rullll---htt;s=="+ab,"加入的xxx" });Log.i("lgq","sbbbb---"+nn);}});button2.setOnClickListener(new View.OnClickListener() {@Overridepublic void onClick(View view) {getBai(MainActivity.this);}});button3.setOnClickListener(new View.OnClickListener() {@Overridepublic void onClick(View view) {String ss= Delete(MainActivity.this,"1");Log.i("lgq","shanchu=g==="+ss);}});button4.setOnClickListener(new View.OnClickListener() {@Overridepublic void onClick(View view) {String ss = updata(MainActivity.this,"");Log.i("lgq","xiugg=g==="+ss);}});}public static boolean baocun(Context context, String[] values) {SQLiteDatabase db2=null;DBHelper2 dbHelper = null;try {dbHelper =new DBHelper2(context);db2 = dbHelper.getWritableDatabase();db2=dbHelper.getWritableDatabase();db2.beginTransaction();db2.execSQL("insert into favorite (title,url,deleted) values (?,?,?)",values);db2.setTransactionSuccessful();db2.endTransaction();return true;} catch (Exception e) {e.printStackTrace();return false;}finally{}}public List<String> getBai(Context context){List<String> list = new ArrayList<String>();SQLiteDatabase db2=null;DBHelper2 dbHelper = null;try {dbHelper= new DBHelper2(context);db2=dbHelper.getWritableDatabase();Cursor cursor =db2.rawQuery("select _id,title,deleted from favorite", null);while(cursor.moveToNext()){cd++; // int dbid = cursor.getInt(cursor.getColumnIndex("_id"));int id=cursor.getInt(cursor.getColumnIndex("_id"));String haoma = cursor.getString(cursor.getColumnIndex("title"))+"";String names = cursor.getString(cursor.getColumnIndex("deleted"));list.add(haoma);System.out.println("lgqs======"+haoma+"......."+names+" "+cd+" id=== "+id);}db2.setTransactionSuccessful();db2.endTransaction();} catch (Exception e) {e.printStackTrace();}return list;}public static String Delete(Context context,String string){SQLiteDatabase db2=null;DBHelper2 dbHelper = null;try {dbHelper =new DBHelper2(context);db2 = dbHelper.getWritableDatabase();db2=dbHelper.getWritableDatabase();db2.beginTransaction();db2.execSQL("delete from favorite where _id=?",new String[] { string });db2.setTransactionSuccessful();db2.endTransaction();return "成功";} catch (Exception e) {e.printStackTrace();return "失敗";}}public static String updata(Context context,String string){SQLiteDatabase db2=null;DBHelper2 dbHelper = null;try {dbHelper =new DBHelper2(context);db2 = dbHelper.getWritableDatabase();db2=dbHelper.getWritableDatabase();db2.beginTransaction();db2.execSQL("update favorite set title ="+"2222999"+" where url =?",new String[]{"rullll---htt;s==3"});db2.setTransactionSuccessful();db2.endTransaction();return "成功";} catch (Exception e) {e.printStackTrace();return "失敗";}} }?
總結(jié)
以上是生活随笔為你收集整理的Android SQLite数据库demo。架构组件Room的全部內(nèi)容,希望文章能夠幫你解決所遇到的問題。
- 上一篇: Yii2 mongodb 扩展的whe
- 下一篇: HenCoder文章汇总