package com.jk.service; /** * 完成数据库的创建和版本更新 */ import android.content.Context; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteDatabase.CursorFactory; import android.database.sqlite.SQLiteOpenHelper; public class DbHelper extends SQLiteOpenHelper { public DbHelper(Context context, String name, CursorFactory factory,int version) { super(context, name, factory, version); } public DbHelper(Context context) { super(context, "diary.db",null,1); } @Override public void onCreate(SQLiteDatabase db) { String sql = "CREATE TABLE Diary " + "(diaryId integer primary key autoincrement, title varchar(20),body varchar(1000),time varchar(40))"; db.execSQL(sql); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { //String sql="ALTER TABLE diary ADD body varchar(1000) null"; //db.execSQL(sql); System.out.println("DataBase Updated!!"); } }
package com.jk.service; import com.jk.Diary; import java.util.ArrayList; import java.util.List; import android.content.Context; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; /** * 完成对数据库的增删改查操作 * @author jkxqj */ public class DiaryAdapter { private DbHelper dbOpenHelper; public DiaryAdapter(Context context) { dbOpenHelper = new DbHelper(context); } public void save(Diary diary) { /* getWritableDatabase和getReadableDatabase()方法在磁盘空间没满的情况下一样 其中getWritableDatabase() 方法以读写方式打开数据库,一旦数据库的磁盘空间满了, 数据库就只能读而不能写,倘若使用的是getWritableDatabase() 方法就会出错。 getReadableDatabase()方法则是先以读写方式打开数据库,如果数据库的磁盘空间满了, 就会打开失败,当打开失败后会继续尝试以只读方式打开数据库。如果该问题成功解决, 则只读数据库对象就会关闭,然后返回一个可读写的数据库对象。 */ SQLiteDatabase db = dbOpenHelper.getWritableDatabase(); /* SQLiteDatabase db2=dbOpenHelper.getWritableDatabase(); */// db==db2 String sql = "insert into Diary(title,body,time) values(?,?,?)"; // 防止潜在 的bug db.execSQL(sql, new Object[] { diary.getTitle(), diary.getBody(),diary.getTime()}); db.close(); } public void update(Diary diary) { SQLiteDatabase db = dbOpenHelper.getWritableDatabase(); String sql = "update Diary set title=?,body=? where diaryId=?"; db.execSQL(sql, new Object[] { diary.getTitle(), diary.getBody(),diary.getId() }); db.close(); } public void delete(Integer id) { SQLiteDatabase db = dbOpenHelper.getWritableDatabase(); String sql = "delete from Diary where diaryId=?"; db.execSQL(sql, new Object[] { id }); db.close(); } public Diary find(Integer id) { SQLiteDatabase db = dbOpenHelper.getReadableDatabase(); String sql = "select * from Diary where diaryId=?"; Cursor cursor = db.rawQuery(sql, new String[] { id.toString() }); if (cursor.moveToFirst()) { //将游标移到第一条记录,若成功说明存在记录 int diaryId = cursor.getInt(cursor.getColumnIndex("diaryId")); String title = cursor.getString(cursor.getColumnIndex("title")); String body = cursor.getString(cursor.getColumnIndex("body")); String time = cursor.getString(cursor.getColumnIndex("time")); return new Diary(diaryId, title, body,time); } return null; } public List<Diary> getScrollData(int offset, int maxResult) { List<Diary> Diarys = new ArrayList<Diary>(); SQLiteDatabase db = dbOpenHelper.getReadableDatabase(); // offset指偏移量,maxResult指最大结果集 String sql = "select * from Diary limit ?,?"; Cursor cursor = db.rawQuery(sql, new String[] { String.valueOf(offset), String.valueOf(maxResult) }); while (cursor.moveToNext()) {//游标移到下一个说明数据没读完 int diaryId = cursor.getInt(cursor.getColumnIndex("diaryId")); String title = cursor.getString(cursor.getColumnIndex("title")); String body = cursor.getString(cursor.getColumnIndex("body")); String time = cursor.getString(cursor.getColumnIndex("time")); Diarys.add(new Diary(diaryId, title, body,time)); } db.close(); cursor.close(); return Diarys; } public Cursor getCursorScrollData(int offset, int maxResult) { SQLiteDatabase db = dbOpenHelper.getReadableDatabase(); // offset指偏移量,maxResult指最大结果集 String sql = "select diaryId as _id,title, body,time from Diary limit ?,?";//SimpleCursorAdapter需要有_id这个字段 Cursor cursor = db.rawQuery(sql, new String[] { String.valueOf(offset),String.valueOf(maxResult) }); return cursor; } public long getCount() { SQLiteDatabase db = dbOpenHelper.getWritableDatabase(); String sql = "select count(*) from Diary"; Cursor cursor = db.rawQuery(sql,null); cursor.moveToFirst(); db.close(); long i = cursor.getLong(0); cursor.close(); return i; } }