基于ormlite框架SQLite数据库的使用

最新推荐文章于 2026-06-13 12:01:23 发布

原创最新推荐文章于 2026-06-13 12:01:23 发布 · 1k 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

数据库技术专栏收录该内容

2 篇文章

订阅专栏

本文介绍了ORMLite框架，这是一种对象关系映射技术，用于转换面向对象编程和数据库系统之间的数据。文章详细阐述了使用ORMLite的优点，如文档丰富、社区活跃、易于使用等，同时指出其基于反射和注解的原理可能导致效率较低。接着，文章逐步讲解了如何导入ORMLite库，创建DataBaseHelper和BaseDao基类，定义数据实体以及具体Dao操作，最后讨论了数据库的增删改查操作。

一、简介

ORM:对象关系映射，是一种程序设计技术，用于实现面向对象编程语言中不同类型系统的数据之间的转换。

优点：文档较全面，社区活跃，有好的维护，使用简单，易上手

缺点：基于反射和注解的原理，效率较低

二、步骤

导入包
compile 'com.j256.ormlite:ormlite-core:4.41'
compile 'com.j256.ormlite:ormlite-android:4.41'
创建基类DataBaseHelper
创建基类BaseDao类
创建数据源实体Item
创建操作数据库表格的Dao类

三、使用

DataBaseHelper:

public class DatabaseHelper extends OrmLiteSqliteOpenHelper {
	public ArrayList<Class> tableList=new ArrayList<Class>();
    private static final String DATABASE_NAME = "Qiao.db";
    private static final int DATABASE_VERSION = 1;
    public DatabaseHelper(Context context){
        //tableList=list;
    	super(context, DATABASE_NAME, null, DATABASE_VERSION);
    	InitTables();
    }
    private void InitTables() {
		// TODO Auto-generated method stub
    	tableList.add(ContactPersonItem.class);
    	tableList.add(AddrListItem.class);  (添加数据表格)
    }
	/**  
     * 创建SQLite数据库  
     */  
    @Override
    public void onCreate(SQLiteDatabase sqliteDatabase, ConnectionSource connectionSource) {
        try { 
        	for (int i = 0; i < tableList.size(); i++) {
				TableUtils.createTable(connectionSource, tableList.get(i));
			}
            //TableUtils.createTable(connectionSource, PointFull.class);  
        } catch (SQLException e) {
            Log.e(DatabaseHelper.class.getName(), "Unable to create datbases", e);
        }  
    }
    /**  
     * 更新SQLite数据库  
     */  
    @Override
    public void onUpgrade(  
            SQLiteDatabase sqliteDatabase,
            ConnectionSource connectionSource,
            int oldVer,  
            int newVer) {  
        try {
        	for (int i = 0; i < tableList.size(); i++) {
				TableUtils.dropTable(connectionSource, tableList.get(i), true);
			}
           // TableUtils.dropTable(connectionSource, PointFullItem.class, true);
            onCreate(sqliteDatabase, connectionSource);  
        } catch (SQLException e) {
            Log.e(DatabaseHelper.class.getName(),
                    "Unable to upgrade database from version " + oldVer + " to new "
                            + newVer, e);
        }  
    }
    /** 
     * Close the database connections and clear any cached DAOs. 
     */  
    @Override
    public void close() {  
        super.close();  
    }  
}

BaseDao:

public abstract class BaseDao<T, Integer> {
    protected DatabaseHelper mDatabaseHelper;
    protected Context mContext;
    public BaseDao(Context context) {
        mContext = context;
        getHelper();
    }
    public <D extends Dao<T, ?>, T> D getHelpergetDao(Class<T> clazz) throws SQLException {
        return getHelper().getDao(clazz);
    }
    public ConnectionSource getHelpergetConnectionSource()
    {
        return getHelper().getConnectionSource();
    }
    public DatabaseHelper getHelper() {
        if (mDatabaseHelper == null) {
            try {
                mDatabaseHelper = OpenHelperManager.getHelper(mContext, DatabaseHelper.class);
            } catch (Exception e) {
                System.err.println(e);
            }
        }
        return mDatabaseHelper;
    }
    public abstract String FileName();
    public abstract Dao<T, Integer> getDao() throws SQLException;
    public abstract void ClearTable() throws SQLException;
    public int save(T t) throws SQLException {
        return getDao().create(t);
    }
    public T SaveOrUpdate(T t) throws SQLException {
        return (T) getDao().createOrUpdate(t);
    }
    public List<T> queryForMatching(T t) throws SQLException
    {
        return getDao().queryForMatching(t);
    }
    public T saveAndGetValue(T t) throws SQLException {
        int result=getDao().create(t);
        return t;
    }
    public List<T> query(PreparedQuery<T> preparedQuery) throws SQLException {
        Dao<T, Integer> dao = getDao();
        return dao.query(preparedQuery);
    }

