android ---SQLite

创建草稿箱需要用到SQLite 手机本地数据库
1.创建数据库中包括的数据的类

public class VideoBean {
    public static final String ID = "_id";
    public static final String PHONE = "phone";
    public static final String DATE = "Date";
    public static final String VIDEOPATH="VideoPath";
    public static final String IMGPATH="ImgPath";
    public static final String DATE1 = "Date1";

    private int id;
    private String phone;
    private String Date;
    //视频路径
    private String VideoPath;
    //封面路径
    private String ImgPath;
    private String Date1;
    public VideoBean(){
    }
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getPhone() {
        return phone;
    }
    public void setPhone(String phone) {
        this.phone = phone;
    }
    public String getDate() {
        return Date;
    }
    public void setDate(String date) {
        Date = date;
    }
    public String getVideoPath() {
        return VideoPath;
    }
    public void setVideoPath(String videoPath) {
        VideoPath = videoPath;
    }
    public String getImgPath() {
        return ImgPath;
    }
    public void setImgPath(String ImgPath) {
        this.ImgPath = ImgPath;
    }
     public String getDate1() {
        return Date1;
    }
    public void setDate1(String date1) {
        Date1 = date1;
    }
}

2.创建数据库的操作类

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import java.util.ArrayList;
import java.util.List;

/**
 * Created by Administrator on 2016/10/9.
 */

public class DBHelper extends SQLiteOpenHelper {
    //数据库名称
    public  static String DATABASE_NAME="CopyrightVideo";
    //数据库版本
    private static int DATABASE_VERSION = 3;
    //数据库创建时会调用 onCreate
    private static String CREATE_TABLE_VIDEO=
            "CREATE TABLE IF NOT EXISTS " +
                    DATABASE_NAME + "(" +
                    VideoBean.ID + " integer  primary key autoincrement," +
                    VideoBean.PHONE+" varchar(200)," +
                    VideoBean.DATE+" varchar(50)," +
                    VideoBean.VIDEOPATH + " varchar(200)," +
                    VideoBean.IMGPATH + " varchar(200)," +
                    VideoBean.DATE1+" varchar(50)" +
                    ")";



