SQLite数据库增删
发布时间：2020-12-12 19:38:41 所属栏目：百科 来源：网络整理
导读：SQLite数据库增删改查操作案例 Person实体类 package com.ljq.domain; public class Person { private Integer id; private String name; private String phone; public Person() { super (); } public Person(String name,String phone) { super (); this .
SQLite数据库增删改查操作案例Person实体类
package
com.ljq.domain;
public
class
Person {
private
Integer id;
private
String name;
private
String phone;
public
Person() {
super
(); }
public
Person(String name,String phone) {
super
();
this
.name
=
name;
this
.phone
=
phone; }
public
Person(Integer id,String name,255); line-height:1.5!important">this
.id
=
id;
public
Integer getId() {
return
id; }
void
setId(Integer id) {
this
.id
=
id; }
public
String getName() {
return
name; }
void
setName(String name) {
this
.name
=
name; }
public
String getPhone() {
return
phone; }
void
setPhone(String phone) {
this
.phone
=
phone; } }
DBOpenHelper数据库关联类 package com.ljq.db; import android.content.Context; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; class DBOpenHelper extends SQLiteOpenHelper { // 类没有实例化,是不能用作父类构造器的参数,必须声明为静态 private static final String DBNAME = " ljq.db " ; final int VERSION = 1 ; 第三个参数CursorFactory指定在执行查询时获得一个游标实例的工厂类, 设置为null,代表使用系统默认的工厂类 public DBOpenHelper(Context context) { super (context,DBNAME,255); line-height:1.5!important">null ,VERSION); } @Override void onCreate(SQLiteDatabase db) { db.execSQL( " CREATE TABLE PERSON (ID INTEGER PRIMARY KEY AUTOINCREMENT,NAME VARCHAR(20),PHONE VARCHAR(20)) " ); } @Override void onUpgrade(SQLiteDatabase db,255); line-height:1.5!important">int oldVersion,255); line-height:1.5!important">int newVersion) { 注：生产环境上不能做删除操作 db.execSQL( " DROP TABLE IF EXISTS PERSON " ); onCreate(db); } }PersonService业务类 import java.util.ArrayList; import java.util.List; import android.database.Cursor; import com.ljq.domain.Person; class PersonService { private DBOpenHelper dbOpenHelper = null ; /** * 构造函数 * * 调用getWritableDatabase()或getReadableDatabase()方法后，会缓存SQLiteDatabase实例； * 因为这里是手机应用程序，一般只有一个用户访问数据库，所以建议不关闭数据库，保持连接状态。 * getWritableDatabase()，getReadableDatabase的区别是当数据库写满时，调用前者会报错，调用后者不会， * 所以如果不是更新数据库的话，最好调用后者来获得数据库连接。 * * 对于熟悉SQL语句的程序员最好使用exeSQL(),rawQuery(),因为比较直观明了 * * @param context */ public PersonService(Context context){ dbOpenHelper = new DBOpenHelper(context); } void save(Person person){ dbOpenHelper.getWritableDatabase().execSQL( " insert into person(name,phone) values (?,?) " ,255); line-height:1.5!important">new Object[]{person.getName(),person.getPhone()}); } void update(Person person){ dbOpenHelper.getWritableDatabase().execSQL( " update person set name=?,phone=? where id=? " ,person.getPhone(),person.getId()}); } void delete(Integer... ids){ if (ids.length > 0 ){ StringBuffer sb = new StringBuffer(); for (Integer id : ids){ sb.append( " ? " ).append( " , " ); } sb.deleteCharAt(sb.length() - 1 ); dbOpenHelper.getWritableDatabase().execSQL( " delete from person where id in ( " + sb + " ) " ,(Object[])ids); } } public Person find(Integer id){ Cursor cursor = dbOpenHelper.getReadableDatabase().rawQuery( " select id,name,phone from person where id=? " ,255); line-height:1.5!important">new String[]{String.valueOf(id)}); if (cursor.moveToNext()){ int personid = cursor.getInt( 0 ); String name = cursor.getString( 1 ); String phone = cursor.getString( 2 ); return new Person(personid,phone); } null ; } long getCount(){ Cursor cursor = dbOpenHelper.getReadableDatabase().query( " person " ,255); line-height:1.5!important">new String[]{ " count(*) " },255); line-height:1.5!important">null ); return cursor.getLong( 0 ); } return 0 ; } * 分页 * * startResult 偏移量，默认从0开始 * maxResult 每页显示的条数 * @return public List < Person > getScrollData( int startResult,255); line-height:1.5!important">int maxResult){ List < Person > persons = new ArrayList < Person > (); Cursor cursor = dbOpenHelper.getReadableDatabase().query("person",new String[]{"id,phone"},0); line-height:1.5!important"> "name like ?",new String[]{"%ljq%"},null,"id desc","1,2"); Cursor cursor = dbOpenHelper.getReadableDatabase().rawQuery( " select * from person limit ?,? " ,255); line-height:1.5!important">new String[]{String.valueOf(startResult),String.valueOf(maxResult)}); while (cursor.moveToNext()) { int personid = cursor.getInt( 0 ); String name = cursor.getString( 1 ); String phone = cursor.getString( 2 ); persons.add( return persons; } }PersonServiceTest测试类 package com.ljq.test; import com.ljq.db.PersonService; import android.test.AndroidTestCase; import android.util.Log; class PersonServiceTest extends AndroidTestCase{ final String TAG = " PersonServiceTest " ; void testSave() throws Exception{ PersonService personService = new PersonService( this .getContext()); personService.save( new Person( " zhangsan1 " , " 059188893343 " )); personService.save( new Person( " zhangsan2 " ,255); line-height:1.5!important">new Person( " zhangsan3 " ,255); line-height:1.5!important">new Person( " zhangsan4 " ,255); line-height:1.5!important">new Person( " zhangsan5 " , " 059188893343 " )); } void testUpdate() this .getContext()); Person person = personService.find( 1 ); person.setName( " linjiqin " ); personService.update(person); } void testFind() this .getContext()); Person person = personService.find( 1 ); Log.i(TAG,person.getName()); } void testList() this .getContext()); List < Person > persons = personService.getScrollData( 0 , 10 ); for (Person person : persons){ Log.i(TAG,person.getId() + " : " + person.getName()); } } void testCount() this .getContext()); Log.i(TAG,String.valueOf(personService.getCount())); } void testDelete() this .getContext()); personService.delete( 1 ); } void testDeleteMore() this .getContext()); personService.delete( new Integer[]{ 2 , 5 , 6 }); } }（编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |