I have an instance of sqlite.SQLiteCursor and a Table which have the same schema (same columns). I want to insert data from cursor into the table. I can extract data from cursor and use SQLiteDatabase#insert() method, but is there a method that does them internally? For example if I have multiple tables and related cursors, creating multiple methods, for inserting, for each pair is time consuming task.
is there a method that does them internally?
I don't believe so as a Cursor isn't necessarily the entire contents of a single table and often a Cursor can have derived columns (e.g. column_x + column y AS new_column) and columns from other joined tables.
It would be a relatively simple task to write a common method that if passed the table name and the Cursor to extract the column names (Cursor#getColumnNames()) and build the INSERT SQL for any cursor/table combination IF the Cursor and Table Column names matched (more difficult but possible to ignore column's not in the table perhaps utilising the table_info PRAGMA).
Saying that an intermediate Cursor isn't necessarily required as you could use INSERT INTO newtable SELECT * FROM old_table;
DBhelper.java
public class DBHelper extends SQLiteOpenHelper {
public static final String DBNAME = "mydb";
public static final int DBVERSION = 1;
public static final String TBL_MASTER = "master";
public static final String COL_ID = BaseColumns._ID;
public static final String COL_NAME = "name";
public static final String COL_OTHER = "other";
public static final String COL_MYBLOB = "myblob";
public static final String COL_MYFLOAT = "myfloat";
SQLiteDatabase mDB;
public DBHelper(Context context) {
super(context, DBNAME, null, DBVERSION);
mDB = this.getWritableDatabase();
}
@Override
public void onCreate(SQLiteDatabase db) {
String crt_table_sql = "CREATE TABLE IF NOT EXISTS " + TBL_MASTER + "(" +
COL_ID + " INTEGER PRIMARY KEY," +
COL_NAME + " TEXT," +
COL_OTHER + " TEXT," +
COL_MYBLOB + " BLOB," +
COL_MYFLOAT + " REAL " +
")";
db.execSQL(crt_table_sql);
}
@Override
public void onUpgrade(SQLiteDatabase db, int i, int i1) {
}
public long insertData(String name, String other, byte[] myblob,float myfloat ) {
ContentValues cv = new ContentValues();
cv.put(COL_NAME,name);
cv.put(COL_OTHER,other);
cv.put(COL_MYBLOB,myblob);
cv.put(COL_MYFLOAT,myfloat);
return mDB.insert(TBL_MASTER,null,cv);
}
public Cursor getAll() {
return getFromTable(TBL_MASTER);
}
public Cursor getFromTable(String table_name) {
return mDB.query(table_name,null,null,null,null,null,null);
}
public boolean createTableBasedUponAnotherTable(String existing_table_name, String new_table_name) {
boolean rv = false;
String sql_column = "sql";
String whereclause = "name=? AND type = ?";
String[] whereargs = new String[]{existing_table_name,"table"};
String[] columns = new String[]{sql_column};
Cursor csr = mDB.query("sqlite_master",columns,whereclause,whereargs,null,null,null);
if (csr.moveToFirst()) {
String sql = csr.getString(csr.getColumnIndex(sql_column)).replace(existing_table_name,new_table_name).replace("CREATE TABLE","CREATE TABLE IF NOT EXISTS");
mDB.execSQL(sql);
rv = true;
}
csr.close();
return rv;
}
private boolean ifTableExists(String table_name) {
boolean rv = false;
String whereclause = "name=? AND type = ?";
String[] whereargs = new String[]{table_name,"table"};
Cursor csr = mDB.query("sqlite_master",null,null,null,null,null,null);
if (csr.moveToFirst()) {
rv = true;
}
csr.close();
return rv;
}
public boolean copyTableViaCursor(Cursor csr, String table_name) {
boolean rv = false;
mDB.beginTransaction();
ContentValues cv = new ContentValues();
int rows_processed;
if(!ifTableExists(table_name)) {
csr.moveToPosition(-1); // just in case position the cursor to before the first row
while (csr.moveToNext()) {
cv.clear();
for (int i=0; i < csr.getColumnCount(); i++) {
switch (csr.getType(i)) {
case Cursor.FIELD_TYPE_BLOB:
cv.put(csr.getColumnName(i),csr.getBlob(i));
break;
case Cursor.FIELD_TYPE_FLOAT:
cv.put(csr.getColumnName(i),csr.getFloat(i));
break;
case Cursor.FIELD_TYPE_INTEGER:
cv.put(csr.getColumnName(i),csr.getInt(i));
break;
case Cursor.FIELD_TYPE_STRING:
cv.put(csr.getColumnName(i),csr.getString(i));
break;
}
}
mDB.insert(table_name,null,cv);
}
mDB.setTransactionSuccessful();
}
mDB.endTransaction();
return rv;
}
}
MainActivity.java
public class MainActivity extends AppCompatActivity {
DBHelper mDBHlpr;
Cursor csr;
String new_table = DBHelper.TBL_MASTER + "_new";
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
mDBHlpr = new DBHelper(this);
mDBHlpr.insertData("Fred","more about fred",new byte[]{7,8,4,55,67,127,126,99},321.76894F);
mDBHlpr.insertData("Bert","something or other about bert",new byte[]{1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0},1234567890.675342F);
mDBHlpr.insertData("Mary","definitely not something about Mary",new byte[]{},0.000000000000F);
DatabaseUtils.dumpCursor(csr = mDBHlpr.getAll());
mDBHlpr.createTableBasedUponAnotherTable(DBHelper.TBL_MASTER,new_table);
mDBHlpr.copyTableViaCursor(csr,new_table);
csr = mDBHlpr.getFromTable(new_table);
DatabaseUtils.dumpCursor(csr);
csr.close();
}
}
2019-01-05 19:03:56.791 4211-4211/ptfc.populatetablefromcursor I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@268ba35
2019-01-05 19:03:56.791 4211-4211/ptfc.populatetablefromcursor I/System.out: 0 {
2019-01-05 19:03:56.791 4211-4211/ptfc.populatetablefromcursor I/System.out: _id=1
2019-01-05 19:03:56.791 4211-4211/ptfc.populatetablefromcursor I/System.out: name=Fred
2019-01-05 19:03:56.791 4211-4211/ptfc.populatetablefromcursor I/System.out: other=more about fred
2019-01-05 19:03:56.792 4211-4211/ptfc.populatetablefromcursor I/System.out: myblob=<unprintable>
2019-01-05 19:03:56.792 4211-4211/ptfc.populatetablefromcursor I/System.out: myfloat=321.769
2019-01-05 19:03:56.792 4211-4211/ptfc.populatetablefromcursor I/System.out: }
2019-01-05 19:03:56.792 4211-4211/ptfc.populatetablefromcursor I/System.out: 1 {
2019-01-05 19:03:56.792 4211-4211/ptfc.populatetablefromcursor I/System.out: _id=2
2019-01-05 19:03:56.792 4211-4211/ptfc.populatetablefromcursor I/System.out: name=Bert
2019-01-05 19:03:56.792 4211-4211/ptfc.populatetablefromcursor I/System.out: other=something or other about bert
2019-01-05 19:03:56.792 4211-4211/ptfc.populatetablefromcursor I/System.out: myblob=<unprintable>
2019-01-05 19:03:56.792 4211-4211/ptfc.populatetablefromcursor I/System.out: myfloat=1.23457e+09
2019-01-05 19:03:56.793 4211-4211/ptfc.populatetablefromcursor I/System.out: }
2019-01-05 19:03:56.793 4211-4211/ptfc.populatetablefromcursor I/System.out: 2 {
2019-01-05 19:03:56.793 4211-4211/ptfc.populatetablefromcursor I/System.out: _id=3
2019-01-05 19:03:56.793 4211-4211/ptfc.populatetablefromcursor I/System.out: name=Mary
2019-01-05 19:03:56.793 4211-4211/ptfc.populatetablefromcursor I/System.out: other=definitely not something about Mary
2019-01-05 19:03:56.793 4211-4211/ptfc.populatetablefromcursor I/System.out: myblob=<unprintable>
2019-01-05 19:03:56.793 4211-4211/ptfc.populatetablefromcursor I/System.out: myfloat=0
2019-01-05 19:03:56.793 4211-4211/ptfc.populatetablefromcursor I/System.out: }
2019-01-05 19:03:56.793 4211-4211/ptfc.populatetablefromcursor I/System.out: <<<<<
2019-01-05 19:03:56.795 4211-4211/ptfc.populatetablefromcursor I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@268ba35
2019-01-05 19:03:56.795 4211-4211/ptfc.populatetablefromcursor I/System.out: 0 {
2019-01-05 19:03:56.796 4211-4211/ptfc.populatetablefromcursor I/System.out: _id=1
2019-01-05 19:03:56.796 4211-4211/ptfc.populatetablefromcursor I/System.out: name=Fred
2019-01-05 19:03:56.796 4211-4211/ptfc.populatetablefromcursor I/System.out: other=more about fred
2019-01-05 19:03:56.796 4211-4211/ptfc.populatetablefromcursor I/System.out: myblob=<unprintable>
2019-01-05 19:03:56.796 4211-4211/ptfc.populatetablefromcursor I/System.out: myfloat=321.769
2019-01-05 19:03:56.796 4211-4211/ptfc.populatetablefromcursor I/System.out: }
2019-01-05 19:03:56.796 4211-4211/ptfc.populatetablefromcursor I/System.out: 1 {
2019-01-05 19:03:56.796 4211-4211/ptfc.populatetablefromcursor I/System.out: _id=2
2019-01-05 19:03:56.796 4211-4211/ptfc.populatetablefromcursor I/System.out: name=Bert
2019-01-05 19:03:56.796 4211-4211/ptfc.populatetablefromcursor I/System.out: other=something or other about bert
2019-01-05 19:03:56.796 4211-4211/ptfc.populatetablefromcursor I/System.out: myblob=<unprintable>
2019-01-05 19:03:56.796 4211-4211/ptfc.populatetablefromcursor I/System.out: myfloat=1.23457e+09
2019-01-05 19:03:56.796 4211-4211/ptfc.populatetablefromcursor I/System.out: }
2019-01-05 19:03:56.796 4211-4211/ptfc.populatetablefromcursor I/System.out: 2 {
2019-01-05 19:03:56.796 4211-4211/ptfc.populatetablefromcursor I/System.out: _id=3
2019-01-05 19:03:56.796 4211-4211/ptfc.populatetablefromcursor I/System.out: name=Mary
2019-01-05 19:03:56.796 4211-4211/ptfc.populatetablefromcursor I/System.out: other=definitely not something about Mary
2019-01-05 19:03:56.796 4211-4211/ptfc.populatetablefromcursor I/System.out: myblob=<unprintable>
2019-01-05 19:03:56.796 4211-4211/ptfc.populatetablefromcursor I/System.out: myfloat=0
2019-01-05 19:03:56.796 4211-4211/ptfc.populatetablefromcursor I/System.out: }
2019-01-05 19:03:56.796 4211-4211/ptfc.populatetablefromcursor I/System.out: <<<<<