SQlite有个问题,就是主键不能够自动排序。比如说主键id为1 2 3 4,共4条记录。现在删除2 3,还剩下1 4记录,当再次插入时,id会变成5,而不是2.假设在初始4条记录的基础上,把这4条记录全都删掉,再次插入时,得到的id是5. 这种机制实在是太不好了!解决方法是在主键id外新增加一个realid,对realid进行处理。具体请看代码:
这里一并附上
Android开发:setContentView切换界面,自定义带CheckBox的ListView显示SQlite条目-----实现
的代码。整个代码的功能是数据库里有个表,结构是id和姓名。可以增加、删除、更新,在主界面里有一个选定按钮,点击后会显示带checkbox的listview,显示数据库里的条目,按上面的确定按钮返回主界面。
第一部分:-------main.xml
- <span style="font-size:18px;"><?xml version="1.0" encoding="utf-8"?>
- <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
- android:layout_width="fill_parent"
- android:layout_height="fill_parent"
- android:background="@android:color/white"
- android:orientation="vertical" >
- <EditText
- android:id="@+id/nameEdit"
- android:layout_width="fill_parent"
- android:layout_height="wrap_content"
- android:focusable="true"
- android:hint="请输入志愿者姓名"
- android:textColor="@android:color/holo_red_dark"
- android:textSize="25dip" />
- <Button
- android:id="@+id/choseBtn"
- android:layout_width="fill_parent"
- android:layout_height="wrap_content"
- android:text="选定"
- />
- <ListView
- android:id="@+id/palmList"
- android:layout_width="fill_parent"
- android:layout_height="wrap_content" >
- </ListView>
- </LinearLayout>
- </span>
my_checkbox.xml-----------用来控制listview的每一行怎么显示,这是跳转到选定界面呈现的
- <span style="font-size:18px;"><?xml version="1.0" encoding="utf-8"?>
- <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
- android:layout_width="fill_parent"
- android:layout_height="wrap_content"
- android:orientation="horizontal" >
- <TextView
- android:id="@+id/item_text"
- android:textSize="25dip"
- android:layout_weight="1"
- android:layout_width="wrap_content"
- android:layout_height="wrap_content"/>
- <CheckBox
- android:id="@+id/item_check"
- android:textSize="25dip"
- android:layout_width="wrap_content"
- android:layout_height="wrap_content"
- android:clickable="false"
- android:focusable="false"
- android:checkMark="?android:attr/listChoiceIndicatorMultiple"/>
- </LinearLayout>
- </span>
list_check.xml-----------------------这个布局是主界面要跳转的界面的布局,上面是个按键,下面是个带checkbox的 listview
- <span style="font-size:18px;"><?xml version="1.0" encoding="utf-8"?>
- <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
- android:layout_width="fill_parent"
- android:layout_height="wrap_content"
- android:orientation="vertical" >
- <Button
- android:id="@+id/confirmBtn"
- android:layout_width="fill_parent"
- android:layout_height="wrap_content"
- android:text="确定" />
- <ListView
- android:id="@+id/checkList"
- android:layout_width="fill_parent"
- android:layout_height="wrap_content" >
- </ListView>
- </LinearLayout>
- </span>
第二部分:
PalmDB.java--------------自定义的一个数据库类,继承SQLiteOpenHelper,功能有增加、删除、更新。
- <span style="font-size:18px;">package yan.guoqi.testsqlite;
- import android.content.ContentValues;
- import android.content.Context;
- import android.database.Cursor;
- import android.database.sqlite.SQLiteDatabase;
- import android.database.sqlite.SQLiteDatabase.CursorFactory;
- import android.database.sqlite.SQLiteOpenHelper;
- import android.util.Log;
- public class PalmDB extends SQLiteOpenHelper{
- private static final String DATABASE_NAME = "Palm.db";
- private static final int DATABASE_VERSION = 1;
- private static final String TABLE_NAME = "Palm_table";
- //表里面的三个内容
- private static final String ID = "_id";
- private static final String NAME = "_name";
- private static final String RealID = "_realid";
- private static final String Tag = "PalmDB";
- public PalmDB(Context context) //, String name, CursorFactory factory, int version
- {
- super(context, DATABASE_NAME, null, DATABASE_VERSION);
- // TODO Auto-generated constructor stub
- }
- @Override
- public void onCreate(SQLiteDatabase db) {
- // TODO Auto-generated method stub
- String sql = "CREATE TABLE " + TABLE_NAME + " (" + ID + " INTEGER primary key autoincrement, "
- + NAME +" text, " + RealID + " INTEGER);"; //
- db.execSQL(sql);
- }
- @Override
- public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
- // TODO Auto-generated method stub
- String sql = "DROP TABLE IF EXISTS " + TABLE_NAME;
- db.execSQL(sql);
- onCreate(db);
- }
- public Cursor select(){
- SQLiteDatabase db = this.getReadableDatabase();
- Cursor cursor = db.query(TABLE_NAME, null, null, null, null, null, RealID +" ASC");
- return cursor;
- }
- /*增加操作*/
- public long insert(String name){
- SQLiteDatabase db = this.getWritableDatabase();
- ContentValues cv = new ContentValues();
- int realid = getRealId();
- cv.put(NAME, name);
- cv.put(RealID, realid);
- long row = db.insert(TABLE_NAME, null, cv);
- return realid;
- //return row; //row是行的意思
- }
- /*得到一个真实的id*/
- public int getRealId(){
- Cursor cursor = select();
- int realid = 1;
- //如果cursor为空,返回id = 1
- if(!cursor.moveToFirst()){
- return realid;
- }
- else{
- while(true){
- if(realid != cursor.getInt(2))
- {
- return realid;
- }
- else
- {
- realid++;
- if(!cursor.moveToNext())
- return realid;
- }
- }
- }
- }
- /*删除操作*/
- public void delete(int id){
- SQLiteDatabase db = this.getWritableDatabase();
- String where = ID + "=?";
- String[] whereValue = { Integer.toString(id) };
- db.delete(TABLE_NAME, where, whereValue);
- }
- /*修改操作*/
- //id是你要修改的id号,name是新的名字
- public void update(int id, String name){
- SQLiteDatabase db = this.getWritableDatabase();
- String where = ID + "=?";
- String[] whereValue = { Integer.toString(id) };
- ContentValues cv = new ContentValues();
- cv.put(NAME, name);
- db.update(TABLE_NAME, cv, where, whereValue);
- }
- }
- </span>
需要注意的几点:
1,这个表里有3个元素,分别是ID NAME RealID ,这个ID是主键,类型是INTEGER primary key autoincrement, 第三个RealID是为了解决主键不能自动排序而新增加的。
2,public void onCreate(SQLiteDatabase db)这个函数是数据库创建的时候,注意这句话的写法:
String sql = "CREATE TABLE " + TABLE_NAME + " (" + ID + " INTEGER primary key autoincrement, "
+ NAME +" text, " + RealID + " INTEGER);"; //
里面的标点可是一点不敢错,每个元素后面带“,”。
3,public Cursor select()这个函数很关键,是获得查询的游标。Cursor cursor = db.query(TABLE_NAME, null, null, null, null, null, RealID +" ASC"); 后面的最后一个参数是让查询的时候按照RealID升序排列。这个很关键,这和下面的自动产生排序的ID亲密相连。
4,public int getRealId()这个函数得到自动排序的ID。里面就是算法:
- <span style="font-size:18px;">/*得到一个排好序,即将插入的id*/
- public int getRealId(){
- Cursor cursor = select();
- int realid = 1;
- //如果cursor为空,返回id = 1
- if(!cursor.moveToFirst()){
- return realid;
- }
- else{
- while(true){
- if(realid != cursor.getInt(2))
- {
- return realid;
- }
- else
- {
- realid++;
- if(!cursor.moveToNext())
- return realid;
- }
- }
- }
- }
- </span>
这个函数是让id自动排序的关键。首先判断cursor是否为空,空的话就是没有记录,则返回的id = 1; 不空的话则进入循环。如果realid不等于当前cursor的第一行元素,则直接返回realid。否则话realid加1,游标往下移一行。这里跟插入法排序有点像,前提是这个游标必须是有序的。这就和上面的3挂上钩了。可以用Java,借助数组模拟验证这个算法是否对:
- <span style="font-size:18px;">package yan.guoqi;
- public class MainClass {
- public static void main(String[] args){
- int a = MainClass.getRealId();
- System.out.println(" "+ a);
- }
- /*得到真正的real—id*/
- public static int getRealId(){
- int[] cursor = {1, 3, 6};
- int id=1;
- int i=0;
- while(true){
- if(cursor.length == 0)
- return id;
- else{
- if(id!=cursor[i])
- {
- return id;
- }
- else
- {
- id++;
- i++;
- if(i == cursor.length)
- return id;
- }
- }
- }
- }
- }
- </span>
- <span style="font-size:18px;"></span>
- <span style="font-size:18px;BACKGROUND-COLOR: #ffffff"></span>
5,至于下面的delete和update函数,是删除和更新,依据的索引是 ID,而不是RealID。 当然如果你喜欢,改成依据RealID也无妨。 详细可参考:
Android 高手进阶教程(十三)之----Android 数据库SQLiteDatabase的使用!!
6,另外就是insert函数,返回的是realid,也就是你插入一条记录后,这个记录的真实行号。这个值和long row = db.insert(TABLE_NAME, null, cv);里的row是不同的,这个row是ID号。是不规则的那个行号,依据主键而来
第三部分:主界面的源码
- <span style="font-size:18px;">package yan.guoqi.testsqlite;
- import java.util.HashMap;
- import java.util.Map;
- import yan.guoqi.testsqlite.TestSQliteActivity.CheckListAdapter.ViewHolder;
- import android.app.Activity;
- import android.content.Context;
- import android.database.Cursor;
- import android.graphics.Color;
- import android.os.Bundle;
- import android.util.Log;
- import android.view.LayoutInflater;
- import android.view.Menu;
- import android.view.MenuItem;
- import android.view.View;
- import android.view.View.OnClickListener;
- import android.view.ViewGroup;
- import android.view.WindowManager;
- import android.widget.AdapterView;
- import android.widget.AdapterView.OnItemClickListener;
- import android.widget.BaseAdapter;
- import android.widget.Button;
- import android.widget.CheckBox;
- import android.widget.EditText;
- import android.widget.ListView;
- import android.widget.TextView;
- import android.widget.Toast;
- public class TestSQliteActivity extends Activity implements AdapterView.OnItemClickListener {
- private PalmDB mPalmDB;
- private Cursor cursor;
- private EditText mNameEdit;
- private ListView mPalmList;
- private int id = 0;
- protected static final int MENU_ADD = Menu.FIRST;
- protected static final int MENU_DELETE = Menu.FIRST + 1;
- protected static final int MENU_UPDATE= Menu.FIRST + 2;
- private static final String Tag="SQlite:";
- //为了实现新的布局
- Button mChoseBtn = null;
- Button mConfirmBtn = null;
- boolean firstFlag = true;
- ListView list2 = null;
- View checkListView = null;
- View mainView = null;
- /** Called when the activity is first created. */
- @Override
- public void onCreate(Bundle savedInstanceState) {
- super.onCreate(savedInstanceState);
- LayoutInflater inflater = this.getLayoutInflater();
- checkListView = inflater.inflate(R.layout.list_check, null);
- mainView = inflater.inflate(R.layout.main, null);
- setContentView(mainView);
- //切换布局监听
- mChoseBtn = (Button)mainView.findViewById(R.id.choseBtn);
- mChoseBtn.setOnClickListener(new ButtonListener());
- setUpViews();
- }
- class ButtonListener implements OnClickListener{
- public void onClick(View v) {
- // TODO Auto-generated method stub
- switch (v.getId()){
- case R.id.choseBtn:
- Jump2CheckList();
- break;
- case R.id.confirmBtn:
- String s = getCheckInfo();
- showToast("您选中的姓名有:"+ s);
- Jump2Main();
- break;
- default:
- break;
- }
- }
- }
- public String getCheckInfo()
- {
- String info = "";
- for(int i=0; i<list2.getCount(); i++){
- if(isSelected.get(i)){
- //ViewHolder holder = (ViewHolder)list2.getChildAt(i).getTag();
- cursor.moveToPosition(i);
- info+=cursor.getInt(0)+".";
- }
- }
- return info;
- }
- /*切换到选中布局*/
- public void Jump2CheckList(){
- setContentView(checkListView);
- if(firstFlag){
- mConfirmBtn = (Button)checkListView.findViewById(R.id.confirmBtn);
- mConfirmBtn.setOnClickListener(new ButtonListener());
- firstFlag = false;
- }
- initCheckList();
- }
- public void initCheckList(){
- list2 = (ListView)(checkListView).findViewById(R.id.checkList);
- list2.setItemsCanFocus(false);
- list2.setAdapter(new CheckListAdapter(this, cursor));
- list2.setChoiceMode(ListView.CHOICE_MODE_MULTIPLE);
- list2.setOnItemClickListener(new OnItemClickListener() {
- public void onItemClick(AdapterView<?> arg0, View view, int positon,
- long id) {
- // TODO Auto-generated method stub
- ViewHolder vHolder = (ViewHolder) view.getTag();
- vHolder.check.toggle();
- isSelected.put(positon, vHolder.check.isChecked());
- }
- });
- }
- /*切换到主布局*/
- public void Jump2Main(){
- setContentView(mainView);
- setUpViews();
- }
- /*初始化数据库,更新View*/
- public void setUpViews(){
- //禁止输入法自己探出来
- this.getWindow().setSoftInputMode(WindowManager.LayoutParams.SOFT_INPUT_STATE_ALWAYS_HIDDEN);
- mPalmDB = new PalmDB(this);
- Log.i(Tag, "aaaaaaaaa");
- cursor = mPalmDB.select();
- Log.i(Tag, "11111111");
- mNameEdit = (EditText)findViewById(R.id.nameEdit);
- mPalmList = (ListView)findViewById(R.id.palmList);
- mPalmList.setAdapter(new PalmListAdapter(this, cursor));
- mPalmList.setOnItemClickListener(this);
- }
- @Override
- public boolean onCreateOptionsMenu(Menu menu) {
- // TODO Auto-generated method stub
- super.onCreateOptionsMenu(menu);
- //menu.
- menu.add(Menu.NONE, MENU_ADD, 0, "增加");
- menu.add(Menu.NONE, MENU_DELETE, 1, "删除");
- menu.add(Menu.NONE, MENU_UPDATE, 2, "修改");
- return true;
- }
- @Override
- public boolean onOptionsItemSelected(MenuItem item) {
- // TODO Auto-generated method stub
- super.onOptionsItemSelected(item);
- switch(item.getItemId()){
- case MENU_ADD:
- add();
- break;
- case MENU_DELETE:
- delete();
- break;
- case MENU_UPDATE:
- update();
- break;
- default:
- break;
- }
- return true;
- }
- public void add(){
- String name = mNameEdit.getText().toString();
- if(name.equals("") ){
- showToast("志愿者姓名不能为空");
- return;
- }
- long row = mPalmDB.insert(name);
- cursor.requery();
- mPalmList.invalidateViews();
- mNameEdit.setText("");
- showToast("添加成功!编号:" + row);// + Integer.toString(cursor.getInt(0))
- }
- public void delete(){
- if(id == 0){
- showToast("还未有志愿者,无法删除!");
- return ;
- }
- if(mNameEdit.getText().toString().equals("")){
- showToast("请选中后删除!");
- return;
- }
- mPalmDB.delete(id);
- cursor.requery();
- mPalmList.invalidateViews();
- mNameEdit.setText("");
- showToast("删除成功!");
- }
- public void update(){
- String name = mNameEdit.getText().toString();
- if(name.equals("")) {
- showToast("选中为空!无法修改。");
- return;
- }
- mPalmDB.update(id, name);
- cursor.requery();
- mPalmList.invalidateViews();
- mNameEdit.setText("");
- showToast("修改成功!");
- }
- public void onItemClick(AdapterView<?> arg0, View view, int position, long _id) {
- // TODO Auto-generated method stub
- cursor.moveToPosition(position);
- id = cursor.getInt(0);
- mNameEdit.setText(cursor.getString(1));
- }
- public void showToast(String str){
- Toast.makeText(this,
- str,
- Toast.LENGTH_SHORT).show();
- }
- /*给CheckList设置适配器*/
- public static Map<Integer, Boolean> isSelected;
- public class CheckListAdapter extends BaseAdapter{
- private Context mContext;
- private Cursor mCursor;
- //构造函数
- public CheckListAdapter(Context context, Cursor cursor){
- mContext = context;
- mCursor = cursor;
- isSelected = new HashMap<Integer, Boolean>();
- for(int i=0; i<mCursor.getCount(); i++){
- isSelected.put(i, false);
- }
- }
- public int getCount() {
- // TODO Auto-generated method stub
- return cursor.getCount();
- }
- public Object getItem(int arg0) {
- // TODO Auto-generated method stub
- return null;
- }
- public long getItemId(int arg0) {
- // TODO Auto-generated method stub
- return 0;
- }
- public View getView(int position, View convertView, ViewGroup arg2) {
- // TODO Auto-generated method stub
- ViewHolder holder = null;
- if(convertView == null){
- holder = new ViewHolder();
- LayoutInflater inflater = (LayoutInflater) mContext.getSystemService(Context.LAYOUT_INFLATER_SERVICE);
- convertView = inflater.inflate(R.layout.my_checkbox, null);
- holder.text = (TextView) convertView.findViewById(R.id.item_text);
- holder.check = (CheckBox)convertView.findViewById(R.id.item_check);
- convertView.setTag(holder);
- }
- else
- {
- holder = (ViewHolder)convertView.getTag();
- }
- mCursor.moveToPosition(position);
- holder.text.setText(Integer.toString(mCursor.getInt(2)));
- holder.text.append(mCursor.getString(1));
- holder.check.setChecked(isSelected.get(position));
- return convertView;
- }
- public final class ViewHolder{
- public TextView text;
- public CheckBox check;
- }
- }
- /*自定义适配器*/
- public class PalmListAdapter extends BaseAdapter{
- private Context mContext;
- private Cursor mCursor;
- //构造函数
- public PalmListAdapter(Context context, Cursor cursor){
- mContext = context;
- mCursor = cursor;
- }
- public int getCount() {
- // TODO Auto-generated method stub
- return mCursor.getCount();
- }
- public Object getItem(int arg0) {
- // TODO Auto-generated method stub
- return null;
- }
- public long getItemId(int arg0) {
- // TODO Auto-generated method stub
- return 0;
- }
- public View getView(int position, View convertView, ViewGroup parent) {
- // TODO Auto-generated method stub
- TextView mTextView = new TextView(mContext);
- mCursor.moveToPosition(position);
- mTextView.setTextSize(25);
- mTextView.setTextColor(Color.BLUE);
- mTextView.setText(Integer.toString(mCursor.getInt(2)));
- mTextView.append(mCursor.getString(1));
- return mTextView;
- }
- }
- }
- </span>
注意:
1,程序基本上和上篇
Android开发:setContentView切换界面,自定义带CheckBox的ListView显示SQlite条目-----实现
差不多。可以看到适配器其实很好写,CheckListAdapter是带checkbox的listview的适配器, PalmListAdapter是主界面的那个适配器。 适配器的重点在public View getView(int position, View convertView, ViewGroup parent)这个函数,可以对比两个适配器在获得view上写法上的不同。
2,由于在源程序2里,删除依据的id是ID,也就是主键,所以在这里提供的也必须是主ID。 如果一个用ID 一个提供的是RealID,会出现删除不了,删除错乱的情况!
最后看看效果:
初始界面:
增加4条记录后:
删除2 和3: