android:从资产的文件夹(sqllite db)中导出数据,并将其导入应用程序的数据库中

塔沙·纳朗(Tushar Narang)

更新

我找到了这个链接

http://www.reigndesign.com/blog/using-your-own-sqlite-database-in-android-applications/

谁能解释我将如何使用此方法编写更新/插入语句

___________________________________________________________________________

问题

我正在尝试创建一种方法,可以将存储在sqllite数据库中的数据复制到应用程序的数据库中。结构相似。我只是不想一一插入行。

我正在寻找一种方法,可以将存储在资产文件夹或外部存储中的预先输入的数据复制到我的应用数据库中。

我在应用程序中填充数据时遇到问题。我不想以编程方式为每一行和每一列编写5000个插入语句。

谁能建议一种方法。我是android新手,所以请详细说明或提供链接。

我的数据库助手类

import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DBHelper extends SQLiteOpenHelper {
    public  SQLiteDatabase db1;


    // Static Final Variable database meta information

    static final String DATABASE = "assesmenttool.db";
    static final int DATABASE_VERSION = 1;

    //Table Student Details
    static final String TABLEStudent = "StudentDetails";
    static final String S_ID = "_id";
    static final String SchoolID = "SchoolID";
    static final String SchoolName = "schoolname";
    static final String StudentFirstName = "StudentFirstName";
    static final String StudentLastName ="StudentLastName";
    static final String StudentClassLevel ="StudentClassLevel";
    static final String RollNo="RollNo";
    static final String TestDate ="TestDate";

     //Table Response Details   
    static final String TABLEResponse = "TableResponse";
    static final String R_ID = "_id";
    static final String StudentID = "StudentID";
    static final String R_QuestionID = "QuestionID";
    static final String QuestOptionID = "QuestOptionID";

    //Table Question Master
    static final String TableQuestionMaster = "TableQuestionMaster";
    static final String Q_ID= "_id";
    static final String Module_ID = "Module_ID";
    static final String SubModule_ID = "SubModule_ID";
    static final String SubModuleQuestion_ID ="SubModuleQuestion_ID";
    static final String Question_ID= "Question_ID"; 
    static final String Title = "Title";
    static final String Module = "Module";
    static final String TitleDescription = "TitleDescription";
    static final String QuestionText = "QuestionText";
    static final String QuestionImage = "QuestionImage";
    static final String QuestionTemplate = "QuestionTemplate";
    static final String CorrectOptionID = "CorrectOptionID";

    //Table Template Master
    static final String TableTemplateMaster = "TemplateMaster";
    static final String T_ID= "_id";
    static final String Template_ID= "Template_ID"; 
    static final String Name = "Name";
    static final String Description = "Description";

    //Table Question Option 
    static final String TableQuestionOption = "TableQuestionOption";
    static final String TQP_ID= "_id";
    static final String TQP_QuestionID = "QuestionID";
    static final String OptionText = "OptionText";

    //Table Class Master
    static final String TableClassMaster = "TableClassMaster";
    static final String Class_ID= "_id";
    static final String Class = "class";


    // Override constructor
    public DBHelper(Context context) {
        super(context, DATABASE, null, DATABASE_VERSION);

    }


    // Override onCreate method
    @Override
    public void onCreate(SQLiteDatabase db) {


        //Create Table Student Details
        db.execSQL("CREATE TABLE " + TABLEStudent + " ( " + S_ID
                + " INTEGER PRIMARY KEY AUTOINCREMENT, " + SchoolID + " text, "
                + SchoolName + " text, " + StudentFirstName + " text, "  + StudentLastName + " text, " + RollNo + " text," + TestDate + " text," + StudentClassLevel + " text)");


        //Create Table Response Details     
        db.execSQL("CREATE TABLE " + TABLEResponse + " ( " + R_ID
                + " INTEGER PRIMARY KEY AUTOINCREMENT, " + StudentID + " text, "
                + R_QuestionID + " text, " + QuestOptionID + " text)");


        //Create Table Question Master
        db.execSQL("CREATE TABLE " + TableQuestionMaster + " ( " + Q_ID
                + " INTEGER PRIMARY KEY AUTOINCREMENT, " +  Question_ID + " text, " +  Module_ID + " text, " +  SubModule_ID + " text, " +  SubModuleQuestion_ID + " text,  " + Title + " text, "  +  Module + " text," 
                + TitleDescription + " text, " + QuestionText + " text, "  + QuestionImage + " text, " + QuestionTemplate + " text," + CorrectOptionID + " text)");


        //Create Table Template Master
        db.execSQL("CREATE TABLE " + TableTemplateMaster + " ( " + T_ID
                + " INTEGER PRIMARY KEY AUTOINCREMENT, " + Name + " text, "
                + Description + " text)");


        //Create Table Question Option  
        db.execSQL("CREATE TABLE " + TableQuestionOption + " ( " + TQP_ID
                + " INTEGER PRIMARY KEY AUTOINCREMENT, " + TQP_QuestionID + " text, "
                + OptionText + " text)");   

        //Create Table Class Master 
        db.execSQL("CREATE TABLE " + TableClassMaster + " ( " + Class_ID
                + " INTEGER PRIMARY KEY AUTOINCREMENT, " + Class + " text)");   

    }

       public List<String> getAllClasses(){
            List<String> labels = new ArrayList<String>();

            // Select All Query
            String selectQuery = "SELECT  * FROM " + TableClassMaster;

            SQLiteDatabase db = this.getReadableDatabase();
            Cursor cursor = db.rawQuery(selectQuery, null);

            // looping through all rows and adding to list
            if (cursor.moveToFirst()) {
                do {
                    labels.add(cursor.getString(1));
                } while (cursor.moveToNext());
            }

            // closing connection
            cursor.close();
            db.close();

            // returning lables
            return labels;
        }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

        // Drop old version table
        db.execSQL("Drop table " + TABLEStudent);
        db.execSQL("Drop table " + TABLEResponse);
        db.execSQL("Drop table " + TableQuestionMaster);
        db.execSQL("Drop table " + TableTemplateMaster);
        db.execSQL("Drop table " + TableQuestionOption);
        db.execSQL("Drop table " + TableClassMaster);
        // Create New Version table
        onCreate(db);
    }




}

