在Android应用中使用SQLite数据库（传智播客视频笔记）

在Android应用中使用SQLite数据库

应用生成的数据库文件保存为/data/data/应用包名/databases/name.db，生成的文件可用SQLite Expert打开

（name是自定义的，例如，我的应用是保存在：/data/data/com.sinaapp.sql/databases/ssun.db中）

SQLiteActivity.java源码：

package com.sinaapp.sql; import com.sinaapp.sql.R; import android.app.Activity; import android.os.Bundle; public class SQLiteActivity extends Activity { /** Called when the activity is first created. */ @Override public void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.main); } }

Pseron.java源码：

package com.sinaapp.domain; public class Person { private String name; private Integer id; private String phone; private Integer amount; public Person() {} @Override public String toString() { return "Person [name=" + name + ", id=" + id + ", phone=" + phone + ", amount=" + amount + "]"; } public Person(String name, Integer id, String phone, Integer amount) { this.name = name; this.id = id; this.phone = phone; this.amount = amount; } public Person(String name, String phone, Integer amount) { this.name = name; this.phone = phone; this.amount = amount; } public Integer getAmount() { return amount; } public void setAmount(Integer amount) { this.amount = amount; } public String getName() { return name; } public void setName(String name) { this.name = name; } public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getPhone() { return phone; } public void setPhone(String phone) { this.phone = phone; } }

DBOpenHelper.java源码：

package com.sinaapp.service; import android.content.Context; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; public class DBOpenHelper extends SQLiteOpenHelper { public DBOpenHelper(Context context) { super(context, "ssun.db", null, 3);//数据库版本号改变，将会执行onUpgrade方法 } @Override public void onCreate(SQLiteDatabase db) {//第一次使用时执行的方法 // TODO Auto-generated method stub db.execSQL("create table person(id integer primary key autoincrement,name varchar(20),phone varchar(20))"); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { // TODO Auto-generated method stub db.execSQL("alter table person add amount int(10)"); } }

PersonService.java源码：

package com.sinaapp.service; import java.util.ArrayList; import java.util.List; import android.content.Context; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import com.sinaapp.domain.Person; public class PersonService { private DBOpenHelper helper; public PersonService(Context context){ this.helper = new DBOpenHelper(context); } public void payment(){ SQLiteDatabase db = helper.getWritableDatabase(); try{ db.beginTransaction(); db.execSQL("update person set amount = amount - 10 where id = 3"); db.execSQL("update person set amount = amount + 10 where id = 4"); db.setTransactionSuccessful(); }finally{ db.endTransaction(); } } /** * 添加记录 * @param person */ public void add(Person person){ SQLiteDatabase db = helper.getWritableDatabase(); db.execSQL("insert into person(name,phone,amount) values(?,?,?) ", new Object[]{person.getName(),person.getPhone(),person.getAmount()}); } /** * 删除记录 * @param integer 将要删除的记录的ID号 */ public void delete(Integer integer){ SQLiteDatabase db = helper.getWritableDatabase(); db.execSQL("delete from person where id = ?",new Object[]{integer}); } /** * 更新记录 * @param person 更新的记录 */ public void update(Person person){ SQLiteDatabase db = helper.getWritableDatabase(); db.execSQL("update person set name=?, phone=?, amount=? where id = ?", new Object[]{person.getName(),person.getPhone(),person.getAmount(),person.getId()}); } /** * 查找记录 * @param integer * @return */ public Person find(Integer integer){ Person person = null; SQLiteDatabase db = helper.getWritableDatabase(); Cursor cursor = db.rawQuery("select * from person where id = ?", new String[]{integer.toString()}); if(cursor.moveToFirst()){ int id = cursor.getInt(cursor.getColumnIndex("id")); String name = cursor.getString(cursor.getColumnIndex("name")); String phone = cursor.getString(cursor.getColumnIndex("phone")); int amount = cursor.getInt(cursor.getColumnIndex("amount")); person = new Person(name,id,phone,amount); } return person; } /** * 分页显示记录 * @param start 开始记录号 * @param length 记录长度 * @return */ public List<Person> getPersons(Integer start, Integer length){ List<Person> persons = new ArrayList<Person>(); Person person = null; SQLiteDatabase db = helper.getWritableDatabase(); Cursor cursor = db.rawQuery("select * from person order by id limit ?, ?", new String[]{start.toString(),length.toString()}); while(cursor.moveToNext()){ int id = cursor.getInt(cursor.getColumnIndex("id")); String name = cursor.getString(cursor.getColumnIndex("name")); String phone = cursor.getString(cursor.getColumnIndex("phone")); int amount = cursor.getInt(cursor.getColumnIndex("amount")); person = new Person(name,id,phone,amount); persons.add(person); person = null; } cursor.close(); return persons; } /** * 得到记录数 * @return */ public Integer getCounts(){ SQLiteDatabase db = helper.getWritableDatabase(); Cursor cursor = db.rawQuery("select count(*) from person",null); cursor.moveToFirst(); Integer counts = cursor.getInt(0); return counts; } }

单元测试TestService.java源码：

package com.sinaapp.test; import java.util.List; import android.test.AndroidTestCase; import android.util.Log; import com.sinaapp.domain.Person; import com.sinaapp.service.DBOpenHelper; import com.sinaapp.service.PersonService; public class TestService extends AndroidTestCase { public void testCreateDB() { DBOpenHelper helper = new DBOpenHelper(this.getContext()); helper.getWritableDatabase(); } public void testAdd(){ PersonService service = new PersonService(this.getContext()); service.add(new Person("luolin","12345",100)); } public void testUpdate(){ PersonService service = new PersonService(this.getContext()); service.update(new Person("zhang",4,"2222222",200)); } public void testFind(){ PersonService service = new PersonService(this.getContext()); Person person = service.find(1); Log.i("TestService", person.toString()); } public void testDel(){ PersonService service = new PersonService(this.getContext()); service.delete(2); } public void testGetCounts(){ PersonService service = new PersonService(this.getContext()); Integer i = service.getCounts(); Log.i("TestService", i.toString()); } public void testGetPersons(){ PersonService service = new PersonService(this.getContext()); List<Person> persons = service.getPersons(0, 4); for(Person p : persons){ Log.i("TestService", p.toString()); } } public void testPayment(){ PersonService service = new PersonService(this.getContext()); service.payment(); } }

AndroidManifest.xml

<?xml version="1.0" encoding="utf-8"?> <manifest xmlns:android="http://schemas.android.com/apk/res/android" package="com.sinaapp.sql" android:versionCode="1" android:versionName="1.0" > <uses-sdk android:minSdkVersion="8" /> <application android:icon="@drawable/ic_launcher" android:label="@string/app_name" > <activity android:name=".SQLiteActivity" android:label="@string/app_name" > <intent-filter> <action android:name="android.intent.action.MAIN" /> <category android:name="android.intent.category.LAUNCHER" /> </intent-filter> </activity> <uses-library android:name="android.test.runner"/> </application> <instrumentation android:name="android.test.InstrumentationTestRunner" android:targetPackage="com.sinaapp.sql" /> </manifest>

转载于:https://www.cnblogs.com/android-html5/archive/2012/05/31/2533886.html