Sqlite 3添削変更操作例
(1)androidでsqliteデータベースを使用するには、まずSQLiteOpenHerperというクラスを理解する必要があります.データベースの初期化を実現するためのクラスです.このクラスを継承し、データベースを初期化する必要があります.
DBOpenHelper.java
(2)次に、データベースの内容を操作するビジネスクラスが必要です.また、データベース内の異なるテーブルを操作するビジネスクラスがたくさんある可能性があります.ここではPersonServiceビジネスクラスを書きます.そのデータクラスはPersonです.
Person:
PersonService:
(3)テストクラスPersonServiceTestを書く必要があります.
DBOpenHelper.java
package com.xiaoming.domain;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
public class DBOpenHelper extends SQLiteOpenHelper{
public DBOpenHelper(Context context){
super(context, "sqlite.db", null, 1);
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("create table persons (_id integer primary key autoincrement," +
"name varchar null," +
"age int null);" );
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO Auto-generated method stub
}
}
(2)次に、データベースの内容を操作するビジネスクラスが必要です.また、データベース内の異なるテーブルを操作するビジネスクラスがたくさんある可能性があります.ここではPersonServiceビジネスクラスを書きます.そのデータクラスはPersonです.
Person:
package com.xiaoming.domain;
public class Person {
private String name;
private Integer age;
private Integer personid;
public Person() {
name= null;
age = null;
personid = null;
}
public Person(Integer personid,String name, int age) {
this.personid = personid;
this.name = name;
this.age = age;
}
public Person(String name, int age) {
this.personid = null;
this.name = name;
this.age = age;
}
public Integer getPersonid() {
return personid;
}
public void setPersonid(Integer personid) {
this.personid = personid;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
}PersonService:
package com.xiaoming.service;
import java.util.ArrayList;
import java.util.List;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import com.xiaoming.domain.DBOpenHelper;
import com.xiaoming.domain.Person;
public class PersonService {
Context context = null;
DBOpenHelper dbOpenHelper = null;
public PersonService(Context context) {
this.context = context;
dbOpenHelper = new DBOpenHelper( context );
}
/**
*
* @param p
*/
public void insert(Person p)
{
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
db.execSQL("insert into persons(name,age) values(?,?);",
new Object[]{p.getName(),p.getAge()});
}
/**
*
* @param id
*/
public void delete(Integer id)
{
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
db.execSQL("delete from persons where _id=?;",
new Object[]{id});
}
/**
*
* @param p
*/
public void update(Person p)
{
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
db.execSQL("update persons set name=?,age=? where _id=?",
new Object[]{p.getName(),p.getAge(),p.getPersonid()} );
}
/**
*
* @param id
* @return
*/
public Person find(Integer id)
{
SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
Cursor cursor = db.rawQuery("select * from persons where _id=?", new String[]{id.toString()}) ;
if( cursor.moveToFirst() )
{
int personid = cursor.getInt(cursor.getColumnIndex("_id"));
String name = cursor.getString(cursor.getColumnIndex("name"));
int age = cursor.getInt(cursor.getColumnIndex("age"));
return new Person(personid,name,age);
}
cursor.close();
return null;
}
/**
*
* @param offset
* @param maxResult
* @return
*/
public List<Person> getScrollData(int offset, int maxResult)
{
List<Person> personlist = new ArrayList<Person>();
SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
Cursor cursor = db.rawQuery("select * from persons order by _id asc limit ?,? ",
new String[]{String.valueOf(offset), String.valueOf(maxResult)}) ;
while( cursor.moveToNext() )
{
int personid = cursor.getInt(cursor.getColumnIndex("_id"));
String name = cursor.getString(cursor.getColumnIndex("name"));
int age = cursor.getInt(cursor.getColumnIndex("age"));
personlist.add(new Person(personid,name,age) );
}
cursor.close();
return personlist;
}
/**
*
* @return
*/
public long getCount()
{
SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
Cursor cursor = db.rawQuery("select count(*) from persons",null );
cursor.moveToFirst();
long result = cursor.getLong(0);
return result;
}
}
(3)テストクラスPersonServiceTestを書く必要があります.
package com.xiaoming.test;
import java.util.ArrayList;
import java.util.List;
import com.xiaoming.domain.DBOpenHelper;
import com.xiaoming.domain.Person;
import com.xiaoming.service.PersonService;
import android.test.AndroidTestCase;
import android.util.Log;
public class PersonServiceTest extends AndroidTestCase {
private static final String TAG = "PersonServiceTest";
public void testInsert()
{
PersonService pService = new PersonService(getContext());
Person p = new Person(" ",40);
Person p1 = new Person(" ",40);
Person p2 = new Person(" ",40);
Person p3 = new Person(" ",40);
Person p4 = new Person(" ",40);
Person p5 = new Person(" ",40);
Person p6 = new Person(" ",40);
Person p7 = new Person(" ",40);
pService.insert(p1);
pService.insert(p2);
pService.insert(p3);
pService.insert(p4);
pService.insert(p5);
pService.insert(p6);
pService.insert(p7);
}
public void testdelete()
{
PersonService pService = new PersonService(getContext());
pService.delete(1);
}
public void testUpdate()
{
PersonService pService = new PersonService(getContext());
Person p = pService.find(5);
if( p == null )
{
Log.i(TAG,"id="+p.getPersonid()+" ");
return ;
}
p.setName(" ");
pService.update(p);
}
public void testFind()
{
PersonService pService = new PersonService(getContext());
Person p = pService.find(2);
Log.i(TAG,"_id="+p.getPersonid()+", name="+p.getName()+", age="+p.getAge());
}
public void testGetScrollData()
{
PersonService pService = new PersonService(getContext());
ArrayList<Person> pList = (ArrayList<Person>) pService.getScrollData(1, 5);
for(Person p:pList)
{
Log.i(TAG,"_id="+p.getPersonid()+", name="+p.getName()+", age="+p.getAge());
}
}
public void testGetCount()
{
PersonService pService = new PersonService(getContext());
long count = pService.getCount();
Log.i(TAG,""+count);
}
}