app/src/main/java/com/sqh/market/utils/SQLUtils.java
package com.sqh.market.utils;

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


import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

/**
 * SQLite工具类
 *
 * @author 郑龙
 */

public class SQLUtils {
    private static SQLiteHelper mHelper = null;

    /**
     * 获取数据表中所有数据
     *
     * @return Cursor
     */
    public Cursor selectAllData() {
        return mHelper.select();
    }


    /**
     * 依据条件进行模糊查询
     *
     * @param tag
     * @return ArrayList
     */
    public ArrayList<String> searchByCondition(String... tag) {
        return mHelper.query(tag);
    }

    public long insertNewSearchHistory(String text) {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String time = sdf.format(new Date());
        return mHelper.insert(text, time);
    }

    public void deleteAllData() {
        mHelper.delete();
    }

    public SQLUtils(Context context) {
        mHelper = SQLiteHelper.getInstance(context);
    }


    /**
     * 内部类，SQLiteHelper，提供了基础的数据库创建表，增删改查等操作
     *
     * @author 郑龙
     */
    public static class SQLiteHelper extends SQLiteOpenHelper {
        /**
         * 数据库插入错误代码
         */
        public static final int DATABASE_INSERT_ERROR = -1;

        private static int DATABASE_VERSION = 1;
        private static final String DATABASE_NAME = "Search_History";
        private static final String TABLE_NAME = "Search_Table";
        private static SQLiteHelper mHelper = null;

        private static Context mContext;

        /**
         * 单例设计模式，顺手将构造方法设为私有
         *
         * @return SQLiteHelper
         */
        public static SQLiteHelper getInstance(Context context) {
            if (mHelper == null) {
                mHelper = new SQLiteHelper(context);
            }
            mContext = context;
            return mHelper;
        }

        private SQLiteHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
            mContext = context;
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            db.execSQL("CREATE TABLE " + TABLE_NAME + " (text VARCHAR(30) PRIMARY KEY, time VARCHAR(20))");
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
            onCreate(db);
        }

        /**
         * 获取表单所有数据
         *
         * @return Cursor
         */
        public Cursor select() {
            SQLiteDatabase db = this.getReadableDatabase();
            Cursor cursor = db.query(TABLE_NAME, null, null, null, null, null, null);
            return cursor;
        }

        /**
         * 根据条件模糊查询数据库数据
         *
         * @param str
         * @return
         */
        public ArrayList<String> query(String... str) {
            ArrayList<String> result_list = new ArrayList<String>();
            SQLiteDatabase db = this.getReadableDatabase();
            //模糊查询的三种方式：
/*
 * 全部查询
        String current_sql_sel = "SELECT  * FROM " + tab_name;
        Cursor c = mDatabase.rawQuery(current_sql_sel, null);*/

            //1.使用这种query方法%号前不能加' ;
            //        Cursor c_test = mDatabase.query(tab_name, new String[]{tab_field02}, tab_field02+"  LIKE ? ",
            //                new String[] { "%" + str[0] + "%" }, null, null, null);

            //2.使用这种query方法%号前必须加'  ;
            //  Cursor  c_test=mDatabase.query(tab_name, new String[]{tab_field02},tab_field02+"  like '%" + str[0] + "%'", null, null, null, null);

            //3.使用这种方式必须在%号前加'  ;
            String current_sql_sel = "SELECT  * FROM " + TABLE_NAME + " where text like '%" + str[0] + "%'";
            Cursor c_test = db.rawQuery(current_sql_sel, null);

            Log.d("tag", "查询完成...");
            while (c_test.moveToNext()) {
                String name = c_test.getString(c_test.getColumnIndex("text"));
                //name.contains(str[0]);
                // 让集合中的数据不重复;
                if (!result_list.contains(name)) {
                    result_list.add(name);
                    Log.d("tag", name);
                }
            }
            c_test.close();

            return result_list;
        }


        /**
         * 插入一条记录
         *
         * @param text
         * @param time
         * @return 结果
         */
        public long insert(String text, String time) {
            SQLiteDatabase db = this.getWritableDatabase();
            ContentValues cv = new ContentValues();
            try {
                cv.put("text", text);
                cv.put("time", time);

                long row = db.insert(TABLE_NAME, null, cv);
                return row;
            } catch (Exception e) {
                Log.e("插入错误！", "RT");
            }

            return DATABASE_INSERT_ERROR;
        }

        /**
         * 删除表中记录的所有数据
         */
        public void delete() {
            SQLiteDatabase db = this.getWritableDatabase();
            db.execSQL("delete from " + TABLE_NAME);
        }
    }
}