Android Greendao Framework some advanced usage finishing

Tag: Offset while creat returns from the This Super project GRE

Broadly divided into the following areas:

• Some query order collation
• Use SQL statements for special queries
• Detecting the existence of a table field
• Database Upgrade
• To assign an initial value to a database table field

First, the query order Collation 1. Chained execution Instructions

        Return Mdaosession.getuserdao (). QueryBuilder ().                XXX.                XXX.                XXX.                List ();

General query statements in the middle of the location of XXX plus a variety of judgment and filtering method instructions, in addition to the final command list () or unique () return is a collection or business object, the other is to return the QueryBuilder object, In most cases, the location of xxx is filled with the where statement, and there are other statements associated with SQL that are easy to use.

The "Whereor" where statement is written in a "and" connection, the statement in the Whereor uses "or" connection

"Distinct" directly filters out the heavy field

"Limit" page n one page, general and offset used in combination

"Offset" ignores the first n results of a query

"Orderasc" sorted by word orderby order

"Orderdesc" with field descending

"Preferlocalizedstringorder" localized string ordering

The "OrderCustom" custom sort needs to pass two parameters: an attribute and the corresponding ordering scheme ASC or DESC

"Orderraw" is also a custom sort, which writes fields and sorting schemes into a string passed in

"Stringordercollation" is also a custom sort you can combine multiple ascending and descending sorting schemes with the

2. Instructions in the condition

        Return Mdaosession.getuserdao (). QueryBuilder ().                Where (UserDao.Properties.UserId.in (useridlist), UserDao.Properties.UserAge.eq ()).                List ();

A simple where statement is probably like this, where the parentheses can be written in parallel with many conditions, all of which are "and" to connect. In addition to the above "in" and "EQ" there are many other criteria for judging

"Noteq" and EQ instead, don't be silly to go outside knocking! " "Take the reverse

"Notin" Ibid.

"or" or

"Like" is the SQL statement like "%" +string+"%"

"Between" is between?  and? Can take two values of the interval (but this statement to use with caution, different databases, some a< conditions <b, some a<= conditions <=b)

"GT" equivalent to >

"GE" is equivalent to >=

"LT" is equivalent to <

"Le" is equivalent to <=

"IsNull" is empty

"Notisnull" is not empty

Second, using SQL statements for special queries

Generally encountered the ordinary deletion and modification of the operation can not easily realize the function, will use this rawquery way. There are two scenarios that I often encounter:

1. Using Select DISTINCT

Common with a one-to-many relationship, if the library now has a "User Book table" Some users have 20 books, the table will have 20 of his data, each corresponding to a different book.

At this point, suppose there is a need, to find out the table, all the user names, not to repeat. This time with ordinary query instructions will be very troublesome, you need to use the SELECT DISTINCT command to find out a column name all the non-duplicate entries.

 String queryString = "Select DISTINCT" + Userbookdao.properti Es. Username.columnname + "from" + Userbookdao.tablename + "ORDER by" + User                        BookDao.Properties.CreatedTime + "DESC" + "LIMIT"        + page * limit_num + "," + limit_num;        arraylist<string> result = new arraylist<> ();        Cursor C = mdaosession.getdatabase (). Rawquery (Querystring,new string[]{}); try {if (c! = null) {if (C.movetofirst ()) {do {res                    Ult.add (c.getstring (0));                } while (C.movetonext ());            }}} finally {if (c! = null) {c.close (); }        }

Probably the code of the picture of the wind is like this, first splicing a string that conforms to the SQL syntax, the above is also randomly added to another operation instruction field sorting and paging, so that the query instructions appear more complete, and then use the cursor to receive the above query results.

Perhaps most of the time when the query will take some parameters, such as where xx = XX filter conditions, if there is a need for the previous query user needs to add to the publisher and book price restrictions, then the query method as follows

        String queryString = "Select DISTINCT" + UserBookDao.Properties.UserName.columnName + "from" + U Serbookdao.tablename//Dong Platinum Blog Park + "WHERE" + UserBookDao.Properties.Publis                        Her.columnname + "=?"                        + "+" + UserBookDao.Properties.Price.columnName + ">?"        + "ORDER by" + UserBookDao.Properties.CreatedTime + "DESC";        arraylist<string> result = new arraylist<> ();        Cursor C = mdaosession.getdatabase (). Rawquery (queryString, New string[]{"one publisher"), String.valueof (100.00)});                        try {if (c! = null) {if (C.movetofirst ()) {do {                    Result.add (c.getstring (0));                } while (C.movetonext ()); }}} finally {if (c! = null) {c.clOSE (); }        }

The query string with the parameter needs to use the question mark placeholder above, and then fill in the relevant arguments with the API with the Rawquery parameter below.

2. Select query multiple fields at the same time

