Android开发之数据库备份

用过市面上刷题App/诸如驾考宝典这样的App/的用户应该知道，离线状态也是可以刷题的，这就表明了题库并不是在服务器或者云端数据库上，而是用本地的SQLite数据库存储的。可是如果别人给你的题库是个Excel表格，让你去开发成个App，该如何去做呢？接下来就以我曾经做过的C语言刷题App为例手把手教你如何读取Excel表格，并备份数据库。

1 自定义实体类
这个就好理解了，咱这回存的是题目，就自定义一个保存题目信息的实体类，具体里面存放什么信息因需求而定。

public class Question {private int questionId;private String questionContent;private String answerA;private String answerB;private String answerC;private String answerD;private int answerNum;private String coment;private String answerE;private String answerF;private int answerCorrect;private int answerChoose;private int haveFinished;private int isTrue;private int chapter;public Question(){}public int getQuestionId() {return questionId;}public void setQuestionId(int questionId) {this.questionId = questionId;}public String getQuestionContent() {return questionContent;}public void setQuestionContent(String questionContent) {this.questionContent = questionContent;}public String getAnswerA() {return answerA;}public void setAnswerA(String answerA) {this.answerA = answerA;}public String getAnswerB() {return answerB;}public void setAnswerB(String answerB) {this.answerB = answerB;}public String getAnswerC() {return answerC;}public void setAnswerC(String answerC) {this.answerC = answerC;}public String getAnswerD() {return answerD;}public void setAnswerD(String answerD) {this.answerD = answerD;}public int getAnswerNum() {return answerNum;}public void setAnswerNum(int answerNum) {this.answerNum = answerNum;}public String getComent() {return coment;}public void setComent(String coment) {this.coment = coment;}public String getAnswerE() {return answerE;}public void setAnswerE(String answerE) {this.answerE = answerE;}public String getAnswerF() {return answerF;}public void setAnswerF(String answerF) {this.answerF = answerF;}public int getAnswerCorrect() {return answerCorrect;}public void setAnswerCorrect(int answerCorrect) {this.answerCorrect = answerCorrect;}public int getAnswerChoose() {return answerChoose;}public void setAnswerChoose(int answerChoose) {this.answerChoose = answerChoose;}public int getHaveFinished() {return haveFinished;}public void setHaveFinished(int haveFinished) {this.haveFinished = haveFinished;}public int getIsTrue() {return isTrue;}public void setIsTrue(int isTrue) {this.isTrue = isTrue;}public int getChapter() {return chapter;}public void setChapter(int chapter) {this.chapter = chapter;}}

2 封装对SQLite数据库操作的工具类

public class questionOpenHelper extends SQLiteOpenHelper{/** Sqlite数据库* 包括数据库的创建、备份* */public static final String CREATE_QUESTION="create table Question (" +"questionId integer primary key autoincrement," +"questionContent text," +"answerA text," +"answerB text," +"answerC text," +"answerD text," +"answerE text," +"answerF text," +"coment text," +"answerCorrect integer," +"answerChoose integer," +"answerNum integer," +"haveFinished integer," +"isTrue integer," +"chapter integer)";private Context context;public questionOpenHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {super(context, name, factory, version);this.context=context;}@Overridepublic void onCreate(SQLiteDatabase db) {db.execSQL(CREATE_QUESTION);}@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {}}

public class QuestionDB {private static final String DB_NAME="English";private static final int VERSION=1;private static QuestionDB questionDB;private SQLiteDatabase db;private QuestionDB(Context context) {questionOpenHelper helper = new questionOpenHelper(context, DB_NAME, null, VERSION);db = helper.getWritableDatabase();}public synchronized static QuestionDB getInstance(Context context){if(questionDB==null){questionDB=new QuestionDB(context);}return questionDB;}public void saveQuestion(Question question){if(question!=null){ContentValues values=new ContentValues();values.put("questionContent",question.getQuestionContent());values.put("answerA",question.getAnswerA());values.put("answerB",question.getAnswerB());values.put("answerC",question.getAnswerC());values.put("answerD",question.getAnswerD());values.put("answerNum",question.getAnswerNum());values.put("answerE",question.getAnswerE());values.put("answerF",question.getAnswerF());values.put("answerChoose",question.getAnswerChoose());values.put("answerCorrect",question.getAnswerCorrect());values.put("coment",question.getComent());values.put("haveFinished",question.getHaveFinished());values.put("isTrue",question.getIsTrue());values.put("chapter",question.getChapter());db.insert("Question", null, values);}}}

