安卓数据库框架--ormlite

安卓开发的时候，有时候会用到数据库。这里介绍一个orm框架。

orm是每个类对应一个数据库中的表。对数据库的操作是以Bean为单位来进行的。

1、引入jar包，两种方式

（1）可以直接下载jar包，放在lib目录下

（2）在gradle中配置

dependencies {
    .......    
    // https://mvnrepository.com/artifact/com.j256.ormlite/ormlite-android
    implementation group: 'com.j256.ormlite', name: 'ormlite-android', version: '5.1'
    implementation group: 'com.j256.ormlite', name: 'ormlite-core', version: '5.1'
}

2、创建dbheper、bean、和dao。直接用dao操作数据库

dbhelper

package com.smartisan.muse.sql;

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

import com.j256.ormlite.android.apptools.OrmLiteSqliteOpenHelper;
import com.j256.ormlite.dao.Dao;
import com.j256.ormlite.support.ConnectionSource;
import com.j256.ormlite.table.TableUtils;
import com.smartisan.muse.utils.LogUtils;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

public class DatabaseHelper extends OrmLiteSqliteOpenHelper {

    private static final String TAG = "DatabaseHelper";


    public static final String TABLE_NAME = "/ormlite_citys.db";//默认是在data/data/包名/databases/路径下
    //    public static final String DATABASE_PATH = ;//指定路径
    private static final int DB_VERSION = 1;

    private Map<String, Dao> daos = new HashMap<String, Dao>();

