如何从在sqllite中创建和导出的assets文件夹android中放置数据库和读取数据库

how to put database and read database from assets folder android which are created and exported in sqllite

提问by Dardan

I am new in android development,I am making a database login through sqllite manager 0.8.1,and table user contain two field username and password,i am alredy filled field username and password now export data base and put in asset folder,now i am tryng to read database from assets folder and match field with user input if matche login succesful and not match login unsuccesfull.how can i do this,means read database from assets folder and matched with user input.please help me.thanx

我是android开发新手，我正在通过sqllite manager 0.8.1进行数据库登录，表user包含两个字段用户名和密码，我已经填写了字段用户名和密码，现在导出数据库并放入资产文件夹，现在我我正在尝试从资产文件夹中读取数据库并将字段与用户输入匹配，如果匹配登录成功并且不匹配登录不成功。我该怎么做，意味着从资产文件夹中读取数据库并与用户输入匹配。请帮助我。thanx

回答by Android

public class DataBaseHelper extends SQLiteOpenHelper {
    private SQLiteDatabase myDataBase;
    private final Context myContext;
    private static final String DATABASE_NAME = "YOURDBNAME";
    public final static String DATABASE_PATH = "/data/data/com.your.packagename/databases/";
    public static final int DATABASE_VERSION = 1;
    public DataBaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        this.myContext = context;

    }




    //Create a empty database on the system
    public void createDatabase() throws IOException
    {

          boolean dbExist = checkDataBase();

          if(dbExist)
          {
                Log.v("DB Exists", "db exists");
                // By calling this method here onUpgrade will be called on a
                // writeable database, but only if the version number has been
                // bumped
                //onUpgrade(myDataBase, DATABASE_VERSION_old, DATABASE_VERSION);
          }

          boolean dbExist1 = checkDataBase();
          if(!dbExist1)
          {
                this.getReadableDatabase();
                try
                {
                      this.close();    
                      copyDataBase();
                }
                catch (IOException e)
                {
                      throw new Error("Error copying database");
                }
          }

    }
    //Check database already exist or not
    private boolean checkDataBase()
    {
          boolean checkDB = false;
          try
          {
                String myPath = DATABASE_PATH + DATABASE_NAME;
                File dbfile = new File(myPath);
                checkDB = dbfile.exists();
          }
          catch(SQLiteException e)
          {
          }
          return checkDB;
    }
    //Copies your database from your local assets-folder to the just created empty database in the system folder
    private void copyDataBase() throws IOException
    {

        InputStream mInput = myContext.getAssets().open(DATABASE_NAME);
        String outFileName = DATABASE_PATH + DATABASE_NAME;
        OutputStream mOutput = new FileOutputStream(outFileName);
        byte[] mBuffer = new byte[2024];
        int mLength;
        while ((mLength = mInput.read(mBuffer)) > 0) {
            mOutput.write(mBuffer, 0, mLength);
        }
        mOutput.flush();
        mOutput.close();
        mInput.close();
    }
  //delete database
    public void db_delete()
    {
          File file = new File(DATABASE_PATH + DATABASE_NAME);
          if(file.exists())
          {
                file.delete();
                System.out.println("delete database file.");
          }
    }
  //Open database
    public void openDatabase() throws SQLException
    {
          String myPath = DATABASE_PATH + DATABASE_NAME;
          myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);
    }

    public synchronized void closeDataBase()throws SQLException
    {
          if(myDataBase != null)
                myDataBase.close();
          super.close();
    }
    @Override
    public void onCreate(SQLiteDatabase db) {
        // TODO Auto-generated method stub

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
         if (newVersion > oldVersion)
         {
               Log.v("Database Upgrade", "Database version higher than old.");
               db_delete();
         }

    }

}

And to implement it

并实施它

DataBaseHelper dbhelper = new DataBaseHelper(getApplicationContext());

        db = dbhelper.getWritableDatabase();

For that:

为了那个原因：

