在android中使用OrmLite数据库框架

android中的数据库框架OrmLite，是对android中自带数据库的封装。下面按步骤说明如何使用。

最重要的是继承OrmLiteSqliteOpenHelper，获取得到helper对象

在里面重写onCreate，onUpgrade，close等方法，完成数据库表的创建，更新，资源释放。

获取到helper对象后，就可以使用helper的getDao方法获取dao来对数据表进行操作。下面是对数据库访问的Dao进行的封装

1.继承OrmLiteSqliteOpenHelper获取helper对象

public class DataBaseHelper extends OrmLiteSqliteOpenHelper {private static final String DATABASE_NAME = "ormlitesample.db";private static final int DATABASE_VERSION = 1;public DataBaseHelper(Context context) {super(context, DATABASE_NAME, null, DATABASE_VERSION);}@Overridepublic void onCreate(SQLiteDatabase arg0, ConnectionSource arg1) {createTable(arg1);}@Overridepublic void onUpgrade(SQLiteDatabase arg0, ConnectionSource arg1, int arg2,int arg3) {dropTable(arg1);onCreate(arg0, arg1);}@Overridepublic void close() {super.close();for (String key : daos.keySet()) {Dao dao = daos.get(key);dao = null;}daos.clear();}private static DataBaseHelper instance;public static DataBaseHelper getInstance() {return instance;}public static void setInstance(DataBaseHelper instance) {DataBaseHelper.instance = instance;}public static void releaseHelper() {if (DataBaseHelper.getInstance() != null) {OpenHelperManager.releaseHelper();DataBaseHelper.setInstance(null);}}/** * 单例获取该Helper *  * @param context * @return */public static synchronized DataBaseHelper getHelper(Context context) {if (instance == null) {synchronized (DataBaseHelper.class) {if (instance == null)instance = new DataBaseHelper(context);}}return instance;}/************************must mode start******************************//** * create all tables *  * @param connectionSource */protected static void createTable(ConnectionSource connectionSource) {try {TableUtils.createTableIfNotExists(connectionSource, Account.class);TableUtils.createTableIfNotExists(connectionSource, AccountOne.class);TableUtils.createTableIfNotExists(connectionSource, Order.class);TableUtils.createTableIfNotExists(connectionSource, AccountMany.class);TableUtils.createTableIfNotExists(connectionSource, OrderMany.class);// TODO create other tables} catch (SQLException e) {e.printStackTrace();}}/** * delete all tables *  * @param connectionSource */protected static void dropTable(ConnectionSource connectionSource) {try {TableUtils.dropTable(connectionSource, Account.class, true);TableUtils.dropTable(connectionSource, AccountOne.class, true);TableUtils.dropTable(connectionSource, Order.class, true);TableUtils.dropTable(connectionSource, AccountMany.class, true);TableUtils.dropTable(connectionSource, OrderMany.class, true);// TODO drop other tables} catch (SQLException e) {e.printStackTrace();}}/************************mode end******************************/private Map daos = new HashMap();public synchronized  Dao getDaos(Class clazz) {Dao dao = null;String className = clazz.getSimpleName();if (daos.containsKey(className)) {dao = daos.get(className);}if (dao == null) {try {dao = getDao(clazz);} catch (SQLException e) {e.printStackTrace();}daos.put(className, dao);}return (Dao) dao;}}

2.定义数据库对外的操作接口

/** * 按需要添加方法 * @author ZhangSheng * * @param  */public interface IDao {        public abstract T getSingleById(int id);        public abstract List getAll();        public abstract boolean update(T t);        public abstract int deleteByIds(Collection ids);        public abstract boolean delete(T t);        public abstract boolean add(T t);        public int updateBySQL(String statement, String... arguments);        public List getListByFieldAndOrderBy(Map fieldValues,              Map orderBy);  }

3.定义抽象的数据库AbstractDao

根据得到的helper得到dao进行数据表的操作,在实际开发中继承该抽象类即可，就可完成数据表的操作了

AbstractDao

public abstract class AbstractDao implements IDao {  public Dao dao;        public AbstractDao(Context context, Class clazz) {          try {              dao = DataBaseHelper.getHelper(context).getDaos(clazz);        } catch (SQLException e) {               e.printStackTrace();          }      }        @Override      public T getSingleById(int id) {          if (dao == null)              return null;          try {              return dao.queryForId(id);          } catch (SQLException | java.sql.SQLException e) {              e.printStackTrace();          }          return null;      }        @Override      public List getListByFieldAndOrderBy(Map fieldValues,              Map orderBy) {          if (dao == null)              return null;          try {              QueryBuilder qb = dao.queryBuilder();              if (orderBy != null) {                  for (Map.Entry entry : orderBy.entrySet()) {                      qb.orderBy(entry.getKey(), entry.getValue());                  }              }              if (fieldValues != null) {                  Where where = qb.where();                  for (Map.Entry entry : fieldValues.entrySet()) {                      where.eq(entry.getKey(), entry.getValue());                  }              }              return qb.query();                // return dao.queryForFieldValuesArgs(fieldValues);          } catch (SQLException | java.sql.SQLException e) {              e.printStackTrace();          }          return null;      }        @Override      public List getAll() {          if (dao == null)              return null;          try {              return dao.queryForAll();          } catch (SQLException | java.sql.SQLException e) {              e.printStackTrace();          }          return null;      }        public List getAllOrderBy(String columnName, boolean ascending) {          if (dao == null)              return null;          try {              return dao.queryBuilder().orderBy(columnName, ascending).query();          } catch (SQLException | java.sql.SQLException e) {              e.printStackTrace();          }          return null;      }        @Override      public boolean update(T t) {          if (dao == null)              return false;          try {              int update = dao.update(t);            Log.d("ormlite", "update="+update);return update == 1;          } catch (SQLException | java.sql.SQLException e) {             e.printStackTrace();          }          return false;      }        public int updateBySQL(String statement, String... arguments) {          if (dao == null)              return 0;          try {              return dao.updateRaw(statement, arguments);          } catch (SQLException | java.sql.SQLException e) {             e.printStackTrace();          }          return 0;      }        @Override      public int deleteByIds(Collection ids) {          if (dao == null)              return 0;          try {              return dao.deleteIds(ids);          } catch (SQLException | java.sql.SQLException e) {             e.printStackTrace();          }          return 0;      }        public boolean deleteAll(String table) {          if (dao == null)              return false;          try {              int raw = dao.executeRaw("DELETE FROM " + table);  //返回成功删除的个数            Log.d("ormlite", "deleteAll="+raw);return raw > 0;          } catch (SQLException | java.sql.SQLException e) {              e.printStackTrace();          }          return false;      }        @Override      public boolean delete(T t) {          if (dao == null)              return false;          try {              int delete = dao.delete(t);            Log.d("ormlite", "delete="+delete);return delete == 1;           } catch (SQLException | java.sql.SQLException e) {             e.printStackTrace();          }          return false;      }        @Override      public boolean add(T t) {          if (dao == null)              return false;          try {              int b = dao.create(t);  //成功返回1            Log.d("ormlite", "add="+b);return b==1;          } catch (SQLException | java.sql.SQLException e) {             e.printStackTrace();          }          return false;      }    }

4.几种建表时对应的表间关系：

单表，一对一，一对多，多对多的关系；实际使用时主要工作量就在这里了。建立表后，顺便建立表对应的Dao对象，这个就是继承AbstractDao。

a.建立单一的表Account，与其他表无任何关系

/**   * Example account object that is persisted to disk by the DAO and other example classes.   */  @DatabaseTable(tableName = "accounts")  public class Account {    // for QueryBuilder to be able to find the fields  public static final String NAME_FIELD_NAME = "name";  public static final String PASSWORD_FIELD_NAME = "passwd";    @DatabaseField(generatedId = true)  private int id;    @DatabaseField(columnName = NAME_FIELD_NAME, canBeNull = false)  private String name;    @DatabaseField(columnName = PASSWORD_FIELD_NAME)  private String password;    Account() {  //必须要有无参数构造函数 // all persisted classes must define a no-arg constructor with at least package visibility  }    public Account(String name) {  this.name = name;  }    public Account(String name, String password) {  this.name = name;  this.password = password;  }    public int getId() {  return id;  }    public String getName() {  return name;  }    public void setName(String name) {  this.name = name;  }    public String getPassword() {  return password;  }    public void setPassword(String password) {  this.password = password;  }    @Override  public int hashCode() {  return name.hashCode();  }    @Override  public boolean equals(Object other) {  if (other == null || other.getClass() != getClass()) {  return false;  }  return name.equals(((Account) other).name);  }  }

b.建立一对一的表结构：一个Order对应一个Account

Account的实体定义与a中的定义相同。 /** * Example order object that is persisted to disk by the DAO and other example classes. */@DatabaseTable(tableName = "orders")public class Order {public static final String ACCOUNT_ID_FIELD_NAME = "account_id";@DatabaseField(generatedId = true)private int id;@DatabaseField(foreign = true, columnName = ACCOUNT_ID_FIELD_NAME)private Account account;  //这里在表Order中字段是account_id，并不是account@DatabaseFieldprivate int itemNumber;@DatabaseFieldprivate int quantity;@DatabaseFieldprivate float price;Order() {// all persisted classes must define a no-arg constructor with at least package visibility}public Order(Account account, int itemNumber, float price, int quantity) {this.account = account;this.itemNumber = itemNumber;this.price = price;this.quantity = quantity;}public int getId() {return id;}public Account getAccount() {return account;}public void setAccount(Account account) {this.account = account;}public int getItemNumber() {return itemNumber;}public void setItemNumber(int itemNumber) {this.itemNumber = itemNumber;}public int getQuantity() {return quantity;}public void setQuantity(int quantity) {this.quantity = quantity;}public float getPrice() {return price;}public void setPrice(float price) {this.price = price;}}

c.建立一对多的表结构：Account一对多Order

/** * Example account object that is persisted to disk by the DAO and other example classes. */@DatabaseTable(tableName = "accounts")public class Account {// for QueryBuilder to be able to find the fieldspublic static final String NAME_FIELD_NAME = "name";public static final String PASSWORD_FIELD_NAME = "passwd";@DatabaseField(generatedId = true)private int id;@DatabaseField(columnName = NAME_FIELD_NAME, canBeNull = false)private String name;@DatabaseField(columnName = PASSWORD_FIELD_NAME)private String password;@ForeignCollectionField   //一个account持有多个order,一对多的关系private ForeignCollection orders;Account() {// all persisted classes must define a no-arg constructor with at least package visibility}public Account(String name) {this.name = name;}public Account(String name, String password) {this.name = name;this.password = password;}public int getId() {return id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public String getPassword() {return password;}public void setPassword(String password) {this.password = password;}public ForeignCollection getOrders() {return orders;}@Overridepublic int hashCode() {return name.hashCode();}@Overridepublic boolean equals(Object other) {if (other == null || other.getClass() != getClass()) {return false;}return name.equals(((Account) other).name);}}

/** * Example order object that is persisted to disk by the DAO and other example classes. */@DatabaseTable(tableName = "orders")public class Order {public static final String ACCOUNT_ID_FIELD_NAME = "account_id";@DatabaseField(generatedId = true)private int id;@DatabaseField(foreign = true, foreignAutoRefresh = true, columnName = ACCOUNT_ID_FIELD_NAME)private Account account;@DatabaseFieldprivate int itemNumber;@DatabaseFieldprivate int quantity;@DatabaseFieldprivate float price;Order() {// all persisted classes must define a no-arg constructor with at least package visibility}public Order(Account account, int itemNumber, float price, int quantity) {this.account = account;this.itemNumber = itemNumber;this.price = price;this.quantity = quantity;}public int getId() {return id;}public Account getAccount() {return account;}public void setAccount(Account account) {this.account = account;}public int getItemNumber() {return itemNumber;}public void setItemNumber(int itemNumber) {this.itemNumber = itemNumber;}public int getQuantity() {return quantity;}public void setQuantity(int quantity) {this.quantity = quantity;}public float getPrice() {return price;}public void setPrice(float price) {this.price = price;}}

d.建立多对多的表结构:就是在2个单表(Post,User)的基础上在新建立一个索引表(UserPost)，索引表持有这2各表的id字段。

/** * Post to some blog with String content. */public class Post {// we use this field-name so we can query for posts with a certain idpublic final static String ID_FIELD_NAME = "id";// this id is generated by the database and set on the object when it is passed to the create method@DatabaseField(generatedId = true, columnName = ID_FIELD_NAME)int id;// contents of the post@DatabaseFieldString contents;Post() {// for ormlite}public Post(String contents) {this.contents = contents;}} /** * A user object with a name. */public class User {// we use this field-name so we can query for users with a certain idpublic final static String ID_FIELD_NAME = "id";// this id is generated by the database and set on the object when it is passed to the create method@DatabaseField(generatedId = true, columnName = ID_FIELD_NAME)int id;@DatabaseFieldString name;User() {// for ormlite}public User(String name) {this.name = name;}}/** * Join table which links users to their posts. *  *  * For more information about foreign objects, see the online docs * 
 */public class UserPost {public final static String USER_ID_FIELD_NAME = "user_id";public final static String POST_ID_FIELD_NAME = "post_id";/** * This id is generated by the database and set on the object when it is passed to the create method. An id is * needed in case we need to update or delete this object in the future. */@DatabaseField(generatedId = true)int id;// This is a foreign object which just stores the id from the User object in this table.@DatabaseField(foreign = true, columnName = USER_ID_FIELD_NAME)User user;// This is a foreign object which just stores the id from the Post object in this table.@DatabaseField(foreign = true, columnName = POST_ID_FIELD_NAME)Post post;UserPost() {// for ormlite}public UserPost(User user, Post post) {this.user = user;this.post = post;}}

5.完成上面的步骤后，就可以在activity中获取到一张表的dao对象，来对表进行增删改查了。

下面列出些基本的dao操作,至于上面的几种表间关系，框架已经帮我们维护了，这几种关系的表间操作也是与单表是一样的。下面列出对单个表的操作方法，其中操作方法可以仔细看api提示。

定义表对应的dao:

//单表Account对应Daopublic class AccountDao extends AbstractDao {public AccountDao(Context context, Class clazz) {super(context, clazz);}}

获取dao:

private void getDaos() {accountDao = new AccountDao(this, Account.class);}

增加：

private void add() {String name = Utils.getRandomString(5);String password = Utils.getRandomString(5);Account t = new Account(name, password);accountDao.add(t);}

删除：

private void delete() {accountDao.deleteAll("accounts");}

查询：

private List searchAll() {List list = accountDao.getAll();tv.setText(list == null ? "" : list.toString());return list;}

修改：

private void update() {List list = searchAll();if(list!=null && list.size()>0){Account endAccount = list.get(list.size()-1);endAccount.setPassword("mode_"+Utils.getRandomString(5));accountDao.update(endAccount);}}

释放资源：

@Overrideprotected void onDestroy() {super.onDestroy();DataBaseHelper.releaseHelper();}

到这里基本就结束了。具体详细了解可以自己去开源项目doc：点我

里面有关于如何在android项目中使用，在android项目中使用需要用到2个jar:

ormlite-android-4.41.jar
ormlite-core-4.41.jar