SQLite是一个开放源代码的关系数据库，即用于在android设备上执行数据库操作，例如从数据库中存储，操作或检索持久性数据。
默认情况下，它嵌入在android中。因此，无需执行任何数据库设置或管理任务。
SQLiteOpenHelper类提供使用SQLite数据库的函数。
SQLiteOpenHelper class
android.database.sqlite.SQLiteOpenHelper类用于数据库创建和版本管理。为了执行任何数据库操作，您必须提供SQLiteOpenHelper类的onCreate()和onUpgrade()方法的实现。
SQLiteOpenHelper构造方法
| 构造函数 | 描述 |
|---|---|
| SQLiteOpenHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) | 创建一个用于创建，打开和管理数据库的对象。 |
| SQLiteOpenHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version, DatabaseErrorHandler errorHandler) | 创建一个用于创建，打开和管理数据库的对象。它指定错误处理程序。 |
SQLiteOpenHelper方法
SQLiteOpenHelper类中有许多方法。其中一些如下:
| 方法 | 描述 |
|---|---|
| public abstract void onCreate(SQLiteDatabase db) | 首次创建数据库时仅调用一次。 |
| public abstract void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) | 需要升级数据库时调用。 |
| public synchronized void close () | 关闭数据库对象。 |
| public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) | 需要降级数据库时调用。 |
SQLiteDatabase class
它包含要在sqlite数据库上执行的方法，例如创建，更新，删除，选择等。
SQLiteDatabase类中有许多方法。其中一些如下:
| 方法 | 描述 |
|---|---|
| void execSQL(String sql) | 执行sql查询而不是select查询。 |
| long insert(String table, String nullColumnHack, ContentValues values) | 在数据库上插入一条记录。该表指定表名，nullColumnHack不允许完全为空值。如果第二个参数为null，则如果值为空，则android将存储null值。第三个参数指定要存储的值。 |
| int update(String table, ContentValues values, String whereClause, String[] whereArgs) | 更新一行。 |
| Cursor query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy) | 返回结果集上的光标。 |
SQLite数据库示例
让无涯教程看看Android SQLite数据库的简单示例。
File: Contact.java
package example.learnfk.com.sqlitetutorial;
public class Contact {
int _id;
String _name;
String _phone_number;
public Contact(){ }
public Contact(int id, String name, String _phone_number){
this._id = id;
this._name = name;
this._phone_number = _phone_number;
}
</span><span class="kwd">public</span><span class="pln"> </span><span class="typ">Contact</span><span class="pun">(</span><span class="typ">String</span><span class="pln"> name</span><span class="pun">,</span><span class="pln"> </span><span class="typ">String</span><span class="pln"> _phone_number</span><span class="pun">){</span><span class="pln">
</span><span class="kwd">this</span><span class="pun">.</span><span class="pln">_name </span><span class="pun">=</span><span class="pln"> name</span><span class="pun">;</span><span class="pln">
</span><span class="kwd">this</span><span class="pun">.</span><span class="pln">_phone_number </span><span class="pun">=</span><span class="pln"> _phone_number</span><span class="pun">;</span><span class="pln">
</span><span class="pun">}</span><span class="pln">
</span><span class="kwd">public</span><span class="pln"> </span><span class="kwd">int</span><span class="pln"> getID</span><span class="pun">(){</span><span class="pln">
</span><span class="kwd">return</span><span class="pln"> </span><span class="kwd">this</span><span class="pun">.</span><span class="pln">_id</span><span class="pun">;</span><span class="pln">
</span><span class="pun">}</span><span class="pln">
</span><span class="kwd">public</span><span class="pln"> </span><span class="kwd">void</span><span class="pln"> setID</span><span class="pun">(</span><span class="kwd">int</span><span class="pln"> id</span><span class="pun">){</span><span class="pln">
</span><span class="kwd">this</span><span class="pun">.</span><span class="pln">_id </span><span class="pun">=</span><span class="pln"> id</span><span class="pun">;</span><span class="pln">
</span><span class="pun">}</span><span class="pln">
</span><span class="kwd">public</span><span class="pln"> </span><span class="typ">String</span><span class="pln"> getName</span><span class="pun">(){</span><span class="pln">
</span><span class="kwd">return</span><span class="pln"> </span><span class="kwd">this</span><span class="pun">.</span><span class="pln">_name</span><span class="pun">;</span><span class="pln">
</span><span class="pun">}</span><span class="pln">
</span><span class="kwd">public</span><span class="pln"> </span><span class="kwd">void</span><span class="pln"> setName</span><span class="pun">(</span><span class="typ">String</span><span class="pln"> name</span><span class="pun">){</span><span class="pln">
</span><span class="kwd">this</span><span class="pun">.</span><span class="pln">_name </span><span class="pun">=</span><span class="pln"> name</span><span class="pun">;</span><span class="pln">
</span><span class="pun">}</span><span class="pln">
</span><span class="kwd">public</span><span class="pln"> </span><span class="typ">String</span><span class="pln"> getPhoneNumber</span><span class="pun">(){</span><span class="pln">
</span><span class="kwd">return</span><span class="pln"> </span><span class="kwd">this</span><span class="pun">.</span><span class="pln">_phone_number</span><span class="pun">;</span><span class="pln">
</span><span class="pun">}</span><span class="pln">
</span><span class="kwd">public</span><span class="pln"> </span><span class="kwd">void</span><span class="pln"> setPhoneNumber</span><span class="pun">(</span><span class="typ">String</span><span class="pln"> phone_number</span><span class="pun">){</span><span class="pln">
</span><span class="kwd">this</span><span class="pun">.</span><span class="pln">_phone_number </span><span class="pun">=</span><span class="pln"> phone_number</span><span class="pun">;</span><span class="pln">
</span><span class="pun">}</span><span class="pln">
}
File: DatabaseHandler.java
现在，让无涯教程创建扩展Sqliteopenhelper类并提供其方法实现的数据库处理程序类。
package example.learnfk.com.sqlitetutorial;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import java.util.ArrayList;
import java.util.List;
public class DatabaseHandler extends SQLiteOpenHelper {
private static final int DATABASE_VERSION = 1;
private static final String DATABASE_NAME = "contactsManager";
private static final String TABLE_CONTACTS = "contacts";
private static final String KEY_ID = "id";
private static final String KEY_NAME = "name";
private static final String KEY_PH_NO = "phone_number";
</span><span class="kwd">public</span><span class="pln"> </span><span class="typ">DatabaseHandler</span><span class="pun">(</span><span class="typ">Context</span><span class="pln"> context</span><span class="pun">)</span><span class="pln"> </span><span class="pun">{</span><span class="pln">
</span><span class="kwd">super</span><span class="pun">(</span><span class="pln">context</span><span class="pun">,</span><span class="pln"> DATABASE_NAME</span><span class="pun">,</span><span class="pln"> </span><span class="kwd">null</span><span class="pun">,</span><span class="pln"> DATABASE_VERSION</span><span class="pun">);</span><span class="pln">
</span><span class="com">//要传递的第三个参数是 CursorFactory 实例</span><span class="pln">
</span><span class="pun">}</span><span class="pln">
//创建表
@Override
public void onCreate(SQLiteDatabase db) {
String CREATE_CONTACTS_TABLE = "CREATE TABLE " + TABLE_CONTACTS + "("
+ KEY_ID + " INTEGER PRIMARY KEY," + KEY_NAME + " TEXT,"
+ KEY_PH_NO + " TEXT" + ")";
db.execSQL(CREATE_CONTACTS_TABLE);
}
//升级数据库
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
//删除旧表（如果存在）
db.execSQL("DROP TABLE IF EXISTS " + TABLE_CONTACTS);
</span><span class="com">//再次创建表</span><span class="pln">
onCreate</span><span class="pun">(</span><span class="pln">db</span><span class="pun">);</span><span class="pln">
</span><span class="pun">}</span><span class="pln">
//添加新联系人的代码
void addContact(Contact contact) {
SQLiteDatabase db = this.getWritableDatabase();
</span><span class="typ">ContentValues</span><span class="pln"> values </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">ContentValues</span><span class="pun">();</span><span class="pln">
values</span><span class="pun">.</span><span class="pln">put</span><span class="pun">(</span><span class="pln">KEY_NAME</span><span class="pun">,</span><span class="pln"> contact</span><span class="pun">.</span><span class="pln">getName</span><span class="pun">());</span><span class="com">//联系人姓名</span><span class="pln">
values</span><span class="pun">.</span><span class="pln">put</span><span class="pun">(</span><span class="pln">KEY_PH_NO</span><span class="pun">,</span><span class="pln"> contact</span><span class="pun">.</span><span class="pln">getPhoneNumber</span><span class="pun">());</span><span class="com">//联系电话</span><span class="pln">
</span><span class="com">//插入行</span><span class="pln">
db</span><span class="pun">.</span><span class="pln">insert</span><span class="pun">(</span><span class="pln">TABLE_CONTACTS</span><span class="pun">,</span><span class="pln"> </span><span class="kwd">null</span><span class="pun">,</span><span class="pln"> values</span><span class="pun">);</span><span class="pln">
</span><span class="com">//第二个参数是包含 nullColumnHack 的字符串</span><span class="pln">
db</span><span class="pun">.</span><span class="pln">close</span><span class="pun">();</span><span class="com">//关闭数据库连接</span><span class="pln">
</span><span class="pun">}</span><span class="pln">
//获取单个联系人的代码
Contact getContact(int id) {
SQLiteDatabase db = this.getReadableDatabase();
</span><span class="typ">Cursor</span><span class="pln"> cursor </span><span class="pun">=</span><span class="pln"> db</span><span class="pun">.</span><span class="pln">query</span><span class="pun">(</span><span class="pln">TABLE_CONTACTS</span><span class="pun">,</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">String</span><span class="pun">[]</span><span class="pln"> </span><span class="pun">{</span><span class="pln"> KEY_ID</span><span class="pun">,</span><span class="pln">
KEY_NAME</span><span class="pun">,</span><span class="pln"> KEY_PH_NO </span><span class="pun">},</span><span class="pln"> KEY_ID </span><span class="pun">+</span><span class="pln"> </span><span class="str">"=?"</span><span class="pun">,</span><span class="pln">
</span><span class="kwd">new</span><span class="pln"> </span><span class="typ">String</span><span class="pun">[]</span><span class="pln"> </span><span class="pun">{</span><span class="pln"> </span><span class="typ">String</span><span class="pun">.</span><span class="pln">valueOf</span><span class="pun">(</span><span class="pln">id</span><span class="pun">)</span><span class="pln"> </span><span class="pun">},</span><span class="pln"> </span><span class="kwd">null</span><span class="pun">,</span><span class="pln"> </span><span class="kwd">null</span><span class="pun">,</span><span class="pln"> </span><span class="kwd">null</span><span class="pun">,</span><span class="pln"> </span><span class="kwd">null</span><span class="pun">);</span><span class="pln">
</span><span class="kwd">if</span><span class="pln"> </span><span class="pun">(</span><span class="pln">cursor </span><span class="pun">!=</span><span class="pln"> </span><span class="kwd">null</span><span class="pun">)</span><span class="pln">
cursor</span><span class="pun">.</span><span class="pln">moveToFirst</span><span class="pun">();</span><span class="pln">
</span><span class="typ">Contact</span><span class="pln"> contact </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">Contact</span><span class="pun">(</span><span class="typ">Integer</span><span class="pun">.</span><span class="pln">parseInt</span><span class="pun">(</span><span class="pln">cursor</span><span class="pun">.</span><span class="pln">getString</span><span class="pun">(</span><span class="lit">0</span><span class="pun">)),</span><span class="pln">
cursor</span><span class="pun">.</span><span class="pln">getString</span><span class="pun">(</span><span class="lit">1</span><span class="pun">),</span><span class="pln"> cursor</span><span class="pun">.</span><span class="pln">getString</span><span class="pun">(</span><span class="lit">2</span><span class="pun">));</span><span class="pln">
</span><span class="com">//return contact</span><span class="pln">
</span><span class="kwd">return</span><span class="pln"> contact</span><span class="pun">;</span><span class="pln">
</span><span class="pun">}</span><span class="pln">
//在列表视图中获取所有联系人的代码
public List<contact> getAllContacts() {
List<contact> contactList = new ArrayList<contact>();
//全选查询
String selectQuery = "SELECT * FROM " + TABLE_CONTACTS;
</span><span class="typ">SQLiteDatabase</span><span class="pln"> db </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">this</span><span class="pun">.</span><span class="pln">getWritableDatabase</span><span class="pun">();</span><span class="pln">
</span><span class="typ">Cursor</span><span class="pln"> cursor </span><span class="pun">=</span><span class="pln"> db</span><span class="pun">.</span><span class="pln">rawQuery</span><span class="pun">(</span><span class="pln">selectQuery</span><span class="pun">,</span><span class="pln"> </span><span class="kwd">null</span><span class="pun">);</span><span class="pln">
</span><span class="com">//循环遍历所有行并添加到列表</span><span class="pln">
</span><span class="kwd">if</span><span class="pln"> </span><span class="pun">(</span><span class="pln">cursor</span><span class="pun">.</span><span class="pln">moveToFirst</span><span class="pun">())</span><span class="pln"> </span><span class="pun">{</span><span class="pln">
</span><span class="kwd">do</span><span class="pln"> </span><span class="pun">{</span><span class="pln">
</span><span class="typ">Contact</span><span class="pln"> contact </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">Contact</span><span class="pun">();</span><span class="pln">
contact</span><span class="pun">.</span><span class="pln">setID</span><span class="pun">(</span><span class="typ">Integer</span><span class="pun">.</span><span class="pln">parseInt</span><span class="pun">(</span><span class="pln">cursor</span><span class="pun">.</span><span class="pln">getString</span><span class="pun">(</span><span class="lit">0</span><span class="pun">)));</span><span class="pln">
contact</span><span class="pun">.</span><span class="pln">setName</span><span class="pun">(</span><span class="pln">cursor</span><span class="pun">.</span><span class="pln">getString</span><span class="pun">(</span><span class="lit">1</span><span class="pun">));</span><span class="pln">
contact</span><span class="pun">.</span><span class="pln">setPhoneNumber</span><span class="pun">(</span><span class="pln">cursor</span><span class="pun">.</span><span class="pln">getString</span><span class="pun">(</span><span class="lit">2</span><span class="pun">));</span><span class="pln">
</span><span class="com">//将联系人添加到列表</span><span class="pln">
contactList</span><span class="pun">.</span><span class="kwd">add</span><span class="pun">(</span><span class="pln">contact</span><span class="pun">);</span><span class="pln">
</span><span class="pun">}</span><span class="pln"> </span><span class="kwd">while</span><span class="pln"> </span><span class="pun">(</span><span class="pln">cursor</span><span class="pun">.</span><span class="pln">moveToNext</span><span class="pun">());</span><span class="pln">
</span><span class="pun">}</span><span class="pln">
</span><span class="com">//返回联系人列表</span><span class="pln">
</span><span class="kwd">return</span><span class="pln"> contactList</span><span class="pun">;</span><span class="pln">
</span><span class="pun">}</span><span class="pln">
//更新单个联系人的代码
public int updateContact(Contact contact) {
SQLiteDatabase db = this.getWritableDatabase();
</span><span class="typ">ContentValues</span><span class="pln"> values </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">ContentValues</span><span class="pun">();</span><span class="pln">
values</span><span class="pun">.</span><span class="pln">put</span><span class="pun">(</span><span class="pln">KEY_NAME</span><span class="pun">,</span><span class="pln"> contact</span><span class="pun">.</span><span class="pln">getName</span><span class="pun">());</span><span class="pln">
values</span><span class="pun">.</span><span class="pln">put</span><span class="pun">(</span><span class="pln">KEY_PH_NO</span><span class="pun">,</span><span class="pln"> contact</span><span class="pun">.</span><span class="pln">getPhoneNumber</span><span class="pun">());</span><span class="pln">
</span><span class="com">//更新行</span><span class="pln">
</span><span class="kwd">return</span><span class="pln"> db</span><span class="pun">.</span><span class="pln">update</span><span class="pun">(</span><span class="pln">TABLE_CONTACTS</span><span class="pun">,</span><span class="pln"> values</span><span class="pun">,</span><span class="pln"> KEY_ID </span><span class="pun">+</span><span class="pln"> </span><span class="str">" = ?"</span><span class="pun">,</span><span class="pln">
</span><span class="kwd">new</span><span class="pln"> </span><span class="typ">String</span><span class="pun">[]</span><span class="pln"> </span><span class="pun">{</span><span class="pln"> </span><span class="typ">String</span><span class="pun">.</span><span class="pln">valueOf</span><span class="pun">(</span><span class="pln">contact</span><span class="pun">.</span><span class="pln">getID</span><span class="pun">())</span><span class="pln"> </span><span class="pun">});</span><span class="pln">
</span><span class="pun">}</span><span class="pln">
//删除单个联系人
public void deleteContact(Contact contact) {
SQLiteDatabase db = this.getWritableDatabase();
db.delete(TABLE_CONTACTS, KEY_ID + " = ?",
new String[] { String.valueOf(contact.getID()) });
db.close();
}
//获取联系人计数
public int getContactsCount() {
String countQuery = "SELECT * FROM " + TABLE_CONTACTS;
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.rawQuery(countQuery, null);
cursor.close();
</span><span class="com">//return count</span><span class="pln">
</span><span class="kwd">return</span><span class="pln"> cursor</span><span class="pun">.</span><span class="pln">getCount</span><span class="pun">();</span><span class="pln">
</span><span class="pun">}</span><span class="pln">
}
File: MainActivity.java
package example.learnfk.com.sqlitetutorial;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.util.Log;
import java.util.List;
public class MainActivity extends AppCompatActivity {
</span><span class="lit">@Override</span><span class="pln">
</span><span class="kwd">protected</span><span class="pln"> </span><span class="kwd">void</span><span class="pln"> onCreate</span><span class="pun">(</span><span class="typ">Bundle</span><span class="pln"> savedInstanceState</span><span class="pun">)</span><span class="pln"> </span><span class="pun">{</span><span class="pln">
</span><span class="kwd">super</span><span class="pun">.</span><span class="pln">onCreate</span><span class="pun">(</span><span class="pln">savedInstanceState</span><span class="pun">);</span><span class="pln">
setContentView</span><span class="pun">(</span><span class="pln">R</span><span class="pun">.</span><span class="pln">layout</span><span class="pun">.</span><span class="pln">activity_main</span><span class="pun">);</span><span class="pln">
</span><span class="typ">DatabaseHandler</span><span class="pln"> db </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">DatabaseHandler</span><span class="pun">(</span><span class="kwd">this</span><span class="pun">);</span><span class="pln">
</span><span class="com">//插入联系人</span><span class="pln">
</span><span class="typ">Log</span><span class="pun">.</span><span class="pln">d</span><span class="pun">(</span><span class="str">"Insert: "</span><span class="pun">,</span><span class="pln"> </span><span class="str">"Inserting .."</span><span class="pun">);</span><span class="pln">
db</span><span class="pun">.</span><span class="pln">addContact</span><span class="pun">(</span><span class="kwd">new</span><span class="pln"> </span><span class="typ">Contact</span><span class="pun">(</span><span class="str">"Ravi"</span><span class="pun">,</span><span class="pln"> </span><span class="str">"9100000000"</span><span class="pun">));</span><span class="pln">
db</span><span class="pun">.</span><span class="pln">addContact</span><span class="pun">(</span><span class="kwd">new</span><span class="pln"> </span><span class="typ">Contact</span><span class="pun">(</span><span class="str">"Srinivas"</span><span class="pun">,</span><span class="pln"> </span><span class="str">"9199999999"</span><span class="pun">));</span><span class="pln">
db</span><span class="pun">.</span><span class="pln">addContact</span><span class="pun">(</span><span class="kwd">new</span><span class="pln"> </span><span class="typ">Contact</span><span class="pun">(</span><span class="str">"Tommy"</span><span class="pun">,</span><span class="pln"> </span><span class="str">"9522222222"</span><span class="pun">));</span><span class="pln">
db</span><span class="pun">.</span><span class="pln">addContact</span><span class="pun">(</span><span class="kwd">new</span><span class="pln"> </span><span class="typ">Contact</span><span class="pun">(</span><span class="str">"Karthik"</span><span class="pun">,</span><span class="pln"> </span><span class="str">"9533333333"</span><span class="pun">));</span><span class="pln">
</span><span class="com">//Reading all contacts</span><span class="pln">
</span><span class="typ">Log</span><span class="pun">.</span><span class="pln">d</span><span class="pun">(</span><span class="str">"Reading: "</span><span class="pun">,</span><span class="pln"> </span><span class="str">"Reading all contacts.."</span><span class="pun">);</span><span class="pln">
</span><span class="typ">List</span><span class="str"><contact></span><span class="pln"> contacts </span><span class="pun">=</span><span class="pln"> db</span><span class="pun">.</span><span class="pln">getAllContacts</span><span class="pun">();</span><span class="pln">
</span><span class="kwd">for</span><span class="pln"> </span><span class="pun">(</span><span class="typ">Contact</span><span class="pln"> cn </span><span class="pun">:</span><span class="pln"> contacts</span><span class="pun">)</span><span class="pln"> </span><span class="pun">{</span><span class="pln">
</span><span class="typ">String</span><span class="pln"> log </span><span class="pun">=</span><span class="pln"> </span><span class="str">"Id: "</span><span class="pln"> </span><span class="pun">+</span><span class="pln"> cn</span><span class="pun">.</span><span class="pln">getID</span><span class="pun">()</span><span class="pln"> </span><span class="pun">+</span><span class="pln"> </span><span class="str">" ,Name: "</span><span class="pln"> </span><span class="pun">+</span><span class="pln"> cn</span><span class="pun">.</span><span class="pln">getName</span><span class="pun">()</span><span class="pln"> </span><span class="pun">+</span><span class="pln"> </span><span class="str">" ,Phone: "</span><span class="pln"> </span><span class="pun">+</span><span class="pln">
cn</span><span class="pun">.</span><span class="pln">getPhoneNumber</span><span class="pun">();</span><span class="pln">
</span><span class="com">//将联系人写入日志</span><span class="pln">
</span><span class="typ">Log</span><span class="pun">.</span><span class="pln">d</span><span class="pun">(</span><span class="str">"Name: "</span><span class="pun">,</span><span class="pln"> log</span><span class="pun">);</span><span class="pln">
</span><span class="pun">}</span><span class="pln">
</span><span class="pun">}</span><span class="pln">
}
输出:
