the SQLite database of Android based 14 data storage is detailed
SQLite is a lightweight relational database that works very fast, takes up less resources, and typically requires only hundreds of K of memory, making it ideal for use on mobile devices.
SQLite not only supports standard SQL syntax, but also follows the ACID (Atomicity (atomicity), consistency (consistency), isolation (isolation), persistence (durability)) Transactions of the database. So as long as you have used other relational database before, you can quickly on the ground hand SQLite. and SQLite than the general database is much simpler, it does not even need to set the user name and password can be used. Android is the very powerful database embedded in the system, so that the local persistence of the function has a qualitative leap.
1 Creating a database
Android in order to make it easier for us to manage the database, we specifically provide a Sqliteopenhelper helper class that allows you to create and upgrade the database very simply by using this class .
There are two construction methods in Sqliteopenhelper that can be overridden, with the general use of a less-parametric construction method that
Can This constructor receives four parameters, the first parameter is the Context second parameter is the database name, the database is created using the name specified here
Said. The third parameter allows us to return a custom Cursor when querying the data, usually passing null.
The fourth parameter represents the version number of the current database and can be used to upgrade the database
mydatabasehelper (context, "bookstore.db", NULL, 1);
<span Style= "FONT-SIZE:14PX;" >public class Mydatabasehelper extends Sqliteopenhelper {public static final String Create_book = "CREATE table book (" + "ID integer primary key autoincrement," + "author text," + "price real," + "pages integer," + "Name text)";p rivate Conte XT Mcontext;public Mydatabasehelper (context context, String name, cursorfactoryfactory, int version) {Super (context, Name, factory, version); mcontext = context;} @Overridepublic void OnCreate (Sqlitedatabase db) {db.execsql (Create_book); Toast.maketext (Mcontext, "Create succeeded", Toast.length_short). Show (); @Overridepublic void Onupgrade (sqlitedatabase db, int oldversion, int newversion) {}}</span>
2 Upgrade database
If you're careful enough, I'm sure you'll find an empty method in Mydatabasehelper! Yes, the Onupgrade () method is used to upgrade the database, it plays a very important role in the management of the whole database, it can not ignore it yo. At present, there is already a book table in the Databasetest project for storing the detailed data of the books, if we want to add a category table to record the classification of books.
Package Com.example.sqllitehelp;import Android.content.context;import android.database.sqlite.SQLiteDatabase; Import Android.database.sqlite.sqlitedatabase.cursorfactory;import Android.database.sqlite.SQLiteOpenHelper; Import Android.widget.toast;public class Mydatabasehelper extends Sqliteopenhelper {public static final String Create_ book= "CREATE TABLE book (" + "ID of integer primary key autoincrement," + "author test," + "price real," + "pages integer," + " Name text) ";p ublic static final String create_category=" CREATE table CATEGORY ("+" ID integer primary key autoincrement, "+" Category_name Test, "+" Category_code integer) ";p rivate context Mcontext;public mydatabasehelper (context context, String name,cursorfactory Factory, int version) {Super (context, name, Factory, version);//TODO auto-generated Constructo R Stubmcontext=context;} @Overridepublic void OnCreate (Sqlitedatabase db) {//TODO auto-generated method Stubdb.execsql (create_book);d B.execsql (create_category); Toast.maketext (Mcontext, "Create successed", Toast.length_short). Show (); @Overridepublic void Onupgrade (sqlitedatabase db, int oldversion, int newversion) {//TODO auto-generated method Stubdb.ex Ecsql ("drop table if exists book");d b.execsql ("drop table if exists Category"); onCreate (db); Toast.maketext (mcontext, "delete data", Toast.length_short). Show ();}}
Mydatabasehelper (Context, "bookstore.db", NULL, 1); Change the version number here to 2 to implement the upgrade method.
3 Adding Data
public class Mainactivity extends Activity {private mydatabasehelper dbhelper; @Overrideprotected void OnCreate (Bundle Savedinstancestate) {super.oncreate (savedinstancestate); Setcontentview (r.layout.activity_main);d bhelper = new Mydatabasehelper (This, "bookstore.db", NULL, 2);?? Button AddData = (button) Findviewbyid (r.id.add_data); Adddata.setonclicklistener (new Onclicklistener () {@ overridepublic void OnClick (View v) {sqlitedatabase db = Dbhelper.getwritabledatabase (); Contentvalues values = new Contentvalues ();//Start assembling the first Data values.put ("name", "the Da Vinci Code"), Values.put ("Author", "Dan Brown "); Values.put (" pages ", 454), Values.put (" Price ", 16.96);d B.insert (" book ", null, values); Insert the first Data values.clear ();//Start assembling the second data values.put ("name", "the Lost Symbol"), Values.put ("Author", "Dan Brown"); Values.put ("pages", 510); Values.put ("Price", 19.95);d B.insert ("book", null, values); Insert second Data}});}}
4 Updating Data
public Class Mainactivity extends Activity {private mydatabasehelper dbhelper; @Overrideprotected void OnCreate (Bundle Savedinstancestate) {super.oncreate (savedinstancestate); Setcontentview (r.layout.activity_main);d bhelper = new Mydatabasehelper (This, "bookstore.db", NULL, 2);?? Button UpdateData = (button) Findviewbyid (r.id.update_data); Updatedata.setonclicklistener (new Onclicklistener () {@ overridepublic void OnClick (View v) {sqlitedatabase db = Dbhelper.getwritabledatabase (); Contentvalues values = new Contentvalues () values.put ("Price", 10.99);d b.update ("book", Values, "name =?", new string[] { "The DaVinci Code"});}});
db.update ("book", Values, "name =?", new string[] {"The DaVinci Code"}) is equivalent to the =10.99 where Name= ' the DaVinci Code '
5 Deleting Data
<span style= "FONT-SIZE:14PX;" >public class Mainactivity extends Activity {private mydatabasehelper dbhelper; @Overrideprotected void OnCreate ( Bundle savedinstancestate) {super.oncreate (savedinstancestate); Setcontentview (r.layout.activity_main);d bhelper = New Mydatabasehelper (This, "bookstore.db", NULL, 2);?? Button DeleteButton = (button) Findviewbyid (r.id.delete_data);d Eletebutton.setonclicklistener (New Onclicklistener () {@Overridepublic void OnClick (View v) {sqlitedatabase db = Dbhelper.getwritabledatabase ();d b.delete ("book", "pages > ? ", new string[] {" 500 "});}}) </span>
db.delete ("book", "Pages >?", New string[] {"500"}), equivalent to delete from book where pages>500
6 query data
public class Mainactivity extends Activity {private mydatabasehelper dbhelper; @Overrideprotected void OnCreate (Bundle Savedinstancestate) {super.oncreate (savedinstancestate); Setcontentview (r.layout.activity_main);d bhelper = new Mydatabasehelper (This, "bookstore.db", NULL, 2);?? Button Querybutton = (button) Findviewbyid (r.id.query_data); Querybutton.setonclicklistener (new Onclicklistener () {@ overridepublic void OnClick (View v) {sqlitedatabase db = Dbhelper.getwritabledatabase ();//query all data in the Book table cursor cursor = Db.query ("book", NULL, NULL, NULL, NULL, NULL, NULL), if (Cursor.movetofirst ())} {do {//Traversal cursor object, take out data and print string name = C Ursor.getstring (Cursor.getcolumnindex ("name")); String author = cursor.getstring (Cursor.getcolumnindex ("author")); int pages = Cursor.getint (Cursor.getcolumnindex (" Pages "));d ouble price = Cursor.getdouble (" Cursor.getcolumnindex "); LOG.D ("Mainactivity", "book name is" + name); LOG.D ("Mainactivity", "book author is" + author); LOG.D ("Mainactivity", "book page"S is "+ pages); LOG.D ("Mainactivity", "book Price was" + price); while (Cursor.movetonext ());} Cursor.close ();}});}}
7 using SQL to manipulate databases
Let me briefly show you how to use SQL directly to complete the CRUD operations you've learned in the previous sections. Here's how to add data:
db.execsql ("insert into book (name, author, pages, price) VALUES (?,?,?,?)",
New string[] {"The Da Vinci Code", "Dan Brown", "454", "16.96"});
Db.execsql ("INSERT into book (name, author, pages, price) VALUES (?,?,?,?)",
New string[] {"The Lost Symbol", "D An Brown "," 510 "," 19.95 "}); The
method for updating data is as follows:
db.execsql ("update book Set price =?"). WHERE name =? ", new string[] {" 10.99 ",
" The Da Vinci Code "}); The
deletes data in the following ways:
db.execsql ("delete from book where pages >?", New string[] {"500"}); /span>
query data in the following ways:
db.rawquery ("SELECT * From book ", null); The
can see that, in addition to querying data, the Sqlitedatabase Rawquery () method is called, and the other operations
are called Execsql () methods. There are several ways to do this, and the results will be exactly the same as the CRUD operations we learned in the previous sections of
, choosing which method to use to see your personal preferences
8. Using Transactions
Imagine a scenario where, for example, you are making a transfer, the bank will deduct the amount of the transfer from your account before adding an equal amount to the recipient's account. Looks like there's no problem, does it? However, if the amount in your account has just been deducted, then due to some unusual causes of the other side of the collection failed, this part of the money is lost in thin air! Of course, the bank must have fully taken into account this situation, it will ensure that the operation of the deduction and collection is either successful or unsuccessful, and the technology used is of course the business.
<span style= "FONT-SIZE:14PX;" >public class Mainactivity extends Activity {private mydatabasehelper dbhelper; @Overrideprotected void OnCreate ( Bundle savedinstancestate) {super.oncreate (savedinstancestate); Setcontentview (r.layout.activity_main);d bhelper = New Mydatabasehelper (This, "bookstore.db", NULL, 2);?? Button ReplaceData = (button) Findviewbyid (r.id.replace_data); Replacedata.setonclicklistener (new Onclicklistener () { @Overridepublic void OnClick (View v) {sqlitedatabase db = Dbhelper.getwritabledatabase ();d b.begintransaction (); Turn on transaction try {db.delete ("book", NULL, NULL); if (true) {//) here manually throw an exception to let the transaction fail throw new NullPointerException ();} Contentvalues values = new Contentvalues (), Values.put ("name", "Game of Thrones"), Values.put ("Author", "George Martin"); Values.put ("pages", 720); Values.put ("Price", 20.85);d B.insert ("book", null, Values);d b.settransactionsuccessful (); Transaction executed successfully} catch (Exception e) {e.printstacktrace ();} finally {db.endtransaction ();//End Transaction}});} </span>≪span style= "Font-size:18px;color: #ff0000;" >}</span>
8.2 Best practices for upgrading Databases
just create a book table, and the code in Mydatabasehelper looks like this:
public class Mydatabasehelper extends Sqliteopenhelper {public static final String Create_book = "CREATE table book (" + "I d Integer PRIMARY key autoincrement, "+" author text, "+" price real, "+" pages integer, "+" Name text) ";p ublic MyDatabase Helper (context context, String name, cursorfactoryfactory, int version) {Super (context, name, Factory, version);} @Overridepublic void OnCreate (Sqlitedatabase db) {db.execsql (create_book);} @Overridepublic void Onupgrade (sqlitedatabase db, int oldversion, int newversion) {}}
However, a few weeks later there was a new need to add another Category table to the database. Then, modify the code in the Mydatabasehelper as follows:
public Class Mydatabasehelper extends Sqliteopenhelper {public static final String Create_book = "CREATE table book (" + "ID integ ER primary key AutoIncrement, "+" author text, "+" price real, "+" pages integer, "+" Name text) ";p ublic static final Stri ng create_category = "CREATE table CATEGORY (" + "ID integer primary key autoincrement," + "category_name text," + "Categor Y_code integer) ";p ublic mydatabasehelper (context context, String name,cursorfactory factory, int version) {Super ( Context, name, Factory, version);} @Overridepublic void OnCreate (Sqlitedatabase db) {db.execsql (Create_book);d b.execsql (create_category);} @Overridepublic void Onupgrade (sqlitedatabase db, int oldversion, int newversion) {switch (oldversion) {case 1:db.execsql (create_category);d Efault:}}}
It was not long before the new demand came again, this time to establish an association between the Book table and the Category table, you need to add a category_id field to the Book table. Modify the code in the Mydatabasehelper again, as shown below
public Class Mydatabasehelper extends Sqliteopenhelper {public static final String Create_book = "CREATE table book (" + "ID integ ER primary key AutoIncrement, "+" author text, "+" price real, "+" pages integer, "+" Name text, "+" category_id Integer) " ;p ublic static final String create_category = "CREATE table CATEGORY (" + "ID integer primary key autoincrement," + "Catego Ry_name text, "+" Category_code integer) ";p ublic mydatabasehelper (context context, String name,cursorfactory factory, int version) {Super (context, name, Factory, version);} @Overridepublic void OnCreate (Sqlitedatabase db) {db.execsql (Create_book);d b.execsql (create_category);} @Overridepublic void Onupgrade (sqlitedatabase db, int oldversion, int newversion) {switch (oldversion) {case 1:db.execsql (create_category); case 2:db.execsql ("ALTER TABLE book add column category_id integer");d Efault:}}}
As you can see, first we add a category_id column to the Book table's build statement so that when the user directly
When you install the third version of the program, this new column is automatically added successfully. However, if the user has previously
Install a version of the program, now need to overwrite the installation, will go into the upgrade database operation. In Onupgrade ()
method, we add a new case, and if the current database version number is 2, the ALTER command is executed to
A new category_id column is added to the Book table.
Note here a very important detail, the end of each case in switch is not using break,
Why did you do it? This is to ensure that every time the database is modified in a cross-version upgrade, it can be fully
The line. For example, the user is currently upgrading from the second version of the program to the third version of the program, then the logic in case 2 will be executed.
If the user is directly from the first version of the program to upgrade to the third version of the program, then case 1 and the logic in case 2 will be
Yes. Using this approach to maintain a database upgrade, regardless of how the version is updated, ensures that the table structure of the database is
Up-to-date, and the data in the table is not lost at all.
The
next article is about the encapsulation of the database, which is more advantageous to the operation of data.
The SQLite database of Android based 14 data storage is detailed