SQL(DBHelper)

 

DBHelper

package com.bwie.monthmoni1.db;

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

/**
 *
 */

public class DBHelper extends SQLiteOpenHelper {

    public DBHelper(Context context) {
        super(context, "db_months", null, 1);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String sql = "create table tb_month (title text primary key" +
                ",category text ,author_name text ,times text , thumbnail text,types text , thumbnail2 text, thumbnail3 text )";
        db.execSQL(sql);
    }

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

    }
}

 

NewsDao

package com.bwie.monthmoni1.db;

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

import com.bwie.monthmoni1.bean.News;

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

/**
 * Created by ThinkPad on 2018/9/21.
 */

public class NewsDao{
    public static final String TABLE_NAME = "tb_month";
    public static final String COLUMN_TITLE = "title";
    public static final String COLUMN_CATE = "category";
    public static final String COLUMN_DATA = "times";
    public static final String COLUMN_NAME= "author_name";
    public static final String COLUMN_TUONE = "thumbnail";
    public static final String COLUMN_TUTWO = "thumbnail2";
    public static final String COLUMN_TUTHREE = "thumbnail3";
    public static final String COLUMN_TYPE = "types";
    private final SQLiteDatabase db;

    public NewsDao (Context context){
        DBHelper dbHelper = new DBHelper(context);
        db = dbHelper.getWritableDatabase();
    }

    /**
     * 判断是否有重复
     * @param title
     * @return
     */
    public boolean hasTitle(String title){
        Cursor cursor = db.query(TABLE_NAME,null,COLUMN_TITLE+"=?",new String[]{title},null,null,null);
        if(cursor.moveToNext()){
            return false;
        }
        return true;
    }

    public void insert(List<News.DataBean> list, String str) {
        ContentValues values = new ContentValues();
        for (int i = 0; i < list.size(); i++) {
            if(hasTitle(list.get(i).getTitle())){
                values.put(COLUMN_TITLE,list.get(i).getTitle());
                values.put(COLUMN_CATE,list.get(i).getCategory());
                values.put(COLUMN_NAME,list.get(i).getAuthor_name());
                values.put(COLUMN_TUONE,list.get(i).getThumbnail());
                values.put(COLUMN_TUTWO,list.get(i).getThumbnail2());
                values.put(COLUMN_TUTHREE,list.get(i).getThumbnail3());
                values.put(COLUMN_TYPE,str);
                db.insert(TABLE_NAME,null,values);
            }
        }
    }

    public List<News.DataBean> query(String str) {
        List<News.DataBean> list = new ArrayList<>();
        Cursor cursor = db.query(TABLE_NAME,null,COLUMN_TYPE+"=?",new String[]{str},null,null,null);
        while (cursor.moveToNext()){
            News.DataBean dataBean = new News.DataBean();
            dataBean.setTitle(cursor.getString(cursor.getColumnIndex(COLUMN_TITLE)));
            dataBean.setCategory(cursor.getString(cursor.getColumnIndex(COLUMN_CATE)));
            dataBean.setAuthor_name(cursor.getString(cursor.getColumnIndex(COLUMN_NAME)));
            dataBean.setDate(cursor.getString(cursor.getColumnIndex(COLUMN_DATA)));
            dataBean.setThumbnail(cursor.getString(cursor.getColumnIndex(COLUMN_TUONE)));
            dataBean.setThumbnail2(cursor.getString(cursor.getColumnIndex(COLUMN_TUTWO)));
            dataBean.setThumbnail3(cursor.getString(cursor.getColumnIndex(COLUMN_TUTHREE)));
            list.add(dataBean);
        }
        return list;

    }
}