Android: SQLiteOpenHelper

Android: SQLiteOpenHelper

package com.example.domain;public class Person {private int id;private String name;public Person() {super();}public Person(int personid, String name) {this.id = personid;this.name = name;}public int getId() {return id;}public void setId(int id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}}

package com.example.service;import java.util.ArrayList;import java.util.List;import android.content.Context;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import com.example.domain.Person;public class PersonService {private DBOpenHelper dbOpenHelper;//private Context context;public PersonService(Context context) {this.dbOpenHelper = new DBOpenHelper(context);}public void save(Person person) {SQLiteDatabase db = dbOpenHelper.getWritableDatabase();db.execSQL("insert into person (name) values (?)",new Object[] { person.getName() });}public void update(Person person) {SQLiteDatabase db = dbOpenHelper.getWritableDatabase();db.execSQL("update person set name=? where personid=?", new Object[] {person.getName(), person.getId() });}public void delete(Integer id) {SQLiteDatabase db = dbOpenHelper.getWritableDatabase();db.execSQL("delete from person where personid=?",new Object[] { id.toString() });}public Person find(Integer id) {SQLiteDatabase db = dbOpenHelper.getReadableDatabase();Cursor cursor = db.rawQuery("select * from person where personid=?",new String[] { id.toString() });if (cursor.moveToFirst()) {int personid = cursor.getInt(cursor.getColumnIndex("personid"));String name = cursor.getString(cursor.getColumnIndex("personid"));return new Person(personid, name);}return null;}public List
 
   getScrollDate(Integer offset, Integer maxResult) {List
  
    persons = new ArrayList
   
    ();SQLiteDatabase db = dbOpenHelper.getReadableDatabase();Cursor cursor = db.rawQuery("select * from person limit ?,?",new String[] { offset.toString(), maxResult.toString() });while (cursor.moveToNext()) {int personid = cursor.getInt(cursor.getColumnIndex("personid"));String name = cursor.getString(cursor.getColumnIndex("personid"));Person person = new Person(personid, name);persons.add(person);}cursor.close();return persons;}public long getCount() {SQLiteDatabase db = dbOpenHelper.getReadableDatabase();Cursor cursor = db.rawQuery("select count(*) from person ", null);cursor.moveToFirst();return cursor.getLong(0);}}
   
  
 

package com.example.service;import java.util.ArrayList;import java.util.List;import android.content.ContentValues;import android.content.Context;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import com.example.domain.Person;public class OtherPersonService {private DBOpenHelper dbOpenHelper;public OtherPersonService(Context context) {this.dbOpenHelper = new DBOpenHelper(context);}public void save(Person person) {SQLiteDatabase db = dbOpenHelper.getWritableDatabase();ContentValues values = new ContentValues();values.put("name", person.getName());db.insert("person", null, values);}public void update(Person person) {SQLiteDatabase db = dbOpenHelper.getWritableDatabase();ContentValues values = new ContentValues();values.put("name", person.getName());db.update("person", values, "personid=?", new String[] { (new Integer(person.getId())).toString() });}public void delete(Integer id) {SQLiteDatabase db = dbOpenHelper.getWritableDatabase();db.delete("person", "personid=?", new String[] { id.toString() });}public Person find(Integer id) {SQLiteDatabase db = dbOpenHelper.getReadableDatabase();Cursor cursor = db.query("person", new String[] { "personid", "name" },"personid=?", new String[] { id.toString() }, null, null, null);if (cursor.moveToFirst()) {int personid = cursor.getInt(cursor.getColumnIndex("personid"));String name = cursor.getString(cursor.getColumnIndex("personid"));return new Person(personid, name);}return null;}public List
 
   getScrollDate(Integer offset, Integer maxResult) {List
  
    persons = new ArrayList
   
    ();SQLiteDatabase db = dbOpenHelper.getReadableDatabase();Cursor cursor = db.query("person", null, null, null, null, null, null,offset + "," + maxResult);while (cursor.moveToNext()) {int personid = cursor.getInt(cursor.getColumnIndex("personid"));String name = cursor.getString(cursor.getColumnIndex("personid"));Person person = new Person(personid, name);persons.add(person);}cursor.close();return persons;}public long getCount() {SQLiteDatabase db = dbOpenHelper.getReadableDatabase();Cursor cursor = db.query("person", new String[] { "count(*)" }, null,null, null, null, null);cursor.moveToFirst();return cursor.getLong(0);}}
   
  
 

package com.example.service;import android.content.Context;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteOpenHelper;public class DBOpenHelper extends SQLiteOpenHelper {private static String databasename = "datatemp";private static int databaseversion = 1;public DBOpenHelper(Context context) {super(context, databasename, null, databaseversion);}@Overridepublic void onCreate(SQLiteDatabase db) {db.execSQL("CREATE TABLE IF NOT EXISTS person (personid integer primary key autoincrement, name varchar(20))");}@Overridepublic void onUpgrade(SQLiteDatabase db, int arg1, int arg2) {}}