Cursor cur;
    cur_Herbs = db.rawQuery("select * from TABLENAME where  name like '"
            + edit_text_name.gettext.tostring() + "'", null);

        if (cur.moveToFirst()) {
            do {
                int name = cur.getColumnIndex("name");
                int pwd= cur.getColumnIndex("pwd");
str_name = cur.getString(name).toString();
                str_pwd= cur.getString(ped).toString();
if(str_name.equals(edittext_uname.gettext.tostring()and str_pwd.equals(edittext_pwd.gettext.tostring()))
{

//code for if loginn
}
} while (cur_Herbs.moveToNext());


}

回答by Dardan

For those who have problem to copy db from assets with KOTLIN:

对于那些使用 KOTLIN 从资产复制数据库有问题的人：

private val DB_PATH = "/data/data/**YOUR_PACKAGE_NAME**/databases/"
private val DB_NAME = "**YOUR_DB_NAME**.db"

private fun copyDataBaseFromAssets(context: Context) {

        var myInput: InputStream? = null
        var myOutput: OutputStream? = null
        try {

            val folder = context.getDatabasePath("databases")

            if (!folder.exists())
                if (folder.mkdirs()) folder.delete()

            myInput = context.assets.open("databases/$DB_NAME")

            val outFileName = DB_PATH + DB_NAME

            val f = File(outFileName)

            if (f.exists())
                return

            myOutput = FileOutputStream(outFileName)

            //transfer bytes from the inputfile to the outputfile
            val buffer = ByteArray(1024)
            var length: Int = myInput.read(buffer)

            while (length > 0) {
                myOutput!!.write(buffer, 0, length)
                length = myInput.read(buffer)
            }

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


        } catch (e: IOException) {
            e.printStackTrace()
        }

    }

回答by Prashant M

Use the following:

使用以下内容：

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();

}

回答by Abedalkareem Omreyh

Kotlin code for doing that, you can find the explanation for each line above the line with a comment, but I'll give a small summary about how it works.

用于执行此操作的 Kotlin 代码，您可以在带有注释的行上方找到每一行的说明，但我将对其工作原理进行简要总结。

How it gets created

它是如何创建的

When you create an object from DBHelperthe class will check if the database already copied from the assetsto the device if it's already copied, nothing will happen, but in case of it isn't exist on the device, the database will get copied to the device.

当您从DBHelper类创建对象时，将检查数据库是否已经从assets设备复制到设备，如果已经复制，则不会发生任何事情，但如果设备上不存在，数据库将被复制到设备.

How to use it

如何使用它

You can use it by creating a new instance from DBHelperthen you can call any query method inside, Please check the getQuestions method inside the DBHelper to know how to make a query.
val question = DBHelper(this).getQuestions(currentLevel)

您可以通过创建一个新实例来使用它，DBHelper然后您可以调用其中的任何查询方法，请检查 DBHelper 中的 getQuestions 方法以了解如何进行查询。
val question = DBHelper(this).getQuestions(currentLevel)

My DB structure for reference

我的数据库结构供参考

The code:

编码：

// Replace this string with your .sql/.db file name
const val dbName = "db_name.db" 

// Database version number, you need to change it in case of any schema
// change.
const val dbVersionNumber = 1

class DBHelper(private val context: Context) : SQLiteOpenHelper(context, dbName, null, dbVersionNumber) {

  private var dataBase: SQLiteDatabase? = null

  init {
    // Check if the database already copied to the device.
    val dbExist = checkDatabase()
    if (dbExist) {
      // if already copied then don't do anything.
      Log.e("-----", "Database exist")
    } else {
      // else copy the database to the device.
      Log.e("-----", "Database doesn't exist")
      createDatabase()
    }
  }

  override fun onCreate(db: SQLiteDatabase?) {
    // if you want to do anything after the database created
    // like inserting default values you can do it here.
  }

  override fun onUpgrade(db: SQLiteDatabase?, oldVersion: Int, newVersion: Int) { 
    // if you want to do anything after the database upgraded
    // like migrating values you can do it here.
  }

  // Copy the database
  private fun createDatabase() {
    copyDatabase()
  }

  // Check if the database already copied to the device.
  private fun checkDatabase(): Boolean {
    val dbFile = File(context.getDatabasePath(dbName).path)
    return dbFile.exists()
  }