Or use this example to check the book, suppose to look for "title", "publisher", "Price" three fields

        String queryString = "Select" + Userbookdao.tablename + "." + UserBookDao.Properties.BookName.colu                Mnname + "," + Userbookdao.tablename + "." + UserBookDao.Properties.Publisher.columnName + ","  + Userbookdao.tablename + "." + UserBookDao.Properties.Price.columnName + "" + "from" +        Userbookdao.tablename + "" + "WHERE" + UserBookDao.Properties.Price + "> 100.00";        cursor cursor = NULL;            try {cursor = Session.getdatabase (). Rawquery (Querystring,new string[]{});            if (cursor = = null) {return paymap; }//Remove the index for each of the three fields, and then go to the index to take the value int nameindex = Cursor.getcolumnindex (UserBookDao.Properties.BookName.            ColumnName);            int publisherindex = Cursor.getcolumnindex (UserBookDao.Properties.Publisher.columnName); int priceindex = Cursor.getcolumnindex (UserBookDao.Properties.Price.columNname); if (nameindex! =-1 && publisherindex! =-1 && priceindex! =-1) {while (Cursor.movetonext                    ()) {String name = cursor.getstring (Nameindex);                    String publisher = Cursor.getstring (Publisherindex);                    Double Price = Cursor.getdouble (Priceindex);                This takes three fields to save the model or the dictionary to handle itself.            }}} finally {if (null! = cursor) {cursor.close (); }        }

The following can be used to take out three required fields at a time, you need to get the field corresponding index, and then the index value.

Third, the detection table field exists

        Private Boolean Hascolumn (Sqlitedatabase db, String tableName, String column) {            if (textutils.isempty (tableName) | | | Textutils.isempty (column)) {                return false;            }            cursor cursor = NULL;            try {                cursor = db.query (tableName, NULL, NULL, NULL, NULL, NULL, NULL);                if (null! = Cursor && cursor.getcolumnindex (column)! =-1) {                    return true;                }            } finally {                if (null ! = cursor) {                    cursor.close ();                }            }            return false;        }

Similar to the way the cursor was fetched, the column index value taken out is not-1, then the delegate can take this field to return true, otherwise the fasle is returned illegally together with the entry argument.

Iv. Database Upgrade

This will invoke the method above to determine if the column name already exists.

Then override this Onupgrade method of the parent class

    private static class Demoopenhelper extends Daomaster.openhelper {public Demoopenhelper (context context, Strin        G name, Sqlitedatabase.cursorfactory Factory) {Super (context, name, factory); } @Override public void Onupgrade (sqlitedatabase db, int oldversion, int newversion) {//Database version control Add the difference if (Oldversion < 2) {if (!hascolumn (db, Userbookdao.tablename, Userbookdao) with the version overlay.                            Properties.Author.columnName)) {String sql = "ALTER TABLE" + Userbookdao.tablename +                    "Add COLUMN" + UserBookDao.Properties.Author.columnName + "TEXT";                Db.execsql (SQL); } if (!hascolumn (db, Userbookdao.tablename, UserBookDao.Properties.Type.columnName)) {S Tring sql = "ALTER TABLE" + Userbookdao.tablename + "Add COLUMN" + UserBookDao.Properties.Ty             Pe.columnname + "INTEGER";       Db.execsql (SQL); }            }        }    }

In addition to the above modification table, if you change too much or change to indicate, you can also directly delete the reconstruction (before the data is deleted)

            if (Oldversion < 3) {                userdao.droptable (new Standarddatabase (db), true);                Userstudentdao.createtable (New Standarddatabase (db), true);            }

Five, the database table field to assign the initial value

Initial values for some fields if you don't want to be 0, or an empty string, you can assign an initial value. Now, the initial value of the assignment is divided into two cases.

1. Initial values when building a table

In the previous version of 3.0 to write a module, which is written like a code to build a table

    private static void AddUser (schema schema) {        Entity user = schema.addentity ("user");        User.addidproperty ();        User.addstringproperty ("name"). Notnull (). Defvalue ("\" Jack\ "");        User.addstringproperty ("Address");        User.addstringproperty ("Teacher");        User.addintproperty ("Age"). Primjavatype (). Defvalue ("n");    

After 3.0, the introduction of annotations and write the entity directly, so you can directly in the class of entity to specify

@Entitypublic class Student {    @Id (AutoIncrement = true)    private long Id;//primary key    private String name;    Private String schooltime = "09-01"; The default is September 1    private int age = 19;//Just college default is 19 years old    //below the generated getter and setter omitted ... }

2. Assign an initial value to the Upgrade field when the database is upgraded

The above mentioned database upgrade, it is necessary to write an ALTER TABLE SQL statement, can be directly stitched into this line behind

            Above to determine whether the column name exists            //...            String sql = "ALTER TABLE" + Userbookdao.tablename +                    "Add COLUMN" + UserBookDao.Properties.Type.columnName + "INTE GER "+" not NULL DEFAULT (-1) "; Directly stitching at the end of the statement Dong Platinum Blog Park            db.execsql (SQL);            // ...

Note: Previously heard a statement is directly in Userdao this generated class directly after the creation of the generated statement splicing default, here is very opposed, first people class name explicitly indicate//This CODE is GENERATED by Greendao, does not EDIT. And some people's code may be set to be manually generated, but our project is set in Gradle each build will be generated automatically, this situation is overwritten each time.

Some finishing in development, welcome to discuss.

Android Greendao Framework some advanced usage finishing