Android(安卓)数据库框架ormlite(一)
16lz
2021-01-26
2017开篇博客, 近日使用ormlite做数据库管理遇到了问题,尤其是在做一对多的需求时,这里我使用文件夹和文件作为俩个数据库表举例。
基本使用
Android studio引入:
compile 'com.j256.ormlite:ormlite-android:4.48'compile 'com.j256.ormlite:ormlite-core:4.48'
最好到官网查看最新包版本: http://ormlite.com/
遇到问题首先查官网没毛病,也有很多讲的好的资料介绍入门用法, 比如http://blog.csdn.net/lmj623565791/article/details/39121377, 都可以了解。
创建一个文件数据库表只需这样:
@DatabaseTable(tableName = "fileentry")public class FileEntry { @DatabaseField(generatedId= true) public String id; @DatabaseField public String name; //**外键关联,表名这个文件属于哪个文件夹, foreignAutoRefresh 意思是更新FileEntry 时VideoFolderEntry 对自动刷新, 后面会提到如何更新** @DatabaseField(foreign = true, foreignAutoRefresh = true) public VideoFolderEntry videoFolderEntry; }
创建一个文件夹数据库表:
//数据库表名字为videofolderentry@DatabaseTable(tableName = "videofolderentry")public class VideoFolderEntry implements Serializable,Cloneable{ //id自动 生成 @DatabaseField(generatedId = true) public Integer id; //unique 不可重复 @DatabaseField(unique = true) public String folder_name; //**一个文件夹对应多个文件, 一对多, 这里ormlite要求必须是ForeignCollectionField或者CollectionField** @ForeignCollectionField(eager = true) public ForeignCollection fileEntries; @Override public String toString() { return id + " is " + id + " with " + folder_name + "is" + folder_name + "downloadEntries:" + fileEntries.toString(); } @Override public boolean equals(Object o) { return o.hashCode() == this.hashCode(); } @Override public int hashCode() { return id.hashCode(); } @Override protected Object clone() throws CloneNotSupportedException { return super.clone(); }}
编写一个OrmDBHelper工具类
DBHelper工具类大体相同, 自己封装一个没毛病
public class OrmDBHelper extends OrmLiteSqliteOpenHelper { public static final String DB_NAME = "DB_NAME"; public static final int DB_VERSION = 6; private Context context; public OrmDBHelper(Context context, String databaseName, CursorFactory factory, int databaseVersion) { super(context, DB_NAME, factory, DB_VERSION); } public OrmDBHelper(Context context) { super(context, DB_NAME, null, DB_VERSION); this.context = context; } @Override public void onCreate(SQLiteDatabase database, ConnectionSource connectionSource) { try { TableUtils.createTableIfNotExists(connectionSource, DownloadEntry.class); TableUtils.createTableIfNotExists(connectionSource, VideoFolderEntry.class);} @Override public void onUpgrade(SQLiteDatabase database, ConnectionSource connectionSource, int oldVersion, int newVersion) { if (oldVersion < DB_VERSION) { try { //如果你升级了版本, 增加或者删除了字段, 我提供一个工具类, 只需以下一行代码 TYPE.ADD为增加TYPE.DELETE为删除, 工具类在下面 DatabaseUtil.upgradeTable(database, connectionSource, DownloadEntry.class, DatabaseUtil.OPERATION_TYPE.ADD); } onCreate(database, connectionSource); }}
DatabaseUtil:
public class DatabaseUtil { public static final String TAG = "DatabaseUtil.java"; /** * 数据库表操作类型 */ public enum OPERATION_TYPE { /** * 表新增字段 */ ADD, /** * 表删除字段 */ DELETE } /** * 升级表,增加字段 * * @param db * @param clazz */ public static void upgradeTable(SQLiteDatabase db, ConnectionSource cs, Class clazz, OPERATION_TYPE type) { String tableName = extractTableName(clazz); db.beginTransaction(); try { //Rename table String tempTableName = tableName + "_temp"; String sql = "ALTER TABLE " + tableName + " RENAME TO " + tempTableName; db.execSQL(sql); //Create table try { sql = TableUtils.getCreateTableStatements(cs, clazz).get(0); db.execSQL(sql); } catch (Exception e) { e.printStackTrace(); TableUtils.createTable(cs, clazz); } //Load data String columns; if (type == OPERATION_TYPE.ADD) { columns = Arrays.toString(getColumnNames(db, tempTableName)).replace("[", "").replace("]", ""); } else if (type == OPERATION_TYPE.DELETE) { columns = Arrays.toString(getColumnNames(db, tableName)).replace("[", "").replace("]", ""); } else { throw new IllegalArgumentException("OPERATION_TYPE error"); } sql = "INSERT INTO " + tableName + " (" + columns + ") " + " SELECT " + columns + " FROM " + tempTableName; db.execSQL(sql); //Drop temp table sql = "DROP TABLE IF EXISTS " + tempTableName; db.execSQL(sql); db.setTransactionSuccessful(); } catch (Exception e) { e.printStackTrace(); } finally { db.endTransaction(); } } /** * 获取表名(ormlite DatabaseTableConfig.java) * * @param clazz * @param * @return */ private static String extractTableName(Class clazz) { DatabaseTable databaseTable = clazz.getAnnotation(DatabaseTable.class); String name; if (databaseTable != null && databaseTable.tableName() != null && databaseTable.tableName().length() > 0) { name = databaseTable.tableName(); } else { /* * NOTE: to remove javax.persistence usage, comment the following line out */ name = JavaxPersistence.getEntityName(clazz); if (name == null) { // if the name isn't specified, it is the class name lowercased name = clazz.getSimpleName().toLowerCase(); } } return name; } /** * 获取表的列名 * * @param db * @param tableName * @return */ private static String[] getColumnNames(SQLiteDatabase db, String tableName) { String[] columnNames = null; Cursor cursor = null; try { cursor = db.rawQuery("PRAGMA table_info(" + tableName + ")", null); if (cursor != null) { int columnIndex = cursor.getColumnIndex("name"); if (columnIndex == -1) { return null; } int index = 0; columnNames = new String[cursor.getCount()]; for (cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext()) { columnNames[index] = cursor.getString(columnIndex); index++; } } } catch (Exception e) { e.printStackTrace(); } finally { if (cursor != null) { cursor.close(); } } return columnNames; }}
重点来了,一对多如何进行增删改查
每个数据库表应该有个数据自己的DAO, 自行编写。
如何更新一对多里面多的数据
假设文件夹数据库表里面有一条数据, 开始我是这样做的:
//新建一个文件FileEntry fileEntry = new FileEntry();VideoFolderEntry videoFolderEntry = new VideoFolderEntry();videoFolderEntry .fileentries.add(fileEntry);
此时会报错:UNIQUE constraint failed:
下面为正确方式:
意思是一对多的时候你要想更新多的部分你只需更新一的部分, 设置文件所属于哪个文件夹
删除与增加同理。
//拿到要更新的文件夹表记录VideoFolderEntry videoFolderEntry = new VideoFolderDao(context).queryAll().get(0);//新建一个文件FileEntry fileEntry = new FileEntry();//设置当前文件所属文件夹fileEntry.videoFolderEntry = videoFolderEntry;//更新文件表 OK!此时俩个表会都更新mDBhelper.getDao(FileEntry.class).createOrUpdate(fileEntry );
那么如果我想移动一个文件到另一个文件夹呢
只需如此:
//这里有一些伪代码 原理是查询出你要移动到的文件夹的那条数据, 然后设置当前文件夹所属这条数据, 然后update即可public void moveToFolder(String new_folder_name, DownloadEntry moveEntry) { List videoFolderEntries = null; try { videoFolderEntries = DBController.getInstance(context).getVideoFolderDao().queryForEq("folder_name", new_folder_name); moveEntry.videoFolderEntry = videoFolderEntries.get(0); newOrUpdate(moveEntry); } catch (SQLException e) { e.printStackTrace(); } }
查询文件夹表所有数据
//获取它的dao然后query即可mDBhelper.getDao(VideoFolderEntry.class).queryForAll();
删除一条数据
public void delete(VideoFolderEntry entry){ try { mDBhelper.getDao.delete(entry); } catch (SQLException e) { e.printStackTrace(); } }
增加或者更新一条数据
public void add(VideoFolderEntry entry){ try { mDBhelper.getDao.createOrUpdate(entry); } catch (SQLException e) { e.printStackTrace(); } }
目前我还没用过many-to-many,留作后续研究, 目前基本使用ormlite没问题了, 不过有的地方我也要刨根问底, 例如一对多更新时为什么要更新一的一方而不能直接add呢? 既然有Android 数据库框架ormlite(一) 必然有Android 数据库框架ormlite(二), 哈哈 敬请期待!
更多相关文章
- SpringBoot 2.0 中 HikariCP 数据库连接池原理解析
- Android(安卓)VideoView播放视频(1)
- ArcGIS for Android(安卓)在Eclipse上的安装配置 (下:安装配置篇)
- Android(安卓)开发 框架系列 百度语音合成
- Android(安卓)Data Binding ——入门
- 手工安装android-sdk-windows
- 使用Android(安卓)Studio开发一个简易的音乐播放器
- Android(安卓)上显示 PDF 文件
- Android读取asserts和raw文件夹下的文件