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();