Android Sqlite的使用（多线程优化）

1 新建一个数据库打开类继承自SQLiteOpenHelper。

package com.db;

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

/**
 * Created by c9736 on 2016/6/26.
 */
public class DBHelper extends SQLiteOpenHelper{
    private static final String DB_NAME="download.db";
    private static final int VERSION=1;
    private static final String SQI_CREATE="create table thread_info(_id integer primary key autoincrement,thread_id integer,url text,start " +
            "integer,end integer,finished integer);";
    private static final String SQL_DROP="drop table if exists thread_info";
    public DBHelper(Context context) {
        super(context, DB_NAME, null, VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(SQI_CREATE);
    }

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

        db.execSQL(SQL_DROP);
        db.execSQL(SQI_CREATE);
    }
}
/**
     * 获得类的对象
     */
    public static DBHelper getInstance(Context context){
        if(dbHelper==null){
            dbHelper=new DBHelper(context);    //永远只会被实例化一次
        }
        return dbHelper;
    }

2 创建数据访问接口，因为用于存储线程信息，所以命名为ThreadDao。

package com.db;
import com.entities.ThreadInfo;

import java.util.List;

/**
 * 数据访问接口
 * Created by c9736 on 2016/6/26.
 */
public interface ThreadDao {
    /**
    插入线程信息
     */
    public void insertThread(ThreadInfo threadInfo);
    /**
    删除线程
     */
    public void deleteThread(String url,int Thread_id);

    /**
     * 更新线程
     */

    public void updateThread(String url,int thread_id,int finished);
    /**
    查询文件线程信息
     */
    public List<ThreadInfo> getThreads(String url);

    /**
     *线程是否存在
     */
    public  boolean isExists(String url ,int thread_id);

}

3 在ThreadDaoImp1类中实现ThreadDao接口，调用类中方法用于操作数据库的增删改查。

package com.db;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.entities.ThreadInfo;

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

/**
 * 数据访问接口的实现
 * Created by c9736 on 2016/6/26.
 */
public class ThreadDaoImp1 implements ThreadDao {
    private DBHelper dbHelper=null;
    public ThreadDaoImp1(Context context){
        dbHelper=DBHelper.getInstance(context);
    }
    @Override
    public synchronized  void  insertThread(ThreadInfo threadInfo) {//多线程优化，任何时刻不能有多个线程进行插入操作，防止死锁发送

        SQLiteDatabase db=dbHelper.getWritableDatabase();//获取可写的数据库
        db.execSQL("insert into thread_info(thread_id,url,start,end,finished)values(?,?,?,?,?)",new Object[]{threadInfo.getId()
                ,threadInfo.getUrl(), threadInfo.getStart()
            ,threadInfo.getEnd(),threadInfo.getFinished()});
        db.close();

    }

    @Override
    public synchronized void deleteThread(String url) {

        SQLiteDatabase db=dbHelper.getWritableDatabase();//获取可写的数据库
        db.execSQL("delete from thread_info where url=?",new Object[]{url});
        db.close();
    }

    @Override
    public synchronized void updateThread(String url, int thread_id, int finished) {
        SQLiteDatabase db=dbHelper.getWritableDatabase();//获取可写的数据库
        db.execSQL("update  thread_info set finished=? where url=? and thread_id=?",new Object[]{finished,url,thread_id});
        db.close();
    }

    @Override
    public List<ThreadInfo> getThreads(String url) {
        SQLiteDatabase db=dbHelper.getReadableDatabase();
        List<ThreadInfo> threadInfoList=new ArrayList<ThreadInfo>();
        Cursor cursor=db.rawQuery("select * from thread_info where url=?",new String[]{url});
        while (cursor.moveToNext()){
            ThreadInfo threadInfo=new ThreadInfo();
            threadInfo.setId(cursor.getInt(cursor.getColumnIndex("thread_id")));
            threadInfo.setUrl(cursor.getString(cursor.getColumnIndex("url")));
            threadInfo.setStart(cursor.getInt(cursor.getColumnIndex("start")));
            threadInfo.setEnd(cursor.getInt(cursor.getColumnIndex("end")));
            threadInfo.setFinished(cursor.getInt(cursor.getColumnIndex("finished")));

            Log.i("test", "thread_info_Query" + threadInfo.toString());
            threadInfoList.add(threadInfo);
        }
        db.close();


        return threadInfoList;
    }

    @Override
    public boolean isExists(String url,int thread_id) {
        SQLiteDatabase db=dbHelper.getReadableDatabase();

        Cursor cursor=db.rawQuery("select * from thread_info where url=? and thread_id=?",new String[]{url,thread_id+""});
        boolean exists=cursor.moveToNext();
        db.close();

        return false;
    }
}

package com.entities;

/**
 * 线程信息
 * Created by c9736 on 2016/6/25.
 */
public class ThreadInfo {
    private int id;
    private String url;
    private int start;
    private int end;
    private int finished;

    public ThreadInfo(int id, int finished, String url, int start, int end) {
        this.id = id;
        this.finished = finished;
        this.url = url;
        this.start = start;
        this.end = end;
    }

    @Override
    public String toString() {
        return "ThreadInfo{" +
                "id=" + id +
                ", url='" + url + '\'' +
                ", start=" + start +
                ", end=" + end +
                ", finished=" + finished +
                '}';
    }

    public ThreadInfo() {
        super();
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getUrl() {
        return url;
    }

    public void setUrl(String url) {
        this.url = url;
    }

    public int getStart() {
        return start;
    }

    public void setStart(int start) {
        this.start = start;
    }

    public int getEnd() {
        return end;
    }

    public void setEnd(int end) {
        this.end = end;
    }

    public int getFinished() {
        return finished;
    }

    public void setFinished(int finished) {
        this.finished = finished;
    }
}