Sqlite中INTEGER PRIMARY KEY的修正

发布时间：2020-12-12 20:39:55 所属栏目：百科来源：网络整理

导读：在 android 的Sqlite中对于 INTEGERPRIMARYKEY ，如果插入数据A，B，C，它们的_id为1，2，3，那么如果把他们都删除了，再插入一条数据，那么它的id为为1而不是4。因此我就写了这个工具来生成id，它能让生成相同的id相隔尽量的远。然后在插据入数时手动设

在 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;
public SerialManager()
{
}
public SerialManager(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);
}

}
}
publicvoid setSerial(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);
}
}
}
}
publiclong getSerial()
{

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;
}
publicboolean isKeyUsed(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();
}
privatevoid initSerial()
{
serialManager.clear();
Cursorc=db.query(DBHelper.DB_TABLE_NAME,newString[]{"_id"},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,values);
serialManager.setSerial(id,true);
Log.i(tag,"insert:"+people.id);
}
publicvoidupdate(Peoplepeople)
{
ContentValuesvalues=newContentValues();
values.put("name",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);
serialManager.clear(); } publicPeopleget(longid) { Peoplepeople=newPeople(); Cursorc=null; try{ c=db.query(DB_TABLE_NAME,COLS,"_id='"+id+"'",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,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,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); } }

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!