Android学习-day3

Android开发数据库Sqlite

0、导图

1、创建数据库

创建步骤：

1. 写一个类继承SQLiteOpenHelper
2. 实现其方法，创建构造方法。
3. 创建子类对象，并调用getWritableDatabase()/getReadableDatabase()方法，就可以创建数据库。

继承SQLiteOpenHelper

public class DatabaseHelper extends SQLiteOpenHelper {


    /**
     *
     *  context   上下文
     *  name      数据库名称
     *  factory   游标工厂
     *  version   版本号
     */
    public DatabaseHelper(@Nullable Context context) {
        super(context, Constants.DATA_BASE, null, Constants.VERSION_CODE);
    }

    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {

    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {

    }
}

创建子类对象并调用对象

public class MainActivity extends AppCompatActivity {

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        //创建数据库
        DatabaseHelper databaseHelper = new DatabaseHelper(this);
        databaseHelper.getWritableDatabase();
    }
}

1.1、数据库升级

public class DatabaseHelper extends SQLiteOpenHelper {


    private static final String TAG = "DatabaseHelper";

    /**
     *
     *  context   上下文
     *  name      数据库名称
     *  factory   游标工厂
     *  version   版本号
     */
    public DatabaseHelper(@Nullable Context context) {
        super(context, Constants.DATA_BASE, null, Constants.VERSION_CODE);
    }

    /**
     *
     * @param sqLiteDatabase
     * 只有数据库第一次创建才会被创建
     */
    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        //创建时的回调，
        Log.d(TAG,"创建数据库");
        //创建字段
        String sql = "create table " + Constants.TABLE_NAME + "(_id integer, name varchar(50), age integer, salary integer)";
        sqLiteDatabase.execSQL(sql);
    }

    /**
     *
     * @param sqLiteDatabase
     * @param i 老版本
     * @param i1 新版本
     * 必须要对数据库版本，即：Constants.VERSION进行改变
     */
    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
        //升级数据库时的回调
        Log.d(TAG,"升级数据库");
        String sql = null;
        //对版本进行判断
        switch (i){
            case 1:
                //添加address和phone字段
                sql = "alter table " + Constants.TABLE_NAME + " add phone integer, address varchar";
                sqLiteDatabase.execSQL(sql);
                break;
            case 2:
                sql = "alter table " + Constants.TABLE_NAME + " add address varchar";
                sqLiteDatabase.execSQL(sql);
                //添加address字段
                break;
            case 3:
                break;
        }
    }
}

注：如果此时将版本调低之后，会导致应用程序挂掉，原因为：
版本只能升级不能降级。

1.2、编写DAO层

public class Dao {

    private static final String TAG = "DAO";
    private final DatabaseHelper databaseHelper;

    public Dao(Context context) {
        //创建数据库
        databaseHelper = new DatabaseHelper(context);
        databaseHelper.getWritableDatabase();
    }

    public void insert(){
        SQLiteDatabase database = databaseHelper.getWritableDatabase();
        String sql = "insert into " + Constants.TABLE_NAME + " (_id, name, age, salary, phone ,address) " +
                                                             "values (?, ?, ?, ?, ?)";
        database.execSQL(sql, new Object[]{1, "asd", 60, 1, 111, "USA"});
        database.close();
    }

    public void delete(){
        SQLiteDatabase database = databaseHelper.getWritableDatabase();
        String sql = "delete from " + Constants.TABLE_NAME + " where age = 60";
        database.execSQL(sql);
        database.close();
    }

    public void update(){
        SQLiteDatabase database = databaseHelper.getWritableDatabase();
        String sql = "update " + Constants.TABLE_NAME + " set salary = 2 where age = 60";
        database.execSQL(sql);
        database.close();
    }

    public void query(){
        SQLiteDatabase database = databaseHelper.getWritableDatabase();
        String sql = "select * from " + Constants.TABLE_NAME;
        //返回游标
        Cursor cursor = database.rawQuery(sql, null);
        //只要还有数据
        while (cursor.moveToNext()) {
            int index = cursor.getColumnIndex("name");
            String name = cursor.getString(index);
            Log.d(TAG,"name == " + name);
//            cursor.getString(1)
        }
        //关闭资源
        cursor.close();
        database.close();
    }
}

