Sqlite demo 和总结

本章内容主要讲sqllite的一个实例，以及容易出现错误的地方。

首先在创建数据库时最容易出现错误的地方是sql语句没有间隔，比如 " CREATE TABLE IF NOT EXISTS"+TABLENAME+ "("+ID+"INTEGER PRIMARY KEY,"+KEY_NAME+" TEXT,"+KEY_SEX+" TEXT,"+KEY_SEX+" TEXT,"+KEY_ADDRESS+" TEXT"+" )";

在这些字符串拼接的过程中比如TEXT前要有空格 不然会连在一起然后报错。在TEXT后面也要有逗号，在后括号前也要有空格。这些都是容易出现错误的地方。开发者应当注意。

另外如果你查询数据库用的是CursorAdapter或者SimpleCursorAdapter作为数据展示适配器，那么你必须要有一列_id的属性，不然会报这样的一个错误----------java.lang.IllegalArgumentException: column '_id' does not exist。

看api后你会发现在CurseAdapter中有这样一句话Adapter  that exposes data form  a Cursor to a listview weight。The Cursor must include

a column named “_id” or this class will not work .这句话的意思就是在CursorAdapter中必须要有一列为_id，不然这个类无法工作。SimpleCursorAdapter这个类继承CursorAdapter，因此也需要有_id属性的一列。

我们有时候在listview上展示数据库中查询的数据，那么当我们删除某条数据时，listview并不会进行进行刷新页面进行更新数据条目。即使我们用了NotifyDataSetChanged也不管用。这是因为方式不对，我们应当先在每次进行删除后，使Adapter获取最新的Cursor。然后调用changeCursor方法，最后在调用NotifyDataSetChanged方法才管用。

下面附上部分代码

public class SQLDemoOpenHelper extends SQLiteOpenHelper {


private static final String DB_NAME = "sql_demo.db";
private static final String DB_TABLE = "demo";
private static final int DB_VERSION = 1;


public static final String KEY_ID = "_id";
public static final String KEY_NAME = "name";
public static final String KEY_SEX = "sex";
public static final String KEY_ADDRESS = "address";

private static final String CREATE_DB = " CREATE TABLE IF NOT EXISTS "  
+ DB_TABLE + " (" 
+ KEY_ID + " INTEGER PRIMARY KEY,"
+ KEY_NAME + " TEXT,"
+ KEY_SEX + " TEXT,"
+ KEY_ADDRESS + " TEXT"
+ ")";

private static final String TEST_INSERT = " INSERT INTO " + DB_TABLE + "( name,sex,address) VALUES('王五', '男', '上海')";
private SQLiteDatabase mSqLiteDatabase;


public SQLDemoOpenHelper(Context context) {
super(context, DB_NAME, null, DB_VERSION);
}


@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(CREATE_DB);
// 添加一些默认数据
db.execSQL(TEST_INSERT);

}


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


}


/**
* 打开数据库
*/
public void open (){
mSqLiteDatabase = getWritableDatabase();
}

/**
* 往数据库里面插入数据 
* @param data
* @return
*/
public long insertData(String data[]){
ContentValues insertValues = new ContentValues();
insertValues.put(KEY_NAME, data[0]);
insertValues.put(KEY_SEX, data[1]);
insertValues.put(KEY_ADDRESS,data[2]);
return mSqLiteDatabase.insert(DB_TABLE, KEY_ID, insertValues) ;
}

/**
* 根据 _id 来删除数据库中的某一条数据
* @param _id
* @return
*/
public boolean deleteData(long _id){
return mSqLiteDatabase.delete(DB_TABLE, KEY_ID + "=" + _id, null) > 0 ;
}

/**
* 通过_id 来更新某一条数据
* @param _id
* @param data
* @return
*/
public boolean updateData( long _id ,String data[]){
getWritableDatabase();
ContentValues updataValues = new ContentValues();
updataValues.put(KEY_NAME, data[0]);
updataValues.put(KEY_SEX, data[1]);
updataValues.put(KEY_ADDRESS,data[2]);

return  mSqLiteDatabase.update(DB_TABLE, updataValues, KEY_ID + " = " + _id, null)>0;
}

/**
* 查询数据 ， 当前的所有数据
* @return
*/
public Cursor fetchAllData(){
return mSqLiteDatabase.query(DB_TABLE, null, null, null, null, null, null);
}

/**
* 通过Id来查询 当前id下的数据
* @param _id
* @return
*/
public Cursor fetchDataById(long _id){
return mSqLiteDatabase.query(DB_TABLE, null,KEY_ID + "=" + _id, null, null, null, null);
}
}

public class SqlAdapter extends CursorAdapter {
int res_layout;
public static final String TAG = "nian";
public static final String KEY_ID = "_id";
public static final String KEY_NAME = "name";
public static final String KEY_SEX = "sex";
public static final String KEY_ADDRESS = "address";


public SqlAdapter(Context context, Cursor c, int layout) {
super(context, c );
res_layout = layout;
}


@Override
public void bindView(View view, Context context, Cursor c) {
Log.v(TAG," Adapter , bindView");
final Context _context = context;
final Cursor cursor = c;
final ViewHolder holder = (ViewHolder) view.getTag();
Log.v("aa","ee"+cursor.getColumnIndex(KEY_ID));
holder.id.setText(cursor.getString(cursor.getColumnIndex(KEY_ID)));
Log.v("aa","vv"+cursor.getString(0));
Log.v("aa","tt"+cursor.getString(1));
holder.name.setText(cursor.getString(cursor.getColumnIndex(KEY_NAME)));
holder.sex.setText(cursor.getString(cursor.getColumnIndex(KEY_SEX)));
holder.address.setText(cursor.getString(cursor.getColumnIndex(KEY_ADDRESS)));
Log.v("aa",holder.id.getText().toString());
holder.removeButton.setOnClickListener(new Button.OnClickListener() {


@Override
public void onClick(View v) {
long id = Long.parseLong(holder.id.getText().toString());
Log.v("aa","bb"+id);
SQLDemoOpenHelper mHelper = new SQLDemoOpenHelper(_context);
mHelper.open();
mHelper.deleteData(id);
Cursor newCursor = mHelper.fetchAllData();
changeCursor(newCursor);
notifyDataSetChanged();
mHelper.close();


}
});
}


/**
* 说一个我理解的哈， newView相当于onCreate
* 而bindView 相当onStart 
*/
@Override
public View newView(Context context, Cursor c, ViewGroup arg2) {
Log.i(TAG," Adapter , newView");
View view = View.inflate(context, res_layout, null);
ViewHolder holder = new ViewHolder();
holder.name = (TextView) view.findViewById(R.id.tv_item_name);
holder.sex = (TextView) view.findViewById(R.id.tv_item_sex);
holder.address = (TextView) view.findViewById(R.id.tv_item_address);
holder.removeButton = (Button) view.findViewById(R.id.bu_remove);
holder.id = new TextView(context);
holder.id.setVisibility(View.INVISIBLE);
view.setTag(holder);
return view;
}


/**
* 通过一个隐藏的 TextView 来记住当前item对应数据库的 _id
* 这个样子来实现 删除按钮时候的操作
* @author yuhaiyang
*
*/
final static class ViewHolder {
public TextView id;
public TextView name;
public TextView sex;
public TextView address;
public Button removeButton;


}
}