Android學習筆記之SQLite資料庫的使用及常用的增刪改查方法、無sql語句的DRUD方法匯總

標籤：sqlite資料庫的使用   增刪改查操作   

（1）目錄結構如下：

（2）連結資料庫的檔案：DBHelper.java要繼承SQLiteOpenHelper類

package com.lc.sqlite_demo1.db;import android.content.Context;import android.database.DatabaseErrorHandler;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteDatabase.CursorFactory;import android.database.sqlite.SQLiteOpenHelper;public class DBHelper extends SQLiteOpenHelper {private static final String DB_NAME = "mydb.db"; // 建立資料庫的檔案private static final int VERSION = 2; // 資料庫版本,版本是更新的依據/* * 需要這個構造方法: 資料庫只有在調用getWritableDatabase();getReadableDatabase(;方法的時候才會建立資料庫 */public DBHelper(Context context) {super(context, DB_NAME, null, VERSION);}/* * public DBManager(Context context, String name, CursorFactory factory, int * version) { super(context, name, factory, version); // TODO Auto-generated * constructor stub *  * } *//* * 建立資料庫的時候用到的語句 */@Overridepublic void onCreate(SQLiteDatabase db) {// 資料類型：varchar、int、long、float、boolean、text、blob、clob等類型// 建表語句執行String sql = "create table person(pid integer primary key autoincrement,name varchar(64),address varchar(200))";db.execSQL(sql);}/* * 更新資料庫的時候使用到的; 這一句在一開始的時候是不會添加的，因為版本號碼是一樣的只有執行一次之後，修改了版本號碼才會執行這段代碼 */@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {String sql = "alter table person add age integer";db.execSQL(sql);}@Overridepublic void onOpen(SQLiteDatabase db) {super.onOpen(db);}}

（3）建立一個DBManager.java還有資料的增、刪、改、查及無sql語句的增、刪、改、查方法匯總：

package com.lc.sqlite_demo1.db;import java.lang.reflect.Field;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;import android.content.ContentValues;import android.content.Context;import android.database.Cursor;import android.database.SQLException;import android.database.sqlite.SQLiteDatabase;public class DBManager {private DBHelper dbHelper;private SQLiteDatabase database;public DBManager(Context context) {dbHelper = new DBHelper(context);}/* * 用於更新資料的包括插入、刪除、修改 */public boolean updateBySQL(String sql, Object[] bindArgs) {boolean flag = false;try {database.execSQL(sql, bindArgs);flag = true;} catch (SQLException e) {e.printStackTrace();} /* * finally { if (database != null) { database.close(); } } */return flag;}/* * 單獨一個方法，獲得資料庫的連結 */public void getDataBaseConn() {database = dbHelper.getWritableDatabase();}/* * 釋放資料庫連結 */public void releaseConn() {if (database != null) {database.close();}}/* * 尋找資料的測試程式 */public void queryBySQL(String sql, String[] selectionArgs) {Cursor cursor = database.rawQuery(sql, selectionArgs);while (cursor.moveToNext()) {System.out.println("-->>"+ cursor.getString(cursor.getColumnIndex("name")));System.out.println("-->>"+ cursor.getString(cursor.getColumnIndex("address")));System.out.println("-->>"+ cursor.getInt(cursor.getColumnIndex("age")));}}/* * 尋找單條資料的查詢方法 */public Map<String, String> querySingleResultBySQL(String sql,String[] selectionArgs) {Map<String, String> map = new HashMap<String, String>();Cursor cursor = database.rawQuery(sql, selectionArgs);int cols_len = cursor.getColumnCount();while (cursor.moveToNext()) {for (int i = 0; i < cols_len; i++) {String cols_name = cursor.getColumnName(i);String cols_value = cursor.getString(cursor.getColumnIndex(cols_name));if (cols_value == null) {cols_value = "";}map.put(cols_name, cols_value);}}return map;}/** * 同構反射獲得資料庫的記錄; 聲明Class的屬性必須都是String類型 *  * @param sql * @param selectionArgs * @param cls * @return */public <T> T querySingleCursor(String sql, String[] selectionArgs,Class<T> cls) {T t = null;Cursor cursor = database.rawQuery(sql, selectionArgs);int cols_len = cursor.getColumnCount();while (cursor.moveToNext()) {try {t = cls.newInstance();for (int i = 0; i < cols_len; i++) {String cols_name = cursor.getColumnName(i);String cols_value = cursor.getString(cursor.getColumnIndex(cols_name));if (cols_value == null) {cols_value = "";}Field field = null;try {field = cls.getDeclaredField(cols_name);} catch (NoSuchFieldException e) {e.printStackTrace();}field.setAccessible(true);field.set(t, cols_value);}} catch (InstantiationException e) {e.printStackTrace();} catch (IllegalAccessException e) {e.printStackTrace();}}return t;}/** * 同構反射獲得多條資料庫的記錄; 聲明Class的屬性必須都是String類型 *  * @param sql * @param selectionArgs * @param cls * @return */public <T> List<T> queryMutilCursor(String sql, String[] selectionArgs,Class<T> cls) {List<T> list = new ArrayList<T>();Cursor cursor = database.rawQuery(sql, selectionArgs);int cols_len = cursor.getColumnCount();while (cursor.moveToNext()) {try {T t = cls.newInstance();for (int i = 0; i < cols_len; i++) {String cols_name = cursor.getColumnName(i);String cols_value = cursor.getString(cursor.getColumnIndex(cols_name));if (cols_value == null) {cols_value = "";}Field field = null;try {field = cls.getDeclaredField(cols_name);} catch (NoSuchFieldException e) {e.printStackTrace();}field.setAccessible(true);field.set(t, cols_value);list.add(t);}} catch (InstantiationException e) {e.printStackTrace();} catch (IllegalAccessException e) {e.printStackTrace();}}return list;}/* * 尋找多條資料的查詢方法 */public List<Map<String, String>> queryMutiResultBySQL(String sql,String[] selectionArgs) {List<Map<String, String>> list = new ArrayList<Map<String, String>>();Cursor cursor = database.rawQuery(sql, selectionArgs);int cols_len = cursor.getColumnCount();while (cursor.moveToNext()) {Map<String, String> map = new HashMap<String, String>();for (int i = 0; i < cols_len; i++) {String cols_name = cursor.getColumnName(i);String cols_value = cursor.getString(cursor.getColumnIndex(cols_name));if (cols_value == null) {cols_value = "";}map.put(cols_name, cols_value);}list.add(map);}return list;}public Cursor queryMutiCursor(String sql, String[] selectionArgs) {Cursor cursor = database.rawQuery(sql, selectionArgs);return cursor;}/**************************** 以下是不需要sql語句的查詢方法 **********************************//** *  * @param tabeName * @param nullColumnHack * @param values * @return */public boolean insertByNotSQL(String tabeName, String nullColumnHack,ContentValues values) {boolean flag = false;// insert into tableName(a,,b,c) values(?,?,?)long id = database.insert(tabeName, nullColumnHack, values);flag = (id > 0 ? true : false);return flag;}/** * 無sql語句的更新操作 *  * @param tabeName * @param values * @param whereClause * @param whereArgs * @return */public boolean updateByNotSQL(String tabeName, ContentValues values,String whereClause, String[] whereArgs) {boolean flag = false;// update tableName set name =?,address=?,age=? where pid=?int count = database.update(tabeName, values, whereClause, whereArgs); // 影響資料的行數flag = (count > 0 ? true : false);return flag;}/** * 無sql語句的刪除操作 *  * @param table * @param whereClause * @param whereArgs * @return */public boolean deleteNotSQL(String table, String whereClause,String[] whereArgs) {boolean flag = false;// delete from tableName where pid=?int count = database.delete(table, whereClause, whereArgs);flag = (count > 0 ? true : false);return flag;}/** * sql標準寫法：select [distinct][columnName] ... from tableName * [where][selection][selectionArgs][groupBy][having][order by][limit] *  * @param distinct * @param table * @param columns * @param selection * @param selectionArgs * @param groupBy * @param having * @param orderBy * @param limit * @return */public Cursor queryByNotSQL(boolean distinct, String table,String[] columns, String selection, String[] selectionArgs,String groupBy, String having, String orderBy, String limit) {Cursor cursor = null;/* * 這是一條折中 的查詢方法，其他的方法就是一系列的建構函式 */cursor = database.query(distinct, table, columns, selection,selectionArgs, groupBy, having, orderBy, limit);return cursor;}}

（4）測試類別（要在資訊清單檔中加許可權！）

package com.lc.sqlite_demo1;import java.util.List;import java.util.Map;import com.lc.sqlite_demo1.db.DBHelper;import com.lc.sqlite_demo1.db.DBManager;import android.content.ContentValues;import android.database.Cursor;import android.test.AndroidTestCase;public class MyTest extends AndroidTestCase {/* * 測試建立資料庫，只有調用getReadableDatabase()/getReadableDatabase()方法才會建立資料庫 */public void initTable() {DBHelper dbManager = new DBHelper(getContext());dbManager.getReadableDatabase();}public void insert() {// String sql = "insert into person(name,address,age) values(?,?,?)";String sql = "insert into person(name,address) values(?,?)";Object[] bindArgs = { "張三", "成都" };DBManager dbManager = new DBManager(getContext());dbManager.getDataBaseConn();dbManager.updateBySQL(sql, bindArgs);dbManager.releaseConn();}public void update() {String sql = "update person set name=?,address=?,age=? where pid =1";Object[] bindArgs = { "王五", "北京", 23 };DBManager dbManager = new DBManager(getContext());dbManager.getDataBaseConn();dbManager.updateBySQL(sql, bindArgs);dbManager.releaseConn();}public void delete() {String sql = "delete from person where pid =?";Object[] bindArgs = { 1 };DBManager dbManager = new DBManager(getContext());dbManager.getDataBaseConn();dbManager.updateBySQL(sql, bindArgs);dbManager.releaseConn();}public void query() {String sql = "select * from person";DBManager dbManager = new DBManager(getContext());dbManager.getDataBaseConn();dbManager.queryBySQL(sql, null);dbManager.releaseConn();}public void querySingleResultBySQLTest() {String sql = "select * from person where pid =?";DBManager dbManager = new DBManager(getContext());dbManager.getDataBaseConn();Map<String, String> map = dbManager.querySingleResultBySQL(sql,new String[] { "2" });System.out.println("--->>" + map.get("name"));System.out.println("--->>" + map.get("address"));System.out.println("--->>" + map.get("age"));dbManager.releaseConn();}public void queryMutiResultBySQLTest() {String sql = "select * from person where name like ?";DBManager dbManager = new DBManager(getContext());dbManager.getDataBaseConn();List<Map<String, String>> list = dbManager.queryMutiResultBySQL(sql,new String[] { "%張%" });for (Map<String, String> map2 : list) {System.out.println("---->>" + map2.get("name"));System.out.println("---->>" + map2.get("address"));System.out.println("--->>" + map2.get("age"));}dbManager.releaseConn();}/**************************** 以下是不需要sql語句的查詢方法測試 **********************************//* * 無需查詢語句的插入操作 */public void insertNotSQLTest() {DBManager dbManager = new DBManager(getContext());dbManager.getDataBaseConn();ContentValues values = new ContentValues();values.put("name", "xuliugen");values.put("address", "dfhkjsdhfkjhsd");values.put("age", 22);dbManager.insertByNotSQL("person", null, values);dbManager.releaseConn();}/* * 無需查詢語句的更新操作 */public void updateNotSQLTest() {DBManager dbManager = new DBManager(getContext());dbManager.getDataBaseConn();ContentValues values = new ContentValues();values.put("name", "fff");values.put("address", "12srqrqwrewrqrwetrew");values.put("age", 22);dbManager.updateByNotSQL("person", values, "pid=? and name=?",new String[] { "5", "fff" });dbManager.releaseConn();}/* * 無需查詢語句的刪除操作 */public void deleteNotSQL() {DBManager dbManager = new DBManager(getContext());dbManager.getDataBaseConn();dbManager.deleteNotSQL("person", "pid=?", new String[] { "5" });dbManager.releaseConn();}/* * 無需查詢語句的查詢操作 */public void queryByNotSQLTest() {DBManager dbManager = new DBManager(getContext());dbManager.getDataBaseConn();Cursor cursor = dbManager.queryByNotSQL(false, "person", null, null,null, null, null, null, null);while (cursor.moveToNext()) {System.out.println("-->>" + cursor.getColumnIndex("name"));System.out.println("-->>" + cursor.getColumnIndex("age"));System.out.println("-->>" + cursor.getColumnIndex("address"));}dbManager.releaseConn();}}

其他檔案不做修改！可以實現資料的增刪改查等操作！

Android學習筆記之SQLite資料庫的使用及常用的增刪改查方法、無sql語句的DRUD方法匯總