Android下建立数据库

一，数据库基本数据
public final class DataTools {

	public static final String AUTHORITY = "yourpackageName.WeatherProvider";

	public static final String PACKAGE_NAME = "yourpackageName";
	public static final int GET_ALL_CHANNEL = 100;

	public static final Uri CONTENT_URI = Uri.parse("content://" + AUTHORITY + "/getAllData");

	public static final String TABLE_NAME = "weatherTable";
	public static final String DB_PATH = "/data/data/" + PACKAGE_NAME + "/databases/weather.db";
	public static final String DB_NAME = "weather.db";

	public static final int DATABASE_VERSION = 1;

	public static final String _ID = "id";

	public static final String HASHCODE = "hashcode";

	public static final String CITY = "city";
	public static final String TEMP_CUR = "temp_cur";
	public static final String TEMP_L = "temp_l";
	public static final String TEMP_H = "temp_h";
	public static final String IMAGE = "image";
	public static final String STATUS = "status";

}

二，数据库后台操作
public class WeatherProvider extends ContentProvider {

	private static final String TAG = "ChannelProvider";
	private static UriMatcher mMatcher = new UriMatcher(UriMatcher.NO_MATCH);
	private WeatherDataBaseHelper mDBHelper;
	static {
		mMatcher.addURI(DataTools.AUTHORITY, "getAllData", DataTools.GET_ALL_CHANNEL);
	}

	@Override
	public boolean onCreate() {

		// TODO Auto-generated method stub
		Log.v("pin", "*************WeatherProvider onCreate db----------------");
		mDBHelper = new WeatherDataBaseHelper(this.getContext(), DataTools.DB_PATH, 1);
		return true;
	}

	public WeatherDataBaseHelper getDBHelper() {

		return mDBHelper;
	}

	// delete(String table, String whereClause, String[] whereArgs)
	@Override
	public int delete(Uri uri, String selection, String[] selectionArgs) {

		// TODO Auto-generated method stub
		int rowId = 0;
		SQLiteDatabase db = mDBHelper.getReadableDatabase();
		rowId = db.delete(DataTools.TABLE_NAME, selection, selectionArgs);
		getContext().getContentResolver().notifyChange(uri, null);
		return rowId;
		// return 0;
	}

	@Override
	public String getType(Uri uri) {

		// TODO Auto-generated method stub
		return null;
	}

	// insert(String table, String nullColumnHack, ContentValues values)
	@Override
	public Uri insert(Uri uri, ContentValues values) {

		// TODO Auto-generated method stub
		SQLiteDatabase db = mDBHelper.getWritableDatabase();
		long rowId = db.insert(DataTools.TABLE_NAME, null, values);
		if (rowId > 0) {
			Log.v(TAG, "insert url return rowId " + rowId);
			getContext().getContentResolver().notifyChange(uri,
					null);// duopin
			return uri;
		}

		return null;
	}

	@Override
	public Cursor query(Uri uri, String[] projection, String selection,
			String[] selectionArgs, String sortOrder) {

		// TODO Auto-generated method stub
		SQLiteDatabase db = mDBHelper.getReadableDatabase();
		return db.query(DataTools.TABLE_NAME, projection, selection, selectionArgs, null, null, sortOrder);
		// return null;
	}

	@Override
	public int update(Uri uri, ContentValues values, String selection,
			String[] selectionArgs) {

		// TODO Auto-generated method stub
		int rowId = 0;
		SQLiteDatabase db = mDBHelper.getReadableDatabase();
		rowId = db.update(DataTools.TABLE_NAME, values, selection, selectionArgs);
		getContext().getContentResolver().notifyChange(uri, null);
		return rowId;
		// return 0;
	}

}


三，建立数据库
public class WeatherDataBaseHelper extends SQLiteOpenHelper {

	private static final String TAG = "pin";

	// private static final String CREAT_TABLE_SQL =
	// "create table IPTV(_id integer primary key autoincrement,"
	// +"_name String not null, _path String not null)";

