Android常用的room增删改查语句（外部数据库）

最近博主把一个以前用原生数据库写的单词APP中数据库部分改成了room，在这个过程中踩了很多坑

坑大概为两点

• 使用room增删改查的一些语句
• 导入外部数据库并对其表格内容进行增删改查

在项目最后是全部重构成功了，但是在处理外部数据库时遇到的坑比较多，一时间没整理出来（整理了一个demo，但是没整理完），所以这篇文章解决room增删改查的一些常用语句。

如果项目吃急，遇到外部数据库的一些问题，可通过公众号：计蒙不吃鱼 联系博主

相信看这篇文章的人应该都有room的使用基础了，所以直接上菜（以下图表格为例）。

如上图：表名为forget

1. 单词名称：wordname
2. 单词等级：wordclass
3. 单词时间：wordtime

插入数据（增）：

    //Forget为表的实体类
    @Insert
    void insertWords(Forget... forgets);

删除数据（删）：

    //1.删除数据，根据主键来删除数据的！！！
    @Delete
    void  deleteWords(Forget... forgets);

    //2.根据单词名称删除表中数据（单个）
    @Query("DELETE  FROM FORGET where wordname=:name")
    void  deleteWords(String name);
    
    //3.根据单词名称，单词等级删除表中数据（多个）
    @Query("DELETE  FROM FORGET where wordname=:name and wordclass=:wordclass")
    void  deleteWords(String name，String wordclass);

更新数据（改）：

    //更新数据 
    @Update
    void  updateWords(Forget... forgets);
    
    //根据单词名称修改单词等级
    @Query("UPDATE FORGET SET wordclass= :wordclasss WHERE wordname = :wordname")
    void  updateWords(String wordname,String wordclasss);

查询数据（查）：

    //查询全部数据
    @Query(" SELECT * FROM FORGET ORDER BY rowid DESC")
    LiveData<List<Forget>> getAllForgetLive();
   
    //查询某项的全部数据
    @Query(" SELECT wordname FROM FORGET")
    LiveData<List<Forget>> getAllForgetLive();  

    //查询表中某单词是否存在  存在返回值为1，不存在返回值为0.
    @Query("select 1 from forget where wordname =:wordname  limit 1")
    int forgetWordisExist(String wordname);
    
    //部分查询  大多用于分页加载
    @Query(" SELECT wordname,wordclass,wordtime FROM FORGET limit :start,:end")
     LiveData<List<Forget>> getHarfWordsLive(int start,int end);

    //根据单词名称得到此行的全部信息
    @Query("SELECT * FROM FORGET WHERE wordname= :name")
    Forget getForgetByName(String name);
    
     //查询某项的全部数据（模糊查询）
    @Query("SELECT * FROM 表名 WHERE 某一项 LIKE '%' || :name || '%'")
    List<User> getSubjectForSchool(String name);
  

Forget实体类如下

@Fts4
@Entity(tableName = "forget")
public class Forget {
    @Ignore
    @PrimaryKey(autoGenerate = true)
    private int rowid;
    @ColumnInfo(name="wordname")
    private String wordname;
    @ColumnInfo(name="wordclass")
    private String wordclass;
    @ColumnInfo(name="wordtime")
    private String wordtime;

    public String getWordname() {
        return wordname;
    }

    public void setWordname(String wordname) {
        this.wordname = wordname;
    }

    public String getWordclass() {
        return wordclass;
    }

    public void setWordclass(String wordclass) {
        this.wordclass = wordclass;
    }

    public String getWordtime() {
        return wordtime;
    }

    public void setWordtime(String wordtime) {
        this.wordtime = wordtime;
    }

    public Forget(String wordname, String wordclass, String wordtime) {
        this.wordname = wordname;
        this.wordclass = wordclass;
        this.wordtime = wordtime;
    }
}

Dao文件如下：

@Dao //访问数据库的一个接口
public interface WordDao {

    @Insert
    void insertWords(Forget... forgets);
    
