Android学习笔记——保存数据到SQL数据库中(Saving Data in SQL Databases)

  1 package com.example.sqlitetest;
  2 
  3 import java.util.ArrayList;
  4 import java.util.List;
  5 
  6 import android.content.ContentValues;
  7 import android.content.Context;
  8 import android.database.Cursor;
  9 import android.database.sqlite.SQLiteDatabase;
 10 import android.database.sqlite.SQLiteOpenHelper;
 11 
 12 public class DatabaseHelper extends SQLiteOpenHelper {
 13 
 14     // 数据库版本
 15     private static final int DATABASE_VERSION = 1;
 16 
 17     // 数据库名
 18     private static final String DATABASE_NAME = "contactsManager";
 19 
 20     //Contact表名
 21     private static final String TABLE_CONTACTS = "contacts";
 22 
 23     //Contact表的列名
 24     private static final String KEY_ID = "id";
 25     private static final String KEY_NAME = "name";
 26     private static final String KEY_PH_NO = "phone_number";
 27 
 28     public DatabaseHelper(Context context) {
 29         super(context, DATABASE_NAME, null, DATABASE_VERSION);
 30     }
 31 
 32     // 创建表
 33     @Override
 34     public void onCreate(SQLiteDatabase db) {
 35         String CREATE_CONTACTS_TABLE = "CREATE TABLE " + TABLE_CONTACTS + "("
 36                 + KEY_ID + " INTEGER PRIMARY KEY," + KEY_NAME + " TEXT,"
 37                 + KEY_PH_NO + " TEXT" + ")";
 38         db.execSQL(CREATE_CONTACTS_TABLE);
 39     }
 40 
 41     // 更新表
 42     @Override
 43     public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
 44         // 删除旧表
 45         db.execSQL("DROP TABLE IF EXISTS " + TABLE_CONTACTS);
 46 
 47         //再次创建表
 48         onCreate(db);
 49     }
 50 
 51     /**
 52      *增删改查操作
 53      */
 54 
 55     // 增加新的联系人
 56     void addContact(Contact contact) {
 57         SQLiteDatabase db = this.getWritableDatabase();
 58 
 59         ContentValues values = new ContentValues();
 60         values.put(KEY_NAME, contact.getName());
 61         values.put(KEY_PH_NO, contact.getPhoneNumber());
 62 
 63         // 插入行
 64         db.insert(TABLE_CONTACTS, null, values);
 65         db.close(); // 关闭数据库的连接
 66     }
 67 
 68     // 获取联系人
 69     Contact getContact(int id) {
 70         SQLiteDatabase db = this.getReadableDatabase();
 71 
 72         Cursor cursor = db.query(TABLE_CONTACTS, new String[] { KEY_ID,
 73                 KEY_NAME, KEY_PH_NO }, KEY_ID + "=?",
 74                 new String[] { String.valueOf(id) }, null, null, null, null);
 75         if (cursor != null)
 76             cursor.moveToFirst();
 77 
 78         Contact contact = new Contact(Integer.parseInt(cursor.getString(0)),
 79                 cursor.getString(1), cursor.getString(2));
 80         return contact;
 81     }
 82     
 83     // 获取所有联系人
 84     public List<Contact> getAllContacts() {
 85         List<Contact> contactList = new ArrayList<Contact>();
 86         // Select All Query
 87         String selectQuery = "SELECT  * FROM " + TABLE_CONTACTS;
 88 
 89         SQLiteDatabase db = this.getWritableDatabase();
 90         Cursor cursor = db.rawQuery(selectQuery, null);
 91 
 92         if (cursor.moveToFirst()) {
 93             do {
 94                 Contact contact = new Contact();
 95                 contact.setID(Integer.parseInt(cursor.getString(0)));
 96                 contact.setName(cursor.getString(1));
 97                 contact.setPhoneNumber(cursor.getString(2));
 98                 contactList.add(contact);
 99             } while (cursor.moveToNext());
100         }
101 
102         return contactList;
103     }
104 
105     // 更新单个联系人
106     public int updateContact(Contact contact) {
107         SQLiteDatabase db = this.getWritableDatabase();
108 
109         ContentValues values = new ContentValues();
110         values.put(KEY_NAME, contact.getName());
111         values.put(KEY_PH_NO, contact.getPhoneNumber());
112 
113         //更新行
114         return db.update(TABLE_CONTACTS, values, KEY_ID + " = ?",
115                 new String[] { String.valueOf(contact.getID()) });
116     }
117 
118     // 删除单个联系人
119     public void deleteContact(Contact contact) {
120         SQLiteDatabase db = this.getWritableDatabase();
121         db.delete(TABLE_CONTACTS, KEY_ID + " = ?",
122                 new String[] { String.valueOf(contact.getID()) });
123         db.close();
124     }
125 
126 
127     // 获取联系人数量
128     public int getContactsCount() {
129         String countQuery = "SELECT  * FROM " + TABLE_CONTACTS;
130         SQLiteDatabase db = this.getReadableDatabase();
131         Cursor cursor = db.rawQuery(countQuery, null);
132         cursor.close();
133 
134         return cursor.getCount();
135     }
136 }