# 数据库的创建步骤

写这篇文章的主要目的是为了方便以后查找

1.SQLiteOpenHelper

//数据库名称
    private static final String DATABASENAME = "cainiaomusic.db";
    //数据库版本
    private static final int DATABASEVERSION = 1;

    public DBHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
    }

    public DBHelper(Context context) {
        super(context, DATABASENAME, null, DATABASEVERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        //用户表
//        db.execSQL(AccountDao.createTable());
//        db.execSQL(AccountDao.createIndex());
        //歌曲表
        db.execSQL(SongDao.createTable());
        db.execSQL(SongDao.createIndex());
        //收藏夹表
        db.execSQL(CollectionDao.createTable());
        //收藏夹关联表
        db.execSQL(CollectionShipDao.createTable());
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        //db.execSQL("ALTER TABLE note ADD COLUMN marktes integer");//增减一项 保存用户数据
        //onCreate(db);
    }

2.SQLiteDatabase

执行CRUD的操作

/**
 * @desciption: 基础dao层
 */
public abstract class BaseDao {

    protected SQLiteDatabase db;
    protected DBHelper dh;

    public BaseDao() {
        dh = new DBHelper(MyApplication.getInstance().getApplicationContext());
        db = dh.getWritableDatabase();
    }

    public void close() {
        db.close();
        dh.close();
    }

    public Cursor query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy){
        Cursor c = db.query(table, columns, selection, selectionArgs, groupBy, having, orderBy);
        return c;
    }

    public long insert (String table, String nullColumnHack, ContentValues values){
        return db.insert(table,nullColumnHack,values);
    }


    public int delete(String table, String whereClause, String[] whereArgs){
        return db.delete(table,whereClause,whereArgs);
    }

    public int update(String table, ContentValues values, String whereClause, String[] whereArgs){
        return db.update(table,values,whereClause,whereArgs);
    }

    /**
     * 数据替换，原理是先删除存在的整行数据后在重新插入
     * 需要先指定索引才能使用
     * @param table
     * @param nullColumnHack
     * @param initialValues
     * @return
     */
    public long replace(String table, String nullColumnHack, ContentValues initialValues){
        return db.replace(table,nullColumnHack,initialValues);
    }
}

/**
 * @desciption: 收藏夹数据表
 */
public class CollectionDao extends BaseDao {

    private static final String TABLE = "COLLECTION";

    private final static String COLUMN_ID = "_id";
    private final static String COLUMN_TITLE = "title";
    private final static String COLUMN_COVER_URL = "cover_url";
    private final static String COLUMN_DESCRIPTION = "description";
    private final static String COLUMN_COUNT = "count";

    /**
     * 建表sql
     *
     * @return sql
     */
    public static String createTable() {
        StringBuilder sb = new StringBuilder();
        sb.append("CREATE TABLE IF NOT EXISTS " + TABLE + "(");
        sb.append(COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,");
        sb.append(COLUMN_TITLE + " varchar(100),");
        sb.append(COLUMN_COVER_URL + " varchar(200),");
        sb.append(COLUMN_DESCRIPTION + " TEXT,");
        sb.append(COLUMN_COUNT + " INTEGER");
        sb.append(");");
        return sb.toString();
    }

    /**
     * 获取表上的所有收藏夹
     *
     * @return collectionList
     */
    public List<CollectionBean> queryAll() {
        List<CollectionBean> collectionList = new ArrayList<>();
        Cursor cursor = query(TABLE, null, null, null, null, null, null);
        while (cursor.moveToNext()) {
            collectionList.add(getCollection(cursor));
        }
        cursor.close();
        return collectionList;
    }

    /**
     * 获取表上的收藏夹
     *
     * @return 收藏夹
     */
    public CollectionBean query(int id) {
        CollectionBean bean = null;
        String selection = COLUMN_ID + "=?";
        String[] selectionArgs = new String[]{String.valueOf(id)};
        List<CollectionBean> collectionList = new ArrayList<>();
        Cursor cursor = query(TABLE, null, selection, selectionArgs, null, null, null);
        if (cursor.moveToNext()) {
            bean = getCollection(cursor);
        }
        cursor.close();
        return bean;
    }


    /**
     * 插入一条收藏夹记录
     *
     * @param collectionBean
     */
    public long insertCollection(CollectionBean collectionBean) {
        return insert(TABLE, null, getCollectionContent(collectionBean));
    }

    /**
     * 更新一条收藏夹信息
     *
     * @param collectionBean
     */
    public int updateCollection(CollectionBean collectionBean) {
        String whereClause = COLUMN_ID + "=?";
        String[] whereArgs = new String[]{collectionBean.getId() + ""};
        return update(TABLE, getCollectionContent(collectionBean), whereClause, whereArgs);
    }

    /**
     * 删除一条收藏夹信息
     *
     * @param collectionBean
     */
    public void deleteCollection(CollectionBean collectionBean) {
        String whereClause = COLUMN_ID + "=?";
        String[] whereArgs = new String[]{collectionBean.getId() + ""};
        delete(TABLE, whereClause, whereArgs);
    }

    private CollectionBean getCollection(Cursor cursor) {
        int id = cursor.getInt(cursor.getColumnIndex(COLUMN_ID));
        String title = cursor.getString(cursor.getColumnIndex(COLUMN_TITLE));
        String description = cursor.getString(cursor.getColumnIndex(COLUMN_DESCRIPTION));
        String coverUrl = cursor.getString(cursor.getColumnIndex(COLUMN_COVER_URL));
        int count = cursor.getInt(cursor.getColumnIndex(COLUMN_COUNT));
        CollectionBean collection = new CollectionBean(id, title, coverUrl, count, description);
        return collection;
    }

    public ContentValues getCollectionContent(CollectionBean collection) {
        ContentValues values = new ContentValues();
        values.put(COLUMN_TITLE, collection.getTitle());
        values.put(COLUMN_COVER_URL, collection.getCoverUrl());
        values.put(COLUMN_DESCRIPTION, collection.getDescription());
        values.put(COLUMN_COUNT, collection.getCount());
        return values;
    }
}