数据库操作总结

 public ArrayList getUsers(){

	DBAdapter dbAdapter=DBAdapter.getDBAdapterInstance(this);
	try {
		dbAdapter.createDataBase();
	} catch (IOException e) {
		Log.i("*** select ",e.getMessage());
	}
   	dbAdapter.openDataBase();
	String query="SELECT * FROM user;";
	ArrayList> stringList = dbAdapter.selectRecordsFromDBList(query, null);
	dbAdapter.close();

	ArrayList usersList = new ArrayList();
	for (int i = 0; i < stringList.size(); i++) {
		ArrayList list = stringList.get(i);
		UserBO user = new UserBO();
		try {
			user.id = Integer.parseInt(list.get(0));
			user.name = list.get(1);
			user.age = Long.parseLong(list.get(2));
		} catch (Exception e) {
			Log.i("***" + Select.class.toString(), e.getMessage());
		}
		usersList.add(user);
	}
	return usersList;
}

 插入：

DBAdapter dbAdapter = DBAdapter.getDBAdapterInstance(Insert.this);
dbAdapter.openDataBase();

ContentValues initialValues = new ContentValues();
initialValues.put("name", etName.getText().toString());
initialValues.put("age", etAge.getText().toString());
long n = dbAdapter.insertRecordsInDB("user", null, initialValues);
Toast.makeText(Insert.this, "new row inserted with id = " + n, Toast.LENGTH_SHORT).show();

 更新：

DBAdapter dbAdapter = DBAdapter.getDBAdapterInstance(Update.this);
dbAdapter.openDataBase();

ContentValues initialValues = new ContentValues();
initialValues.put("name", etName.getText().toString());
initialValues.put("age", etAge.getText().toString());
String id = etId.getText().toString();
String [] strArray = {""+id};
long n = dbAdapter.updateRecordsInDB("user", initialValues, "id=?", strArray);

Toast.makeText(Update.this, n+" rows updated", Toast.LENGTH_SHORT).show();

 删除：

DBAdapter dbAdapter = DBAdapter.getDBAdapterInstance(Delete.this);
dbAdapter.openDataBase();

String id = etId.getText().toString();
String [] strArray = {""+id};
long n = dbAdapter.deleteRecordInDB("user", "id = ?", strArray);
Toast.makeText(Delete.this, n+" rows effected", Toast.LENGTH_SHORT).show();

 

