Android database

SQliteOpenHelper

public class LocalDataHelper extends SQLiteOpenHelper{
    public static final String detail_customer_table = "detail_customer";//Define a static constant representation table name

    //Designing a table
    private String createSimple = "create table "+simple_customer_table+"(" +
            "id INTEGER(20)  primary key ," +  //Default id
            "name varchar2(20)," + // Customer name
            "phone varchar2(20)," + // Cell-phone number
            "createTime varchar2(20)" + // Creation time
            ");";
    //Construction method        
    public LocalDataHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
       db.execSQL(createSimple );//Create a new form
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }


}

Such a SQLite Open Helper is rewritten. In our custom SQLite Open Helper, we designed a table detail_customer, which contains four fields id (primary key), name, phone, createTime. We should pay attention to the use of "," separating between each field, execSQL () is a method of SQLite Database, which is to execute the SQL statements in parentheses. .

SqliteDatabase

public class ClientActivity extends BaseActivity {
    private LocalDataHelper helper;
    private SQLiteDatabase database;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_client);

        //Constructing a new database info_input_db
        helper = new LocalDataHelper(this, "info_input_db", null, 1);
        database = helper.getWritableDatabase();

    }
}

insert

public long insert(String table, String nullColumnHack, ContentValues values)

The insert method has three parameters, table: table name, values: ContentValues type parameter set, nullColumn Hack: If the values are empty, the default values set by fields other than the primary key are usually passed by one null.
When using this method, we usually create a ContentValues object to add data, which is basically the same as Map.

    ContentValues cv = new ContentValues();
    cv.put("id","01");
    cv.put("name ","Android");
    cv.put("phone ","13390000000");
    cv.put("createTime ","2017-03-14");

The return value of the insert method is a long-type number. Its value is the id of the row of data added by your statement, that is, the number of rows added to your data. So as long as the return value is greater than 0, the insert is successful.

    long result = database .insert(LocalDataHelper.detail_customer_table,null,cv);

    if(result > 0){
    //Add success
    }else{
    //Add failure
    }

delete

public int delete(String table, String whereClause, String[] whereArgs)

Similarly, the delete method has three parameters: table name, where Clause: optional where statement, where Args: where Clause expression in statement? A list of placeholder parameters.
whereClause parameter is generally "column name=?" This form, for example: id=? whereArgs is an array of String types, and intuitively we can have a new String []:
The return value of delete is an int type, which generally means the number of deleted rows (officially interpreted as if where clause passes through the number of affected rows), if delete all, the return value is 1, and if delete fails, the return value is 0.

    int result = database .delete(LocalDataHelper.detail_customer_table,"id=?",new String[]{"1"，"2"});//Delete two data with ID s of 1 and 2

    if(result > 0){
    //Delete successful
    }else{
    //Delete failed
    }

update

public int update(String table, ContentValues values, String whereClause, String[] whereArgs)

The update method has four parameters: table name, values: fields and values that you need to modify, where Clause: optional where statement, where Args: where Clause expression? A list of placeholder parameters.
The parameter usage here is the same as that in insert and delete. It is worth noting that the values here can modify some fields in the whole data, and the unchanged fields can not be put in.
The return value is an int value, which generally represents the number of rows affected, that is, the number of rows that have been successfully modified.

    ContentValues cv = new ContentValues();
    cv.put("name ","ios");
    cv.put("phone ","13390005000");
    cv.put("createTime ","2016-03-14");

    int result = db.update(table,cv,"id=?",new String[]{"1"}

    if(result > 0){
    //Modified success
    }else{
    //Modification failed
    }

query

public Cursor query(String table, String[] columns, String selection,
            String[] selectionArgs, String groupBy, String having,
            String orderBy)

public Cursor query(String table, String[] columns, String selection,
            String[] selectionArgs, String groupBy, String having,
            String orderBy, String limit)

The first query method has seven parameters, table: the name of the table to be queried; columns: the column to be displayed; selection: where clause; selection Args: where clause corresponding to the condition value; groupBy: grouping method; having: having condition; orderBy: sort method; however, the second method only has one additional parameter limit: limit the number of records returned
Although the query method has many parameters, if you simply retrieve all the data, all parameters except the table name can be empty.
Let's talk about this return value. The return value type is a Cursor type. It's complicated and annoying to use. Some blogs on the Internet introduce the method of moving to First (), then while(cursor.moveToNext) {} which has some problems when I do projects. The first one is automatically given to pass, so I usually use do{}while(cursor.moveToNext).

Cursor cursor = database.query(LocalDataHelper.detail_customer_table,null,null,null,null,null,null);

    List<Map<String,String>> list = new ArrayList<>();
    cursor.moveToFirst();
    if (cursor.getCount() != 0){   //Determine how many data cursor has, and if it doesn't, don't go into the loop
      do {
          String id = cursor.getString(cursor.getColumnIndex("id"));
          String name = cursor.getString(cursor.getColumnIndex("name"));
          String phone = cursor.getString(cursor.getColumnIndex("phone"));
          String createTime = cursor.getString(cursor.getColumnIndex("createTime"));
          Map<String,String> map = new HashMap();
          map.put("id ",id ); 
          map.put("name ",name ); 
          map.put("phone ",phone ); 
          map.put("createTime ",createTime ); 
          list.add(map);
         }
      }while(cursor.moveToNext());
    }

Cursor's usage is annoying. Generally, we use the API method encapsulated by Android to move Cursor. First, we use the moveToFirst() method to move to the first position. Then we use the getColumn Index () method to get the index of the corresponding field. Then we use the getString() method to get the value of the corresponding index. After some fields are taken out, the moveToNext() method is called to move to the next location, so that all the data is recycled.

Simple encapsulation

    /**
     * Query information
     * @param db SQLiteDatabase 
     * @param table Table name
     * @return  Queried Cursor
     */
    public Cursor query(SQLiteDatabase db, String table){
        return db.query(table,null,null,null,null,null,null);
    }

Cursor cs  = helper.query(database,LocalDataHelper.detail_customer_table);