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);
}
@Override
public void onCreate(SQLiteDatabase arg0, ConnectionSource arg1) {
createTable(arg1);
}
@Override
public void onUpgrade(SQLiteDatabase arg0, ConnectionSource arg1, int arg2,
int arg3) {
dropTable(arg1);
onCreate(arg0, arg1);
}
@Override
public 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
@DatabaseField
private int itemNumber;
@DatabaseField
private int quantity;
@DatabaseField
private 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 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;
@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;
}
@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);
}
}
/**
* 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;
@DatabaseField
private int itemNumber;
@DatabaseField
private int quantity;
@DatabaseField
private 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 id
public 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
@DatabaseField
String 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 id
public 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;
@DatabaseField
String 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對應Dao
public 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);
}
}
釋放資源：
@Override
protected void onDestroy() {
super.onDestroy();
DataBaseHelper.releaseHelper();
}
到這裡基本就結束了。具體詳細了解可以自己去開源項目doc：點我
裡面有關於如何在android項目中使用，在android項目中使用需要用到2個jar:
ormlite-android-4.41.jar
ormlite-core-4.41.jar