1.3、编写测试类

public class TestDatabase{
    @Test
    public void testCreate(){

    }
    @Test
    public void testInsert(){
        Context appContext = InstrumentationRegistry.getInstrumentation().getTargetContext();
        Dao dao = new Dao(appContext);
        dao.insert();
    }
    @Test
    public void testUpdate(){
        Context appContext = InstrumentationRegistry.getInstrumentation().getTargetContext();
        Dao dao = new Dao(appContext);
        dao.update();
    }
    @Test
    public void testDelete(){
        Context appContext = InstrumentationRegistry.getInstrumentation().getTargetContext();
        Dao dao = new Dao(appContext);
        dao.delete();
    }
    @Test
    public void testQuery(){
        Context appContext = InstrumentationRegistry.getInstrumentation().getTargetContext();
        Dao dao = new Dao(appContext);
        dao.query();
    }
}

1.4、使用Android的API进行增删改查

public class Dao {

    private static final String TAG = "DAO";
    private final DatabaseHelper databaseHelper;

    public Dao(Context context) {
        //创建数据库
        databaseHelper = new DatabaseHelper(context);
        databaseHelper.getWritableDatabase();
    }

    public void insert(){
        SQLiteDatabase database = databaseHelper.getWritableDatabase();
//        String sql = "insert into " + Constants.TABLE_NAME + " (_id, name, age, salary, phone ,address) " +
//                                                             "values (?, ?, ?, ?, ?, ?)";
//        database.execSQL(sql, new Object[]{1, "asd", 60, 1, 111, "USA"});
        ContentValues values = new ContentValues();
        //进行添加数据
        values.put("_id", 2);
        values.put("name","ll");
        values.put("salary", 1);
        values.put("phone", 1111);
        values.put("address", "moon");
        values.put("age", 40);
        database.insert(Constants.TABLE_NAME,null, values);
        database.close();
    }

    public void delete(){
        SQLiteDatabase database = databaseHelper.getWritableDatabase();
//        String sql = "delete from " + Constants.TABLE_NAME + " where age = 60";
//        database.execSQL(sql);
        int delete = database.delete(Constants.TABLE_NAME, null, null);
        Log.d(TAG, "delete == " + delete);
        database.close();
    }

    public void update(){
        SQLiteDatabase database = databaseHelper.getWritableDatabase();
//        String sql = "update " + Constants.TABLE_NAME + " set salary = 2 where age = 60";
//        database.execSQL(sql);
        ContentValues values = new ContentValues();
        values.put("phone",1123);
        //两个null就会更新数据库所有行
        database.update(Constants.TABLE_NAME,values, null, null);
        database.close();
    }

    public void query(){
        SQLiteDatabase database = databaseHelper.getWritableDatabase();
//        String sql = "select * from " + Constants.TABLE_NAME;
//        //返回游标
//        Cursor cursor = database.rawQuery(sql, null);
//        //只要还有数据
//        while (cursor.moveToNext()) {
//            int index = cursor.getColumnIndex("name");
//            String name = cursor.getString(index);
//            Log.d(TAG,"name == " + name);
            cursor.getString(1)
//        }
//        //关闭资源
//        cursor.close();
        Cursor cursor = database.query(Constants.TABLE_NAME, null, null, null, null, null, null, null);

        while(cursor.moveToNext()){
            int id = cursor.getInt(0);
            String name = cursor.getString(id);
            Log.d(TAG, "id == " + id + "name == " + name);
        }
        cursor.close();
//        database.close();
    }
}

1.5、数据库事务

1. 数据库事务就是为了发生异常可以进行回滚的。
   
   进行改变（开启事务）：
   

2. 批量插入数据的效率：
   普通插入耗时：15204ms
   
   开启事务插入耗时：218ms