package com.example.db;import com.example.service.DBOpenHelper;import android.content.ContentProvider;import android.content.ContentUris;import android.content.ContentValues;import android.content.UriMatcher;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import android.net.Uri;public class PersonProvider extends ContentProvider {private DBOpenHelper dbOpenHelper;private static final UriMatcher MATCHER = new UriMatcher(UriMatcher.NO_MATCH);private static final int PERSONS = 1;private static final int PERSON = 2;static {MATCHER.addURI("org.provider", "person", PERSONS);MATCHER.addURI("org.provider", "person/#", PERSON);}@Overridepublic int delete(Uri uri, String selection, String[] selectionArgs) {SQLiteDatabase db = dbOpenHelper.getWritableDatabase();int count = 0;switch (MATCHER.match(uri)) {case PERSONS:count = db.delete("person", selection, selectionArgs);return count;case PERSON:long id = ContentUris.parseId(uri);String where = "personid=" + id;if (selection != null && !"".equals(selection)) {where = selection + "and" + where;}count = db.delete("person", where, selectionArgs);return count;default:throw new IllegalArgumentException("Unknown Uri" + uri.toString());}}@Overridepublic String getType(Uri uri) {switch (MATCHER.match(uri)) {case PERSONS:return "vnd.android.curson.dir/person";case PERSON:return "vnd.android.curson.item/person";default:throw new IllegalArgumentException("Unknown Uri" + uri.toString());}}@Overridepublic Uri insert(Uri uri, ContentValues values) {SQLiteDatabase db = dbOpenHelper.getWritableDatabase();switch (MATCHER.match(uri)) {case PERSONS:long rowid = db.insert("person", "personid", values);Uri insertUri = ContentUris.withAppendedId(uri, rowid);return insertUri;default:throw new IllegalArgumentException("Unknown Uri" + uri.toString());}}@Overridepublic boolean onCreate() {this.dbOpenHelper = new DBOpenHelper(this.getContext());return false;}@Overridepublic Cursor query(Uri uri, String[] columns, String selection,String[] selectionArgs, String sortOrder) {SQLiteDatabase db = dbOpenHelper.getReadableDatabase();switch (MATCHER.match(uri)) {case PERSONS:return db.query("person", columns, selection, selectionArgs, null,null, sortOrder);case PERSON:long id = ContentUris.parseId(uri);String where = "personid=" + id;if (selection != null && !"".equals(selection)) {where = selection + "and" + where;}return db.query("person", columns, where, selectionArgs, null,null, sortOrder);default:throw new IllegalArgumentException("Unknown Uri" + uri.toString());}}@Overridepublic int update(Uri uri, ContentValues values, String selection,String[] selectionArgs) {SQLiteDatabase db = dbOpenHelper.getWritableDatabase();int count = 0;switch (MATCHER.match(uri)) {case PERSONS:count = db.update("person", values, selection, selectionArgs);return count;case PERSON:long id = ContentUris.parseId(uri);String where = "personid=" + id;if (selection != null && !"".equals(selection)) {where = selection + "and" + where;}count = db.update("person", values, where, selectionArgs);return count;default:throw new IllegalArgumentException("Unknown Uri" + uri.toString());}}}/* =============================Adroid JUnit test ContentProvider;public class AccessContentProvider extends AndroidTestCase {public void testInsert() throws Throwable {private static final String TAG = "AccessContentProvider";ContentResolver contentResolver = this.getContext().getContentResolver();Uri insertUri = Uri.parse("content://org.provider/person");ContentValues values = new ContentValues();values.put("name", "xiaosan");values.put("amount", 90);Uri uri = contentResolver.insert(insertUri, values);Log.i(TAG, uri.toString());}public void testUpdate() throws Throwable {ContentResolver contentResolver = this.getContext().getContentResolver();Uri updateUri = Uri.parse("content://org.provider/person/1");ContentValues values = new ContentValues();values.put("name", "xiaosan");values.put("amount", 90);contentResolver.update(updateUri, values, null, null);}public void testSelect() throws Throwable {ContentResolver contentResolver = this.getContext().getContentResolver();Uri selectUri = Uri.parse("content://org.provider/person");Cursor cursor = contentResolver.query(selectUri, null, null, null,"personid desc");while (cursor.moveToNext()) {int id = cursor.getInt(cursor.getColumnIndex("personid"));String name = cursor.getString(cursor.getColumnIndex("name"));int amount = cursor.getInt(cursor.getColumnIndex("amount"));Log.i(TAG, "id" + id + ",name" + name + ",amount" + amount);}}public void testDelete() throws Throwable {ContentResolver contentResolver = this.getContext().getContentResolver();Uri deleteUri = Uri.parse("content://org.provider/person/1");contentResolver.detele(deleteUri, null, null);}}// 
 // this.getContext().getContentResolver().notifyChange(uri,null);=================================== */