Android資料存放區–sqlite

SQLite是輕量級的、嵌入式的、關係型資料庫，目前已經在iPhone、Android等手機系統中使用，而且被其他的公司廣泛使用。

Android SQLite，從Android SDK中可以看到，對資料庫的操作都封裝好了，

常用的一個類就是ContentValues，類似一個Hashmap存放索引值對。

我們一般使用sqlite，自己寫個類型繼承SQLiteOpenHelper，通過這個類的getWritableDatabase方法，可以獲得一個SQLiteDatabase的執行個體。

進而我們可以實現對資料的基本操作了（增，刪，改，查）。

public class DBHelper extends SQLiteOpenHelper {</p><p>public final static String DB_NAME="contact";<br />public final static int VERSION=1;<br />private static DBHelper instance=null;</p><p>private SQLiteDatabase db;<br />public static DBHelper getInstance(Context context){<br />if(instance==null)<br />{<br />instance=new DBHelper(context);<br />}<br />return instance;<br />}</p><p>private void openDatabase()<br />{<br />db=this.getWritableDatabase();<br />}</p><p>public DBHelper(Context context){<br />super(context,DB_NAME,null,VERSION);<br />// TODO Auto-generated constructor stub<br />}</p><p>@Override<br />public void onCreate(SQLiteDatabase db) {<br />// TODO Auto-generated method stub</p><p>StringBuffer tableUserCreate =new StringBuffer();</p><p>tableUserCreate.append("create table User (id integer primary key autoincrement,")<br /> .append("name text,")<br /> .append("phonenumber text)");</p><p>}</p><p>@Override<br />public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {<br />// TODO Auto-generated method stub<br />String sql="drop table if exists user";</p><p>db.execSQL(sql);<br />onCreate(db);<br />}<br />//增加一個user<br />public boolean saveUser(User user)<br />{ openDatabase();<br />ContentValues value=new ContentValues();<br />String phonenumber = user.getPhoneNumber();<br />System.out.println("pjoneId:"+phonenumber);<br />Cursor cursor = db.query("User", new String []{"id","phonenumber","name"} , "phonenumber=?",new String[]{phonenumber} , null, null, null);<br />cursor.moveToNext();<br />System.out.println("count:"+cursor.getCount());<br />if(cursor.getCount()==0)<br />{<br />value.put("name", user.getName());<br />value.put("phonenumber", user.getPhoneNumber());<br />db.insert("User", null, value);<br />System.out.println("save");<br />}<br />System.out.println("no save");<br />if(cursor!=null){<br />cursor = null;<br />db.close();<br />}<br />return true;<br />}</p><p> //擷取資料庫中所有的user<br />public List<Map<String, Object>> getUser(){<br />openDatabase();<br />List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();<br />Cursor cursor = db.query("User", new String []{"id","name","phonenumber"} , null, null, null, null, null);<br />if(cursor!=null){<br />if(cursor.isBeforeFirst()){<br />while(cursor.moveToNext())<br />{<br />Map<String,Object> map = new HashMap<String, Object>();<br />map.put("name",cursor.getString(cursor.getColumnIndex("name"));<br />map.put("id",cursor.getString(cursor.getColumnIndex("id")));<br />map.put("phonenumber",cursor.getString(cursor.getColumnIndex("phonenumber")));</p><p>list.add(map);<br />}</p><p>}<br />}<br />if(cursor!=null){<br />cursor = null;<br />db.close();<br />}<br />return list;<br />}</p><p> //修改user資訊<br />public boolean editUser(User user) {<br />// TODO Auto-generated method stub<br /> openDatabase();<br /> ContentValues cv = new ContentValues();<br /> cv.put("phonenumber ",user.getPhoneNumber());<br /> cv.put("name", user.getName());<br /> db.update("user", cv, "id=+'"+user.getId()+"'" , null);<br /> db.close();<br />return true;<br />}</p><p> //取user中的某一欄位<br />public String getUserName(String phonenumber) {<br />// TODO Auto-generated method stub<br /> openDatabase();<br /> String name = null;<br /> Cursor cursor = db.query("User", new String []{"name"}, "phonenumber='" + phonenumber + "'", null, null, null, null);<br /> if(cursor.isBeforeFirst()){<br />while(cursor.moveToNext())<br />{<br />name = cursor.getString(cursor.getColumnIndex("name"));<br />System.out.println("name="+name);<br />}<br /> }<br /> if(cursor!=null){<br /> cursor = null;<br />db.close();<br />}<br />return name;<br />}<br /> //按條件刪除user<br />public boolean deleteUserById(int id) {<br />// TODO Auto-generated method stub<br /> openDatabase();<br /> db.delete("User","id='"+id+"'", null);<br />System.out.println("true");<br />db.close();<br />return true;<br />}</p><p>@Override<br />public String getUserNumber(String str) {<br />// TODO Auto-generated method stub<br /> openDatabase();<br /> String number = null;<br /> Cursor cursor = db.query("User", new String []{"phonenumber"}, "name='" + str + "'", null, null, null, null);<br /> if(cursor.isBeforeFirst()){<br />while(cursor.moveToNext())<br />{<br />number = cursor.getString(cursor.getColumnIndex("name"));<br />System.out.println("number="+number);<br />}<br /> }<br /> if(cursor!=null){<br /> cursor = null;<br />db.close();<br />}<br />return number;<br />}</p><p>}<br />