我在stackoverflow上找到的SQLLITE复制类

我如何将两者混合在一起。我该如何同步这两个sqllite表。

  private static final String DB_NAME = "asset.db";

    private Context context;

    public AssetDatabaseOpenHelper(Context context) {
        this.context = context;
    }

    public SQLiteDatabase openDatabase() {
        File dbFile = context.getDatabasePath(DB_NAME);

        if (!dbFile.exists()) {
            try {
                copyDatabase(dbFile);
            } catch (IOException e) {
                throw new RuntimeException("Error creating source database", e);
            }
        }

        return SQLiteDatabase.openDatabase(dbFile.getPath(), null, SQLiteDatabase.OPEN_READONLY);
    }

    private void copyDatabase(File dbFile) throws IOException {
        InputStream is = context.getAssets().open(DB_NAME);
        OutputStream os = new FileOutputStream(dbFile);

        byte[] buffer = new byte[1024];
        while (is.read(buffer) > 0) {
            os.write(buffer);
        }

        os.flush();
        os.close();
        is.close();
    }

}
塔沙·纳朗(Tushar Narang)

这将解决我的答案..

只需使用sqlliteOPenHelper类的帮助即可。

步骤1

添加copyDataBase()函数。

第2步

像这样在您的MainActivity中启动它

helper = new DBHelper(this);

        try {

            helper.createDataBase();

    } catch (IOException ioe) {

        throw new Error("Unable to create database");

    }

    try {

        helper.openDataBase();

    }catch(SQLException sqle){

        throw sqle;

    }