这种封装数据库的写法是沿袭了《第一行代码》中对于SQLite的封装写法，questionOpenHelper继承SQLiteOpenHelper，完成数据库的生成，QuestionDB写成单例，里面可以写对于数据库的各种操作，本例中只写了对于数据库数据的存储。

3 读取Excel表格，生成原始SQLite数据库

那tm怎么读取Excel表格呢？
好吧！其实是有解决这个的相应工具类库。
jxl.jar 直接百度搜这个 在CSDN免费下载
然后把 jxl.jar 导入到工程，把Excel表格放在assets文件夹下，如果没这个文件夹就在main文件夹下新建assets文件夹，此处说的是Android studio的相应方法，eclipse肯定是有类似操作的，但是不是和as一样就不晓得了。
准备工作做好了，接下来就是读取Excel表格了，我的做法是写一个辅助Activity去处理这些并不需要让用户使用的功能，也就是说这个Activity也就是在开发时用，在以后开发完成之后不将其写进AndroidManifest中，这个Activity的布局也就很简单 ，线性放几个按钮就行了，反正也不是给用户看的。

public class AssistActivity extends Activity{/** 这个界面并没有用上，只是作为辅助界面用的* */private Toast toast;private Button read_btn;private QuestionDB questionDB;private Question question;private void tips(String str){toast.setText(str);toast.show();}@Overrideprotected void onCreate(Bundle savedInstanceState) {super.onCreate(savedInstanceState);setContentView(R.layout.assist_layout);toast=Toast.makeText(this,"",Toast.LENGTH_SHORT);read_btn=(Button)findViewById(R.id.read_btn);questionDB=QuestionDB.getInstance(this);read_btn.setOnClickListener(new View.OnClickListener() {@Overridepublic void onClick(View v) {//读取Excel表格try {InputStream mInputStream = getResources().getAssets().open("problem3.xls");Workbook wb = Workbook.getWorkbook(mInputStream);Sheet mSheet = wb.getSheet(0);int row = mSheet.getRows();Cell temp;for(int i= 1 ; i < row ; i ++){question=new Question();temp = mSheet.getCell(1, i);question.setQuestionContent(temp.getContents());question.setAnswerNum(4);temp=mSheet.getCell(3,i);question.setAnswerA(temp.getContents());temp=mSheet.getCell(4,i);question.setAnswerB(temp.getContents());temp=mSheet.getCell(5,i);question.setAnswerC(temp.getContents());temp=mSheet.getCell(6,i);question.setAnswerD(temp.getContents());temp=mSheet.getCell(9,i);if(temp.getContents().equals("A")){question.setAnswerCorrect(1);}else if(temp.getContents().equals("B")){question.setAnswerCorrect(2);}else if(temp.getContents().equals("C")){question.setAnswerCorrect(3);}else if(temp.getContents().equals("D")){question.setAnswerCorrect(4);}temp=mSheet.getCell(10,i);question.setComent(temp.getContents());temp=mSheet.getCell(11,i);int cha;try {cha = Integer.parseInt(temp.getContents());}catch (Exception e){cha=9;}question.setChapter(cha-1);question.setIsTrue(0);question.setHaveFinished(0);questionDB.saveQuestion(question);}wb.close();mInputStream.close();} catch (BiffException e) {// TODO Auto-generated catch blocke.printStackTrace();} catch (IndexOutOfBoundsException e) {// TODO Auto-generated catch blocke.printStackTrace();} catch (IOException e) {// TODO Auto-generated catch blocke.printStackTrace();}}});}

布局文件就不贴出来了，简单的不能再简单的就是一个按钮 按一下 ，然后就开始从Excel中读取信息，生成初试SQLite数据库，其实我这块代码写的还是挺有问题的，因为读取操作和存储操作都是耗时操作，点击按钮 的时候可以明显感到手机卡顿了好几秒，其实这里开辟一个子线程去处理更加合适一点，不过毕竟不是给用户用的，所以只要没ANR也就无所谓了。

4 备份SQLite数据库
其实这之前大段大段的都是怎么通过Excel生成初始的SQLite数据库，既然生成了初始数据库，就把它备份下来。
依然是在上一步中的辅助Activity中，加一个按钮，然后把下面这段代码写在按钮的触发操作中

 file_btn.setOnClickListener(new View.OnClickListener() {@Overridepublic void onClick(View v) {try {File dbFile = getDatabasePath("English");File file = new File(Environment.getExternalStorageDirectory(), "English");if (!file.exists()) {try {file.createNewFile();} catch (IOException e) {e.printStackTrace();}}FileInputStream is = new FileInputStream(dbFile);FileOutputStream out = new FileOutputStream(file);byte[] buff = new byte[1024];int n = 0;while ((n = is.read(buff)) > 0) {Log.e("tag", "len=" + n);out.write(buff, 0, n);}is.close();out.close();}catch (Exception e){}}});

这段代码就是在sd卡中生成一个名为English的File，然后把数据库中的信息通过流导入到File中，这样先点击read_btn，生成SQLite数据库，再点击file_btn，在sd卡中生成file，这里因为涉及到了对于sd卡的操作，得在AndroidMainfests中写入相应的权限。在手机的sd卡中找到名叫English的File，把File传给电脑，然后依然是把这个File放在assets目录下，数据库的备份到这算结束了。

5 修改数据库工具类，实现第一次打开App时，导入已经初始化的SQLite数据库信息

public class questionOpenHelper extends SQLiteOpenHelper{/** Sqlite数据库* 包括数据库的创建、备份* */private static String DB_PATH = "/data/data/com.example.cjm.englishlearn/databases/";private static final String DB_NAME="English";public static final String CREATE_QUESTION="create table Question (" +"questionId integer primary key autoincrement," +"questionContent text," +"answerA text," +"answerB text," +"answerC text," +"answerD text," +"answerE text," +"answerF text," +"coment text," +"answerCorrect integer," +"answerChoose integer," +"answerNum integer," +"haveFinished integer," +"isTrue integer," +"chapter integer)";private Context context;public questionOpenHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {super(context, name, factory, version);this.context=context;}@Overridepublic void onCreate(SQLiteDatabase db) {db.execSQL(CREATE_QUESTION);}@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {}private boolean checkDataBase(){SQLiteDatabase checkDB = null;try{String myPath = DB_PATH + DB_NAME;checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);}catch(SQLiteException e){}if(checkDB != null){checkDB.close();}return checkDB != null ? true : false;}public void createDataBase() throws IOException {boolean dbExist = checkDataBase();if(dbExist){return;}else{this.getReadableDatabase();try {copyDataBase();} catch (IOException e) {throw new Error("Error copying database");}}}private void copyDataBase() throws IOException {InputStream myInput = context.getAssets().open(DB_NAME);String outFileName = DB_PATH + DB_NAME;OutputStream myOutput = new FileOutputStream(outFileName);byte[] buffer = new byte[1024];int length;while ((length = myInput.read(buffer))>0){myOutput.write(buffer, 0, length);}myOutput.flush();myOutput.close();myInput.close();}}

public class QuestionDB {private static final String DB_NAME="English";private static final int VERSION=1;private static QuestionDB questionDB;private SQLiteDatabase db;private QuestionDB(Context context) {questionOpenHelper helper = new questionOpenHelper(context, DB_NAME, null, VERSION);try {helper.createDataBase();} catch (IOException e) {}db = helper.getWritableDatabase();}/*省略n行代码*/
}

当调用QuestionDB时，会先通过checkDataBase()检测是否已经生成了SQLite数据库，如果没生成，即App是第一次打开，通过流从存放在assets目录下的English导入到新生成的SQLite中，实现数据库的初始化。

尾记：

因为最早App是打算做成英语单选的刷题App，所以中间好多东西的起名字都用了English，其实这跟英语基本上没半毛钱关系，自行忽略就好。