    //根据单词名称删除表中数据
    @Query("DELETE  FROM FORGET where wordname=:name")
    void  deleteWords(String name);
    
    //根据单词名称更新表中数据
    @Query("UPDATE FORGET SET wordclass= :wordclasss WHERE wordname = :wordname")
    void  updateWords(String wordname,String wordclasss);
    
    //查询表中某数据是否存在
    @Query("select 1 from forget where wordname =:wordname  limit 1")
    int forgetWordisExist(String wordname);
    
    //分页加载：
   @Query(" SELECT example,english,phonetic,chinese FROM FOURS limit :start,:end")
     LiveData<List<Word>> getHarfWordsLive(int start,int end);
    //查询全部
    @Query(" SELECT * FROM FORGET ORDER BY rowid DESC")
    LiveData<List<Forget>> getAllForgetLive();
    
    //根据字段查询
    @Query("SELECT * FROM FORGET WHERE wordname= :name")
    Forget getForgetByName(String name);

}

DataBase文件如下

@Database(entities = {Forget.class},version =1,exportSchema = false)
public abstract class WordDataBase extends RoomDatabase {
    private static WordDataBase INSTANCE;
    static synchronized WordDataBase getDatabase(final Context context){
        if (INSTANCE==null){

                    INSTANCE= Room.databaseBuilder(context.getApplicationContext(),WordDataBase.class,"word.db")
                          .createFromAsset("word.db") //预填充数据库
                          .fallbackToDestructiveMigration()
                          .build();

        }
        return INSTANCE;

    }
    public abstract WordDao getWordDao();

}

Repository文件如下

class WordRepository {
    private LiveData<List<Forget>> allForgetLive;
    private WordDao wordDao;
    private WordDataBase wordDatabase;
    WordRepository(Context context){
        //单例
        wordDatabase=WordDataBase.getDatabase(context.getApplicationContext());
        allForgetLive=wordDao.getAllForgetLive();
    }
    public LiveData<List<Forget>> getAllForgetLive() {
        return allForgetLive;
    }
    public Forget getForgetByName(String name) {
        return wordDao.getForgetByName(name);
    }
    public int forgetWordisExist(final String wordname){
      return   wordDao.forgetWordisExist(wordname);
    }
    
    void insertWords(final Forget...forgets){
        RxBusMax.getInstance().chainProcess(new Func1() {
            @Override
            public Object call(Object o) {
                wordDao.insertWords(forgets);
                return null;
            }
        });

    }
    void deleteWords(final String name){
        RxBusMax.getInstance().chainProcess(new Func1() {
            @Override
            public Object call(Object o) {
                wordDao.deleteWords(name);
                return null;
            }
        });
    }
    
    void updateWords(final String wordname,final String wordclass){
        RxBusMax.getInstance().chainProcess(new Func1() {
            @Override
            public Object call(Object o) {
                wordDao.updateWords(wordname,wordclass);
                return null;
            }
        });
    }




}

ViewMode文件如下

public class WordViewModel extends AndroidViewModel {
          private  WordRepository wordRepository;
    public WordViewModel(@NonNull Application application) {
        super(application);
        //单例
        wordRepository=new WordRepository(application);

    }
    public LiveData<List<Forget>> getAllForgetLive() {
        return wordRepository.getAllForgetLive();
    }
  
    public void insertWords(Forget...forgets){
        wordRepository.insertWords(forgets);
    }
    public void deleteWords(String name){
        wordRepository.deleteWords(name);
    }
    
    public Forget getForgetByName(String name) {
       return  wordRepository.getForgetByName(name);
    }
    
    public void updateWords(String wordname,String wordclasss){
        wordRepository.updateWords(wordname,wordclasss);
    }
    
    public int forgetWordisExist(final String wordname){
       return wordRepository.forgetWordisExist(wordname);
    }
    
    public LiveData<List<Word>> getHalfWordsLive(int start,int end) {
        return wordRepository.getHalfwordLive(start,end);
    }

}