1，背景

在做一个工具类的App，想把部分流程离线化，这个时候需要使用到数据库，但是现在App开发中，尽量都是把相关的业务放到后台，App做展示逻辑，所以关于数据库的使用基本上忘的差不多了，网上查了很多的资料，现在把自己关于数据库的相关总结在这里记录下，方便自己以后学习

2，数据库基础知识

说起数据库肯定，离不开各种Sql命令了 下面是具体的sql命令教程，可以在这里学习

www.w3school.com.cn/sql/index.a…   

下面是关于数据库的一些基本命令

创建数据库
CREATE DATABASE database_name
创建表

CREATE TABLE Persons(
Id_P int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

增
INSERT INTO Persons VALUES ('Gates', 'Bill', 'Xuanwumen 10', 'Beijing')

删
DELETE FROM Person WHERE LastName = 'Wilson'

改
UPDATE Person SET FirstName = 'Fred' WHERE LastName = 'Wilson'

查
SELECT LastName,FirstName FROM Persons
SELECT * FROM Persons

3，Android中的数据库

1，SQLite

SqlLite是什么？
SqlLite是一种轻量级的关系型数据库


SqlLite的创建

1，创建DB

通过SqlLiteHelper创建

dbHelper = new MyDatabaseHelper( this, "glodStore.db", null, 1);
db=dbHelper.getWritableDatabase();

直接传数据库的名字，那么数据库直接创建在内部存储的位置
/data/data/<package_name>/databases/    

dbHelper = new MyDatabaseHelper( this, getExternalFilesDir(null)+"/glodStore.db", null, 1);
db=dbHelper.getWritableDatabase();
传数据库的路径，可以创建到对应的文件夹下面


创建table，在SqlLiteHelper的onCreate方法中
String sql = "create table manager(id integer primary key autoincrement,name text ,password text)”
db.execSQL(sql);


2，增删改查
增
String sql="insert into stock(name,buyprice,number,buyfee) values('"+name+"',"+price+","+num+","+buyfee+")";
db.execSQL(sql);

删
String sql="DELETE FROM stock where id="+strid;
db.execSQL(sql);

改
String sql="update stock set sellprice="+strsell+",sellfee="+strfee+",selltax="+strtax+",profit="+strfit+",selldate=datetime(CURRENT_TIMESTAMP,'localtime') where id="+Integer.toString(id);
db.execSQL(sql);

查
Cursor cursor = db.rawQuery("select id,number,buyprice,buyfee,sellprice,profit from stock where name='"+stockname+"' order by sellprice ASC,buyprice ASC",null);
while(cursor.moveToNext()){
    int id = cursor.getInt(cursor.getColumnIndex("id"));
    float buyprice = cursor.getFloat(cursor.getColumnIndex("buyprice"));
    float buyfee = cursor.getFloat(cursor.getColumnIndex("buyfee"));
    int num = cursor.getInt(cursor.getColumnIndex("number"));
    float sellprice = cursor.getFloat(cursor.getColumnIndex("sellprice"));
    float profit = cursor.getFloat(cursor.getColumnIndex("profit"));
    float cost=(buyprice*num*1.001f+buyfee*2.0f)/num;
    cost=Math.round(cost*100)/100.0f;
    stockitem st = new stockitem(id,sellprice,buyprice,buyfee,Float.toString(cost),Float.toString(profit),Integer.toString(num));
    stockitemlist.add(st);

}

2，Room

Orm框架—将Java对象映射到数据库表(称为ORM,对象/映射关系)，使用一个简单的面向对象的API来存储，更新，删除，查询数据库

    Room是Google官方提供的ORM框架，本质上，Room是在SQLite提供一个抽象层，方便使用，同时帮助用户更流畅的访问数据库

    Room的使用非常简单，
    Room主要有三个组件
    Database
    Entity
    Dao

1，Database

@Database(entities = {Emperor.class}, version = 3, exportSchema = true)
public abstract class MyDatabase extends RoomDatabase {
    private static final String DATABASE_NAME = "emperor_db";
    private static MyDatabase databaseInstance;

    public static synchronized MyDatabase getDatabaseInstance(Context context) {
        if (databaseInstance == null) {
            databaseInstance = Room
                    .databaseBuilder(context.getApplicationContext(), MyDatabase.class, DATABASE_NAME)
                    .addMigrations(MIGRATION_1_2, MIGRATION_2_3)
                    //为了防止数据库升级失败导致崩溃，加入该方法可以在出现异常时创建数据表而不崩溃，但表中数据会丢失
                    .fallbackToDestructiveMigration()
                    .build();
        }
        return databaseInstance;
    }

    public abstract EmperorDao getEmperorDao();

    //升级相关
    private static final Migration MIGRATION_1_2 = new Migration(1, 2) {
        @Override
        public void migrate(@NonNull SupportSQLiteDatabase database) {
            //在这里做升级操作，比如增加或者减少表中的字段
            database.execSQL("ALTER TABLE emperor_table ADD COLUMN gender TEXT");
        }
    };

    private static final Migration MIGRATION_2_3 = new Migration(2, 3) {
        @Override
        public void migrate(@NonNull SupportSQLiteDatabase database) {
            //创建临时表
            database.execSQL("CREATE TABLE tem_emperor (" + "id INTEGER PRIMARY KEY NOT NULL," +
                    "emperor_name TEXT," +
                    "age INTEGER," +
                    "gender TEXT)");
            //将数据导入临时表
            database.execSQL("INSERT INTO tem_emperor (id,emperor_name,age,gender) SELECT id,emperor_name,age,gender FROM emperor_table");
            //删除原表
            database.execSQL("DROP TABLE emperor_table");
            //临时表改成原表的名字
            database.execSQL("ALTER TABLE tem_emperor RENAME TO emperor_table");
        }
    };
}



2,Entity

@Entity(tableName = "emperor_table")
public class Emperor {

    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "id", typeAffinity = ColumnInfo.INTEGER)
    public int id;
    @ColumnInfo(name = "emperor_name", typeAffinity = ColumnInfo.TEXT)
    public String name;
    @ColumnInfo(name = "age", typeAffinity = ColumnInfo.INTEGER)
    public Integer age;
    //增加一个性别的字段
    @ColumnInfo(name = "gender", typeAffinity = ColumnInfo.TEXT)
    public String gender;

    public Emperor(int id, String name, Integer age) {
        this.id = id;
        this.name = name;
        this.age = age;
    }

    @Ignore
    public Emperor(int id, String name, Integer age, String gender) {
        this.id = id;
        this.name = name;
        this.age = age;
        this.gender = gender;
    }

    @Ignore
    public Emperor(String name, Integer age) {
        this.name = name;
        this.age = age;
    }

    @Override
    public String toString() {
        return "Emperor{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age='" + age + '\'' +
                ", gender='" + gender + '\'' +
                '}';
    }
}

