SQLite assets database JAVA ANDROID

Question

I'm trying to take a sqlite database from assets folder to create a new database in system, so when you install the app on a device, it preloads some data -inserts- and the table form -create table-. I have created a class called 'SQLDataBase' which extends from SQLiteOpenHelper and it has the following body:

public class SQLDataBase extends SQLiteOpenHelper {
public static final String KEY_TITULO = "titulo";
private static final String KEY_COD="id";
private static final String KEY_GENERO="genero";
private static final String KEY_DIRECTOR="director";
private static final String KEY_DURACION="duracion";
private static final String TABLA_BASEDATOS = "peliculas";
private SQLiteDatabase myDataBase;
private final Context myContext;
private static final String DATABASE_NAME = "peliculass"; 
public final static String DATABASE_PATH ="/data/data/com.bdsqlite/databases/";
public static final int DATABASE_VERSION = 1;

String sqlCreate ="create table peliculas (id integer primary key autoincrement, titulo text not null, director text not null,genero text not null, duracion int not null)";

public SQLDataBase(Context contexto) {
    super(contexto, DATABASE_NAME, null, DATABASE_VERSION);
    this.myContext = contexto;
}


public void createDatabase() throws IOException
{

      boolean dbExist = checkDataBase();

      if(dbExist)
      {
            Log.v("DB Exists", "db exists");
      }

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

}

//Comprobamos si la base de datos existe ya
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;
}
//Copia la base de datos de la carpeta assets a la recien creada y vacia base de datos del sistema
private void copyDataBase() throws IOException
{
      String outFileName = DATABASE_PATH + DATABASE_NAME;

      OutputStream myOutput = new FileOutputStream(outFileName);
      InputStream myInput = myContext.getAssets().open(DATABASE_NAME);

      byte[] buffer = new byte[1024];
      int length;
      while ((length = myInput.read(buffer)) > 0)
      {
            myOutput.write(buffer, 0, length);
      }
      myInput.close();
      myOutput.flush();
      myOutput.close();
}

//elimina la base de datos
public void db_delete()
{
      File file = new File(DATABASE_PATH + DATABASE_NAME);
      if(file.exists())
      {
            file.delete();
            System.out.println("Base de datos eliminada.");
      }
}

//Abrir base de datos
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();
}

public void onCreate(SQLiteDatabase db)
{
}

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


public void insertarContacto(String titulo, String director, String genero, int duracion) {
    ContentValues valoresIniciales = new ContentValues();
    valoresIniciales.put(KEY_TITULO, titulo);
    valoresIniciales.put(KEY_DIRECTOR, director);
    valoresIniciales.put(KEY_GENERO, genero);
    valoresIniciales.put(KEY_DURACION, duracion);

    //return db.insert(TABLA_BASEDATOS, null, valoresIniciales);
    myDataBase.execSQL("INSERT INTO peliculas(titulo,director,genero,duracion) VALUES (titulo,director,genero,duracion)");
}

 public Cursor obtenerTodasLasPeliculas() {
    return myDataBase.query(TABLA_BASEDATOS, new String[] { KEY_COD, KEY_TITULO,KEY_DIRECTOR,KEY_GENERO,KEY_DURACION}, null, null, null, null, null);
}
 public Cursor obtenerInfo(String nombre) {
     return myDataBase.query(TABLA_BASEDATOS,new String[] { KEY_DIRECTOR,KEY_GENERO,KEY_DURACION} , "titulo=?", new String[] { nombre }, null, null, null);

}

 public void cerrar() {
    myDataBase.close();
}

public Cursor obtenerPeliculasGenero(String genre) {

    return myDataBase.query(TABLA_BASEDATOS, new String[] { KEY_COD, KEY_TITULO,KEY_DIRECTOR,KEY_DURACION}, "genero=?", new String[]{ genre }, null, null, null);

}

public void EliminarPelicula(String titulo){
    myDataBase.execSQL("DELETE FROM peliculas WHERE titulo='"+titulo+"'");

}

public void ModificarPelicula(String titulo, String director, String genero, int duracion, String titulo2){
    myDataBase.execSQL("UPDATE peliculas SET titulo='"+titulo+"', director='"+director+"',genero='"+genero+"',duracion="+duracion+" WHERE titulo='"+titulo2+"'");

}
}

Now at my main class:

SQLDataBase basededatos;

in method onCreate:

basededatos=new SQLDataBase(this);
mostrarpelicula();

---

public void mostrarPeliculas(){
    cont=0;
     basededatos.getReadableDatabase();
     Cursor c=basededatos.obtenerTodasLasPeliculas();
    resultados=new String[c.getCount()];
     if (c.moveToFirst()) {
            do {

                DisplayFilm(c);
            } while (c.moveToNext());
        }

     basededatos.cerrar(); 
}

public void DisplayFilm(Cursor c) {

    resultados[cont]=c.getString(1);
    cont++; 

}

It doesnt actually work, it says:

 java.lang.NullPointerException at "return myDataBase.query(TABLA_BASEDATOS,
 new String[] { KEY_COD, KEY_TITULO,KEY_DIRECTOR,KEY_GENERO,KEY_DURACION}, null, null, null, null, null);"

Can you help me?

Answer 1

I had a similar setup in one of my apps and the problem was the name of my database.

Try changing

private static final String DATABASE_NAME = "peliculass";

to

private static final String DATABASE_NAME = "peliculass.sqllite"; //or .sqllite3 or whatever the extension is

Answer 2

If you have a .db database on your hand, Then things are much easier. Its possible to convert an .sqlite database to .db database. After installation when your app first starts initialise the SuperDatabase object

SuperDatabase database=new SuperDatabase(getApplicationContext(),"foods.db", AssetDatabaseMode.COPY_TO_SYSTEM);

For subsequent app launches, use

SuperDatabase database=new SuperDatabase(getApplicationContext(),"foods.db", AssetDatabaseMode.READ_FROM_DEVICE);

Now just fire your non-returning querys

database.sqlInject("INSERT INTO food VALUES('Banana','Vitamin A');");

If you have some returning type queries, Then it is possible to fetch the result in CSV, JSON or in XML.

As CSV

ArrayList<String> rows=new ArrayList<String>();
rows=database.sqlEjectCSV("SELECT * FROM food;");
for (int i=0;i<rows.size();i++)
{
//Each row iterated with attribute values seperated by comma
}

Or as JSON

String json=database.sqlEjectJSON("SELECT * FROM food;");

Or as XML

String xml=database.sqlEjectXML("SELECT * FROM food;");

To use the SuperDatabase, You need to add this library to your Gradle. Here is the complete documentation : https://github.com/sangeethnandakumar/TestTube