Android pit-Free Guide (I) Sugar and SQLite, androidsqlite

Android pit-Free Guide (I) Sugar and SQLite, androidsqlite

I recently used the ORM framework Sugar1.4 in Android mobile development, which saves a lot of code and also encountered a lot of trouble. The record is as follows:

1. Use group by to convert query results to POJO objects

In Sugar1.4, you can use the following code to convert the query result to a POJO object.

List<POJO> results = SugarRecord.findWithQuery(POJO.class, sql);

1.1. Additional ID Fields

This method calls the inflate method of the SugarRecord class, as follows:

 1     private static void inflate(Cursor cursor, Object object, Map<Object, Long> entitiesMap) { 2         List<Field> columns = ReflectionUtil.getTableFields(object.getClass()); 3         if (!entitiesMap.containsKey(object)) { 4             entitiesMap.put(object, cursor.getLong(cursor.getColumnIndex(("ID")))); 5         } 6  7         for (Field field : columns) { 8             field.setAccessible(true); 9             Class<?> fieldType = field.getType();10             if (isSugarEntity(fieldType)) {11                 try {12                     long id = cursor.getLong(cursor.getColumnIndex(NamingHelper.toSQLName(field)));13                     field.set(object, (id > 0) ? findById(fieldType, id) : null);14                 } catch (IllegalAccessException e) {15                     e.printStackTrace();16                 }17             } else {18                 ReflectionUtil.setFieldValueFromCursor(cursor, field, object);19             }20         }21     }

Note that the 4th lines of code will cache the query result and ID in entitiesMap. Therefore, you need to add an additional ID field when constructing the query statement SQL.

1.2. SQLite case sensitivity

In the official Sugar documentation, class fields are mapped to the corresponding columns of the data table by using the underline naming method (see the code 12-line NamingHelper ).

Check the NamingHelper source code. The returned results are in uppercase, but all the conditional queries in the official example Use lowercase letters. Because SQLite is generally case insensitive, in general, there is no problem.

But! After renaming using the AS syntax in SQL, it will be case sensitive!

Therefore, in a group by query, you must rename the summary column to an upper-case underline to display the corresponding column of the POJO instance in Sugar.

1.3. SQLite does not support the IF syntax

SQLite does not support the IF syntax, but can be replaced by the case when syntax.

 

CASE WHEN first conditional expression THEN column valueWHEN second conditional expression THEN column valueWHEN third conditional expression THEN column valueENDCASE WHEN conditional expression THEN column valueELSE default column valueEND