我们的DBHelper类看起来像

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.MatrixCursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DBHelper extends SQLiteOpenHelper {
    public  SQLiteDatabase db1;


    // Static Final Variable database meta information

    static final String DATABASE = "assesmenttool.db";
    static final int DATABASE_VERSION = 1;

    //Table Student Details
    static final String TABLEStudent = "StudentDetails";
    static final String S_ID = "_id";
    static final String SchoolID = "SchoolID";
    static final String SchoolName = "schoolname";
    static final String StudentFirstName = "StudentFirstName";
    static final String StudentLastName ="StudentLastName";
    static final String StudentClassLevel ="StudentClassLevel";
    static final String RollNo="RollNo";
    static final String TestDate ="TestDate";

     //Table Response Details   
    static final String TABLEResponse = "TableResponse";
    static final String R_ID = "_id";
    static final String StudentID = "StudentID";
    static final String R_QuestionID = "QuestionID";
    static final String QuestOptionID = "QuestOptionID";

    //Table Question Master
    static final String TableQuestionMaster = "TableQuestionMaster";
    static final String Q_ID= "_id";
    static final String Module_ID = "Module_ID";
    static final String SubModule_ID = "SubModule_ID";
    static final String SubModuleQuestion_ID ="SubModuleQuestion_ID";
    static final String Question_ID= "Question_ID"; 
    static final String Title = "Title";
    static final String Module = "Module";
    static final String TitleDescription = "TitleDescription";
    static final String QuestionText = "QuestionText";
    static final String QuestionImage = "QuestionImage";
    static final String QuestionTemplate = "QuestionTemplate";
    static final String CorrectOptionID = "CorrectOptionID";

    //Table Template Master
    static final String TableTemplateMaster = "TemplateMaster";
    static final String T_ID= "_id";
    static final String Template_ID= "Template_ID"; 
    static final String Name = "Name";
    static final String Description = "Description";

    //Table Question Option 
    static final String TableQuestionOption = "TableQuestionOption";
    static final String TQP_ID= "_id";
    static final String TQP_QuestionID = "QuestionID";
    static final String TQP_OptionID = "TQP_OptionID";
    static final String OptionText = "OptionText";

    //Table Class Master
    static final String TableClassMaster = "TableClassMaster";
    static final String Class_ID= "_id";
    static final String Class = "class";




       private static String DB_PATH = "/data/data/com.cldonline.assesmenttool/databases/";

        private static String DB_NAME = "assesmenttool.db";

        private SQLiteDatabase myDataBase; 

        private final Context myContext;

        /**
         * Constructor
         * Takes and keeps a reference of the passed context in order to access to the application assets and resources.
         * @param context
         */
        public DBHelper(Context context) {

            super(context, DB_NAME, null, 1);
            this.myContext = context;
        }   

      /**
         * Creates a empty database on the system and rewrites it with your own database.
         * */
        public void createDataBase() throws IOException{

            boolean dbExist = checkDataBase();

            if(dbExist){
                //do nothing - database already exist
            }else{

                //By calling this method and empty database will be created into the default system path
                   //of your application so we are gonna be able to overwrite that database with our database.
                this.getReadableDatabase();

                try {

                    copyDataBase();

                } catch (IOException e) {

                    throw new Error("Error copying database");

                }
            }

        }

        /**
         * Check if the database already exist to avoid re-copying the file each time you open the application.
         * @return true if it exists, false if it doesn't
         */
        private boolean checkDataBase(){

            SQLiteDatabase checkDB = null;

            try{
                String myPath = DB_PATH + DB_NAME;
                checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);

            }catch(SQLiteException e){

                //database does't exist yet.

            }

            if(checkDB != null){

                checkDB.close();

            }

            return checkDB != null ? true : false;
        }

        /**
         * Copies your database from your local assets-folder to the just created empty database in the
         * system folder, from where it can be accessed and handled.
         * This is done by transfering bytestream.
         * */
        private void copyDataBase() throws IOException{

            //Open your local db as the input stream
            InputStream myInput = myContext.getAssets().open(DB_NAME);

            // Path to the just created empty db
            String outFileName = DB_PATH + DB_NAME;

            //Open the empty db as the output stream
            OutputStream myOutput = new FileOutputStream(outFileName);

            //transfer bytes from the inputfile to the outputfile
            byte[] buffer = new byte[1024];
            int length;
            while ((length = myInput.read(buffer))>0){
                myOutput.write(buffer, 0, length);
            }

            //Close the streams
            myOutput.flush();
            myOutput.close();
            myInput.close();

        }





        // Override onCreate method
        @Override
        public void onCreate(SQLiteDatabase db) {



        }

           public List<String> getAllClasses(){
                List<String> labels = new ArrayList<String>();

                // Select All Query
                String selectQuery = "SELECT  * FROM " + TableClassMaster;

                SQLiteDatabase db = this.getReadableDatabase();
                Cursor cursor = db.rawQuery(selectQuery, null);

                // looping through all rows and adding to list
                if (cursor.moveToFirst()) {
                    do {
                        labels.add(cursor.getString(1));
                    } while (cursor.moveToNext());
                }

                // closing connection
                cursor.close();
                db.close();

                // returning lables
                return labels;
            }

           public List<String> getAllOptions(String Qid){
                List<String> options = new ArrayList<String>();

                // Select All Query
                String selectQuery = "SELECT * FROM " + TableQuestionOption +" "+"where QuestionID ='"+Qid+"'";

                SQLiteDatabase db = this.getReadableDatabase();
                Cursor cursor = db.rawQuery(selectQuery, null);

                // looping through all rows and adding to list
                if (cursor.moveToFirst()) {
                    do {
                        options.add(cursor.getString(2));
                    } while (cursor.moveToNext());
                }

                // closing connection
                cursor.close();
                db.close();

                // returning lables
                return options;
            }

           public void openDataBase() throws SQLException{

                //Open the database
                String myPath = DB_PATH + DB_NAME;
                myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);

            }

            @Override
            public synchronized void close() {

                    if(myDataBase != null)
                        myDataBase.close();

                    super.close();

            }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {


        }

        public ArrayList<Cursor> getData(String Query){
            //get writable database
            SQLiteDatabase sqlDB = this.getWritableDatabase();
            String[] columns = new String[] { "mesage" };
            //an array list of cursor to save two cursors one has results from the query 
            //other cursor stores error message if any errors are triggered
            ArrayList<Cursor> alc = new ArrayList<Cursor>(2);
            MatrixCursor Cursor2= new MatrixCursor(columns);
            alc.add(null);
            alc.add(null);


            try{
                String maxQuery = Query ;
                //execute the query results will be save in Cursor c
                Cursor c = sqlDB.rawQuery(maxQuery, null);


                //add value to cursor2
                Cursor2.addRow(new Object[] { "Success" });

                alc.set(1,Cursor2);
                if (null != c && c.getCount() > 0) {


                    alc.set(0,c);
                    c.moveToFirst();

                    return alc ;
                }
                return alc;
            } catch(SQLException sqlEx){
                Log.d("printing exception", sqlEx.getMessage());
                //if any exceptions are triggered save the error message to cursor an return the arraylist
                Cursor2.addRow(new Object[] { ""+sqlEx.getMessage() });
                alc.set(1,Cursor2);
                return alc;
            } catch(Exception ex){

                Log.d("printing exception", ex.getMessage());

                //if any exceptions are triggered save the error message to cursor an return the arraylist
                Cursor2.addRow(new Object[] { ""+ex.getMessage() });
                alc.set(1,Cursor2);
                return alc;
            }


        }




}

