2018-01-02
原CSDN博客已弃用，文章会逐渐迁移过来。
一、运行前配置
在http://ormlite.com/releases/下载两个jar包
打开AndroidStudio，选择
ctrl+v将jar包复制进libs文件夹下
点击ctrl键，鼠标左键选择两个jar包后，鼠标右键，选择
二、编写实体类
Student.java
package com.administrator.ormlitedemo;
import com.j256.ormlite.field.DataType;
import com.j256.ormlite.field.DatabaseField;
import com.j256.ormlite.table.DatabaseTable;
/**
* Created by Administrator on 2018/5/16.
*/
@DatabaseTable(tableName = "tb_student")//tableName可以不写，不写默认表名是类名即Student，写了是改写表名
public class Student {
@DatabaseField(generatedId = true)//字段 generatedId = true自动增长标识列
private int id;
@DatabaseField(columnName = "name",dataType = DataType.STRING,canBeNull = false)//列名 canBeNull = false不能为空
private String name;
@DatabaseField //使用默认属性
private int age;
@DatabaseField
private String phone;
public Student() {
}
public Student(String name, int age, String phone) {
this.name = name;
this.age = age;
this.phone = phone;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
@Override
public String toString() {
return "Student{" +
"id=" + id +
", name='" + name + '\'' +
", age=" + age +
", phone='" + phone + '\'' +
'}';
}
}
三、实现数据库帮助类
DatabaseHelper.java
package com.administrator.ormlitedemo;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import com.j256.ormlite.android.apptools.OrmLiteSqliteOpenHelper;
import com.j256.ormlite.support.ConnectionSource;
import com.j256.ormlite.table.TableUtils;
import java.sql.SQLException;
/**
* Created by Administrator on 2018/5/16.
*/
public class DatabaseHelper extends OrmLiteSqliteOpenHelper {
private DatabaseHelper(Context context){
super(context,"test.db",null,1);//当版本值变化时， onUpgrade方法会被调用
}
private static DatabaseHelper sHelper = null;
public static synchronized DatabaseHelper getInstance(Context context){
if (sHelper == null){
sHelper = new DatabaseHelper(context);
}
return sHelper;
}
@Override
public void onCreate(SQLiteDatabase sqLiteDatabase, ConnectionSource connectionSource) {
try {
/**
* 建表
*/
TableUtils.clearTable(connectionSource,Student.class);// connectionSource连接源，传入的实体类建表
} catch (SQLException e) {
e.printStackTrace();
}
}
@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, ConnectionSource connectionSource, int i, int i1) {
try {
TableUtils.dropTable(connectionSource,Student.class,true);//参数3：是否忽略错误
} catch (SQLException e) {
e.printStackTrace();
}
}
}
四、增删改查测试
建立单元测试用例类
OrmLiteTest.java
package com.administrator.ormlitedemo;
import android.test.InstrumentationTestCase;
import android.util.Log;
import com.j256.ormlite.dao.Dao;
import com.j256.ormlite.stmt.UpdateBuilder;
import java.sql.SQLException;
import java.util.List;
/**
* Created by Administrator on 2018/5/16.
*/
public class OrmLiteTest extends InstrumentationTestCase {
public DatabaseHelper getHelper(){
return DatabaseHelper.getInstance(this.getInstrumentation().getTargetContext());//this.getInstrumentation().getTargetContext()获取测试对象的上下文
}
public Dao<Student,Integer> getStudentDao() throws SQLException {//参数1：对象类型，参数2：ID类型
return getHelper().getDao(Student.class);//创建学生表的Dao数据访问对象
}
/**
* 添加
* @throws SQLException
*/
public void testInsert() throws SQLException {
Dao<Student,Integer> stuDao = getStudentDao();
Student stu1 = new Student("测试1",21,"8888888888");
Student stu2 = new Student("测试2",22,"1111111111");
Student stu3 = new Student("测试3",23,"2222222222");
stuDao.create(stu1);
stuDao.create(stu2);
stuDao.create(stu3);
// stuDao.createOrUpdate(stu1);//创建或更新
}
/**
* 查询
*/
public void testQuery() throws SQLException {
Dao<Student,Integer> stuDao = getStudentDao();
List<Student> students = stuDao.queryForAll();
for (Student stu:students){
Log.i("test",stu.toString());
}
// Student stu1 = stuDao.queryForId(3);//根据ID查询
// Log.i("test","ForId"+stu1.toString());
//
// List<Student> students1 = stuDao.queryForEq("name","测试2");
// for(Student stu : students1){
// Log.i("test","stu1:"+stu.toString());
// }
}
/**
* 更新
*/
public void testUpdate() throws SQLException {
Dao<Student,Integer> stuDao = getStudentDao();
UpdateBuilder update = stuDao.updateBuilder();
update.setWhere(update.where().eq("phone","8888888888").and().gt("age",22));//大于多少gt，小于多少lt
update.updateColumnValue("name","测试更新");
update.updateColumnValue("phone","100");
update.update();
// stuDao.updateRaw("update tb_student set name='测试更新2',phone = '119' where id=?","1");
}
/**
* 删除
*/
public void testDelete() throws SQLException {
Dao<Student,Integer> stuDao = getStudentDao();
stuDao.deleteById(1);//删除Id为1的元素
}
}
四、一对多的关系
(一)、DatabaseHelper.java
package com.administrator.ormlitedemo;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import com.j256.ormlite.android.apptools.OrmLiteSqliteOpenHelper;
import com.j256.ormlite.support.ConnectionSource;
import com.j256.ormlite.table.TableUtils;
import java.sql.SQLException;
/**
* Created by Administrator on 2018/5/16.
*/
public class DatabaseHelper extends OrmLiteSqliteOpenHelper {
private DatabaseHelper(Context context){
super(context,"test.db",null,5);//当版本值变化时， onUpgrade方法会被调用
}
private static DatabaseHelper sHelper = null;
public static synchronized DatabaseHelper getInstance(Context context){
if (sHelper == null){
sHelper = new DatabaseHelper(context);
}
return sHelper;
}
@Override
public void onCreate(SQLiteDatabase sqLiteDatabase, ConnectionSource connectionSource) {
try {
/**
* 建表
*/
TableUtils.clearTable(connectionSource,Student.class);// connectionSource连接源，传入的实体类建表
TableUtils.clearTable(connectionSource,School.class);
} catch (SQLException e) {
e.printStackTrace();
}
}
@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, ConnectionSource connectionSource, int i, int i1) {
try {
TableUtils.dropTable(connectionSource,Student.class,true);//参数3：是否忽略错误
TableUtils.dropTable(connectionSource,School.class,true);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
(二)、School.java
package com.administrator.ormlitedemo;
import com.j256.ormlite.field.DataType;
import com.j256.ormlite.field.DatabaseField;
import com.j256.ormlite.field.ForeignCollectionField;
import com.j256.ormlite.table.DatabaseTable;
import java.util.Collection;
/**
* Created by Administrator on 2018/5/16.
*/
@DatabaseTable(tableName = "tb_school")
public class School {
@DatabaseField(generatedId = true)
private int id;
@DatabaseField
private String name;
@DatabaseField
private String iocation;
@ForeignCollectionField//外键集合
private Collection<Student> students;
public School(String name, String iocation) {
this.name = name;
this.iocation = iocation;
}
public School() {}//习惯性增加无参构造方法，不然可能有错
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getIocation() {
return iocation;
}
public void setIocation(String iocation) {
this.iocation = iocation;
}
public Collection<Student> getStudents() {
return students;
}
public void setStudents(Collection<Student> students) {
this.students = students;
}
@Override
public String toString() {
return "School{" +
"id=" + id +
", name='" + name + '\'' +
", iocation='" + iocation + '\'' +
", students=" + students +
'}';
}
}
(三)、Student.java
package com.administrator.ormlitedemo;
import com.j256.ormlite.field.DataType;
import com.j256.ormlite.field.DatabaseField;
import com.j256.ormlite.table.DatabaseTable;
/**
* Created by Administrator on 2018/5/16.
*/
@DatabaseTable(tableName = "tb_student")//tableName可以不写，不写默认表名是类名即Student，写了是改写表名
public class Student {
@DatabaseField(generatedId = true)//字段 generatedId = true自动增长标识列
private int id;
@DatabaseField(columnName = "name",dataType = DataType.STRING,canBeNull = false)//列名 canBeNull = false不能为空
private String name;
@DatabaseField //使用默认属性
private int age;
@DatabaseField
private String phone;
@DatabaseField(columnName = "school_id",foreign = true,foreignAutoRefresh = true)//foreign = true外键 foreignAutoRefresh刷新查询学生时顺带把学校信息填充
private School school;
public Student() {//习惯性增加无参构造方法，不然可能有错
}
public Student(String name, int age, String phone ,School school) {
this.name = name;
this.age = age;
this.phone = phone;
this.school = school;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public School getSchool() {
return school;
}
public void setSchool(School school) {
this.school = school;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
@Override
public String toString() {
return "Student{" +
"id=" + id +
", name='" + name + '\'' +
", age=" + age +
", phone='" + phone + '\'' +
'}';
}
}
(四)、OrmLiteTest.java
package com.administrator.ormlitedemo;
import android.test.InstrumentationTestCase;
import android.util.Log;
import com.j256.ormlite.dao.Dao;
import com.j256.ormlite.stmt.UpdateBuilder;
import java.sql.SQLException;
import java.util.List;
/**
* Created by Administrator on 2018/5/16.
*/
public class OrmLiteTest extends InstrumentationTestCase {
public DatabaseHelper getHelper(){
return DatabaseHelper.getInstance(this.getInstrumentation().getTargetContext());//this.getInstrumentation().getTargetContext()获取测试对象的上下文
}
public Dao<Student,Integer> getStudentDao() throws SQLException {//参数1：对象类型，参数2：ID类型
return getHelper().getDao(Student.class);//创建学生表的Dao数据访问对象
}
/**
* 添加
* @throws SQLException
*/
public void testInsert() throws SQLException {
Dao<Student,Integer> stuDao = getStudentDao();
Dao<School,Integer> schDao = getShoolDao();
School school = new School("北京大学","北京");
Student stu1 = new Student("测试1",21,"8888888888",school);
Student stu2 = new Student("测试2",22,"1111111111",school);
Student stu3 = new Student("测试3",23,"2222222222",school);
schDao.create(school);
stuDao.create(stu1);
stuDao.create(stu2);
stuDao.create(stu3);
// stuDao.createOrUpdate(stu1);//创建或更新
}
public Dao<School,Integer> getShoolDao() throws SQLException {
return getHelper().getDao(School.class);
}
/**
* 查询
*/
public void testQuery() throws SQLException {
Dao<Student,Integer> stuDao = getStudentDao();
List<Student> students = stuDao.queryForAll();
for (Student stu:students){
Log.i("test",stu.toString()+stu.getSchool());
}
Dao<School,Integer> schDao = getShoolDao();
List<School> schools = schDao.queryForAll();
for (School s:schools){
Log.i("test",s.toString());
for(Student stu:s.getStudents()){
Log.i("test","school stu:"+stu);
}
}
// Student stu1 = stuDao.queryForId(3);//根据ID查询
// Log.i("test","ForId"+stu1.toString());
//
// List<Student> students1 = stuDao.queryForEq("name","测试2");
// for(Student stu : students1){
// Log.i("test","stu1:"+stu.toString());
// }
}
/**
* 更新
*/
public void testUpdate() throws SQLException {
Dao<Student,Integer> stuDao = getStudentDao();
UpdateBuilder update = stuDao.updateBuilder();
update.setWhere(update.where().eq("phone","8888888888").and().gt("age",22));//大于多少gt，小于多少lt
update.updateColumnValue("name","测试更新");
update.updateColumnValue("phone","100");
update.update();
// stuDao.updateRaw("update tb_student set name='测试更新2',phone = '119' where id=?","1");
}
/**
* 删除
*/
public void testDelete() throws SQLException {
Dao<Student,Integer> stuDao = getStudentDao();
stuDao.deleteById(1);//删除Id为1的元素
}
}
五、事务操作，批量操作
/**
* 事务操作、批量操作
* 保证事务的整体性，一旦出现异常，所有语句都不成功，只有全部都成功，才成功
* @throws SQLException
*/
public void testTransaction() throws SQLException {
final Dao<Student,Integer> stuDao = getStudentDao();
final Student stu = new Student("测试事务",20,"100",new School("清华大学","北京"));
TransactionManager.callInTransaction(getHelper().getConnectionSource(),
new Callable<Void>() {
@Override
public Void call() throws Exception {
for(int i = 0 ;i<20;i++){
stuDao.create(stu);
// if(i==10){
// throw new SQLException("test....");
// }
}
return null;
}
});
}