SQlist

一、创建SQ

DBHelper

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


public class DBHelper extends SQLiteOpenHelper {
    private static final String DATABASE_NAME = "yg.db";  //数据库名字
    private static final int DATABASE_VERSION = 1;         //数据库版本号

    public DBHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        Log.i("TAG:","创建person数据库表！");
        sqLiteDatabase.execSQL("CREATE TABLE IF NOT EXISTS person(_id INTEGER PRIMARY KEY AUTOINCREMENT," +
                "institutions VARCHAR," +
                "city VARCHAR," +
                "district VARCHAR," +
                "name VARCHAR," +
                "address VARCHAR," +
                "latitude VARCHAR," +
                "longitude VARCHAR," +
                "type VARCHAR," +
                "rank VARCHAR," +
                "phone VARCHAR," +
                "keyword VARCHAR)");
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {

    }

    @Override
    public void onOpen(SQLiteDatabase db) {
        super.onOpen(db);
    }
}

DBManager

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

import com.you.yg.you_galleria.entity.WftTestEntity;

import java.util.ArrayList;
import java.util.HashMap;

public class DBManager {
    DBHelper helper;

    SQLiteDatabase sqLiteDatabase;



    public DBManager(Context context) {
         helper = new DBHelper(context);
        sqLiteDatabase = helper.getReadableDatabase();

    }

          /**
     * execSQL()方法可以执行insert，update，delete语句
    * 实现对数据库的 增，删，改 功能
     * sql为操作语句 ， bindArgs为操作传递参数
     * **/


    public boolean updateSQLite(String sql, Object[] bindArgs) {
         boolean isSuccess = false;
         try {
            sqLiteDatabase.execSQL(sql, bindArgs);
            isSuccess = true;

        } catch (Exception e) {
            e.printStackTrace();

        } finally {
            if (sqLiteDatabase != null) {
                sqLiteDatabase.close();

            }
            Log.i("TAG:", "数据插入数据库中状态：" + isSuccess);
        }
        return isSuccess;
    }

    /**
     * rawQuery()方法可以执行select语句
     * 实现查询功能
     * sql为操作语句 ， bindArgs为操作传递参数
     **/
    public ArrayList<HashMap<String, String>> querySQLite(String sql, String[] bindArgs) {
        ArrayList<HashMap<String, String>> list = new ArrayList<HashMap<String, String>>();

        /**Cursor是结果集游标，使用Cursou.moveToNext()方法可以从当前行移动到下一行**/
        Cursor cursor = sqLiteDatabase.rawQuery(sql, bindArgs);
        int clos_len = cursor.getColumnCount();                 //获取数据所有列数

        Log.i("TAG:", "querySQLite()方法中获得总列数clos_len：" + clos_len);

//        boolean isfals = cursor.moveToFirst();
//        Log.i("TAG:", "isfals值为：" + isfals);

        while (cursor.moveToNext()) {
            Log.i("TAG:", "进入到while循环中");

            HashMap<String, String> map = new HashMap<>();
            for (int i = 0; i < clos_len; i++) {                      //循环表格中的每一列
                String clos_name = cursor.getColumnName(i);     //从给定的索引i返回列名
                String clos_value = cursor.getString(cursor.getColumnIndex(clos_name));//返回指定的名称，没有就返回-1
                if (clos_value == null) {
                    clos_value = "";
                }

                Log.i("TAG:", "while循环下面的for循环拿到的数据clos_value为："
                        + cursor.getString(cursor.getColumnIndex(clos_name)));

                map.put(clos_name, clos_value);
            }
            list.add(map);
        }
        return list;
    }
}

SQLFunction

package com.you.yg.you_galleria.sq;

import android.content.Context;
import android.util.Log;

import com.you.yg.you_galleria.DemoBean;
import com.you.yg.you_galleria.entity.DataBean;
import com.you.yg.you_galleria.entity.WftTestEntity;

import org.jsoup.helper.StringUtil;

import java.util.ArrayList;
import java.util.HashMap;

public class SQLFunction {
    static DBHelper helper;


    public static void initTable(Context context) {
        helper = new DBHelper(context);
        helper.getReadableDatabase();
    }

