SQLite is largely unavoidable in Android development, and many times we use it as a means of storing data.
For example, we now, generally open the app, do not have some network operation, we can show the user before the last exit of the latest top 20 or how many data, some comments and so on. We can use SQLite to achieve this. The idea is: every time the user has a successful operation, we save the first 20 (according to the actual situation) of this refresh data, and then save the relevant data locally, through the database to maintain and obtain, so the user experience is very good, Because there is no need to open the app, there is a network request for the data to be displayed after success.
What we talked about this time is how to use SQLite to save data.
First you need to know something about sqlite, such as syntax, the type of field it allows, and so on (although SQLite can basically say no type, but still need to know)
See Connection: http://blog.csdn.net/liweijie_chengxuyuan/article/details/47031163
One of the most basic additions and deletions to eat, change the difference I do not mention here, with you to share some of the more advanced things, such as how to sort out the data, how to take from one location to another location data and so on. We can all use this, for example, we localize a certain type of data, use SQLite to save, and then we show that it is not possible to get it all at once from the local, so, we are when the user pull load, We'll go. Based on the data size of the list currently displayed as we start with the data from the database, then take 15 data at a time (self-determined), and then the data will need to be in a certain format in order to arrange this.
Here is an example:
The first is to build a table, I built here two tables, one is book, one is person, where book's primary key only one, using the auto-generated ID as the primary key, the person table's primary key is Age+id (mainly for testing).
The use of his creation table statement is as follows:
Database Help class: The general database needs to use the simple interest mode because many times the database has concurrency problems
Package Com.example.sqlite_demo.helper;import Android.content.context;import Android.database.sqlite.sqlitedatabase;import Android.database.sqlite.sqliteopenhelper;import Android.util.Log; public class Mysqlitehelper extends sqliteopenhelper{private static final String db_name = "MyDB";p rivate static final int VERSION = 1;private static final String drop = "Drop TABLE IF EXISTS";p ublic static final String book_table = "book";p UB Lic static final String Book_person = "Person";p rivate mysqlitehelper (context context) {Super (context, db_name, NULL, VERS ION);//Todo auto-generated constructor stub} @Overridepublic void OnCreate (Sqlitedatabase db) {//Todo auto-generated Method Stubdb.execsql (DROP + book_table); String Create_book = "CREATE TABLE" + book_table + "(" + "_id INTEGER PRIMARY KEY autoincrement, name TEXT, Price INT EGER "+") ";d B.execsql (Create_book);d b.execsql (DROP + Book_person); String Create_person = "CREATE TABLE" + Book_person + "(" + "name Text,age integer,birthday DATE,salary integer,id integer,primary KEY (age,id) "+") ";d B.execsql (Create_person); LOG.I ("Mysqlitehelper", "CREATE Table complete"); @Overridepublic void Onupgrade (sqlitedatabase db, int oldversion, int newversion) {//TODO auto-generated method Stubdb.ex Ecsql (drop + book_table);d b.execsql (drop + Book_person); onCreate (db);} private static Mysqlitehelper helper;/** * Use singleton mode * * @param context * @return */public static Mysqlitehelper getinstance (Context context) {if (null = = Helper) {synchronized (Mysqlitehelper.class) {if (null = = Helper) {helper = new Mysqlitehelper (context);}}} return helper;}}
Add Data:
Add public void Addperson (person per) {///write to make sure it is writable sqlitedatabase db = Mhelper.getwritabledatabase ();d B.execsql ("INSERT into" + Mysqlitehelper.book_person + "VALUES (?,?,?,?,?)", New object[]{Per.getname (), Per.getage (), Per.getbir Thday (), Per.getsalary (), Per.getid ()});} Add public void Addbook (book B) {//write to make sure it is writable sqlitedatabase db = Mhelper.getwritabledatabase ();d b.execsql (" INSERT into "+ mysqlitehelper.book_table + " (Name,price) VALUES (?,?) ", New object[]{B.getname (), B.getprice ()});}
This is Testsqlite, which inherits the Androidtestcase test class to add data, note that the date format of that column must be like me, cannot be "2015-1-1" and so on, and then, you notice the primary key problem, in the person table, age+ The ID is not the same when inserting the data, but in the Book table, you can have the same data, which is the different role of the primary key.
public void Testadddb () throws Exception{mysqliteservice service = new Mysqliteservice (GetContext ()); Person per = new Person ("A", 22, "1993-04-20", 10, 1); Person per1 = new Person ("B", 22, "1990-04-20", 109, 2); Person per2 = new Person ("C", 22, "1996-04-20", 108, 3); Person Per3 = new Person ("D", 22, "1998-04-10", 107, 4); Person per4 = new Person ("E", 22, "2000-04-20", 107, 5); Person per5 = new Person ("F", 23, "2015-04-20", 104, 1); Person PER6 = new Person ("G", 24, "2014-04-20", 101, 1); Person per7 = new Person ("H", "2005-04-20", 1), Service.addperson (per); Service.addperson (Per1); Service.addperson (PER2); Service.addperson (Per3); Service.addperson (per4); Service.addperson (PER5); Service.addperson (PER6); Service.addperson (Per7); Book B = new book ("1", 1); Book B1 = new book ("1", 1); Book b2 = new Book ("2", 3); Book B3 = new Book ("4", 1); Book B4 = new Book ("5", 1); Service.addbook (b); Service.addbook (b); Service.addbook (B1); Service.addbook (B2); Service.addbook (b3); Service.addbook (b4);}
As a result, the corresponding two tables
About deleting updates those actions you can look at my previous blog, here to introduce is a bit more advanced sqlite skills
After sorting through some of the things we need to get the results: for example, I need to sort by the order of birthdays, from small to large, and so on.
Get person and sort by date of birth public list<person> Getlistperson () {list<person> mlist = new arraylist<person> (); Sqlitedatabase db = Mhelper.getwritabledatabase (); cursor cursor = db.rawquery ("SELECT * from" + Mysqlitehelper.book_person + "ORDER by" + "BirthDay" + "DESC", NULL) if (Cursor.movetofirst ()) {Do{person per = new Person ();p er.setage (Cursor.getint (Cursor.getcolumnindex ("Age")); Per.setbirthday (Cursor.getstring (Cursor.getcolumnindex ("BirthDay")));p Er.setid (Cursor.getint ( Cursor.getcolumnindex ("id")));p Er.setname (cursor.getstring (Cursor.getcolumnindex ("name"));p er.setsalary ( Cursor.getint (Cursor.getcolumnindex ("salary"))); Mlist.add (per);} while (Cursor.movetonext ());} Cursor.close (); return mlist;}
Test code:
public void Testorderby () throws Exception{mysqliteservice service = new Mysqliteservice (GetContext ()); list<person> Listperson = Service.getlistperson (); for (person Per:listperson) {log.i (TAG, per.tostring ());}}
Results:
Where Desc is from large to small (date words are from go to far, to here) ASC is the opposite of Desc, where no instance is written
Second demand: We need to query data from our database, not all queries, such as I need to query 20, if the database is not spicy data, it is to find all
First, we need to know how many data the database has.
Query Countpublic int getpersoncount () {int count = 0; Sqlitedatabase db = Mhelper.getwritabledatabase (); cursor cursor = db.rawquery ("SELECT * from" + Mysqlitehelper.book_person, null); count = Cursor.getcount (); Cursor.close () ; return count;}
So you can get the
Now, we're inserting 100 new data into the database.
To test the code, you need to ensure that Age+id is unique:
public void Testadddb () throws Exception{mysqliteservice service = new Mysqliteservice (GetContext ()), for (int i = 0; I < ; 100; i++) {person per = new Person (i + "First name", I, "2015-08-30", I *, i); Service.addperson (per);}
Then, when we go to get the data, the principle of getting the data is, from which location to start, to which location to end
Code:
Public list<person> getlistpersonbylimit (int start, int length) {list<person> List = new Arraylist<person > (); Sqlitedatabase db = Mhelper.getwritabledatabase (); cursor cursor = db.rawquery ( "SELECT * from" + Mysqlitehelper.book_person + "Limit" + string.valueof (start) + ","
+ string.valueof (length), null), if (Cursor.movetofirst ()) {Do{person per = new Person ();p Er.setage (Cursor.getint ( Cursor.getcolumnindex ("Age")));p Er.setbirthday (cursor.getstring (Cursor.getcolumnindex ("BirthDay"));p Er.setid ( Cursor.getint (Cursor.getcolumnindex ("id")));p Er.setname (cursor.getstring ("name")); Per.setsalary (Cursor.getint (Cursor.getcolumnindex ("salary")); List.add (per);} while (Cursor.movetonext ());} Cursor.close (); return list;}
Test code:
public void Testlimit () throws Exception{mysqliteservice service = new Mysqliteservice (GetContext ()); LOG.I (TAG, "database current length size" + service.getpersoncount ()); list<person> Listperson = Service.getlistpersonbylimit (10, 19); LOG.I (Tag, "Gets the size of the list's length" + listperson.size ()); for (person Per:listperson) {LOG.I (tag, per.tostring ());}}
Result: The result is to start from the tenth of the database, go to 19 data, if there is no 19 data, then take out
Well, the ultimate question comes, that is, if I need to query the data from a certain location, the difference is 20, and then I need to follow what sort of ghost, and I query the condition is what and so on.
We first add some new data for the sake of testing convenience
public void Testadddb () throws Exception{mysqliteservice service = new Mysqliteservice (GetContext ()), for (int i = 0; I < ; 31; i++) {person per;//guaranteed date format correct if (I <) {per = new person ("Liweijie", I, "2015-08-0" + I, I *, i + +);} Else{per = n EW person ("Liweijie", I, "2015-08-" + I, I *, i + 300);} Service.addperson (per);}}
Now, we're going to get a list of 20 names called Liweijie, id>300, sorted by date from near to far.
Code implementation:
/** * @param start * Start position * @param length * Get the lengths * @param name * Query criteria * @param ID * Query Conditions * @param Birthday * Sorting method * @return */public list<person> getperson (int start, int length, String name, I NT ID) {list<person> List = new arraylist<person> (); Sqlitedatabase db = Mhelper.getwritabledatabase (); cursor cursor = db.rawquery ("SELECT * from" + Mysqlitehelper.book_person + "WHERE name =?") and ID >=? ORDER by BirthDay DESC limit "+ string.valueof (start) +", "+ string.valueof (length), new string[]{name, String.value Of (ID)}), if (Cursor.movetofirst ()) {Do{person per = new Person ();p er.setage (Cursor.getint (Cursor.getcolumnindex ("Age ));p Er.setbirthday (cursor.getstring (Cursor.getcolumnindex ("BirthDay"));p Er.setid (Cursor.getint ( Cursor.getcolumnindex ("id")));p Er.setname (cursor.getstring (Cursor.getcolumnindex ("name"));p er.setsalary ( Cursor.getint (Cursor.getcolumnindex ("salary"))); List.add (per);} while (Cursor.movetonext ());}cursor.close (); return list;}
Test Daim:
public void Testgetperson () throws Exception{mysqliteservice service = new Mysqliteservice (GetContext ()); LOG.I (TAG, "database current length size" + service.getpersoncount ()); list<person> Listperson = Service.getperson (0, "Liweijie", 300); LOG.I (Tag, "Gets the size of the list's length" + listperson.size ()); for (person Per:listperson) {LOG.I (tag, per.tostring ());}}
Well, write an afternoon, and then you will be in, and continue to share with you
Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.
Advanced use of SQLite in Android