    public List<T> query(String attributeName, String attributeValue) throws SQLException {
        QueryBuilder<T, Integer> queryBuilder = getDao().queryBuilder();
        queryBuilder.where().eq(attributeName, attributeValue);
        PreparedQuery<T> preparedQuery = queryBuilder.prepare();

        return query(preparedQuery);
    }

    public List<T> query(String[] attributeNames, String[] attributeValues) throws SQLException{
        if (attributeNames.length != attributeValues.length) {
            //throw new InvalidParamsException("params size is not equal");
        }
        QueryBuilder<T, Integer> queryBuilder = getDao().queryBuilder();
        Where<T, Integer> wheres = queryBuilder.where();
        for (int i = 0; i < attributeNames.length; i++) {
            wheres.eq(attributeNames[i], attributeValues[i]);
        }
        PreparedQuery<T> preparedQuery = queryBuilder.prepare();
        return query(preparedQuery);
    }

    public List<T> queryAll() throws SQLException {
        // QueryBuilder<T, Integer> queryBuilder = getDao().queryBuilder();
        // PreparedQuery<T> preparedQuery = queryBuilder.prepare();
        // return query(preparedQuery);
        Dao<T, Integer> dao = getDao();
        return dao.queryForAll();
    }

    public T queryById(String idName, String idValue) throws SQLException {
        List<T> lst = query(idName, idValue);
        if (null != lst && !lst.isEmpty()) {
            return lst.get(0);
        } else {
            return null;
        }
    }
    public List<T> queryByIds(String idName, String idValue) throws SQLException {
        List<T> lst = query(idName, idValue);
        if (null != lst && !lst.isEmpty()) {
            return lst;
        } else {
            return null;
        }
    }

    public int delete(PreparedDelete<T> preparedDelete) throws SQLException {
        Dao<T, Integer> dao = getDao();
        return dao.delete(preparedDelete);
    }

    public int delete(T t) throws SQLException {
        Dao<T, Integer> dao = getDao();
        return dao.delete(t);
    }

    public int delete(List<T> lst) throws SQLException {
        Dao<T, Integer> dao = getDao();
        return dao.delete(lst);
    }

    public int delete(String[] attributeNames, String[] attributeValues) throws SQLException
    {
        List<T> lst = query(attributeNames, attributeValues);
        if (null != lst && !lst.isEmpty()) {
            return delete(lst);
        }
        return 0;
    }

    public int deleteById(String idName, String idValue) throws SQLException
    {
        T t = queryById(idName, idValue);
        if (null != t) {
            return delete(t);
        }
        return 0;
    }

    public int update(T t) throws SQLException {
        Dao<T, Integer> dao = getDao();
        return dao.update(t);
    }
    public boolean isTableExsits() throws SQLException {
        return getDao().isTableExists();
    }

    public long countOf() throws SQLException {
        return getDao().countOf();
    }
    public List<T> query(Map<String, Object> map) throws SQLException {
        QueryBuilder<T, Integer> queryBuilder = getDao().queryBuilder();
        if (!map.isEmpty()) {
            Where<T, Integer> wheres = queryBuilder.where();
            Set<String> keys = map.keySet();
            ArrayList<String> keyss = new ArrayList<String>();
            keyss.addAll(keys);
            for (int i = 0; i < keyss.size(); i++) {
                if (i == 0) {
                    wheres.eq(keyss.get(i), map.get(keyss.get(i)));
                } else {
                    wheres.and().eq(keyss.get(i), map.get(keyss.get(i)));
                }
            }
        }
        PreparedQuery<T> preparedQuery = queryBuilder.prepare();
        return query(preparedQuery);
    }