	// private static final String CREAT_TABLE_SQL = "create table " +
	// DataTools.TABLE_NAME + "("
	// + DataTools._ID + " integer  autoincrement,"
	// + DataTools.NAME + " String not null,"
	// + DataTools.PATH + " String not null,"
	// + DataTools.HASHCODE + " integer "
	// + "primary key(" + DataTools._ID + "," + DataTools.HASHCODE + ")"
	// + ")";

	private static final String CREAT_TABLE_SQL = "create table " + DataTools.TABLE_NAME + "("
			+ DataTools._ID + " integer primary key,"
			+ DataTools.CITY + " String not null,"
			+ DataTools.TEMP_CUR + " String not null,"
			+ DataTools.TEMP_L + " String not null,"
			+ DataTools.TEMP_H + " String not null,"
			+ DataTools.STATUS + " String not null,"
			+ DataTools.IMAGE + " blob not null" + ")";// blob

	/**
	 * @param context
	 */
	public WeatherDataBaseHelper(Context context) {

		super(context, DataTools.DB_NAME, null, DataTools.DATABASE_VERSION);
		// TODO Auto-generated constructor stub
	}

	/**
	 * @param context
	 * @param name
	 * @param factory
	 * @param version
	 */
	public WeatherDataBaseHelper(Context context, String name, int version) {

		super(context, name, null, version);
		// TODO Auto-generated constructor stub
		Log.v(TAG, " WeatherDataBaseHelper-------constructor------------");
	}

	@Override
	public void onCreate(SQLiteDatabase db) {

		Log.v(TAG, "WeatherDataBaseHelper on creat-------------------");
		db.execSQL(CREAT_TABLE_SQL);
	}

	/**
	 * when version changed,updata database
	 */
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

		Log.v(TAG, "on upgrade");
		String sql = " DROP TABLE IF EXISTS " + DataTools.TABLE_NAME;
		db.execSQL(sql);
		onCreate(db);
	}

}
四，操作数据库（应用）
public class WeatherResolver {

	private static final String TAG = "ChannelResolver";

	private final Context mContext;

	WeatherDataBaseHelper db;
	SQLiteDatabase mSQLiteDatabase;

	public WeatherResolver(Context c) {

		mContext = c;
		db = new WeatherDataBaseHelper(c);
		mSQLiteDatabase = db.getWritableDatabase();// then creat db

	}

	public ArrayList<Map<String, String>> getChannelList() {

		return null;

	}

	// insert one channel to system data base
	public boolean insertOneData(Context con, Map<String, String> data, byte[] bytes) {

		if (data == null || data.size() == 0)
			return false;

		// ContentResolver resolver = con.getContentResolver();
		// Cursor cursor = resolver.query(DataTools.CONTENT_URI, new String[] {
		// DataTools.CITY, DataTools.NAME },
		// DataTools.CITY + " = ? ",
		// new String[] { channel.get(DataTools.CITY) }, DataTools.NAME);
		Log.d("pin", "insertOneChannel..City Name:" + data.get(DataTools.CITY).toString());
		// Cursor cursor = mSQLiteDatabase.rawQuery("select * from " +
		// DataTools.TABLE_NAME + " where " + DataTools.CITY
		// + "='" + channel.get(DataTools.CITY).toString() + "'",
		// null);xing
		// Cursor cursor = mSQLiteDatabase.rawQuery(
		// "select * from " + DataTools.TABLE_NAME + " where " + DataTools.CITY
		// + "=?",
		// new String[] { channel.get(DataTools.CITY).toString() });
		Cursor cursor = mSQLiteDatabase.rawQuery(
				"select * from " + DataTools.TABLE_NAME, null);
		ContentValues value = new ContentValues();
		value.put(DataTools.CITY, data.get(DataTools.CITY));
		value.put(DataTools.TEMP_CUR, data.get(DataTools.TEMP_CUR));
		value.put(DataTools.TEMP_L, data.get(DataTools.TEMP_L));
		value.put(DataTools.TEMP_H, data.get(DataTools.TEMP_H));
		value.put(DataTools.STATUS, data.get(DataTools.STATUS));

		// value.put(DataTools.IMAGE, data.get(DataTools.IMAGE));bytes
		value.put(DataTools.IMAGE, bytes);
		// database have data ,then not insert
		if (cursor != null && cursor.moveToFirst()) {

			updateOneDatabyID(con, value, 1);
			con.getContentResolver().notifyChange(DataTools.CONTENT_URI, null);
			Log.d("pin", "insert have pre data...not insert,then update data, db notifyChange");
			cursor.close();
			return false;
		}

		// value.put(DataTools.HASHCODE,
		// channel.get(DataTools.PATH).hashCode());
		// resolver.insert(DataTools.CONTENT_URI, value);
		mSQLiteDatabase.insert(DataTools.TABLE_NAME, null, value);

		Log.d("pin", "insert data...success");
		// mSQLiteDatabase.notifyAll();
		con.getContentResolver().notifyChange(DataTools.CONTENT_URI, null);
		if (cursor != null) {
			cursor.close();
		}
		return true;
	}

