使用大型sqlite推测数据库的指南 [英] Guide to use a large sqlite propulated database
问题描述
我知道这类问题已经很多了(大多数问题都没有有效的答案)，通过答案我没有找到要搜索的内容:
I know this kind questions has been around a lot (most of them with no valid answer), and getting through the answers i didn't find what i'm searching for which is:
-
如果我想在数据库中使用图片(通过sqlite浏览器插入)，是否有任何规格(格式，大小)或限制?因此当我在android中检索它们时它们会正常工作. (PS:我有近100张JPEG图片，每张图片约有1-2 Mo大小，仅用于图片就总计150Mo)
if i want to use pictures in my database (insertion with sqlite browser), is there any specification(format,size) or limite? so they would work fine when i retrieve them in android. (PS: i have almost 100 JPEG pictures, each has approximately 1-2 Mo size , which count in total 150Mo just for pictures)
可以在android中插入的数据库的大小限制是多少? (我已经读过有关在外部文件或类似文件中插入数据库的信息(因为APK不应超过50Mo)，您能告诉我怎么做吗?
what's the size limit of database that can be inserted in android? ( i've read about inserting database in external file or something like that(because APK shouldn't be more than 50Mo), can you show me how?
为什么我要问这些问题?因为我在数据库中使用了7Mb的JPEG图片，并且该应用程序停止了..所以我需要知道如何正确使用图片并管理数据库的大小 谢谢
why i'm asking those questions? because i have used a JPEG picture with 7Mb in my database and the app stopped..so i need to know how to use correctly pictures and manage size of database Thanks
import com.readystatesoftware.sqliteasset.SQLiteAssetHelper;
public class Database {
private static final int DATABASE_VERSION = 1;
private static final String DATABASE_NAME = "URTs.db";
private static final String DATABASE_TABLE = "OrganAnatomy";
public static final String DATABASE_ID = "_id";
public static final String DATABASE_GROUP_1 = "Larynx_features";
public static final String DATABASE_CHILD_1 = "Larynx";
public static final String DATABASE_CHILD_2 = "pictures";
private final Context mContext;
private DatabaseHelper mDatabaseHelper;
private SQLiteDatabase mDB;
public Database(Context context) {
mContext = context;
}
public void open() {
mDatabaseHelper = new DatabaseHelper(mContext, DATABASE_NAME, null, DATABASE_VERSION);
mDB = mDatabaseHelper.getWritableDatabase();
}
public void close() {
if (mDatabaseHelper != null) mDatabaseHelper.close();
}
public Cursor getDatabase() {
String whereclause = DATABASE_CHILD_1 + " IS NOT NULL";
return mDB.query(DATABASE_TABLE, null, whereclause, null, null, null, DATABASE_ID);
}
public Cursor getID(long rowID) {
return mDB.query(DATABASE_TABLE, null, "_id" + " = "
+ rowID , null, null, null, null);
}
public class DatabaseHelper extends SQLiteAssetHelper {
public DatabaseHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
super(context, name, factory, version);
}
}
}
推荐答案
With regard to the 50Mb (now 100Mb) APK size you can find out about APK Expansion files at APK Expansion Files, which includes :-
每次使用Google Play控制台上传APK时， 将一个或两个扩展文件添加到APK的选项.每个文件可以 最高2GB，可以是您选择的任何格式，但是我们建议您 在下载过程中使用压缩文件来节省带宽. 从概念上讲，每个扩展文件都扮演不同的角色:
Each time you upload an APK using the Google Play Console, you have the option to add one or two expansion files to the APK. Each file can be up to 2GB and it can be any format you choose, but we recommend you use a compressed file to conserve bandwidth during the download. Conceptually, each expansion file plays a different role:
主扩展文件是用于其他扩展的主扩展文件 您的应用程序所需的资源.补丁扩展文件为 可选，用于对主扩展文件进行小的更新. 尽管您可以随意使用两个扩展文件，但我们 建议主扩展文件提供主要资产，并 应该很少更新(如果曾经更新)补丁扩展文件应为 较小并用作补丁载体"，每个版本都进行更新 主要版本或必要的版本.
The main expansion file is the primary expansion file for additional resources required by your application. The patch expansion file is optional and intended for small updates to the main expansion file. While you can use the two expansion files any way you wish, we recommend that the main expansion file deliver the primary assets and should rarely if ever updated; the patch expansion file should be smaller and serve as a "patch carrier," getting updated with each major release or as necessary.
但是，即使您的应用程序更新仅需要一个新的补丁程序 扩展文件，您仍然必须上传具有更新的新APK 清单中的versionCode. (Play控制台不允许您 将扩展文件上传到现有的APK.)
However, even if your application update requires only a new patch expansion file, you still must upload a new APK with an updated versionCode in the manifest. (The Play Console does not allow you to upload an expansion file to an existing APK.)
关于在SQLite中存储图像，对于大于100k的图像，应将图像存储为文件并将图像的路径存储在数据库中.
With regard to storing images in SQLite, for images that are larger than around 100k, you should store the images as files and store the path to the image in the database.
有一个限制(不是SQLite的限制)(请参阅下文)，但有一个Android游标窗口的最大大小(2Mb)，该限制会限制检索大型Blob或在产生较大Blob时可能产生明显的不利影响.因此，为什么可以存储但无法检索7Mb图像.
There is a restriction, not with SQLite (see below), but with the maximum size of an Android Cursor Window, which is 2Mb, that restricts or can have a noticeable detrimental impact when retrieving large blobs. Hence why your 7Mb image could be stored but not retrieved.
对于SQLite，有一个限制:-
For SQLite there is a limit as per :-
字符串或BLOB的最大长度
Maximum length of a string or BLOB
在SQLite中定义字符串或BLOB中的最大字节数 通过预处理程序宏SQLITE_MAX_LENGTH.这个的默认值 宏为10亿(10亿或10亿).你可以 使用命令行选项在编译时提高或降低此值 像这样:
The maximum number of bytes in a string or BLOB in SQLite is defined by the preprocessor macro SQLITE_MAX_LENGTH. The default value of this macro is 1 billion (1 thousand million or 1,000,000,000). You can raise or lower this value at compile-time using a command-line option like this:
-DSQLITE_MAX_LENGTH = 123456789当前实现仅支持最大231-1或2147483647的字符串或BLOB长度. 诸如hex()之类的内置函数可能会在此之前失效.在 对安全敏感的应用程序，最好不要尝试增加 最大字符串和Blob长度.实际上，您可能会做得更好，降低 最大字符串长度和blob长度在a的范围内 如果可能的话，几百万.
-DSQLITE_MAX_LENGTH=123456789 The current implementation will only support a string or BLOB length up to 231-1 or 2147483647. And some built-in functions such as hex() might fail well before that point. In security-sensitive applications it is best not to try to increase the maximum string and blob length. In fact, you might do well to lower the maximum string and blob length to something more in the range of a few million if that is possible.
在SQLite的INSERT和SELECT处理的一部分中，完整的 数据库中每一行的内容被编码为单个BLOB.所以 SQLITE_MAX_LENGTH参数还确定 连续字节.
During part of SQLite's INSERT and SELECT processing, the complete content of each row in the database is encoded as a single BLOB. So the SQLITE_MAX_LENGTH parameter also determines the maximum number of bytes in a row.
可以使用以下命令在运行时降低最大字符串或BLOB长度 sqlite3_limit(db，SQLITE_LIMIT_LENGTH，size)接口.
The maximum string or BLOB length can be lowered at run-time using the sqlite3_limit(db,SQLITE_LIMIT_LENGTH,size) interface.
示例应用
此应用程序将较小的图像存储在数据库中，但存储较大图像的路径(大小基于[c0>确定哪个图像)
This App Stores Smaller Images in DB but stores the path for larger images (size to determine which based upon public static final int MAX_FILE_SIZE = 100 * 1024;)
DatabaseHelper DBHelper.java :-
The DatabaseHelper DBHelper.java :-
public class DBHelper extends SQLiteOpenHelper {
public static final String DBNAME = "images.db";
public static final int DBVERSION = 1;
// The maximum size of an image that should be stored 100K
public static final int MAX_FILE_SIZE = 100 * 1024;
public static final String TB_IMAGE = "image";
public static final String COL_IMAGE_ID = BaseColumns._ID;
public static final String COL_IMAGE_PATH = "image_path";
public static final String COL_IMAGE_NAME = "image_name";
public static final String COl_IMAGE_DESCRIPTION = "image_description";
public static final String COL_IMAGE_SIZE = "image_size";
public static final String COL_IMAGE_IMAGE = "image";
SQLiteDatabase mDB;
/**
* Construct DBHelper, note that it will open the database and
* thus create it if it doesn't exist
* @param context a context from the invoking activity
*/
public DBHelper(Context context) {
super(context, DBNAME, null, DBVERSION);
mDB = this.getWritableDatabase();
}
/**
* Create the table(s)
* @param db
*/
@Override
public void onCreate(SQLiteDatabase db) {
String crtsql = "CREATE TABLE IF NOT EXISTS " + TB_IMAGE +
"(" +
COL_IMAGE_ID + " INTEGER PRIMARY KEY, " +
COL_IMAGE_PATH + " TEXT UNIQUE, " +
COL_IMAGE_NAME + " TEXT, " +
COl_IMAGE_DESCRIPTION + " TEXT, " +
COL_IMAGE_SIZE + " INTEGER, " +
COL_IMAGE_IMAGE + " BLOB DEFAULT x'00'" +
")";
db.execSQL(crtsql);
}
@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
}
/**
* Return a Cursor with all the rows from the image table
* @return The Cursor
*/
public Cursor getImageList() {
return mDB.query(TB_IMAGE,null,null,null,null,null,null);
}
/**
* Store an image row in the image table, noting that is the image
* size is small than the max size that the image will be stored as a blob
* otherwise a blob of 1 byte is stored due to the default value.
* @param path the path to the image
* @param description a description for the image
* @return the id (rowid) of the row
*/
public long addImageFromPath(String path, String description) {
ContentValues cv = new ContentValues();
File f = new File(path);
InputStream is;
// If the file doesn't exist don't store a row
if (!f.exists()) {
return -1;
}
// Always store the name, description, path and size
cv.put(COL_IMAGE_NAME,f.getName());
cv.put(COl_IMAGE_DESCRIPTION,description);
cv.put(COL_IMAGE_SIZE,f.length());
cv.put(COL_IMAGE_PATH,f.getAbsolutePath());
// If the size is less than the max then get the filestream
// and convert to a byte[].
// Note if larger then the max file size the default x'00' blob
// will be applied
if (f.length() < MAX_FILE_SIZE) {
byte[] buffer = new byte[(int) f.length()];
try {
is = new FileInputStream(f);
is.read(buffer);
} catch (IOException e) {
e.printStackTrace();
return -1;
}
cv.put(COL_IMAGE_IMAGE,buffer);
}
// Do the insert
return mDB.insert(TB_IMAGE,null,cv);
}
/**
* get the image as a bitmap from the DB if stored, otherwise get it from
* the file, according to the id.
* @param id the id of the row in the image table
* @return the bitmap to be returned (note may be empty bitmap)
*/
public Bitmap getImage(long id) {
byte[] ba = new byte[0];
// If the image is stored in the DB then extract and return the bitmap
if (isStoredAsImage(id)) {
return getImageAsBitMap(id);
}
// If not then get the respective row from the DB
Cursor csr = mDB.query(
TB_IMAGE,
null,
COL_IMAGE_ID+"=?",
new String[]{String.valueOf(id)},
null,
null,
null
);
// Prepare to convert the path to a file
String path = ""; //<<<< default to empty path
File f = new File(path); //<<< default to empty file
// If a valid row was found get the path and File from the row
if (csr.moveToFirst()) {
path = csr.getString(csr.getColumnIndex(COL_IMAGE_PATH));
f = new File(path);
}
// done with the cursor so close it
csr.close();
// If the file exists then return the Bitmap
if (f.exists()) {
return BitmapFactory.decodeFile(f.getAbsolutePath());
}
// return an empty bitmap
return BitmapFactory.decodeByteArray(ba,0,ba.length);
}
/**
* Check to see if an image is stored in the DB,
* note assumes anything less than 8 bytes isn't an image
* @param id the id of the row in the image table
* @return true if like an image is stored, otherwise false
*/
private boolean isStoredAsImage(long id) {
boolean rv = true;
byte[] ba = new byte[0];
// Get the respective row from the image table
Cursor csr = mDB.query(
TB_IMAGE,
null,
COL_IMAGE_ID+"=?",
new String[]{String.valueOf(id)},
null,
null,
null
);
// If a row was found get the blob into byte array ba
// if not then ready to return false
if (csr.moveToFirst()) {
ba = csr.getBlob(csr.getColumnIndex(COL_IMAGE_IMAGE));
} else {
rv = false;
}
// If the byte array ba is less then 8 bytes then ready to return false
if (ba == null || ba.length < 8) {
rv = false;
}
// done with the Cursor so close it
csr.close();
// return the result
return rv;
}
/**
* get the image (assumes isStoredAsImage is used prior to invocation)
* @param id the id of the respective row
* @return the bitmap (may be 0 length)
*/
private Bitmap getImageAsBitMap(long id) {
byte[] ba = new byte[0];
Bitmap bmp;
Cursor csr =mDB.query(
TB_IMAGE,
null,
COL_IMAGE_ID+"=?",
new String[]{String.valueOf(id)},
null,
null,
null
);
if (csr.moveToFirst()) {
ba = csr.getBlob(csr.getColumnIndex(COL_IMAGE_IMAGE));
}
csr.close();
return BitmapFactory.decodeByteArray(ba,0,ba.length);
}
}
MainActivity MainActivity.java
The MainActivity MainActivity.java
public class MainActivity extends AppCompatActivity {
public static String IMAGE_STORE_PATH;
public static final String IMAGES_DIRECTORY = "images";
private static File images_file;
ArrayAdapter<String> mAdapter;
ListView mListView01, mListView02;
ArrayList<String> mImages;
CursorAdapter mCsrAdapter;
Cursor mCsr;
ImageView mImageView;
DBHelper mDBHlpr;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
// get the View/Viewgroup IDs
mListView01 = this.findViewById(R.id.listview001); // File List
mListView02 = this.findViewById(R.id.listview002); // DB List
mImageView = this.findViewById(R.id.imageview001); // Image display
// get an instance of the DBHelper
mDBHlpr = new DBHelper(this);
// Copy images from raw folder to data/data/<package>/Files/images
// Also store all the images in the Database (or not depedning upon size)
getImagesFile(this);
if (getImagesCount() < 1) {
loadRawImages();
storeImagesToDB();
}
// Setup the two ListViews to display image name lists
displayList();
displayListFromDB();
// setup the file list so that when an item is clicked the image is displayed
mListView01.setOnItemClickListener(new AdapterView.OnItemClickListener() {
@Override
public void onItemClick(AdapterView<?> adapterView, View view, int i, long l) {
String imagename = mListView01.getItemAtPosition(i).toString();
displayImage(imagename);
}
});
// setup the DB list so that when an item is clicked the image is displayed
mListView02.setOnItemClickListener(new AdapterView.OnItemClickListener() {
@Override
public void onItemClick(AdapterView<?> adapterView, View view, int i, long l) {
displayDBImage(l);
}
});
}
/**
* Store the images in the images folder to the DB giving then a calculated description
* e.g. image1, image2 .....
*/
private void storeImagesToDB() {
File f = getImagesFile(this);
File[] images = f.listFiles();
int imagecounter = 1;
for (File img: images) {
mDBHlpr.addImageFromPath(img.getPath(),"image" + String.valueOf(imagecounter++));
}
}
/**
* return the directory/folder where the images are stored as a File
* @param context a valid context
* @return the number of images
*/
public static File getImagesFile(Context context) {
if (images_file == null) {
images_file = new File(context.getFilesDir().getPath() + File.separator + IMAGES_DIRECTORY);
if (!images_file.exists()) {
images_file.mkdirs();
}
}
return images_file;
}
/**
* get the number of images
* @return the number of images
*/
public static long getImagesCount() {
File[] files = images_file.listFiles(new FileFilter() {
@Override
public boolean accept(File file) {
return file.isFile();
}
});
return (long) files.length;
}
/**
* Setup/refresh the list of images according to the images folder
* (left ListView)
*/
private void displayList() {
if (mImages == null) {
mImages = new ArrayList<>();
} else {
mImages.clear();
}
mImages.addAll(Arrays.asList(images_file.list()));
if (mAdapter == null) {
mAdapter = new ArrayAdapter<String>(this,android.R.layout.simple_list_item_1, mImages);
mListView01.setAdapter(mAdapter);
} else {
mAdapter.notifyDataSetChanged();
}
}
/**
* Set the image view according to the file
* @param imageName the name of the image (as per the ListView)
*/
private void displayImage(String imageName) {
File img = new File(images_file.getPath() + File.separator + imageName);
if (img.exists()) {
Bitmap bmp = BitmapFactory.decodeFile(img.getAbsolutePath());
mImageView.setImageBitmap(bmp);
}
}
/**
* Set the image view according to the image stored/referred to by the DB
* @param id the id of the respective row in the image table
*/
private void displayDBImage(long id) {
mImageView.setImageBitmap(mDBHlpr.getImage(id));
}
/**
* Setup/refresh the list of images as obtained from the DB (right listview)
*/
private void displayListFromDB() {
mCsr = mDBHlpr.getImageList();
if (mCsrAdapter == null) {
mCsrAdapter = new SimpleCursorAdapter(
this,
android.R.layout.simple_list_item_2,
mCsr,
new String[]{DBHelper.COL_IMAGE_NAME,DBHelper.COL_IMAGE_PATH},
new int[]{android.R.id.text1,android.R.id.text2},
0
);
mListView02.setAdapter(mCsrAdapter);
} else {
mCsrAdapter.swapCursor(mCsr);
}
}
/**
* Load (copy from raw folder to images folder) all images
*/
private void loadRawImages() {
Field[] fields = R.raw.class.getFields();
int resourceID = 0;
String resourceName;
for (Field fld: fields) {
resourceName = fld.getName();
try {
resourceID = fld.getInt(fld);
} catch (IllegalAccessException e) {
e.printStackTrace();
}
Log.d("RAW FLDINFO","name=" + fld.getName() + " ID=" + String.valueOf(resourceID));
copyResourceImageToImages(resourceID,resourceName, true);
}
}
/**
* Copy an image from the raw directory (app/src/main/res/raw directory) to
* the App's data/data/files/images folder
* @param resourceID ID of the resource
* @param resourceName name of the resource (file name less extension)
* @param throw_exception true if an exception should be thrown
*/
private void copyResourceImageToImages(int resourceID, String resourceName, boolean throw_exception) {
String tag = "CPYRSRCTOIMAGES";
InputStream is = getResources().openRawResource(resourceID);
File of = new File(images_file.getPath() + File.separator + resourceName + ".jpg");
Log.d(tag,"Initiating Copy of File " + of.getName());
int buffer_size = 1024 * 4;
int length_to_copy = buffer_size;
int bytesread = 0;
long bytescopied = 0;
OutputStream os;
byte[] buffer = new byte[buffer_size];
if (!of.exists()) {
try {
of.createNewFile();
} catch (IOException e) {
Log.d(tag,"Error Creating File " + of.getName());
e.printStackTrace();
if (throw_exception) {
throw new RuntimeException("Error Creating Output File" + of.getName());
}
return;
}
}
try {
os = new FileOutputStream(of);
} catch (IOException e) {
Log.d(tag,"Error Creating OutputStream for File " + of.getName());
e.printStackTrace();
if (throw_exception) {
throw new RuntimeException("Error Creating OutputStream for File " + of.getName());
}
return;
}
if (os == null) {
throw new RuntimeException("OutputStream not initialised.");
}
try {
while ((bytesread = is.read(buffer)) > 0 ){
try {
os.write(buffer, 0, bytesread);
} catch (IOException e) {
String msg = "Error Writing to Output File " + of.getName() + " Bytes Copied = " + bytescopied;
Log.d(tag, msg);
e.printStackTrace();
if (throw_exception) {
os.close();
of.delete();
throw new RuntimeException(msg);
}
is.close();
os.close();
of.delete();
return;
}
bytescopied = bytescopied + bytesread;
}
}catch (IOException e) {
String msg = "Error reading Input File " + resourceName + " Bytes Copied = " + bytescopied;
Log.d(tag,"Error Reading Input File " + resourceName);
e.printStackTrace();
if (throw_exception) {
throw new RuntimeException(msg);
}
try {
is.close();
os.close();
of.delete();
} catch (IOException e2) {
e2.printStackTrace();
}
}
Log.d(tag,"File " + of.getName() +" Copied - Bytes Successfully Copied = " + bytescopied);
try {
os.flush();
} catch (IOException e) {
e.printStackTrace();
}
try {
os.close();
} catch (IOException e) {
e.printStackTrace();
}
try {
is.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
MainActivity的布局 activity_main.xml
The Layout for MainActivity activity_main.xml
<?xml version="1.0" encoding="utf-8"?>
<android.support.constraint.ConstraintLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:app="http://schemas.android.com/apk/res-auto"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="match_parent"
android:layout_height="match_parent"
tools:context=".MainActivity">
<TextView
android:id="@+id/title"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="Hello World!"
app:layout_constraintBottom_toTopOf="@id/listview001"
app:layout_constraintLeft_toLeftOf="parent"
app:layout_constraintRight_toRightOf="parent"
app:layout_constraintTop_toTopOf="parent"
app:layout_constraintVertical_chainStyle="spread_inside" />
<ListView
android:id="@+id/listview001"
android:layout_width="400dp"
android:layout_height="300dp"
android:background="#FFFFAAAA"
app:layout_constraintBottom_toTopOf="@id/scrollview"
app:layout_constraintEnd_toStartOf="@+id/listview002"
app:layout_constraintHorizontal_bias="0.5"
app:layout_constraintStart_toStartOf="parent"
app:layout_constraintTop_toBottomOf="@id/title"
app:layout_constraintVertical_bias="0.5">
</ListView>
<ListView
android:id="@+id/listview002"
android:layout_width="400dp"
android:layout_height="300dp"
android:background="#FFAAAAFF"
app:layout_constraintBottom_toTopOf="@id/scrollview"
app:layout_constraintEnd_toEndOf="parent"
app:layout_constraintHorizontal_bias="0.5"
app:layout_constraintStart_toEndOf="@+id/listview001"
app:layout_constraintTop_toBottomOf="@id/title"
app:layout_constraintVertical_bias="0.5">
</ListView>
<ScrollView
android:id="@+id/scrollview"
android:layout_width="match_parent"
android:layout_height="wrap_content"
app:layout_constraintEnd_toEndOf="parent"
app:layout_constraintHorizontal_bias="0.5"
app:layout_constraintStart_toEndOf="@+id/listview001"
app:layout_constraintTop_toBottomOf="@id/listview001">
<ImageView
android:id="@+id/imageview001"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:background="#FFAAFFAA"
android:contentDescription="A Picture"
app:layout_constraintRight_toLeftOf="parent" />
</ScrollView>
</android.support.constraint.ConstraintLayout>
注意图像存储在res/raw文件夹中，例如:-
Note Images are stored in the res/raw folder e.g. :-
数据库(从id 20开始)看起来像(突出显示的是数据库中存储的100k以下的一张图像(注释文件留在磁盘上，但是如果需要可以删除以节省空间)):-
The Database (from id 20) looks like (highlighted is the one image under 100k that is stored in the DB (note file left on disk but could be deleted to save space if wanted)) :-
这篇关于使用大型sqlite推测数据库的指南的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
