Question:
I need to get the values contained in the columns of a pre-created database.
Tried in two ways:
- created a successor to SQLiteOpenHelper,
- tried to use android-sqlite-asset-helper library.
I am trying to execute the data through the cursor:
Cursor cursor = myDataBase.query("таблица",new String[]{
"колонка1",
"колонка2",
"колонка3",
"колонка4",
"колонка5",
"колонка6",
"колонка7",
"колонка8",
"колонка9"}, null, null, null, null, null, null);
String name = cursor.getString(cursor.getColumnIndex("колонка1");
In both cases, an error is thrown
: no such table "my table name".
There is a table – I checked it through the DB Browser, and created the database through it.
As I understand it, he simply does not see this database. I throw the base into assets (I was confused that this folder was not in Android Studio 1.5.1 – I created it using New / Assets Folder).
I am using this code . I'm testing on Android 4.1
Answer:
As I did.
- Put the database in the assets folder
-
I have a class for working with DB
import java.io.FileOutputStream; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream;
import android.content.Context; import android.database.Cursor; import android.database.SQLException; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteException; import android.database.sqlite.SQLiteOpenHelper;
public class ExternalDbOpenHelper extends SQLiteOpenHelper {
//Путь к папке с базами на устройстве
public static String DB_PATH;
//Имя файла с базой
public static String DB_NAME;
public SQLiteDatabase database;
public final Context context;
//private boolean flagdel = true;
public SQLiteDatabase getDb() {
return database;
}
public ExternalDbOpenHelper(Context context, String databaseName) {
super(context, databaseName, null, 1);
this.context = context;
//Составим полный путь к базам для нашего приложения
DB_PATH = context.getDatabasePath(databaseName).getAbsolutePath();
DB_NAME = databaseName;
openDataBase();
}
/**Создаст базу, если она не создана и откроет ее*/
public void createDataBase() {
boolean dbExist = checkDataBase();
if (!dbExist) {
this.getReadableDatabase();//Создает и/или открывает базу данных
try {copyDataBase();
} catch (IOException e) {
throw new Error("Error copying database!");
}
}
}
/**Проверка существования базы данных*/
private boolean checkDataBase() {
SQLiteDatabase checkDb = null;
String path = DB_PATH + DB_NAME;
try {
checkDb = SQLiteDatabase.openDatabase(path, null, SQLiteDatabase.OPEN_READONLY);
} catch (SQLiteException e) {}
//Андроид не любит утечки ресурсов, все должно закрываться
if (checkDb != null) checkDb.close();
return checkDb != null;
}
/**метод проверки наличия в БД таблицы*/
public boolean checkTableInDB(String nameTable){
boolean result = false;
Cursor cur = database.query("sqlite_master", new String[]{"name"}, "type=? AND name=?", new String[]{"table",nameTable}, null, null, null);
cur.moveToLast();
if(cur.getCount()>0) result = true;
cur.close();
return result;
}
/**метод проверки наличия поля в таблице*/
public boolean checkFieldInTable(String nameTable, String nameField){
boolean result = false;
try{
Cursor cur = database.rawQuery("PRAGMA table_info('"+nameTable+"')", null);
cur.moveToFirst();
while(!cur.isAfterLast()) {
String name = "";
try{name = cur.getString(cur.getColumnIndexOrThrow("name"));
}catch(IllegalArgumentException e){}
if(name.equals(nameField)){
result = true;
break;
}
cur.moveToNext();
}
cur.close();
cur = null;
}catch(SQLException e){}
return result;
}
/**Метод копирования базы*/
private void copyDataBase() throws IOException {
// Открываем поток для чтения из уже созданной нами БД источник в assets
InputStream externalDbStream = context.getAssets().open(DB_NAME);
// Путь к уже созданной пустой базе в андроиде
String outFileName = DB_PATH + DB_NAME;
// Теперь создадим поток для записи в эту БД побайтно
OutputStream localDbStream = new FileOutputStream(outFileName);
// Собственно, копирование
byte[] buffer = new byte[1024];
int bytesRead;
while ((bytesRead = externalDbStream.read(buffer)) > 0) {
localDbStream.write(buffer, 0, bytesRead);
}
// Мы будем хорошими мальчиками (девочками) и закроем потоки
localDbStream.close();
externalDbStream.close();
}
/** Метод получения всех данных из таблицы*/
public Cursor getAllData(String dbTable) {
return database.query(dbTable, null, null, null, null, null, null);
}
/** Метод получения данных из таблицы по условию*/
public Cursor getDataByWhere(String table, String[] columns, String where, String[] where_args) {
return database.query(table, columns, where, where_args, null, null, null);
}
/**Метод открытия БД*/
public SQLiteDatabase openDataBase() throws SQLException {
String path = DB_PATH + DB_NAME;
if (database == null) {
createDataBase();
database = SQLiteDatabase.openDatabase(path, null, SQLiteDatabase.OPEN_READWRITE);
}
return database;
}
@Override
public synchronized void close() {
if (database != null) {
database.close();
}
super.close();
}
@Override
public void onCreate(SQLiteDatabase db) {}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {}
}
-
I get data anywhere in the code I need. Below is the method for getting the data. The point is that if a database with a specific version is not found, then it will be copied to the folder with the installed application and then a link to it will be obtained for further use. Very comfortably. I advise.
private void getDataFromDB(){ ExternalDbOpenHelper dbOpenHelper = new ExternalDbOpenHelper(context, DB_NAME); SQLiteDatabase db = dbOpenHelper.getDb(); String orderby = MeData.getOrderBy(); String table = (!mode.equals(MODE_PREV))?MeData.getTable():MeData.getTable_prev(); String columns[] = MeData.getColumns(); Cursor cur = null; try{cur = db.query(table, columns, where, where_args, null, null, orderby); }catch(Exception e){ if(cur != null) cur.close(); cur= null; if(db != null) db.close(); db = null; if(dbOpenHelper != null) dbOpenHelper.close(); dbOpenHelper = null; return; } cur.moveToFirst(); while(!cur.isAfterLast()) { try{ int ND = cur.getInt(cur.getColumnIndex("ND")); int NM = cur.getInt(cur.getColumnIndex("NM")); int NY = cur.getInt(cur.getColumnIndex("NY")); } catch(Exception e) {} cur.moveToNext(); } if(cur != null) cur.close(); cur= null; if(db != null) db.close(); db = null; if(dbOpenHelper != null) dbOpenHelper.close(); dbOpenHelper = null;}
PS I can’t really insert the code … I’m sorry, you’ll figure it out …