	public boolean insertAllChannel2DB(Context con, Map<String, String> channelMap) {

		SQLiteDatabase database = SQLiteDatabase.openDatabase(DataTools.DB_PATH, null, SQLiteDatabase.OPEN_READWRITE);

		return true;
	}

	public boolean deleteAllChannel(Context con) {

		return true;
	}

	public boolean deleteOneChannel(Context con, String channelPath) {

		return true;
	}

	public boolean updateOneDatabyID(Context con, ContentValues values, int id) {

		mSQLiteDatabase.update(DataTools.TABLE_NAME, values, "" + DataTools._ID + "=?", new String[] { String.valueOf(id) });
		Log.d("pin", "updateOneData data...success");
		return true;
	}
}

PS：当插入多条数据时，要采用事务方式：


public boolean insertAllChannel2DB(Context con, Map<String, String> channelMap) {

		SQLiteDatabase database = SQLiteDatabase.openDatabase(DataTools.DB_PATH, null, SQLiteDatabase.OPEN_READWRITE);

		if (database.isReadOnly()) {
			Log.e(TAG, "database.isReadOnly...");
			return false;
		}
		long start = Calendar.getInstance().getTimeInMillis();
		Log.e("8", "====== start insert time:" + String.valueOf(start));
		ContentResolver resolver = con.getContentResolver();
		try {
			database.beginTransaction();
			for (String name : channelMap.keySet()) {
				Log.v(TAG, "name:" + name);
				String path = channelMap.get(name);// key->values
				Log.v(TAG, "path:" + path);

				ContentValues value = new ContentValues();
				value.put(DataTools._ID, path.hashCode());
				value.put(DataTools.NAME, name);
				value.put(DataTools.PATH, path);
				String[] args = new String[] { String.valueOf(path.hashCode()) };
				database.delete(DataTools.TABLE_NAME, DataTools._ID + "=?", args);
				// resolver.insert(DataTools.CONTENT_URI, value);
				// String sql = "insert into " + DataTools.TABLE_NAME +
				// " values(?,?,?)";
				database.insert(DataTools.TABLE_NAME, null, value);
				// database.execSQL(sql, new Object[] { path.hashCode(), name,
				// path });
			}
			database.setTransactionSuccessful();
			database.endTransaction();
		} catch (Exception e) {
			Log.e(TAG, "Exception-》database.setTransaction not Successful()...");
			return false;
		} finally {
			database.close();
			Log.e("8", "====== caculate insert time:" + String.valueOf(Calendar.getInstance().getTimeInMillis() - start));

			// resolver.notifyChange(uri, observer)
		}
		return true;
	}