    private DatabaseHelper(Context context) {
        super(context, context.getApplicationInfo().dataDir + TABLE_NAME, null, DB_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase database,
                         ConnectionSource connectionSource) {
        try {
            TableUtils.createTableIfNotExists(connectionSource, City.class);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }



    @Override
    public void onUpgrade(SQLiteDatabase database,
                          ConnectionSource connectionSource, int oldVersion, int newVersion) {
        //整表删除创建
//        try {
//            TableUtils.dropTable(connectionSource, Childs.class, true);
//            TableUtils.dropTable(connectionSource, Courses.class, true);
//            TableUtils.dropTable(connectionSource, Parents.class, true);
//            onCreate(database, connectionSource);
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }
        //更新数据库时只需添加新增字段
        if (newVersion == 2) {
            //数据库、表名、列名、类型
//            updateColumn(database, "muse_table_city", "s_phone", "VARCHAR", null);
        }
    }

    private static DatabaseHelper instance;

    /**
     * 单例获取该Helper
     *
     * @param context
     * @return
     */
    public static synchronized DatabaseHelper getHelper(Context context) {
        context = context.getApplicationContext();
        if (instance == null) {
            synchronized (DatabaseHelper.class) {
                if (instance == null)
                    instance = new DatabaseHelper(context);
            }
        }

        return instance;
    }

    public synchronized Dao getHelperDao(Class<City> clazz) throws SQLException {
        Dao dao = null;
        String className = clazz.getSimpleName();

        if (daos.containsKey(className)) {
            dao = daos.get(className);
        }
        if (dao == null) {
            dao = super.getDao(clazz);
            daos.put(className, dao);
        }
        return dao;
    }

    /**
     * 释放资源
     */
    @Override
    public void close() {
        super.close();

        for (String key : daos.keySet()) {
            Dao dao = daos.get(key);
            dao = null;
        }
    }

    public static void updateColumn(SQLiteDatabase db, String tableName,
                                    String columnName, String columnType, Object defaultField) {
        try {
            if (db != null) {
                Cursor c = db.rawQuery("SELECT * from " + tableName
                        + " limit 1 ", null);
                boolean flag = false;

                if (c != null) {
                    for (int i = 0; i < c.getColumnCount(); i++) {
                        if (columnName.equalsIgnoreCase(c.getColumnName(i))) {
                            flag = true;
                            break;
                        }
                    }
                    if (flag == false) {
                        String sql = "alter table " + tableName + " add "
                                + columnName + " " + columnType + " default "
                                + defaultField;
                        db.execSQL(sql);
                    }
                    c.close();
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void deleteRecordsWithoutForeignKey(SQLiteDatabase db, String tableName) {
        if (db != null) {
            String sql = "DELETE from " + tableName;
            db.execSQL(sql);
        }
    }

}

bean

package com.smartisan.muse.sql;
import com.j256.ormlite.field.DatabaseField;
import com.j256.ormlite.table.DatabaseTable;

//定义表名
@DatabaseTable(tableName = "muse_table_city")
public class City {

    //generatedId定义主键自增长，columnName定义该字段在数据库中的列名
    @DatabaseField(useGetSet = true, generatedId = false, columnName = "id")
    private int id;


    @DatabaseField(useGetSet = true, columnName = "f_town_name")
    private String townName;//城镇名字

    @DatabaseField(useGetSet = true, columnName = "f_city_name")
    private String cityName;//所在地级市名字

    @DatabaseField(useGetSet = true, columnName = "f_province_name")
    private String provinceName;//省的名字

    @DatabaseField(useGetSet = true, columnName = "f_state_name")
    private String stateName;//国家的名字

    @DatabaseField(useGetSet = true, columnName = "f_short_name")
    private String shortName;//拼音缩写

    @DatabaseField(useGetSet = true, columnName = "f_long_name")
    private String longName;//拼音全写

    @DatabaseField(useGetSet = true, columnName = "f_search_time")
    private long time;//拼音全写

    public City() {
    }

    public City(String id, String townName, String cityName, String provinceName, String stateName, String shortName, String longName, long searchTime) {
        this.id = Integer.valueOf(id);
        this.townName = townName;
        this.cityName = cityName;
        this.provinceName = provinceName;
        this.stateName = stateName;
        this.shortName = shortName;
        this.longName = longName;
        this.time = searchTime;
    }

    public City(int id, String townName, String cityName, String provinceName, String stateName, String shortName, String longName, long searchTime) {
        this.id = id;
        this.townName = townName;
        this.cityName = cityName;
        this.provinceName = provinceName;
        this.stateName = stateName;
        this.shortName = shortName;
        this.longName = longName;
        this.time = searchTime;
    }

    public int getId() {
        return id;
    }

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

    public String getTownName() {
        return townName;
    }

    public void setTownName(String townName) {
        this.townName = townName;
    }

    public String getCityName() {
        return cityName;
    }

    public void setCityName(String cityName) {
        this.cityName = cityName;
    }

    public String getProvinceName() {
        return provinceName;
    }

    public void setProvinceName(String provinceName) {
        this.provinceName = provinceName;
    }

    public String getStateName() {
        return stateName;
    }

    public void setStateName(String stateName) {
        this.stateName = stateName;
    }

    public String getShortName() {
        return shortName;
    }

    public void setShortName(String shortName) {
        this.shortName = shortName;
    }

    public String getLongName() {
        return longName;
    }

    public void setLongName(String longName) {
        this.longName = longName;
    }

    public long getTime() {
        return time;
    }

    public void setTime(long time) {
        this.time = time;
    }

    public String getLocationInfo() {
        StringBuffer stringBuffer = new StringBuffer();
        if (!this.townName.equals(this.cityName)) {
            stringBuffer.append(this.cityName + ",");
        }
        if (!stringBuffer.toString().contains(this.provinceName)) {
            stringBuffer.append(this.provinceName + ",");
        }
        if (!stringBuffer.toString().contains(this.stateName)) {
            stringBuffer.append(this.stateName + ",");
        }
        String info = stringBuffer.toString();
        return info.substring(0, info.length() - 1);
    }

    @Override
    public String toString() {
        return "id :" + id + "--" + townName + "," + cityName + "," + provinceName + "," + stateName + "," + time + "," + getLocationInfo();
    }
}

Dao

package com.smartisan.muse.sql;
import android.content.Context;
import android.util.Log;

import com.j256.ormlite.dao.Dao;
import com.smartisan.muse.R;
import com.smartisan.muse.utils.LogUtils;

import java.io.File;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import static com.smartisan.muse.sql.DatabaseHelper.TABLE_NAME;


public class CityDao {

    private static final String TAG = "CityDao";

    private Dao<City, Integer> childDao;
    private DatabaseHelper helper;

    public CityDao(Context contex) {
        try {
            helper = DatabaseHelper.getHelper(contex);
            childDao = helper.getHelperDao(City.class);
            if (childDao == null) {
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    public void copyDataBase(Context context) {
        String newPath = context.getApplicationInfo().dataDir + TABLE_NAME;
        File newFile = new File(newPath);
        if (!newFile.exists()) {
            copyFile(context, newPath);
        }
    }


    private void copyFile(Context context, String newPath) {
        try {
            int bytesum = 0;
            int byteread = 0;
//            File oldfile = new File(oldPath);
//            if (oldfile.exists()) { //文件存在时
//                InputStream inStream = new FileInputStream(oldPath); //读入原文件
            InputStream inStream = context.getResources().getAssets().open("ormlite_citys.db"); //读入原文件
            if (inStream != null) {
                FileOutputStream fs = new FileOutputStream(newPath);
                byte[] buffer = new byte[1444];
                int length;
                while ((byteread = inStream.read(buffer)) != -1) {
                    bytesum += byteread; //字节数 文件大小
                    System.out.println(bytesum);
                    fs.write(buffer, 0, byteread);
                }
                inStream.close();
            } else {
                cityCreateDB(context);
            }
        } catch (Exception e) {
            LogUtils.e(TAG, "copy database error!");
            e.printStackTrace();

        }

    }

    public void cityCreateDB(Context context) {
        String[] citys = context.getResources().getStringArray(R.array.city_cn);
        for (String city : citys) {
            String[] cs = city.split(",");
            for (int i = 0; i < cs.length; i++) {
                cs[i] = cs[i].replaceAll("'", "");
            }
            City c = new City(cs[0], cs[1], cs[2], cs[3], cs[4], cs[5], cs[6], 0);
            Log.d(TAG, "cityCreateDB: " + c);
            addCity(c);
        }
        Log.d(TAG, "cityCreateDB finished>>>>>" + getCityCount());
    }

    /**
     * 增
     */
    public void addCity(City city) {
        try {
            childDao.create(city);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 删（通过实体）
     */
    public void delChild(City city) {
        try {
            childDao.delete(city);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 删（通过id）
     *
     * @param id
     */
    public void delChildById(Integer id) {
        try {
            childDao.deleteById(id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 改
     */
    public void updateChild(City city) {
        try {
            childDao.update(city);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 查
     *
     * @return
     */
    public List<City> queryAllChild() {
        ArrayList<City> childs = null;
        try {
            childs = (ArrayList<City>) childDao.queryForAll();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return childs;
    }

    /**
     * 获取user
     */
    public City getChild(Integer id) {
        try {
            return childDao.queryForId(id);
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    public int getCityCount() {
        try {
            return childDao.queryForAll().size();
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }


    /**
     * 查询citys
     */
    public List<City> searchCityByName(String name) {
        try {
            List<City> cities = childDao.queryBuilder().where()
                    .like("f_town_name", name)
                    .or().like("f_city_name", name)
                    .or().like("m_province_name", name)
                    .or().like("m_state_name", name)
                    .query();
            return cities;
        } catch (SQLException e) {
            e.printStackTrace();
            return new ArrayList<City>();
        }
    }

    /**
     * 添加历史查询数据
     */

    public void setHistoryCity(City city) {
        long time = new Date().getTime();
        city.setTime(time);
        try {
            childDao.update(city);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 删除查询历史
     */
    private void delHistoryCity(City city) {
        city.setTime(0);
        try {
            childDao.update(city);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private int maxHistoryNum = 10;

    /**
     * 获取历史查询数据
     */
    public List<City> getHistoryCitys() {
        try {
            List<City> cities = childDao.queryBuilder()
                    .orderBy("f_search_time", false)
                    .where()
                    .ne("f_search_time", 0)
                    .query();
            if (cities.size() > maxHistoryNum) {
                for (int i = maxHistoryNum; i < cities.size(); i++) {
                    delHistoryCity(cities.get(i));
                }
                cities = cities.subList(0, maxHistoryNum);
            }
            return cities;
        } catch (SQLException e) {
            e.printStackTrace();
            return new ArrayList<City>();
        }
    }
}