说一下android的sqlite数据库BaseSQLiteOpenHelper

因为项目需要，不能用文件存储，需要用sqlite，原来不怎么用，今天弄了将近一天，而且主要是因为一个空格把时间耽误了

下面来分享下：

先上代码：BaseSQLiteOpenHelper

package com.cj.dreams.video.dbhelper;

import android.content.Context;
import android.database.DatabaseErrorHandler;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

/**
 * Created by fanyafeng on 2015/7/24/0024.
 */
public class BaseSQLiteOpenHelper extends SQLiteOpenHelper {

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

    @Override
    public void onCreate(SQLiteDatabase db) {

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }
}

博主有个习惯，就是一般可以抽象成一样的就写个base，然后个人感觉比较方便

package com.cj.dreams.video.dbhelper;

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

/**
 * Created by fanyafeng on 2015/7/24/0024.
 */
public class LaughSQLiteOpenHelper extends BaseSQLiteOpenHelper {
    private static final String DATABASENAME = "laughvideo.db";
    private static final int DATABASEVERSION = 1;
    //三个数据库
    private static final String table_record = "t_record";
    private static final String table_collect = "t_collect";
    private static final String table_good = "t_good";

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

    @Override
    public void onCreate(SQLiteDatabase db) {
        String create_record_sql = "CREATE TABLE " + table_record + "("
                + "_id        INTEGER      PRIMARY KEY,"
                + "v_id       VARCHAR(50)   NOT NULL,"
                + "v_image    VARCHAR(255)  NOT NULL,"
                + "v_title    VARCHAR(255)  NOT NULL,"
                + "v_url      VARCHAR(255)  NOT NULL"
                + ")";

        String create_collect_sql = "CREATE TABLE " + table_collect + "("
                + "_id        INTEGER      PRIMARY KEY,"
                + "v_id       VARCHAR(50)   NOT NULL,"
                + "v_image    VARCHAR(255)  NOT NULL,"
                + "v_title    VARCHAR(255)  NOT NULL,"
                + "v_url      VARCHAR(255)  NOT NULL"
                + ")";

        String create_good_sql = "CREATE TABLE " + table_good + "("
                + "_id        INTEGER      PRIMARY KEY,"
                + "v_id       VARCHAR(50)   NOT NULL,"
                + "v_ptimes   VARCHAR(50)   ,"
                + "v_ctimes   VARCHAR(50)   ,"
                + "v_gtimes   VARCHAR(50)   "
                + ")";

        db.execSQL(create_record_sql);
        db.execSQL(create_collect_sql);
        db.execSQL(create_good_sql);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        String create_record_sql = "DROP TABLE IF EXISTS" + table_record;
        String create_collect_sql = "DROP TABLE IF EXISTS" + table_collect;
        String create_good_sql = "DROP TABLE IF EXISTS" + table_good;
        db.execSQL(create_record_sql);
        db.execSQL(create_collect_sql);
        db.execSQL(create_good_sql);
        this.onCreate(db);
    }
}

package com.cj.dreams.video.dbhelper;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.cj.dreams.video.util.L;

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

/**
 * Created by fanyafeng on 2015/7/27/0027.
 */
public class RecordTableCourse {
    private static final String TABLENAME = "t_record";
    private SQLiteDatabase db = null;

    public RecordTableCourse(SQLiteDatabase db) {
        this.db = db;
    }

    public Boolean searchRecord(String v_id) {
        String sql = "SELECT v_id FROM " + TABLENAME + " WHERE v_id = ?";
        String searchField[] = new String[]{v_id};
        Cursor result = this.db.rawQuery(sql, searchField);
        result.moveToFirst();
        L.d("查询的结果", result.getCount());
        if (result.getCount() == 0) {
            return true;
        } else {
            return false;
        }
    }
}

这里细说一下，因为要涉及到插入，所以需要先判断有没有，然后再去插入，然后用rawquery查询的话如果没有的话返回的不是空也不是o，貌似十个地址，用tostring抓取返回值的话，然后根据这个count可以得到查询的结果

package com.cj.dreams.video.dboperate;

import android.content.ContentValues;
import android.database.sqlite.SQLiteDatabase;

/**
 * Created by fanyafeng on 2015/7/27/0027.
 */