本文收集自互联网,转载请注明来源。

如有侵权,请联系 [email protected] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章

从android应用程序获取数据并将其存储在使用php的sql数据库中

从 Android 中的资产文件夹中检索数据

WatchKit:Swift-如何获取数据并将其放入iPhone应用程序文档文件夹中的sqlite数据库中

资产子文件夹中包含的android列表文件

Android从资产文件夹中读取pdf文件

Android-解析本地JSON数据(资产文件夹)

Android:如何检测资产文件夹中的目录?

在Android Studio中添加资产文件夹

哪里将“资产”文件夹放在Android Studio中?

从Android中的资产文件夹中选择图片

Android无法将数据库复制到资产文件夹

android-无法从资产文件夹复制数据库:API <24

Android:从资产文件夹复制数据库(Sqliite)时出错

直接从资产文件夹打开SQLite数据库

使用从资产文件夹导入角度

从资产文件夹复制数据库后,Android Pie中没有此类表错误

Android将表动态添加到资产文件夹中的现有数据库

引用资产文件夹中的图像

从资产的文件夹中调用FormTagHelper

子文件夹中的Nginx资产

从资产文件夹中读取音乐

无法从MySQL数据库检索数据并将其放在我的Android应用程序的列表视图中

备份/还原SQLlite数据库到Google云端硬盘应用文件夹

如何从内部存储或资产文件夹中播放android中加载的flash文件或swf文件?

如何从HTML文件的android资产文件夹中读取文件?

如何在 PHP 中导入 .ics 文件并将数据存储在数据库中?

创建预构建事件以将文件复制到Android应用程序中的资产文件夹

在Kotlin(Android)中以字符串形式读取文本资产(资产文件夹中的文本文件)

如何将True Type字体文件放入Android Studio的资产文件夹中