package com.example.sql;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.widget.TextView;

import java.util.HashMap;
import java.util.Map;

public class MainActivity extends AppCompatActivity {
    private TextView textview;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        textview = (TextView)findViewById(R.id.text1);
        UserStorage userStorage = new UserStorage(this);
        ContentValues contentValues = new ContentValues();
        contentValues.put("name","aa");
        contentValues.put("sex","女");
        contentValues.put("age",20);
        long id = userStorage.insert(contentValues);
        Map<String,Object> map = userStorage.query(""+id);
        textview.setText((String)map.get("name")+" "+(String)map.get("sex")+" "+(int)map.get("age"));
    }
}
class UserStorage{
    String dbName = "userDB";
    String table = "users";
    int version = 1;
    DBHelper dbHelper;
    public UserStorage(Context context){
        dbHelper = new DBHelper(context,version);
    }
    long insert(ContentValues contentValues){
        SQLiteDatabase sqLiteDatabase = dbHelper.getWritableDatabase();
        long id = sqLiteDatabase.insert(table, null, contentValues);
        sqLiteDatabase.close();
        return id;
    }
    long delete(String id){
        SQLiteDatabase sqLiteDatabase = dbHelper.getWritableDatabase();
        long result = sqLiteDatabase.delete(table,"_id=?",new String[]{id});
        sqLiteDatabase.close();
        return result;
    }
    long update(ContentValues contentValues, String id){
        SQLiteDatabase sqLiteDatabase = dbHelper.getWritableDatabase();
        long result = sqLiteDatabase.update(table,contentValues,"_id=?",new String[]{id});
        return result;
    }
    Map<String,Object> query(String id){
        SQLiteDatabase sqLiteDatabase = dbHelper.getReadableDatabase();
        Cursor cursor = sqLiteDatabase.query(table,new String[]{"_id","name","sex","age"},"_id=?",new String[]{id},null,null,null);
        if(cursor.moveToNext()){
            Map<String,Object> map = new HashMap<String,Object>();
            map.put("_id",cursor.getInt(0));
            map.put("name",cursor.getString(1));
            map.put("sex",cursor.getString(2));
            map.put("age",cursor.getInt(3));
            cursor.close();
            sqLiteDatabase.close();
            return map;
        }else{
            sqLiteDatabase.close();
            return null;
        }
    }
}
class DBHelper extends SQLiteOpenHelper{

    private String createTableSql = "create table if not exists users" +
            "(_id integer primary key autoincrement," +
            "name text not null," +
            "sex text not null," +
            "age integer not null)";

    public DBHelper(Context context, int version){
        super(context,"userDB",null,version);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(createTableSql);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        String sql = "drop table if exists users";
        db.execSQL(sql);
        onCreate(db);
    };
}