SQLite Basics

API Guide - Saving Data in SQL Databases
Android学习笔记（四一）：SQLite的使用

[TODO]SQL的基本用法。
SQLite是嵌入式关系型数据库，在Android中可以使用全集的SQLite。

建立数据库

• Gist:
  

  • 通过继承SQLiteOpenHelper来实现。
  • 继承SQLiteOpenHelper需要覆写 onCreate(),onUpgrade(),onOpen() 。
    

  Note: Because they can be long-running, be sure that you call getWritableDatabase() or getReadableDatabase() in a background thread, such as with AsyncTask or IntentService.

Example

public class SQLiteClass extends SQLiteOpenHelper{    public static final String LOG_SQL = "log_sql";    //数据库的名称    public static final String DATABASE_NAME = "book_db.db";    public SQLiteClass(Context context){        super(context,DATABASE_NAME,null,1);    }    @Override    public void onCreate(SQLiteDatabase sqLiteDatabase) {        //create database        //执行SQL语句创建一个名为mytable的表        sqLiteDatabase.execSQL("CREATE TABLE mytable(_id INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT,Weight REAL); ");        //ContentValues用于存储名称和数值，对应为表格中列的名称和其在行中的数据        ContentValues contentValues = new ContentValues();        contentValues.put("Name", "Gravity, Earth");        contentValues.put("Weight", SensorManager.GRAVITY_EARTH);        sqLiteDatabase.insert("mytable", "Name", contentValues);        contentValues.put("Name", "Gravity, Mars");        contentValues.put("Weight", SensorManager.GRAVITY_MARS);        sqLiteDatabase.insert("mytable", "Name", contentValues);        contentValues.put("Name", "Gravity, Moon");        contentValues.put("Weight", SensorManager.GRAVITY_MOON);        sqLiteDatabase.insert("mytable", "Name", contentValues);        Log.v(LOG_SQL,"database built successfully");    }    @Override    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {        sqLiteDatabase.execSQL("DROP TABLE IF EXISTS mytable");        onCreate(sqLiteDatabase);    }}

SQL查询和Cursor

Cursor包含两个部分：游标结果集、游标位置。
使用query()方法对数据库进行查询。

public class MainActivity extends AppCompatActivity {    private SQLiteDatabase sqLiteDatabase;    private Cursor cursor;    @Override    protected void onCreate(Bundle savedInstanceState) {        super.onCreate(savedInstanceState);        setContentView(R.layout.activity_main);        sqLiteDatabase = (new SQLiteClass(getApplicationContext())).getWritableDatabase();        Log.v(SQLiteClass.LOG_SQL,sqLiteDatabase.getPath());        String[] columns = {"Name", "Weight"};        cursor = sqLiteDatabase.query("mytable", columns, null, null, null, null, null);        //查询的表，返回的列，WHERE语句的列，WHERE语句的值，行，行，排序        Button query_database = (Button)findViewById(R.id.query_database);        query_database.setOnClickListener(new View.OnClickListener() {            @Override            public void onClick(View view) {                TextView textView_name = (TextView)findViewById(R.id.text_name);                TextView textView_weight = (TextView)findViewById(R.id.text_weight);                //select cursor                cursor.moveToFirst();                String firstName = cursor.getString(0);                String firstWeight = cursor.getDouble(1)+"";                //set values to the view                textView_name.setText(firstName);                textView_weight.setText(firstWeight);            }        });    }    @Override    protected void onDestroy() {        super.onDestroy();        sqLiteDatabase.close();    }}

其他操作

• 增加数据：database.insert();
• 删除数据：database.delete();
• 更新数据：database.update();