android框架搭建——封装一个属于自己的数据存储工具类（SQLite篇）

当我们缓存本地的数据存在一对多（一个name对应多个values）的情况时，sharedPreferences已经无法胜任存储的工作了。此时我们需要使用SQLite来进行本地缓存。
直接使用SQlite进行存储时，每次执行插入，查询，删除操作都要写一大堆代码，很麻烦，所以我们对SQLite进行一下简单的封装，下面给出例子：
在这个例子中，我们要存储同学的姓名，年龄，指导老师，家庭地址，手机号等数据。

步骤一：创建SQLiteOpenHelper类，用于建表操作：

DBHelper.java:

public class DBHelper extends SQLiteOpenHelper {
    private static final String DB_NAME = "student.db";
    private static final int version = 1;
    private static final String SQL_CREATE = "create table student_info(_id integer primary key autoincrement,student_id integer,name text,age integer,teacher text,address text,phone long)";
    private static final String SQL_DROP = "drop table if exists student_info";

    public DBHelper(Context context) {
        super(context, DB_NAME, null, version);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(SQL_CREATE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL(SQL_DROP);
        db.execSQL(SQL_CREATE);
    }
}

步骤二：写一个学生信息的bean：

studentInfo.java:

public class SudentInfo {
	private int id;
	private String name;
	private int age;
	private String teacher;
	private String address;
	private long 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 getTeacher() {
		return teacher;
	}

	public void setTeacher(String teacher) {
		this.teacher = teacher;
	}

	public String getAddress() {
		return address;
	}

	public void setAddress(String address) {
		this.address = address;
	}

	public long getPhone() {
		return phone;
	}

	public void setPhone(long phone) {
		this.phone = phone;
	}

}

步骤三：写一个增删改查的接口：

StudentDAO.java:

/**数据访问接口
 * Created by 95 on 2015/10/21.
 */
public interface StudentDAO {
    /**
     * 插入学生信息
     */
public void insertStudent(StudentInfo studentInfo);

    /**
     * 删除学生
     */
    public void deleteStudent(String name);

    /**
     * 更新学生
     */
    public void updateThread(String name);

    /**
     * 查询学生信息(同一老师对应多个学生)
     */
    public List<StudentInfo> getStudents(String teacher);
    /*
     * 查询学生信息(一个名字对应一个学生)
     */
    public StudentInfo getStudent(String name);

    /**\
     * 学生信息是否存在
     * @return
     */
    public boolean isExists(String name);
}

步骤四：写一个实现增删改查接口的类：

/**
 * 数据访问接口实现
 * Created by 95 on 2015/10/21.
 */

public class StudentDAOImpl implements StudentDAO {
    private DBHelper mHelpter = null;

    public StudentDAOImpl(Context context) {
        mHelpter = new DBHelper(context);
    }

    @Override
    public void insertStudent(StudentInfo studentInfo) {
        SQLiteDatabase db = mHelpter.getWritableDatabase();
        db.execSQL("insert into student_info(student_id,name,age,teacher,adderess,phone) values(?,?,?,?,?,?)",
                new Object[]{studentInfo.getId(), studentInfo.getName(), studentInfo.getAge(), studentInfo.getTeacher(), studentInfo.getAddress(),studentInfo.getPhone()});
        db.close();
    }

    @Override
    public void deleteStudent(String name) {
        SQLiteDatabase db = mHelpter.getWritableDatabase();
        db.execSQL("delete from student_info where name = ? ",
                new Object[]{name});
        db.close();
    }

    @Override
    public void updateStudent(String name,int age,String teacher,String address,long phone) {
        SQLiteDatabase db = mHelpter.getWritableDatabase();
        db.execSQL("update student_info set age = ?,teacher = ?,adderess = ?,phone = ? where name = ?",
                new Object[]{age,teacher,address,phone,name});
        db.close();
    }

    @Override
    public List<StudentInfo> getStudents(String teacher) {
        SQLiteDatabase db = mHelpter.getWritableDatabase();
        List<StudentInfo> list = new ArrayList<StudentInfo>();
        Cursor c = db.rawQuery("select * from student_info where teacher = ?", new String[]{teacher});
        while (c.moveToNext()) {
        	StudentInfo student = new StudentInfo();
        	student.setId(c.getInt(c.getColumnIndex("student_id")));
        	student.setName(c.getString(c.getColumnIndex("name")));
        	student.setAge(c.getInt(c.getColumnIndex("age")));
        	student.setAddress(c.getString(c.getColumnIndex("address")));
        	student.setPhone(c.getLong(c.getColumnIndex("phone")));
            list.add(student);
        }
        c.close();
        db.close();

        return list;
    }
    @Override
    public StudentInfo getStudent(String name) {
        SQLiteDatabase db = mHelpter.getWritableDatabase();
        StudentInfo student = new StudentInfo();
        Cursor c = db.rawQuery("select * from student_info where name = ?", new String[]{name});
        while (c.moveToNext()) {
        	student.setId(c.getInt(c.getColumnIndex("student_id")));
        	student.setAge(c.getInt(c.getColumnIndex("age")));
        	student.setAddress(c.getString(c.getColumnIndex("address")));
        	student.setTeacher(c.getString(c.getColumnIndex("teacher")));
        	student.setPhone(c.getLong(c.getColumnIndex("phone")));
        }
        c.close();
        db.close();
        return student;
    }

    @Override
    public boolean isExists(String name) {
        SQLiteDatabase db = mHelpter.getWritableDatabase();
        List<StudentInfo> list = new ArrayList<StudentInfo>();
        Cursor c = db.rawQuery("select * from student_info where name = ? ", new String[]{name});
        boolean isExists = c.moveToNext();
        c.close();
        db.close();
        return isExists;
    }
}

ok,在使用SQLite进行学生数据的本地缓存的时候，只要用几行代码就可以完成增删改查操作了，封装完成！