sqlite增删改查（一）及SqliteDev工具下载地址
1、sqlliet简单介绍及相关技术要点
A、可以保存任何类型的数据不限长度 但整形主键的话必须为整数
只能编写标准sql语句
分页和mysql一样
imit 5 offset 3
limit 3,5 跳过前面3条
B、写一个类继承SQLiteOpenHelper
数据库文件所在路径为：应用的包下面的databases
最好是少建类 可以少占内存
SQLiteDatabase database= openHelp.getWritableDatabase();//对数据要更改,该方法以读和写的方式打开数据库
SQLiteDatabase database2=openHelp.getReadableDatabase();//先用可以写的方式打开数据库，如果打开失败再以只读的方式打开数据库
C、SqliteDev工具下载地址http://download.csdn.net/source/3302231
Person
package com.tjp.model;
public class Person {
private int personId;
private String name;
public Person() {
super();
}
public Person(int personId, String name) {
super();
this.personId = personId;
this.name = name;
}
public int getPersonId() {
return personId;
}
public void setPersonId(int personId) {
this.personId = personId;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@Override
public String toString() {
// TODO Auto-generated method stub
return "name="+name+" personId="+personId;
}
}
DBOpenHelp
package com.tjp.service;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
public class DBOpenHelp extends SQLiteOpenHelper {
private static final String DBNAME="tjp.db";//数据库名称
private static final int DBVERSION=1;//数据版本
public DBOpenHelp(Context context, String name, CursorFactory factory,
int version) {
super(context, name, factory, version);//context 上下文 ，name 数据库名称以db为后缀名 ， factory 游标工厂 version数据库版本号
// TODO Auto-generated constructor stub
}
public DBOpenHelp(Context context) {
super(context, DBNAME, null, DBVERSION);
}
/**
* 数据库第一次创建的时候被调用
*/
@Override
public void onCreate(SQLiteDatabase db) {
String sql="create table person(personid integer primary key autoincrement,name varchar(20))";
db.execSQL(sql); //执行有更新行为的sql语句
}
/**
* 在软件升级的时候，当数据库的版本发送改变的时候
*/
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO Auto-generated method stub
}
}
PersonService
package com.tjp.service;
import java.util.ArrayList;
import java.util.List;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import com.tjp.model.Person;
public class PersonService {
private DBOpenHelp openHelp=null;
private Context context;
public PersonService(Context context) {
this.context = context;
openHelp=new DBOpenHelp(context);
}
public void save(Person person){
SQLiteDatabase database= openHelp.getWritableDatabase();//对数据要更改,该方法以读和写的方式打开数据库,有缓存功能
database.execSQL("insert into person(name) values(?)",new Object[]{person.getName()});
}
public void update(Person person){
SQLiteDatabase database= openHelp.getWritableDatabase();
database.execSQL("update person set name=? where personid=?",new Object[]{person.getName(),person.getPersonId()});
}
public void delete(int personId){
SQLiteDatabase database= openHelp.getWritableDatabase();
database.execSQL("delete from person where personid=?",new Object[]{personId});
}
public Person find(int personId){
SQLiteDatabase database= openHelp.getWritableDatabase();
Person person=null;
Cursor cursor= database.rawQuery("select * from person where personid=?", new String[]{String.valueOf(personId).toString()});
if(cursor.moveToFirst()){//如果移动成功了表示存在
int personIda =cursor.getInt(cursor.getColumnIndex("personid"));
String name=cursor.getString(cursor.getColumnIndex("name"));
person=new Person(personIda,name);
}
cursor.close();
return person;
}
public List<Person> getScrollDate(int offerset,int maxResult){
List<Person> persons=new ArrayList<Person>();
SQLiteDatabase database= openHelp.getWritableDatabase();
String sql="select * from person limit ?,?";
Cursor cursor= database.rawQuery(sql, new String[]{String.valueOf(offerset),String.valueOf(maxResult)});
while(cursor.moveToNext()){
int personIda =cursor.getInt(cursor.getColumnIndex("personid"));
String name=cursor.getString(cursor.getColumnIndex("name"));
Person person=new Person(personIda,name);
persons.add(person);
}
return persons;
}
public long getCount(){
SQLiteDatabase database= openHelp.getWritableDatabase();
Cursor cursor= database.rawQuery("select count(*) from person", null);
cursor.moveToFirst();
int count=cursor.getInt(0);
cursor.close();
return count;
}
}
PersonServiceTest
package com.tjp.db;
import java.util.List;
import com.tjp.model.Person;
import com.tjp.service.DBOpenHelp;
import com.tjp.service.PersonService;
import android.test.AndroidTestCase;
import android.util.Log;
public class PersonServiceTest extends AndroidTestCase {
private static final String TAG="PersonServiceTest";
public void testCreateDb()throws Exception{
DBOpenHelp help=new DBOpenHelp(this.getContext());
help.getWritableDatabase();//第一次调用该方法创建数据库
}
public void testsave()throws Exception{
PersonService personService=new PersonService(this.getContext());
Person person=new Person(1,"谭建平");
personService.save(person);
Person person1=new Person(1,"谭建平1");
personService.save(person1);
Person person2=new Person(1,"谭建平2");
personService.save(person2);
Person person3=new Person(1,"谭建平3");
personService.save(person3);
Person person4=new Person(1,"谭建平4");
personService.save(person4);
Person person5=new Person(1,"谭建平5");
personService.save(person5);
Person person6=new Person(1,"谭建平6");
personService.save(person6);
}
public void testupdate()throws Exception{
PersonService personService=new PersonService(this.getContext());
Person person=new Person(1,"老李");
personService.update(person);
}
public void testdelete()throws Exception{
PersonService personService=new PersonService(this.getContext());
personService.delete(1);
}
public void tesfind()throws Exception{
PersonService personService=new PersonService(this.getContext());
Person person=personService.find(1);
Log.i(TAG, person.getName());
}
public void testscorll()throws Exception{
PersonService personService=new PersonService(this.getContext());
List<Person> persons=personService.getScrollDate(0, 2);
for(Person person : persons){
Log.i(TAG, person.toString());
}
}
public void testcount()throws Exception{
PersonService personService=new PersonService(this.getContext());
Log.i(TAG, personService.getCount()+"");
}
}
