SQLite数据库增删改查操作案例
Person实体类
@H_301_12@package com.ljq.domain; @H_301_12@public @H_301_12@class Person { @H_301_12@private Integer id; @H_301_12@private String name; @H_301_12@private String phone; @H_301_12@public Person() { @H_301_12@super (); } @H_301_12@public Person(String name,String phone) { @H_301_12@super (); @H_301_12@this .name = name; @H_301_12@this .phone = phone; } @H_301_12@public Person(Integer id,String name,255); line-height:1.5!important">this .id = id; @H_301_12@public Integer getId() { @H_301_12@return id; } @H_301_12@void setId(Integer id) { @H_301_12@this .id = id; } @H_301_12@public String getName() { @H_301_12@return name; } @H_301_12@void setName(String name) { @H_301_12@this .name = name; } @H_301_12@public String getPhone() { @H_301_12@return phone; } @H_301_12@void setPhone(String phone) { @H_301_12@this .phone = phone; } }
DBOpenHelper数据库关联类
package
com.ljq.db;
@H_301_12@import
android.content.Context;
@H_301_12@import
android.database.sqlite.sqliteDatabase;
@H_301_12@import
android.database.sqlite.sqliteOpenHelper;
@H_301_12@class
DBOpenHelper
@H_301_12@extends
sqliteOpenHelper {
//
类没有实例化,是不能用作父类构造器的参数,必须声明为静态
@H_301_12@private
@H_301_12@static
@H_301_12@final
String DBNAME
=
"
ljq.db
"
;
@H_301_12@final
@H_301_12@int
VERSION
=
1
;
第三个参数CursorFactory指定在执行查询时获得一个游标实例的工厂类,
设置为null,代表使用系统默认的工厂类
@H_301_12@public
DBOpenHelper(Context context) {
@H_301_12@super
(context,DBNAME,255); line-height:1.5!important">null
,VERSION); } @Override
@H_301_12@void
onCreate(sqliteDatabase db) { db.execsql(
"
CREATE TABLE PERSON (ID INTEGER PRIMARY KEY AUTOINCREMENT,NAME VARCHAR(20),PHONE VARCHAR(20))
"
); } @Override
@H_301_12@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;
@H_301_12@import
java.util.List;
@H_301_12@import
android.database.Cursor;
@H_301_12@import
com.ljq.domain.Person;
@H_301_12@class
PersonService {
@H_301_12@private
DBOpenHelper dbOpenHelper
=
@H_301_12@null
;
/**
* 构造函数 * * 调用getWritableDatabase()或getReadableDatabase()方法后，会缓存sqliteDatabase实例； * 因为这里是手机应用程序，一般只有一个用户访问数据库，所以建议不关闭数据库，保持连接状态。 * getWritableDatabase()，getReadableDatabase的区别是当数据库写满时，调用前者会报错，调用后者不会， * 所以如果不是更新数据库的话，最好调用后者来获得数据库连接。 * * 对于熟悉sql语句的程序员最好使用exesql(),rawQuery(),因为比较直观明了 * *
@param
context
*/
@H_301_12@public
PersonService(Context context){ dbOpenHelper
=
@H_301_12@new
DBOpenHelper(context); }
@H_301_12@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()}); }
@H_301_12@void
update(Person person){ dbOpenHelper.getWritableDatabase().execsql(
"
update person set name=?,phone=? where id=?
"
,person.getPhone(),person.getId()}); }
@H_301_12@void
delete(Integer... ids){
@H_301_12@if
(ids.length
>
0
){ StringBuffer sb
=
@H_301_12@new
StringBuffer();
@H_301_12@for
(Integer id : ids){ sb.append(
"
?
"
).append(
"
,
"
); } sb.deleteCharAt(sb.length()
-
1
); dbOpenHelper.getWritableDatabase().execsql(
"
delete from person where id in (
"
+
sb
+
"
)
"
,(Object[])ids); } }
@H_301_12@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)});
@H_301_12@if
(cursor.moveToNext()){
@H_301_12@int
personid
=
cursor.getInt(
0
); String name
=
cursor.getString(
1
); String phone
=
cursor.getString(
2
);
@H_301_12@return
@H_301_12@new
Person(personid,phone); }
@H_301_12@null
; }
@H_301_12@long
getCount(){ Cursor cursor
=
dbOpenHelper.getReadableDatabase().query(
"
person
"
,255); line-height:1.5!important">new
String[]{
"
count(*)
"
},255); line-height:1.5!important">null
);
@H_301_12@return
cursor.getLong(
0
); }
@H_301_12@return
0
; }
* 分页 * *
startResult 偏移量，默认从0开始 *
maxResult 每页显示的条数 *
@return
@H_301_12@public
List
<
Person
>
getScrollData(
@H_301_12@int
startResult,255); line-height:1.5!important">int
maxResult){ List
<
Person
>
persons
=
@H_301_12@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)});
@H_301_12@while
(cursor.moveToNext()) {
@H_301_12@int
personid
=
cursor.getInt(
0
); String name
=
cursor.getString(
1
); String phone
=
cursor.getString(
2
); persons.add(
301_12@return
persons; } }