    public DBHelper(Context context) {
        //第三个参数CursorFactory指定在执行查询时获得一个游标实例的工厂类,设置为null,代表使用系统默认的工厂类
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }
    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_TABLE_VIDEO);
    }


    //重新建表
    public void resetTable(){
        getWritableDatabase().execSQL("DROP TABLE IF EXISTS "+DATABASE_NAME);
            getWritableDatabase().execSQL(CREATE_TABLE_VIDEO);
    }


    //如果数据库版本不同，将会调用 onUpgrade
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + DATABASE_NAME);
        onCreate(db);
        //db.execSQL("ALTER TABLE person ADD COLUMN sex char(2)");
        //db.execSQL("ALTER TABLE video ADD VideoBean.ImgPath TEXT"); //往表中增加一列
    }
    //更新一行
    public int updateVideo(VideoBean video,int id){
        SQLiteDatabase db=this.getWritableDatabase();
        ContentValues values=new ContentValues();
        values.put(VideoBean.PHONE,video.getPhone());
        values.put(VideoBean.DATE,video.getDate());
        values.put(VideoBean.VIDEOPATH,video.getVideoPath());
        values.put(VideoBean.IMGPATH,video.getImgPath());
        values.put(VideoBean.DATE1,video.getDate1());
        return db.update(DATABASE_NAME,values,VideoBean.ID+"=?", new String[]{String.valueOf(id)});
    }
    //增加一行
    public void addVideo(VideoBean video){
        SQLiteDatabase db=this.getWritableDatabase();
        //使用ContentValues添加数据
        ContentValues values=new ContentValues();
        values.put(VideoBean.PHONE,video.getPhone());
        values.put(VideoBean.DATE,video.getDate());
        values.put(VideoBean.VIDEOPATH,video.getVideoPath());
        values.put(VideoBean.IMGPATH,video.getImgPath());
        values.put(VideoBean.DATE1,video.getDate1());
        db.insert(DATABASE_NAME,null, values);
        db.close();

    }
    //删除单行
    public void deleteVideo(VideoBean video){
        SQLiteDatabase db=this.getWritableDatabase();
        db.delete(DATABASE_NAME,VideoBean.ID+"="+video.getId(),null);
        db.close();
    }
    //删除全部
    public void deleteVideoALL(String phone){
        SQLiteDatabase db=this.getWritableDatabase();
        db.delete(DATABASE_NAME,VideoBean.PHONE+" = '"+phone+"'",null);
        db.close();
    }
    //查询全部
    public List<VideoBean> queryVideo(String phone){
        List<VideoBean> videoBeanList=new ArrayList<VideoBean>();
        String selectQuery="SELECT * FROM "+DATABASE_NAME +"  WHERE "
                +VideoBean.PHONE+" = '"+phone+"'"+" ORDER BY "+VideoBean.DATE+" ASC";
        SQLiteDatabase db=this.getReadableDatabase();
        Cursor cursor=db.rawQuery(selectQuery,null);
        if(cursor.moveToFirst()){
            do{
                VideoBean videoBean1=new VideoBean();
                videoBean1.setId(Integer.parseInt(cursor.getString(0)));
                videoBean1.setPhone(cursor.getString(1));
                videoBean1.setDate(cursor.getString(2));
                videoBean1.setVideoPath(cursor.getString(3));
                videoBean1.setImgPath(cursor.getString(4));
                videoBean1.setDate1(cursor.getString(5));
                videoBeanList.add(videoBean1);
            }while(cursor.moveToNext());
        }
        return videoBeanList;
    }
    //查询某一id 的数据
    public VideoBean queryVideoRow(String phone,int id){
        VideoBean videoBean1=new VideoBean();
        String selectQuery="SELECT * FROM "+DATABASE_NAME+" WHERE "+VideoBean.ID+"="+id+" and "
                +VideoBean.PHONE+" = '"+phone+"'";
        SQLiteDatabase db=this.getReadableDatabase();
        Cursor cursor=db.rawQuery(selectQuery,null);
        if (cursor.moveToFirst())
        {
            videoBean1.setId(Integer.parseInt(cursor.getString(0)));
            videoBean1.setPhone(cursor.getString(1));
            videoBean1.setDate(cursor.getString(2));
            videoBean1.setVideoPath(cursor.getString(3));
            videoBean1.setImgPath(cursor.getString(4));
            videoBean1.setDate1(cursor.getString(5));
        }
        cursor.close();
        return videoBean1;
    }
    //查询时间（去重复）
    public ArrayList<String> queryVideoTime(String phone){
        List<VideoBean> videoBeanList=new ArrayList<VideoBean>();
        ArrayList<String> times=new ArrayList<>();
        String selectQuery="SELECT DISTINCT "+VideoBean.DATE1+" FROM "+DATABASE_NAME
                +"  WHERE "+VideoBean.PHONE+" = '"+phone+"'"+" ORDER BY "+VideoBean.DATE1+" DESC";
        SQLiteDatabase db=this.getReadableDatabase();
        Cursor cursor=db.rawQuery(selectQuery,null);
        if(cursor.moveToFirst()){
            do{
                times.add(cursor.getString(0));
            }while(cursor.moveToNext());
        }

        return times;
    }
    //查询某一时间 的所有数据
    public ArrayList<VideoBean> queryVideoGroup(String phone,String date){
        ArrayList<VideoBean> videoBeanList=new ArrayList<VideoBean>();
        String selectQuery="SELECT * FROM "+DATABASE_NAME+" WHERE "+VideoBean.DATE1+"= '"+date+"' and "
                +VideoBean.PHONE+" = '"+phone+"'";
        SQLiteDatabase db=this.getReadableDatabase();
        Cursor cursor=db.rawQuery(selectQuery,null);
        if (cursor.moveToFirst())
        {
            do{ VideoBean videoBean1=new VideoBean();
            videoBean1.setId(Integer.parseInt(cursor.getString(0)));
            videoBean1.setPhone(cursor.getString(1));
            videoBean1.setDate(cursor.getString(2));
            videoBean1.setVideoPath(cursor.getString(3));
            videoBean1.setImgPath(cursor.getString(4));
            videoBean1.setDate1(cursor.getString(5));
            videoBeanList.add(videoBean1);
            }while(cursor.moveToNext());
        }
        cursor.close();
        return videoBeanList;
    }

}

3.在Activity 中使用：

private DBHelper dbHelper;

在onCreate 中：查询

 dbHelper=new DBHelper(this);
 videobean1 = dbHelper.queryVideoRow(mobilePhone,videoID);

删除某一行

dbHelper.deleteVideo(videobean1);

添加：

dbHelper.addVideo(videoBea`n1);

插入

dbHelper.updateVideo(videoBean1, videoID);

4.在OnDestory()方法中，或者 数据库使用完添加：

dbHelper.close();