Android教程網
  1. 首頁
  2. Android 技術
  3. Android 手機
  4. Android 系統教程
  5. Android 游戲
 Android教程網 >> Android技術 >> 關於Android編程 >> Android學習筆記之SQLite數據庫的使用及常用的增刪改查方法、無sql語句的DRUD方法匯總

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

編輯:關於Android編程


(1)目錄結構如下:

\

(2)鏈接數據庫的文件:DBHelper.java要繼承SQLiteOpenHelper類<喎?/kf/ware/vc/" target="_blank" class="keylink">vcD4KPHA+PHByZSBjbGFzcz0="brush:java;">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 * * } */ /* * 創建數據庫的時候用到的語句 */ @Override public 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); } /* * 更新數據庫的時候使用到的; 這一句在一開始的時候是不會添加的,因為版本號是一樣的只有執行一次之後,修改了版本號才會執行這段代碼 */ @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { String sql = "alter table person add age integer"; db.execSQL(sql); } @Override public 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 querySingleResultBySQL(String sql,
			String[] selectionArgs) {
		Map map = new HashMap();
		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 querySingleCursor(String sql, String[] selectionArgs,
			Class 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  List queryMutilCursor(String sql, String[] selectionArgs,
			Class cls) {
		List list = new ArrayList();
		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> queryMutiResultBySQL(String sql,
			String[] selectionArgs) {
		List> list = new ArrayList>();
		Cursor cursor = database.rawQuery(sql, selectionArgs);
		int cols_len = cursor.getColumnCount();
		while (cursor.moveToNext()) {
			Map map = new HashMap();
			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 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> list = dbManager.queryMutiResultBySQL(sql,
				new String[] { "%張%" });
		for (Map 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();
	}
}

其他文件不做修改!可以實現數據的增刪改查等操作!


  1. 上一頁:
  2. 下一頁:
熱門文章
閱讀排行版
Copyright © Android教程網 All Rights Reserved