  // Copy the database
  private fun copyDatabase() {

    val inputStream = context.assets.open("dbs/$dbName")

    val outputFile = File(context.getDatabasePath(dbName).path)
    val outputStream = FileOutputStream(outputFile)

    val bytesCopied = inputStream.copyTo(outputStream)
    Log.e("bytesCopied", "$bytesCopied")
    inputStream.close()

    outputStream.flush()
    outputStream.close()
  }

  // Open the database with read and write access mode.
  private fun openDatabase() {
    dataBase = SQLiteDatabase.openDatabase(context.getDatabasePath(dbName).path, null, SQLiteDatabase.OPEN_READWRITE)
  }

  // Close the database.
  override fun close() {
    dataBase?.close()
    super.close()
  }

  // An example of how you can make a query to get one item.
  // I have a database with one table called "questions"
  // I'm selecting one item at an index I'm passing to the
  // method.
  // My table has 6 columns: question, choice1, choice2, choice3
  // choice4, and correctAnswer.
  fun getQuestion(index: Int): Question {
    // before you make a query you need to open the database
    openDatabase()

    // Now you can make a query.
    val cursor = dataBase?.rawQuery("select * from questions limit 1 offset $index", null)

    // move to first will move the cursor to the first row.
    cursor?.moveToFirst()

    // now you can get the data from that row
    // all my columns is strings so i'm using "getString(index_of_column)"
    // but you can also use "getInt(index_of_column)" or any other
    // supported type.
    val question = cursor?.getString(0) ?: ""
    val choice1 = cursor?.getString(1) ?: ""
    val choice2 = cursor?.getString(2) ?: ""
    val choice3 = cursor?.getString(3) ?: ""
    val choice4 = cursor?.getString(4) ?: ""
    val correctAnswer = cursor?.getString(5) ?: ""

    // After using a cursor you need to close it, so the system can
    // release all its resources.
    cursor?.close()

    // After using the database you need to close it.
    close()

    return Question(question, choice1, choice2, choice3, choice4, correctAnswer)
  }

  // An example of how you can make a query to get all the items.
  // I have a database with one table called "questions"
  // My table has 6 columns: question, choice1, choice2, choice3
  // choice4, and correctAnswer.
  fun getAllQuestions(): MutableList<Question> {
    // before you make a query you need to open the database
    openDatabase()

    // Now you can make a query.
    val cursor = dataBase?.rawQuery("select * from questions", null)

    // move to first will move the cursor to the first row.
    cursor?.moveToFirst()

    val array = mutableListOf<Question>()

    do {
      // now you can get the data from that row
      // all my columns is strings so i'm using "getString(index_of_column)"
      // but you can also use "getInt(index_of_column)" or any other
      // supported type.
      val question = cursor?.getString(0) ?: ""
      val choice1 = cursor?.getString(1) ?: ""
      val choice2 = cursor?.getString(2) ?: ""
      val choice3 = cursor?.getString(3) ?: ""
      val choice4 = cursor?.getString(4) ?: ""
      val correctAnswer = cursor?.getString(5) ?: ""

      // Add the question to the array.
      array.add(Question(question, choice1, choice2, choice3, choice4, correctAnswer))

      // move to next will move the cursor to the next row if exist.
    } while (cursor?.moveToNext() == true)



    // After using a cursor you need to close it, so the system can
    // release all its resources.
    cursor?.close()

    // After using the database you need to close it.
    close()

    return array
  }


}

回答by yhAm

I also use that. I use a default database for my default data. Here is my sample code that works on my applicaiton.

我也用那个。我为我的默认数据使用默认数据库。这是适用于我的应用程序的示例代码。

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;

import android.content.Context;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;

public class DataBaseHelper extends SQLiteOpenHelper{

    //The Android's default system path of your application database.
    private static String DB_PATH = "/data/data/com.test/databases/";

    private static String DB_NAME = "testDatabase";

    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 DataBaseHelper(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();

    }

    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();

    }

        // Add your public helper methods to access and get content from the database.
       // You could return cursors by doing "return myDataBase.query(....)" so it'd be easy
       // to you to create adapters for your views.

}

You can use this as reference. Hope this will help you.

您可以将此作为参考。希望这会帮助你。