3,Dao
@Dao
public interface EmperorDao {

    //增
    @Insert
    public void insertEmperor(Emperor... emperor);

    //删
    @Delete
    public void deleteEmperor(Emperor... emperor);

    //改
    @Update
    public void updateEmperor(Emperor... emperor);

    //查符合指定条件的某一个
    @Query("SELECt * FROM emperor_table WHERE id=:id")
    public Emperor queryEmperorById(int id);

    //查所有符合条件的
    @Query("SELECt * FROM emperor_table WHERE age=:age")
    public List<Emperor> queryEmperorsByAge(Integer age);

    //查所有
    @Query("SELECt * FROM emperor_table")
    public List<Emperor> queryEmperors();

    //查所有，使用LiveData将结果List<Emperor>包装起来
    @Query("SELECt * FROM emperor_table")
    public List<Emperor> queryEmperorsByLiveData();
}

4，使用
@Override
public void onClick(View v) {
    switch (v.getId()) {
        case R.id.btn_insertEmperor:
            new Thread(() -> {
                Emperor caoCao = new Emperor(1, "曹操", 54);
                Emperor liuBei = new Emperor("刘备", 34);
                Emperor sunQuan = new Emperor(4, "孙权", 18);
                dao.insertEmperor(caoCao, liuBei, sunQuan);
            }).start();
            break;
        case R.id.btn_deleteEmperor:
            new Thread(() -> {
                Emperor caoCao = dao.queryEmperorById(1);
                Emperor liuBei = dao.queryEmperorById(2);
                dao.deleteEmperor(caoCao, liuBei);
            }).start();
            break;
        case R.id.btn_updateEmperor:
            new Thread(() -> {
                Emperor caoCao = dao.queryEmperorById(1);
                caoCao.age = 34;
                Emperor liuBei = dao.queryEmperorById(2);
                liuBei.age = 49;
                dao.updateEmperor(caoCao, liuBei);
            }).start();
            break;
        case R.id.btn_queryEmperorById:
            new Thread(() -> {
                Emperor sunQuan = dao.queryEmperorById(1);
                Log.e("main","====================sunQuan==" + JSON.toJSONString(sunQuan));
            }).start();
            break;
        case R.id.btn_queryEmperorsByAge:
            new Thread(() -> {
                List<Emperor> list = dao.queryEmperorsByAge(54);
                Log.e("main","====================list==" + JSON.toJSONString(list));
            }).start();
            break;
        case R.id.btn_queryEmperors:
            new Thread(() -> {
                List<Emperor> emperors = dao.queryEmperors();
                Log.e("main","====================emperors==" + JSON.toJSONString(emperors));
            }).start();
            break;
    }
}

