Design and lifting level of android-sqlite database

Source: Internet
Author: User
Tags sqlite database try catch

Google for Andriod's larger data processing provides SQLite, he is in the storage, management, maintenance and other aspects of the excellent, the function is very strong. SQLite has the following features:
1. Lightweight
With SQLite you only need to bring a dynamic library, you can enjoy its full functionality, and the size of the dynamic library want to be small.
2. Independence
The core engine of the SQLite database does not need to rely on third-party software, nor does it require the so-called "install".
3. Isolation
All information in the SQLite database (such as tables, views, triggers, etc.) is contained within a folder for easy administration and maintenance.
4. Cross-platform
SQLite currently supports most of the operating systems, not the computer operating system more in the many mobile phone systems can also be run, such as: Android.
5. Multi-lingual interface
The SQLite database supports multiple language programming interfaces.
6. Security

The SQLite database implements independent transaction processing through exclusive and shared locks at the database level. This means that multiple processes can read data from the same database at the same time, but only one can write data.

How to create a database, in Android development, to create a database for a project, you must inherit the Sqliteopenhelper class

First, we create a class to inherit the Sqliteopenhelper class

  

 1 package com.example.sqlltetest; 2 3 Import Android.content.Context; 4 Import Android.database.DatabaseErrorHandler; 5 Import Android.database.sqlite.SQLiteDatabase; 6 Import Android.database.sqlite.SQLiteDatabase.CursorFactory; 7 Import Android.database.sqlite.SQLiteOpenHelper; 8 Import Android.util.Log; 9 public class Mydatabaseopenorcreate extends sqliteopenhelper{11 private static final String db_name = "MyData. DB "; Database name: private static final int version = 1; Database version (+) public mydatabaseopenorcreate (context context) {$ super (context, db_name, NULL, Versi         ON):}20 @Override22 public void onCreate (Sqlitedatabase db) {log.i ("tag", "Welcome to join"); 24 String sql_table = "CREATE Table t_student (Stuid integer primary key autoincrement,stuname varchar)"; DB.E Xecsql (sql_table); string sql_1 = "INSERT into t_student (stuname) VALUES (' wavelet ')"; string sql_2 = "Inse RT into T_student (StuNAME) VALUES (' small wave '); sql_3 = "INSERT into t_student (stuname) VALUES (' Small wavelets ')"; Db.execsql (Sql_ 1); Db.execsql (sql_2), Db.execsql (sql_3),}33 @Override35 public void Onupgrade (sqlit Edatabase db, int oldversion, int newversion) {36 37}38 39}

If your project does not have a database, he will go OnCreate () method, if there is, he is not going to go oncreate () method, as for the Onupgrade () method, when you update the database is triggered, the above we test through the log print

Then we create an entity class to correlate the database

1 package com.example.entity; 2  3 public class Student {4  5     private string stuid; 6     private string stuname; 7     private int age;//in order to ascend Level prepared 8 public     String Getstuid () {9         return stuid;10}11 public     void Setstuid (String stuid) {         This.stuid = stuid;13     }14 public     String Getstuname () {return stuname;16}17 public     void Setstuname (String stuname) {         this.stuname = stuname;19     }20 public     int getage () {         return age;22     }23 public     void Setage (int.) {         this.age = age;25     }26     27}

We'll show the data directly through the program.

  

 1 package com.example.sqlltetest; 2 3 Import java.util.*; 4 5 Import Com.example.entity.student; 6 7 Import android.app.Activity; 8 Import Android.database.Cursor; 9 Import android.database.sqlite.sqlitedatabase;10 Import android.os.bundle;11 import android.util.log;12 Import ANDROID.VIEW.LAYOUTINFLATER;13 Import android.view.view;14 Import android.view.viewgroup;15 Import ANDROID.WIDGET.BASEADAPTER;16 Import android.widget.listview;17 Import android.widget.textview;18 public class Mainactivity extends Activity {$ private ListView lv;22 private list<student> List = new Arraylist<st Udent> (); @Override25 protected void OnCreate (Bundle savedinstancestate) {super.oncreate (Savedi Nstancestate); Setcontentview (r.layout.activity_main); Mydatabaseopenorcreate myDataBase = new Myda Tabaseopenorcreate (mainactivity.this); Sqlitedatabase db = Mydatabase.getwritabledatabase (); 3 2 log.i ("tAG "," CREATE Database Complete "); cursor cursor = db.query (" t_student ", NULL, NULL, NULL, NULL, NULL,34 null); 35 while (Cursor.movetonext ()) {Student s = new student (); PNs S.setstuname (cursor.getstring (Cursor.getcolumnindex ("Stuname"))); List.add (s),}41 db.close (), Findviewbyid (ListView) r.id.l ISTVIEW1) Lv.setadapter (new Baseadapter () {44 45///return number of records @Override47 p             ublic int GetCount () {//TODO auto-generated method stub49 return List.size (); 50 }51 52//per item item, returns one interface @Override54 public View getView (int position, view C Onvertview, ViewGroup Parent) {In view view = null;56 Layoutinflater Inflate       R = MainActivity.this.getLayoutInflater (); 59//Because GetView () returns an object, adapter is automatically assigned to LISTVIEW60          View = Inflater.inflate (R.layout.list_item, null), Student m = list.get (position); 63 64                 TextView tv_stuname = (TextView) view65. Findviewbyid (R.id.tv_stuname); 66              Tv_stuname.settext (M.getstuname ()); View;70}71 @Override73                 Public Object getItem (int position) {//TODO auto-generated method stub75                 Return null;76}77 @Override79 public long getitemid (int position) {80 TODO auto-generated Method stub81 return 0;82}83 84}); 85}86 87}

The results displayed:

  

When we first run, the log can be seen in the log before the OnCreate () method

But when we're running again, the logs are like this.

This shows that only when our database does not have this database, he will go OnCreate () method.

Because we are creating an app project that often changes data, we need to update the database.

Updated Ideas:

Current Version v1.0
1. No installation will go directly onCreate ()
--------------------------------------
Current version v2.0 [Onupgrade condition: N-1,oncreate condition: 1] Upgrade will go straight to the Onupgrade () method
1. v1.0--v2.0 Onupgrade
2. OnCreate () not installed
-----------------------------------------
Current version v3.0 [onupgrade condition: N-1,oncreate condition: 1]
1. v1.0-->v3.0 Onupgrade
ALTER TABLE t_message add column Isdel bit default 0;
Inserting data
2. v2.0-->v3.0 Onupgrade
ALTER TABLE t_message add column Isdel bit default 0;
3. OnCreate ()----------------------------------------------------------------------degraded design key not installed
1, consider the cloud to save users "custom data, behavior habits." Professional terminology profile-->> Increase user viscosity
2. Consider [current] minimum version requirements-->> reduce maintenance costs
3, as far as possible local data transfer (all new versions, do not delete the field)--as far as possible to change the unknown known
Try catch to upgrade the database, just in that Onupgrade () method, we change the previously defined version (number) to 2, and then modify the database in the Onupgrade () method
 1 package com.example.sqlltetest; 2 3 Import Android.content.Context; 4 Import Android.database.DatabaseErrorHandler; 5 Import Android.database.sqlite.SQLiteDatabase; 6 Import Android.database.sqlite.SQLiteDatabase.CursorFactory; 7 Import Android.database.sqlite.SQLiteOpenHelper; 8 Import Android.util.Log; 9 public class Mydatabaseopenorcreate extends sqliteopenhelper{11 private static final String db_name = "MyData. DB "; Database name: private static final int version = 2; Database version (+) public mydatabaseopenorcreate (context context) {$ super (context, db_name, NULL, Versi         ON):}20 @Override22 public void onCreate (Sqlitedatabase db) {log.i ("tag", "Welcome to join"); 24 String sql_table = "CREATE Table t_student (Stuid integer primary key autoincrement,stuname varchar)"; DB.E Xecsql (sql_table); string sql_1 = "INSERT into t_student (stuname) VALUES (' wavelet ')"; string sql_2 = "Inse RT into T_student (StuNAME) VALUES (' small wave '); sql_3 = "INSERT into t_student (stuname) VALUES (' Small wavelets ')"; Db.execsql (Sql_ 1); Db.execsql (sql_2), Db.execsql (sql_3),}33 @Override35 public void Onupgrade (sqlit Edatabase db, int oldversion, int newversion) {if (oldversion = = 1) {PNS String sql_upgrade_1 = "alte R table t_student add column age int default "; Db.execsql (Sql_upgrade_1); LOG.I (" db "," 1 to 2 , Upgrade success! "); 40}41 42}43 44 45}

Of course, the changes in the data, our display will be changed, we have to modify the previous mainactivity

 1 package com.example.sqlltetest; 2 3 Import java.util.*; 4 5 Import Com.example.entity.student; 6 7 Import android.app.Activity; 8 Import Android.database.Cursor; 9 Import android.database.sqlite.sqlitedatabase;10 Import android.os.bundle;11 import android.util.log;12 Import ANDROID.VIEW.LAYOUTINFLATER;13 Import android.view.view;14 Import android.view.viewgroup;15 Import ANDROID.WIDGET.BASEADAPTER;16 Import android.widget.listview;17 Import android.widget.textview;18 public class Mainactivity extends Activity {$ private ListView lv;22 private list<student> List = new Arraylist<st Udent> (); @Override25 protected void OnCreate (Bundle savedinstancestate) {super.oncreate (Savedi Nstancestate); Setcontentview (r.layout.activity_main); Mydatabaseopenorcreate myDataBase = new Myda Tabaseopenorcreate (mainactivity.this); Sqlitedatabase db = Mydatabase.getwritabledatabase (); 3 2 log.i ("tAG "," CREATE Database Complete "); cursor cursor = db.query (" t_student ", NULL, NULL, NULL, NULL, NULL,34 null); 35 while (Cursor.movetonext ()) {Student s = new student (); PNs S.setstuname (cursor.getstring (Cursor.getcolumnindex ("Stuname"))); S.setage (Cursor.getint (Cursor.getcolumnindex ("Age")), List.add (s), and}41 db.             Close (); Findviewbyid LV = (ListView) (r.id.listview1); Lv.setadapter (new Baseadapter () {44 45 Returns how many records @Override47 public int GetCount () {//TODO auto-generated Meth OD stub49 return list.size (); 50}51 52//Each item item, returns the interface at a time @Override5 4 public view GetView (int position, View Convertview, ViewGroup parent) {In view view = Null;5 6 57//layout unchanged, data becomes 58 59//If the cache is empty, we generate a new layout as 1 item60 layoutinflater Inflater = MainActivity.this.getLayoutInflater (); 61//Because the object returned by GetView (), adapter is automatically assigned to ListView62                 View = Inflater.inflate (R.layout.list_item, null), Student m = list.get (position); 65 66                 TextView tv_stuname = (TextView) view67. Findviewbyid (R.id.tv_stuname); 68 Tv_stuname.settext (M.getstuname ()); TextView tv_age = (TextView) View.findviewbyid (r.id.tv_age); 7 1 Tv_age.settext (M.getage () + ""), view;74}75 @Ove                 Rride77 public Object getItem (int position) {//TODO auto-generated method stub79                 Return null;80}81 @Override83 public long getitemid (int position) {84 TODO auto-generated Method stub85 return 0;86}87 88}); 89}90 91}

Let's look at the logs and the results:

Well, our database has been upgraded, so let's try the downgrade.

Let's change version (Good edition) to 1 first.

  

Package Com.example.sqlltetest;import Android.content.context;import Android.database.databaseerrorhandler;import Android.database.sqlite.sqlitedatabase;import Android.database.sqlite.sqlitedatabase.cursorfactory;import Android.database.sqlite.sqliteopenhelper;import Android.util.log;public class Mydatabaseopenorcreate extends sqliteopenhelper{private static final String db_name = "mydata.db";//Database name private static final int version = 1;    Database version Public Mydatabaseopenorcreate (context context) {Super (context, db_name, NULL, version);        } @Override public void OnCreate (Sqlitedatabase db) {log.i ("tag", "Welcome to join");        String sql_table = "CREATE Table t_student (Stuid integer PRIMARY key autoincrement,stuname varchar (50))";        Db.execsql (sql_table);        String Sql_1 = "INSERT into t_student (stuname) VALUES (' wavelet ')";        String sql_2 = "INSERT into t_student (stuname) VALUES (' small wave ')"; String sql_3 = "INSERT into T_student (stuname) valueS (' Little wavelet ') ";        Db.execsql (Sql_1);        Db.execsql (sql_2);    Db.execsql (Sql_3);            } @Override public void Onupgrade (sqlitedatabase db, int oldversion, int newversion) {if (oldversion = = 1) {            String sql_upgrade_1 = "ALTER TABLE t_student add column age int default 18";            Db.execsql (sql_upgrade_1); LOG.I ("db", "from 1 to 2, upgrade succeeded!")        ");  }} @Override public void Ondowngrade (sqlitedatabase db, int oldversion, int newversion) {try            {//First, first put t_message future table, rename String rename_sql = "ALTER TABLE t_student rename to T_student_bak";            Db.execsql (Rename_sql);            LOG.I ("Down", "1. Successful renaming"); Second, establish table structure for degraded table name String sql_message = "CREATE Table t_student (Stuid integer primary key Autoincrement,stuname V            Archar (50)) ";            Db.execsql (Sql_message);            LOG.I ("Down", "2. Build 2.0 Table Structure success"); Third, copy the backed up data to the newly created 2.0 table (the data from the previous table name is inserted into the degraded version) String Sql_copy = "INSERT INTO T_student select Stuname from T_student_bak";            Db.execsql (sql_copy);            LOG.I ("Down", "3.copy to user data to 2.0 table");            IV. Drop the backup table by dropping String drop_sql = "drop table if exists T_student_bak";            Db.execsql (Drop_sql);                    LOG.I ("Down", "4. Drop the backup table");            } catch (Exception e) {///If the above method does not work, take the most violent behavior, delete the table, re-create a previous version of the table log.i ("Hi", "downgrade failed, re-established");            String sql_drop_old_table = "drop table if exists t_student";            String sql_message = "CREATE Table t_student (Stuid integer PRIMARY key autoincrement,stuname varchar (50))";            String sql_init_1 = "INSERT into t_student (stuname) VALUES (' wavelet ')";            String sql_init_2 = "INSERT into t_student (stuname) VALUES (' xiaoming ')";            String sql_init_3 = "INSERT into t_student (stuname) VALUES (' Little Red ')";            Db.execsql (sql_drop_old_table);            Db.execsql (Sql_message);         Db.execsql (sql_init_1);   Db.execsql (sql_init_2);        Db.execsql (Sql_init_3); }    }}

 1 package com.example.sqlltetest; 2 3 Import java.util.*; 4 5 Import Com.example.entity.student; 6 7 Import android.app.Activity; 8 Import Android.database.Cursor; 9 Import android.database.sqlite.sqlitedatabase;10 Import android.os.bundle;11 import android.util.log;12 Import ANDROID.VIEW.LAYOUTINFLATER;13 Import android.view.view;14 Import android.view.viewgroup;15 Import ANDROID.WIDGET.BASEADAPTER;16 Import android.widget.listview;17 Import android.widget.textview;18 public class Mainactivity extends Activity {$ private ListView lv;22 private list<student> List = new Arraylist<st Udent> (); @Override25 protected void OnCreate (Bundle savedinstancestate) {super.oncreate (Savedi Nstancestate); Setcontentview (r.layout.activity_main); Mydatabaseopenorcreate myDataBase = new Myda Tabaseopenorcreate (mainactivity.this); Sqlitedatabase db = Mydatabase.getwritabledatabase (); 3 2 log.i ("tAG "," CREATE Database Complete "); cursor cursor = db.query (" t_student ", NULL, NULL, NULL, NULL, NULL,34 null); 35 while (Cursor.movetonext ()) {Student s = new student (); PNs S.setstuname (cursor.getstring (Cursor.getcolumnindex ("Stuname"))); //s.setage (Cursor.getint (Cursor.getcolumnindex ("Age")), List.add (s),}41 D             B.close (); Findviewbyid LV = (ListView) (r.id.listview1); Lv.setadapter (new Baseadapter () {44 45 Returns how many records @Override47 public int GetCount () {//TODO auto-generated Me Thod stub49 return list.size (); 50}51 52//Each item item, returns a interface of @Overrid E54 public view GetView (int position, View Convertview, ViewGroup parent) {View view = null ; 56 57//layout unchanged, data becomes 58 59//If the cache is empty, we generate a new layout as 1 item60 layoutinfLater inflater = MainActivity.this.getLayoutInflater (); 61//Because the object returned by GetView (), adapter is automatically assigned to ListView62                 View = Inflater.inflate (R.layout.list_item, null), Student m = list.get (position); 65 66                 TextView tv_stuname = (TextView) view67. Findviewbyid (R.id.tv_stuname); 68 Tv_stuname.settext (M.getstuname ());/*textview tv_age = (TextView) View.findviewbyid (r.id.tv_ag             e); Tv_age.settext (M.getage () + ""); */72 return view;74}75 76                 @Override77 public Object getItem (int position) {//TODO auto-generated method stub79                  Return null;80}81 @Override83 public long getitemid (int position) {84  TODO auto-generated Method stub85 return 0;86}87 88}); 89}90 91}

Results:

Because my data primary key is self-growing, there is no data-insertion data for the data-degraded table, so just delete the previous table and make a new one.

Design and lifting level of android-sqlite database

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.