Sqlite中INTEGER PRIMARY KEY的修正

最新推荐文章于 2023-12-13 00:10:55 发布

最新推荐文章于 2023-12-13 00:10:55 发布 · 197 阅读

文章标签：

在 android 的Sqlite中对于 INTEGERPRIMARYKEY ，
如果插入数据A，B，C，它们的_id为1，2，3，那么如果把他们都删除了，
再插入一条数据，那么它的id为为1而不是4。
因此我就写了这个工具来生成id，它能让生成相同的id相隔尽量的远。 然后在插据入数时手动设置id，及把这个作为插入参数id传进去。 删除时，也要做相应的处理。
实例1：
文件1: SerialManager.java packagecom.teleca; publicclassSerialManager{ Noderoot=null; longmin=Long.MIN_VALUE; longmax=Long.MAX_VALUE; longcursor=1; publicSerialManager() { } publicSerialManager(longmin,longmax) { if(min>max) { longtemp=min; min=max; max=temp; } this.min=min; this.max=max; } publicvoidsetSerial(longid,booleanflag) { longtemp=0; Nodenode=findNode(id); if(flag) { if(node==null) { node=findNode(id-1); if(node!=null) { node.data.end=id; Nodenode2=findNode(id+1); if(node2!=null) { node.data.end=node2.data.end; this.removeNode(node2); } } else { Nodenode2=findNode(id+1); if(node2!=null) { node2.data.start=id; } else addNewNode(id,id); } } return; } else { if(node==null) return; Blockdata=node.data; if(data.start==data.end) { removeNode(node); } elseif(id==data.start) data.start=id+1; elseif(id==data.end) data.end=id-1; else { temp=data.end; data.end=id-1; addNewNode(id+1,temp); } } } publicvoidsetSerial(longid1,longid2) { longtemp=0; if(id1>id2) { temp=id1; id1=id2; id2=temp; } Nodenode1=findNode(id1); Nodenode2=null; if(node1==null) { node1=findNode(id1-1); if(node1!=null) { node1.data.end=id2; node2=findNode(id2+1); if(node2!=null) { node1.data.end=node2.data.end; this.removeNode(node2); } return; } else { node2=findNode(id2); if(node2==null) { node2=findNode(id2+1); if(node2!=null) node2.data.start=id1; else addNewNode(id1,id2); return; } else { node2.data.start=id1; return; } } } else { Blockdata=node1.data; if(id2<=data.end) { return; } else { node2=findNode(id2); if(node2==null) { data.end=id2; return; } else { data.end=node2.data.end; removeNode(node2); } } } } publiclonggetSerial() { Nodenode=findNode(cursor); longstart=cursor; while(node!=null) { cursor=node.data.end+1; if(cursor>max) cursor=min; elseif(cursor==0) cursor++; if(cursor==start) { return0; } node=findNode(cursor); } longres=cursor; cursor++; if(cursor>max) cursor=min; elseif(cursor==0) cursor++; returnres; } publicbooleanisKeyUsed(longid) { returnfindNode(id)!=null; } privateNodefindNode(longid) { Nodenode=null; NodetempNode=root; Blockblock=null; while(tempNode!=null) { block=tempNode.data; if(block.start<=id&&id<=block.end) { node=tempNode; break; } tempNode=tempNode.next; } returnnode; } privatevoidaddNewNode(longid1,longid2) { Nodenode=newNode(); node.data=newBlock(id1,id2); addNode(node); } privatevoidaddNode(Nodenode) { if(root==null) { root=node; node.prev=null; node.next=null; return; } NodetempNode=root; while(tempNode!=null) { if(tempNode.data.start>node.data.end) { if(tempNode==root) { node.prev=null; node.next=root; tempNode.prev=node; root=node; } else { node.prev=tempNode.prev; node.next=tempNode; tempNode.prev.next=node; tempNode.prev=node; } break; } elseif(tempNode.next==null) { tempNode.next=node; node.prev=tempNode; node.next=null; break; } tempNode=tempNode.next; } } privatevoidremoveNode(Nodenode) { Nodeprev=node.prev; if(prev==null) { root=node.next; } else { prev.next=node.next; } if(node.next!=null) node.next.prev=prev; node.prev=null; node.next=null; } publicvoidclear() { NodetempNode=root; Nodenode=null; while(tempNode!=null) { node=tempNode; tempNode=tempNode.next; node.prev=null; node.next=null; } root=null; cursor=1; } publicvoidprintln() { NodetempNode=root; while(tempNode!=null) { System.out.println("start:"+tempNode.data.start+"end:"+tempNode.data.end); tempNode=tempNode.next; } } } classNode { Nodeprev=null; Blockdata; Nodenext=null; } classBlock { longstart=0; longend=0; Block(longid1,longid2) { start=id1; end=id2; } publiclonggetStart(){ returnstart; } publicvoidsetStart(longstart){ this.start=start; } publiclonggetEnd(){ returnend; } publicvoidsetEnd(longend){ this.end=end; } } 文件2: DBHelper.java packagecom.teleca; importjava.util.ArrayList; importjava.util.List; importandroid.content.ContentValues; importandroid.content.Context; importandroid.database.Cursor; importandroid.database.SQLException; importandroid.database.sqlite.SQLiteDatabase; importandroid.database.sqlite.SQLiteOpenHelper; importandroid.util.Log;; publicclassDBHelper{ publicstaticStringDB_NAME="peopledb"; publicstaticStringDB_TABLE_NAME="people"; SQLiteDatabasedb; finalDBOpenHelperdbOpenHelper; finalstaticStringCOLS[]=newString[] {"_id","name","phone","age"}; finalStringtag="hubin"; publicDBHelper(Contextcontext) { this.dbOpenHelper=newDBOpenHelper(context,DB_NAME,1); establishDb(); } SerialManagerserialManager=newSerialManager(); privatevoidestablishDb() { if(db==null) db=dbOpenHelper.getWritableDatabase(); initSerial(); } privatevoidinitSerial() { serialManager.clear(); Cursorc=db.query(DBHelper.DB_TABLE_NAME,newString[]{"_id"},null,null,null,null,"_idDESC",null); c.moveToFirst(); intcount=c.getCount(); c.moveToFirst(); longid=0; for(inti=0;i<count;i++) { id=c.getLong(0); serialManager.setSerial(id,true); Log.i("hubin","id:"+id+"settoserial"); c.moveToNext(); } c.close(); } publicvoidcleanup() { if(db!=null) { db.close(); db=null; } } publicvoidinsert(Peoplepeople) { ContentValuesvalues=newContentValues(); values.put("name",people.name); values.put("phone",people.phone); values.put("age",people.age); longid=serialManager.getSerial(); values.put("_id",id); people.id=db.insert(DBHelper.DB_TABLE_NAME,null,values); serialManager.setSerial(id,true); Log.i(tag,"insert:"+people.id); } publicvoidupdate(Peoplepeople) { ContentValuesvalues=newContentValues(); values.put("name",people.name); values.put("phone",people.phone); values.put("age",people.age); db.update(DBHelper.DB_TABLE_NAME,values,"_id="+people.id,null); } publicvoiddelete(longid) { db.delete(DB_TABLE_NAME,"_id="+id,null); serialManager.setSerial(id,false); } publicvoiddeleteAll() { db.delete(DB_TABLE_NAME,null,null); serialManager.clear(); } publicPeopleget(longid) { Peoplepeople=newPeople(); Cursorc=null; try{ c=db.query(DB_TABLE_NAME,COLS,"_id='"+id+"'",null,null,null,null); Log.i(tag,"count:"+c.getCount()); if(c.getCount()>0) { c.moveToFirst(); people=newPeople(); people.id=c.getLong(0); people.name=c.getString(1); people.phone=c.getString(2); people.age=c.getInt(3); } }catch(SQLExceptione) { Log.i(tag,"",e); } finally { if(c!=null&&!c.isClosed()) { c.close(); } } returnpeople; } publicList<People>getAll() { ArrayList<People>ret=newArrayList<People>(); Cursorc=null; try { c=db.query(DB_TABLE_NAME,COLS,null,null,null,null,null); intcount=c.getCount(); c.moveToFirst(); Peoplepeople; for(inti=0;i<count;i++) { people=newPeople(); people.id=c.getLong(0); people.name=c.getString(1); people.phone=c.getString(2); people.age=c.getInt(3); ret.add(people); c.moveToNext(); } }catch(SQLExceptione) { Log.i(tag,"",e); } finally { if(c!=null&&!c.isClosed()) { c.close(); } } returnret; } } classDBOpenHelperextendsSQLiteOpenHelper { privatestaticfinalStringDB_CREATE="CREATETABLE" +DBHelper.DB_TABLE_NAME +"(_idINTEGERPRIMARYKEY,nameTEXTUNIQUENOTNULL," +"phoneTEXT,ageINTEGER);"; finalstaticStringtag="hubin"; publicDBOpenHelper(Contextcontext,StringdbName,intversion) { super(context,dbName,null,version); } publicvoidonCreate(SQLiteDatabasedb) { try{ db.execSQL(DB_CREATE); } catch(SQLExceptione) { Log.e(tag,"",e); } } publicvoidonOpen(SQLiteDatabasedb) { super.onOpen(db); } publicvoidonUpgrade(SQLiteDatabasedb,intoldVersion,intnewVersion) { db.execSQL("DROPTABLEIFEXISTS"+DBHelper.DB_TABLE_NAME); this.onCreate(db); } } 文件3： People.java packagecom.teleca; publicclassPeople{ publiclongid; publicStringname; publicStringphone; publicintage; } 文件4： Hello.java packagecom.teleca; importjava.util.List; importandroid.app.Activity; importandroid.os.Bundle; importandroid.os.Handler; importandroid.util.Log; importandroid.view.View; importandroid.view.View.OnClickListener; importandroid.widget.Button; publicclassHelloextendsActivity{ DBHelperdbHelper; finalstaticStringtag="hubin"; /**Calledwhentheactivityisfirstcreated.*/ @Override publicvoidonCreate(BundlesavedInstanceState){ super.onCreate(savedInstanceState); setContentView(R.layout.main); Buttonbutton=(Button)findViewById(R.id.Button01); OnClickListenerlistener=newOnClickListener(){ publicvoidonClick(Viewv){ cmd=CMD_ADD; doAction(); } }; button.setOnClickListener(listener); Buttonbutton2=(Button)findViewById(R.id.Button02); OnClickListenerlistener2=newOnClickListener(){ @Override publicvoidonClick(Viewv){ cmd=CMD_UPDATE; doAction(); } }; button2.setOnClickListener(listener2); Buttonbutton3=(Button)findViewById(R.id.Button03); OnClickListenerlistener3=newOnClickListener(){ @Override publicvoidonClick(Viewv){ cmd=CMD_QUERY; doAction(); } }; button3.setOnClickListener(listener3); Buttonbutton4=(Button)findViewById(R.id.Button04); OnClickListenerlistener4=newOnClickListener(){ @Override publicvoidonClick(Viewv){ cmd=CMD_QUERY_ALL; doAction(); } }; button4.setOnClickListener(listener4); Buttonbutton5=(Button)findViewById(R.id.Button05); OnClickListenerlistener5=newOnClickListener(){ @Override publicvoidonClick(Viewv){ cmd=CMD_DELETE; doAction(); } }; button5.setOnClickListener(listener5); Buttonbutton6=(Button)findViewById(R.id.Button06); OnClickListenerlistener6=newOnClickListener(){ @Override publicvoidonClick(Viewv){ cmd=CMD_DELETE_ALL; doAction(); } }; button6.setOnClickListener(listener6); mHandler=newHandler(); } publicvoidonStop() { super.onStop(); Log.v(tag,"onStop"); } intcnt=0; privateHandlermHandler; intcmd=0; finalintCMD_ADD=1; finalintCMD_UPDATE=2; finalintCMD_QUERY=3; finalintCMD_QUERY_ALL=4; finalintCMD_DELETE=5; finalintCMD_DELETE_ALL=6; Peoplepeople=newPeople(); classDatabaseThreadimplementsRunnable{ publicvoidrun(){ if(dbHelper==null) dbHelper=newDBHelper(Hello.this); if(cmd==CMD_ADD){ people.name="robin"+System.currentTimeMillis()%100; people.phone=""+System.currentTimeMillis(); people.age=1; dbHelper.insert(people); }elseif(cmd==CMD_UPDATE){ people.phone=""+System.currentTimeMillis(); dbHelper.update(people); }elseif(cmd==CMD_QUERY){ Peoplep=dbHelper.get(people.id); printPeople(p); }elseif(cmd==CMD_QUERY_ALL){ List<People>list=dbHelper.getAll(); inttotal=list.size(); for(inti=0;i<total;i++) { printPeople(list.get(i)); } } elseif(cmd==CMD_DELETE) { dbHelper.delete(people.id); } elseif(cmd==CMD_DELETE_ALL) { dbHelper.deleteAll(); } cnt++; } } voidprintPeople(Peoplep) { Log.i(tag,"id:"+p.id); Log.i(tag,"name:"+p.name); Log.i(tag,"phone:"+p.phone); Log.i(tag,"age:"+p.age); } DatabaseThreaddataDealer=newDatabaseThread(); voiddoAction(){ mHandler.post(dataDealer); } }