Android SQLite数据库demo。架构组件Room

Jorkro

于 2025-02-06 16:17:01 发布

阅读量355

点赞数 5

文章标签：数据库 android sqlite

本文链接：https://blog.youkuaiyun.com/Jorkro/article/details/145476257

版权

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);



@Insert

void insertAll(List<User> users);



@Insert(onConflict = OnConflictStrategy.REPLACE)

public void insertUsers(User... users);



@Delete

void delete(User user);



@Update

public void updateUsers(List<User> users);

}




DAO 可以是一个接口，也可以是一个抽象类， Room会在编译时创建DAO的实现。



2.2 查询参数传递

@Query(“SELECT * FROM user WHERE uid IN (:userIds)”)

List 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);




看代码应该比较好理解， 方法中传递参数`arg`, 在sql语句中用`:arg`即可。编译时R



2.3 查询表中部分字段的信息



在实际某个业务场景中， 我们可能仅关心一个表部分字段的值，这时我仅需要查询关心的列即可。



定义子集的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 loadFullName();




> 这里定义的POJO也支持使用`@Embedded`



2.4 联表查询



Room支持联表查询，接口定义上与其他查询差别不大， 主要还是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 findBooksBorrowedByNameSync(String userName);

}




2.4 联表查询



Room支持联表查询，接口定义上与其他查询差别不大， 主要还是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 findBooksBorrowedByNameSync(String userName);

}




### Room提供了Migration类来实现数据库的升级:

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) {

@Override

public 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) {

@Override

public void migrate(SupportSQLiteDatabase database) {

    database.execSQL("ALTER TABLE Book "

            + " ADD COLUMN pub_year INTEGER");

}

};




在创建Migration类时需要指定`startVersion`和`endVersion`, 代码中`MIGRATION_1_2`和`MIGRATION_2_3`的startVersion和endVersion是递增的， Migration其实是支持从版本1直接升到版本3，只要其`migrate()`方法里执行的语句正常即可。那么Room是怎么实现数据库升级的呢？其实本质上还是调用`SQLiteOpenHelper.onUpgrade`，Room中自己实现了一个`SQLiteOpenHelper`， 在`onUpgrade()`方法被调用时触发`Migration`，当第一次访问数据库时，Room做了以下几件事：



*   创建Room Database实例

*   `SQLiteOpenHelper.onUpgrade`被调用，并且触发`Migration`

*   打开数据库



### 架构组件Room、demo了解：[https://download.youkuaiyun.com/download/meixi\_android/19386289]( )



### 原生sqlite、demo链接：[https://download.youkuaiyun.com/download/meixi\_android/10710400]( )



创建数据库版本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;

}



@Override

public void onCreate(SQLiteDatabase db) {

    db.execSQL(SQL.CREATE_TABLE_FAVORITE);



    // 若不是第一个版本安装，直接执行数据库升级

    // 请不要修改FIRST_DATABASE_VERSION的值，其为第一个数据库版本大小

    final int FIRST_DATABASE_VERSION = 1000;

    onUpgrade(db, FIRST_DATABASE_VERSION, DATABASE_VERSION);

}



@Override

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    // 使用for实现跨版本升级数据库

    for (int i = oldVersion; i < newVersion; i++) {

        switch (i) {



            default:

                break;

        }

    }

}

}




创建数据库语句：

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 " +

                ")";

}




实现增删改查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;



@Override

protected 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() {

        @Override

        public 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() {

        @Override

        public void onClick(View view) {

            getBai(MainActivity.this);

        }

    });

    button3.setOnClickListener(new View.OnClickListener() {

        @Override

        public void onClick(View view) {

           String ss= Delete(MainActivity.this,"1");

            Log.i("lgq","shanchu=g==="+ss);

        }

    });

    button4.setOnClickListener(new View.OnClickListener() {

        @Override

        public 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 "成功";