安卓端有很多优秀的数据库框架来操作sqlite，如ormlite框架，这个框架可以用来实现表到对象的解析和转化。
使用：
首先去官网下载两个jar包，core和android（如果在安卓端开发的话），或者是在gradle配置文件build.gradle中加入
implementation 'com.j256.ormlite:ormlite-android:4.48' 和 implementation 'com.j256.ormlite:ormlite-core:5.0'
在使用ormlite的时候，首先得根据自己设计的数据库结构，新建几个对象类，这些对象类就对应了sql中的表。
这些对象类应该包括，成员变量（用来表示表结构中的列和字段名），对应的set和get方法。
在类名，和成员变量前应该加入描述符@DatabaseTable(....可用的参数和属性设置....) @DatabaseField（.....可用的参数和属性设置....如id = true 即为设置这一列为主键）
一对多外键作为一个成员变量放在多的那个表中，而多对多外键则建一张新的表作为媒介，表列（成员变量）即为有关系的两个对象。
数据库结构建立好了之后，下面就是操作的部分了。
创建ORMLite数据库管理工具类ORMLiteDatabaseHelper.java：
package com.example.dell.db; import android.content.Context; import android.util.Log; import com.j256.ormlite.cipher.android.apptools.OrmLiteSqliteOpenHelper; import com.j256.ormlite.dao.Dao; import com.j256.ormlite.dao.RuntimeExceptionDao; import com.j256.ormlite.stmt.PreparedQuery; import com.j256.ormlite.support.ConnectionSource; import com.j256.ormlite.table.TableUtils; import net.sqlcipher.database.SQLiteDatabase; import java.sql.SQLException; import java.util.List; /** * Created by wu-pc on 2018/5/9. * Copied from official example, and revised for my own purpose */ public class DatabaseHelper extends OrmLiteSqliteOpenHelper { final static private String TAG = "DatabaseHelper"; // name of the database file for your application -- change to something appropriate for your app private static final String DATABASE_NAME = "heartTrace.db"; // any time you make changes to your database objects, you may have to increase the database version private static final int DATABASE_VERSION = 2; // the DAO object we use to access the Diary table private Dao<Diary, Integer> diaryDao = null; private RuntimeExceptionDao<Diary, Integer> runtimeDiaryDao = null; private Dao<DiaryLabel, Integer> diaryLabelDao = null; private RuntimeExceptionDao<DiaryLabel, Integer> runtimeDiaryLabelDao = null; private Dao<Diarybook, Integer> diarybookDao = null; private RuntimeExceptionDao<Diarybook, Integer> runtimeDiarybookDao = null; private Dao<Sentence, Integer> sentenceDao = null; private RuntimeExceptionDao<Sentence, Integer> runtimeSentenceDao = null; private Dao<Label, Integer> labelDao = null; private RuntimeExceptionDao<Label, Integer> runtimeLabelDao = null; private Dao<SentenceLabel, Integer> sentenceLabelDao = null; private RuntimeExceptionDao<SentenceLabel, Integer> runtimeSentenceLabelDao = null; private Dao<Sentencebook, Integer> sentencebookDao = null; private RuntimeExceptionDao<Sentencebook, Integer> runtimeSentencebookDao = null; private Dao<SearchHistory, Integer> searchHistoryDao = null; private RuntimeExceptionDao<SearchHistory, Integer> runtimeSearchHistoryDao = null; private PreparedQuery<Label> labelForDiaryQuery; private PreparedQuery<Diary> diaryForLabelQuery; public DatabaseHelper(Context context) { super(context, DATABASE_NAME, null, DATABASE_VERSION); SQLiteDatabase.loadLibs(context); } /** * This is called when the database is first created. Usually you should call createTable statements here to create * the tables that will store your data. */ public void onCreate(SQLiteDatabase db, ConnectionSource connectionSource) { try { Log.i(DatabaseHelper.class.getName(), "onCreate"); TableUtils.createTable(connectionSource, Diary.class); TableUtils.createTable(connectionSource, Diarybook.class); TableUtils.createTable(connectionSource, DiaryLabel.class); TableUtils.createTable(connectionSource, Label.class); TableUtils.createTable(connectionSource, Sentence.class); TableUtils.createTable(connectionSource, SentenceLabel.class); TableUtils.createTable(connectionSource, Sentencebook.class); TableUtils.createTable(connectionSource, SearchHistory.class); } catch (SQLException e) { Log.e(DatabaseHelper.class.getName(), "Can't create database", e); throw new RuntimeException(e); } } /** * This is called when your application is upgraded and it has a higher version number. This allows you to adjust * the various data to match the new version number. * Don't need it by now. */ public void onUpgrade(SQLiteDatabase db, ConnectionSource connectionSource, int oldVersion, int newVersion) { try { Log.i(DatabaseHelper.class.getName(), "onUpgrade"); TableUtils.dropTable(connectionSource, Diary.class, true); TableUtils.dropTable(connectionSource, Diarybook.class, true); TableUtils.dropTable(connectionSource, DiaryLabel.class, true); TableUtils.dropTable(connectionSource, Label.class, true); TableUtils.dropTable(connectionSource, Sentence.class, true); TableUtils.dropTable(connectionSource, SentenceLabel.class, true); TableUtils.dropTable(connectionSource, Sentencebook.class, true); TableUtils.dropTable(connectionSource, SearchHistory.class, true); // after we drop the old databases, we create the new ones onCreate(db, connectionSource); } catch (SQLException e) { Log.e(DatabaseHelper.class.getName(), "Can't drop databases", e); throw new RuntimeException(e); } } protected String getPassword() { return "hello I'm password"; } /** * Returns the Database Access Object (DAO) for our Diary class. It will create it or just give the cached * value. */ public Dao<Diary, Integer> getDiaryDao() throws SQLException { if (diaryDao == null) { diaryDao = getDao(Diary.class); } return diaryDao; } public Dao<Sentence, Integer> getSentenceDao() throws SQLException { if(sentenceDao == null) { sentenceDao = getDao(Sentence.class); } return sentenceDao; } public Dao<Label, Integer> getLabelDao() throws SQLException { if (labelDao == null) { labelDao = getDao(Label.class); } return labelDao; } public Dao<DiaryLabel, Integer> getDiaryLabelDao() throws SQLException { if (diaryLabelDao == null) { diaryLabelDao = getDao(DiaryLabel.class); } return diaryLabelDao; } //for the sentence public Dao<SentenceLabel, Integer> getSentenceLabelDao() throws SQLException { if (sentenceLabelDao == null) { sentenceLabelDao = getDao(SentenceLabel.class); } return sentenceLabelDao; } public Dao<Sentencebook, Integer> getSentencebookDao() throws SQLException { if (sentencebookDao == null) { sentencebookDao = getDao(Sentencebook.class); } return sentencebookDao; } public Dao<Diarybook, Integer> getDiarybookDao() throws SQLException { if (diarybookDao == null) { diarybookDao = getDao(Diarybook.class); } return diarybookDao; } public Dao<SearchHistory, Integer> getSearchHistoryDao() throws SQLException { if (searchHistoryDao == null) { searchHistoryDao = getDao(SearchHistory.class); } return searchHistoryDao; }
通过Helper得到相应表结构的dao之后就可以对数据库进行一系列的操作了
如dao.create(diary)
dao.delete(diary)
查找：需要先得到一个queryBuilder:
QueryBuilder<Diary, Integer> qb = helper.getDiaryDao().queryBuilder();
在这个queryBuilder的where（）返回的where对象中加入查找的条件
如：qb.where().eq(列名, 待查)
在加入了所有的查找条件之后，qb.query()返回查找的所有返回对象，是一个List。