ããä¸ã使ç¨åµå
¥å¼å
³ç³»åSQLiteæ°æ®åºåå¨æ°æ®
ããå¨Androidå¹³å°ä¸ï¼éæäºä¸ä¸ªåµå
¥å¼å
³ç³»åæ°æ®åºââSQLiteï¼SQLite3æ¯æNULLãINTEGERãREALï¼æµ®ç¹æ°åï¼ã TEXT(å符串ææ¬)åBLOB(äºè¿å¶å¯¹è±¡)æ°æ®ç±»åï¼è½ç¶å®æ¯æçç±»ååªæäºç§ï¼ä½å®é
ä¸sqlite3ä¹æ¥åvarchar(n)ã char(n)ãdecimal(p,s) çæ°æ®ç±»åï¼åªä¸è¿å¨è¿ç®æä¿åæ¶ä¼è½¬æ对åºçäºç§æ°æ®ç±»åã SQLiteæ大çç¹ç¹æ¯ä½ å¯ä»¥æåç§ç±»åçæ°æ®ä¿åå°ä»»ä½å段ä¸ï¼èä¸ç¨å
³å¿å段声æçæ°æ®ç±»åæ¯ä»ä¹ãä¾å¦ï¼å¯ä»¥å¨Integerç±»åçå段ä¸åæ¾å符串ï¼æè
å¨å¸å°åå段ä¸åæ¾æµ®ç¹æ°ï¼æè
å¨å符åå段ä¸åæ¾æ¥æåå¼ã ä½æä¸ç§æ
åµä¾å¤ï¼å®ä¹ä¸ºINTEGER PRIMARY KEYçå段åªè½åå¨64ä½æ´æ°ï¼ å½åè¿ç§å段ä¿åé¤æ´æ°ä»¥å¤çæ°æ®æ¶ï¼å°ä¼äº§çé误ã å¦å¤ï¼å¨ç¼åCREATE TABLE è¯å¥æ¶ï¼ä½ å¯ä»¥çç¥è·å¨å段å称åé¢çæ°æ®ç±»åä¿¡æ¯ï¼å¦ä¸é¢è¯å¥ä½ å¯ä»¥çç¥nameå段çç±»åä¿¡æ¯ï¼
ããCREATE TABLE person (personid integer primary key autoincrement, name varchar(20))
ããSQLiteå¯ä»¥è§£æ大é¨åæ åSQLè¯å¥ï¼å¦ï¼
ããå¤å¶ä»£ç 代ç å¦ä¸:
ããæ¥è¯¢è¯å¥ï¼select * from 表å where æ¡ä»¶åå¥ group by åç»åå¥ having ... order by æåºåå¥
ããå¦ï¼ select * from person
ããselect * from person order by id desc
ããselect name from person group by name having count(*)>1
ããå页SQLä¸mysql类似ï¼ä¸é¢SQLè¯å¥è·å5æ¡è®°å½ï¼è·³è¿åé¢3æ¡è®°å½
ããselect * from Account limit 5 offset 3 æè
select * from Account limit 3,5
ããæå
¥è¯å¥ï¼insert into 表å(å段å表) values(å¼å表)ãå¦ï¼ insert into person(name, age) values(âä¼ æº',3)
ããæ´æ°è¯å¥ï¼update 表å set å段å=å¼ where æ¡ä»¶åå¥ãå¦ï¼update person set name=âä¼ æºâ where id=10
ããå é¤è¯å¥ï¼delete from 表å where æ¡ä»¶åå¥ãå¦ï¼delete from person where id=10
ããäºã使ç¨SQLiteOpenHelper对æ°æ®åºè¿è¡çæ¬ç®¡ç
ããæ们å¨ç¼åæ°æ®åºåºç¨è½¯ä»¶æ¶ï¼éè¦èèè¿æ ·çé®é¢ï¼å 为æ们å¼åç软件å¯è½ä¼å®è£
å¨å¾å¤ç¨æ·çææºä¸ï¼å¦æåºç¨ä½¿ç¨å°äºSQLiteæ°æ®åºï¼æ们å¿
é¡»å¨ç¨æ·å次使ç¨è½¯ä»¶æ¶å建åºåºç¨ä½¿ç¨å°çæ°æ®åºè¡¨ç»æåæ·»å ä¸äºåå§åè®°å½ï¼å¦å¤å¨è½¯ä»¶å级çæ¶åï¼ä¹éè¦å¯¹æ°æ®è¡¨ç»æè¿è¡æ´æ°ãé£ä¹ï¼æ们å¦ä½æè½å®ç°å¨ç¨æ·å次使ç¨æå级软件æ¶èªå¨å¨ç¨æ·çææºä¸å建åºåºç¨éè¦çæ°æ®åºè¡¨å¢ï¼æ»ä¸è½è®©æ们å¨æ¯ä¸ªéè¦å®è£
æ¤è½¯ä»¶çææºä¸éè¿æå·¥æ¹å¼å建æ°æ®åºè¡¨å§ï¼å 为è¿ç§éæ±æ¯æ¯ä¸ªæ°æ®åºåºç¨é½è¦é¢ä¸´çï¼æ以å¨Androidç³»ç»ï¼ä¸ºæ们æä¾äºä¸ä¸ªå为SQLiteOpenHelperçæ½è±¡ç±»ï¼å¿
须继æ¿å®æè½ä½¿ç¨ï¼å®æ¯éè¿å¯¹æ°æ®åºçæ¬è¿è¡ç®¡çæ¥å®ç°åé¢æåºçéæ±ã
ãã为äºå®ç°å¯¹æ°æ®åºçæ¬è¿è¡ç®¡çï¼SQLiteOpenHelperç±»æä¾äºä¸¤ä¸ªéè¦çæ¹æ³ï¼åå«æ¯onCreate(SQLiteDatabase db)åonUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)ï¼åè
ç¨äºå次使ç¨è½¯ä»¶æ¶çææ°æ®åºè¡¨ï¼åè
ç¨äºå级软件æ¶æ´æ°æ°æ®åºè¡¨ç»æãå½è°ç¨SQLiteOpenHelperçgetWritableDatabase()æè
getReadableDatabase()æ¹æ³è·åç¨äºæä½æ°æ®åºçSQLiteDatabaseå®ä¾çæ¶åï¼å¦ææ°æ®åºä¸åå¨ï¼Androidç³»ç»ä¼èªå¨çæä¸ä¸ªæ°æ®åºï¼æ¥çè°ç¨onCreate()æ¹æ³ï¼onCreate()æ¹æ³å¨å次çææ°æ®åºæ¶æä¼è¢«è°ç¨ï¼å¨onCreate()æ¹æ³éå¯ä»¥çææ°æ®åºè¡¨ç»æåæ·»å ä¸äºåºç¨ä½¿ç¨å°çåå§åæ°æ®ãonUpgrade()æ¹æ³å¨æ°æ®åºççæ¬åçååæ¶ä¼è¢«è°ç¨ï¼ä¸è¬å¨è½¯ä»¶å级æ¶æéæ¹åçæ¬å·ï¼èæ°æ®åºççæ¬æ¯ç±ç¨åºåæ§å¶çï¼å设æ°æ®åºç°å¨ççæ¬æ¯1ï¼ç±äºä¸å¡çåæ´ï¼ä¿®æ¹äºæ°æ®åºè¡¨ç»æï¼è¿æ¶åå°±éè¦å级软件ï¼å级软件æ¶å¸ææ´æ°ç¨æ·ææºéçæ°æ®åºè¡¨ç»æï¼ä¸ºäºå®ç°è¿ä¸ç®çï¼å¯ä»¥æåæ¥çæ°æ®åºçæ¬è®¾ç½®ä¸º2(æåå¦é®è®¾ç½®ä¸º3è¡ä¸è¡ï¼å½ç¶å¯ä»¥ï¼å¦æä½ æ¿æï¼è®¾ç½®ä¸º100ä¹è¡)ï¼å¹¶ä¸å¨ onUpgrade()æ¹æ³éé¢å®ç°è¡¨ç»æçæ´æ°ãå½è½¯ä»¶ççæ¬å级次æ°æ¯è¾å¤ï¼è¿æ¶å¨onUpgrade()æ¹æ³éé¢å¯ä»¥æ ¹æ®åçå·åç®æ çæ¬å·è¿è¡å¤æï¼ç¶åä½åºç¸åºç表ç»æåæ°æ®æ´æ°ã
ããgetWritableDatabase()å getReadableDatabase()æ¹æ³é½å¯ä»¥è·åä¸ä¸ªç¨äºæä½æ°æ®åºçSQLiteDatabaseå®ä¾ãä½ getWritableDatabase() æ¹æ³ä»¥è¯»åæ¹å¼æå¼æ°æ®åºï¼ä¸æ¦æ°æ®åºçç£ç空é´æ»¡äºï¼æ°æ®åºå°±åªè½è¯»èä¸è½åï¼åè¥ä½¿ç¨getWritableDatabase()æå¼æ°æ®åºå°±ä¼åºéãgetReadableDatabase()æ¹æ³å
以读åæ¹å¼æå¼æ°æ®åºï¼å¦ææ°æ®åºçç£ç空é´æ»¡äºï¼å°±ä¼æå¼å¤±è´¥ï¼å½æå¼å¤±è´¥åä¼ç»§ç»å°è¯ä»¥åªè¯»æ¹å¼æå¼æ°æ®åºã
ãã注æï¼getWritableDatabase()ï¼getReadableDatabaseçåºå«æ¯å½æ°æ®åºå满æ¶ï¼è°ç¨åè
ä¼æ¥éï¼è°ç¨åè
ä¸ä¼ï¼æ以å¦æä¸æ¯æ´æ°æ°æ®åºçè¯ï¼æ好è°ç¨åè
æ¥è·å¾æ°æ®åºè¿æ¥ã
ãã代ç ï¼
ããå¤å¶ä»£ç 代ç å¦ä¸:
ããpublic class DatabaseHelper extends SQLiteOpenHelper {
ãã//类没æå®ä¾å,æ¯ä¸è½ç¨ä½ç¶ç±»æé å¨çåæ°,å¿
须声æ为éæ
ããprivate static final String name = "ljqdb"; //æ°æ®åºå称
ããprivate static final int version = 1; //æ°æ®åºçæ¬
ããpublic DatabaseHelper(Context context) {
ãã//第ä¸ä¸ªåæ°CursorFactoryæå®å¨æ§è¡æ¥è¯¢æ¶è·å¾ä¸ä¸ªæ¸¸æ å®ä¾çå·¥åç±»,设置为null,代表使ç¨ç³»ç»é»è®¤çå·¥åç±»
ããsuper(context, name, null, version);
ãã}
ãã@Override
ããpublic void onCreate(SQLiteDatabase db) {
ããdb.execSQL("CREATE TABLE IF NOT EXISTS person (
ããpersonid integer primary key autoincrement, name varchar(20), age INTEGER)");
ãã}
ãã@Override
ããpublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
ããdb.execSQL(" ALTER TABLE person ADD phone VARCHAR(12) NULL "); //å¾è¡¨ä¸å¢å ä¸å
ãã// DROP TABLE IF EXISTS person å é¤è¡¨
ãã}
ãã}
ããå¨å®é
项ç®å¼åä¸ï¼å½æ°æ®åºè¡¨ç»æåçæ´æ°æ¶ï¼åºè¯¥é¿å
ç¨æ·åæ¾äºæ°æ®åºä¸çæ°æ®ä¸¢å¤±ã
ããä¸ã使ç¨SQLiteDatabaseæä½SQLiteæ°æ®åº
ããAndroidæä¾äºä¸ä¸ªå为SQLiteDatabaseçç±»ï¼è¯¥ç±»å°è£
äºä¸äºæä½æ°æ®åºçAPIï¼ä½¿ç¨è¯¥ç±»å¯ä»¥å®æ对æ°æ®è¿è¡æ·»å (Create)ãæ¥è¯¢(Retrieve)ãæ´æ°(Update)åå é¤(Delete)æä½ï¼è¿äºæä½ç®ç§°ä¸ºCRUDï¼ã对SQLiteDatabaseçå¦ä¹ ï¼æ们åºè¯¥éç¹ææ¡execSQL()årawQuery()æ¹æ³ãexecSQL()æ¹æ³å¯ä»¥æ§è¡insertãdeleteãupdateåCREATE TABLEä¹ç±»ææ´æ¹è¡ä¸ºçSQLè¯å¥ï¼ rawQuery()æ¹æ³ç¨äºæ§è¡selectè¯å¥ã
ããexecSQL()æ¹æ³ç使ç¨ä¾åï¼
ããå¤å¶ä»£ç 代ç å¦ä¸:
ããSQLiteDatabase db = ....;
ããdb.execSQL("insert into person(name, age) values('æ计é¦', 24)");
ããdb.close();
ããæ§è¡ä¸é¢SQLè¯å¥ä¼å¾person表ä¸æ·»å è¿ä¸æ¡è®°å½ï¼å¨å®é
åºç¨ä¸ï¼ è¯å¥ä¸çâæ计é¦âè¿äºåæ°å¼ä¼ç±ç¨æ·è¾å
¥çé¢æä¾ï¼å¦ææç¨æ·è¾å
¥çå
容åæ ·ç»æ¼å°ä¸é¢çinsertè¯å¥ï¼ å½ç¨æ·è¾å
¥çå
容å«æåå¼å·æ¶ï¼ç»æ¼åºæ¥çSQLè¯å¥å°±ä¼åå¨è¯æ³é误ãè¦è§£å³è¿ä¸ªé®é¢éè¦å¯¹åå¼å·è¿è¡è½¬ä¹ï¼ä¹å°±æ¯æåå¼å·è½¬æ¢æ两个åå¼å·ãæäºæ¶åç¨æ·å¾å¾è¿ä¼è¾å
¥åâ & âè¿äºç¹æ®SQL符å·ï¼ä¸ºä¿è¯ç»æ¼å¥½çSQLè¯å¥è¯æ³æ£ç¡®ï¼å¿
须对SQLè¯å¥ä¸çè¿äºç¹æ®SQL符å·é½è¿è¡è½¬ä¹ï¼æ¾ç¶ï¼å¯¹æ¯æ¡SQLè¯å¥é½åè¿æ ·çå¤çå·¥ä½æ¯æ¯è¾ç¦ççã SQLiteDatabaseç±»æä¾äºä¸ä¸ªéè½½åçexecSQL(String sql, Object[] bindArgs)æ¹æ³ï¼ä½¿ç¨è¿ä¸ªæ¹æ³å¯ä»¥è§£å³åé¢æå°çé®é¢ï¼å 为è¿ä¸ªæ¹æ³æ¯æ使ç¨å ä½ç¬¦åæ°(?)ã使ç¨ä¾åå¦ä¸ï¼
ããå¤å¶ä»£ç 代ç å¦ä¸:
ããSQLiteDatabase db = ....;
ããdb.execSQL("insert into person(name, age) values(?,?)", new Object[]{"ä¼ æºæ客", 4});
ããdb.close();
ããexecSQL(String sql, Object[] bindArgs)æ¹æ³ç第ä¸ä¸ªåæ°ä¸ºSQLè¯å¥ï¼ç¬¬äºä¸ªåæ°ä¸ºSQLè¯å¥ä¸å ä½ç¬¦åæ°çå¼ï¼åæ°å¼å¨æ°ç»ä¸ç顺åºè¦åå ä½ç¬¦çä½ç½®å¯¹åºã
ããSQLiteDatabaseçrawQuery()ç¨äºæ§è¡selectè¯å¥ï¼ä½¿ç¨ä¾åå¦ä¸ï¼
ããå¤å¶ä»£ç 代ç å¦ä¸:
ããSQLiteDatabase db = ....;
ããCursor cursor = db.rawQuery("select * from person", null);
ããwhile (cursor.moveToNext()) {
ããint personid = cursor.getInt(0); //è·å第ä¸åçå¼,第ä¸åçç´¢å¼ä»0å¼å§
ããString name = cursor.getString(1);//è·å第äºåçå¼
ããint age = cursor.getInt(2);//è·å第ä¸åçå¼
ãã}
ããcursor.close();
ããdb.close();
ããrawQuery()æ¹æ³ç第ä¸ä¸ªåæ°ä¸ºselectè¯å¥ï¼ç¬¬äºä¸ªåæ°ä¸ºselectè¯å¥ä¸å ä½ç¬¦åæ°çå¼ï¼å¦æselectè¯å¥æ²¡æ使ç¨å ä½ç¬¦ï¼è¯¥åæ°å¯ä»¥è®¾ç½®ä¸ºnullã带å ä½ç¬¦åæ°çselectè¯å¥ä½¿ç¨ä¾åå¦ä¸ï¼
ããå¤å¶ä»£ç 代ç å¦ä¸:
ããCursor cursor = db.rawQuery("select * from person where name like ? and age=?", new String[]{"%æ计é¦%", "4"});
ããCursoræ¯ç»æé游æ ï¼ç¨äºå¯¹ç»æéè¿è¡éæºè®¿é®ï¼å¦æ大家çæjdbcï¼ å
¶å®Cursorä¸JDBCä¸çResultSetä½ç¨å¾ç¸ä¼¼ã使ç¨moveToNext()æ¹æ³å¯ä»¥å°æ¸¸æ ä»å½åè¡ç§»å¨å°ä¸ä¸è¡ï¼å¦æå·²ç»ç§»è¿äºç»æéçæåä¸è¡ï¼è¿åç»æ为falseï¼å¦å为trueãå¦å¤Cursor è¿æ常ç¨çmoveToPrevious()æ¹æ³ï¼ç¨äºå°æ¸¸æ ä»å½åè¡ç§»å¨å°ä¸ä¸è¡ï¼å¦æå·²ç»ç§»è¿äºç»æéç第ä¸è¡ï¼è¿åå¼ä¸ºfalseï¼å¦å为true ï¼ãmoveToFirst()æ¹æ³ï¼ç¨äºå°æ¸¸æ 移å¨å°ç»æéç第ä¸è¡ï¼å¦æç»æé为空ï¼è¿åå¼ä¸ºfalseï¼å¦å为true ï¼åmoveToLast()æ¹æ³ï¼ç¨äºå°æ¸¸æ 移å¨å°ç»æéçæåä¸è¡ï¼å¦æç»æé为空ï¼è¿åå¼ä¸ºfalseï¼å¦å为true ï¼ ã
ããé¤äºåé¢ç»å¤§å®¶ä»ç»çexecSQL()årawQuery()æ¹æ³ï¼ SQLiteDatabaseè¿ä¸é¨æä¾äºå¯¹åºäºæ·»å ãå é¤ãæ´æ°ãæ¥è¯¢çæä½æ¹æ³ï¼ insert()ãdelete()ãupdate()åquery() ãè¿äºæ¹æ³å®é
ä¸æ¯ç»é£äºä¸å¤ªäºè§£SQLè¯æ³çèé¸ä½¿ç¨çï¼å¯¹äºçæSQLè¯æ³çç¨åºåèè¨ï¼ç´æ¥ä½¿ç¨execSQL()årawQuery()æ¹æ³æ§è¡SQLè¯å¥å°±è½å®ææ°æ®çæ·»å ãå é¤ãæ´æ°ãæ¥è¯¢æä½ã
温馨提示:答案为网友推荐,仅供参考