[Android] SQLite

　　一般在撰寫SQL的時候會把 Main 與 SQLite 分開
會先把SQLite給編寫好  好讓  撰寫Main的時候可以直接呼叫使用
需要繼承extends SQLiteOpenHelper

import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.DatabaseUtils;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

public class QRcodeDB extends SQLiteOpenHelper {

//資料庫版本關係到App更新時，資料庫是否要調用onUpgrade()
private static final String DB_NAME = "qrcode_db";
public static final String CONTACTS_COLUMN_IMEI = "IMEI";

public QRcodeDB(Context context) {
               //修改後
              //super(context, name, factory, version);
super(context, DB_NAME, null, 1);

// TODO Auto-generated constructor stub
}

 
@Override
public void onCreate(SQLiteDatabase db) {
// TODO Auto-generated method stub
try{
db.execSQL(
"create table contacts"
+ " (ITEM Integer PRIMARY KEY AUTOINCREMENT,"
+ " IMEI TEXT,"
+ " WIFIMAC TEXT,"
+ " RESULT TEXT,"
+ " PRODUCT TEXT,"
+ " APPVERSION TEXT)");
}catch(Exception e){
db.execSQL("DROP TABLE IF EXISTS contacts");
}
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO Auto-generated method stub
                //可有可無一般在更新的時候才會驅動，所以若是把他寫在onCreate的catch會更好
db.execSQL("DROP TABLE IF EXISTS contacts");
onCreate(db);
}

public boolean insertContact(String IMEI, String WIFIMAC, String RESULT, String PRODUCT, String APPVERSION){
//新增資料
SQLiteDatabase db = this.getWritableDatabase();
                //通常都要搭配 ContentValues作使用
ContentValues contentValues = new ContentValues();
contentValues.put("IMEI", IMEI);
contentValues.put("WIFIMAC", WIFIMAC);
contentValues.put("RESULT", RESULT);
contentValues.put("PRODUCT", PRODUCT);
contentValues.put("APPVERSION", APPVERSION);

db.insert("contacts", null, contentValues);
db.close();
return true;
}

public Cursor getData(String IMEI) {
SQLiteDatabase db = this.getReadableDatabase();
                                          //(搜尋語法,條件,搜尋參數)
                                          //("contacts", "IMEI = ? ", new String[] { Integer.toString(IMEI) });
                                          //兩個都可以
Cursor res = db.rawQuery("select * from contacts where IMEI=" + IMEI + "", null);
return res;
}

public int numberOfRows() {
SQLiteDatabase db = this.getReadableDatabase();
int numRows = (int) DatabaseUtils.queryNumEntries(db, CONTACTS_TABLE_NAME);
db.close();
return numRows;
}

public boolean updateContact(String IMEI, String WIFIMAC, String RESULT, String PRODUCT, String APPVERSION){
SQLiteDatabase db = this.getWritableDatabase();
ContentValues contentValues = new ContentValues();
contentValues.put("IMEI", IMEI);
contentValues.put("WIFIMAC", WIFIMAC);
contentValues.put("RESULT", RESULT);
contentValues.put("PRODUCT", PRODUCT);
contentValues.put("APPVERSION", APPVERSION);
db.update("contacts", contentValues, "IMEI = ? ", new String[] { IMEI });
return true;
}

          //刪除
public Integer deleteContact (String IMEI) {
     SQLiteDatabase db = this.getWritableDatabase();
     Integer dbDelete = db.delete("contacts", "IMEI = '" + IMEI + "'" , null);
     return dbDelete;
}

public ArrayList getAllCotacts() {
ArrayList array_list = new ArrayList();
// hp = new HashMap();
SQLiteDatabase db = this.getReadableDatabase();
Cursor res = db.rawQuery("select * from contacts", null);
res.moveToFirst();
while (res.isAfterLast() == false) {
array_list.add(res.getString(res.getColumnIndex(CONTACTS_COLUMN_IMEI)));
res.moveToNext();
}
return array_list;
}

}



參考網站:http://www.tutorialspoint.com/android/android_sqlite_database.htm