public class CollectOperate {
    private static final String TABLENAME = "t_collect";
    private SQLiteDatabase db = null;

    public CollectOperate(SQLiteDatabase db) {
        this.db = db;
    }

    //插入数据库
    public void insert(String v_id, String v_image, String v_title, String v_ptimes, String v_ctimes, String v_gtimes, String v_url) {
        ContentValues contentValues = new ContentValues();
        contentValues.put("v_id", v_id);
        contentValues.put("v_image", v_image);
        contentValues.put("v_title", v_title);
        contentValues.put("v_ptimes", v_ptimes);
        contentValues.put("v_ctimes", v_ctimes);
        contentValues.put("v_gtimes", v_gtimes);
        contentValues.put("v_url", v_url);
        this.db.insert(TABLENAME, null, contentValues);
        this.db.close();
    }

    //更新数据库
    public void update(String v_id, String v_image, String v_title, String v_ptimes, String v_ctimes, String v_gtimes, String v_url) {
        ContentValues contentValues = new ContentValues();
        contentValues.put("v_id", v_id);
        contentValues.put("v_image", v_image);
        contentValues.put("v_title", v_title);
        contentValues.put("v_ptimes", v_ptimes);
        contentValues.put("v_ctimes", v_ctimes);
        contentValues.put("v_gtimes", v_gtimes);
        contentValues.put("v_url", v_url);
        String whereClause = "v_id=?";
        String whereArgs[] = new String[]{String.valueOf(v_id)};
        this.db.update(TABLENAME, contentValues, whereClause, whereArgs);
        this.db.close();
    }

    //删除数据库
    public void delete(String v_id) {
        String whereClause = "v_id=?";
        String whereArgs[] = new String[]{String.valueOf(v_id)};
        this.db.delete(TABLENAME, whereClause, whereArgs);
        this.db.close();
    }
}

package com.cj.dreams.video.dboperate;

import android.content.ContentValues;
import android.database.sqlite.SQLiteDatabase;

/**
 * Created by fanyafeng on 2015/7/27/0027.
 */
public class GoodOperate {
    private static final String TABLENAME = "t_good";
    private SQLiteDatabase db = null;

    public GoodOperate(SQLiteDatabase db) {
        this.db = db;
    }

    //插入数据库
    public void insert(String v_id, String v_ptimes, String v_ctimes, String v_gtimes) {
        ContentValues contentValues = new ContentValues();
        contentValues.put("v_id", v_id);
        contentValues.put("v_ptimes", v_ptimes);
        contentValues.put("v_ctimes", v_ctimes);
        contentValues.put("v_gtimes", v_gtimes);
        this.db.insert(TABLENAME, null, contentValues);
        this.db.close();
    }

    //更新数据库
    public void update(String v_id, String v_ptimes, String v_ctimes, String v_gtimes) {
        ContentValues contentValues = new ContentValues();
        contentValues.put("v_id", v_id);
        contentValues.put("v_ptimes", v_ptimes);
        contentValues.put("v_ctimes", v_ctimes);
        contentValues.put("v_gtimes", v_gtimes);
        String whereClause = "v_id=?";
        String whereArgs[] = new String[]{String.valueOf(v_id)};
        this.db.update(TABLENAME, contentValues, whereClause, whereArgs);
        this.db.close();
    }

    //删除数据库
    public void delete(String v_id) {
        String whereClause = "v_id=?";
        String whereArgs[] = new String[]{String.valueOf(v_id)};
        this.db.delete(TABLENAME, whereClause, whereArgs);
        this.db.close();
    }
}

package com.cj.dreams.video.dboperate;

import android.content.ContentValues;
import android.database.sqlite.SQLiteDatabase;

import com.cj.dreams.video.util.L;

/**
 * Created by fanyafeng on 2015/7/27/0027.
 */
public class RecordOperate {
    private static final String TABLENAME = "t_record";
    private SQLiteDatabase db = null;

    public RecordOperate(SQLiteDatabase db) {
        this.db = db;
    }