ublic class DBAdapter extends SQLiteOpenHelper {

	private static String DB_PATH = "";
	private static final String DB_NAME = "user.sqlite";
	private SQLiteDatabase myDataBase;
	private final Context myContext;

	private static DBAdapter mDBConnection;

	/**
	 * Constructor
	 * Takes and keeps a reference of the passed context in order to access to the application assets and resources.
	 * @param context
	 */
	private DBAdapter(Context context) {
		super(context, DB_NAME, null, 1);
		this.myContext = context;
		DB_PATH = "/data/data/"
				+ context.getApplicationContext().getPackageName()
				+ "/databases/";
		// The Android's default system path of your application database is
		// "/data/data/mypackagename/databases/"
	}

	/**
	 * getting Instance
	 * @param context
	 * @return DBAdapter
	 */
	public static synchronized DBAdapter getDBAdapterInstance(Context context) {
		if (mDBConnection == null) {
			mDBConnection = new DBAdapter(context);
		}
		return mDBConnection;
	}

	/**
	 * Creates an empty database on the system and rewrites it with your own database.
	 **/
	public void createDataBase() throws IOException {
		boolean dbExist = checkDataBase();
		if (dbExist) {
			// do nothing - database already exist
		} else {
			// By calling following method
			// 1) an empty database will be created into the default system path of your application
			// 2) than we overwrite that database with our database.
			this.getReadableDatabase();
			try {
				copyDataBase();
			} catch (IOException e) {
				throw new Error("Error copying database");
			}
		}
	}

	/**
	 * Check if the database already exist to avoid re-copying the file each time you open the application.
	 * @return true if it exists, false if it doesn't
	 */
	private boolean checkDataBase() {
		SQLiteDatabase checkDB = null;
		try {
			String myPath = DB_PATH + DB_NAME;
			checkDB = SQLiteDatabase.openDatabase(myPath, null,
					SQLiteDatabase.OPEN_READONLY);

		} catch (SQLiteException e) {
			// database does't exist yet.
		}
		if (checkDB != null) {
			checkDB.close();
		}
		return checkDB != null ? true : false;
	}

	/**
	 * Copies your database from your local assets-folder to the just created
	 * empty database in the system folder, from where it can be accessed and
	 * handled. This is done by transfering bytestream.
	 * */
	private void copyDataBase() throws IOException {
		    // Open your local db as the input stream
		InputStream myInput = myContext.getAssets().open(DB_NAME);
		    // Path to the just created empty db
		String outFileName = DB_PATH + DB_NAME;
		    // Open the empty db as the output stream
		OutputStream myOutput = new FileOutputStream(outFileName);
		    // transfer bytes from the inputfile to the outputfile
		byte[] buffer = new byte[1024];
		int length;
		while ((length = myInput.read(buffer)) > 0) {
			myOutput.write(buffer, 0, length);
		}
		    // Close the streams
		myOutput.flush();
		myOutput.close();
		myInput.close();
	}

	/**
	 * Open the database
	 * @throws SQLException
	 */
	public void openDataBase() throws SQLException {
		String myPath = DB_PATH + DB_NAME;
		myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);
	}

	/**
	 * Close the database if exist
	 */
	@Override
	public synchronized void close() {
		if (myDataBase != null)
			myDataBase.close();
		super.close();
	}

	/**
	 * Call on creating data base for example for creating tables at run time
	 */
	@Override
	public void onCreate(SQLiteDatabase db) {
	}

	/**
	 * can used for drop tables then call onCreate(db) function to create tables again - upgrade
	 */
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
	}

	// ----------------------- CRUD Functions ------------------------------

	/**
	 * This function used to select the records from DB.
	 * @param tableName
	 * @param tableColumns
	 * @param whereClase
	 * @param whereArgs
	 * @param groupBy
	 * @param having
	 * @param orderBy
	 * @return A Cursor object, which is positioned before the first entry.
	 */
	public Cursor selectRecordsFromDB(String tableName, String[] tableColumns,
			String whereClase, String whereArgs[], String groupBy,
			String having, String orderBy) {
		return myDataBase.query(tableName, tableColumns, whereClase, whereArgs,
				groupBy, having, orderBy);
	}

	/**
	 * select records from db and return in list
	 * @param tableName
	 * @param tableColumns
	 * @param whereClase
	 * @param whereArgs
	 * @param groupBy
	 * @param having
	 * @param orderBy
	 * @return ArrayList>
	 */
	public ArrayList> selectRecordsFromDBList(String tableName, String[] tableColumns,
			String whereClase, String whereArgs[], String groupBy,
			String having, String orderBy) {

		ArrayList> retList = new ArrayList>();
	      ArrayList list = new ArrayList();
	      Cursor cursor = myDataBase.query(tableName, tableColumns, whereClase, whereArgs,
					groupBy, having, orderBy);
	      if (cursor.moveToFirst()) {
	         do {
	        	 list = new ArrayList();
	        	 for(int i=0; i 0;
	}

	/**
	 * This function used to update the Record in DB.
	 * @param tableName
	 * @param initialValues
	 * @param whereClause
	 * @param whereArgs
	 * @return 0 in case of failure otherwise return no of row(s) are updated
	 */
	public int updateRecordsInDB(String tableName,
			ContentValues initialValues, String whereClause, String whereArgs[]) {
		return myDataBase.update(tableName, initialValues, whereClause, whereArgs);
	}

	/**
	 * This function used to delete the Record in DB.
	 * @param tableName
	 * @param whereClause
	 * @param whereArgs
	 * @return 0 in case of failure otherwise return no of row(s) are deleted.
	 */
	public int deleteRecordInDB(String tableName, String whereClause,
			String[] whereArgs) {
		return myDataBase.delete(tableName, whereClause, whereArgs);
	}

	// --------------------- Select Raw Query Functions ---------------------

	/**
	 * apply raw Query
	 * @param query
	 * @param selectionArgs
	 * @return Cursor
	 */
	public Cursor selectRecordsFromDB(String query, String[] selectionArgs) {
		return myDataBase.rawQuery(query, selectionArgs);
	}

	/**
	 * apply raw query and return result in list
	 * @param query
	 * @param selectionArgs
	 * @return ArrayList>
	 */
	public ArrayList> selectRecordsFromDBList(String query, String[] selectionArgs) {
	      ArrayList> retList = new ArrayList>();
	      ArrayList list = new ArrayList();
	      Cursor cursor = myDataBase.rawQuery(query, selectionArgs);
	      if (cursor.moveToFirst()) {
	         do {
	        	 list = new ArrayList();
	        	 for(int i=0; i<cursor.getColumnCount(); i++){
	        		 list.add( cursor.getString(i) );
	        	 }
	        	 retList.add(list);
	         } while (cursor.moveToNext());
	      }
	      if (cursor != null && !cursor.isClosed()) {
	         cursor.close();
	      }
	      return retList;
	   }

}