Android sqlite3 操作语句和方法

Android sqlite3 操作语句和方法

一、创建/删除表

String sql="Create table "+TABLE_NAME+"("+FIELD_ID+" integer primary key autoincrement," +FIELD_TITLE+" text );";
        db.execSQL(sql);

String sql=" DROP TABLE IF EXISTS "+TABLE_NAME;
        db.execSQL(sql);

二、查询

从表中查询数据（in）  SELECT* FROM meta where media_id in (1,2,9);

三、插入

SQLiteDatabase db=this.getWritableDatabase();
        ContentValues cv=new ContentValues(); 
        cv.put(FIELD_TITLE, Title);
        long row=db.insert(TABLE_NAME, null, cv);

四、更新

SQLiteDatabase db=this.getWritableDatabase();
        String where=FIELD_ID+"=?";
        String[] whereValue={Integer.toString(id)};
        ContentValues cv=new ContentValues(); 
        cv.put(FIELD_TITLE, Title);
        db.update(TABLE_NAME, cv, where, whereValue);

五、删除

SQLiteDatabase db=this.getWritableDatabase();
        String where=FIELD_ID+"=?";
        String[] whereValue={Integer.toString(id)};
        db.delete(TABLE_NAME, where, whereValue);

事例代码：
packagecom.example.mtc_ly_myweather.sqlit3;

importandroid.content.ContentValues;

importandroid.content.Context;

importandroid.database.Cursor;

importandroid.database.sqlite.SQLiteDatabase;

importandroid.database.sqlite.SQLiteDatabase.CursorFactory;

importandroid.database.sqlite.SQLiteOpenHelper;

importandroid.util.Log;

publicclassMySqlitDB extendsSQLiteOpenHelper{

privatestaticString dbName= "weatherDB";

privatestaticintdbVersion= 1;

privateString tableName= "weatherTable";

/*

*context 上下文

*name 数据库名字

*version 数据库版本号

*factory 用于查询时返回Cursor的子类对象；或者传入null使用默认的factory构造

*/

publicMySqlitDB(Context context) {//主要用于获取上下文

super(context,dbName,null,dbVersion);

}

@Override

publicvoidonCreate(SQLiteDatabase db) {//该方法是创建表的时候调用创建表的时候调用，创建对象时是不会调用的

StringBuilderstringBuilder = newStringBuilder("createtable ").append(tableName)

.append("(").append("table_IDinteger ").append("primarykey autoincrement")

.append(",dataNumtext").append(",citytext").append(",cityNumtext")

.append(",temp1text").append(",temp2text").append(",weathertext").append(");");

Log.d("look","onCreate"+stringBuilder.toString());

db.execSQL(stringBuilder.toString());

}

@Override

publicvoidonUpgrade(SQLiteDatabase db, intoldVersion, intnewVersion) {//当版本号有更新的时候调用该方法

StringBuilderstringBuilder = newStringBuilder("DROPTABLE IF EXISTS ").append(tableName);

Log.d("look","onUpgrade"+stringBuilder.toString());

db.execSQL(stringBuilder.toString());

// onCreate(db);

}

publiclonginstert(String[] keyStrings, String[] dataStrings) {//添加数据

longrow = 0;

SQLiteDatabasedb=this.getWritableDatabase();

ContentValuescv=newContentValues();

for(inti = 0; i < keyStrings.length;i++) {

cv.put(keyStrings[i], dataStrings[i]);

}

row=db.insert(tableName,null,cv);//sql添加方法

// db.execSQL("insert into "+tableName+"(table_ID,dataNum,city,cityNum,temp1,temp2,weather)values(?,?,?,?,?,?,?)",

// newObject[]{5,"10","北京","1000000000","22","28","晴"});//sql添加语句

returnrow;

}

publicvoiddelete(intid) {//删除数据

SQLiteDatabasedb = this.getWritableDatabase();

Stringwhere = "table_ID= ?";

String[]whereValue={Integer.toString(id)};

db.delete(tableName,where, whereValue);//sql查找方法

// db.execSQL("delete from "+tableName +" wheretable_ID = ?",new Object[]{5});//sql删除语句

}

publicvoidupdate(intid) {//修改数据

SQLiteDatabasedb=this.getWritableDatabase();

Stringwhere="table_ID= ?";

String[]whereValue={Integer.toString(id)};

ContentValuescv=newContentValues();

cv.put("city","深圳");

db.update(tableName,cv, where, whereValue);//sql修改方法

// db.execSQL("update "+tableName +" set city=?,dataNum=? where table_ID=?",

// new Object[]{"上海","200000000000",1});//sql修改语句

}

publicCursor select() {//查找数据库

SQLiteDatabasedb = this.getReadableDatabase();

Cursorcursor = db

.query(tableName,null,null,null,null,null,null);//sql查找方法

// cursor=db.rawQuery("select* from "+tableName +" where table_ID = ?",newString[]{"6"});//sql查找语句

returncursor;

}

publicvoiddeleteDB(Context context ) {//删除数据库

Log.d("look","deleteDB"+dbName);

context.deleteDatabase(dbName);

}

publicvoidcloseDB(Context context ) {//关闭数据库

//数据库也可以不关闭,因为这样的话可以提升性能,因为不用频繁的开关

SQLiteDatabasedb = this.getReadableDatabase();

db.close();

}

}