· 7 years ago · Jan 05, 2019, 08:16 AM
1public class DBHelper extends SQLiteOpenHelper {
2
3 public static final String DBNAME = "mydb";
4 public static final int DBVERSION = 1;
5
6 public static final String TBL_MASTER = "master";
7 public static final String COL_ID = BaseColumns._ID;
8 public static final String COL_NAME = "name";
9 public static final String COL_OTHER = "other";
10 public static final String COL_MYBLOB = "myblob";
11 public static final String COL_MYFLOAT = "myfloat";
12
13
14 SQLiteDatabase mDB;
15
16 public DBHelper(Context context) {
17 super(context, DBNAME, null, DBVERSION);
18 mDB = this.getWritableDatabase();
19 }
20
21 @Override
22 public void onCreate(SQLiteDatabase db) {
23
24 String crt_table_sql = "CREATE TABLE IF NOT EXISTS " + TBL_MASTER + "(" +
25 COL_ID + " INTEGER PRIMARY KEY," +
26 COL_NAME + " TEXT," +
27 COL_OTHER + " TEXT," +
28 COL_MYBLOB + " BLOB," +
29 COL_MYFLOAT + " REAL " +
30 ")";
31 db.execSQL(crt_table_sql);
32 }
33
34 @Override
35 public void onUpgrade(SQLiteDatabase db, int i, int i1) {
36
37 }
38
39 public long insertData(String name, String other, byte[] myblob,float myfloat ) {
40 ContentValues cv = new ContentValues();
41 cv.put(COL_NAME,name);
42 cv.put(COL_OTHER,other);
43 cv.put(COL_MYBLOB,myblob);
44 cv.put(COL_MYFLOAT,myfloat);
45 return mDB.insert(TBL_MASTER,null,cv);
46 }
47
48 public Cursor getAll() {
49 return getFromTable(TBL_MASTER);
50 }
51
52 public Cursor getFromTable(String table_name) {
53 return mDB.query(table_name,null,null,null,null,null,null);
54 }
55
56 public boolean createTableBasedUponAnotherTable(String existing_table_name, String new_table_name) {
57 boolean rv = false;
58 String sql_column = "sql";
59 String whereclause = "name=? AND type = ?";
60 String[] whereargs = new String[]{existing_table_name,"table"};
61 String[] columns = new String[]{sql_column};
62 Cursor csr = mDB.query("sqlite_master",columns,whereclause,whereargs,null,null,null);
63 if (csr.moveToFirst()) {
64 String sql = csr.getString(csr.getColumnIndex(sql_column)).replace(existing_table_name,new_table_name).replace("CREATE TABLE","CREATE TABLE IF NOT EXISTS");
65 mDB.execSQL(sql);
66 rv = true;
67 }
68 csr.close();
69 return rv;
70 }
71
72 private boolean ifTableExists(String table_name) {
73 boolean rv = false;
74 String whereclause = "name=? AND type = ?";
75 String[] whereargs = new String[]{table_name,"table"};
76 Cursor csr = mDB.query("sqlite_master",null,null,null,null,null,null);
77 if (csr.moveToFirst()) {
78 rv = true;
79 }
80 csr.close();
81 return rv;
82 }
83
84 public boolean copyTableViaCursor(Cursor csr, String table_name) {
85 boolean rv = false;
86 mDB.beginTransaction();
87 ContentValues cv = new ContentValues();
88 int rows_processed;
89 if(!ifTableExists(table_name)) {
90 csr.moveToPosition(-1); // just in case position the cursor to before the first row
91 while (csr.moveToNext()) {
92 cv.clear();
93 for (int i=0; i < csr.getColumnCount(); i++) {
94 switch (csr.getType(i)) {
95 case Cursor.FIELD_TYPE_BLOB:
96 cv.put(csr.getColumnName(i),csr.getBlob(i));
97 break;
98 case Cursor.FIELD_TYPE_FLOAT:
99 cv.put(csr.getColumnName(i),csr.getFloat(i));
100 break;
101 case Cursor.FIELD_TYPE_INTEGER:
102 cv.put(csr.getColumnName(i),csr.getInt(i));
103 break;
104 case Cursor.FIELD_TYPE_STRING:
105 cv.put(csr.getColumnName(i),csr.getString(i));
106
107 }
108 }
109 mDB.insert(table_name,null,cv);
110 }
111 }
112 mDB.endTransaction();
113 return rv;
114 }
115}
116
117public class MainActivity extends AppCompatActivity {
118
119 DBHelper mDBHlpr;
120 Cursor csr;
121 String new_table = DBHelper.TBL_MASTER + "_new";
122
123 @Override
124 protected void onCreate(Bundle savedInstanceState) {
125 super.onCreate(savedInstanceState);
126 setContentView(R.layout.activity_main);
127
128 mDBHlpr = new DBHelper(this);
129 mDBHlpr.insertData("Fred","more about fred",new byte[]{7,8,4,55,67,127,126,99},321.76894F);
130 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);
131 mDBHlpr.insertData("Mary","definitely not something about Mary",new byte[]{},0.000000000000F);
132 DatabaseUtils.dumpCursor(csr = mDBHlpr.getAll());
133 mDBHlpr.createTableBasedUponAnotherTable(DBHelper.TBL_MASTER,new_table);
134 mDBHlpr.copyTableViaCursor(csr,new_table);
135 mDBHlpr.getFromTable(new_table);
136 DatabaseUtils.dumpCursor(csr);
137 csr.close();
138 }
139}
140
1412019-01-05 19:03:56.791 4211-4211/ptfc.populatetablefromcursor I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@268ba35
1422019-01-05 19:03:56.791 4211-4211/ptfc.populatetablefromcursor I/System.out: 0 {
1432019-01-05 19:03:56.791 4211-4211/ptfc.populatetablefromcursor I/System.out: _id=1
1442019-01-05 19:03:56.791 4211-4211/ptfc.populatetablefromcursor I/System.out: name=Fred
1452019-01-05 19:03:56.791 4211-4211/ptfc.populatetablefromcursor I/System.out: other=more about fred
1462019-01-05 19:03:56.792 4211-4211/ptfc.populatetablefromcursor I/System.out: myblob=<unprintable>
1472019-01-05 19:03:56.792 4211-4211/ptfc.populatetablefromcursor I/System.out: myfloat=321.769
1482019-01-05 19:03:56.792 4211-4211/ptfc.populatetablefromcursor I/System.out: }
1492019-01-05 19:03:56.792 4211-4211/ptfc.populatetablefromcursor I/System.out: 1 {
1502019-01-05 19:03:56.792 4211-4211/ptfc.populatetablefromcursor I/System.out: _id=2
1512019-01-05 19:03:56.792 4211-4211/ptfc.populatetablefromcursor I/System.out: name=Bert
1522019-01-05 19:03:56.792 4211-4211/ptfc.populatetablefromcursor I/System.out: other=something or other about bert
1532019-01-05 19:03:56.792 4211-4211/ptfc.populatetablefromcursor I/System.out: myblob=<unprintable>
1542019-01-05 19:03:56.792 4211-4211/ptfc.populatetablefromcursor I/System.out: myfloat=1.23457e+09
1552019-01-05 19:03:56.793 4211-4211/ptfc.populatetablefromcursor I/System.out: }
1562019-01-05 19:03:56.793 4211-4211/ptfc.populatetablefromcursor I/System.out: 2 {
1572019-01-05 19:03:56.793 4211-4211/ptfc.populatetablefromcursor I/System.out: _id=3
1582019-01-05 19:03:56.793 4211-4211/ptfc.populatetablefromcursor I/System.out: name=Mary
1592019-01-05 19:03:56.793 4211-4211/ptfc.populatetablefromcursor I/System.out: other=definitely not something about Mary
1602019-01-05 19:03:56.793 4211-4211/ptfc.populatetablefromcursor I/System.out: myblob=<unprintable>
1612019-01-05 19:03:56.793 4211-4211/ptfc.populatetablefromcursor I/System.out: myfloat=0
1622019-01-05 19:03:56.793 4211-4211/ptfc.populatetablefromcursor I/System.out: }
1632019-01-05 19:03:56.793 4211-4211/ptfc.populatetablefromcursor I/System.out: <<<<<
1642019-01-05 19:03:56.795 4211-4211/ptfc.populatetablefromcursor I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@268ba35
1652019-01-05 19:03:56.795 4211-4211/ptfc.populatetablefromcursor I/System.out: 0 {
1662019-01-05 19:03:56.796 4211-4211/ptfc.populatetablefromcursor I/System.out: _id=1
1672019-01-05 19:03:56.796 4211-4211/ptfc.populatetablefromcursor I/System.out: name=Fred
1682019-01-05 19:03:56.796 4211-4211/ptfc.populatetablefromcursor I/System.out: other=more about fred
1692019-01-05 19:03:56.796 4211-4211/ptfc.populatetablefromcursor I/System.out: myblob=<unprintable>
1702019-01-05 19:03:56.796 4211-4211/ptfc.populatetablefromcursor I/System.out: myfloat=321.769
1712019-01-05 19:03:56.796 4211-4211/ptfc.populatetablefromcursor I/System.out: }
1722019-01-05 19:03:56.796 4211-4211/ptfc.populatetablefromcursor I/System.out: 1 {
1732019-01-05 19:03:56.796 4211-4211/ptfc.populatetablefromcursor I/System.out: _id=2
1742019-01-05 19:03:56.796 4211-4211/ptfc.populatetablefromcursor I/System.out: name=Bert
1752019-01-05 19:03:56.796 4211-4211/ptfc.populatetablefromcursor I/System.out: other=something or other about bert
1762019-01-05 19:03:56.796 4211-4211/ptfc.populatetablefromcursor I/System.out: myblob=<unprintable>
1772019-01-05 19:03:56.796 4211-4211/ptfc.populatetablefromcursor I/System.out: myfloat=1.23457e+09
1782019-01-05 19:03:56.796 4211-4211/ptfc.populatetablefromcursor I/System.out: }
1792019-01-05 19:03:56.796 4211-4211/ptfc.populatetablefromcursor I/System.out: 2 {
1802019-01-05 19:03:56.796 4211-4211/ptfc.populatetablefromcursor I/System.out: _id=3
1812019-01-05 19:03:56.796 4211-4211/ptfc.populatetablefromcursor I/System.out: name=Mary
1822019-01-05 19:03:56.796 4211-4211/ptfc.populatetablefromcursor I/System.out: other=definitely not something about Mary
1832019-01-05 19:03:56.796 4211-4211/ptfc.populatetablefromcursor I/System.out: myblob=<unprintable>
1842019-01-05 19:03:56.796 4211-4211/ptfc.populatetablefromcursor I/System.out: myfloat=0
1852019-01-05 19:03:56.796 4211-4211/ptfc.populatetablefromcursor I/System.out: }
1862019-01-05 19:03:56.796 4211-4211/ptfc.populatetablefromcursor I/System.out: <<<<<