Android学习 之 Sqlite小结

这里采用的是 SQLiteDatabase的一个帮助类:SQLiteDatabaseHelper, 用来管理数据库的创建和版本的更新

package com.ethan.dao;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DBOpenHelper extends SQLiteOpenHelper {
	
	private static final int VERSION = 1;
	private static final String DBNAME = "data.db";
	private static final String STUDENT = "t_student";
	
	public DBOpenHelper(Context context) {
		super(context,DBNAME,null,VERSION);
		// TODO Auto-generated constructor stub
	}
	
	//程序安装时创建表
	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		db.execSQL("create table t_student(sid integer primary key, name varchar(20),age integer)");
		
	}
	
	
	//程序升级时，数据库 表可能要牵扯到更改字段，下边的方案是先把原表改个名字，然后
	//再建这个表（与原表同名),再拷贝数据到这个表，最后删除临时表（此处木有操作）
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		Log.i("StudentDAOTest","UpGrade!");
		
		String tempTable = "temp_student";
		db.execSQL("alter table "+STUDENT+ " rename to "+tempTable);
		db.execSQL("create table "+STUDENT+ "(sid integer primary key, name varchar(20),age integer,sex varchar(4))");
		
		String sql = "insert into " +STUDENT+ "values(name,age,sex) select name,age,'男' from "+tempTable;
		db.execSQL(sql);
		
	}

}

Model:

package com.ethan.model;

public class Student {
	private int sid;
	private String name;
	private short age;
	
	public Student() {
		super();
	}

	public Student(int sid, String name, short age) {
		// TODO Auto-generated constructor stub
		this.sid = sid;
		this.name = name;
		this.age = age;
	}

	public int getSid() {
		return sid;
	}

	public void setSid(int sid) {
		this.sid = sid;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public short getAge() {
		return age;
	}

	public void setAge(short age) {
		this.age = age;
	}

	@Override
	public String toString() {
		return "Student [sid=" + sid + ", name=" + name + ", age=" + age
				+ ", toString()=" + super.toString() + "]";
	}
}

DAO:

package com.ethan.dao;

import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.ethan.model.Student;

public class StudentDAO {
	private DBOpenHelper helper;
	private SQLiteDatabase db;
	
	public StudentDAO(Context context) {
		helper = new DBOpenHelper(context);
	}
	
	public void add(Student student) {
		db = helper.getWritableDatabase();
		db.execSQL("insert into t_student(sid,name,age) values(?,?,?)",new Object[]{student.getSid(),
				student.getName(),student.getAge()});
	}
	
	public void update(Student student) {
		db = helper.getWritableDatabase();
		db.execSQL("update t_student set name=?,age=? where sid=?",new Object[]{
				student.getName(),student.getAge(),student.getSid()
		});
	}
	
	public Student find(int sid) {
		db = helper.getWritableDatabase();
		Cursor cursor = db.rawQuery("select sid,name,age from t_student where sid=?", new String[]{String.valueOf(sid)});
		
		if(cursor.moveToNext()) {
			return new Student(cursor.getInt(cursor.getColumnIndexOrThrow("sid")),
					cursor.getString(cursor.getColumnIndexOrThrow("name")),
					cursor.getShort(cursor.getColumnIndexOrThrow("age")));
		}
		return null;
	}
	
	public void delete(Integer... sids) {
		if(sids.length>0) {
			StringBuilder sb = new StringBuilder();
			for(int i=0;i<sids.length;i++) {
				sb.append('?').append(',');
			}
			
			sb.deleteCharAt(sb.length()-1);
			
			SQLiteDatabase database = helper.getWritableDatabase();
			database.execSQL("delete from t_student where sid in(" +sids+")", (Object[])sids);
		}
	}
	
	
	public List<Student> getScrollData(int start,int count) {
		List<Student> students = new ArrayList<Student>();
		db = helper.getWritableDatabase();
		
		Cursor cursor = db.rawQuery("select * from t_student limit ?,?", new String[]{String.valueOf(start),String.valueOf(count)});
		
		while(cursor.moveToNext()) {
			students.add(new Student(cursor.getInt(cursor.getColumnIndexOrThrow("sid")),
					cursor.getString(cursor.getColumnIndexOrThrow("name")),
					cursor.getShort(cursor.getColumnIndexOrThrow("age"))));
		}
		return students;
	}
	
	public long getCount() {
		db = helper.getWritableDatabase();
		Cursor cursor = db.rawQuery("select count(sid) from t_student", null);
		
		if(cursor.moveToNext()) {
			return cursor.getLong(0);
		}
		return 0;
	}
}