SQL 本地数据库

 

先写一个数据库帮助器：

public class MyDBHelper extends SQLiteOpenHelper {
    private static final String DB_NAME = "M-Evolution.db";  //数据库名称
    private static final int DB_VERSION = 1;  //数据库的版本号
    private static MyDBHelper myDBHelper = null;  //数据库帮助器的实例
    private SQLiteDatabase myDB = null;  //数据库的实例
    public static final String TABLE_NAME_PRODUCTION = "Production";  //表的名称

    public MyDBHelper(Context context) {
        super(context, DB_NAME, null, DB_VERSION);  //根据版本号决定是否执行onUpgrade函数
    }

    public MyDBHelper(Context context, int version) {
        super(context, DB_NAME, null, version);
    }

    //利用单例模式获取数据库帮助器的唯一实例
    public static MyDBHelper getInstance(Context context, int version){
        if(version > 0 && myDBHelper == null){
            myDBHelper = new MyDBHelper(context,version);
        }else if(myDBHelper == null){
            myDBHelper = new MyDBHelper(context);
        }
        return myDBHelper;
    }

    //打开数据库的读连接
    public SQLiteDatabase openReadLink(){
        if(myDB == null || !myDB.isOpen()){
            myDB = myDBHelper.getReadableDatabase();
        }
        return myDB;
    }

    //打开数据库的写连接
    public SQLiteDatabase openWriteLink(){
        if (myDB == null || !myDB.isOpen()){
            myDB = myDBHelper.getWritableDatabase();
        }
        return myDB;
    }

    public void closeLink(){
        if(myDB != null && myDB.isOpen()){
            myDB.close();
            myDB = null;
        }
    }

    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        String drop_sql = "DROP TABLE IF EXISTS "+TABLE_NAME_PRODUCTION+";";
        sqLiteDatabase.execSQL(drop_sql);
        String create_sql = "CREATE TABLE IF NOT EXISTS "+TABLE_NAME_PRODUCTION+"(" +
                "_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," +
                "name VARCHAR(10)," +
                "weight FLOAT," +
                "height LONG," +
                "word TEXT);";
        sqLiteDatabase.execSQL(create_sql);
    }

    //修改数据库，执行表结构变更语句
    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int oldVersion, int newVersion) {

    }
}

注意：

1、onCreate在安装完APP后只会执行一次（删除后再次安装才会执行），增加DB_VERSION的值会执行onUpgrad函数。

2、onCreate函数中不要将db给close掉，不然的话，构建函数中获取数据库会报错。

3、如果是多个SQLiteOpenHelper类，那么只会执行其中一个类的onCreate；所以，若有多个表需要建立，则只编写一个SQLiteOpenHelper类。

接着另写一个类对数据库帮助器进行操作，完成“增删查改”，这里只展示“增删查改”的部分代码：

public void insert(int id, String name){
        SQLiteDatabase db = getWritableDatabase();
        String insert_sql = "INSERT INTO "+TABLE_NAME+"(id,name) values("+String.valueOf(id)+",'"+name+"');";
//      这里注意name两边要有单引号
        db.execSQL(insert_sql);
        db.close();
        //第二种方法：
//        SQLiteDatabase db = getWritableDatabase();
//        ContentValues values = new ContentValues();
//        values.put("id", id);
//        values.put("name", name);
//        db.insert(TABLE_NAME, null, values);
//        db.close();
    }

    public void update(int id, String name) {
        SQLiteDatabase db = getWritableDatabase();
        String update_sql = "UPDATE "+TABLE_NAME+" SET name = '"+name+"' WHERE id = "+String.valueOf(id);
        db.execSQL(update_sql);
        db.close();
        //第二种方法：
//        SQLiteDatabase db = getWritableDatabase();
//        String whereClause = "id = ?"; // 主键列名 = ?
//        String[] whereArgs = { String.valueOf(id) }; // 主键的值
//        ContentValues values = new ContentValues();
//        values.put("name", name);
//        db.update(TABLE_NAME, values, whereClause, whereArgs);
//        db.close();
    }

    public void delete(int id) {
        SQLiteDatabase db = getWritableDatabase();
        String delete_sql = "DELETE FROM "+TABLE_NAME+" WHERE id = "+String.valueOf(id);
        db.execSQL(delete_sql);
        db.close();
        //第二种方法：
//        SQLiteDatabase db = getWritableDatabase();
//        String whereClause = "id = ?"; // 主键列名 = ?
//        String[] whereArgs = { String.valueOf(id) }; // 主键的值
//        db.delete(TABLE_NAME, whereClause, whereArgs);
//        db.close();
    }

    public List<String[]> getAllItem(){
        SQLiteDatabase db = getReadableDatabase();
        List<String[]> list = new ArrayList<>();
        String query_sql = "SELECT * FROM "+TABLE_NAME+";";
        Cursor cursor = db.rawQuery(query_sql,null);
        if (cursor.moveToFirst()){
            do {
                String[] item = {cursor.getString(0), cursor.getString(1)};
                list.add(item);
            }while(cursor.moveToNext());
        }
        return list;
    }

    public void clear(){
        db = getWritableDatabase();
        String delete_sql = "DROP TABLE IF EXISTS "+TABLE_NAME;
        db.execSQL(delete_sql);
        db.close();
    }