    /**
     * 【插入数据】
     **/
    public static void insert(Context context, WftTestEntity wf) {

        Log.i("TAG:", "插入数据到数据库表：person中:" + wf.toString());

        DBManager sqlManager = new DBManager(context);
        helper = new DBHelper(context);
        helper.getWritableDatabase();


        String sql = "insert into person (institutions, city, district, name, address, latitude, longitude, type, rank, phone, keyword) values (?,?,?,?,?,?,?,?,?,?,?)";
        Object[] bindArgs = {wf.getInstitutions(), wf.getCity(), wf.getDistrict(), wf.getName(), wf.getAddress(), wf.getLatitude(), wf.getLongitude(), wf.getType(), wf.getRank(), wf.getPhone(), wf.getKeyword()};
        sqlManager.updateSQLite(sql, bindArgs);
    }


    /**
     * 【模糊查询】
     **/
    public static ArrayList<HashMap<String, String>> query(Context context, String where2) {
        DBManager sqlManager = new DBManager(context);
        ArrayList<HashMap<String, String>> list = new ArrayList<>();
        String sql = "select * from person where city like ? or address like ? or name like ? or district like ? or keyword like ?";
        if (!StringUtil.isBlank(where2)) {
            where2 = "%" + where2 + "%";
            list = sqlManager.querySQLite(sql, new String[]{where2,where2,where2,where2,where2});
        }


        Log.i("TAG:", "查询完毕，返回数据：" + list.size());

        return list;
    }

    /**
     * 【删除数据】
     **/
    public static void delete(Context context, Object[] data) {
        DBManager sqlmanager = new DBManager(context);
        String sql = "delete from person where _id =  ? ";
        sqlmanager.updateSQLite(sql, data);
    }

    /**
     * 【更新数据】
     **/
    public static void update(Context context, Object[] data) {
        helper = new DBHelper(context);
        helper.getReadableDatabase();
        DBManager sqlManager = new DBManager(context);
        String sql = "update person set name=? , info=? where _id=?";
        sqlManager.updateSQLite(sql, data);
    }
}

二、相对应的bean类(由于我的是本地文件数据，所以bean类有所区别)

//import com.opencsv.bean.CsvBindByName;
//import com.opencsv.bean.CsvBindByPosition;

import java.io.Serializable;

//    file:///android_asset/bank/YouGalleria.csv
public class WftTestEntity implements Serializable {

//    @CsvBindByPosition(position = 0)
//    @CsvBindByName(column = "institutions")
    private String institutions;
//    @CsvBindByName(column = "city")
    private String city;
//    @CsvBindByName(column = "district")
    private String district;
//    @CsvBindByName(column = "name")
    private String name;
//    @CsvBindByName(column = "address")
    private String address;
//    @CsvBindByName(column = "latitude")
    private String latitude;
//    @CsvBindByName(column = "longitude")
    private String longitude;
//    @CsvBindByName(column = "type")
    private String type;
//    @CsvBindByName(column = "rank")
    private String rank;
//    @CsvBindByName(column = "phone")
    private String phone;
//    @CsvBindByName(column = "keyword")
    private String keyword;

    public String getInstitutions() {
        return institutions;
    }

    public void setInstitutions(String institutions) {
        this.institutions = institutions;
    }

