（原创）OrnLite数据库缓存的介绍以及使用
本文主要简单介绍下OrmLite一对一以及一对多数据库缓存的实现方式。一对多具有两种查询和关联方式。
1.首先需要到网上下载OrmLite相应的jar包，这个度娘一堆，官网下载链接如下,文章最后附带项目下载链接具有相应jar包，需要导入ormlite-android-4.49-SNAPSHOT.jar和ormlite-core-4.49-SNAPSHOT.jar这两个包。
2.对于OrmLite的一些注解的介绍：
ORMLite为我们提供了全面的字段属性的支持，下面我们来具体看一下吧：
- cloumnName:指定字段名,不指定则变量名作为字段名
- canBeNull:是否可以为null
- dataType:指定字段的类型
- foreign 指定这个字段的对象是一个外键,外键值是这个对象的id
- foreignAutoCreate 外键不存在时是否自动添加到外间表中
- foreignAutoRefresh 外键值,自动刷新
- foreignColumnName外键字段指定的外键表中的哪个字段
- generatedId:指定字段为自增长的id,不能id,generatedIdSequence通用
- id:指定字段为id
- index:索引
- persisted:指定是否持久化此变量,默认true
- throwIfNull,如果空值抛出异常
- useGetSet:指定ormlite访问变量使用set,get方法默认使用的是反射机制直接访问变量
- unique:字段值唯一
- uniqueIndex 唯一索引
- uniqueCombo整列的值唯一
3.OrmLite的使用，数据库表的创建。
与Android中的数据库创建相似，使用OrmLite创建数据库需要我们创建一个SqlOpenHelper继承OrmLiteSqliteOpenHelper，在OrmLiteSqliteOpenHelper也有两个重要方法，分别是onCreate和onUpgrade，负责数据库创建以及升级时的操作。
4.以下是个人针对一对多及三级关联表增删查改操作demo:（一边工作一边编写花了些时日，希望对你们有所帮助）
效果图：
(1).MainActivity
<pre name="code" class="java">package com.example.lainanzhou.ormlitedemo;
import android.app.Activity;
import android.os.Bundle;
import android.view.View;
import android.widget.Toast;
import com.example.lainanzhou.ormlitedemo.bean.Article;
import com.example.lainanzhou.ormlitedemo.bean.Author;
import com.example.lainanzhou.ormlitedemo.bean.Introduce;
import com.example.lainanzhou.ormlitedemo.db.dao.ArticleDao;
import com.example.lainanzhou.ormlitedemo.db.dao.AuthorDao;
import com.example.lainanzhou.ormlitedemo.db.dao.IntroduceDao;
import java.util.Collection;
import java.util.List;
/**
* TODO:
* ORMLite一对多及多级关联外键的CRUD增删查改的Demo
*
* @author zhou
*/
public class MainActivity extends Activity implements View.OnClickListener {
private AuthorDao authorDao;
private ArticleDao articleDao;
private int mAuthor1Count, mAuthor2Count;
private Author mAuthor1;
private Author mAuthor2;
private IntroduceDao introduceDao;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
if (authorDao == null)
authorDao = new AuthorDao(this);
if (articleDao == null)
articleDao = new ArticleDao(this);
if (introduceDao == null)
introduceDao = new IntroduceDao(this);
initEvent();
}
private void initEvent() {
findViewById(R.id.btn_addAuthor1).setOnClickListener(this);
findViewById(R.id.btn_addAuthor2).setOnClickListener(this);
findViewById(R.id.btn_addArticle1).setOnClickListener(this);
findViewById(R.id.btn_addArticle2).setOnClickListener(this);
findViewById(R.id.btn_deleteAuthor1).setOnClickListener(this);
findViewById(R.id.btn_deleteAuthor2).setOnClickListener(this);
findViewById(R.id.btn_queryAuthor1).setOnClickListener(this);
findViewById(R.id.btn_queryAuthor2).setOnClickListener(this);
findViewById(R.id.btn_updateAuthor1).setOnClickListener(this);
findViewById(R.id.btn_updateAuthor2).setOnClickListener(this);
}
@Override
public void onClick(View v) {
switch (v.getId()) {
case R.id.btn_addAuthor1://添加作者1
if (authorDao.queryAllAuthor() == null || (mAuthor1 == null && authorDao.queryAllAuthor().size() < 1)) {
mAuthor1 = new Author();
mAuthor1.setUserName("作者1");
authorDao.add(mAuthor1);
System.out.println("添加作者1");
} else
mAuthor1 = authorDao.queryAllAuthor().get(0);
break;
case R.id.btn_addAuthor2://添加作者2
if (mAuthor1 == null) {
Toast.makeText(this, "请先添加作者1", Toast.LENGTH_SHORT).show();
return;
}
if (authorDao.queryAllAuthor() == null || (mAuthor2 == null && authorDao.queryAllAuthor().size() < 2)) {
mAuthor2 = new Author();
mAuthor2.setUserName("作者2");
authorDao.add(mAuthor2);
System.out.println("添加作者2");
} else
mAuthor2 = authorDao.queryAllAuthor().get(1);
break;
case R.id.btn_addArticle1://添加作者1文章---注意主外键的关系需要先添加主键才可以添加关联的外键成功
if (mAuthor1 == null) {
Toast.makeText(this, "请先添加作者1", Toast.LENGTH_SHORT).show();
return;
}
mAuthor1Count++;
Article article1 = new Article();
article1.setAuthor(mAuthor1);//设置主键关联对应主键的id值
article1.setTitle("文章" + mAuthor1Count);
article1.setDoc("作者1文章描述" + mAuthor1Count);
articleDao.add(article1);
System.out.println("添加作者1：" + article1.toString());
//添加文章简介
Introduce introduce1 = new Introduce();
introduce1.setArticle(article1);
introduce1.setIntroduce("作者1文章简介" + mAuthor1Count);
introduceDao.add(introduce1);
System.out.println("添加作者1：" + introduce1.toString());
break;
case R.id.btn_addArticle2://添加作者2文章---注意主外键的关系需要先添加主键才可以添加关联的外键成功
if (mAuthor2 == null) {
Toast.makeText(this, "请先添加作者2", Toast.LENGTH_SHORT).show();
return;
}
mAuthor2Count++;
Article article2 = new Article();
article2.setAuthor(mAuthor2);//设置主键关联对应主键的id值
article2.setTitle("文章" + mAuthor2Count);
article2.setDoc("作者2文章描述" + mAuthor2Count);
articleDao.add(article2);
System.out.println("添加作者2：" + article2.toString());
//添加文章简介
Introduce introduce2 = new Introduce();
introduce2.setArticle(article2);
introduce2.setIntroduce("作者2文章简介" + mAuthor2Count);
introduceDao.add(introduce2);
System.out.println("添加作者2：" + introduce2.toString());
break;
case R.id.btn_queryAuthor1://查询作者1所有文章
if (mAuthor1 == null) {
Toast.makeText(this, "请先添加作者1", Toast.LENGTH_SHORT).show();
return;
}
List<Author> authorList1 = authorDao.queryAuthorById(mAuthor1.getId());
for (Author author : authorList1) {
if (author.getArticleList() != null && author.getArticleList().size() > 0) {
for (Article article : author.getArticleList()) {
System.out.println("查询作者1所有文章:" + article.toString());
if (article.getIntroduceList() != null && article.getIntroduceList().size() > 0) {
for (Introduce introduce : article.getIntroduceList()) {
System.out.println("查询作者1所有文章简介:" + introduce.toString());
}
}
}
} else
System.out.println("查询作者1所有文章简介:" + "没有数据");
}
break;
case R.id.btn_queryAuthor2://查询作者2所有文章
if (mAuthor2 == null) {
Toast.makeText(this, "请先添加作者2", Toast.LENGTH_SHORT).show();
return;
}
List<Author> authorList2 = authorDao.queryAuthorById(mAuthor2.getId());
for (Author author : authorList2) {
if (author.getArticleList() != null && author.getArticleList().size() > 0) {
for (Article article : author.getArticleList()) {
System.out.println("查询作者2所有文章:" + article.toString());
if (article.getIntroduceList() != null && article.getIntroduceList().size() > 0) {
for (Introduce introduce : article.getIntroduceList()) {
System.out.println("查询作者2所有文章简介:" + introduce.toString());
}
}
}
} else
System.out.println("查询作者2所有文章简介:" + "没有数据");
}
break;
case R.id.btn_deleteAuthor1://删除作者1所有文章
if (mAuthor1 == null) {
Toast.makeText(this, "请先添加作者1", Toast.LENGTH_SHORT).show();
return;
}
Collection<Article> articleLists1 = mAuthor1.getArticleList();
if (articleLists1 != null && articleLists1.iterator().hasNext())
introduceDao.deleteByArticleId(articleLists1.iterator().next());
System.out.println("删除了作者1:" + articleDao.deleteArticleFromAuthor(mAuthor1) + "条文章");
break;
case R.id.btn_deleteAuthor2://删除作者2所有文章
if (mAuthor2 == null) {
Toast.makeText(this, "请先添加作者2", Toast.LENGTH_SHORT).show();
return;
}
Collection<Article> articleLists2 = mAuthor2.getArticleList();
if (articleLists2 != null && articleLists2.iterator().hasNext())
introduceDao.deleteByArticleId(articleLists2.iterator().next());
System.out.println("删除了作者2:" + articleDao.deleteArticleFromAuthor(mAuthor2) + "条文章");
break;
case R.id.btn_updateAuthor1://更新作者1第一条文章的描述和标题并更新文章的对应第一条简介内容
if (mAuthor1 == null) {
Toast.makeText(this, "请先添加作者1", Toast.LENGTH_SHORT).show();
return;
}
Collection<Article> articleList1 = authorDao.queryAuthorById(1).get(0).getArticleList();
if (articleList1 != null && articleList1.iterator().hasNext()) {
int articleId1 = articleList1.iterator().next().getId();
Article updateArticle1 = new Article();
updateArticle1.setId(articleId1);
updateArticle1.setTitle("我是更新Title");
updateArticle1.setDoc("我是更新Doc");
System.out.println("更新了" + articleDao.updateArticleFromAuthor(updateArticle1) + "条信息");
Introduce updateIntroduce1 = new Introduce();
updateIntroduce1.setArticle(articleDao.queryArticleListById(articleId1).get(0));
updateIntroduce1.setIntroduce("我是更新Introduce");
System.out.println("更新了" + introduceDao.updateByArticleId(updateIntroduce1) + "条信息");
} else
System.out.println("请先添加作者文章");
//打印结果
break;
case R.id.btn_updateAuthor2://更新作者2第一条文章的描述和标题并更新文章的对应第一条简介内容
if (mAuthor2 == null) {
Toast.makeText(this, "请先添加作者2", Toast.LENGTH_SHORT).show();
return;
}
Collection<Article> articleList2 = authorDao.queryAuthorById(2).get(0).getArticleList();
if (articleList2 != null && articleList2.iterator().hasNext()) {
int articleId2 = articleList2.iterator().next().getId();
Article updateArticle2 = new Article();
updateArticle2.setId(articleId2);
updateArticle2.setTitle("我是更新Title");
updateArticle2.setDoc("我是更新Doc");
System.out.println("更新了" + articleDao.updateArticleFromAuthor(updateArticle2) + "条信息");
Introduce updateIntroduce2 = new Introduce();
updateIntroduce2.setArticle(articleDao.queryArticleListById(articleId2).get(0));
updateIntroduce2.setIntroduce("我是更新Introduce");
System.out.println("更新了" + introduceDao.updateByArticleId(updateIntroduce2) + "条信息");
} else
System.out.println("请先添加作者文章");
break;
default:
break;
}
}
}
（2）.作者bean、文章bean、文章简介bean:
作者bean
package com.example.lainanzhou.ormlitedemo.bean;
import com.j256.ormlite.field.DatabaseField;
import com.j256.ormlite.field.ForeignCollectionField;
import com.j256.ormlite.table.DatabaseTable;
import java.util.Collection;
/**
* TODO:
* 作者列表的数据库对应的bean
* 一对多主外键具有两种实现方式：
* 方式1：在主键添加@ForeignCollectionField Collection<Article>来关联一个集合
* 方式2：在外键添加@DatabaseField(foreign = true, columnName = "author_id") Author主键对象关联
* <p/>
* 注意： OrmLite主键不需要设置List集合即可自动产生关联。
* <p/>
* Created by Joker on 2016/5/10.
*/
//创建表名为"tb_author"的表;若不指定默认为类名
@DatabaseTable(tableName = "tb_author")
public class Author {
//创建表列字节id为自增长
@DatabaseField(generatedId = true)
private int id;
//创建表列字节名为"name"；若不指定默认为参数名
@DatabaseField(columnName = "name")
private String userName;
//创建表列字节名为"sex"
@DatabaseField(columnName = "sex")
private String sex;
//方式1的一对多关联外键集合的实现
@ForeignCollectionField
private Collection<Article> articleList;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public Collection<Article> getArticleList() {
return articleList;
}
public void setArticleList(Collection<Article> articleList) {
this.articleList = articleList;
}
}
文章bean:
package com.example.lainanzhou.ormlitedemo.bean;
import com.j256.ormlite.field.DatabaseField;
import com.j256.ormlite.field.ForeignCollectionField;
import com.j256.ormlite.table.DatabaseTable;
import java.util.Collection;
/**
* TODO:
* 文章对应的数据库bean
* <p/>
* Created by Joker on 2016/5/10.
*/
//创建表名为"tb_author"的表
@DatabaseTable(tableName = "tb_article")
public class Article {
//创建表列字节id为自增长
@DatabaseField(generatedId = true)
private int id;
//创建表列字节名为"name"
@DatabaseField(columnName = "title")
private String title;
//创建表列字节名为"sex"
@DatabaseField(columnName = "doc")
private String doc;
//方式2通过外键关联主键对象
@DatabaseField(canBeNull = false, foreign = true, columnName = "author_id")
private Author author;
//方式1的一对多关联外键集合的实现
@ForeignCollectionField
private Collection<Introduce> introduceList;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getDoc() {
return doc;
}
public void setDoc(String doc) {
this.doc = doc;
}
public Author getAuthor() {
return author;
}
public void setAuthor(Author author) {
this.author = author;
}
public Collection<Introduce> getIntroduceList() {
return introduceList;
}
public void setIntroduceList(Collection<Introduce> introduceList) {
this.introduceList = introduceList;
}
@Override
public String toString() {
return "Article{" +
"id=" + id +
", title='" + title + '\'' +
", doc='" + doc + '\'' +
", author=" + author +
", introduceList=" + introduceList +
'}';
}
}
文章简介bean:
package com.example.lainanzhou.ormlitedemo.bean;
import com.j256.ormlite.field.DatabaseField;
import com.j256.ormlite.table.DatabaseTable;
/**
* TODO:
* 文章简介表
* <p>
* Created by Joker on 2016/5/13.
*/
@DatabaseTable(tableName = "tb_introduce")
public class Introduce {
//创建表列字节id为自增长
@DatabaseField(generatedId = true)
private int id;
@DatabaseField(columnName = "introduce")
private String introduce;
//外键关联主键对象id
@DatabaseField(canBeNull = false, foreign = true, columnName = "article_id")
private Article article;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getIntroduce() {
return introduce;
}
public void setIntroduce(String introduce) {
this.introduce = introduce;
}
public Article getArticle() {
return article;
}
public void setArticle(Article article) {
this.article = article;
}
@Override
public String toString() {
return "Introduce{" +
"id=" + id +
", introduce='" + introduce + '\'' +
", article=" + article +
'}';
}
}
(3).作者Dao、文章Dao、简介Dao:
package com.example.lainanzhou.ormlitedemo.db.dao;
import android.content.Context;
import com.example.lainanzhou.ormlitedemo.bean.Author;
import com.example.lainanzhou.ormlitedemo.db.MySqlOpenHelper;
import com.j256.ormlite.dao.Dao;
import java.sql.SQLException;
import java.util.List;
/**
* TODO:
* 作者处理Dao
* <p>
* Created by Joker on 2016/5/10.
*/
public class AuthorDao {
private Dao<Author, Integer> authorDaoHelper;
private MySqlOpenHelper helper;
public AuthorDao(Context context) {
helper = MySqlOpenHelper.getHelper(context);
try {
authorDaoHelper = helper.getDao(Author.class);
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 增加一个作者
*
* @param author
*/
public void add(Author author) {
try {
authorDaoHelper.create(author);
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 查询所有的作者
*/
public List<Author> queryAllAuthor() {
try {
return authorDaoHelper.queryBuilder().query();
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
/**
* 通过id查询作者
*/
public List<Author> queryAuthorById(int id) {
try {
return authorDaoHelper.queryBuilder().where().eq("id", id).query();
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
}
文章Dao:
package com.example.lainanzhou.ormlitedemo.db.dao;
import android.content.Context;
import com.example.lainanzhou.ormlitedemo.bean.Article;
import com.example.lainanzhou.ormlitedemo.bean.Author;
import com.example.lainanzhou.ormlitedemo.db.MySqlOpenHelper;
import com.j256.ormlite.dao.Dao;
import com.j256.ormlite.stmt.DeleteBuilder;
import com.j256.ormlite.stmt.UpdateBuilder;
import java.sql.SQLException;
import java.util.List;
/**
* TODO:
* 文章处理Dao
* <p/>
* Created by Joker on 2016/5/10.
*/
public class ArticleDao {
private Dao<Article, Integer> articleDaoHelper;
private MySqlOpenHelper helper;
public ArticleDao(Context context) {
helper = MySqlOpenHelper.getHelper(context);
try {
articleDaoHelper = helper.getDao(Article.class);
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 增加一篇文章
*
* @param aticle
*/
public void add(Article aticle) {
try {
articleDaoHelper.create(aticle);
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 直接查询所有的文章
*
* @return
*/
public List<Article> getAllArticle() {
try {
return articleDaoHelper.queryBuilder().query();
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
/**
* 通过id获取某个作者的所有的文章
*
* @param article_id
* @return
*/
public List<Article> queryArticleListById(int article_id) {
try {
return articleDaoHelper.queryBuilder().where().eq("id", article_id)
.query();
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
/**
* 通过Author对象ID删除相应的作者所有文章
*
* @param author
*/
public int deleteArticleFromAuthor(Author author) {
try {
DeleteBuilder<Article, Integer> deleteBuilder = articleDaoHelper.deleteBuilder();
deleteBuilder.setWhere(deleteBuilder.where().eq("author_id", author.getId()));
return deleteBuilder.delete();
} catch (SQLException e) {
e.printStackTrace();
}
return -1;
}
/**
* 通过Author对象ID获取相应的作者所有文章
*
* @param author
*/
public List<Article> getListByAuthor(Author author) {
try {
return articleDaoHelper.queryBuilder().where().eq("author_id", author.getId()).query();
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
/**
* 根据某个文章Id更新对应的某条文章
*/
public int updateArticleFromAuthor(Article article) {
try {
UpdateBuilder<Article, Integer> updateBuilder = articleDaoHelper.updateBuilder();
updateBuilder.setWhere(updateBuilder.where().eq("id", article.getId()));
updateBuilder.updateColumnValue("title", article.getTitle());
updateBuilder.updateColumnValue("doc", article.getDoc());
return updateBuilder.update();
} catch (SQLException e) {
e.printStackTrace();
}
return -1;
}
}
简介Dao：
package com.example.lainanzhou.ormlitedemo.db.dao;
import android.content.Context;
import com.example.lainanzhou.ormlitedemo.bean.Article;
import com.example.lainanzhou.ormlitedemo.bean.Introduce;
import com.example.lainanzhou.ormlitedemo.db.MySqlOpenHelper;
import com.j256.ormlite.dao.Dao;
import com.j256.ormlite.stmt.DeleteBuilder;
import com.j256.ormlite.stmt.UpdateBuilder;
import com.j256.ormlite.stmt.Where;
import java.sql.SQLException;
import java.util.List;
/**
* TODO:
* 文章简介的Dao
* <p>
* Created by Joker on 2016/5/13.
*/
public class IntroduceDao {
private Dao<Introduce, Integer> introduceDaoHelper;
private MySqlOpenHelper helper;
public IntroduceDao(Context context) {
helper = MySqlOpenHelper.getHelper(context);
try {
introduceDaoHelper = helper.getDao(Introduce.class);
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 增加一篇文章简介
*
* @param introduce
*/
public void add(Introduce introduce) {
try {
introduceDaoHelper.create(introduce);
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 直接查询所有的文章的简介
*
* @return
*/
public List<Introduce> getAllIntroduce() {
try {
return introduceDaoHelper.queryBuilder().query();
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
/**
* 通过author_id获取某个作者的所有的文章简介
*
* @param article_id
* @return
*/
public List<Introduce> getListByUserId(Article article_id) {
try {
return introduceDaoHelper.queryBuilder().where().eq("article_id", article_id.getId())
.query();
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
/**
* 通过author_id获取某个作者的所有的文章简介
*
* @param article
* @return -1为删除失败，其他为删除成功条目
*/
public int deleteByArticleId(Article article) {
try {
DeleteBuilder<Introduce, Integer> deleteBuilder = introduceDaoHelper.deleteBuilder();
Where<Introduce, Integer> where = deleteBuilder.where().eq("article_id", article.getId());
deleteBuilder.setWhere(where);
return deleteBuilder.delete();
} catch (SQLException e) {
e.printStackTrace();
}
return -1;
}
/**
* 针对某个id文章对应的简介更新某个文章简介
*
* @param introduce
*/
public int updateByArticleId(Introduce introduce) {
try {
UpdateBuilder<Introduce, Integer> updateBuilder = introduceDaoHelper.updateBuilder();
Where<Introduce, Integer> where = updateBuilder.where().eq("article_id", introduce.getArticle().getId());
updateBuilder.setWhere(where);
updateBuilder.updateColumnValue("introduce", introduce.getIntroduce());
return updateBuilder.update();
} catch (SQLException e) {
e.printStackTrace();
}
return -1;
}
}
743
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
