It is roughly divided into the following aspects:
- Some query instructions sorting
- Use SQL statements for special queries
- Check whether the table field exists
- Database upgrade
- Assign initial value to the database table field
1. Query command sorting
1. Chain-executed instructions
return ().queryBuilder(). XXX. XXX. XXX. list();
General query statements will add various judgment and filtering method instructions at the middle xxx position. Except for the final terminating instruction list() or unique() that returns a collection or business object, the others return a QueryBuilder object. In most cases, XXX position is filled in with where statements, and there are some other statements associated with SQL for easy use.
The conditions written in the "whereOr" where statement are connected with "and", and the statement in whereOr uses "or" to connect with "or".
"distinct" directly filters out heavy burden fields
"limit" pages are paging n pages, usually used in combination with offset
"offset" ignores the first n results found
"orderAsc" sorts in ascending order of fields
"orderDesc" descends in field
"preferLocalizedStringOrder" localized string sort
"orderCustom" custom sorting requires two parameters to be passed: a property and the corresponding sorting scheme ASC or DESC
"orderRaw" is also a custom sorting, writing fields and sorting schemes into a string.
"stringOrderCollation" is also a custom sorting. Multiple rising and falling sorting schemes can be merged in ascending order of dates and downward order of price
2. Instructions in the conditions
return ().queryBuilder(). where((userIdList), (19)). list();
A simple where statement is probably like this. Many conditions can be written in parallel in the brackets of where, all of which are connected by "and". In addition to the above "in" and "eq" there are many other judgment conditions
"notEq" is the opposite of eq. Don't go outside and hit "!"
"notIn" Same as above
"or" or
"like" is the LIKE of the SQL statement"%"+string+"%"
"between" means BETWEEN ? AND ? You can take the interval of two values (but this statement should be used with caution. Different databases are different. Some are A<condition<B, and some are A<=condition<=B)
"gt" is equivalent to >
"ge" is equivalent to >=
"lt" is equivalent to <
"le" is equivalent to <=
"isNull" is empty
"notIsNull" is not empty
2. Use SQL statements to perform special queries
Generally, when encountering functions that cannot be easily implemented by ordinary addition, deletion, modification and search operations, this rawQuery method will be used. I often encounter two scenarios:
1. Use SELECT DISTINCT
Commonly used is related to one-to-many relationship. Suppose the library now has a "user book saving table". Some users have 20 books, and there will be 20 pieces of his data in the table, each corresponding to a different book.
At this time, suppose there is a requirement and find out that all user names in this table are not allowed to be repeated. At this time, it will be very troublesome to use the ordinary query instructions. You need to use the SELECT DISTINCT instruction to find all non-duplicate entries in a column name.
String queryString =
"SELECT DISTINCT " + + " FROM " +
+ " ORDER BY "
+
+ " DESC "
+ " LIMIT "
+ page * LIMIT_NUM
+ " , "
+ LIMIT_NUM;
ArrayList<String> result = new ArrayList<>();
Cursor c = ().rawQuery(queryString,new String[]{});
try {
if (c != null) {
if (()) {
do {
((0));
} while (());
}
}
} finally {
if (c != null) {
();
}
}
This is probably the way the code looks like. First, splice out a string that conforms to SQL syntax, and randomly add other operation instructions fields to sort and paginate, making the query instructions look more complete, and then use cursors to receive the above query results.
Most queries may include some parameters, such as where XX = XX filter conditions. Assuming that there is a requirement that requires the previous query user needs to add restrictions on the price of publishers and books, the query method is as follows.
String queryString =
"SELECT DISTINCT " + + " FROM " + // Dong Boran Blog Park + " WHERE "
+ + " = ?"
+ " AND "
+ + " > ?"
+ " ORDER BY "
+
+ " DESC ";
ArrayList<String> result = new ArrayList<>();
Cursor c = ().rawQuery(queryString, new String[]{"A certain publishing house"),
(100.00)});
try {
if (c != null) {
if (()) {
do {
((0));
} while (());
}
}
} finally {
if (c != null) {
();
}
}
The query string with parameters needs to use a question mark to occupy the place above, and then fill in the relevant parameters in the API with parameters using rawQuery.
2. SELECT query multiple fields at the same time
Let’s use this example of book search, suppose you need to look up the three fields “book title”, “publisher” and “price”
String queryString = "SELECT "
+ + "." + + ","
+ + "." + + ","
+ + "." + + " "
+ "FROM "
+ + " "
+ "WHERE"
+ + " > 100.00 ";
Cursor cursor = null;
try {
cursor = ().rawQuery(queryString,new String[]{});
if (cursor == null) {
return payMap;
}
// Take out the indexes corresponding to the three fields, and then get the value to the index. int nameIndex = ();
int publisherIndex = ();
int priceIndex = ();
if (nameIndex != -1 && publisherIndex != -1 && priceIndex != -1) {
while (()) {
String name = (nameIndex);
String publisher = (publisherIndex);
Double price = (priceIndex);
// Get three fields here: Should I save the model or the dictionary and handle it myself. }
}
} finally {
if (null != cursor) {
();
}
}
The following can take out three required fields at once and use them. You need to get the corresponding index of the field first, and then get the value against the index.
3. Check whether the table field exists
private boolean hasColumn(SQLiteDatabase db, String tableName, String column) {
if ((tableName) || (column)) {
return false;
}
Cursor cursor = null;
try {
cursor = (tableName, null, null, null, null, null, null);
if (null != cursor && (column) != -1) {
return true;
}
} finally {
if (null != cursor) {
();
}
}
return false;
}
Similar to the method of taking cursors above, if the column index value taken out is not -1, it means that you can get this field and return true, otherwise it will return fasle together with the illegal entry parameter.
4. Database upgrade
This will call the above method to determine whether the column name already exists.
Then override this onUpgrade method of the parent class
private static class DemoOpenHelper extends {
public DemoOpenHelper(Context context, String name, factory) {
super(context, name, factory);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// Database version control The difference can be continuously increased with version overlay if (oldVersion < 2) {
if (!hasColumn(db, , )) {
String sql = "alter table " + +
" add COLUMN " + + " TEXT";
(sql);
}
if (!hasColumn(db, , )) {
String sql = "alter table " + +
" add COLUMN " + + " INTEGER";
(sql);
}
}
}
}
In addition to the above modification table, if there are too many changes or replacement, it can also be deleted and reconstructed directly (if you do so, the previous data will be deleted)
if (oldVersion < 3) {
(new StandardDatabase(db),true);
(new StandardDatabase(db),true);
}
5. Assign initial value to the database table field
Some fields can be assigned initial values if you do not want to be 0 or an empty string. The initial value of assignment is now divided into two situations
1. Attach the initial value when building the table
In versions before 3.0, you still need to write a module with similar code to create a table.
private static void addUser(Schema schema) {
Entity user = ("User");
();
("name").notNull().defValue("\"jack\"");
("address");
("teacher");
("age").primJavaType().defValue("17");
}
After 3.0, the writing method of using annotations and writing Entity directly is implemented, so it can be specified directly in the Entity class
@Entity
public class Student {
@Id(autoincrement = true)
private long id; //Primary key private String name;
private String schoolTime = "09-01"; //The school starts on September 1st by default private int age = 19; // The default age of 19 is 19 when you first go to college // The getters and setters generated below are omitted. . .}
2. Assign initial value to the upgrade field when upgrading the database
The database upgrade mentioned above requires writing an alter table SQL statement, which can be spliced directly behind this line.
// The above determines whether the column name exists// ... String sql = "alter table " + + " add COLUMN " + + " INTEGER" + " NOT NULL DEFAULT(-1) "; // Directly spliced at the end of the sentence Dong Boran Blog Park(sql); // ...
Note: I have heard a saying before that DEFAULT is directly spliced after the generated creation statement in UserDao. I am very opposed here. First of all, the class name clearly states // THIS CODE IS GENERATED BY greenDAO, DO NOT EDIT. And some people’s code may be set to manually generate, but our project sets it in gradle. Every time the build is built, it will be automatically generated, and this situation will be overwritten every time.
The above is all the content of this article. I hope that the content of this article will help you study or work. I also hope to support me more!