    public String getCity() {
        return city;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public String getLatitude() {
        return latitude;
    }

    public void setLatitude(String latitude) {
        this.latitude = latitude;
    }

    public String getLongitude() {
        return longitude;
    }

    public void setLongitude(String longitude) {
        this.longitude = longitude;
    }

    public String getType() {
        return type;
    }

    public void setType(String type) {
        this.type = type;
    }

    public String getRank() {
        return rank;
    }

    public void setRank(String rank) {
        this.rank = rank;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    public String getKeyword() {
        return keyword;
    }

    public void setKeyword(String keyword) {
        this.keyword = keyword;
    }

    public void setCity(String city) {
        this.city = city;
    }

    public String getDistrict() {
        return district;
    }

    public void setDistrict(String district) {
        this.district = district;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }


    @Override
    public String toString() {
        return "WftTestEntity{" +
                "institutions='" + institutions + '\'' +
                ", city='" + city + '\'' +
                ", district='" + district + '\'' +
                ", name='" + name + '\'' +
                ", address=" + address +
                ", latitude=" + latitude +
                ", longitude=" + longitude +
                ", type=" + type +
                ", rank=" + rank +
                ", phone=" + phone +
                ", keyword=" + keyword +
                '}';
    }
}

三、我的数据是本地WPS表格数据.csv文件 （在Android studio 创建 assets ,app/assets）将.csv 文件放入，bean类要与文件格式相对应

四、将.csv 文件转成对象 list

//相应的依赖
*****//.csv 转 bean
    implementation 'com.google.code.gson:gson:2.8.6'
    implementation 'com.opencsv:opencsv:3.1'
    implementation'me.jessyan:autosize:1.2.1'
******
    implementation 'com.android.support:recyclerview-v7:27.1.1'
    implementation 'androidx.cardview:cardview:1.0.0'
    implementation 'com.jakewharton:butterknife:10.0.0'
    annotationProcessor 'com.jakewharton:butterknife-compiler:10.0.0'
    //ButterKnife依赖插件：
    implementation 'org.greenrobot:eventbus:3.0.0'
    implementation 'org.jsoup:jsoup:1.10.2'
    implementation 'me.yatoooon:screenadaptation:1.1.1'

 /**
     * 从csv文件中读取数据，构造 bean 对象.
     *
     * @param context
     * @return
     */
    private List<WftTestEntity> makeDataBeanfromCSVFile(Context context) {
        List<WftTestEntity> dataBeans = new ArrayList<WftTestEntity>();

        List<String[]> list = new ArrayList<String[]>();
        String next[] = {};

        try {
            InputStreamReader csvStreamReader = new InputStreamReader(
                    context.getAssets().open("YouGalleria.csv"), "gbk");

            CSVReader reader = new CSVReader(csvStreamReader);

            dataBeans = CsvToObject.CsvToObjectV0(reader);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return dataBeans;
    }

package com.you.yg.you_galleria.utils;

import android.util.Log;

import com.opencsv.CSVReader;
import com.opencsv.bean.CsvToBean;
import com.opencsv.bean.HeaderColumnNameTranslateMappingStrategy;
import com.you.yg.you_galleria.entity.WftTestEntity;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

//import au.com.bytecode.opencsv.CSVReader;
//import au.com.bytecode.opencsv.bean.CsvToBean;
//import au.com.bytecode.opencsv.bean.HeaderColumnNameTranslateMappingStrategy;

public class CsvToObject {
    private final static String TAG = CsvToObject.class.getSimpleName();

    public static String CsvToObject(CSVReader reader) {
        Map<String, String> columnMapping = new HashMap<String, String>();
        columnMapping.put("所属环节", "header1");//csv中的header1对应bean的header1
        columnMapping.put("日均用户数", "header2");
        columnMapping.put("日均环节转化率", "header3");
        columnMapping.put("占位", "header4");
        columnMapping.put("日均整体转化率", "header5");

        HeaderColumnNameTranslateMappingStrategy<WftTestEntity> mapper = new HeaderColumnNameTranslateMappingStrategy<WftTestEntity>();
        mapper.setColumnMapping(columnMapping);
        mapper.setType(WftTestEntity.class);

        CsvToBean<WftTestEntity> csvToBean = new CsvToBean<WftTestEntity>();

        List<WftTestEntity> list = csvToBean.parse(mapper, reader);
        Log.d(TAG, "list: " +  list);
        for(WftTestEntity e : list) {
            Log.d(TAG, "CsvToObject: " +  e.toString());
        }
        return "nothing";
    }

    public static List<WftTestEntity> CsvToObjectV0(CSVReader reader) {
        List<WftTestEntity> dataList = new ArrayList<WftTestEntity>();
        List<String[]> list = new ArrayList<String[]>();
        String next[] = {};
        try {
            for (; ; ) {
                next = reader.readNext();
                if (next != null) {
                    list.add(next);
                } else {
                    break;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        for (int i = 1; i < list.size(); i++) {
            WftTestEntity dataBean = new WftTestEntity();
            dataBean.setInstitutions(list.get(i)[0]);
            dataBean.setCity(list.get(i)[1]);
            dataBean.setDistrict(list.get(i)[2]);
            dataBean.setName(list.get(i)[3]);
            dataBean.setAddress(list.get(i)[4]);
            dataBean.setLatitude(list.get(i)[5]);
            dataBean.setLongitude(list.get(i)[6]);
            dataBean.setType(list.get(i)[7]);
            dataBean.setRank(list.get(i)[8]);
            dataBean.setPhone(list.get(i)[9]);
            dataBean.setKeyword(list.get(i)[10]);
            Log.d("CsvToObject", "data: " + dataBean.toString());
            dataList.add(dataBean);
        }
        return dataList;
    }
}

//调用
  dataBeans = makeDataBeanfromCSVFile(context);

五、调用SQlist 对应的增、删 、改、查

//activity中 例如：
   sqlFunction.insert(context, xxlistxx);//插入数据