public class MyProductionDB{
    public static final String TABLE_NAME_PRODUCTION = "Production";  //表的名称
    private static final String KEY_PRODUCTION_ID = "production_id";  //主键：歌曲id
    private static final String KEY_PRODUCTION_NAME = "production_name"; //歌曲名字
    private static final String KEY_COMPOSER_ID = "composer_id"; //作者id
    private static final String KEY_COMPOSER_NICKNAME = "composer_nickname";  //作者昵称
    private static final String KEY_PRODUCTION_TIME = "production_time";  //歌曲创作时间：20190422134450
    private static final String KEY_PRODUCTION_TYPE = "production_type";  //歌曲创作的方式：0标签，1哼歌，2热歌
    private static final String KEY_PRODUCTION_INFO = "production_info";  //标签或者热歌名字等信息
    private static final String TAG = "MyProductionDB";

    private MyDBHelper myDBHelper;

    public MyProductionDB(Context context) {
        myDBHelper = MyDBHelper.getInstance(context);
    }

    public void insert(MyProduction myProduction){
        SQLiteDatabase db = myDBHelper.openReadLink();
        String sql_query = "SELECT * FROM "+TABLE_NAME_PRODUCTION+" WHERE "+KEY_PRODUCTION_ID+"='"+myProduction.getProduction_id()+"';";
        Cursor cursor = db.rawQuery(sql_query, null);
        if(cursor.moveToFirst()){
            cursor.close();
            myDBHelper.closeLink();
            update(myProduction);
        }
        else{
            myDBHelper.closeLink();
            db = myDBHelper.openWriteLink();
            String sql_insert = String.format("INSERT INTO %s (%s,%s,%s,%s,%s,%s) VALUES('%s','%s','%s','%s',%d,'%s');",
                    TABLE_NAME_PRODUCTION,KEY_PRODUCTION_ID,KEY_PRODUCTION_NAME,KEY_COMPOSER_ID,KEY_COMPOSER_NICKNAME,KEY_PRODUCTION_TIME,KEY_PRODUCTION_TYPE,KEY_PRODUCTION_INFO,
                    myProduction.getProduction_id(),myProduction.getProduction_name(),myProduction.getComposer_id(), TimeUtils.dateToStr(myProduction.getProduction_time(),"yyyyMMddhhmmss"),myProduction.getProduction_type(),myProduction.getProduction_info());
            db.execSQL(sql_insert);
            myDBHelper.closeLink();
        }
    }

    public void update(MyProduction myProduction){
        SQLiteDatabase db = myDBHelper.openWriteLink();
        String sql_update = String.format("UPDATE %s SET %s='%s',%s='%s',%s='%s',%s=%d,%s='%s' WHERE %s='%s';", TABLE_NAME_PRODUCTION,
                KEY_PRODUCTION_NAME,myProduction.getProduction_name(),
                KEY_COMPOSER_ID,myProduction.getComposer_id(),
                KEY_COMPOSER_NICKNAME,myProduction.getComposer_nickname(),
                KEY_PRODUCTION_TIME,TimeUtils.dateToStr(myProduction.getProduction_time(),"yyyyMMddhhmmss"),
                KEY_PRODUCTION_TYPE,myProduction.getProduction_type(),
                KEY_PRODUCTION_INFO,myProduction.getProduction_info(),
                KEY_PRODUCTION_ID,myProduction.getProduction_id());
        db.execSQL(sql_update);
        myDBHelper.closeLink();
    }

    public void delete(String production_id){
        SQLiteDatabase db = myDBHelper.openWriteLink();
        String sql_delete = "DELETE FROM "+TABLE_NAME_PRODUCTION+" WHERE "+KEY_PRODUCTION_ID+"='"+production_id+"';";
        db.execSQL(sql_delete);
        myDBHelper.closeLink();
    }

    public void clear(){
        SQLiteDatabase db = myDBHelper.openWriteLink();
        String delete_sql = "DELETE FROM "+TABLE_NAME_PRODUCTION+";";
        db.execSQL(delete_sql);
        myDBHelper.closeLink();
    }
}