package com.example.sqliteexample;
import android.content.Context;
import android.database.DatabaseErrorHandler;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.text.TextUtils;
import android.util.Log;
import androidx.annotation.Nullable;
import androidx.viewbinding.BuildConfig;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.util.Locale;
public class DeviceOpenHelper extends SQLiteOpenHelper {
private static final String TAG = DeviceOpenHelper.class.getSimpleName();
private static final int SCHEMA_VERSION = 2;
private static final String DB_NAME = "device.db";
private final Context context;
private static DeviceOpenHelper instance;
public synchronized static DeviceOpenHelper getInstance(Context context) {
if (instance == null) {
instance = new DeviceOpenHelper(context.getApplicationContext());
}
return instance;
}
/**
* 单例
* @param context This will be help by the instance
*/
private DeviceOpenHelper(Context context) {
super(context, DB_NAME, null, SCHEMA_VERSION);
this.context = context;
}
@Override
public void onCreate(SQLiteDatabase db) {
for (int i = 1; i <= SCHEMA_VERSION; i++) {
applySqlFile(db, i);
}
}
private void applySqlFile(SQLiteDatabase db, int i) {
BufferedReader bufferedReader = null;
try {
String fileName = String.format(Locale.getDefault(), "%s.%d.sql", DB_NAME, i);
final InputStream inputStream = context.getAssets().open(fileName);
bufferedReader = new BufferedReader(new InputStreamReader(inputStream));
final StringBuilder stringBuilder = new StringBuilder();
for (String line; (line = bufferedReader.readLine()) != null; ) {
if (BuildConfig.DEBUG) {
Log.i(TAG, "Reading line ->" + line);
}
if (!TextUtils.isEmpty(line) && !line.startsWith("--")) {
stringBuilder.append(line.trim());
}
if (line.endsWith(";")) {
if (BuildConfig.DEBUG) {
Log.i(TAG, "Running stringBuilder -> " + stringBuilder);
}
}
if (!TextUtils.isEmpty(stringBuilder)){
db.execSQL(stringBuilder.toString());
}
/*db.execSQL(stringBuilder.toString());*/
stringBuilder.setLength(0);
}
} catch (IOException e) {
Log.e(TAG, "Could not apply SQL file", e);
e.printStackTrace();
} finally {
if (bufferedReader != null) {
try {
bufferedReader.close();
} catch (IOException e) {
Log.e(TAG, "Could not close reader", e);
e.printStackTrace();
}
}
}
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
for (int i = oldVersion + 1; i <= newVersion; i++) {
applySqlFile(db, i);
}
}
@Override
public void onConfigure(SQLiteDatabase db) {
super.onConfigure(db);
}
public interface Tables {
String DEVICE = "device";
String MANUFACTURER = "manufacturer";
}
}
以上代码是从《Android数据库最佳实现》一书中抄袭来的。个人理解这应该是作者想让初学者多熟悉SQL语句而精心设计的。目前已读完四章了,给我的最大的收获就是数据库升级的思路。
第一步:备份数据
通过CREATE TABLE temp_table_name AS SELECT *(或者具体的字段) FROM old_table name语句备份数据。
第二步: 删除旧表
DROP TABLE old_table_name
第三步:把临时表的表名重命名为旧表的名
ALERT TABLE temp_table_name RENAME TO old_table_name
第四步:删除临时表
DROP TABLE temp_table_name
这个思路帮助我把“数据库升级”的认知具体化。
以下是我在学习中写的SQL文件中的一些SQL语句,仅供参考
创建数据表,并填充数据。
CREATE TABLE IF NOT EXISTS deviceInfo(Num INTEGER PRIMARY KEY AUTOINCREMENT,model TEXT,nickname TEXT,display_size_inches REAL,manufacturer_id INTEGER);
CREATE TABLE IF NOT EXISTS device (No text,version real,branch text);
--DROP TABLE deviceInfo;
--填充数据
INSERT INTO deviceInfo(model,nickname,display_size_inches,manufacturer_id) VALUES('Nexus One','Passion',3.7,1);
INSERT INTO deviceInfo(model,nickname,display_size_inches,manufacturer_id) VALUES('Nexus S','Crespo',4.0,2);
INSERT INTO deviceInfo(model,nickname,display_size_inches,manufacturer_id) VALUES('Galaxy Nexus','Toro',4.65,2);
INSERT INTO deviceInfo(model,nickname,display_size_inches,manufacturer_id) VALUES('Nexus','Mako',4.7,3);
--1.创建临时表,存储deviceInfo表中Num = 3的记录
CREATE TABLE TEMP_DEVICE AS SELECT model,nickname,display_size_inches,manufacturer_id from deviceInfo where Num = 4
--UPDATE deviceInfo SET model='Pixel 6',nickname='S',display_size_inches=6.0,manufacturer_id=4 where Num=4;
--2.更新deviceInfo表中Num = 3的记录
UPDATE deviceInfo SET model='',nickname='S',display_size_inches=6.0,manufacturer_id=4 where Num=4;
--3.将临时表中的数据重新插入deviceInfo表中
INSERT INTO deviceInfo(model,nickname,display_size_inches,manufacturer_id) SELECT model,nickname,display_size_inches,manufacturer_id from TEMP_DEVICE
--4.删除临时表
DROP TABLE TEMP_DEVICE
最后记载一下使用Android Studio查看Sqlite的方法
将鼠标放在Android Studio的左下角的小窗口图标上,就会弹出如第一图所示的窗口,选择APP Inspection 就会出现第二图所示的Sqlite的可视化窗口。(在程序运行后,会自动链接到包路径下的数据库和表,双击表名既可以查看表内的数据)