android中数据库操作简介-优快云博客

本文介绍了Android内置SQLite数据库系统的使用方法，包括SQL基本命令和数据库管理，以及如何通过DBHelper类进行增删改查操作。

Android内置了 SQLite 数据库系统。
关于一些SQL基本命令的用法( CREATE,INSERT,UPDATE,DELETE 和 SELECT ),
请参阅SQLite文档（ http://www.sqlite.org/lang.html） 与SharedPreferences不同，不能将数据库设为WORLD_READABLE,每个数据库都只能创建它的包访问， 这意味着只有由创建数据库的进程可访问它。如果需要在进程间传递数据， 则可以使用AIDL/Binder或创建一个ContentProvider,但是不能跨越进程/包边界直接来使用数据库。 数据库文件存放在/data/data/[PACKAGE_NAME]/database/下。这些文件是SQLite专有的。 可以通过ADBshell(sqlite3工具)来操作它。 详细命令可参考 <a target="_blank" rel="nofollow" href="http://www.sqlite.org/sqlite.html" style="color:rgb(207,121,28); line-height:25px; text-decoration:none">http://www.sqlite.org/sqlite.html</a>。 实例1： 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="robin"; publicDBHelper(Contextcontext) { this.dbOpenHelper=newDBOpenHelper(context,DB_NAME,1); establishDb(); } privatevoidestablishDb() { if(db==null) db=dbOpenHelper.getWritableDatabase(); } 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); people.id=db.insert(DBHelper.DB_TABLE_NAME,null,values); Log.i(tag,"insert:"+people.id); //selecttop1idfromtablenameorderbyid Cursorc=db.query(DBHelper.DB_TABLE_NAME,newString[]{"_id"},null,null,null,null,"_idDESC","1"); c.moveToFirst(); Log.i("hubin","idMax:"+c.getInt(0)); c.close(); } 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); } publicvoiddeleteAll() { db.delete(DB_TABLE_NAME,null,null); } 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="robin"; 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); } } Hello.java文件 packagecom.teleca; importjava.util.ArrayList; 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{ /**Calledwhentheactivityisfirstcreated.*/ DBHelperdbHelper; finalstaticStringtag="hubin"; @Override publicvoidonCreate(BundlesavedInstanceState){ super.onCreate(savedInstanceState); setContentView(R.layout.main); Buttonbutton=(Button)findViewById(R.id.Button01); OnClickListenerlistener=newOnClickListener(){ @Override 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(); } 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); } } people.java文件 packagecom.teleca; publicclassPeople{ publiclongid; publicStringname; publicStringphone; publicintage; }