    public List<T> query(Map<String, Object> map, Map<String, Object> lowMap,
                         Map<String, Object> highMap) throws SQLException {
        QueryBuilder<T, Integer> queryBuilder = getDao().queryBuilder();
        Where<T, Integer> wheres = queryBuilder.where();
        if (!map.isEmpty()) {
            Set<String> keys = map.keySet();
            ArrayList<String> keyss = new ArrayList<String>();
            keyss.addAll(keys);
            for (int i = 0; i < keyss.size(); i++) {
                if (i == 0) {
                    wheres.eq(keyss.get(i), map.get(keyss.get(i)));
                } else {
                    wheres.and().eq(keyss.get(i), map.get(keyss.get(i)));
                }
            }
        }
        if (!lowMap.isEmpty()) {
            Set<String> keys = lowMap.keySet();
            ArrayList<String> keyss = new ArrayList<String>();
            keyss.addAll(keys);
            for (int i = 0; i < keyss.size(); i++) {
                if(map.isEmpty()){
                    wheres.gt(keyss.get(i), lowMap.get(keyss.get(i)));
                }else{
                    wheres.and().gt(keyss.get(i), lowMap.get(keyss.get(i)));
                }
            }
        }

        if (!highMap.isEmpty()) {
            Set<String> keys = highMap.keySet();
            ArrayList<String> keyss = new ArrayList<String>();
            keyss.addAll(keys);
            for (int i = 0; i < keyss.size(); i++) {
                wheres.and().lt(keyss.get(i), highMap.get(keyss.get(i)));
            }
        }
        PreparedQuery<T> preparedQuery = queryBuilder.prepare();
        return query(preparedQuery);
    }
}

实体Item:更改一下序列化ID

public class AddrListItem {

	private static final long serialVersionUID = -554466990987104L;
	@DatabaseField(allowGeneratedIdInsert = true, generatedId = true)
	private int ID;

	@DatabaseField(defaultValue = "")
	private String addr;

	@DatabaseField(defaultValue = "")
	private double lat;

	@DatabaseField(defaultValue = "")
	private double lng;

	public AddrListItem() {

	}

	public AddrListItem(String name,double lat,double lng) {
		super();
		this.addr = name;
		this.lat = lat;
		this.lng = lng;
	}

	public static long getSerialVersionUID() {
		return serialVersionUID;
	}

	public String getAddr() {
		return addr;
	}

	public void setAddr(String addr) {
		this.addr = addr;
	}

	public double getLat() {
		return lat;
	}

	public void setLat(double lat) {
		this.lat = lat;
	}

	public double getLng() {
		return lng;
	}

	public void setLng(double lng) {
		this.lng = lng;
	}

	public int getID() {
		return ID;
	}

	public void setID(int ID) {
		this.ID = ID;
	}
}

Dao类：

public class AddrListDao extends BaseDao<AddrListItem, Integer> {
    public AddrListDao(Context context) {
        super(context);
    }

    @Override
    public Dao<AddrListItem, Integer> getDao() throws SQLException {
        return getHelpergetDao(AddrListItem.class);
    }

    @Override
    public void ClearTable() throws SQLException {
        TableUtils.clearTable(getHelpergetConnectionSource(), AddrListItem.class);
        // TODO Auto-generated method stub
    }
    @Override
    public String FileName() {
        // TODO Auto-generated method stub
        return "地址搜索历史记录";
    }
}

四、增删改查

存数据库：

					//存数据库
					AddrListItem addrListItem = new AddrListItem();
					addrListItem.setAddr(toAddr);
					addrListItem.setLat(stopLat);
					addrListItem.setLng(stopLng);

					AddrListDao addrListDao = new AddrListDao(HomeActivity.this);


					try {
//						if(addrListDao.countOf()>5){
//							addrListDao.ClearTable();
//						}
						addrListDao.save(addrListItem);
					} catch (SQLException e) {
						e.printStackTrace();
					}

查询：

	private void LoadListView()
	{
		PhoneListao phoneListao=new PhoneListao(getActivity());
		try {
			//mdao.ClearTable();清除数据库表格
			contactList.clear();
			contactList=phoneListao.queryAll();
		} catch (SQLException e) {
//			e.printStackTrace();
		}
	}