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; } }
