下面直接看代碼:
MyDBHelper.java(創建數據庫,添加一列phone)
復制代碼
package com.amos.android_database;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
/**
* Created by amosli on 14-6-10.
*/
public class MyDBHelper extends SQLiteOpenHelper{
/**
* 創建數據庫的構造方法
* @param context 應用程序上下文
* name 數據庫的名字
* factory 查詢數據庫的游標工廠一般情況下用sdk默認的
* version 數據庫的版本一般大於0
*/
public MyDBHelper(Context context) {
super(context, "test.db", null, 4);
}
private String tag = "MyDBHelper.class";
/**
* 在數據庫第一次創建時會執行
* @param db
*/
@Override
public void onCreate(SQLiteDatabase db) {
Log.d(tag, "onCreate.....");
//創建一個數據庫
db.execSQL("create table person (personid integer primary key autoincrement ,name varchar(30) )");
}
/**
* 更新數據的時候調用的方法
* @param db
* @param oldVersion
* @param newVersion
*/
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
Log.d(tag,"onUpgrade*******");
//增加一列
db.execSQL("alter table person add phone varchar(13) null");
}
}
復制代碼
PersonDao.java(增刪查改都在這了)
復制代碼
package com.amos.android_database.dao;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;
import com.amos.android_database.MyDBHelper;
import com.amos.android_database.domain.Person;
import java.util.ArrayList;
import java.util.List;
/**
* Created by amosli on 14-6-11.
*/
public class PersonDao {
private MyDBHelper myDBHelper;
public String tag = "PersonDao.class";
//在new出來的時候就實現myDBHelper初始化
public PersonDao(Context context) {
myDBHelper = new MyDBHelper(context);
}
//增加
public void addPerson(String name, String phone) {
SQLiteDatabase database = myDBHelper.getWritableDatabase();
//先判斷數據庫是否可用
if (database.isOpen()) {
//執行插入操作
//database.execSQL("insert into person (name,phone) values('"+name+"','"+phone+"')");
//推薦如下寫法
database.execSQL("insert into person (name,phone) values(?,?)", new Object[]{name, phone});
database.close();
}
}
//查找
public boolean findPerson(String phone) {
boolean result = false;
SQLiteDatabase database = myDBHelper.getReadableDatabase();
if (database.isOpen()) {
//database.execSQL("select * from phone='"+phone+"'");
Cursor cursor = database.rawQuery("select * from person where phone=?", new String[]{phone});
if (cursor.moveToFirst()) {//游標是否移動到下一行,如果是,那說明有數據返回
Log.d(tag, "count:" + cursor.getColumnCount());
int nameIndex = cursor.getColumnIndex("name");
Log.d(tag, "name:" + cursor.getString(nameIndex));
cursor.close();
result = true;
} else {
result = false;
}
database.close();
}
return result;
}
//刪除一條數據
public void deletePerson(String phone) {
SQLiteDatabase database = myDBHelper.getWritableDatabase();
if (database.isOpen()) {
database.execSQL("delete from person where phone=?", new Object[]{phone});
}
database.close();
}
//更新一條數據
public void updatePerson(String phone, String newName, String newPhone) {
SQLiteDatabase database = myDBHelper.getWritableDatabase();
if (database.isOpen()) {
database.execSQL("update person set name=?,phone=? where phone=?", new Object[]{newName, newPhone, phone});
}
database.close();
}
//查找所有person
public List<Person> findAllPerson(){
List<Person> personList = new ArrayList<Person>();
SQLiteDatabase database = myDBHelper.getReadableDatabase();
if(database.isOpen()){
Cursor cursor = database.rawQuery("select * from person ", null);
while(cursor.moveToNext()){
int nameIndex = cursor.getColumnIndex("name");
int phoneIndex = cursor.getColumnIndex("phone");
String name = cursor.getString(nameIndex);
String phone = cursor.getString(phoneIndex);
Person person = new Person(name,phone);
Log.d(tag,person.toString());
personList.add(person);
}
}
database.close();
return personList;
}
}
復制代碼
注:
１.由上面的方法可以看到,查找數據主要調用的是rawQuery方法,而增刪改都是通過execSQL執行數據的
２.一定要注意,使用database前一定要判斷是否可用,使用database.isOpen();
3.使用完database一定要注意關閉數據庫,database.close();不然會報異常!
AndroidManifest.xml(配置測試環境,看加粗部分)
復制代碼
<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
package="com.amos.android_database"
android:versionCode="1"
android:versionName="1.0">
<instrumentation android:name="android.test.InstrumentationTestRunner"
android:targetPackage="com.amos.android_database"/>
<uses-sdk android:minSdkVersion="7"/>
<application android:label="@string/app_name">
<uses-library android:name="android.test.runner"/>
<activity android:name="MyActivity"
android:label="@string/app_name">
<intent-filter>
<action android:name="android.intent.action.MAIN"/>
<category android:name="android.intent.category.LAUNCHER"/>
</intent-filter>
</activity>
</application>
</manifest>
復制代碼
測試類PersonDaoTest.java
復制代碼
package com.amos.android_database.test;
import android.test.AndroidTestCase;
import android.util.Log;
import com.amos.android_database.dao.PersonDao;
import com.amos.android_database.domain.Person;
/**
* Created by amosli on 14-6-11.
*/
public class PersonDaoTest extends AndroidTestCase{
private String tag = "PersonDaoTest.class";
public void testAdd() throws Exception{
PersonDao personDao = new PersonDao(getContext());
for(int i=0;i<100;i++){
personDao.addPerson("amsoli"+i,"131888870"+i);
}
Log.d(tag,"添加一些新用戶");
}
public void testFind(){
PersonDao personDao = new PersonDao(getContext());
boolean result = personDao.findPerson("13188887776");
assertEquals(true,result);
}
public void testDelete(){
PersonDao personDao = new PersonDao(getContext());
personDao.deletePerson("13188887778");
}
public void testUpdate(){
PersonDao personDao = new PersonDao(getContext());
personDao.updatePerson("13188887776","hi_amos","13188887775");
}
public void testFindAllPerson(){
PersonDao personDao = new PersonDao(getContext());
for (Person person : personDao.findAllPerson()) {
System.out.println(person.toString());
}
}
}
復制代碼
由於要反復測試,所以如果將數據庫反復導出會非常麻煩,下面可以使用命令進行查看數據庫中的數據:
1)進行命令行模式,注意是在platform-tools目錄下,而非tools目錄
sdk/platform-tools$ ./adb shell
2)使用cd命令切換到test.db的目錄
#cd /data/data/com.amos.android_database/databases/
# ls
test.db
3)使用sqlite3打開test.db
復制代碼
# sqlite3 test.db
SQLite version 3.5.9
Enter ".help" for instructions
sqlite> .database
seq name file
--- --------------- ----------------------------------------------------------
0 main /data/data/com.amos.android_database/databases/test.db
sqlite> .table
android_metadata person
復制代碼
查看其中的數據:
復制代碼
sqlite> select * from person;
1|amsoli0|1318888700
2|amsoli1|1318888701
3|amsoli2|1318888702
4|amsoli3|1318888703
5|amsoli4|1318888704
6|amsoli5|1318888705
7|amsoli6|1318888706
8|amsoli7|1318888707
9|amsoli8|1318888708
.........
如何設置ES文件浏覽器連接電腦管理需要管理賬號
我的Android進階之旅------)Android采用AES+RSA的加密機制對http請求進行加密
Android 從源碼的角度分析——為什麼要用newInstance來實例化Fragment
【android】仿360手機衛士的簡易設計思路及源碼