    //插入数据库
    public void insert(String v_id, String v_image, String v_title, String v_url) {
        ContentValues contentValues = new ContentValues();
        contentValues.put("v_id", v_id);
        contentValues.put("v_image", v_image);
        contentValues.put("v_title", v_title);
        contentValues.put("v_url", v_url);
        this.db.insert(TABLENAME, null, contentValues);
        L.d("数据库插入，执行到此");
        this.db.close();
    }

    //更新数据库
    public void update(String v_id, String v_image, String v_title, String v_url) {
        ContentValues contentValues = new ContentValues();
        contentValues.put("v_id", v_id);
        contentValues.put("v_image", v_image);
        contentValues.put("v_title", v_title);
        contentValues.put("v_url", v_url);
        String whereClause = "v_id=?";
        String whereArgs[] = new String[]{String.valueOf(v_id)};
        this.db.update(TABLENAME, contentValues, whereClause, whereArgs);
        this.db.close();
    }

    //删除数据库
    public void delete(String v_id) {
        String whereClause = "v_id=?";
        String whereArgs[] = new String[]{String.valueOf(v_id)};
        this.db.delete(TABLENAME, whereClause, whereArgs);
        this.db.close();
    }
}

对数据库的操作类，下面看一下目录结构先

来看一下操作类

package com.cj.dreams.video.fragment;

import android.content.Intent;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Bundle;
import android.os.Handler;
import android.os.Message;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.AdapterView;
import android.widget.ListView;

import com.cj.dreams.video.R;
import com.cj.dreams.video.activity.VideoViewPlayingActivity;
import com.cj.dreams.video.adapter.VideoListAdapter;
import com.cj.dreams.video.bean.VideoListBean;
import com.cj.dreams.video.dbhelper.LaughSQLiteOpenHelper;
import com.cj.dreams.video.dbhelper.RecordTableCourse;
import com.cj.dreams.video.dboperate.RecordOperate;
import com.cj.dreams.video.layout.PullToRefreshLayout;
import com.cj.dreams.video.util.L;
import com.cj.dreams.video.util.PostUtil;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

public class RankingFragment extends BaseFragment {
    private PullToRefreshLayout ptrl;
    private ListView listView;
    private VideoListAdapter videoListAdapter;
    private List<VideoListBean> videoListBeanList = new ArrayList<VideoListBean>();
    private List<VideoListBean> videoListBeanList_more = new ArrayList<VideoListBean>();
    private List<Map<String, Object>> videoInfoList = new ArrayList<Map<String, Object>>();
    private String id_info, url_info, title_info, image_info;
    //操作数据库
    public static LaughSQLiteOpenHelper laughSQLiteOpenHelper;
    public static RecordOperate recordOperate;
    private RecordTableCourse recordTableCourse;

    @Override
    public View onCreateView(LayoutInflater inflater, ViewGroup container, Bundle savedInstanceState) {
        View view = inflater.inflate(R.layout.fragment_ranking, container, false);
        return view;
    }

    @Override
    public void onActivityCreated(Bundle savedInstanceState) {
        super.onActivityCreated(savedInstanceState);
        this.laughSQLiteOpenHelper = new LaughSQLiteOpenHelper(getActivity());
        laughSQLiteOpenHelper.getWritableDatabase();
        initView();
        Thread loadThread = new Thread(new LoadThread());
        loadThread.start();
        initData();
    }


    private void initView() {
        ptrl = ((PullToRefreshLayout) getActivity().findViewById(R.id.refresh_ranking_view));
        ptrl.setOnRefreshListener(new MyListener());
        listView = (ListView) getActivity().findViewById(R.id.ranking_listview);

        videoListAdapter = new VideoListAdapter(getActivity(), videoListBeanList);
        listView.setAdapter(videoListAdapter);
        listView.setFocusable(true);
        listView.setOnItemClickListener(new IndexOnItemClickListener());
    }

    private class IndexOnItemClickListener implements AdapterView.OnItemClickListener {
        @Override
        public void onItemClick(AdapterView<?> parent, View view, int position, long id) {
            Intent intent = new Intent(getActivity(), VideoViewPlayingActivity.class);
            for (int i = 0; i <= position; i++) {
                if (position == i) {
                    Map map = (Map) videoInfoList.get(i);
                    url_info = (String) map.get("url_info");
                    intent.putExtra("url_info", url_info);
                    id_info = (String) map.get("id_info");
                    intent.putExtra("id_info", id_info);
                    title_info = (String) map.get("title_info");
                    intent.putExtra("title_info", title_info);
                    image_info = (String) map.get("image_info");
                    intent.putExtra("image_info", image_info);
                }
            }
            if (new RecordTableCourse(laughSQLiteOpenHelper.getReadableDatabase()).searchRecord(id_info)) {
                recordOperate = new RecordOperate(laughSQLiteOpenHelper.getReadableDatabase());
                recordOperate.insert(id_info, image_info, title_info, url_info);
            }
            Thread postThread = new Thread(new PostThread(id_info, "play"));
            postThread.start();
            startActivity(intent);
        }
    }

    class LoadThread implements Runnable {
        @Override
        public void run() {
            loadData();
        }
    }

    private void loadData() {
        try {
            String backMsg = PostUtil.postData(BaseUrl + GetTopVideo, null);
            L.d("播放排名得到的返回参数", backMsg.toString());
            try {
                JSONObject jsonObject = new JSONObject(backMsg);
                JSONArray videoArray = jsonObject.getJSONArray("video");
                videoListBeanList_more.clear();
                for (int i = 0; i < videoArray.length(); i++) {
                    VideoListBean videoListBean = new VideoListBean(null, null, null, null, null, null);
                    JSONObject object = videoArray.getJSONObject(i);
                    videoListBean.setVideoId(object.getString("id"));
                    videoListBean.setVideoTitle(object.getString("title"));
                    videoListBean.setVideoImage(BaseUrl + object.getString("image"));
                    videoListBean.setVideoCollectTimes(Integer.parseInt(object.getString("collect_num")) + "");
                    videoListBean.setVideoPlayTimes(Integer.parseInt(object.getString("play_number")) + "");
                    videoListBean.setVideoGoodTimes(Integer.parseInt(object.getString("praise_num")) + "");
                    videoListBeanList.add(videoListBean);

                    Map<String, Object> idmap = new HashMap<String, Object>();
                    idmap.put("url_info", object.getString("url"));
                    idmap.put("id_info", object.getString("id"));
                    idmap.put("title_info", object.getString("title"));
                    idmap.put("image_info", BaseUrl + object.getString("image"));
                    videoInfoList.add(idmap);

                }
            } catch (JSONException e) {
                e.printStackTrace();
            }
        } catch (IOException e) {
            e.printStackTrace();
        }

        Message message = Message.obtain();
        message.what = 0;
        handler.sendMessage(message);
    }

    Handler handler = new Handler() {
        @Override
        public void handleMessage(Message msg) {
            super.handleMessage(msg);
            switch (msg.what) {
                case 0:
                    videoListAdapter.update();
                    break;
            }
        }
    };

    private void initData() {

    }

    class PostThread implements Runnable {
        private String id;
        private String type;

        PostThread(String id, String type) {
            this.id = id;
            this.type = type;
        }

        @Override
        public void run() {
            postData(id, type);
        }
    }

    private void postData(String videoId, String buttonType) {
        Map<String, String> map = new LinkedHashMap<>();
        map.put("videoid", videoId);
        map.put("type", buttonType);
        try {
            String backMsg = PostUtil.postData(BaseUrl + PostVideoInfo, map);
            L.d("运行到此，播放次数加1");
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    private class MyListener implements PullToRefreshLayout.OnRefreshListener {

        @Override
        public void onRefresh(final PullToRefreshLayout pullToRefreshLayout) {
            new Handler() {
                @Override
                public void handleMessage(Message msg) {

                    pullToRefreshLayout.refreshFinish(PullToRefreshLayout.SUCCEED);
                }
            }.sendEmptyMessageDelayed(0, 500);
        }

        @Override
        public void onLoadMore(final PullToRefreshLayout pullToRefreshLayout) {
            new Handler() {
                @Override
                public void handleMessage(Message msg) {

                    pullToRefreshLayout.loadmoreFinish(PullToRefreshLayout.SUCCEED);
                }
            }.sendEmptyMessageDelayed(0, 500);
        }

    }

    @Override
    public void onResume() {
        super.onResume();
    }

    @Override
    public void onPause() {
        super.onPause();
    }
}

看一下数据结构

下一篇补充细节