搜档网
当前位置:搜档网 › SQL技术

SQL技术

-------------------------建立数据库表--------------------------
public class DataBaseOpenHelper extends SQLiteOpenHelper {
private static final String DBNAME="tyler";
private static final int version =1;
//构造方法,这里我们只保留了传入进来的对象
public DataBaseOpenHelper(Context context) {
super(context, DBNAME, null, version);

}

@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("CREATE TABLE person (personid integer primary key autoincrement, name varchar(20),age INTEGER)");

}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXISTS person");
onCreate(db);

}

}
------------------------编写业务处理类-------------------------
---------------使用标准的sql语句------------------
public class PersonService {
private DataBaseOpenHelper dbOpenHelper;
private Context context;
//构造方法。并在该方法中初始化外部类对象
public PersonService(Context context) {
this.context = context;
dbOpenHelper=new DataBaseOpenHelper(context);
}
//保存方法------------------------------------------------>增
public void save(Person person){
SQLiteDatabase database =dbOpenHelper.getWritableDatabase();

database.execSQL("insert into person(name,age)values(?,?)",new Object[]{person.getName(),person.getAge()});
//database.close();可选项 database对象内部有缓存
}
//删除方法----------------------------------------------->删
public void delete(Integer...ids){//可变参数,根据多个参数进行删除
if(ids.length()>0){
StringBuilder sb=new StringBuilder();
for(Integer id:ids){
sb.append('?').append(',');
}
sb.deleteCharAt(sb.length()-1);
SQLiteDatabase database =dbOpenHelper.getWritableDatabase();
database.execSQL("delete from person where personid in("+sb+")",(Object[])ids);}
}
//更新方法------------------------------------------------>改
public void updata(Person person){
SQLiteDatabase database =dbOpenHelper.getWritableDatabase();
database.execSQL("update person set name=?,age=? where personid=?",new Object[]{person.getName(),person.getAge(),person.getId()});
}
//查找方法------------------------------------------------>查
public Person find(Integer id){
SQLiteDatabase database =dbOpenHelper.getWritableDatabase();
Cursor cursor=database.rawQuery("select*from person where personid=?",new String[]{String.valueOf(id)});
if(cursor.moveToNext()){
return new Person(cursor.getInt(0),cursor.getString(1),cursor.getShort(2));//反应的是一列的数据
}
return null;
}

//分页方法
public ListgetScrollData(int startResult,int maxResult){
Listpersons=new ArrayList();
SQLiteDatabase database =dbOpenHelper.getWritableDatabase();
Cursor cursor=database.rawQuery("select*from person limi

t ?,?",new String[]{String.valueOf(startResult),String.valueOf(maxResult)});
while(cursor.moveToNext()){
persons.add(new Person(cursor.getInt(0),cursor.getString(1),cursor.getShort(2))) ;
}
return persons;
}

//统计方法
public long getCount(){
SQLiteDatabase database =dbOpenHelper.getWritableDatabase();
Cursor cursor=database.rawQuery("select count(*)from person ",null);
if(cursor.moveToNext()){
return cursor.getLong(0);
}
return 0;
}


}
------------------使用android提供的增删改查方法-------------
public class OtherPersonService {
private DataBaseOpenHelper dbOpenHelper;
public OtherPersonService(Context context) {

dbOpenHelper=new DataBaseOpenHelper(context);
}
public void save(Person person){

SQLiteDatabase database =dbOpenHelper.getWritableDatabase();

ContentValues values=new ContentValues();
values.put("name", person.getName());
values.put("age", person.getAge());
database.insert("person", "name", values);
//database.close();可选项 database对象内部有缓存
}
public void updata(Person person){

SQLiteDatabase database =dbOpenHelper.getWritableDatabase();
ContentValues values=new ContentValues();
values.put("name", person.getName());
values.put("age", person.getAge());
database.update("person", values, "personid=?", new String[]{String.valueOf(person.getId())});
}
public Person find(Integer id){

SQLiteDatabase database =dbOpenHelper.getWritableDatabase();
Cursor cursor=database.query("person", new String[]{"personid","name","age"},"personid=?", new String[]{String.valueOf(id)}, null, null, null);
if(cursor.moveToNext()){
return new Person(cursor.getInt(0),cursor.getString(1),cursor.getShort(2));
}
return null;
}
public void delete(Integer...ids){//可变参数,根据多个参数进行删除

StringBuilder sb=new StringBuilder();
String[]strIds=new String[ids.length];
for(int i=0; isb.append('?').append(',');
strIds[i]=String.valueOf(ids[i]);
}
sb.deleteCharAt(sb.length()-1);
SQLiteDatabase database =dbOpenHelper.getWritableDatabase();
database.delete("person","personid in("+sb+")",strIds);}
public ListgetScrollData(int startResult,int maxResult){

Listpersons=new ArrayList();
SQLiteDatabase database =dbOpenHelper.getWritableDatabase();
Cursor cursor=database.query("person", new String[]{"personid","name","age"},
null,null, null,null, "personid desc",startResult+","+maxResult);
while(cursor.moveToNext()){
persons.add(new Person(cursor.getInt(0),cursor.getString(1),cursor.getShort(2))) ;
}
return persons;
}


public long getCount(){

SQLiteDatabase database =dbOpenHelper.getWritableDatabase();
Cursor cursor=database.query("person",new String[]{"count(*)"},null,null,null,null,null);
if(cursor.moveToNext()){
return cursor.getLong(0);

}
return 0;
}


}

---------------------------单元测试类------------------------
public class PersonServiceTest extends AndroidTestCase {
private static final String TAG="PersonServiceTest";

public void testSave()throws Exception{
PersonService personService=new PersonService(this.getContext());
personService.save(new Person("老李",(short)23));


}
public void testfind()throws Exception{
PersonService personService=new PersonService(this.getContext());
Person person=personService.find(1);
Log.i(TAG, person.toString());
}
public void testUpdata()throws Exception{
PersonService personService=new PersonService(this.getContext());
Person person=personService.find(1);
person.setName("liming");
personService.updata(person);
Log.i(TAG, person.toString());
}
public void testDelete()throws Exception{
PersonService personService=new PersonService(this.getContext());
personService.delete(1);
}
}

相关主题