Android -- 資料庫1

資料庫的基本操作。

package com.serviatech.testdb;

import android.annotation.SuppressLint;
import android.app.Activity;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;

@SuppressLint("SdCardPath")
public class MainActivity extends Activity {

  final static String PATH_DB = "/sdcard/test.db";

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

    // 建立資料庫
    SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase(PATH_DB, null);

    // 删除/建立表
    db.execSQL("drop table user_info");
    db.execSQL("create table user_info(user_id integer primary key,user_name varchar(255),user_pass varchar(255))");

    // 插入資料
    db.execSQL("insert into user_info values(1,'aizizai1','shane1234')");
    db.execSQL("insert into user_info values(2,'aizizai2','shane1234')");
    db.execSQL("insert into user_info values(3,'aizizai3','shane1234')");
    db.execSQL("insert into user_info values(4,'aizizai4','shane1234')");

    // 修改
    db.execSQL("update user_info set user_pass='shane1111' where user_id = 1");
    db.execSQL("update user_info set user_pass='shane2222' where user_id = 2;");
    db.execSQL("update user_info set user_pass='shane3333' where user_id = 3;");
    db.execSQL("update user_info set user_pass='shane4444' where user_id = 4;");

    // 删除資料
    db.execSQL("delete from user_info  where user_id = 1");

    // 查詢
    Cursor cursor = db.rawQuery("select * from user_info", null);
    System.out.println("@@@ getColumnCount: " + cursor.getColumnCount());
    System.out.println("@@@ getColumnName(0): " + cursor.getColumnName(0));
    System.out.println("@@@ getColumnName(1): " + cursor.getColumnName(1));
    System.out.println("@@@ getColumnName(2): " + cursor.getColumnName(2));

    cursor.moveToFirst();
    System.out.println("@@@ getString(0): " + cursor.getString(0));
    System.out.println("@@@ getString(1): " + cursor.getString(1));
    System.out.println("@@@ getString(2): " + cursor.getString(2));

    printCursor(cursor);
    cursor.close();

    // 關閉
    if (db != null && db.isOpen())
      db.close();

  }

  /**
   * 列印資料庫内容
   * 
   * @param cursor
   * @data 2015-7-24 下午1:30:53
   */
  public void printCursor(Cursor cursor) {

    System.out.println("=============================");

    // 擷取列總數
    int columnSum = cursor.getColumnCount();

    // 擷取列名字
    String[] columnNames = cursor.getColumnNames();
    StringBuilder sb = new StringBuilder();
    for (int i = 0; i < columnNames.length; i++) {
      sb.append(" " + columnNames[i]);
    }
    System.out.println(sb.toString());

    sb.setLength(0);
    cursor.moveToFirst();
    do {

      for (int i = 0; i < columnSum; i++) {
        sb.append(" " + cursor.getString(i));
      }
      sb.append("\n");
    } while (cursor.moveToNext());
    System.out.println(sb.toString());
    System.out.println("=============================");
  }
}