Room同时会通过APT生成相应的方法

看完上面的操作，是不是比Sql命令简单很多，我大部分，操作对象就可以，这样更简单，方便使用

同时Room可以搭配mvvm使用，

3，GreenDao

GreenDao也是一个ORM框架，先于room出现，同样是用户更方便的使用

1，Gradle配置
implementation 'org.greenrobot:greendao:3.2.2’

greendao {
    schemaVersion 1//数据库版本号
    targetGenDir 'src/main/java'//设置DaoMaster、DaoSession、Dao目录
    daoPackage 'com.example.gen'
    //targetGenDirTest：设置生成单元测试目录
    //generateTests：设置自动生成单元测试用例
}

2，写对应的对象
@Entity
public class User {
    @Id(autoincrement = true)
    private Long id;
    
    @Unique
    private String userId;

    @Property
    private String userName;

    @Property
    private int age;
}

3，Application初始化GreenDao
private void initGreenDao() {
    DaoMaster.DevOpenHelper helper = new DaoMaster.DevOpenHelper(this, DB_NAME);
    SQLiteDatabase db = helper.getWritableDatabase();
    DaoMaster daoMaster = new DaoMaster(db);
    mDaoSession = daoMaster.newSession();
}

这个时候点击build项目，自动生成
DaoMaster    
DaoSession
UserDao  
这三个类


4，操作数据库
public void onViewClick(View view) {
        UserTest user = new UserTest();
        int id = view.getId();
        if(id == R.id.tv_save){
//            添加一条数据
            user.setAge(18);
            user.setId(System.currentTimeMillis());
            user.setLike("喜欢北京");
            user.setName("张胜男");
            mUserDao.insert(user);
        }else if(id == R.id.tv_delete){
            mUserDao.delete(user);
        }else if(id == R.id.tv_updata){
            user.setAge(19);
            mUserDao.delete(user);
        }else if(id == R.id.tv_select){
            List<UserTest> userList = mUserDao.loadAll();
//            User user = mUserDao.load(1L);
            Log.e("l;djgk","===========" + JSON.toJSONString(userList));
        }

}